Understanding DB2 UDB for z/OS AdministrationThis 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.
Work with Alters on DB2 z/OS.
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 AdministrationThis section discusses:
Database considerations.
Concurrency.
Database Considerations
The section discusses:
Tablespace strategy
Locksize tablespace
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.
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
For PeopleTools, the use of Cursor With Hold (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.
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 |
|
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. |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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. |
|
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 directly in the DB2CLI.INI directly or using the Client Configuration Assistant.
When updating the DB2CLI.INI directly, these are the recommended settings for enabling Trace:
[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
Or you can enable the trace, with the same options, using the Service tab in 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% /*
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
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 Users with DB2 UDB Threads
In a PeopleSoft application running on DB2, threads are displayed with the access ID as the ID. If the access ID is PSOFT, for example, then you are likely see hundreds or even thousands of DB2 UDB distributed threads all signed on with PSOFT, not the actual user ID associated with a transaction.
One workaround is to give each PeopleSoft user a unique access ID. This method works fine for PeopleSoft two-tier users (working in the development environment). But, this approach involves extra overhead to create and maintain these individual access IDs. Of course, this approach would not work for your PIA (browser) connections, which in many sites can involve thousands of users. Plus, all PIA connections are displayed with the access ID that was used to boot the application server, regardless of whether each user ID has a unique access ID.
However, so that you can associate individual users with individual transactions, features of the DB2 Connect’s sqleseti API (set client information) function, enable you to associate the following information with each PeopleSoft database connection or transaction. The PeopleTools system populates these fields with PeopleSoft-specific information:
|
Field |
Description |
|
WORKSTATION |
An 18-character field reserved for providing the workstation name for the client. |
|
USERID |
A 16-character field reserved for the PeopleSoft user ID. |
|
APPLICATION NAME |
A 32-character field reserved for the application name. For two-tier connections the value will be blank. For three-tier and PIA connections, the value is the application server domain handling the connection. |
|
ACCOUNTING |
A 200-character field reserved for additional, module information that selected PeopleTools "modules" populate. The ACCOUNTING field contains the following information, depending on the type of transaction:
|
The following example shows a connection to a PeopleSoft DB2 UDB database through the development environment (two-tier connection). The DISPLAY THREAD command, shows the user, PTDMO, signed on to Data Mover (PSDMT) and the client workstation name.
NAME ST A REQ ID AUTHID PLAN ASID TOKEN SERVER RA * 3251 PSDMT PSOFT DISTSERV 00D0 3009 V437-WORKSTATION=EPRESZ050499, USERID=PTDMO, APPLICATION NAME=*
The following example shows a PIA connection with DB2 UDB Client Monitoring enabled for the application server domain. The user is signing on to a PeopleSoft DB2 UDB database with the user ID of VP1.
The DISPLAY THREAD command shows:
Workstation Name of the client.
PeopleSoft user, VP1, as the USERID.
Application server domain name, PSHCMT5, as the APPLICATION NAME value.
PeopleSoft component, QUERY_MANAGER, set as the ACCOUNTING value.
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=PSHCMT5 ACCOUNTING=QUERY_MANAGER
Note. DB2 UDB Client Monitoring is enabled by default for PeopleTools. To confirm that DB2 UDB Client Monitoring is enabled, check for the following setting in PSADMIN or 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 SchedulerCOBOL 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 (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.
Be aware that some application processes are designed to run only through Process Scheduler.
SQR for z/OS is delivered with the PeopleTools installation. The PeopleTools 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.
Allow at least 12 cylinders of 3390 DASD or equivalent disk space to complete the installation.
See Also
PeopleTools 8.51 PeopleBook: PeopleSoft Process Scheduler
PeopleTools Installation for your platform
Running SQRs Outside of Process Scheduler
These run time parameters are supplied to SQRs initiated by the Process Scheduler.
PROCESS_INSTANCE
OPRID
RUN_CNTL_ID (required by specific applications only, such as Financials)
When an SQR request is 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 PeopleSoft application documentation 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 PeopleBooks.
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.
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.
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.
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.
Note. The system overrides the FILEPREFIX and FILESUFFIX variables with the value specified in setfile.sqc, when running the SQR reports through the Process Scheduler with an output type of Web. The FILEPREFIX variable will be substituted with the log_output folder path of the Process Scheduler domain (<DOMAIN>/log_output/SQR_<process_name>_<prcs_instance>/). The FILESUFFIX variable will be substituted with a blank value. When there is a requirement to generate a static file or provide a static file input to the SQR report for an output type of Web, these variables should be redefined in the SQR file itself (rather than in the SQC file) to any suitable path and extension.
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. The %UpdateStats meta-SQL is enabled by default for both z/OS and Windows Process Scheduler servers..
See PeopleTools 8.51 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 My
Oracle Support. This document outlines the minimum requirements to run Workload
Manager in goal mode which is required by the DSNUTILS stored procedure.
See Also
IBM DB2 UDB for z/OS documentation
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 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
See Also
PeopleTools 8.51 Installation for DB2 UDB for z/OS, “Creating a Database.”
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. 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.
|
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.
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 Windows and z/OS Process Scheduler servers.
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.51 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 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, remaining 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 (beginning with version 8). This definition takes advantage of the DB2 optimizer’s enhanced capability to formulate efficient index access paths for those tables that hold volatile data, without relying on current table statistics.
Example: VOLATILE Used in CREATE TABLE DDL
This example shows the VOLATILE parameter in the CREATE TABLE DDL for the base temp table and its instances.
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;
Working with Alters on DB2 z/OS
This section provides an overview and discusses:
Advisory Reorg Pending and Rebuild Pending Status
Determining Whether A Tablespace or Index Is In A Pending Status
Tablespace Versioning
Determining When to Recycle Tablespace Version Numbers
Working with DB2 tablespace Vversioning and PeopleSoft upgrades
Understanding Alters on DB2 z/OSPeopleTools Data Administration tools support native DB2 zOS alter syntax (known as DB2 Online Schema Evolution) for altering tables. For example:
ALTER TABLE <table name> ALTER COLUMN <column name> SET DATA TYPE <new definition>
Use of this Alter syntax provides the capability to make structural changes to a table without the requirement to drop and recreate it. The data remains available for both inquiry and update processing. This means that when possible, PeopleTools use "Alter In Place" through this native alter syntax, rather than "Alter By Table Rename" for specific additional use cases.
As a result of this Alter In Place syntax, existing data rows are not immediately reformatted at the time that the alter is committed to DB2. Instead, as a result of the use of the ALTER TABLE <table name> ALTER COLUMN <column name> SET DATA TYPE <new definition> syntax, DB2 does the following:
Places the corresponding tablespace or indices in either an Advisory Reorg Pending (AREO*), or Rebuild Pending (RBDP) status depending on the nature of the change (alter) made to a particular table.
Creates a new version of the tablespace which reflects the format of the desired change. Despite the pending status, the data continues to be available for inquiry and update processing, and rows are subsequently materialized in the post-alter format (the current tablespace version) as they are retrieved.
Advisory Reorg Pending and Rebuild Pending StatusThe following types of changes will cause tablespaces and indices to be placed in Advisory Reorg Pending (AREO*) status:
|
DB2 z/OS version |
Changes |
|
8.1 |
|
|
9.1 (and later) |
|
The following types of changes will cause indices to be placed in Rebuild Pending (RBDP) status:
|
DB2 z/OS version |
Changes |
|
8.1 and later |
Changes between compatible numeric data types will place any index that contains the affected column in RBDP. |
Determining Whether A Tablespace or Index Is In A Pending StatusTo find objects in a pending status, run the display database command using the DB2 Interactive Command Processor (DB2I), DSN session under TSO, or a z/OS console session. As an example, the following command displays all tablespaces in the Advisory Reorg Pending (AREO*) status and all indices in the Rebuild Pending status for a database called Q51802R1:
-DIS DB(Q51802R1) SPACENAM(*) ADVISORY(AREO*) RESTRICT(RBDP) DSNT360I -5A *********************************** DSNT361I -5A * DISPLAY DATABASE SUMMARY * RESTRICTED ADVISORY DSNT360I -5A *********************************** DSNT362I -5A DATABASE = Q51802R1 STATUS = RW DBD LENGTH = 2996486 DSNT397I -5A NAME TYPE PART STATUS PHYERRLO PHYERRHI CATALOG PIECE -------- ---- ----- ----------------- -------- -------- -------- ----- PTTBLZZ TS RW,AREO* IDX1R9 IX RW,RBDP ******* DISPLAY OF DATABASE Q51802R1 ENDED **********************
Tablespace PTTBLZZ is in Read, Write and Advisory Reorg Pending status.
Index IDX1R9 is in Read, Write and Rebuild Pending status.
See Also
Your IBM DB2 for z/OS command reference guide
Tablespace VersioningIn most circumstances, a committed alter will cause DB2 to create a new version of the tablespace, which reflects the format of the desired alter. Rows are subsequently materialized in the post-alter format as they are retrieved. DB2 can store up to a maximum of 256 active versions of a tablespace, numbered 0 to 255. Version 0 indicates a tablespace that has never been altered, and version 0 is never reused. All rows are formally converted to the format determined by the latest tablespace version when the tablespace is reorganized.
When a tablespace reaches the maximum number of versions, it is important to note that subsequent attempts to alter any table contained in the tablespace will fail with SQL Code -4702. At this point, the DB2 Reorg Tablespace and Modify Recovery utilities must be executed to:
reformat the data as dictated by the latest version of the tablespace (by running the Reorg Tablespace utility).
recycle the version numbers so that DB2 can reuse all version numbers other than the active version of the tablespace (by running the Modify Recovery utility).
Note. The Reorg Tablespace utility with Index(All) option also removes the AREO* and RBDP statuses.
Important! When executing a significant number of Alters In Place (such as during the Upgrade Alter Without Deletes step or when applying a Maintenance Pack), the potential exists to create the maximum number of tablespace versions, particularly when running against shared tablespaces.
To mitigate the risk of encountering the -4702 SQLCode, PeopleTools Development recommends that you query the DB2 zOS Catalog to determine if there are any tablespaces in your PeopleSoft database that are close to the maximum version limit, and then execute the Reorg and Modify Recovery utilities accordingly to recycle version numbers before beginning any step that executes a significant number of Alters In Place.
Determining When to Recycle Tablespace Version NumbersTo determine which tablespaces may require recycling of version numbers prior to beginning the Alter Without Deletes step, review the OLDEST_VERSION and CURRENT_VERSION columns of SYSIBM.SYSTABLESPACE for each tablespace in your PeopleSoft database as described in this section.
Use the following query as a guide to list the oldest and current version numbers for all PeopleSoft tablespaces in your environment:
SELECT NAME, DBNAME, OLDEST_VERSION, CURRENT_VERSION FROM SYSIBM.SYSTABLESPACE WHERE CREATOR = '<authid of the owner of the tablespace>' AND CURRENT_VERSION > 0;
Recycling tablespace version numbers is mandatory when all version numbers are currently in use. All tablespace versions are currently in use when one of the following conditions is true:
The value of the CURRENT_VERSION column is 255 (SYSIBM.SYSTABLESPACE).
The value of the CURRENT_VERSION column is one less than the value of the OLDEST_VERSION column.
Example: The value of the CURRENT_VERSION column is 255 (SYSIBM.SYSTABLESPACE)
All versions for tablespace PTTLRG0M are now in use, and the version numbers must be recycled.
DBNAME NAME OLDEST_VERSION CURRENT_VERSION ------------ ------------- -------------- --------------- CEBC0003 PTTLRG0M 0 255
No subsequent Alters to objects in this tablespace will be allowed, and any attempt to do so will result in a SQLCode of -4702.
After executing the Reorg Tablespace and Modify Recovery utilities, CURRENT_VERSION and OLDEST_VERSION are equal:
DBNAME NAME OLDEST_VERSION CURRENT_VERSION ---------- --------- ---------------- ---------------- CEBC0003 PTTLRG0M 255 255
The following is the result of another Alter committed against an object in tablespace PTTLRG0M after the Reorg Tablespace and Modify Recovery utilities were executed to recycle the version numbers:
DBNAME NAME OLDEST_VERSION CURRENT_VERSION ---------- --------- ---------------- ---------------- CEBC0003 PTTLRG0M 255 1
The value of CURRENT_VERSION for tablespace PTTLRG0M will now continue to increment from 1 to 254 as Alters In Place are committed. When the value of CURRENT_VERSION reaches 254, version numbers must again be recycled.
Example: The Value of the CURRENT_VERSION Column Is One Less Than The Value of the OLDEST_VERSION Column
For the following example, assume that shared tablespace PTAMSG01 was at its initial version (version 0), and that several Alters In Place were committed against multiple tables contained therein:
Initial version (version 0) of PTAMSG01:
DBNAME NAME OLDEST_VERSION CURRENT_VERSION ---------- ---------- -------------- --------------- CEBC0003 PTAMSG01 0 0
After 108 committed Alters In Place to various tables within PTAMSG01:
DBNAME NAME OLDEST_VERSION CURRENT_VERSION ---------- ---------- -------------- --------------- CEBC0003 PTAMSG01 0 108
Now assume that the Reorg Tablespace and Modify Recovery utilities were executed against tablespace PTAMSG01 when the CURRENT_VERSION was 108--prior to reaching the maximum of 255.
As a result of executing these utilities, the values for OLDEST_VERSION and CURRENT_VERSION were both set to 108:
DBNAME NAME OLDEST_VERSION CURRENT_VERSION ------------ --------- -------------- -------------- CEBC0003 PTAMSG01 108 108
The value of CURRENT_VERSION for this tablespace then continued to increment from 108 to 255--and then onward to 107 (below).
At a value of 107, the value of CURRENT_VERSION was one less than the OLDEST_VERSION and execution of the Reorg and Modify Recovery utilities was mandatory.
DBNAME NAME OLDEST_VERSION CURRENT_VERSION ------------ --------- --------------- --------------- CEBC0003 PTAMSG01 108 107
Any attempt to alter another table in tablespace PTAMSG01 while at a CURRENT_VERSION of 107 would have resulted in SQLCode -4702 because the value of CURRENT_VERSION was one less than the value of OLDEST_VERSION.
Working with DB2 Tablespace Versioning and PeopleSoft UpgradesThis section covers these key issues related to DB2 tablespace versioning and PeopleSoft upgrades:
Avoiding SQL code -4702.
Ensuring optimal performance for Data Conversion steps.
Avoiding SQL Code -4702
An Alter Without Deletes script executes a significant number of alters. Although the PeopleTools alter processing for DB2 zOS was designed to prevent DB2 from creating an excessive number of tablespace versions by carefully controlling the manner in which table alters are committed per tablespace, it is possible that DB2 may still create the maximum number of tablespace versions when running the Alter Without Deletes script if there are shared tablespaces already close to the maximum 255 version numbers.
To mitigate the possibility of the Alter Without Deletes script stopping due to SQL code -4702, run the following query prior to the Alter Without Deletes step, and run the Reorg Tablespace and Modify Recovery utilities accordingly for any tablespaces that may be close to the maximum allowed version number (either the CURRENT_VERSION is equal to 255, or CURRENT_VERSION is one less than OLDEST_VERSION, as previously explained).
SELECT NAME, DBNAME, OLDEST_VERSION, CURRENT_VERSION FROM SYSIBM.SYSTABLESPACE WHERE CREATOR = '<authid of the owner of the tablespace>' AND CURRENT_VERSION > 0;
Then continue with the Alter Without Deletes script as documented.
If you run the Alter Without Deletes script manually (outside of the Change Assistant) using a tool, such as the DB2 Command Line Processor, Command Editor, SPUFI, and so on, disable the auto-commit feature. Change Assistant disables auto-commit when it invokes the Command Line Processor.
Ensuring Optimal Performance For Data Conversion Steps
While the PeopleTools alter processing for DB2 zOS was designed to prevent DB2 from creating excessive tablespace versions, you should still expect some shared tablespaces in your environment to become multi-versioned as a result of the normal execution of the Alter Without Deletes step. Until you reorganize, you may notice:
performance of dynamic SQL statements executed against a tablespace with multiple versions may suffer.
any indices in RBDP may be ignored by the DB2 optimizer.
To ensure optimal performance, we strongly recommend that you run the Reorg Tablespace (Index All) utility and the Modify Recovery utility to reformat data rows into the format described by the most current tablespace version for any tablespaces with several versions, after the Alter steps (Alter With/Without Deletes) have completed and prior to beginning the Upgrade Data Conversion steps.
For more details regarding the use of the DB2 zOS Reorg Tablespace and Modify Recovery utilities, refer to your IBM DB2 documentation.
See IBM DB2 UDB for z/OS Utility Guide and Reference