zOs/TX/WK600SIN

$*(
     insert loop
     $loops time
          insert into tables specified in parmLine
          from partition $paFr to partition $paTo
          max $maIn inserts in one statement
          after $maIn (or more) inserts
              commit
              sleep $sleep
$*)
call compRun '@', file('DSN.TX.CASE(wk600ins)')
$=cr = $creator
$=maIn=1000    $**max inserts per commit
$=loops=3333
$=sleep=5
call sqlConnect $subsys
$= insCnt = 0
$= insTot = 0
if \ ${?paFr} then
    $= paFr = 1
if \ ${?paTo} then
    $= paTo = 8
say 'partitions' $paFr '-' $paTo
$@do loop=1 to $loops $@/loop/
$**   tFrom = from table
$**   |     tTo = to table
$**   |     |      number of inserts
$**   |     |      |     maxStringLen
$**   |     |      |     |
$$    0     3    200  1000
$$    4     6     20   123
$$    7     15    22   345
$**   0     0  10000  1000
$**   1     9   1000   500
$**  10    20    100   100
$$   16    30     10   100
$$   31    49      2   100
$** 100   200     10   100
$|    $@for parm $@/parmLine/
    parse value $parm with tFrom tTo ins maLe
    $= maLe =- maLe
    $@do tx=tFrom to tTo $@/tx table/
        $=tx =- right($tx, 3, 0)
        cc = sql2one('select coalesce(max(i1),0) max from' $cr'.'$tb$tx,
                         'with ur')
        $@do ix=cc by $maIn to cc+ins $@/insert/
             $=ixTo =- min($ix+$maIn, cc+ins)-1
             $=rnd=- (($tx*100 +$paFr) * 10000+ix) // 214748357
             call sqlExec $-$insSql
     $**     say sqlerrd.3 'rows inserted into' $cr'.'$tb$tx,
     $**               'from' $ix '-' $ixTo
             $=insCnt =- $insCnt + $ixTo - $ix + 1
             if $insCnt >= $maIn then do
                 call sleep $sleep
                 call sqlCommit
                 $=insTot =- $insTot + $insCnt
                 say time() ,
                     'commit after' $insCnt 'from total' $insTot,
                     'last' $cr'.'$tb$tx 'loop' $loop'/'$loops
                 $=insCnt = 0
                 end
$/insert/
$/tx table/
$/parmLine/
$/loop/
                 call sqlCommit
                 $=insTot =- $insTot + $insCnt
                 say 'commit after' $insCnt 'from total' $insTot ,
                     'last' $cr'.'$tb$tx 'loop' $loop'/'$loops
$@proc insSql $@=/insSql/
insert into $cr.$tb$tx (part, I1, i2, v1, v2, v3)
 with n  (n) as
 ( select $ix from sysibm.sysDummy1
   union all select n+1 from n where n < $ixTo
 ),
 nc (n, l1, l2, lv2, s1, s2) as
 ( select n, int(rand(${rnd}1)*$maLe), int(rand(${rnd}2)*$maLe),
          0, varchar('',$maLe), varchar('',$maLe) from n
   union all select n, l1, l2, lv2+1, s1 ||
          case when length (s1) >= l1 then ''
              else substr('abcdefghijklmnopqrstuvwxyz 0123456789<>.'
             ||  'ABCDEFGHIJKLMNOPQRSTUVWXYZ+*%&/()=?\[]{}'
                , int(rand(${rnd}3)*80)+1, 1)
             || case when length(s1) < 10 then ''
                else left(s2, min(length(s2), l1-length(s1)-1)) end end
          , s2 ||
          case when length (s2) >= l2 then ''
              else substr('abcdefghijklmnopqrstuvwxyz 0123456789<>.'
             ||  'ABCDEFGHIJKLMNOPQRSTUVWXYZ+*%&/()=?\[]{}'
                , int(rand(${rnd}4)*80)+1, 1)
             || case when length(s2) < 10 then ''
                else left(s1, min(length(s1), l2-length(s2)-1)) end end
          from nc
          where (length(s1) < l1 or length(s2) < l2) and lv2 < 2*$maLe
 )
 select
     int($paFr + $-{$paTo + .99 - $paFr} * rand(${rnd}5)),
     n,
     int(1e6*rand(${rnd}6)- .5e6),
     s1, s2, ''
   from nc
   where length(s1)  =  l1 and length(s2) = l2
$/insSql/
$***out            20090924 15:07:24
$***out            20090924 15:06:33
$***out            20090924 15:06:00
$***out            20090924 15:05:39
$***out            20090924 15:05:17
$***out            20090924 15:03:35