如何取得 SQL Job 所執行的最後一個 SQL statement？(Command RTVSQLINF with API QUSRJOBI Format JOBI0900)
現在有許多程式會透過 ODBC，JDBC 方式連線至 AS/400 查詢或更新資料，而其所連線至 AS/400 的 Job 名稱為
QZDASOINIT，而當有程式當掉時，需要得知該 Job 所執行的最後一個 SQL statement為何，用以除錯及快數解決
錯誤時使用。所以我們需要透過 API QUSRJOBI Format JOBI0900 來擷取 Job 的最後一個 SQL statement。
File : QRPGLESRC
Member: RTVSQLINF
Type : RPGLE
Usage : CRTBNDRPG PGM(RTVSQLINF) TGTRLS(V5R1M0)
OS Version: V5R1
**
** Program . . : RTVSQLINF
** Description : Retrieve Job last SQL statement
** Author . . : Vengoal Chang
**
** Date . . : 2008/06/25
**
** Compile and setup instructions:
** CrtRpgMod Module( RTVSQLINF )
** DbgView( *LIST )
**
** CrtPgm Pgm( RTVSQLINF )
** Module( RTVSQLINF )
** ActGrp( *NEW )
**
**
**-- Control specification: --------------------------------------------**
H DEBUG OPTION(*SRCSTMT:*NODEBUGIO) DFTACTGRP(*NO) ACTGRP(*CALLER)
FQSYSPRT O F 132 Printer UsrOpn
*
D RtvSQLInf PR
D JobName 10a CONST
D UserName 10a CONST
D JobNumber 6a CONST
*
D RtvSQLInf PI
D ParmJobName 10a CONST
D ParmUserName 10a CONST
D ParmJobNumber 6a CONST
*
D RtvJobSQL PR EXTPGM('QUSRJOBI')
D RcvVar 65535a Options( *VarSize)
D RcvVarLen 10i 0 Const
D FmtName 8a Const
D QualJobName 26a Const
D InternalJobID 16a Const
D ErrorCode like(APIErr)
D ResetPfrStat 1a Const
*
D JOBI0900 DS 65535 Qualified
D NbrBytesRtn 10i 0
D NbrBytesAvl 10i 0
D JobName 10a
D UsrName 10a
D JobNbr 6a
D InternalJobID 16a
D JobSts 10a
D JobType 1a
D JobSubType 1a
D SvrMode 1a
D rsvd 1a
D OfsOpnCrs 10i 0
D SizOpnCrs 10i 0
D NbrOpnCrs 10i 0
D OfsCurCrs 10i 0
D LenCurCrs 10i 0
D StsCurCrs 10i 0
D CCSIDCurCrs 10i 0
D RDBname 18a
D SQLObj 10a
D SQLLib 10a
D SQLObjType 10a
D rsvd2 4a
D CumNbrFullOpn 20i 0
D CumNbrPsedOpn 20i 0
D OfsCurSQL 10i 0
D LenCurSQL 10i 0
*
D CursorInfo DS Qualified
D ObjName 10a
D ObjLib 10a
D ObjType 10a
D SQLCurName 18a
D SQLStmtname 18a
*
D APIErr DS Qualified
D ErrSize 10i 0 inz(%size(APIErr))
D ErrLen 10i 0 inz(0)
D ErrID 7a
D rsvd 1a
D ErrData 256a
*
D QualJobName DS 26 Qualified
D JobName 10a
D UserName 10a
D JobNumber 6a
*
D RcvSize S 10 0 INZ(262140)
*
D i S 5 0
D StartPos S 5 0
*
D SQLStmt S 65535a Varying
*
D SQLLineDS DS
D SQLLine 100a Dim(50)
D SQLLineOut S 100a
D idx S 5 0
D totline S 5 0
*
D HandleErr PR
*
/Free
QualJobName.JobName = ParmJobName ;
QualJobName.UserName = ParmUserName ;
QualJobName.JobNumber = ParmJobNumber ;
RtvJobSQL( JOBI0900 :
// %Size(JOBI0900) :
RcvSize :
'JOBI0900' :
QualJobName :
*blanks :
APIErr :
'0' ) ;
If APIErr.ErrLEN <> 0 ;
HandleErr() ;
else ;
If JOBI0900.NBROPNCRS <> 0 ;
StartPos = %dec(JOBI0900.OFSOPNCRS) + 1 ;
for i = 1 to %dec(JOBI0900.NBROPNCRS) ;
CursorInfo = %subst(JOBI0900 : StartPos) ;
//dsply CursorInfo.SQLStmtname;
StartPos = StartPos + %size(CursorInfo) ;
endfor ;
else ;
CursorInfo.SQLCurName = '*NONE' ;
endIf ;
if JOBI0900.LENCURCRS > 0 ;
SQLStmt = %subst(JOBI0900:%dec(JOBI0900.OFSCURCRS)+1:
%dec(JOBI0900.LENCURCRS)) ;
Open Qsysprt;
SQLLineDS = SQLStmt;
totline = %div(%len(%trim(SQLLineDS)) : %len(SQLLineOut));
Except Title;
if (%len(%trim(SQLLineDS)) > (totline * %len(SQLLineOut)));
totline += 1;
endif;
For idx = 1 to totline;
SQLLineOut = SQLLine(idx);
Except detail;
EndFor;
Close Qsysprt;
//Dump;
endIf;
endIf ;
*inLR = *on ;
return ;
/End-Free
*
OQSYSPRT E Title 1
O 12 'Jobname '
O 23 'User '
O 30 'Jobnbr'
OQSYSPRT E Title 1
O ParmJobName 12
O ParmUserName 23
O ParmJobNumber 30
O 46 'SQL statement:'
OQSYSPRT E detail 1
O SQLLineOut 110
*
P HandleErr B
D HandleErr PI
*
/Free
/End-Free
*
P HandleErr E
File : QCMDSRC
Member: RTVSQLINF
Type : RPGLE
Usage : CRTCMD CMD(RTVSQLINF) PGM(RTVSQLINF)
/* =============================================================== */
/* = Command....... RtvSqlInf = */
/* = CPP........... RtvSqlInf = */
/* = Description... Retrieve Job last current SQL statement = */
/* = = */
/* = CrtCmd Cmd( RtvSqlInf ) = */
/* = Pgm( RtvSqlInf ) = */
/* = SrcFile( YourSourceFile ) = */
/* =============================================================== */
/* = Date : 2008/06/25 = */
/* = Author: Vengoal Chang = */
/* =============================================================== */
CMD PROMPT('Retrieve Job last SQL stmt')
PARM KWD(JOBNAME) TYPE(*NAME) MIN(1) PROMPT('Job +
name')
PARM KWD(JOBUSER) TYPE(*NAME) MIN(1) PROMPT('Job +
user')
PARM KWD(JOBNBR) TYPE(*CHAR) LEN(6) +
RANGE('000000' '999999') MIN(1) +
FULL(*YES) EXPR(*YES) PROMPT('Job number')
File : QCLSRC
Member: RTVSQLINFC
Type : CLP
Usage : CRTCLPGM RTVSQLINFC
執行 CALL RTVSQLINFC 後，會產生 QSYSPRT 報表，檢視 QSYSPRT 報表，
報表會顯示所有 QZDASOINIT job 所執行的最後一個 SQL statement。
若無 QSYSPRT 報表產生，表示並無任何透過 ODBC 或 JDBC 連線進入
系統執行 SQL statement。
/* =============================================================== */
/* = Program: RtvSqlInfc = */
/* = Type : CLP = */
/* = Description : Retrieve QZDASOINIT Job last SQL stmt = */
/* =============================================================== */
/* = Date : 2008/06/25 = */
/* = Author: Vengoal Chang = */
/* =============================================================== */
RtvSQLINFC: PGM
DCL VAR(&JOBNAME) TYPE(*CHAR) LEN(10)
DCL VAR(&USER) TYPE(*CHAR) LEN(10)
DCL VAR(&CURUSR) TYPE(*CHAR) LEN(10)
DCL VAR(&JOBNBR) TYPE(*CHAR) LEN(6)
DCL VAR(&STATUS) TYPE(*CHAR) LEN(10)
DCL VAR(&JOBTYPE) TYPE(*CHAR) LEN(1)
DCL VAR(&SUBTYPE) TYPE(*CHAR) LEN(1)
DCL VAR(&USP_NAME) TYPE(*CHAR) LEN(10)
DCL VAR(&USP_LIB) TYPE(*CHAR) LEN(10)
DCL VAR(&USP_QUAL) TYPE(*CHAR) LEN(20)
DCL VAR(&USP_TYPE) TYPE(*CHAR) LEN(10)
DCL VAR(&USP_SIZE) TYPE(*CHAR) LEN(4)
DCL VAR(&USP_FILL) TYPE(*CHAR) LEN(1)
DCL VAR(&USP_AUT) TYPE(*CHAR) LEN(10)
DCL VAR(&USP_TEXT) TYPE(*CHAR) LEN(50)
DCL VAR(&USP_REPL) TYPE(*CHAR) LEN(10)
DCL VAR(&USP_RTNL) TYPE(*CHAR) LEN(10)
DCL VAR(&USP_CHGATR) TYPE(*CHAR) LEN(16)
DCL VAR(&USP_ATRREC) TYPE(*CHAR) LEN( 4)
DCL VAR(&USP_ATRLEN) TYPE(*CHAR) LEN( 4)
DCL VAR(&USP_ATRKEY) TYPE(*CHAR) LEN( 4)
DCL VAR(&USP_ATRDTA) TYPE(*CHAR) LEN( 1)
DCL VAR(&API_USQUAL) TYPE(*CHAR) LEN(20)
DCL VAR(&API_JBQUAL) TYPE(*CHAR) LEN(26)
DCL VAR(&API_JBNAM) TYPE(*CHAR) LEN(10)
DCL VAR(&API_USER) TYPE(*CHAR) LEN(10)
DCL VAR(&API_JOBNR) TYPE(*CHAR) LEN(6)
DCL VAR(&API_STATUS) TYPE(*CHAR) LEN(10)
DCL VAR(&STARTPOS) TYPE(*CHAR) LEN(4)
DCL VAR(&DATALEN) TYPE(*CHAR) LEN(4)
DCL VAR(&HEADER) TYPE(*CHAR) LEN(150)
DCL VAR(&LST_OFFSET) TYPE(*DEC) LEN(5 0)
DCL VAR(&LST_SIZE) TYPE(*DEC) LEN(5 0)
DCL VAR(&LST_DATA) TYPE(*CHAR) LEN(4096)
DCL VAR(&LST_NBR) TYPE(*DEC) LEN(5 0)
DCL VAR(&LST_LEN) TYPE(*DEC) LEN(5 0)
DCL VAR(&LST_LENBIN) TYPE(*CHAR) LEN(4)
DCL VAR(&LST_POSBIN) TYPE(*CHAR) LEN(4)
DCL VAR(&LST_COUNT) TYPE(*DEC) LEN(5) VALUE(0)
DCL VAR(&TYPE) TYPE(*CHAR) LEN(1) VALUE('*')
DCL VAR(&NBRTORTN) TYPE(*CHAR) LEN(4)
DCL VAR(&KEYSTORTN) TYPE(*CHAR) LEN(16)
DCL VAR(&KEY1 ) TYPE(*CHAR) LEN(4)
DCL VAR(&KEY2 ) TYPE(*CHAR) LEN(4)
DCL VAR(&KEY3 ) TYPE(*CHAR) LEN(4)
DCL VAR(&KEY4 ) TYPE(*CHAR) LEN(4)
DCL VAR(&SBSSYS ) TYPE(*CHAR) LEN(20)
DCL VAR(&WRKSTS ) TYPE(*CHAR) LEN(4)
DCL VAR(&MSGRPLY ) TYPE(*CHAR) LEN(1)
DCL VAR(&MSGTXT ) TYPE(*CHAR) LEN(256)
DCL VAR(&JOBTYPE ) TYPE(*CHAR) LEN(1)
DCL VAR(&RTNLIB ) TYPE(*CHAR) LEN(10)
RTVJOBA TYPE(&JOBTYPE)
CHGVAR VAR(%BIN(&NBRTORTN)) VALUE(4)
/* 0101 -- Ststus as WRKACTJOB */
CHGVAR VAR(%BIN(&KEY1 )) VALUE(0101)
/* 1906 -- Subsystem */
CHGVAR VAR(%BIN(&KEY2 )) VALUE(1906)
/* 1307 -- Message Reply */
CHGVAR VAR(%BIN(&KEY3 )) VALUE(1307)
/* 0305 -- Current user profile */
CHGVAR VAR(%BIN(&KEY4 )) VALUE(0305)
CHGVAR VAR(&KEYSTORTN) VALUE(&KEY1 *CAT &KEY2 *CAT +
&KEY3 *CAT &KEY4)
CHGVAR VAR(&USP_NAME) VALUE('RTVSQLINFC')
CHGVAR VAR(&USP_LIB) VALUE('QTEMP')
CHGVAR VAR(&USP_QUAL) VALUE(&USP_NAME *CAT +
&USP_LIB)
CHGVAR VAR(&USP_TYPE) VALUE('MYTYPE')
CHGVAR VAR(%BIN(&USP_SIZE)) VALUE(64000)
CHGVAR VAR(&USP_FILL) VALUE(' ')
CHGVAR VAR(&USP_AUT) VALUE('*USE')
CHGVAR VAR(&USP_TEXT) VALUE('my user space')
CHGVAR VAR(&USP_REPL) VALUE('*YES')
CHGVAR VAR(%BIN(&USP_ATRREC)) VALUE( 1)
CHGVAR VAR(%BIN(&USP_ATRKEY)) VALUE( 3)
CHGVAR VAR(%BIN(&USP_ATRLEN)) VALUE( 1)
CHGVAR VAR(&USP_ATRDTA) VALUE('1')
CHGVAR VAR(&USP_CHGATR) VALUE( +
&USP_ATRREC *CAT &USP_ATRKEY *CAT +
&USP_ATRLEN *CAT &USP_ATRDTA)
LOOP:
/* CREATE USER SPACE */
CALL PGM(QUSCRTUS) PARM(&USP_QUAL &USP_TYPE +
&USP_SIZE &USP_FILL &USP_AUT &USP_TEXT +
&USP_REPL X'00000000')
/* SET AUTOMATIC EXTENDIBILITY */
CALL PGM(QUSCUSAT) PARM(&USP_RTNL &USP_QUAL +
&USP_CHGATR X'00000000')
CHGVAR VAR(&API_USQUAL) VALUE(&USP_QUAL)
CHGVAR VAR(&API_JBNAM) VALUE('QZDASOINIT')
CHGVAR VAR(&API_USER) VALUE('*ALL')
CHGVAR VAR(&API_JOBNR) VALUE('*ALL')
CHGVAR VAR(&API_STATUS) VALUE('*ACTIVE')
CHGVAR VAR(&API_JBQUAL) VALUE(&API_JBNAM *CAT +
&API_USER *CAT &API_JOBNR)
CALL PGM(QUSLJOB) PARM(&API_USQUAL 'JOBL0200' +
&API_JBQUAL &API_STATUS X'00000000' +
&TYPE &NBRTORTN &KEYSTORTN)
CHGVAR VAR(%BIN(&STARTPOS)) VALUE(1)
CHGVAR VAR(%BIN(&DATALEN)) VALUE(140)
CALL PGM(QUSRTVUS) PARM(&API_USQUAL &STARTPOS +
&DATALEN &HEADER)
CHGVAR VAR(&LST_OFFSET) VALUE(%BIN(&HEADER 125 4))
CHGVAR VAR(&LST_SIZE) VALUE(%BIN(&HEADER 129 4))
CHGVAR VAR(&LST_NBR) VALUE(%BIN(&HEADER 133 4))
CHGVAR VAR(&LST_LEN) VALUE(%BIN(&HEADER 137 4))
CHGVAR VAR(%BIN(&LST_POSBIN)) VALUE(&LST_OFFSET + 1)
CHGVAR VAR(&LST_LENBIN) VALUE(%SST(&HEADER 137 4))
CHGVAR VAR(&LST_COUNT) VALUE(0)
LST_LOOP: IF COND(&LST_COUNT *EQ &LST_NBR) THEN(GOTO +
CMDLBL(LST_END))
CALL PGM(QUSRTVUS) PARM(&API_USQUAL &LST_POSBIN +
&LST_LENBIN &LST_DATA)
CHGVAR VAR(&JOBNAME) VALUE(%SST(&LST_DATA 1 10))
CHGVAR VAR(&USER) VALUE(%SST(&LST_DATA 11 10))
CHGVAR VAR(&JOBNBR) VALUE(%SST(&LST_DATA 21 6))
CHGVAR VAR(&STATUS) VALUE(%SST(&LST_DATA 43 10))
CHGVAR VAR(&JOBTYPE) VALUE(%SST(&LST_DATA 53 1))
CHGVAR VAR(&SUBTYPE) VALUE(%SST(&LST_DATA 54 1))
/* for status */
CHGVAR VAR(&WRKSTS ) VALUE(%SST(&LST_DATA 81 4))
/* for subsystem */
CHGVAR VAR(&SBSSYS ) VALUE(%SST(&LST_DATA 101 20))
/* for msgrply */
CHGVAR VAR(&MSGRPLY) VALUE(%SST(&LST_DATA 137 1))
/* for current user */
CHGVAR VAR(&CURUSR ) VALUE(%SST(&LST_DATA 157 10))
RTVSQLINF JOBNAME(&JOBNAME) JOBUSER(&USER) +
JOBNBR(&JOBNBR)
CHGVAR VAR(&LST_COUNT) VALUE(&LST_COUNT + 1)
CHGVAR VAR(%BIN(&LST_POSBIN)) +
VALUE(%BIN(&LST_POSBIN) + &LST_LEN)
GOTO CMDLBL(LST_LOOP)
LST_END: DLTUSRSPC USRSPC(&USP_LIB/&USP_NAME)
END: ENDPGM
星期三, 11月 08, 2023
2008-06-27 如何取得 SQL Job 所執行的最後一個 SQL statement？(Command RTVSQLINF with API QUSRJOBI Format JOBI0900)
訂閱：
張貼留言 (Atom)
沒有留言:
張貼留言