Administering PeopleSoft Databases on DB2 UDB for z/OS

Appendix: Administering PeopleSoft Databases on DB2 UDB for z/OS

This appendix provides an overview of administration on DB2 UDB for z/OS, and discusses how to:

Monitor batch programs.
Associate PeopleSoft operators with DB2 UDB threads.
Run COBOL.
Administer SQR for z/OS.
Update statistics.
Set the number of temporary tables.
Create temporary tables.

Note. DB2 UDB for z/OS is the official IBM name for the DBMS.

For the sake of brevity, this appendix sometimes refers to DB2 UDB for z/OS as DB2 z/OS.

Understanding DB2 UDB for z/OS Administration

This section discusses:

Database considerations.
Concurrency.

Database Considerations

The section discusses:

Tablespace strategy
Locksize tablespace

Tablespace Strategy

Tablespaces named xxLARGE—where xx is a product identifier, such as HR—contain tables that grow substantially and/or experience high update activity. You should track the growth and extents for tablespaces and indexes, as well as monitor for page splits in the indexes.

Each of the tables in xxLARGE is a candidate for partitioning or for a separate tablespace. Tables defined in tablespaces other than xxLARGE are relatively stable and can be defined in shared tablespaces with little, if any, freespace.

As a general rule of thumb, the xxLARGE tablespaces grow substantially large with application data and contain the largest tables in your database. From a PeopleTools perspective, there are several delivered tablespaces that may grow in size. For example, tablespace PTTLRG contains PeopleTools tables (XLATTABLE, PSPCMNAME, and others) that my grow large in size. The “Tree” tables are delivered in tablespace PTTREE—tables prefixed with PSTREE%. These tables may grow substantially early on as you add branches and nodes in the Tree Manager, then plateau once the tree structure is fully defined.

Customers with large amounts of data may require that the larger tables be partitioned, and as a result must be moved to their own tablespace. This improves concurrency and also allow DB2 UDB utilities such as backup, reorg, and Runstats to be run in parallel.

With PeopleSoft 8, new tablespaces were introduced for tables requiring row level locking to avoid deadlock and timeout errors. Those tablespaces are: PTLOCK, PTAMSG, PTPRC, PTRPTS, PTAUDIT, , PTPRJWK PTCMSTAR and PSIMGR. Note that PSIMGR and PSIMAGE both require a 32K page size. If redistributing any of the tables delivered in these tablespaces, it is critical for performance to carry over the row level locking attribute and buffer pool assignment for the new tablespace.

Locksize Tablespace

You can avoid reaching lock escalation thresholds by ALTERing tablespaces from LOCKSIZE ANY (or PAGE) to LOCKSIZE TABLESPACE for the duration of batch jobs. This technique also improves batch program performance.

The ALTERed LOCKSIZE specification is effective immediately. Plan rebinds are not needed since PeopleSoft uses dynamic SQL. The simplest way to implement this technique is to ALTER all of the application tablespaces. If that is not desirable, determine the tables accessed in a particular job by examining SQL statements in PS_SQLSTMT_TBL and finding their corresponding tablespaces.

Note. Tablespaces should be ALTERed back to the original value after job completion. Tablespace locks will lock out online users until LOCKSIZE is reset to PAGE or ANY. If online users are active during the time you are running batch jobs, you may not want to ALTER LOCKSIZE to TABLESPACE.

Concurrency

This section discusses:

CursorHold
Isolation levels and CURRENTDATA
RELCURHL

CursorHold

Enabling CURSORHOLD was required in pre 8.0x releases of PeopleTools; however, this is now the default for DB2 Connect (CURSORHOLD=1). For PeopleTools 8 and beyond, the use of Cursor With Hold, or "persistent cursors", with PeopleSoft applications is controlled entirely by PeopleTools. Consequently, there is no reason to use anything other than the IBM default for CURSORHOLD.

Isolation Levels and CURRENTDATA

PeopleSoft batch processes interface to DB2 UDB either through PTPSQLRT (for Cobol and AE), or through SQRPLAN for SQRs. Both of these are bound with the defaults—that is, CS (cursor stability) and CURRENTDATA NO. Using CURRENTDATA NO results in less lock contention in DB2 UDB and potentially reduce deadlock situations. It also provides two extra benefits:

Block fetch is enabled for ambiguous cursors.
DB2 UDB considers parallelism for ambiguous cursors.

RELCURHL

A DB2 z/OS subsystem parameter RELCURHL lets you indicate that you want DB2 UDB to release a data page or row lock after a COMMIT is issued for cursors defined WITH HOLD. This lock is not necessary for maintaining cursor position.

The default for DB2 z/OS is YES. In prior releases, the value was NO, which causes DB2 UDB to hold a data page or row lock for the row on which the cursor is positioned. This lock is not necessary for maintaining cursor position and could cause deadlocks. The PeopleSoft recommendation is Yes to improve concurrency.

Monitoring Batch Programs

This section provides an overview of batch program monitoring tools and discusses how to:

Enable DB2 CLI/ODBC trace.
Enable the PTPSQLRT Mainframe Statistics report.
Enable dynamic explains.
Enable parallelism.
Enable PeopleSoft SQL trace.
Enable SQR monitoring.

Understanding Batch Program Monitoring Tools

This section discusses the utilities provided by PeopleSoft and IBM to monitor and help you tune the performance of PeopleSoft batch programs.

Utility	Description
SQL Trace - Client	This PeopleSoft client utility records the actual SQL statements that PeopleTools and batch COBOL send to the database, along with their relative processing times. This trace can increase response time significantly.
DB2 CLI/ODBC Trace	The DB2 CLI/ODBC trace is a trace provided by IBM that can be very helpful in debugging DB2 Connect problems. For PeopleSoft, this trace can be very useful for tracking down problems when running client COBOL or AE. It can also be used for debugging the PeopleSoft on-line as well. Like many of these other traces, enabling this trace slows processing down and results in large output files on the client.
PTPSQLRT Statistics Report	The PeopleSoft COBOL API, PTPSQLRT, provides a report called “PTPSQLRT Statistics” that shows frequency and elapsed times for SQL statements executed in batch processing. This report provides you with an effective and easy-to-use tool to monitor and evaluate SQL performance. No DBA involvement is required, and impact on batch performance is negligible. This report can track both client or mainframe COBOL. For client COBOL, you enable this report by selecting a SQL Trace option. On the mainframe, you modify the JCL to set the trace option to Y.
Dynamic Explains	The PTPSQLRT API program allows you to capture access path information of the SQL statements executed during batch processing. This information can help you tune any problem queries identified by the Timings Statistics Report.
Parallelism	The PTPSQLRT API program allows you to capture access path information of the SQL statements executed during batch processing. This information can help you tune any problem queries identified by the Timings Statistics Report.
Parallelism	You can specify the degree of parallelism for the execution of queries that are dynamically prepared by the application process.
SQL Trace - Server	You can run a PeopleSoft SQL trace on the z/OS server that displays similar results as the SQL Trace on the client. You can see SQL times, return codes and SQL that is executed.
SQR flag	You can use the -S flag to generate a SQL script consisting of fully resolved DB2 z/OS statements. This trace doesn't provide timings for the SQL statements.

Enabling DB2 CLI/ODBC Trace

The DB2 CLI/ODBC trace can be enabled in two places. The first method is to update the DB2CLI.INI directly. These are the recommended settings for turning Trace on:

[COMMON]
TRACEFLUSH=1
TRACEPATHNAME=C:\TEMP\DB2TRACE\ (or use TRACEFILENAME=C:\TEMP\filename to direct to file)
TRACECOMM=1
TRACE=1   (trace=0 turns the trace OFF)
TRACEREFRESHINTERVAL=60

The second method is to enable the trace using the Client Configuration Assistant.

Enabling the PTPSQLRT Mainframe Statistics Report

Control over enabling and disabling Statistics Reports on the z/OS batch server is now done primarily through the PSOS390 Process Scheduler configuration. The JCL shell SHELCBL.JCT file located in the /u/datax/psvvv/appserv/prcs/process_scheduler_name/shelljcl directory on USS contains symbolic variables for each parameter that is resolved by Process Scheduler when a COBOL job is submitted. As an option, you can replace the symbolic with the "hard coded" value of Y to enable or N to disable the particular parameter. The section in the JCL shell appears as follows:

//* PARMFILE - PARM 1 IS OPRID - LEAVE AS SYMBOLIC //* PARM 2 IS RUN CONTROL NAME //* PARM 3 IS A YES/NO SWITCH FOR PERFORMANCE STATISTIC //* PARM 4 IS PROCESS INSTANCE; 0 TRIGGERS PROC INST LOGIC //* BLANK IF NON-PROCESS SCHEDULER JOB //* PARM 5 IS A YES/NO SWITCH FOR DYNAMIC EXPLAINS //* PARM 5 REQUIRES THAT PARM 3 IS SET TO YES //* PARM 6 IS A YES/NO SWITCH TO ENABLE PARALLEL PROCESSING //* PARM 7 IS A YES/NO SWITCH TO ENABLE SQL TRACE //* PARM 8 IS A YES/NO SWITCH TO ENABLE RUN STATISTICS //* PARM 9 IS A REMOTE-CAL INDICATOR - ALWAYS "BATCH" IN JCL //* PARM 10 IS THE FULL DIRECTORY PATH OF PS_HOME //* PARM 11 IS THE FULL DIRECTORY PATH OF PS_SERVDIR //* PARM 12 IS THE FULL PATH OF THE PROCESS SCHEDULER //* CONFIGURATION FILE //* PARM 13 IS THE USERID WITH FULL AUTHORIZATION IN USS //* OF ALL HFS DIRECTORY WHERE AE WILL WRITE THE LOGS TO //* PARM 14 IS THE JCL JOB NAME //* PARM 15 IS THE REGION SIZE SETTING (DEFAULT IS -1) //* PARM 16 IS THE MAX CPU TIME ALOTTED FOR AN AE SUBROUTINE //* //* //****************************************************************** //* NOTE ON ENABLE RUN STATISTICS: IF YOU HAVE CHANGED THE //* SETTINGS TO RUN STATISTICS ON TABLESPACES, YOU MUST BIND //* THE PLAN FOR PTPSQLRT USING BINDAREP AND BINDEREP IN THE //* JCLLIB LIBRARY WITH THE BIND OPTION: PKLIST (DSNUTILS.*) //* INCLUDED IN THE OPTIONS LIST //* //* PARAMETERS 10-16 ARE REQUIRED WHEN RUNNING A COBOL //* PROGRAM THAT TRIGGERS AN AE SUBROUTINE //*********************************************************************** //* //PARMFILE DD * %OPRID% %RUNID% %PERFSTAT% %INSTANCE% %DYNEXPLN% %PARALLEL% %SQLTRACE% %RUNSTAT% BATCH %PS_HOME% %PS_SERVDIR% %PS_SERVERCFG% %PS_CONFIG% %HFS_USERID% %JOBNAME% %REGION_SIZE% %CPU_TIME% /*

Viewing the PTPSQLRT Report

Timings represent elapsed (wall clock) times expressed in seconds. For example, the value 2.383 means 2 seconds, 383 milliseconds. Where a value for COUNT exists, and TIME = .000, indicates an elapsed time of less than 1 millisecond. Because the results of this report are in elapsed time, care should be taken in interpreting the results. If you are using the report to identify poorly performing SQL, then multiple executions should be analyzed and the results compared before drawing any conclusions. Further analysis requires Explains and CPU timings of possible problem statements.

STATEMENT NAME

The name associated with a single SQL statement to be executed dynamically by program PTPSQLRT. It can be either a Select, Update, Insert or Delete statement as indicated by the “S,” “U,” “I,” or “D” designation in the statement name. Each statement may be executed once, or many times during a PeopleSoft batch program.

A statement name is made up of the program name and the type of SQL DML. For example, “PSPAGERT_S_AGERT” is in program PSPAGERT and is a SELECT; AGERT is a unique name within the application.

To examine a statement’s contents, you may either:
- Select * From your_id.PS_SQLSTMT_TBL Where PGM_NAME=’PSPAGERT’, STMT_TYPE=’S’ and STMT_NAME=’AGERT’
- On a LAN, locate the \PS\SRC\CBL\BASE subdirectory; statements are contained in files having the “DMS” extension and a filename of program name.
RETRIEVE

Reports the number of times and total time it takes for PTPSQLRT to select SQL statement text from PS_SQLSTMT_TBL. Retrieve count of zero indicate a Dynamic Statement that is not stored in PS_SQLSTMT_TBL.
PREPARE

Reports the number of times and total time it takes PTPSQLRT to do “DECLARE CURSOR” and prepare the SQL statement.
CLOSE

Reports the number of times and total time to CLOSE an open cursor. Applies to Cursor SELECT statements only
FETCH

Reports the number of FETCHes as well as the total time spent

For example, if Count=2 and Time=.040, it means that the program issued 2 fetch statements totaling .040 seconds (40/1000), or an average of .020 per fetch. If EXECUTION Count = 1, it means 2 FETCHes were done in a single OPEN Cursor. (This is true for columns 2 through 6.)
STMT TOTALS

Shows sum of all timings for each statement, calculated by adding all TIME accumulations horizontally. Also shown under “% SQL” is the percentage of the total processing time that a particular SQL statement represents. For example, a “% SQL” time of 1.03 indicates a statement used just over 1% of all SQL processing time.
TOTAL IN SQL CALLS

Total time spent by PTPSQLRT making SQL calls (sum of all SQL activity).
TOTAL IN SQLRT STATS

Total time spent by PTPSQLRT producing statistics (Assembler calls, COBOL processing).
TOTAL IN SQLRT OTHER

Total time spent by application programs (as in PSPTCALC.CBL) processing COBOL statements.
TOTAL IN SQLRT

Total in SQL Calls + Total in SQLRT stats + Total in SQLRT other. Total time spent by PTPSQLRT (SQL, STATS, COBOL).
TOTAL IN APPL COBOL

Total time processing COBOL programs.
TOTAL IN APPL

Total in SQLRT + Total in APPL COBOL. Grand total processing of all COBOL, PTPSQLRT, and SQL processing.
TOTAL SQLRT CALLS

Total number of calls made to PTPSQLRT called by COBOL programs.
TOTAL SQLRT STATEMENTS

Total number of distinct SQL statements executed.
MAXIMUM CURSORS CONNECTED

Largest number of active concurrent connection paths to DB2 UDB during program execution; “cursor” refers not only to open cursors, but deletes, inserts and updates as well.

There are three ways to enable the statistics report generation. The following steps take you through the three possibilities.

Enabling the Statistics Report on the DB2 UDB for z/OS Server

To enable the statistics report on the DB2 UDB for z/OS server:

Initialize the PSADMIN program on Unix System Services to administer the Process Scheduler PSOS390.
You may either select Option 3 - Configure a Process Scheduler Server, or Option 6 - Edit a Process Scheduler Configuration File.
If you select Option 3, you would set the value for TraceSQL to 128.
If you select Option 6, you need to locate the related section in the file and change the TraceSQL flag to 128.
As a third option, you could chose Option 9 - Edit a Shell JCL from the PSADMIN menu and select JCL Shell shelcbl.jct (selection 1)

This brings the file up in the VI editor.
Locate the PARMFILE section in the JCL Shell, and replace the symbolic %PERFSTAT% (Performance Statistic-PARM 3) parameter with the value Y for Yes.
Save the file and stop and restart the Process Scheduler.

The Statistics Report is written to a Sequential Dataset HLQ.ppvvv.program_name.

Enabling Dynamic Explains

The PTPSQLRT API program enables you to capture access path information of the SQL statements executed during batch processing. This information can help you tune any problem queries identified by the Timings Statistics Report.

Note. The Dynamic Explains feature is a performance tool for DBAs and other PeopleSoft product support personnel to be used for performance tuning. We recommend that this feature be disabled when in production mode.

There are three ways to enable generating a dynamic explain. The following steps take you through the three possibilities.

To enable Dynamic Explains in the JCL:

Initialize the PSADMIN program on Unix System Services to administer the Process Scheduler PSOS390.
You may either select Option 3 - Configure a Process Scheduler Server, or Option 6 - Edit a Process Scheduler Configuration File.
If you select Option 3, you need to set the value for TraceSQL to 256.
If you select Option 6, you need to locate the related section in the file and change the TraceSQL flag to 256.
As a third option, you could chose Option 9 - Edit a Shell JCL from the PSADMIN menu and select JCL Shell shelcbl.jct (selection 1)

This brings the file up in the VI editor.
Locate the related section in the JCL Shell, and replace both the symbolic %PERFSTAT% (Performance Statistic-PARM 3) and %DYNEXPLN% (Dynamic Explains-PARM 5) parameters with the value Y.
Save the file. It is not necessary to stop and re-start the Process Scheduler for the change in the JCL shell to take effect.

Enabling Parallelism

The PTPSQLRT API program provides a feature that allows you to enable DB2 UDB parallelism. If enabled, PTPSQLRT issues the following command to DB2 UDB:

SET CURRENT DEGREE = 'ANY'

The CURRENT DEGREE parameter specifies the degree of parallelism for the execution of queries that are dynamically prepared by the application process. For PeopleSoft, this applies to all queries run in batch. While setting CURRENT DEGREE =ANY enables DB2 UDB parallelism, this does not necessarily mean that the statements in the application programs uses parallelism. What it means is that DB2 UDB's optimizer considers parallelism as a possible option.

There are also three ways to enable parallel processing. The following steps takes you through the three possibilities.

To enable parallelism in the JCL

Initialize the PSADMIN program on Unix System Services to administer the Process Scheduler PSOS390.
You may either select Option 3 - Configure a Process Scheduler Server, or Option 6 - Edit a Process Scheduler Configuration File.
If you select Option 3, you need to set the value for Enable Parallel Processing to 1.
If you select Option 6, you need to locate the related section in the file and change the Enable Parallel Processing flag to 1.
As a third option, you could chose Option 9 - Edit a Shell JCL from the PSADMIN menu and select JCL Shell shelcbl.jct (selection 1).

This brings the file up in the VI editor.
Locate the section in the JCL Shell, and replace the symbolic %PARALLEL% (Parallel Processing-PARM 6) parameter with the value Y.
Save the file. It is not necessary to stop and re-start the Process Scheduler for this change to be recognized.

Enabling PeopleSoft SQL Trace

The PTPSQLRT API program enables you to capture a PeopleSoft SQL trace. This trace has been improved in PeopleSoft 8 to mirror the familiar SQL trace used on the client. The functionality of this trace has been improved to include all dynamic SQL statements that have been captured in the past by the DYSQLOG trace.

Note. The SQL Trace feature is a tool for DBAs and other PeopleSoft product support personnel to use for performance tuning. We recommend that this feature be disabled in production mode.

There are three ways to enable the SQL Trace. The following steps take you through the three possibilities.

To enable PeopleSoft SQL trace in the JC:

Initialize the PSADMIN program on Unix System Services to administer the Process Scheduler PSOS390.
You may either select Option 3 - Configure a Process Scheduler Server, or Option 6 - Edit a Process Scheduler Configuration File.
If you select Option 3, you need to set the value for TraceSQL to 1.
If you select Option 6, you need to locate the related section in the file and change the TraceSQL flag to 1.
As a third option, you could chose Option 9 - Edit a Shell JCL from the PSADMIN menu and select JCL Shell shelcbl.jct (selection 1).

This brings the file up in the VI editor.
Locate the section in the JCL Shell, and replace the symbolic %SQLTRACE% parameter with the value Y.
Save the file. If you originally configure the Process Scheduler with “Allow Dynamic Changes = Y” it isn't necessary to stop and re-start the Process Scheduler for this change to take effect.

The following is Sample PeopleSoft SQL trace from z/OS output queue:

Elapsed     SQL
    Time       Time  Crsr Return  DB API Statement
    ------    ------ ---- ------  ----------------
     0.044     0.044      RC=   0 CEX Stmt=SELECT OWNERID FROM PSSTATUS
     0.000     0.000      RC=   0 CEX Stmt=SET CURRENT SQLID = 'PT800RB'
     0.000     0.000      RC=   0 CEX Stmt=SET CURRENT DEGREE= '1'
     0.039     0.029      RC=   0 GETSTMT Stmt=PTPRUNID_U_UPDID
     0.003     0.002 #001 RC=   0 Prepare=UPDATE PS_PRCSSYSTEM SET LASTPRCSINSTANCE = LASTPRCSINSTANCE + 1
     0.013     0.000 #001 RC=   0 Execute
     0.013     0.000 #001 RC=   0 Row Count=000000001
     0.000     0.000      RC=   0 GETSTMT Stmt=PTPRUNID_S_GETID
     0.002     0.002 #001 RC=   0 COM=SELECT LASTPRCSINSTANCE FROM PS_PRCSSYSTEM
     0.006     0.000 #001 RC=   0 SSB=0001 TYPE=SQLPSLO LEN=0004
     0.000     0.000 #001 RC=   0 Execute
     0.000     0.000 #001 RC=   0 Fetch
     0.002     0.000 #001 RC=   0 Commit
     0.011     0.000      RC=   0 GETSTMT Stmt=PTPLOGMS_S_OPRDEFN
     0.002     0.002 #002 RC=   0 COM=SELECT LANGUAGE_CD FROM PSOPRDEFN WHERE OPRID = :1
     0.007     0.000 #002 RC=   0 SSB=0001 TYPE=SQLPBUF LEN=0003
     0.000     0.000 #002 RC=   0 Bind=0001 Type=SQLPBUF Len=0002 Data=PS
     0.000     0.000 #002 RC=   0 Execute
     0.000     0.000 #002 RC=   0 Fetch
     0.000     0.000 #001 RC=   0 Close Cursor for PTPRUNID_S_GETID
     0.000     0.000      RC=   0 GETSTMT Stmt=PTPLOGMS_I_LOGMSG
     0.002     0.002 #001 RC=   0 Prepare=INSERT INTO PS_MESSAGE_LOG ( PROCESS_INSTANCE, MESSAGE_SEQ, JOBID,
 PROGRAM_NAME, MESSAGE_SET_NBR, MESSAGE_NBR, MESSAGE_SEVERITY, DTTM_STAMP_SEC ) VALUES (:1,:2,:3,:4,:5,:6,:7,:8)
     0.006     0.000 #001 RC=   0 Bind=0001 Type=SQLPSLO Len=0004 Data=000000044
     0.000     0.000 #001 RC=   0 Bind=0002 Type=SQLPSLO Len=0004 Data=000000001
     0.000     0.000 #001 RC=   0 Bind=0003 Type=SQLPBUF Len=0008 Data=PTPTEDIT
     0.000     0.000 #001 RC=   0 Bind=0004 Type=SQLPBUF Len=0008 Data=PTPTEDIT
     0.000     0.000 #001 RC=   0 Bind=0005 Type=SQLPSLO Len=0004 Data=000000104
     0.000     0.000 #001 RC=   0 Bind=0006 Type=SQLPSLO Len=0004 Data=000000101
     0.000     0.000 #001 RC=   0 Bind=0007 Type=SQLPSLO Len=0004 Data=000000010
     0.000     0.000 #001 RC=   0 Bind=0008 Type=0392 Len=0026 Data=1999-10-12-17.40.35.580000
     0.000     0.000 #001 RC=   0 Execute
     0.000     0.000 #001 RC=   0 Row Count=000000001
     0.000     0.000      RC=   0 GETSTMT Stmt=PTPLOGMS_S_GETMSG
     0.002     0.002 #003 RC=   0 COM=SELECT MESSAGE_TEXT FROM PS_MESSAGE_CATALOG WHERE LANGUAGE_CD = :1 AND
 MESSAGE_SET_NBR = :2 AND MESSAGE_NBR = :3
     0.006     0.000 #003 RC=   0 SSB=0001 TYPE=SQLPBUF LEN=0100
     0.000     0.000 #003 RC=   0 Bind=0001 Type=SQLPBUF Len=0003 Data=ENG
     0.000     0.000 #003 RC=   0 Bind=0002 Type=SQLPSLO Len=0004 Data=000000104
     0.000     0.000 #003 RC=   0 Bind=0003 Type=SQLPSLO Len=0004 Data=000000101
     0.000     0.000 #003 RC=   0 Execute
     0.000     0.000 #003 RC=   0 Fetch
 > 1999-10-12-17.40.35.580000 INFO(104,101) PI(44) Program(PTPTEDIT)
 TSE Application Edits:  Begin Job.
     0.006     0.000 #003 RC=   0 Commit
     0.013     0.000 #003 RC=   0 Commit
     0.001     0.001      RC=   0 GETSTMT Stmt=PTPUSTAT_U_PRCRQSB
     0.004     0.003 #001 RC=   0 Prepare=UPDATE PSPRCSRQST SET RUNSTATUS = :1 ,MSGNUM = :2 ,MSGSET = :3 ,PRCSRTNCD = :4
 ,BEGINDTTM = CURRENT TIMESTAMP ,LASTUPDDTTM = CURRENT TIMESTAMP ,MSGPARM1 = :5 ,MSGPARM2 = :6 ,MSGPARM3 = :7 ,MSGPARM4
 = :8 ,MSGPARM5 = :9 ,CONTINUEJOB = :10 WHERE PRCSINSTANCE = :11
     0.008     0.000 #001 RC=   0 Bind=0001 Type=SQLPBUF Len=0001 Data=7
     0.000     0.000 #001 RC=   0 Bind=0002 Type=SQLPSLO Len=0004 Data=000000000
     0.000     0.000 #001 RC=   0 Bind=0003 Type=SQLPSLO Len=0004 Data=000000104
     0.000     0.000 #001 RC=   0 Bind=0004 Type=SQLPSSH Len=0002 Data=0000
     0.000     0.000 #001 RC=   0 Bind=0005 Type=SQLPBUF Len=0001 Data=
     0.000     0.000 #001 RC=   0 Bind=0006 Type=SQLPBUF Len=0001 Data=
     0.000     0.000 #001 RC=   0 Bind=0007 Type=SQLPBUF Len=0001 Data=
     0.000     0.000 #001 RC=   0 Bind=0008 Type=SQLPBUF Len=0001 Data=
     0.000     0.000 #001 RC=   0 Bind=0009 Type=SQLPBUF Len=0001 Data=
     0.000     0.000 #001 RC=   0 Bind=0010 Type=SQLPSSH Len=0002 Data=0000
     0.000     0.000 #001 RC=   0 Bind=0011 Type=SQLPSLO Len=0004 Data=000000044
     0.000     0.000 #001 RC= 100 Execute
     0.000     0.000 #001 RC=   0 Row Count=000000000

Some PeopleSoft COBOL programs utilize the stored statement technique of fetching and executing dynamic SQL. Programs fetch SQL statements—commonly known as stored SQL statements—from PS_SQLSTMT_TBL, then processes them using dynamic SQL (Prepares and Executes). Other COBOL programs are designed to generate their own SQL text inside the program, rather than fetching the SQL text from a table. This technique is sometimes referred to as “dynamic-dynamic,” and is more commonly known as “dynamic statement” owing to its ability to generate dynamic SQL text and then to execute a dynamic SQL statement.

In the past, the timings of these dynamically generated statements have been recorded to the DYSQLLOG. In PeopleSoft 8 we have included the information on ‘dynamic-dynamic’ SQL statements into the PeopleSoft trace.

For example:

DYSQLLOG from previous versions of PeopleTools: ************************************************************************ DYNAMIC SQL-STATEMENT (Len= 90) UPDATE PS_TSE_EDITTBL SET TSE_EDIT_ERROR = ' ' WHERE (SETID = 'USA' AND COMPANY = 'CCB') Begin Time End Time Stmt Run Time Total Run Time 08:59:42 08:59:42 0.00.00.000000 0.00.00.000000 ************************************************************************

Same dynamic SQL represented in the new PeopleTools trace for PeopleSoft appears as follows:

0.010     0.000 #001 RC=   0 DYNAMICSTMT Stmt=PTPEDIT_U_HE000
0.003     0.003 #001 RC=   0 Prepare=UPDATE PS_TSE_EDITTBL SET TSE_EDIT_ERROR = ' ' 
WHERE (SETID = 'USA' AND  COMPANY = 'CCB')
0.007     0.000 #001 RC=   0 Execute
0.007     0.000 #001 RC=   0 Row Count=000000008

Note. The PeopleSoft SQL trace can grow very large, so do your initial testing on smaller processes—for example, a small number of journals to EDIT or POST.

Enabling SQR Monitoring

For SQR programs, there is no report available that shows statement timings like the one provided by PTPSQLRT. However, you can generate a SQL script consisting of fully resolved DB2 UDB for z/OS statements by running the SQR with the -S option.

There are three areas that SQR monitoring can be introduced and four ways that it can be enabled.

Adding SQR Flag to SQRSAMP

The first area is in the JCL member SQRSAMP. The SQR flag can be added to the PARMLIB(SQRPARMS) directly if you plan to use sample JCL member JCLLIB(SQRSAMP).

DSN SYSTEM(DSND)
RUN PROG(SQR)  -
    PLAN(SQR84) -
    LIB('<PSHLQ>.SQR.UNICODE.LOAD') -
    PARMS('SP DSN/PT84 -FSQROUT -S -GPRINT=NO -ISI -TBZ -PRINTER:LP')
END

Configuring Process Scheduler

The second area is within the Process Scheduler configuration, as follows:

To include the –S flag when configuring the PSOS390 Process Scheduler:

Initialize the PSADMIN program on Unix System Services to administer the Process Scheduler PSOS390.
You may either select Option 3 - Configure a Process Scheduler Server, or Option 6 - Edit a Process Scheduler Configuration File.
If you select Option 3, you would set the value for PSSQRFLAGS to -GPRINT=NO -TBZ -S.
If you select Option 6, you need to locate the related section in the file and change the PSSQRFLAGS value to -GRPINT=NO -TBZ -S.

Amending the Process Definition

The third area is from within the Process Definition. If you are running the SQR via the PSOS390 server, you have to append the –S flag to the process definition.

This can be accomplished by selecting PeopleTools, Process Scheduler Manager, Use, Process Definitions. Enter ‘SQR Report’ or ‘SQR Process’ for the process type and enter the SQR name (for example, XRFWIN). Select the Override Options tab and then select Append from the drop down Parameter list and enter the –S. This appends the –S flag to the SQR list when you run the SQR. Output from the –S flag is directed to SYSOUT in the SHELSQR JCL, which is the output queue by default.

Sample output from SQR with the –S flag enabled:

Cursor Status:

Cursor #1:
   SQL = SET CURRENT PRECISION = 'DEC31'
Compiles = 1
Executes = 1
Rows     = 0

Cursor #2:
   SQL = select A.RECNAME, A.SQLTABLENAME  FROM PSRECDEFN A WHERE
         A.SQLTABLENAME <> ' ' AND A.SQLTABLENAME <> A.RECNAME ORDER BY
         RECNAME
Compiles = 1
Executes = 1
Rows     = 0

Cursor #3:
  SQL = select A.RECNAME, A.SQLTABLENAME  FROM  PSRECDEFN A WHERE A.RECTYPE
        AND A.RECNAME <> 'PSDUMMY' ORDER BY A.RECNAME
Compiles = 2
Executes = 1
Rows     = 1284

Cursor #4:
  SQL = select 'X' FROM SYSIBM.SYSTABLES B WHERE B.CREATOR = CURRENT SQLID
        AND B.NAME = ? AND B.TYPE = 'T'
Compiles = 2
Executes = 1284
Rows     = 1280

Note. The -S option produces output that shows the frequency in which all SQL statements are compiled and executed.

Associating PeopleSoft Operators with DB2 UDB Threads

DBAs who have worked with PeopleSoft in the past know that it is a daunting task to associate PeopleSoft operators to DB2 UDB distributed threads. This is because all threads are displayed (via DISPLAY THREAD) with the access ID as the ID. If my access ID is PSOFT then there is the potential to see hundreds or even thousands of DB2 UDB distributed threads all signed on with PSOFT. One workaround is to give each PeopleSoft operator a unique access ID. This method works fine for PeopleSoft two-tier users. But it involves extra overhead to create and maintain these new access IDs, and it doesn't work for PIA connections, because all PIA connections are displayed with the access ID that was used to boot the application server regardless of whether each operator ID has a unique access ID.

With enhancements made to DB2 Connect’s sqleseti API Function, you can now associate the following items with each database connection or transaction:

User ID.

A 16-character field reserved for the client’s ID. This ID entered in this field is not used for connecting in DB2 UDB; it’s only intended for identification purposes.
Workstation name.

An 18-character field reserved for providing the workstation name for the client.
Application name.

A 32-character field reserved for the application name. Set to blanks for two tier connections. Set to the Domain Name for three tier connections. May appear to be database name but is really the Domain Name. Most customers set Domain Name equal to database name.

In PeopleSoft 8.4 these fields are populated with PeopleSoft-specific information.

Example #1: The following example shows a connection to a Peoplesoft/DB2 UDB database via two tier. I have signed onto Data Mover with Oprid = PTDMO. Notice that the Workstation Name of the client and PeopleSoft operator are displayed when using the DISPLAY THREAD command.

NAME ST A REQ ID AUTHID PLAN ASID TOKEN SERVER RA * 3251 PSDMT PSOFT DISTSERV 00D0 3009 V437-WORKSTATION=EPRESZ050499, USERID=PTDMO, APPLICATION NAME=*

Example #2: The following example shows a three-tier connection with DB2 UDB Client Monitoring enabled for the application server domain PT800T5. I am signing onto a Peoplesoft/DB2 UDB database with Oprid = VP1. The DISPLAY THREAD command shows the Workstation Name of the client, the PeopleSoft Operator as the user ID, and the application name is set to the Domain Name from the application server.

NAME ST A REQ ID AUTHID PLAN ASID TOKEN SERVER RA * 13237 PSQCKSRV.exe PSOFT DISTSERV 00D0 3097 V437-WORKSTATION=EPRESZ050499, USERID=VP1, APPLICATION NAME= PT812

Note. DB2 UDB Client Monitoring is enabled by default for PeopleTools 8.44 and above. For previous releases, DB2 UDB Client Monitoring was automatically enabled for two-tier PeopleSoft connections. For three-tier connections, customers must enable the DB2 UDB Client Monitoring feature by overriding the EnableDBMonitoring flag in the App Server configuration (psappsrv.cfg) file. To confirm that DB2 UDB Client Monitoring is enabled, check for the following setting in psappsrv.cfg: [Database Options] EnableDBMonitoring=1

Running COBOL

This section provides an overview of COBOL API and Meta SQL and discusses how to:

Run COBOL outside of Process Scheduler.
Disable persistent cursors.

Understanding COBOL API and Meta SQL

PTPSQLRT is the COBOL API program called by application COBOL programs to prepare and execute dynamic SQL statements. The program fetches SQL statements—known as stored SQL statements—from PS_SQLSTMT_TBL, then processes them using dynamic SQL. (Prepares and Executes.) Except for PTPSQLRT, PeopleSoft application COBOL programs do not contain a direct DB2 UDB interface and therefore need only be compiled and link-edited.

Stored SQL statements contain META SQL statements mainly to support date and time functions, for example:

Select %currentdatetimeout from PSLOCK ;

PTPSQLRT resolves META SQL statements by calling PTPSQLGS which translates the META SQL function into DB2 UDB syntax. Stored statements are delivered in directory \SRC\CBL\BASE of the installation file server.

Running COBOL Outside of Process Scheduler

COBOL jobs may run outside process scheduler by specifying a 0 (a numeric zero) for the process instance, as shown in the fourth parameter below:

//PARMFILE DD * %OPRID% %RUNID% N 0 N N Y

Sample COBOL JCL is provided in HLQ.PPVVV.JCLLIB(CBLSAMP). Be aware that some application processes are designed to run only through Process Scheduler.

Disabling Persistent Cursors

The z/OS version of the COBOL API program called PTPSQLRT uses Cursor With Hold by default.

In PeopleSoft terminology, the field CURSOR_SW in PTPSQLRT is used to define Persistent Cursors (CURSOR-PERSISTENT) and Normal Cursors (CURSOR-NORMAL). CURSOR-PERSISTENT adds the WITH HOLD keyword to SQL selects in DB2 UDB. This maintains cursor position after a commit, so that repositioning (i.e. reopening and re-fetching) does not need to occur.

The DB2 UDB version of PTPSQLRT is shipped with Persistent Cursors enabled. If you don’t want to use this feature, you can disable it by editing PTPSQLRT as follows:

To disable Cursor with Hold (i.e. Persistent Cursors):

Edit PTPSQLRT and do a “find” on ‘CURSOR WITH HOLD’ => f ‘CURSOR WITH HOLD’.
For each of the 254 pairings of Cursor statements, remove the asterisk (*) from line that creates the cursor without the WITH HOLD option (column 7) and place it in column 7 of the line that creates the cursor with the WITH HOLD option above it. For example:

Before:
```
EXEC SQL
           DECLARE CURSOR_01
           CURSOR WITH HOLD FOR SQLSTMT_01
  *       CURSOR FOR SQLSTMT_01
        END-EXEC
```
After:
```
EXEC SQL
           DECLARE CURSOR_01
  *       CURSOR WITH HOLD FOR SQLSTMT_01
           CURSOR FOR SQLSTMT_01
        END-EXEC
```

Administering SQR for z/OS

This section provides an overview of SQR on z/OS and discusses how to:

Run SQRs outside of Process Scheduler.
Specify input and output files.
Print SQRs.

Understanding SQR on z/OS

The SQR product is available for z/OS server platforms. The z/OS version of SQR is compatible with your existing SQR reports that you currently run from your client machines. The ability to run SQRs on the mainframe means a significant performance enhancement for SQR execution. All SQL is dynamic, therefore no precompiling is necessary for running SQRs.

You can execute SQRs on z/OS by either by using PeopleSoft Process Scheduler or submitting them as traditional z/OS jobs. Process Scheduler dynamically generates SQR JCL.

See Enterprise PeopleTools 8.49 PeopleBook: PeopleSoft Process Scheduler.

Be aware that some application processes are designed to run only through Process Scheduler.

SQR for z/OS is now delivered on the PeopleTools Installation CD. The DB2 UDB for z/OS Installation guide includes instructions for performing the installation of SQR on the z/OS server. SQR must be installed prior to running PeopleSoft SQRs on the z/OS server. Please allow at least 12 cylinders of 3390 DASD or equivalent disk space to complete the installation.

Running SQRs Outside of Process Scheduler

Three run time parameters are supplied to SQRs initiated by the Process Scheduler: PROCESS_INSTANCE, OPRID and RUN_CNTL_ID. (RUN_CNTL_ID is needed by FS SQRs only.) When submitted by Process Scheduler, run time parameters are obtained from a Process Scheduler table and dynamically inserted into the generated JCL.

For an SQR submitted as a traditional z/OS batch job, two run time parameters are required, but it is not necessary to pass any specific values. It is only necessary to include a blank line for each parameter in the JCL specified in the SYSIN DD. The appropriate segment of the JCL is shown below:

//SQRNAME EXEC SQRPROC,SQRID=SQRNAME //SQR.SYSIN DD * /*

Sample SQR JCL is provided in HLQ.PPVVV.JCLLIB(SQRSAMP).

Specifying Input and Output Files

Input and output files are required by SQR when using commands such as OPEN and NEW-REPORT. Remember to consult the appropriate Application User Guide for important application specific information concerning SQR for z/OS. For instance, Accounts Payable naming conventions used to build DD names are discussed in the PeopleSoft Payables PeopleBook.

There are two ways to specify input and output files in SQR for z/OS:

Add DD statements to the JCL.

This method allows you to maintain a common set of SQR that can execute on any operating system by requiring the modification of Process Scheduler Shell JCL and/or z/OS Batch JCL.
Add a DSN style filename to the SQR.

This method alleviates the need to modify JCL but creates z/OS operating system specific SQR.

Use the first method if your SQR should execute on any operating system. Use the second method if your SQR executes only on the z/OS operating system.

Adding DD Statements to the JCL

You may specify a file name for commands such as OPEN and NEW-REPORT using DOS or UNIX file naming conventions. The SQR for z/OS documentation states that SQR will use up to 8 alpha-numeric characters preceding the file extension as a DD name in JCL. This has been found to be untrue.

SQR for z/OS does not find 8 alpha-numeric characters as documented. To get around this problem, the FILEPREFIX and FILESUFFIX environment variables in $PSHLQ$.SQRINC(SETENV) are used when coding filenames in SQR. The example below shows that SETENV does not contain values for FILEPREFIX and FILESUFFIX when running on the z/OS operating system:

! File prefixes and suffix ! #ifdef NT #define FILEPREFIX C:\TEMP\ #define FILESUFFIX #endif ! #ifdef MVS #define FILEPREFIX #define FILESUFFIX #endif ! #ifdef UNIX #define FILEPREFIX /usr/tmp/ #define FILESUFFIX #endif !

This coding standard enables DOS or UNIX file naming conventions to be used with the OPEN or NEW-REPORT SQR commands. The root portion of the file name is used as a JCL DD name. The Process Scheduler Shell JCL or z/OS Batch JCL must contain this DD name. Each file used for input or output requires a separate DD statement in the JCL.

In the following example, SQR for z/OS uses VIEWTBL as the DD name in JCL:

let $outputfile = ‘{FILEPREFIX}VIEWTBL{FILESUFFIX}’
open $outputfile as 1 for-writing record=132

The DD statement in the execution JCL requires the same DD name:

//VIEWTBL DD  DSN=&PSHLQ..OUTFILES(VIEWTBL),DISP=SHR

The data set name specified in the JCL may be either sequential or partitioned dataset. If the SQR requires multiple input or output files, you must add a separate DD statement to the JCL for each file.

Note. DD names must reference either sequential datasets or separate partitioned datasets due to the z/OS restriction on writing to more than one PDS member simultaneously.

While modifications to Process Scheduler Shell JCL or z/OS Batch JCL are required using this method, the resulting SQR is not operating system specific.

Adding a DSN Style Filename to the SQR

Filenames are preceded by DSN: (dataset name). For example:

OPEN ‘DSN: $PSHLQ$.SQR.DAT(VIEWTBL)’ FOR-READING RECORD=133
NEW-REPORT ‘DSN: $PSHLQ$.SQR.DAT(VIEWTBL)’

Modifications to Process Scheduler Shell JCL or z/OS Batch JCL are not required when using the above option. However, this option results in operating system specific SQR that executes only on the z/OS operating system.

Printing SQRs

The SQR language supports a DECLARE PRINTER command so that reports can be directed to HPLASERJET and POSTSCRIPT type printers. However, if the printer is not mainframe-connected, the TYPE=LINEPRINTER option is required.

The TYPE=LINEPRINTER specification produces a basic text type report which can be redirected to a system printer. Normally, print output lines don't exceed 124 print positions.

SQRs containing the SETUP02 statement (refers to a member in the SQC library) allow print lines up to 177 positions. If SQROUT DD prints to SYSOUT, then supply a DCB override for SYSOUT and set the LRECL to 178, this is given in SQRSAMP JCL under your JCLLIB PDS. It would be a good practice to have DCB override with LRECL=178 for SQROUT DD, as this fits both landscape and portrait mode.

Formatted reports cannot be downloaded, then printed from a workstation connected printer. Only selected SQRs utilize the DECLARE PRINTER feature.

Note. A “formatted” report is one produced with the TYPE=HPLASERJET /POSTSCRIPT specification.

Many customers customize SQRs to tailor information to unique business requirements.

Another reason to customize SQRs is due to the way SQR formats reports when the TYPE=LINEPRINTER option is used. Column header and data field alignment may not be optimal, so modifications may be required.

Updating Statistics

This section provides an overview of %UpdateStats and discusses how to:

Set up the IBM stored procedure DSNUTILS.
Install the database following the enhanced installation path.
Update system tables with database and tablespace information.
Activate %UpdateStats.

Understanding %UpdateStats

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.

Setting Up the IBM System Stored Procedure: DSNUTILS

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. In PeopleTools 8.44 and beyond, the %UpdateStats meta-SQL is enabled by default for both z/OS and Windows NT/2000 Process Schedulers.

See PeopleTools 8.49 Installation for DB2 UDB for z/OS.

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

Installing the Database Following the Enhanced Installation Path

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 follows our pre-PeopleSoft 8 strategy of combining multiple tables into a single tablespace. The “Enhanced” installation path has segregated the PeopleSoft temporary tables into separate tablespaces.

See PeopleTools 8.49 Installation for DB2 UDB for z/OS, “Creating a Database.”

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 invoked with PeopleSoft temporary tables. It is not intended to be used to update catalog statistics for permanent Application or PeopleTools tables.

Updating System Tables with Database and Tablespace Information

When issuing %UpdateStats meta-SQL in your program, you specify the temporary table on which you intend to have the statistics updated. For PeopleTools releases prior to 8.44, code within PeopleTools determines the tablespace and database to which the table belongs and passes these values to the DSNUTILS stored procedure. Database and tablespace name is 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.

For PeopleTools 8.44 and later, 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.

Activating %UpdateStats

%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 NT/2000 and z/OS Process Schedulers.

Enabling/Disabling %UpdateStats for Batch Processing

To enable/disable %UpdateStats for batch processing:

Initialize the PSADMIN program on Unix System Services to administer the Process Scheduler PSOS390.
Select either Configure a Process Scheduler Server or Edit a Process Scheduler Configuration File.
If you select the first option, set the value for DbFlags to 0 to enable or 1 to disable %UpdateStats.
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.

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

Uncomment the lines noted above so they are activated in the program code.
Compile the program PTPSQLRT by submitting the two JCL members found in $PSHLQ.JCLLIB:
- PSCOBDA
- PSCOBDE
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.*)
For first time Binding modify the following two members in $PSHLQ.JCLLIB:
- BINDAADD
- BINDEADD
For rebinding an existing plan, modify the following two members in $PSHLQ.JCLLIB
- BINDAREP
- BINDEREP

Setting the Number of Temporary Tables

Normally you may leave the number of temporary tables set to the default established at installation. You may need to change this setting for optimal performance, depending on various aspects of your implementation, including account transaction volumes, benchmark numbers for the current hardware and database platform, as well as your service-level requirements. Use the following procedure if you need to adjust the number of temporary tables to improve performance in your implementation.

To set the number of temporary tables:

Select PeopleTools, Utilities, Administration, PeopleTools Options.
Set the Temp Table Instances (Total) and Temp Table Instances (Online) fields to the desired settings.

Note. Temp Table Instances (Total) should always be set to the same values as Temp Table Instances (Online), unless you have been instructed otherwise in the application documentation.
Scroll to the bottom of the page and select the Save icon to save the newly edited PeopleTools options.

Note. The total number of instance generated consists of the allocations specified on the PeopleTools Options panel plus the allocations specified on each individual Application Engine program. (To modify these allocations, open an Application Engine program in Application Designer, open the Properties dialog box for the object, and click the Temporary Tables tab.)
Recreate all temp tables in your database.

See PeopleTools 8.49 Installation for DB2 UDB for z/OS, “Creating a Database.”

Warning! If you change the number of online temporary table instances as described above, it is critical that you recreate all temporary tables in your database, particularly if you are increasing the number of instances. The parameter above is global to all temporary tables and is used by all on-line processes to determine the number of temporary table instances that should be available to a given process. If you don't recreate all temporary tables, a process may try to use an instance that has not been created on the database, and will subsequently fail.

Creating Temporary Tables

For each temporary table you define, a base table structure and a number of its instances are created in the database as ordinary tables with ordinary table structures. The number of temporary table instances is determined by the value of the Temp Table Instances setting in PeopleTools options Utilities, Administration, PeopleTools Options added to the number of PeopleSoft Application Engine temporary tables. These temporary tables are used as work tables that hold transient data, and because they are real tables, they are permanent structures in the database and remains until an explicit drop table command is executed against them.

The nature of a temporary table means that the amount of data that each temporary work table holds varies significantly after each use. Therefore, when RUNSTATS are executed against them, there is a good chance that the statistics captured may not apply and will negatively influence the DB2 optimizer access path selection the next time you use the temporary work table.

Each record of the type Temporary Table is defined as a VOLATILE table in DB2 Version 8. This definition takes advantage of DB2 version 8 optimizer’s enhanced capability to formulate an efficient index access paths for those tables that hold volatile data without relying on current table statistics.

Note. There is no VOLATILE table in DB2 Version 7.

Example

This example shows the VOLATILE parameter in the CREATE TABLE DDL for the base temp table and its instances in DB2 Version 8.

CREATE TABLE FSDMOA.PS_AEEXT_TAO (PROCESS_INSTANCE DECIMAL(10) NOT
 NULL,
   AE_INT_1 SMALLINT NOT NULL,
   AE_APPLID CHAR(12) NOT NULL,
   AE_SECTION CHAR(8) NOT NULL,
   AE_STEP CHAR(8) NOT NULL) VOLATILE IN FSDMOA.PTAPPE;