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 UDB catalog table. Updating the statistics in the DB2 UDB 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 UDB 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 UDB in your environment, you need to be aware of the following key items:

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

  • Installing the database following the Enhanced Installation Path.

  • 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.

The lowest level of granularity for running RUNSTATS on DB2 z/OS is at the tablespace level. The %UpdateStats function processes at the table level. For this reason, it is critical to the success of implementing the %UpdateStats functionality that those temporary tables which are the object of the %UpdateStats meta-SQL are placed in their own, unique tablespaces, rather than a shared tablespace. The performance of the Runstats utility itself can be negatively affected if these tables are not segregated. There is also risk of invalidating the catalog statistics of other objects that reside in the shared tablespace.

To assist with this process, we deliver two installation paths for our System and Demo databases. The “Traditional” installation path combines multiple tables into a single tablespace. The “Enhanced” installation path has segregated the PeopleSoft temporary tables into separate tablespaces.

If you plan to use the %UpdateStats functionality, it is critical that you use the Enhanced Installation path for optimal performance of the %UpdateStats function and the Runstats utility.

Note: The %UpdateStats meta-SQL function is only intended to be used for PeopleSoft temporary tables. It is not intended to be used to update catalog statistics for permanent application or PeopleTools tables.

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 UDB catalog.

Running the following SQRs ensures that the PeopleTools tables are in sync with the DB2 UDB 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 UDB catalog. You should, however, still run SETSPACE and SETTMPIN to keep the PeopleTools metadata synchronized with the DB2 UDB Catalog.

See IBM's Installation Guide for DB2 UDB 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 UDB 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