Things to know about %UpdateStats Meta-SQL

 

Things to know about %UpdateStats Meta-SQL

PeopleSoft uses shared temporary tables, dedicated temporary tables, or both in batch processes. These temporary tables will have few or no rows at the beginning of the process; at the end of the process, these tables will again have few or no rows. Keeping the statistics updated for these tables is somewhat challenging. The life cycle of a temporary table is as follows:

•        All rows are automatically truncated (if dedicated) or deleted (if shared) at the beginning of an AE execution.
•        Rows are populated.
•        All rows are truncated (if dedicated) or deleted (if shared) at the end of an AE execution if the programmer has requested. This action is not performed automatically.

Beginning with PeopleSoft 8, an AE program can use the meta-SQL “%UpdateStats” after rows are populated to a temporary table. Doing so would update statistics in the temporary table before the table is used in the SQL statements that follow.

Note: Commit is required before executing the %UpdateStats statement. Because of the implicit commit feature that Oracle uses when performing DDL (statistics gathering is considered DDL), the AE will ignore the %UpdateStats command after any uncommitted changes. Allowing the implicit commit may affect the restart capability of the program.

Example: Command in SQL Step of an Application Engine program:

%UpdateStats(%Table(PS_PERSON_TMP))

This meta-SQL starting with Peopletools 8.48 will issue the Oracle database command at runtime:

DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=>[TBNAME], estimate_percent=>1, method_opt=> 'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);

Turning Off %UpdateStats

Note: This change affects the entire process scheduler; therefore, you should not turn this feature off.

Having the update statistics at runtime incurs some overhead. If this feature is to be turned off, Please check DBFLAG parameter in psprcs.cfg file at process Scheduler

Here are the details on the DBFLAGS setting:
DbFlags
The following values are valid for the DbFlags parameter:

Value Description
0 Enable the %UpdateStats meta-SQL construct.
1 Disable the %UpdateStats meta-SQL construct.
2 Ignore the Truncate command for DB2 LUW. Use Delete instead.

Note: By using this option, when %Truncatetable is encountered in the app engine program, it will resolve to a DELETE, rather than an IMPORT FROM /dev/null. This may have a performance impact since the DELETE is normally slower than the IMPORT FROM /dev/null.

4 Disable a secondary database connection (used with the GetNextNumberWithGapsCommit PeopleCode function). This prevents the creation of a secondary database connection, bundling all SQL into a single unit of work. Without the additional database connection, the database row lock is held for a longer time, reducing concurrency in a multiple-user environment.

Note: Analytic instance processing requires a secondary database connection, so if you're using analytic servers, ensure that this value is not set.

8 Disable a persistent second database connection (used with the GetNextNumberWithGapsCommit PeopleCode function). This creates a second database connection in each GetNextNumberWithGapsCommit call, then immediately closes the second connection. This keeps the number of database connections to a minimum but requires each call to create a new database connection on demand.

Note: The performance impact of making a new database connection is significant, especially in high volume user production environments. Don't use this setting without carefully considering its effect.

DbFlags uses a bit mask so that you can specify one or more of these values. You set this parameter to the total of the values that you want to apply. For example, to disable %UpdateStats and ignore the Truncate command, set DbFlags to 3 (setting bits one and two). The default is value is 1.

Running %UpdateStats using PeopleCode

Can we place %UpdateStats meta-SQL inside the PeopleCode action?

Yes, here is one Example:

&recname = "%UpdateStats("|&recname|")";

SQLExec(&recname);