midrange.com code scratchpad
Name:
RTVSQLDUMP - Dan Bale
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
01/20/2017 02:45:26 am
IP:
Logged
Description:
This utility takes an ugly DSPSYSOBJ dump output of SQL session history and formats it into a readable format. This program could be vastly improved upon. It was developed when I was in a big hurry to get something I needed, 'nuff said. If your DMPSYSOBJ output is less than 345 pages, this program will do the job. This was compiled at v7r1. If your compiler chokes at the size of the data structure, you'll have to decrease it accordingly to get this to work.
Code:
  1.       *  Problem:  SQL session history "disappears" and you're missing a lot of good "stuff".   
  2.       *  Solution: Run a DMPSYSOBJ on a special "internal space" that keeps this history,       
  3.       *            and parse the ugly printed output into usable statements.                    
  4.       *  Details:  Suggested by Elvis Budimlic of Centerfield Technologies, in their            
  5.       *            February 2006 newsletter (pg 5).                                             
  6.       *            DMPSYSOBJ OBJ(ISQLST<usrprf>*) CONTEXT(QRECOVERY) TYPE(19) SUBTYPE(EE)       
  7.       *            where <usrprf> is your user profile name.                                    
  8.       *            Copy this spool file to $DMPSYSOBJ, a 132-byte record length flatfile,       
  9.       *            and process that file here to produce readable printed output.               
  10.      h       Option( *SrcStmt : *NoDebugIO )                                                    
  11.      f$DmpSysObjif   f  132        disk                                                         
  12.      fqPrint    o    f  132        printer OflInd( *inOF )                                      
  13.                                                                                                 
  14.       *  There are up 58 "Dump32" records per page, so 20000 should be good for about 344 pages.
  15.      d                 ds                                                                       
  16.      d  Dump32                 1 640128    Dim( 20004 )                                         
  17.      d  Dump82                17 640108    Dim( 7806 )                                          
  18.      d  d              s              9  0                                                      
  19.      d  d82High        s              9  0                                                      
  20.      d Capture         s               n   Inz( *off )                                          
  21.                                                                                  
  22.      i$DmpSysObjns                                                               
  23.      i                                  1    6  SPACEdash                        
  24.      i                                  1    5  NAMEdash                         
  25.      i                                  1   10  POINTERSdash                     
  26.      i                                 87   87  Star1                            
  27.      i                                 88  119  pDump32                          
  28.      i                                120  120  Star2                            
  29.      i                                  1  132  Line132                          
  30.      i                                122  125  Page#                            
  31.                                                                                  
  32.       /free                                                                      
  33.          *inLR = *on ;                                                           
  34.          Dou  %eof( $DmpSysObj ) ;                                               
  35.            Read  $DmpSysObj ;                                                    
  36.            If  not %eof( $DmpSysObj ) ;                                          
  37.                                                                                  
  38.              If  POINTERSdash = '.POINTERS-' ;                                   
  39.                Capture = *off ;                                                  
  40.                Exsr ConvertDump ;                                                
  41.              Endif ;                                                             
  42.        // The stuff between "SPACE-" & ".POINTERS-" is the stuff we want to parse
  43.        //  (Capture is *on)                                                      
  44.              If  Capture and Star1 = '*' and Star2 = '*' ;                       
  45.                d += 1 ;                       
  46.                Dump32( d ) = pDump32 ;        
  47.              Endif ;                          
  48.                                               
  49.              If  SPACEdash = 'SPACE-' ;       
  50.                Capture = *on ;                
  51.              Endif ;                          
  52.                                               
  53.            Endif ;                            
  54.          Enddo ;                              
  55.                                               
  56.          Begsr ConvertDump ;                  
  57.            *inOF = *On ;                      
  58.            d82High  = ( d * 32 /  82 ) + 1 ;                              
  59.            For d = 1 to d82High ;                                         
  60.              *in82 = *on ;                                                
  61.              Except Print82 ;                                             
  62.              *in82 = *off ;                                               
  63.            Endfor ;                                                       
  64.                                                                           
  65.            Clear  Dump32 ;                                                
  66.            Clear  d ;                                                     
  67.          Endsr ;                                                          
  68.                                                                           
  69.       /end-free                                                           
  70.                                                                           
  71.      oqPrint    ef           Print82        1                             
  72.      o                       Dump82( d )                                  
© 2004-2019 by midrange.com generated in 0.007s valid xhtml & css