Updating Statistics

This section provides an overview of %UpdateStats and discusses factors related to updating statistics.

The meta-SQL %UpdateStats was introduced for PeopleSoft environments to allow an Application Engine and COBOL programs to update statistics in the Db2 catalog. Updating the statistics in the Db2 catalog is particularly helpful for the overall performance of Application Engine programs that heavily use PeopleSoft temporary tables.

Often, these tables are delivered empty or the Application Engine program contains code to purge the content of these tables prior to termination. So even if you periodically perform a REORG or RUNSTATS against all the tablespaces in a PeopleSoft database, the Db2 catalog does not reflect accurate statistical information on these temporary tables. The %UpdateStats meta-SQL was specifically written to get around this issue.

To fully implement the %UpdateStats functionality in Db2 in your environment, you need to be aware of the following key items:

  • Setting up the Db2 z/OS stored procedure: DSNUTILS.

  • Updating the PeopleSoft System Tables with Database and Tablespace Information

  • Activating the %UpdateStats in Application Engine.

DSNUTILS is required to enable the %UpdateStats meta-SQL function on Db2 z/OS.

The DSNUTILS procedure has been integrated with Application Engine specifically to invoke the Runstats utility; hence DSNUTILS is required if you intend to use the %UpdateStats meta-SQL function.

Refer to your IBM manuals for more information on enabling the DSNUTILS stored procedure for Db2 z/OS.

The %UpdateStats meta SQL function can be enabled and disabled through the Process Scheduler configuration on non-z/OS platforms for both Application Engine and COBOL. When running COBOL directly on z/OS with your own JCL, enable %UpdateStats by setting PARM 6 to Y in the PARMFILE instream data set. If the command is disabled, the Application Engine and COBOL programs ignore any %UpdateStats coded within the program, and the Runstats utility will not execute.

Note: The %UpdateStats meta-SQL is enabled by default for Microsoft Windows Process Scheduler servers.

See PeopleSoft 9.2 Application Installation for DB2 for z/OS.

When issuing %UpdateStats meta-SQL in your program, you specify the temporary table on which you intend to have the statistics updated. Database and tablespace name values are retrieved from the PeopleTools meta data. Therefore, it is imperative that these tables reflect accurate information as contained in the Db2 catalog.

Running the following SQRs ensures that the PeopleTools tables are in sync with the Db2 system catalog.

SQR Program

Purpose

SETSPACE.SQR

Extracts the database/tablespace values from the SYSIBM.SYSTABLES and updates the PSRECTBLSPC table with this information. The SQR also inserts valid database/tablespace combinations into PSTBLSPCCAT

SETTMPIN.SQR

Inserts Temporary Table instance information into PSRECTBLSPC to provide values necessary for processing Runstats on the instance.

Note: DB2 RUNSTATS is run at the tablespace level. From a performance perspective, it is recommended that you move tables that are the object of the %UpdateStats to a separate tablespace.

It is not mandatory to run SETSPACE or SETTMPIN to use the %UpdateStats meta-SQL function because %UpdateStats retrieves the correct database and tablespace name directly from the Db2 catalog. You should, however, still run SETSPACE and SETTMPIN to keep the PeopleTools metadata synchronized with the Db2 Catalog.

See IBM's Installation Guide for Db2 for z/OS.

%UpdateStats can be disabled by setting the DbFlags application server domain parameter.

This parameter has two values that apply to %UpdateStats:

  • 0 – enable %UpdateStats.

  • 1 – disable %UpdateStats.

%UpdateStats is enabled by default for Microsoft Windows Process Scheduler servers.

Enabling or Disabling %UpdateStats for Batch Processing

To fully enable %UpdateStats for COBOL on z/OS:

  1. Set PARM 6 to Y in the PARMFILE instream data set of your JCL.

  2. Modify the program PSPTSQLRT.

    Note: Several lines are delivered commented out in the program.

    --
* ELSE
* PERFORM VX000-EXECUTE-RUNSTATS
END-IF
--.
--.
* EXEC SQL
* CALL DSNUTILS(:DSNUTIL-WK.UID, :DSNUTIL-WK.RESTART,
* :DSNUTIL-WK.UTSTMT,
* :RETCODE, :DSNUTIL-WK.UTILITY,
* :DSNUTIL-WK.RECDSN, :DSNUTIL-WK.RECDEVT,
* :DSNUTIL-WK.RECSPACE,
* :DSNUTIL-WK.DISCDSN, :DSNUTIL-WK.DISCDEVT,
* :DSNUTIL-WK.DISCSPACE,
* :DSNUTIL-WK.PNCHDSN, :DSNUTIL-WK.PNCHDEVT,
* :DSNUTIL-WK.PNCHSPACE,
* :DSNUTIL-WK.COPYDSN1, :DSNUTIL-WK.COPYDEVT1,
* :DSNUTIL-WK.COPYSPACE1,
* :DSNUTIL-WK.COPYDSN2, :DSNUTIL-WK.COPYDEVT2,
* :DSNUTIL-WK.COPYSPACE2,
* :DSNUTIL-WK.RCPYDSN1, :DSNUTIL-WK.RCPYDEVT1,
* :DSNUTIL-WK.RCPYSPACE1,
* :DSNUTIL-WK.RCPYDSN2, :DSNUTIL-WK.RCPYDEVT2,
* :DSNUTIL-WK.RCPYSPACE2,
* :DSNUTIL-WK.WORKDSN1, :DSNUTIL-WK.WORKDEVT1,
* :DSNUTIL-WK.WORKSPACE1,
* :DSNUTIL-WK.WORKDSN2, :DSNUTIL-WK.WORKDEVT2,
* :DSNUTIL-WK.WORKSPACE2,
* :DSNUTIL-WK.MAPDSN, :DSNUTIL-WK.MAPDEVT,
* :DSNUTIL-WK.MAPSPACE,
* :DSNUTIL-WK.ERRDSN, :DSNUTIL-WK.ERRDEVT,
* :DSNUTIL-WK.ERRSPACE,
* :DSNUTIL-WK.FILTERDSN, :DSNUTIL-WK.FILTERDEVT,
* :DSNUTIL-WK.FILTERSPACE )
* END-EXEC.
--

  3. Uncomment (remove the asterisk at the beginning) the lines noted above so they are activated in the program code.

  4. Compile the program PTPSQLRT by submitting the two JCL members found in $PSHLQ.JCLLIB:

    • PSCOBDA

    • PSCOBDE

  5. Determine whether you want to bind or rebind two Db2 plans for PeopleSoft.

    Add the following line to the Bind Parameter list in the appropriate JCL members noted below, before submitting them:

    PKLIST (DSNUTILS.*)

  6. For first time Binding modify the following two members in $PSHLQ.JCLLIB:

    • BINDAADD

    • BINDEADD

  7. For rebinding an existing plan, modify the following two members in $PSHLQ.JCLLIB:

    • BINDAREP

    • BINDEREP