Admin Tools Forum

Re: how to use multiple temp db spaces

Posted By: Keith Simmons
Date: Monday, 22 December 2008, at 4:04 a.m.

In Response To: Re: how to use multiple temp db spaces (KRISHNAN BALASUBRAMANIAN)

2008/12/22 KRISHNAN BALASUBRAMANIAN <krishnanbalasubramanian@rediffmail.com>:
> Thanks Keith,
>
> Here is the program we ahve written:
> database ibs7000_test2
> define rec_main record
> fld_1 char(1),
> fld_2 char(1),
> fld_3 char(9),
> fld_4 char(2),
> fld_5 char(1),
> fld_6 char(9),
> fld_7 char(1),
> fld_8 char(9),
> fld_9 date,
> fld_10 char(9),
> fld_11 char(1),
> fld_12 datetime year to second,
> fld_13 varchar(10,0),
> fld_14 varchar(16,0)
> end record
> main
>
> define l_index integer
> define l_cnt integer
> whenever error call error_log
> clear screen
> display "Creating Temp Table....."
> call create_tmp_tbl()
> display "Loading Data......"
> let l_index = 0
> while(l_index < 15000 )
> call load_data()
> select count(*) into l_cnt
> from t_sample_in
> display "No.of Records Loaded: ", l_cnt
> let l_index = l_index + 1
> end while
>
> declare cur_bptr cursor for
> select * from t_sample_in
> let l_index = 0
> start report rbptr to "/tmp/trail2.rpt"
> clear screen
> display "Reporting......"
> while(l_index < 50 )
> run "rm -f /tmp/trail2.rpt 2>/dev/null"
> foreach cur_bptr into rec_main.*
> output to report rbptr(rec_main.*)
> end foreach
> let l_index = l_index + 1
> display "NO:OF RPTS", l_index
> end while
>
> finish report rbptr
> end main
> function create_tmp_tbl()
>
> create temp table t_sample_in
> (
> fld_1 char(1),
> fld_2 char(1),
> fld_3 char(9),
> fld_4 char(2),
> fld_5 char(1),
> fld_6 char(9),
> fld_7 char(1),
> fld_8 char(9),
> fld_9 date,
> fld_10 char(9),
> fld_11 char(1),
> fld_12 datetime year to second,
> fld_13 varchar(10,0),
> fld_14 varchar(16,0)
> )with no log
> end function
>
> function load_data()
> load from "/tmp/sample_data.unl"
> insert into t_sample_in
> end function
>
> function error_log()
> call startlog("/tmp/sample_err.log")
> call errorlog(err_get(status))
>
> exit program
> end function
> report rbptr(rec)
>
> define rec record
> fld_1 char(1),
> fld_2 char(1),
> fld_3 char(9),
> fld_4 char(2),
> fld_5 char(1),
> fld_6 char(9),
> fld_7 char(1),
> fld_8 char(9),
> fld_9 date,
> fld_10 char(9),
> fld_11 char(1),
> fld_12 datetime year to second,
> fld_13 varchar(10,0),
> fld_14 varchar(16,0)
> end record
>
> order by rec.fld_1,rec.fld_4
> format
> page header
> print "*******Sample report *******"
> after group of rec.fld_4
> print "No: of Records ", count(*)
> on every row
> print
> column 1, rec.fld_1,
> rec.fld_2,
> rec.fld_3,
> rec.fld_4,
> rec.fld_5,
> rec.fld_6,
> rec.fld_7
> end report
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
KRISHNAN

This will create a temp table in true temp spaces (those marked 'T' on
onstat -d), but will use a random temp dbspace if you have more than
one configured. Also the sort in the report will create two sort
'files' the same size as your table in your temp spaces (one in each
if two or both in the same). Your records are around 80 bytes long,
multiply by the number of records to be loaded, then double (if you
only have two temp spaces, or triple if only one) and this will tell
you how big each temp space needs to be.
Might I suggest you place an index on your temp table (after loading)
on cols fld_1 and fld_4 and put the order by in your cursor rather
than in the report. Also just select the required fields in the
cursor, not the entire record, that will improve run speed.

Keith

Messages In This Thread

• how to use multiple temp db spaces
  KRISHNAN BALASUBRAMANIAN -- Friday, 12 December 2008, at 1:26 a.m.
  • Re: how to use multiple temp db spaces
    Keith Simmons -- Friday, 12 December 2008, at 4:01 a.m.
    • Re: how to use multiple temp db spaces
      KRISHNAN BALASUBRAMANIAN -- Monday, 22 December 2008, at 3:31 a.m.
      • Re: how to use multiple temp db spaces
        Keith Simmons -- Monday, 22 December 2008, at 4:04 a.m.
        • Re: how to use multiple temp db spaces
          KRISHNAN BALASUBRAMANIAN -- Tuesday, 23 December 2008, at 12:15 a.m.
          • Re: how to use multiple temp db spaces
            Keith Simmons -- Tuesday, 23 December 2008, at 3:49 a.m.
    • Re: how to use multiple temp db spaces
      REYNALDO ZEBALLOS -- Sunday, 29 March 2009, at 9:41 a.m.
  • Re: how to use multiple temp db spaces
    Art Kagel -- Friday, 12 December 2008, at 2:10 p.m.
    • Re: how to use multiple temp db spaces
      KRISHNAN BALASUBRAMANIAN -- Monday, 22 December 2008, at 3:33 a.m.
      • Re: how to use multiple temp db spaces
        Art Kagel -- Monday, 22 December 2008, at 1:34 p.m.
        • Re: how to use multiple temp db spaces
          KRISHNAN BALASUBRAMANIAN -- Tuesday, 23 December 2008, at 12:11 a.m.
          • Re: how to use multiple temp db spaces
            Art Kagel -- Tuesday, 23 December 2008, at 12:37 a.m.

Admin Tools Forum is maintained by Administrator with WebBBS 5.12.