ソケットごとの実行 SQL (可変長) とその状況の収集 (V7R1)

ソケットごとの実行 SQL (可変長) とその状況の収集 (V5R4)」で紹介したジョブの”最終実行 SQL”を取得するプログラムですが、V7R1 では取得できる情報が大幅に増えています。

取り方のサンプルも含めて変更を入れてみました。

                if JOBI0900.OFSIFCLVL <> 0 ;                                                        
                   StartPosCI = %addr(JOBI0900) + (JOBI0900.OFSIFCLVL) ;                            
                   IfcLevel = %str(StartPosCI:%dec(JOBI0900.LENIFCLVL)) ;                           
                endif ;                                                                             
                                                                                                    
                if JOBI0900.OFSIFCNAME <> 0 ;                                                       
                   StartPosCI = %addr(JOBI0900) + (JOBI0900.OFSIFCNAME) ;                           
                   IfcName = %str(StartPosCI:%dec(JOBI0900.LENIFCNAME)) ;                           
                endif ;                                                                             
                                                                                                    
                if JOBI0900.OFSIFCTYPE <> 0 ;                                                       
                   StartPosCI = %addr(JOBI0900) + (JOBI0900.OFSIFCTYPE) ;                           
                   IfcType = %str(StartPosCI:%dec(JOBI0900.LENIFCTYPE)) ;                           
                endif ;                                                                             

その値そのものが必ずしも入っていない場合があり、その時はオフセット値が 0 になっています。
最初に 0 かどうかをチェックしてから、アドレスの計算をするようなかたちがいいでしょう。

また、↓のような、クライアントの情報が取れるようになっています。
データの属性がどうなっているか等については、sqleseti()--Set Client Information に載っています。

 

CLI current handle count. The number of CLI handles which are active for the job. The limit for a job is 160,000. This count includes CLI statement handles, descriptor handles, environment handles and connection handles.End of change

Start of changeClient accounting string. The value of the client accounting string. Refer to the sqleseti()--Set Client Information documentation for more details about this Client Information SQL special register value.End of change

Start of changeClient application name. The value of the client application name. Refer to the sqleseti()--Set Client Information documentation for more details about this Client Information SQL special register value.End of change

Start of changeClient IP address for Server job. The character form of the Client IP address for the database server job. If the target job is not a database server job (QRWTSRVR, QZDASOINIT or QSQSRVR), blanks will be returned. Refer to the Client IP address for server job type field to determine the IP format used by this field. End of change

Start of changeClient IP address for server job type. Indicates the format of data returned within the Client IP address for server job field. The valid values are:

'0' No Client IP address is in use.
'1' IPv4 format.
'2' IPv6 format.End of change

Start of changeClient program identifier. The value of the client program identifier. Refer to the sqleseti()--Set Client Information API documentation for more details about this Client Information SQL special register value.End of change

Start of changeClient user identifier. The value of the client user identifier. Refer to the sqleseti()--Set Client Information documentation for more details about this Client Information SQL special register value.End of change

Start of changeClient workstation name. The value of the client workstation name. Refer to the sqleseti()--Set Client Information documentation for more details about this Client Information SQL special register value.End of change

全文は↓になります。(返り値には反映していません)

      *******************************************************************************               
      *                                                                             *               
      *   CRTSQLRPGI OBJ(NETSQL/RTVNETSQL) SRCFILE(NETSQL/QRPGLESRC) COMMIT(*NONE)  *               
      *   OBJTYPE(*MODULE) DBGVIEW(*SOURCE)                                         *               
      *                                                                             *               
      *******************************************************************************               
     H                                                                                              
      *                                                                                             
     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 RtvJobSQL       PI                                                                           
     D  ParmJobName                  10a   CONST                                                    
     D  ParmUserName                 10a   CONST                                                    
     D  ParmJobNumber                 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(APIErr)                                             
     D  ResetPfrStat                  1a   Const                                                    
      *                                                                                             
     D JOBI0900        DS                  Qualified Based(usPtr)                                   
     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     
      * Extended by V7R1                                                     
     D  LOBLocCnt                    10i 0                                                          
     D  ACTGRPCnt                    10i 0                                                          
     D  CurSQLDCnt                   20i 0                                                          
     D  CLICurHdlCnt                 20i 0                                                          
     D  OfsCliActStr                 10i 0                                                          
     D  LenCliActStr                 10i 0                                                          
     D  OfsCliAppName                10i 0                                                          
     D  LenCliAppName                10i 0                                                          
     D  OfsCliPGMID                  10i 0                                                          
     D  LenCliPGMID                  10i 0                                                          
     D  OfsCliUsrID                  10i 0                                                          
     D  LenCliUsrID                  10i 0                                                          
     D  OfsCliWsnName                10i 0                                                          
     D  LenCliWsnName                10i 0                                                          
     D  OfsIfcName                   10i 0                                                          
     D  LenIfcName                   10i 0                                                          
     D  OfsIfcType                   10i 0                                                          
     D  LenIfcType                   10i 0                                                          
     D  OfsIfcLvl                    10i 0                                                          
     D  LenIfcLvl                    10i 0                                                          
     D  PsedClsdCrsNb                10i 0                                                          
     D  LclPortSvrJob                10i 0                                                          
     D  CliIP4SvrType                 1a                                                            
     D  CliIP4SvrJob                 45a                                                            
     D  QryOptLibName                10a                                                            
     D  SQLStartTS                    8a                                                            
     D  SvrModeCnnJob                28a                                                            
     D  SvrModeCnnThd                 8a                                                            
      *                                                                                             
     D CursorInfo      DS                  Qualified Based(StartPosC)                               
     D   ObjName                     10a                                                            
     D   ObjLib                      10a                                                            
     D   ObjType                     10a                                                            
     D   SQLCurName                  18a                                                            
     D   SQLStmtname                 18a                                                            
     D   rsvd                         2a                                                            
     D   OfsESQLStmtN                10i 0                                                          
     D   LenESQLStmtN                10i 0                                                          
     D   OfsESQLCurN                 10i 0                                                          
     D   LenESQLCurN                 10i 0                                                          
      *                                                                                             
     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                   32767a                                                            
      *                                                                                             
     D QualJobName     DS            26    Qualified                                                
     D  JobName                      10a                                                            
     D  UserName                     10a                                                            
     D  JobNumber                     6a                                                            
      *                                                                                             
     D pName           S             20a                                                            
     D usPtr           S               *                                                            
      *                                                                                             
     D RcvSize         S             10  0 INZ(16776704)                                            
      *                                                                                             
     D i               S              5  0                                                          
     D StartPosC       S               *                                                            
     D StartPosS       S               *                                                            
     D StartPosCI      S               *                                                            
      *                                                                                             
     D HandleErr       PR                                                                           
      *                                                                                             
     D SQLCurName      S             18a                                                            
     D SQLStmtname     S             18a                                                            
      *                                                                                             
     D IfcLevel        S              8a   VARYING                                                  
     D IfcName         S            128a   VARYING                                                  
     D IfcType         S             10a   VARYING                                                  
      *                                                                                             
      /Free                                                                                         
                                                                                                    
          QualJobName.JobName = ParmJobName ;                                                       
          QualJobName.UserName = ParmUserName ;                                                     
          QualJobName.JobNumber = ParmJobNumber ;                                                   
                                                                                                    
          pName =    'JOBI0900  QTEMP     ' ;                                                       
          usPtr = CrtUsrspcP  (pName ) ;                                                            
                                                                                                    
          RtvJobSts( JOBI0900 :                                                                     
                      RcvSize :                                                                     
                   'JOBI0900' :                                                                     
                  QualJobName :                                                                     
                      *blanks :                                                                     
                       APIErr :                                                                     
                        '0' ) ;                                                                     
                                                                                                    
          if APIErr.ErrLEN <> 0 ;                                                                   
                                                                                                    
             handleErr() ;                                                                          
                                                                                                    
          else ;                                                                                    
                                                                                                    
             if JOBI0900.NBROPNCRS <> 0 ;                                                           
                                                                                                    
                StartPosC = %addr(JOBI0900) + (JOBI0900.OFSOPNCRS) ;                                
                                                                                                    
                for i = 1 to %dec(JOBI0900.NBROPNCRS) ;                                             
                   SQLCurName = CursorInfo.SQLCurName ;                                             
                   SQLStmtname = CursorInfo.SQLStmtname ;                                           
                   StartPosC = StartPosC + %size(CursorInfo) ;                                      
                endfor ;                                                                            
                                                                                                    
                StartPosS = %addr(JOBI0900) + (JOBI0900.OFSCURCRS) ;                                
                SQLStmt = %str(StartPosS:%dec(JOBI0900.LENCURCRS)) ;                                
                                                                                                    
                if JOBI0900.OFSIFCLVL <> 0 ;                                                        
                   StartPosCI = %addr(JOBI0900) + (JOBI0900.OFSIFCLVL) ;                            
                   IfcLevel = %str(StartPosCI:%dec(JOBI0900.LENIFCLVL)) ;                           
                endif ;                                                                             
                                                                                                    
                if JOBI0900.OFSIFCNAME <> 0 ;                                                       
                   StartPosCI = %addr(JOBI0900) + (JOBI0900.OFSIFCNAME) ;                           
                   IfcName = %str(StartPosCI:%dec(JOBI0900.LENIFCNAME)) ;                           
                endif ;                                                                             
                                                                                                    
                if JOBI0900.OFSIFCTYPE <> 0 ;                                                       
                   StartPosCI = %addr(JOBI0900) + (JOBI0900.OFSIFCTYPE) ;                           
                   IfcType = %str(StartPosCI:%dec(JOBI0900.LENIFCTYPE)) ;                           
                endif ;                                                                             
                                                                                                    
                SELECT ;                                                                            
                   WHEN JOBI0900.StsCurCrs = 1 ;                                                    
                      SQLStatus = 'ACT' ;                                                           
                   WHEN JOBI0900.StsCurCrs = 2 ;                                                    
                      SQLStatus = 'INACT' ;                                                         
                   OTHER ;                                                                          
                      SQLStatus = %char(JOBI0900.StsCurCrs) ;                                       
                endSL ;                                                                             
                                                                                                    
             else ;                                                                                 
                                                                                                    
                SQLCurName = '*NONE' ;                                                              
                SQLStmt = '*NONE' ;                                                                 
                                                                                                    
             endIf ;                                                                                
                                                                                                    
             SQLStmt = %trimR(SQLStmt) ;                                                            
                                                                                                    
          endIf ;                                                                                   
                                                                                                    
          *inLR = *on ;                                                                             
          return ;                                                                                  
                                                                                                    
      /End-Free                                                                                     
      *                                                                                             
      *                                                                                             
     P HandleErr       B                                                                            
     D HandleErr       PI                                                                           
      *                                                                                             
      /Free                                                                                         
                                                                                                    
      /End-Free                                                                                     
      *                                                                                             
     P HandleErr       E                                                                            

[Top Pageに戻る]

Ads by TOK2