プラン・キャッシュ・スナップショットをプログラムで取得

プラン・キャッシュ・スナップショットをプログラムで取得するやり方の例です。

プラン・キャッシュの内容は System i ナビゲータの GUI を通して見ることができますが、GUI では定期的に取りたいとか、時間指定や何かが起きたときをトリガーに自動的に取得するとかいったことができませんよね。

知り合いの人からいただいたプログラムを(許可をもらって)ちょっと修正して載せています。


動的SQL バージョン

プラン・キャッシュ・スナップショットは、ANALYZE_PLAN_CACHE というストアド・プロシージャを呼び出すことで取得できるようになっています。

スナップショットを取得する際のいろいろな選択条件は、ストアド・プロシージャの引数で指定するようになっています。
↓のプログラムでは、その指定を文字列として動的に作成してストアド・プロシージャの呼び出しに付加し、生成された SQL の文字列(ストアド・プロシージャの呼び出し)を動的に実行する、という流れになっています。

      *                                                                                             
      *  CRTSQLRPGI OBJ(LIB/PGM) SRCFILE(LIB/QRPGLESRC) COMMIT(*NONE)                
      *  SQLCURRULE(*STD) DBGVIEW(*SOURCE)                                                          
      *                                                                                             
     H DFTACTGRP(*NO)                                                                               
      *                                                                                             
     D KEYP            S                   SQLTYPE(VarBinary:32700)                                 
     D KEYV            S                   SQLTYPE(VarBinary:32700)                                 
      *                                                                                             
     D LIBHL           S             10A   VARYING                                                  
     D FILEHL          S             10A   VARYING                                                  
     D LIBLL           S             10A   VARYING                                                  
     D FILELL          S             10A   VARYING                                                  
      *                                                                                             
     D FILTER          S           1000A   VARYING DIM(36)                                          
      *                                                                                             
     D i               S              5i 0                                                          
     D e               S              5i 0                                                          
      *                                                                                             
     D STRING02        S          32700A   VARYING                                                  
     D STRING04        S          32700A   VARYING                                                  
     D pSTRING         S          32700A   VARYING                                                  
     D cSTRING         S          32700A   VARYING                                                  
      *                                                                                             
      /Free                                                                                         
                                                                                                    
         LIBHL = 'QGPL' ;                                                                           
         FILEHL = 'PCHL' ;                                                                          
         LIBLL = 'QGPL' ;                                                                           
         FILELL = 'PCLL' ;                                                                          
                                                                                                    
       //FILTER(1) = 'CU';                                                                          
         FILTER(1) = 'TE%ORDER BY%';                                                                
         FILTER(2) = 'ST2009-09-16-18.00.00.000000' ;                                               
       //FILTER(2) = 'MT100';                                                                       
                                                                                                    
         // expects the end of elements to be *BLANK                                                
         e = %lookup(*BLANK : FILTER) ;                                                             
         if e = 0 ;                                                                                 
             for i = 1 to (%elem(FILTER) - 1) ;                                                     
                pString = pSTRING + '''' + FILTER(i) + ''', ' ;                                     
             endfor ;                                                                               
                pString = pSTRING + '''' + FILTER(i) + '''  ' ;                                     
          else ;                                                                                    
             for i = 1 to (e - 1) ;                                                                 
                pString = pSTRING + '''' + FILTER(i) + ''', ' ;                                     
             endfor ;                                                                               
             for i to 35 ;                                                                          
                pString = pSTRING + ''''', ' ;                                                      
             endfor ;                                                                               
                pString = pSTRING + '''''' ;                                                        
          endif ;                                                                                   
                                                                                                    
         STRING02 =                                                                                 
             'CALL ANALYZE_PLAN_CACHE ' +                                                           
                  '(''0200000000011'', ' +                                                          
                  '''' + LIBHL + '''' + ', ' +                                                      
                  '''' +  FILEHL + '''' + ', ' +                                                    
                   'X'''', ' + pSTRING + ')' ;                                                      
         EXEC SQL    SET SCHEMA DEFAULT ;                                                           
         EXEC SQL    PREPARE CALL_HL FROM :STRING02 ;                                               
         EXEC SQL    EXECUTE CALL_HL ;                                                              
                                                                                                    
         cSTRING  =                                                                                 
             'SELECT QQJFLD FROM ' + FILEHL +                                                       
                    ' ORDER BY TOTAL_TIMES_RUN DESC';                                               
         EXEC SQL    SET SCHEMA :LIBHL ;                                                            
         EXEC SQL    PREPARE HL_STRING FROM :cSTRING ;                                              
         EXEC SQL    DECLARE HL CURSOR FOR HL_STRING ;                                              
         EXEC SQL    OPEN HL ;                                                                      
                                                                                                    
         DoU %Subst(SQLSTATE:1:2) = '02' ;                                                          
           EXEC SQL    FETCH NEXT FROM HL INTO :KEYV ;                                              
                                                                                                    
             If %Subst(SQLSTATE:1:2) <> '00' ;                                                      
                leave ;                                                                             
             EndIf ;                                                                                
                                                                                                    
           KEYP += KEYV ;                                                                           
         EndDo ;                                                                                    
                                                                                                    
         KEYP = 'UJ' + KEYP ;                                                                       
         STRING04 =                                                                                 
             'CALL ANALYZE_PLAN_CACHE ' +                                                           
                  '(''0400000000011'', ' +                                                          
                  '''' + LIBLL + '''' + ', ' +                                                      
                  '''' +  FILELL + '''' + ', ' +                                                    
                    '?' + ', ' +                                                                    
                    pSTRING + ')' ;                                                                 
         EXEC SQL    SET SCHEMA DEFAULT ;                                                           
         EXEC SQL    PREPARE CALL_LL FROM :STRING04 ;                                               
         EXEC SQL    EXECUTE CALL_LL USING :KEYP;                                                   
         EXEC SQL    CLOSE HL ;                                                                     
                                                                                                    
         *inLR = *on ;                                                                              
         Return ;                                                                                   
                                                                                                    
      /End-Free                                                                                     
      *                                                                                             
      *  SELECT * FROM (PCHL H LEFT OUTER JOIN PCLL L                                                     
      *                 ON SUBSTR(L.QQJFLD,35,8) = SUBSTR(H.QQJFLD,35,8) )                          
      *           ORDER BY H.MOST_EXPENSIVE_TIME DESC                                               
      *                                                                                             

プログラミング Tips 「後続ブランクを取り除く」

各配列の要素内に、プラン・キャッシュの内容をどういう基準で取得するかのオプションを文字列で指定する仕様になっています。

このプログラムでは、そのオプションをまとめたものを pString という文字フィールドにまとめて動的 SQL の一部としています。

元のプログラムでは、その際に余計な後続ブランクを取り除くために↓のように毎回 %trimr を使用していました。

         e = %lookup(*BLANK : FILTER) ;                                                             
         if e = 0 ;                                                                                 
             for i = 1 to (%elem(FILTER) - 1) ;                                                     
                pString = %trimr(pSTRING) + '''' + %trimR(FILTER(i)) + ''', ' ;                     
             endfor ;                                                                               
                pString = %trimr(pSTRING) + '''' + %trimR(FILTER(i)) + '''  ' ;                     
          else ;                                                                                    
             for i = 1 to (e - 1) ;                                                                 
                pString = %trimr(pSTRING) + '''' + %trimR(FILTER(i)) + ''', ' ;                     
             endfor ;                                                                               
             for i to 35 ;                                                                          
                pString = %trimr(pSTRING) + ''''', ' ;                                              
             endfor ;                                                                               
                pString = %trimr(pSTRING) + '''''' ;                                                
          endif ;                         

実は、同じことが pString フィールドと FILTER 配列(の要素)を VARYING と定義することで、

     D pSTRING         S          32700A   VARYING                                                  
     D FILTER          S           1000A   VARYING DIM(36)                                          

↓のように、できるんですね。

         e = %lookup(*BLANK : FILTER) ;                                                             
         if e = 0 ;                                                                                 
             for i = 1 to (%elem(FILTER) - 1) ;                                                     
                pString = pSTRING + '''' + FILTER(i) + ''', ' ;                                     
             endfor ;                                                                               
                pString = pSTRING + '''' + FILTER(i) + '''  ' ;                                     
          else ;                                                                                    
             for i = 1 to (e - 1) ;                                                                 
                pString = pSTRING + '''' + FILTER(i) + ''', ' ;                                     
             endfor ;                                                                               
             for i to 35 ;                                                                          
                pString = pSTRING + ''''', ' ;                                                      
             endfor ;                                                                               
                pString = pSTRING + '''''' ;                                                        
          endif ; 

%trimr をつけると、そのフィールドの右一杯からブランクでない文字まで各バイトをテストして取り除いていくわけで、細かいことですがそれなりにパフォーマンスへの影響はあります。
それを可変長フィールドにすることで、より負荷の少ない方法で同じことができる、ということなんですね。
(これくらいの回数ではたいしたことはないと思いますが、もっと大量に行う場合は効いてくる可能性がありますね。まぁ習慣にしておけば、ということで)

見た目もこっちの方がすっきりです。

ただ、
「この VARYING というキーワードは"可変長"だということを表しているだけで、「自動的に右ブランクを取り除く」ということを意味するわけではない」ということは注意しておいた方がいいでしょう。

VARYING を指定した文字を VARYING を指定した文字に接続する場合、"可変長"なので接続される側(つまり左側)の文字は右ブランクが取り除かれた長さの状態になる、ということです。
接続する/される文字が固定長だった場合は当然そのブランクは %trimr を行わないと右側にフィールド長までに達する分のブランクが入ります。

自動的に %trimr が行われる、ということではありませんので、そのあたりは気をつけてください。


静的SQL バージョン

↑の例はストアド・プロシージャへの CALL 文を組み立てて、動的SQL で実行していますが、これをストアド・プロシージャの直接 CALL、言わば静的SQL に書き換えたものが↓になります。

      *                                                                                             
      *  CRTSQLRPGI OBJ(LIB/PGM) SRCFILE(LIB/QRPGLESRC) COMMIT(*NONE)                
      *  SQLCURRULE(*STD) DBGVIEW(*SOURCE)                                                          
      *                                                                                             
     H DFTACTGRP(*NO)                                                                               
      *                                                                                             
     D KEYP            S                   SQLTYPE(VarBinary:32700)                                 
     D KEYV            S                   SQLTYPE(VarBinary:32700)                                 
      *                                                                                             
     D i               S              5i 0                                                          
     D e               S              5i 0                                                          
      *                                                                                             
     D LIBHL           S             10A   VARYING                                                  
     D FILEHL          S             10A   VARYING                                                  
     D LIBLL           S             10A   VARYING                                                  
     D FILELL          S             10A   VARYING                                                  
      *                                                                                             
     D STRING          S            500A   INZ('') VARYING                                          
      *                                                                                             
     D PARM            S           1000A   INZ(x'00') VARYING DIM(36)                               
      *                                                                                             
     D pfilter         S               *                                                            
     D FILTER          DS                  Based(pfilter)                                           
     D  FILTER1                    1000A   VARYING                                                  
     D  FILTER2                    1000A   VARYING                                                  
     D  FILTER3                    1000A   VARYING                                                  
     D  FILTER4                    1000A   VARYING                                                  
     D  FILTER5                    1000A   VARYING                                                  
     D  FILTER6                    1000A   VARYING                                                  
     D  FILTER7                    1000A   VARYING                                                  
     D  FILTER8                    1000A   VARYING                                                  
     D  FILTER9                    1000A   VARYING                                                  
     D  FILTER10                   1000A   VARYING                                                  
     D  FILTER11                   1000A   VARYING                                                  
     D  FILTER12                   1000A   VARYING                                                  
     D  FILTER13                   1000A   VARYING                                                  
     D  FILTER14                   1000A   VARYING                                                  
     D  FILTER15                   1000A   VARYING                                                  
     D  FILTER16                   1000A   VARYING                                                  
     D  FILTER17                   1000A   VARYING                                                  
     D  FILTER18                   1000A   VARYING                                                  
     D  FILTER19                   1000A   VARYING                                                  
     D  FILTER20                   1000A   VARYING                                                  
     D  FILTER21                   1000A   VARYING                                                  
     D  FILTER22                   1000A   VARYING                                                  
     D  FILTER23                   1000A   VARYING                                                  
     D  FILTER24                   1000A   VARYING                                                  
     D  FILTER25                   1000A   VARYING                                                  
     D  FILTER26                   1000A   VARYING                                                  
     D  FILTER27                   1000A   VARYING                                                  
     D  FILTER28                   1000A   VARYING                                                  
     D  FILTER29                   1000A   VARYING                                                  
     D  FILTER30                   1000A   VARYING                                                  
     D  FILTER31                   1000A   VARYING                                                  
     D  FILTER32                   1000A   VARYING                                                  
     D  FILTER33                   1000A   VARYING                                                  
     D  FILTER34                   1000A   VARYING                                                  
     D  FILTER35                   1000A   VARYING                                                  
     D  FILTER36                   1000A   VARYING                                                  
      *                                                                                             
      /Free                                                                                         
                                                                                                    
         LIBHL = 'QGPL' ;                                                                           
         FILEHL = 'PCHL' ;                                                                          
         LIBLL = 'QGPL' ;                                                                           
         FILELL = 'PCLL' ;                                                                          
                                                                                                    
         // PARM(1) = 'IA' ;                                                                        
         // PARM(1) = 'MT100';                                                                      
         // PARM(1) = 'WR0021';                                                                     
         // PARM(1) = 'CU';                                                                         
         // PARM(1) = 'MR40';                                                                       
         // PARM(1) = 'ST2009-9-16-12.00.00.00';  X (Full time-format required)                                                 
         // PARM(1) = 'LIQEOL';                                                                  
         // PARM(2) = 'FITOKMSP'; X ('FI' without 'LI' seems not allowed)                                  
         PARM(1) = 'TE%ORDER BY%';                                                                  
         PARM(2) = 'ST2009-09-16-18.00.38.000000';                                                  
                                                                                                    
         pfilter = %addr(PARM) ;                                                                    
                                                                                                    
         EXEC SQL    SET SCHEMA DEFAULT ;                                                           
         EXEC SQL                                                                                   
              CALL ANALYZE_PLAN_CACHE                                                               
                   ('0200000000011',                                                                
                    :LIBHL ,                                                                        
                    :FILEHL ,                                                                       
                    X'',                                                                            
                    :FILTER1,                                                                       
                    :FILTER2,                                                                       
                    :FILTER3,                                                                       
                    :FILTER4,                                                                       
                    :FILTER5,                                                                       
                    :FILTER6,                                                                       
                    :FILTER7,                                                                       
                    :FILTER8,                                                                       
                    :FILTER9,                                                                       
                    :FILTER10,                                                                      
                    :FILTER11,                                                                      
                    :FILTER12,                                                                      
                    :FILTER13,                                                                      
                    :FILTER14,                                                                      
                    :FILTER15,                                                                      
                    :FILTER16,                                                                      
                    :FILTER17,                                                                      
                    :FILTER18,                                                                      
                    :FILTER19,                                                                      
                    :FILTER20,                                                                      
                    :FILTER21,                                                                      
                    :FILTER22,                                                                      
                    :FILTER23,                                                                      
                    :FILTER24,                                                                      
                    :FILTER25,                                                                      
                    :FILTER26,                                                                      
                    :FILTER27,                                                                      
                    :FILTER28,                                                                      
                    :FILTER29,                                                                      
                    :FILTER30,                                                                      
                    :FILTER31,                                                                      
                    :FILTER32,                                                                      
                    :FILTER33,                                                                      
                    :FILTER34,                                                                      
                    :FILTER35,                                                                      
                    :FILTER36) ;                                                                    
                                                                                                    
         STRING  =                                                                                  
             'SELECT QQJFLD FROM ' + FILEHL +                                                       
                    ' ORDER BY TOTAL_TIMES_RUN DESC';                                               
         EXEC SQL    SET SCHEMA :LIBHL ;                                                            
         EXEC SQL    PREPARE HL_STRING FROM :STRING ;                                               
         EXEC SQL    DECLARE HL CURSOR FOR HL_STRING ;                                              
         EXEC SQL    OPEN HL ;                                                                      
                                                                                                    
         DoU %Subst(SQLSTATE:1:2) = '02' ;                                                          
           EXEC SQL    FETCH NEXT FROM HL INTO :KEYV ;                                              
                                                                                                    
             If %Subst(SQLSTATE:1:2) <> '00' ;                                                      
                leave ;                                                                             
             EndIf ;                                                                                
                                                                                                    
           KEYP += KEYV ;                                                                           
         EndDo ;                                                                                    
                                                                                                    
         KEYP = 'UJ' + KEYP ;                                                                       
         EXEC SQL    SET SCHEMA DEFAULT ;                                                           
         EXEC SQL                                                                                   
              CALL ANALYZE_PLAN_CACHE                                                               
                   ('0400000000011',                                                                
                    :LIBLL ,                                                                        
                    :FILELL ,                                                                       
                    :KEYP,                                                                          
                    :FILTER1,                                                                       
                    :FILTER2,                                                                       
                    :FILTER3,                                                                       
                    :FILTER4,                                                                       
                    :FILTER5,                                                                       
                    :FILTER6,                                                                       
                    :FILTER7,                                                                       
                    :FILTER8,                                                                       
                    :FILTER9,                                                                       
                    :FILTER10,                                                                      
                    :FILTER11,                                                                      
                    :FILTER12,                                                                      
                    :FILTER13,                                                                      
                    :FILTER14,                                                                      
                    :FILTER15,                                                                      
                    :FILTER16,                                                                      
                    :FILTER17,                                                                      
                    :FILTER18,                                                                      
                    :FILTER19,                                                                      
                    :FILTER20,                                                                      
                    :FILTER21,                                                                      
                    :FILTER22,                                                                      
                    :FILTER23,                                                                      
                    :FILTER24,                                                                      
                    :FILTER25,                                                                      
                    :FILTER26,                                                                      
                    :FILTER27,                                                                      
                    :FILTER28,                                                                      
                    :FILTER29,                                                                      
                    :FILTER30,                                                                      
                    :FILTER31,                                                                      
                    :FILTER32,                                                                      
                    :FILTER33,                                                                      
                    :FILTER34,                                                                      
                    :FILTER35,                                                                      
                    :FILTER36) ;                                                                    
         EXEC SQL    CLOSE HL ;                                                                     
                                                                                                    
         *inLR = *on ;                                                                              
         Return ;                                                                                   
                                                                                                    
      /End-Free                                                                                     
      *                                                                                             
      *  SELECT * FROM (PCHL H LEFT OUTER JOIN PCLL L                                                     
      *                 ON SUBSTR(L.QQJFLD,35,8) = SUBSTR(H.QQJFLD,35,8) )                          
      *           ORDER BY H.MOST_EXPENSIVE_TIME DESC                                               
      *                                                                                             

プログラミング Tips 「配列の要素をそのままデータストラクチャの各サブフィールドとして読み出す」

このストアド・プロシージャは、オプションとして 36個までいろんな選択条件を指定できるようになっています。

いくつ指定されるかをあらかじめ知ることはできないので、それこそ全部 NULL に初期化した配列に順番にオプションを指定していきたいところなのですが、埋め込みSQL のホスト変数に配列の要素を指定することは(少なくとも今現在のV5R4/V6R1では)できません。

そこで、今回は 36個のホスト変数を 1つのデータストラクチャとして設定し、別に定義した同様の配列とポインタでアタマを合わせてしまう、という手段をとりました。
配列の要素をそのままデータストラクチャの各サブフィールドとして読み出すことができる、というわけです。

     D PARM            S           1000A   INZ(x'00') VARYING DIM(36)                               
      *                                                                                             
     D pfilter         S               *                                                            
     D FILTER          DS                  Based(pfilter)                                           
     D  FILTER1                    1000A   VARYING                                                  
     D  FILTER2                    1000A   VARYING                                                  
     D  FILTER3                    1000A   VARYING                                                  
     D  ...

FILTER というデータストラクチャは pfilter というポインタによって起点を決められています。

配列にデータを入れて、

         PARM(1) = 'TE%ORDER BY%';                                                                  
         PARM(2) = 'ST2009-09-16-18.00.38.000000';                                                  

配列の起点のアドレスを pfilter というポインタにセットすれば、

         pfilter = %addr(PARM) ;    

そのまま FILTER データストラクチャは配列 PARM と同じ内容になっているわけです。

ちなみに、の Tips を使って双方ともに VARYING が指定されているので、無駄な後続ブランクはこちらのケースでも取り除かれています。

[Top Pageに戻る]

Ads by TOK2