RE: Retrieve STRSQL history (was: QRECOVERY library)

https://code.midrange.com/ and then post the link here.

Kevin Bucknum
Senior Programmer Analyst
MEDDATA/MEDTRON
Tel: 985-893-2550

-----Original Message-----

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf
Of Dan
Sent: Thursday, January 25, 2018 10:23 AM
To: Midrange Systems Technical Discussion
Subject: 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 )
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD