Database file opens consuming temporary addresses

Diagnosing The Problem

The number one cause of temporary address consumption is a high rate of database file full opens and closes.

Collection services provides one method for investigating the volume and source of full opens. File QAPMJOBOS can be queried to determine the number of full opens requested by a job. Field JBNUS reports the sum total of number of native database (non-SQL) file and SQL cursors which have been full opened. Subtracting the value withing field JBLBO from JBNUS yields the number of native (non-SQL) database full opens.

One easy method of extracting and using the information from file QAPMJOBOS is provided by IBM iDoctor for i Collection Services Investigator, which provides easy to use graphs and drill down to the file open information via the 'Other graphs' folder.

IBM iDoctor for IBM i Pex Analyzer also provides the ability to perform a PEX trace specific to file opens and closes. This trace provides the ability to also determine the callstacks and programs requesting the opens and closes.

Note: Ensure that current list of PEX PTFs have been loaded prior to tracing from the Web site.

For V7R1M0 refer to the following Web site:

http://www.ibm.com/support/docview.wss?uid=nas8N1012020

For V7R2M0 refer to the following Web site:

http://www.ibm.com/support/docview.wss?uid=nas8N1020091

This is the PEX definition that IBM iDoctor for IBM i Pex Analyzer would use to collect the data and produce the reports.

ADDPEXDFN DFN(QDB_OPEN)                                          
          TYPE(*TRACE)                                      
          JOB((*ALL *ALL))                                  
          TASK(*ALL)                                        
          MAXSTG(4000000)                                  
          TRCTYPE(*SLTEVT)                                  
          SLTEVT(*YES)                                      
          MCHINST(*NONE)                                    
          OSEVT((*DBOPEN *NONE *FORMAT2))        

While file opens are a leading source of temporary address consumption, PEX traces also provide another method for determining the consumer of these addresses. An ASM PEX trace can be collected using this PEX definition such as this and reviewed using PRTPEXRPT.

ADDPEXDFN DFN(ASM)                                          
          TYPE(*TRACE)                                      
          JOB((*ALL *ALL))                                  
          TASK(*ALL)                                        
          MAXSTG(4000000)                                  
          TRCTYPE(*SLTEVT)                                  
          SLTEVT(*YES)                                      
          MCHINST(*NONE)                                    
          STGEVT((*CRTSEG *NONE *FORMAT2) (*DLTSEG *NONE *FORMAT2))        

IBM iDoctor for IBM i Pex Analyzer also provides support for excuting the above trace, checking for critical PEX PTFs, and analyzing the results. Here's one approach that might be used to analyze the results using IBM i PEX Analyzer:

Within IBM i PEX Analyzer, run the analysis for 'Run trace details (SMTRMOD-like output)'. Query the resultant file once the analysis finishes. Field SIDTYPE will indicate if the address is 'T'emp or 'P'erm. Field FULLADDR is the address of the create or destroy opreration. And, the OPR field is the 'CRT' - create or 'DST' destroy operation. Database does reuse some temporary addresses, so that has to be taken into consideration. If you find specific rows of interest, note the value in field QRECN and use iDoctor PEX Analyzer to navigate to the service side file QAYPETIDX. Add a filter for the given QRECN, double-click on the row, then select the 'Display callstack' tab.

The above information is meant to describe a general approach.

Note: Software Maintenance is not structured to address questions on performance, consulting or extensive configuration questions. Additional telephone and on-site support services are available to meet these needs (see page 11, Premium Support Service Offerings). For further information about these services please contact your IBM Representative who can help direct you to the person(s) who can discuss your needs.