Updating Statistics

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

The meta-SQL %UpdateStats is introduced in PeopleSoft 8 to allow an Application Engine and COBOL programs to update statistics in the DB2 catalog table. Updating the statistics in the DB2 catalog table 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.

Please 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. 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 both z/OS and Windows Process Scheduler servers.

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

Note: A document from IBM entitled “Enabling the DSNUTILS Stored Procedure for DB2 for OS/390” is available on My Oracle Support. This document outlines the minimum requirements to run Workload Manager in goal mode which is required by the DSNUTILS stored procedure.

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 Windows and z/OS Process Scheduler servers.

Enabling/Disabling %UpdateStats for Batch Processing

To enable/disable %UpdateStats for batch processing:

  1. Initialize the PSADMIN program on Unix System Services to administer the Process Scheduler PSOS390.

  2. Select either Configure a Process Scheduler Server or Edit a Process Scheduler Configuration File.

  3. If you select the first option, set the value for DbFlags to 0 to enable or 1 to disable %UpdateStats.

  4. If you select the second option, locate the related section in the file and change DbFlags to 0 to enable and 1 to disable %UpdateStats.

  5. To fully enable %UpdateStats for COBOL to run on the mainframe (Server PSOS390), you need to modify the program PSPTSQLRT. Note that 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.
--

  6. Uncomment the lines noted above so they are activated in the program code.

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

    • PSCOBDA

    • PSCOBDE

  8. 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.*)

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

    • BINDAADD

    • BINDEADD

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

    • BINDAREP

    • BINDEREP