midrange.com code scratchpad |
Name:
Retrieve SQL History
|
Scriptlanguage:
Plain Text
|
Tabwidth:
4
|
Date:
01/25/2018 04:35:57 pm
|
IP:
Logged
|
|
Description:
Discussion on this source code can be found in the Midrange-L archives, January 2018, with the subject "Retrieve STRSQL history (was: QRECOVERY library)"
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.
|
Code:
- 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 )
|
|
|