midrange.com code scratchpad
Name:
Retrieve SQL History
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
01/25/2018 04:35:57 pm
IP:
Logged
Description:
Discussion on this source code can be found in the Midrange-L archives, January 2018, with the subject "Retrieve STRSQL history (was: QRECOVERY library)"

Problem: SQL session history "disappears" and you're missing a lot of "gold".
Also, no way to search history from interactive SQL sessions.
Solution: Run a DMPSYSOBJ on a special "internal space" that keeps this history,
and parse the ugly printed output into usable statements.
Details: Suggested by Elvis Budimlic of Centerfield Technologies, in their
February 2006 newsletter (pg 5).
DMPSYSOBJ OBJ(ISQLST<usrprf>*) CONTEXT(QRECOVERY) TYPE(19) SUBTYPE(EE)
where <usrprf> is your user profile name.
Copy this spool file to $DMPSYSOBJ, a 132-byte record length flatfile, and
process that file in the RPG program to produce readable printed output.
Code:
  1. CLLE source - RTVSQLDUMC:
  2. /*  Problem:  SQL session history "disappears" and you're missing a lot of "gold".        */
  3. /*            Also, no way to search history from interactive SQL sessions.               */
  4. /*  Solution: Run a DMPSYSOBJ on a special "internal space" that keeps this history,      */
  5. /*            and parse the ugly printed output into usable statements.                   */
  6. /*  Details:  Suggested by Elvis Budimlic of Centerfield Technologies, in their           */
  7. /*            February 2006 newsletter (pg 5).                                            */
  8. /*            DMPSYSOBJ OBJ(ISQLST<usrprf>*) CONTEXT(QRECOVERY) TYPE(19) SUBTYPE(EE)      */
  9. /*            where <usrprf> is your user profile name.                                   */
  10. /*            Copy this spool file to $DMPSYSOBJ, a 132-byte record length flatfile, and  */
  11. /*            process that file in the RPG program to produce readable printed output.    */
  12. Pgm   Parm( &p_UserID )                    
  13.                                            
  14.    Dcl        &p_UserID    *char  (  10   )
  15.    Dcl        &UserID      *char  (  10   )
  16.    Dcl        &JobUser     *char  (  10   )
  17.    Dcl        &qDateTime   *char  (  20   )
  18.    Dcl        &qDateYMD    *char  (   6   )
  19.    Dcl        &x           *dec   (   2 0 )
  20.    Dcl        &x@          *char  (   2   )
  21.    Dcl        &MbrName     *char  (  10   )
  22.                                            
  23.    ChgVar     &UserID  &p_UserID           
  24.      MonMsg     MCH3601  Exec( Do )        
  25.        ChgVar     &UserID  '*'               
  26.      Enddo                                   
  27.                                              
  28.    If         ( &UserID *eq '*' )  Then( Do )
  29.      RtvJobA    User( &JobUser )             
  30.      ChgVar     &UserID  &JobUser            
  31.    Enddo                                     
  32.                                              
  33.    DmpSysObj  Obj( 'ISQLST' *cat &UserID *tcat '*' ) + 
  34.                 Context(qRecovery) Type(19) SubType(EE)
  35.    /* output is spool file QPSRVDMP */         
  36.                                                
  37.    CrtPf      DanLib/$DmpSysObj  RcdLen( 132 ) 
  38.      MonMsg     CPF7302  /* Already exists */  
  39.    ClrPfm     DanLib/$DmpSysObj                
  40.    ChgPf      DanLib/$DmpSysObj  Size( *NoMax )
  41.                                                
  42.     CpySplf    qpSrvDmp  ToFile( DanLib/$DmpSysObj )  SplNbr( *Last )
  43.                                               
  44.    Call       RtvSqlDumR                      
  45.                                               
  46.    CrtPf      DanLib/SqlHistDmp  RcdLen( 132 )
  47.      MonMsg     CPF7302  /* Already exists */ 
  48.  
  49.    ChgPf      DanLib/SqlHistDmp  MaxMbrs( *NoMax )  Size( *NoMax )
  50.                                                                   
  51. /* Member name $yymmdd_01 */                                      
  52.    RtvSysVal  qDateTime  &qDateTime   /* YYYYMMDDHHNNSSXXXXXX */  
  53.    ChgVar     &qDateYMD  %sst( &qDateTime 3 6 )                   
  54.    ChgVar     &x  00                                              
  55. AddMbrLoop:                                                       
  56.    ChgVar     &x@  &x                                             
  57.    ChgVar     &MbrName  ( '$' *cat &qDateYMD *cat '_' *cat &x@ )  
  58.    AddPfm     DanLib/SqlHistDmp  &MbrName                         
  59.      MonMsg     CPF7306  Exec( Do )                               
  60.        ChgVar     &x  ( &x + 1 )                                  
  61.        /* Not gonna worry about wrapping from 99 to 00! */        
  62.        Goto       AddMbrLoop                                      
  63.    Enddo                                                          
  64.                                                                   
  65.    CpySplf    qPrint  SplNbr( *Last ) +                           
  66.                 ToFile( DanLib/SqlHistDmp ) ToMbr( *First )       
  67.    CpySplf    qPrint  SplNbr( *Last ) +                           
  68.                 ToFile( DanLib/SqlHistDmp ) ToMbr( &MbrName )     
  69.  
  70.    DspPfm     DanLib/SqlHistDmp  &MbrName
  71.                                          
  72. Endpgm                                   
  73.  
  74.  
  75.  
  76. CMD source - RTVSQLDUMP:
  77.       CMD        PROMPT('Retrieve SQL Dump')              
  78.       PARM       KWD(USERID) TYPE(*CNAME) LEN(10) DFT(*) +
  79.                    SPCVAL((* *)) +                        
  80.                    PROMPT('User ID')                      
  81.  
  82.  
  83.  
  84. RPGLE source - RTVSQLDUMR
  85.       *  Problem:  SQL session history "disappears" and you're missing a lot of "gold".           
  86.       *            Also, no way to search history from interactive SQL sessions.                  
  87.       *  Solution: Run a DMPSYSOBJ on a special "internal space" that keeps this history,         
  88.       *            and parse the ugly printed output into usable statements.                      
  89.       *  Details:  Suggested by Elvis Budimlic of Centerfield Technologies, in their              
  90.       *            February 2006 newsletter (pg 5).                                               
  91.       *            DMPSYSOBJ OBJ(ISQLST<usrprf>*) CONTEXT(QRECOVERY) TYPE(19) SUBTYPE(EE)         
  92.       *            where <usrprf> is your user profile name.                                      
  93.       *            Copy this spool file to $DMPSYSOBJ, a 132-byte record length flatfile,         
  94.       *            and process that file here to produce readable printed output.                 
  95.                                                                                                   
  96.      h Option( *SrcStmt : *NoDebugIO )                                                            
  97.                                                                                                   
  98.      f$DmpSysObjif   f  132        disk                                                           
  99.      fqPrint    o    f  132        printer OflInd( *inOF )                                        
  100.                                                                                                   
  101.       *  There are up 58 "Dump32" records per page, so 312400 should be good for about 5386 pages.
  102.      d                 ds                               
  103.      d  Dump32                 19996800    Dim( 312400 )
  104.      d  Dump82                179996800    Dim( 121912 )
  105.                                                         
  106.      d  d              s              9  0              
  107.      d  d82High        s              9  0              
  108.      d Capture         s               n   Inz( *off )      
  109.                                                             
  110.      i$DmpSysObjns                                          
  111.      i                                  1    6  SPACEdash   
  112.      i                                  1    5  NAMEdash    
  113.      i                                  1   10  POINTERSdash
  114.      i                                 87   87  Star1       
  115.      i                                 88  119  pDump32     
  116.      i                                120  120  Star2       
  117.      i                                  1  132  Line132     
  118.      i                                122  125  Page#       
  119.                                                             
  120.       /free                                                 
  121.          *inLR = *on ;                                      
  122.          Dou  %eof( $DmpSysObj ) ;                          
  123.            Read  $DmpSysObj ;                               
  124.            If  not %eof( $DmpSysObj ) ;                     
  125.                                                             
  126.              If  POINTERSdash = '.POINTERS-' ;              
  127.                Capture = *off ;                             
  128.                Exsr ConvertDump ;                           
  129.              Endif ;                                        
  130.        // The stuff between "SPACE-" & ".POINTERS-" is the stuff we want to format
  131.        //  (Capture is *on) 
  132.              If  Capture and Star1 = '*' and Star2 = '*' ;
  133.                d += 1 ;                     
  134.                Dump32( d ) = pDump32 ;      
  135.                                             
  136.              Endif ;                        
  137.                                             
  138.              If  SPACEdash = 'SPACE-' ;     
  139.                Capture = *on ;              
  140.              Endif ;                        
  141.                                             
  142.            Endif ;                          
  143.          Enddo ;                            
  144.                                             
  145.                                             
  146.          Begsr ConvertDump ;                
  147.            *inOF = *On ;                    
  148.            d82High  = ( d * 32 /  82 ) + 1 ;
  149.            For d = 1 to d82High ;           
  150.              Except Print82 ;               
  151.            Endfor ;                         
  152.                                             
  153.            Clear  Dump32 ;                  
  154.            Clear  d ;                       
  155.          Endsr ;                             
  156.                                              
  157.       /end-free                              
  158.                                              
  159.      oqPrint    e            Print82        1
  160.      o                       Dump82( d )     
© 2004-2019 by midrange.com generated in 0.005s valid xhtml & css