%UpdateStats meta-SQL element

Syntax

%UpdateStats(record name ,[HIGH/LOW])

For example,

%UpdateStats(PO_WRK1)

The default is LOW.

Description

Use the %UpdateStats construct to generate a platform-dependent SQL statement that updates the system catalog tables used by the database optimizer in choosing optimal query plans. Use this construct after your program has inserted large amounts of data into a temporary table that will be deleted before the end of the program run. This construct saves you from having to use dummy seed data for the temporary table and having to update statistics manually.

Notes About %UpdateStats

For databases that either run an implicit commit for %UpdateStats or require a commit before or after this meta-SQL, Application Engine skips %UpdateStats in the following circumstances:

A commit is not allowed, as in an Application Engine program called from PeopleCode.
The program issues a non-select SQL statement since the last commit occurred.

In such a situation, data is likely to have changed.
You are deferring commits in a Select/Fetch loop in a restartable program.

Application Engine skips %UpdateStats even if the previous condition is false.

The following table shows how the %UpdateStats construct is resolved by the supported database systems:

Database Function	Behavior
MSS %UpdateStats	Specifying LOW produces the statement `UPDATE STATISTICS tablename` Specifying HIGH produces the statement `UPDATE STATISTICS tablename WITH FULLSCAN`
Oracle %UpdateStats	Oracle uses DDL templates (in PSDDLMODEL) to determine SQL statements for %UpdateStats. Use DDLORA.DMS to change. Specifying LOW produces the statement `execute DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'PT861GA', tabname=>'PSSTATUS', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,NO_INVALIDATE=>FALSE, CASCADE=>TRUE)` You can use the LOW option SQLs if no row exists for the HIGH option.
DB2 UNIX %UpdateStats	In DB2 UNIX, %UpdateStats is performed by issuing sqlustat() calls that are equivalent to SQL statements. The sqlustat() is an internal DB2 API call function rather than an SQL command. Specifying LOW is equivalent to issuing the statement `RUNSTATS ON TABLE tablename AND INDEXES ALL` Specifying HIGH is equivalent to issuing the statement `RUNSTATS ON TABLE tablename WITH DISTRIBUTION AND⇒ DETAILED INDEXES ALL` Note: You cannot view the sqlustat() calls nor the RUNSTATS statement in the SQL trace.
Db2 for z/OS %UpdateStats	Uses a DDL model template (in PSDDLMODEL) to format a control statement for the Db2 for z/OS Runstats utility. See the product documentation for PeopleSoft 9.2 Application Installation for DB2 for z/OS for more details on using %UpdateStats. Specifying LOW produces the statement `RUNSTATS TABLESPACE [DBNAME].[TBSPCNAME] TABLE([⇒ DBNAME].[TABLE]) SAMPLE 25 [INDEXLIST] REPORT NO⇒ SHRLEVEL CHANGE UPDATE ACCESSPATH` Specifying HIGH produces the statement `RUNSTATS TABLESPACE [DBNAME].[TBSPCNAME] TABLE([⇒ DBNAME].[TABLE]) [INDEXLIST] REPORT NO SHRLEVEL⇒ CHANGE UPDATE ACCESSPATH`

Using %DeleteStats for Oracle Database

To delete meta-SQLs in Oracle database, use %DeleteStats().

%DeleteStats(table_name)

%UpdateStats Database Considerations

The following table lists potential issues that you might encounter when using %UpdateStats:

Database	Consideration
Oracle	Oracle has an implicit commit after the %UpdateStats statement executes.
Db2 for z/OS	For Db2 for z/OS, %UpdateStats requires IBM stored procedure DSNUTILS running in an authorized Work Load Manager Application Environment. It is also highly recommended that individual tables intended to be a target of the %UpdateStats function are segregated to their own tablespaces. Refer to the following documents for more details on using %UpdateStats: PeopleSoft 9.2 Application Installation for DB2 for z/OS Getting Started with PeopleTools: Understanding Administration Tools Note: You can trace information messages from the Runstats command on DB2 for z/OS executed as a result of issuing %UpdateStats. To enable this trace, select the SQL Informational Trace check box on the Configuration Manager – Trace page.
All	%UpdateStats consumes a large amount of time and database resources if run against very large tables. Therefore, analyze permanent data tables outside of application programs. Also, if temporary tables are likely to grow very large during a batch run, run the batch program only with %UpdateStats enabled to seed the statistics data or when the data composition changes dramatically.

Database

Consideration

Oracle

Oracle has an implicit commit after the %UpdateStats statement executes.

Db2 for z/OS

For Db2 for z/OS, %UpdateStats requires IBM stored procedure DSNUTILS running in an authorized Work Load Manager Application Environment. It is also highly recommended that individual tables intended to be a target of the %UpdateStats function are segregated to their own tablespaces. Refer to the following documents for more details on using %UpdateStats: PeopleSoft 9.2 Application Installation for DB2 for z/OS

Getting Started with PeopleTools: Understanding Administration Tools

Note: You can trace information messages from the Runstats command on DB2 for z/OS executed as a result of issuing %UpdateStats. To enable this trace, select the SQL Informational Trace check box on the Configuration Manager – Trace page.

All

%UpdateStats consumes a large amount of time and database resources if run against very large tables. Therefore, analyze permanent data tables outside of application programs. Also, if temporary tables are likely to grow very large during a batch run, run the batch program only with %UpdateStats enabled to seed the statistics data or when the data composition changes dramatically.

Disabling %UpdateStats

You can disable %UpdateStats in the following ways:

Include the following parameter on the command line when running an Application Engine program:
```
-DBFLAGS  1 
```
Change the Dbflags=0 parameter in the PeopleSoft Process Scheduler configuration file (or PSADMIN) to Dbflags=1.

Using %UpdateStats With COBOL

You can use the %UpdateStats construct from SQL embedded in COBOL programs. Use this syntax:

%UpdateStats(tablename)

When you issue this construct from PeopleTools, the parameter is record name.