Retrieve STRSQL history (was: QRECOVERY library)

O.k. First things first. While credit for the DMPSYSOBJ command that
retrieves the SQL history belongs to Elvis Budimlic, I must take the blame
for all of the hideous code presented below. I vaguely recall that when I
wrote this ~10 years ago, that I was in a hurry to find a specific query I
wrote and the result was a quick and dirty program that did the job. It
morphed a few times in the span since; the most recent modification was
copying the printer output from my RPG program to a physical file,
primarily so I could search without regard to upper/lower case.

Secondly, I remember that there was a way to post source code to
midrange.com, but I could not find it on the website. Is that no longer
available? I'm not sure how the long lines of the source code will show up
in email and/or the archives, so if it wraps, I'll need to find another way
to post it.

- Dan

CLLE source - RTVSQLDUMC:
/* Problem: SQL session history "disappears" and you're missing a lot of
"gold". */
/* Also, no way to search history from interactive SQL
sessions. */
/* Solution: Run a DMPSYSOBJ on a special "internal space" that keeps this
history, */
/* and parse the ugly printed output into usable
statements. */
/* Details: Suggested by Elvis Budimlic of Centerfield Technologies, in
their */
/* February 2006 newsletter (pg
5). */
/* DMPSYSOBJ OBJ(ISQLST<usrprf>*) CONTEXT(QRECOVERY) TYPE(19)
SUBTYPE(EE) */
/* where <usrprf> is your user profile
name. */
/* Copy this spool file to $DMPSYSOBJ, a 132-byte record length
flatfile, and */
/* process that file in the RPG program to produce readable
printed output. */
Pgm Parm( &p_UserID )

Dcl &p_UserID *char ( 10 )
Dcl &UserID *char ( 10 )
Dcl &JobUser *char ( 10 )
Dcl &qDateTime *char ( 20 )
Dcl &qDateYMD *char ( 6 )
Dcl &x *dec ( 2 0 )
Dcl &x@ *char ( 2 )
Dcl &MbrName *char ( 10 )

ChgVar &UserID &p_UserID
MonMsg MCH3601 Exec( Do )
ChgVar &UserID '*'
Enddo

If ( &UserID *eq '*' ) Then( Do )
RtvJobA User( &JobUser )
ChgVar &UserID &JobUser
Enddo

DmpSysObj Obj( 'ISQLST' *cat &UserID *tcat '*' ) +
Context(qRecovery) Type(19) SubType(EE)
/* output is spool file QPSRVDMP */

CrtPf DanLib/$DmpSysObj RcdLen( 132 )
MonMsg CPF7302 /* Already exists */
ClrPfm DanLib/$DmpSysObj
ChgPf DanLib/$DmpSysObj Size( *NoMax )

CpySplf qpSrvDmp ToFile( DanLib/$DmpSysObj ) SplNbr( *Last )

Call RtvSqlDumR

CrtPf DanLib/SqlHistDmp RcdLen( 132 )
MonMsg CPF7302 /* Already exists */

ChgPf DanLib/SqlHistDmp MaxMbrs( *NoMax ) Size( *NoMax )

/* Member name $yymmdd_01 */
RtvSysVal qDateTime &qDateTime /* YYYYMMDDHHNNSSXXXXXX */
ChgVar &qDateYMD %sst( &qDateTime 3 6 )
ChgVar &x 00
AddMbrLoop:
ChgVar &x@ &x
ChgVar &MbrName ( '$' *cat &qDateYMD *cat '_' *cat &x@ )
AddPfm DanLib/SqlHistDmp &MbrName
MonMsg CPF7306 Exec( Do )
ChgVar &x ( &x + 1 )
/* Not gonna worry about wrapping from 99 to 00! */
Goto AddMbrLoop
Enddo

CpySplf qPrint SplNbr( *Last ) +
ToFile( DanLib/SqlHistDmp ) ToMbr( *First )
CpySplf qPrint SplNbr( *Last ) +
ToFile( DanLib/SqlHistDmp ) ToMbr( &MbrName )

DspPfm DanLib/SqlHistDmp &MbrName

Endpgm



CMD source - RTVSQLDUMP:
CMD PROMPT('Retrieve SQL Dump')
PARM KWD(USERID) TYPE(*CNAME) LEN(10) DFT(*) +
SPCVAL((* *)) +
PROMPT('User ID')



RPGLE source - RTVSQLDUMR
* Problem: SQL session history "disappears" and you're missing a
lot of "gold".
* Also, no way to search history from interactive SQL
sessions.
* Solution: Run a DMPSYSOBJ on a special "internal space" that keeps
this history,
* and parse the ugly printed output into usable
statements.
* Details: Suggested by Elvis Budimlic of Centerfield Technologies,
in their
* February 2006 newsletter (pg
5).
* DMPSYSOBJ OBJ(ISQLST<usrprf>*) CONTEXT(QRECOVERY)
TYPE(19) SUBTYPE(EE)
* where <usrprf> is your user profile
name.
* Copy this spool file to $DMPSYSOBJ, a 132-byte record
length flatfile,
* and process that file here to produce readable printed
output.


h Option( *SrcStmt : *NoDebugIO
)


f$DmpSysObjif f 132
disk
fqPrint o f 132 printer OflInd( *inOF
)


* There are up 58 "Dump32" records per page, so 312400 should be
good for about 5386 pages.
d ds
d Dump32 19996800 Dim( 312400 )
d Dump82 179996800 Dim( 121912 )

d d s 9 0
d d82High s 9 0
d Capture s n Inz( *off )

i$DmpSysObjns
i 1 6 SPACEdash
i 1 5 NAMEdash
i 1 10 POINTERSdash
i 87 87 Star1
i 88 119 pDump32
i 120 120 Star2
i 1 132 Line132
i 122 125 Page#

/free
*inLR = *on ;
Dou %eof( $DmpSysObj ) ;
Read $DmpSysObj ;
If not %eof( $DmpSysObj ) ;

If POINTERSdash = '.POINTERS-' ;
Capture = *off ;
Exsr ConvertDump ;
Endif ;
// The stuff between "SPACE-" & ".POINTERS-" is the stuff we want to
format
// (Capture is *on)
If Capture and Star1 = '*' and Star2 = '*' ;
d += 1 ;
Dump32( d ) = pDump32 ;

Endif ;

If SPACEdash = 'SPACE-' ;
Capture = *on ;
Endif ;

Endif ;
Enddo ;


Begsr ConvertDump ;
*inOF = *On ;
d82High = ( d * 32 / 82 ) + 1 ;
For d = 1 to d82High ;
Except Print82 ;
Endfor ;

Clear Dump32 ;
Clear d ;
Endsr ;

/end-free

oqPrint e Print82 1
o Dump82( d )