ジョブの実行 SQL (可変長) とその状況およびジョブ実行状況の収集 (V5R4)

ソケットごとの実行 SQL (可変長) とその状況の収集 (V5R4)」を中心に「System API で NETSTAT 情報と関連ジョブの SQL の取得 (V5R4)」を流用しながら、ジョブを指定してその最終実行SQL をその時のシステム/ジョブ状況を取得するプログラムを作ってみました。

     H bnddir('QC2LE')                                                                              
      *                                                                                             
      *  CREATE TABLE SQLINF/SQLLOG                                                                 
      *        (INQUIRED_DATE TIMESTAMP NOT NULL WITH DEFAULT,                                      
      *         JOB_NAME CHAR (10 ) NOT NULL WITH DEFAULT,                                          
      *         JOB_USER CHAR (10 ) NOT NULL WITH DEFAULT,                                          
      *         JOB_NUMBER CHAR (6 ) NOT NULL WITH DEFAULT,                                         
      *         CURSOR_STATUS CHAR (5 ) NOT NULL WITH DEFAULT,                                      
      *         SQL_TEXT VARCHAR (1024 ) NOT NULL WITH DEFAULT,                                     
      *         ACT_JOB_STATUS CHAR(4 ) NOT NULL WITH DEFAULT,                                      
      *         DB_LOCK_WAITS INTEGER NOT NULL WITH DEFAULT,                                        
      *         INT_MCH_WAITS INTEGER NOT NULL WITH DEFAULT,                                        
      *         NDB_LOCK_WAITS INTEGER NOT NULL WITH DEFAULT,                                       
      *         DB_LOCK_WAITS_TIME INTEGER NOT NULL WITH DEFAULT,                                   
      *         INT_MCH_WAITS_TIME INTEGER NOT NULL WITH DEFAULT,                                   
      *         NDB_LOCK_WAITS_TIME INTEGER NOT NULL WITH DEFAULT,                                  
      *         THREAD_COUNT INTEGER NOT NULL WITH DEFAULT,                                         
      *         CPU_TIME INTEGER NOT NULL WITH DEFAULT,                                             
      *         AUX_IO INTEGER NOT NULL WITH DEFAULT,                                               
      *         PAGE_FAULTS INTEGER NOT NULL WITH DEFAULT,                                          
      *         ELAPSED_TIME INTEGER NOT NULL WITH DEFAULT,                                         
      *         ASYNC_IO INTEGER NOT NULL WITH DEFAULT,                                             
      *         SYNC_IO INTEGER NOT NULL WITH DEFAULT,                                              
      *         ELAPSED_TIME_PERC INTEGER NOT NULL WITH DEFAULT,                                    
      *         ELAPSED_CPU_TIME INTEGER NOT NULL WITH DEFAULT,                                     
      *         ELAPSED_LOCK_WAITS INTEGER NOT NULL WITH DEFAULT,                                   
      *         ELAPSED_PAGE_FAULTS INTEGER NOT NULL WITH DEFAULT);                                 
      *                                                                                             
      *                                                                                             
      *  CRTSQLRPGI OBJ(SQLTOOL/RTVNJOBINF) SRCFILE(SQLTOOL/QRPGLESRC)                         
      *             COMMIT(*NONE) OBJTYPE(*MODULE) DBGVIEW(*SOURCE)                                 
      *                                                                                             
      *  CRTPGM PGM(SQLTOOL/RTVNJOBINF)                                                           
      *         MODULE(SQLTOOL/RTVNJOBINF NETSQL/RTVJOBSQL NETSQL/CRTUSRSPCP)             
      *                                                                                             
      *                                                                                             
     D RtvJobInf       PR                                                                           
     D  JobName                      10a   CONST                                                    
     D  UserName                     10a   CONST                                                    
     D  JobNumber                     6a   CONST                                                    
      *                                                                                             
     D RtvJobInf       Pi                                                                           
     D  JobName                      10a   CONST                                                    
     D  UserName                     10a   CONST                                                    
     D  JobNumber                     6a   CONST                                                    
      *                                                                                             
     D RtvJobSQL       PR                                                                           
     D  JobName                      10a   CONST                                                    
     D  UserName                     10a   CONST                                                    
     D  JobNumber                     6a   CONST                                                    
     D  SQLStmt                   65535a   VARYING Options(*VarSize)                                
     D  SQLStatus                     5a                                                            
      *                                                                                             
     D CrtUsrspcP      PR              *                                                            
     D  Name                         20a                                                            
      *                                                                                             
     D RtvJobSts       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(ERRC0100)                                           
     D  ResetPfrStat                  1a   Const                                                    
      *                                                                                             
     D JOBI1000        DS                  Qualified Based(usPtr10)                                 
     D  NbrBytesRtn                  10i 0                                      Bytes Returned      
     D  NbrBytesAvl                  10i 0                                      Bytes Available     
     D  JobName                      10a                                        Job name            
     D  UsrName                      10a                                        Job user            
     D  JobNbr                        6a                                        Job number          
     D  InternalJobID                16a                                        Internal Job id     
     D  JobSts                       10a                                        Job status          
     D  JobType                       1a                                        Job type            
     D  JobSubType                    1a                                        Job subtype         
     D  rsvd                          2a                                                            
     D  ElapsTim                     20u 0                                      Elapsed time        
     D  durDiskIO                    20u 0                                      Disk I/O during     
     D  durDiskIOAsc                 20u 0                                      Disk I/O async      
     D  durDiskIOSyc                 20u 0                                      Disk I/O sync       
     D  IntRspTim                    10i 0                                      Interactive rsp     
     D  IntTrans                     10i 0                                      Interancive txs     
     D  CPU                          10i 0                                      CPU %               
     D  DbCPU                        10i 0                                      DB CPU %            
     D  CPUT                         20u 0                                      CPU Time            
     D  DbCPUT                       20u 0                                      DB CPU Time         
     D  LCKWT                        20u 0                                      Lock wait time      
     D  PageFltCounts                20u 0                                      Page fault          
      *                                                                                             
     D JOBI0200        DS                  Qualified Based(usPtr2)                                  
     D  NbrBytesRtn                  10i 0                                      Bytes Returned      
     D  NbrBytesAvl                  10i 0                                      Bytes Available     
     D  JobName                      10a                                        Job name            
     D  UsrName                      10a                                        Job user            
     D  JobNbr                        6a                                        Job number          
     D  InternalJobID                16a                                        Internal Job id     
     D  JobSts                       10a                                        Job status          
     D  JobType                       1a                                        Job type            
     D  JobSubType                    1a                                        Job subtype         
     D  SBSD                         10a                                        SBSD name           
     D  RUNPTY                       10i 0                                      Run priority        
     D  POOLID                       10i 0                                      System pool id      
     D  CPUTime                      10i 0                                                          
     D  numAUXIO                     10i 0                                                          
     D  numIntTrans                  10i 0                                      Interactive txs     
     D  TotRsp                       10i 0                                      Rsp time total      
     D  FuncTyp                       1a                                        Function type       
     D  FuncNam                      10a                                        Function name       
     D  ActJobSts                     4a                                        Active job status   
     D  numDBLW                      10i 0                                      DB lock waits       
     D  numIntLW                     10i 0                                      Internal lock w     
     D  numNDBLW                     10i 0                                      NDB lock waits      
     D  timDBLW                      10i 0                                      DB lock waits time  
     D  timIntLW                     10i 0                                      Internal lock w time
     D  timNDBLW                     10i 0                                      NDB lock waits time 
     D  rsvd                          1a                                                            
     D  curPOOLID                    10i 0                                      Current pool id     
     D  THDCount                     10i 0                                      Thread count        
     D  totCPUT                      20u 0                                      CPU time total      
     D  numAUXIOReq                  20u 0                                      AUX IO reqs         
     D  totDBCPUT                    20u 0                                      DB CPU time total   
     D  PageFlts                     20u 0                                      Page faults         
     D  ActJStsEnd                    4a                                        Act job status end  
     D  MemPoolNam                   10a                                        Memory poll name    
     D  MsgRpy                        1a                                        Message reply       
      *                                                                                             
     **-- Api error data structure:                                                                 
     D ERRC0100        Ds                  Qualified                                                
     D  BytPro                       10i 0 Inz( %Size( ERRC0100 ))                                  
     D  BytAvl                       10i 0 Inz                                                      
     D  MsgId                         7a                                                            
     D                                1a                                                            
     D  MsgDta                      128a                                                            
      *                                                                                             
     D  pSQLStatus     S              5a                                                            
     D  pSQLStmt       S          65535a   VARYING                                                  
     D  SQLTEXT        S           1024a                                                            
      *                                                                                             
     D QualJobName     DS            26    Qualified                                                
     D  Job                          10a                                                            
     D  User                         10a                                                            
     D  JobNO                         6a                                                            
      *                                                                                             
     D pName           S             20a                                                            
      *                                                                                             
     D RcvSize         S             10  0 INZ(16776704)                                            
      *                                                                                             
     D SndPgmMsg       PR                  EXTPGM('QMHSNDPM')                                       
     D  MsgID                         7a   Const                                                    
     D  QualMsgF                     20a   Const                                                    
     D  MsgDta                      256a   Const                                                    
     D  MsgDtaLen                    10i 0 Const                                                    
     D  MsgType                      10a   Const                                                    
     D  CallStkEntry                 10a   Const                                                    
     D  CallStkCount                 10i 0 Const                                                    
     D  MsgKey                        4a   Const                                                    
     D  ErrorCode                          like(ERRC0100)                                           
      *                                                                                             
     D MsgKey          S              4a                                                            
      *                                                                                             
      /Free                                                                                         
                                                                                                    
             *inLR = *on;                                                                           
                                                                                                    
             pName =    'JOBI0200  QTEMP     ' ;                                                    
             usPtr2 = CrtUsrspcP  (pName ) ;                                                        
             pName =    'JOBI1000  QTEMP     ' ;                                                    
             usPtr10 = CrtUsrspcP  (pName ) ;                                                       
                                                                                                    
             RtvJobSQL( JobName                                                                     
                      : UserNAme                                                                    
                      : JobNumber                                                                   
                      : pSQLStmt                                                                    
                      : pSQLStatus                                                                  
                       );                                                                           
                                                                                                    
              QualJobName.Job = JobName ;                                                           
              QualJobName.User = UserName ;                                                         
              QualJobName.JobNO = JobNumber ;                                                       
                                                                                                    
              RtvJobSts( JOBI0200                                                                   
                       : RcvSize                                                                    
                       : 'JOBI0200'                                                                 
                       :  QualJobName                                                               
                       :  *blanks                                                                   
                       : ERRC0100                                                                   
                       : '0'                                                                        
                       ) ;                                                                          
                                                                                                    
              if ERRC0100.MsgId <> '' ;                                                             
                 SndPgmMsg( ERRC0100.msgid                                                          
                          : 'QCPFMSG   *LIBL'                                                       
                          : %trimR(%trim(ERRC0100.msgdta))                                          
                          : %len(%trimR(%trim(ERRC0100.msgdta)))                                    
                          : '*COMP'                                                                 
                          : '*EXT'                                                                  
                          : 1                                                                       
                          : MsgKey                                                                  
                          : ERRC0100 );                                                             
                 return ;                                                                           
              endif ;                                                                               
                                                                                                    
              RtvJobSts( JOBI1000                                                                   
                       : RcvSize                                                                    
                       : 'JOBI1000'                                                                 
                       :  QualJobName                                                               
                       :  *blanks                                                                   
                       : ERRC0100                                                                   
                       : '0'                                                                        
                       ) ;                                                                          
                                                                                                    
              SQLText = %subst(pSQLStmt:1) ;                                                        
                                                                                                    
              exec SQL INSERT INTO SQLINF/SQLLOG                                                    
                       VALUES( CURRENT_TIMESTAMP,                                                   
                               :JobName,                                                            
                               :UserName,                                                           
                               :JobNumber,                                                          
                               :pSQLStatus,                                                         
                               :SQLText,                                                            
                               //JOBI0200                                                           
                               :JOBI0200.ActJobSts,                                                 
                               :JOBI0200.numDBLW,                                                   
                               :JOBI0200.numIntLW,                                                  
                               :JOBI0200.numNDBLW,                                                  
                               :JOBI0200.timDBLW,                                                   
                               :JOBI0200.timIntLW,                                                  
                               :JOBI0200.timNDBLW,                                                  
                               :JOBI0200.THDCount,                                                  
                               :JOBI0200.totCPUT,                                                   
                               :JOBI0200.numAUXIOReq,                                               
                               :JOBI0200.PageFlts,                                                  
                               //JOBI1000                                                           
                               :JOBI1000.ElapsTim,                                                  
                               :JOBI1000.durDiskIOAsc,                                              
                               :JOBI1000.durDiskIOSyc,                                              
                               :JOBI1000.CPU,                                                       
                               :JOBI1000.CPUT,                                                      
                               :JOBI1000.LCKWT,                                                     
                               :JOBI1000.PageFltCounts                                              
                             ) ;                                                                    
                                                                                                    
              return;                                                                               
                                                                                                    
      /End-Free                                                                                     

呼び出し用の CL プログラムです。

             PGM        PARM(&PARM1)                                            
                                                                                
             DCL        VAR(&PARM1) TYPE(*CHAR) LEN(26)                         
             DCL        VAR(&MSGDTA) TYPE(*CHAR) LEN(512)                       
             DCL        VAR(&JOB) TYPE(*CHAR) STG(*DEFINED) LEN(10) +           
                          DEFVAR(&PARM1 1)                                      
             DCL        VAR(&USR) TYPE(*CHAR) STG(*DEFINED) LEN(10) +           
                          DEFVAR(&PARM1 11)                                     
             DCL        VAR(&NBR) TYPE(*CHAR) STG(*DEFINED) LEN(6) +            
                          DEFVAR(&PARM1 21)                                     
                                                                                
             IF         COND(&JOB = '          ') THEN(GOTO +                   
                          CMDLBL(PARM_ERR))                                     
             IF         COND(&USR = '          ') THEN(GOTO +                   
                          CMDLBL(PARM_ERR))                                     
             IF         COND(&NBR = '      ') THEN(GOTO +                       
                          CMDLBL(PARM_ERR))                                     
                                                                                
             DSPJOBLOG  JOB(&NBR/&USR/&JOB) OUTPUT(*PRINT)                      
             MONMSG     MSGID(CPF1070) EXEC(DO)                                 
             RCVMSG     MSGDTA(&MSGDTA)                                         
             SNDPGMMSG  MSGID(CPF1070) MSGF(QCPFMSG) MSGDTA(&MSGDTA)            
             GOTO       CMDLBL(RETURN)                                          
             ENDDO                                                              
                                                                                
             CALL       PGM(RTVJOBINF) PARM(&JOB &USR &NBR)                     
             GOTO       CMDLBL(RETURN)                                          
                                                                                
 PARM_ERR:   SNDPGMMSG  +                                                       
                          MSG('ジョブ名が指定されていないので情報は+            
                          取得されていません。') MSGTYPE(*COMP)                 
 RETURN:     RETURN                                                             
             ENDPGM                                                             

実行用のコマンドも作ってみました。

             CMD        PROMPT('ジョブ情報の検索')                               
             PARM       KWD(JOB) TYPE(QUAL1) PROMPT('ジョブ名')                  
 QUAL1:      QUAL       TYPE(*NAME) LEN(10) DFT(*)                              
             QUAL       TYPE(*NAME) LEN(10) PROMPT('ユーザー')                  
             QUAL       TYPE(*CHAR) LEN(6) RANGE(000000 999999) +               
                          PROMPT('番号')                                        

7.1 になったら「ソケットごとの実行 SQL (可変長) とその状況の収集 (V7R1)」を利用して機能拡張も考えたいところですねぇ。

[Top Pageに戻る]

Ads by TOK2