/* REXX * DBRM: inspect a DBRM * * Matthe van der Lee */ arg da /* input data set name */ dsn = strip(da,,'''') /* strip off quotes */ msg = msg('OFF') /* suppress TSO messages */ 'PIPE <' da'|stem a.' /* read the thing */ parse var a.1 d 5 l 9 user 17 . 25 ts 33 . /* inspect first record */ if rc ¬= 0 | d ¬= 'DBRM' then do /* not a DBRM */ say da 'does not appear to be a DBRM' exit 8 end f = (sysvar('SYSENV') = 'FORE') /* running in the foreground */ if f then 'CLS' /* clear screen */ o = 0 /* output line index */ call zay dsn 'was generated', /* show the timestamp */ db2ts(ts)',', /* as converted by the DB2TS function */ 'by' user /* user responsible */ b = 1+c2d(l)/80 /* 1 plus length of header divided by LRECL */ c = 0 /* query counter */ m = 10 /* largest query length */ ebcdic = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' /* EBCDIC characters */ unicode = 0 /* assume no UNICODE present */ do i = b to a.0 /* begin at line B */ parse var a.i d, /* extract eyecatcher */ 5 l, /* statement length */ 9 a 11 ., /* "SQLSECTN" */ 13 s 17 ., /* "SQLSTNUM" */ 21 q, /* length of query */ 25 t /* actual text */ if d ¬= 'DBRM' then do /* no good */ call zay 'error in line' i': DBRM eyecatcher missing' signal exit end if i = b then do unicode = (verify(left(t,3),ebcdic) > 0) if unicode then tableo =, '00010203372D2E2F1605250B0C0D0E0F'x ||, '101112133C3D322618193F271C1D1E1F'x ||, '404F7F7B5B6C507D4D5D5C4E6B604B61'x ||, 'F0F1F2F3F4F5F6F7F8F97A5E4C7E6E6F'x ||, '7CC1C2C3C4C5C6C7C8C9D1D2D3D4D5D6'x ||, 'D7D8D9E2E3E4E5E6E7E8E94AE05A5F6D'x ||, '79818283848586878889919293949596'x ||, '979899A2A3A4A5A6A7A8A9C0BBD0A107'x ||, '202122232415061728292A2B2C090A1B'x ||, '30311A333435360838393A3B04143EFF'x ||, '41AAB0B19FB26AB5BDB49A8ABACAAFBC'x ||, '908FEAFABEA0B6B39DDA9B8BB7B8B9AB'x ||, '6465626663679E687471727378757677'x ||, 'AC69EDEEEBEFECBF80FDFEFBFCADAE59'x ||, '4445424643479C485451525358555657'x ||, '8C49CDCECBCFCCE170DDDEDBDC8D8EDF'x end l = c2d(l) /* convert length to decimal */ r = 80 /* bytes read for this statement */ do while r < l /* while incomplete */ i = i+1 /* next input record */ t = t || a.i /* append text */ r = r+80 /* another 80 bytes */ end t = left(t,l-24) /* truncate */ q = c2d(q) /* convert query length */ h = substr(t,q+1) /* extract the host variables section */ d = left(t,q) /* and the query data */ t = '' /* clear output query buffer */ parse var h v 3 . 5 h /* get V = number of host variables */ v = c2d(v) /* convert */ do j = 1 to v /* do all items */ parse var h w 3 . 21 l 23 n /* W = item length, L = name length */ n.j = left(n,c2d(l)) /* extract host variable name */ if unicode then n.j = translate(n.j,tableo) /* convert if UTF-8 */ h = substr(h,c2d(w)+1) /* remove data processed */ end j = 0 /* initialize host variable counter */ if unicode then d = translate(d,tableo) /* convert UNICODE data */ y = '' do l = 1 to q /* slightly reformat the query */ u = substr(d,l,8) /* current 8 bytes under the cursor */ v = left(u,3) /* leftmost 3 bytes */ w = left(u,2) /* leftmost 2 */ x = left(u,1) /* current byte */ select when w == ' ,' then do /* comma preceded by a blank */ t = t || ',' /* remove blank */ l = l+1 end when w == '( ' then do /* change "( " */ t = t || '(' /* to "(" */ l = l+1 end when w == ' )' then do /* change " )" */ t = t || ')' /* to ")" */ l = l+1 end when v == ': H' then do /* host variable */ j = j+1 /* increment count */ t = t':'n.j /* fetch name */ l = l+2 /* skip the ": H" placeholder */ end when v == ' . ' then do /* change " . " */ t = t || '.' /* to "." */ l = l+2 end when u == 'QUERYNO ' then do y = word(substr(d,l+8),1) t = t || u || y l = l+7+length(y) end otherwise t = t || x /* otherwise just copy current byte */ end end c = c+1 /* increment query count */ a = c2d(a) /* convert SQLSECTN */ if a = 0 then a = '' /* this is a DECLARE TABLE statement */ s = c2d(s) /* convert SQLSTNUM */ if y = '' then y = s /* no QUERYNO, default to precompiler line nr */ q.c = right(a,8) right(s,8) right(y,8) '' t /* store data */ m = max(m,length(t)-1) /* keep track of maximal query length */ end call zay call zay 'DBRM contents:' call zay call zay 'SQLSECTN SQLSTNUM QUERYNO Query text' n = m%10+1 call zay '-------- -------- -------- ' left(copies('---------+',n),m) q.0 = c o.0 = o /* output lines produced */ da = ''''userid()'.#DBRM#''' /* output data set */ 'PIPE state' da'|count lines|var exists' /* does it already exist? */ if ¬exists then do /* no, allocate it */ 'ALLOC DA('da') DSORG(PS) RECFM(V,B) LRECL(32756)', 'TRACKS SPACE(1,1) NEW CATALOG' 'FREE DA('da')' /* free and catalog the data set */ end 'PIPE stem o.|append stem q.|>' da 'coerce' if f then, /* foreground execution */ address ISPEXEC 'BROWSE DATASET('da')' /* browse the results */ else say dsn exit: parse value msg(msg) with . /* reenable TSO messages */ exit zay: parse arg u o = o+1 o.o = u return /*____________________________________________________________________*/ /* REXX * DB2TS: translate a DB2 timestamp into normal date/time format */ db2ts: procedure numeric digits 19 /* request sufficient precision */ m = 60 /* seconds in a minute */ h = m*m /* seconds in an hour */ s = h*24 /* seconds in a day */ /* * The offset of the local time zone from Greenwich Mean Time is kept in * field CVTTZ in the CVT in units of 1.048576 seconds. */ cvt = c2d(storage('10',4)) /* CVT address */ cvttz = d2x(cvt+c2d('130'x)) /* address of time zone field CVTTZ */ gmt = c2d(storage(cvttz,4),4) /* get GMT offset */ gmt = ((gmt*(2**20)/1E6)+1)%1 /* compute true offset from GMT */ timestamp = left(arg(1),6) /* bits 0-47 of the incoming timestamp */ m2 = c2d(timestamp) /* 2 microseconds is the unit */ sec = m2*128/1E6 /* convert this value to seconds */ sec = sec+gmt /* add hour(s), to compensate for deviation from GMT */ /* * Now convert to normal date/time format: */ days = sec%s /* days since 00:00:00h, 1/1/1900 */ time = (sec-days*s)%1 /* time of day in seconds */ block = 4*365 /* no leap year during the first 4 years */ if days < block then do /* date falls in the range 1900-1903 */ year = 1900 /* start year at 1900 */ l = 0 /* no leaping in this period */ end else do days = days+1 /* add 1, since 1900 was NOT a leap year */ block = block+1 /* one leap year every 4 years */ year4 = days%block /* number of elapsed 4-year blocks */ days = days-block*year4 /* days remaining */ year = 1900+4*year4 /* set starting year */ l = 1 /* must take leap years into account */ end do forever /* now for the fine tuning */ len = 365+(year//4=0)*l /* days in this year */ if len <= days then parse value year+1 days-len with year days . else leave /* found the right annum */ end /* * Compute length of Februari, initialize Month to 1, and add 1 to the * day number - as humans are inclined to call day 0 "day 1": */ parse value (year//4=0)*l+28 days+1 1 with feb day month . months = 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec' mndays = '31' feb '31 30 31 30 31 31 30 31 30 31' do while day > word(mndays,month) day = day - word(mndays,month) /* home in on the correct month */ month = month + 1 end parse value time%h time//h with hour time . /* and work out the */ parse value time%m time//m with minute second . /* right time of day */ return pad(day), /* return result to caller */ word(months,month) year',' pad(hour)':'pad(minute)':'pad(second) pad: return right('0'arg(1),2) /* pad on the left with a zero */