Database Administration Change Requests (DARTS)

The Phoenix Logical DBA team continues to notice a large number of DARTS requests (Database Administration Request Tracking System) that contain either missing, incomplete, or inaccurate information. We are also seeing a high number of last minute requests with little to no lead time provided for implementation. This is causing a fair amount of problems and frustration for both the Phoenix Logical DBA and HP Physical DBA teams who are trying to complete these requests in a timely manner, and meet contracted SLA levels for such requests.

It is extremely important that each DARTS request contains a complete set of specifications for the changes that are being requested, with accurate information provided for items such as server names, database names, table names, etc. If there are any scripts that are available to facilitate the request, they should be included as file attachments to the DARTS request.

The reason why we need a complete and accurate set of specifications for each request is threefold:

• The DARTS database is used as a repository for all database change requests for all databases in all environments. There have been times where the DARTS database has been used to track the history of database changes through environments, and it is important that the information about these changes be as complete and accurate as possible.
• We are working with an increasingly leveraged staff of HP Physical DBA's, many of whom are fairly new to the Phoenix account and are not familiar with our applications or our database environments. The leveraged support model dictates that the HP leveraged personnel work off of tickets (e.g. DARTS, for database requests) with complete and detailed specifications.
• All DARTS requests go through the Phoenix Logical DBA team first for review and assessment. The more time we have to spend on tracking down or correcting missing, incomplete, or inaccurate information negatively impacts how quickly we can turn the requests around and requeue them to the HP Physical DBA's for scheduling and implementation.

Another point that also needs be reinforced is that all Model Office and Production DARTS changes must be accompanied by an RFC number. Logical DBA will verify that the RFC number provided aligns with the nature of the request and the requested date/time for implementation of the change. Physical DBA will verify that the RFC is fully approved before completing the request. Our recommendation is that the DARTS request should be submitted as soon as the RFC is created in order to provide sufficient lead time to the DBA team to schedule completion of the request.

The Phoenix Logical DBA team will be strictly enforcing this policy of requiring complete and accurate specifications for all new DARTS requests that are submitted. Any request that is incomplete or inaccurate will be routed back to the originator with a request for additional information, potentially causing a delay in the scheduling and implementation of the request.

Please let me know if you have any questions or concerns about this.

Please also forward this to any offshore teams that may support your applications.

DB2 z/OS: Loading and Unloading Data Using CA Utilities

Loading and Unloading Data

There are two third-party utilities for loading and unloading data from DB2 tables that are available for use at Phoenix. These utilities are the "Fast Load" and "Fast Unload" utilities from Computer Associates. These utilities provide better performance and functionality over the native DB2 load and unload utilities/programs.

JCL for Fast Unload

//STEP001 EXEC PGM=PTLDRIVM,PARM='EP=UTLGLCTL/DB0G',
//        REGION=6M
//STEPLIB  DD DISP=SHR,DSN=PDB2.PLAT.TEST.LOADLIB
//         DD DISP=SHR,DSN=SYSD1.DB0G.SDSNLOAD
//PTILIB   DD DISP=SHR,DSN=PDB2.PLAT.TEST.LOADLIB
//         DD DISP=SHR,DSN=SYSD1.DB0G.SDSNLOAD
//PTIPARM  DD DISP=SHR,DSN=PDB2.PLAT.TEST.PARMLIB
//PTIXMSG  DD DISP=SHR,DSN=PDB2.PLAT.TEST.XMESSAGE
//SYSREC01 DD DSN=YOUR.UNLOAD.DSN,
//            DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,
//            SPACE=(CYL,(50,50),RLSE)
//SYSCTL01 DD DSN=YOUR.OUTPUT.PDS(LOADCNTL),DISP=SHR
//PTIMSG   DD SYSOUT=*
//PTIIMSG  DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//ABNLIGNR DD DUMMY
//SYSOUT   DD SYSOUT=*
//SYSIN    DD * 
FASTUNLOAD LOAD-CONTROL FASTLOAD IO-BUFFERS 60 
OUTPUT-FORMAT DSNTIAUL SHRLEVEL IGNORE EXCP YES 
INPUT-FORMAT TABLE 
DISPLAY-STATUS 100000,INTERVAL 
ESTIMATED-ROWS 5000000 
SELECT * FROM YOUR.TABLE;
Notes on Using Fast Unload

The unload data will be written to the dataset defined by the SYSREC01 ddname. You do not have to specify DCB information such as LRECL or BLKSIZE; the utility will do that for you and allocate the dataset with the appropriate DCB information. The unload utility will create load syntax for loading the data back into the table. The load syntax will be stored in the PDS member specified by the SYSCTL01 ddname. You can restrict the columns and/or the rows to be unloaded with the SELECT statement, which must be the last line of the Fast Unload syntax in the SYSIN ddname. Please note that the FROM clause in the SQL statement should only reference one table, i.e. no joins. There are many features and options that are provided by the Fast Unload utility.

The examples shown here are the most common options that you may need to use. Please contact your favorite DBA if you would like more information about the other options and features for the Fast Unload utility.

Unloading From an Image Copy Dataset
One of the more powerful features of the Fast Unload utility is its ability to optionally unload data from an image copy dataset instead of directly from the DB2 table itself. This is ideal if you have a need to extract data from your table as it looked at a previous point in time, or if you want to move data from one environment to another, such as production to model office, without directly impacting the production data. Here are two examples of how to unload data from an image copy dataset:

Example 1 (Unloading from the most recent full image copy)

//SYSIN DD *
FASTUNLOAD LOAD-CONTROL FASTLOAD IO-BUFFERS 60
OUTPUT-FORMAT DSNTIAUL SHRLEVEL REFERENCE EXCP YES
INPUT-FORMAT IMAGECOPY LAST-COPY YES
DISPLAY-STATUS 100000,INTERVAL
ESTIMATED-ROWS 5000000
SELECT * FROM DCMSU.AGTHIST ;

In this example, specifying LAST-COPY YES will tell the Fast Unload utility to query the DB2 catalog to determine the dataset name of the most recent full image copy for the table being unloaded. Fast Unload will then dynamically allocate that dataset to the job and perform the unload.

Example 2 (Unloading from a previous full image copy)

//SYSIN DD *
FASTUNLOAD LOAD-CONTROL FASTLOAD IO-BUFFERS 60
OUTPUT-FORMAT DSNTIAUL SHRLEVEL REFERENCE EXCP YES
INPUT-FORMAT IMAGECOPY LAST-COPY NO
DISPLAY-STATUS 100000,INTERVAL
ESTIMATED-ROWS 5000000
SELECT * FROM CRPACCTU.MOVE_MERGE ;
//SYSIMAG DD DSN=YOUR.IMAGE.COPY.DSN,DISP=OLD

In this example, specifying LAST-COPY NO will tell the Fast Unload utility to unload the table from the image copy dataset specified by the SYSIMAG ddname.

JCL for Fast Load

//STEP001 EXEC PGM=PTLDRIVM,PARM='EP=UTLGLCTL/DB0G',
//             REGION=6M
//STEPLIB  DD DISP=SHR,DSN=PDB2.PLAT.TEST.LOADLIB
//         DD DISP=SHR,DSN=SYSD1.DB0G.SDSNLOAD
//PTILIB   DD DISP=SHR,DSN=PDB2.PLAT.TEST.LOADLIB
//         DD DISP=SHR,DSN=SYSD1.DB0G.SDSNLOAD
//PTIPARM  DD DISP=SHR,DSN=PDB2.PLAT.TEST.PARMLIB
//PTIXMSG  DD DISP=SHR,DSN=PDB2.PLAT.TEST.XMESSAGE
//PTIMSG   DD SYSOUT=*
//PTIIMSG  DD SYSOUT=*
//SYSULD   DD DSN=YOUR.UNLOAD.DSN,DISP=OLD
//SYSERR   DD DSN=YOUR.SYSERR.DSN,DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(5,5),RLSE),UNIT=SYSDA
//SYSUDUMP DD SYSOUT=*
//ABNLIGNR DD DUMMY
//SYSOUT   DD SYSOUT=*
//SYSIN    DD *
FASTLOAD INDDN SYSULD
INPUT-FORMAT SEQ
OUTPUT-CONTROL BUILD
RESUME NO REPLACE
EBCDIC
INTO TABLE DCMSU.AGTHIST
(
LASTMAINT POSITION( 1: 26 ) TIMESTAMP EXTERNAL ( 26),
SOURCE_CORP POSITION( 27: 29 ) CHAR ( 3) ,
SOURCE_DIV POSITION( 30: 39 ) CHAR ( 10) ,
SOURCE_DEPT POSITION( 40: 49 ) CHAR ( 10) ,
SOURCE_SUB_DEPT POSITION( 50: 59 ) CHAR ( 10) ,
SOURCE_COST_CTR POSITION( 60: 69 ) CHAR ( 10) ,
TARGET_CORP POSITION( 70: 72 ) CHAR ( 3) ,
TARGET_DIV POSITION( 73: 82 ) CHAR ( 10) ,
TARGET_DEPT POSITION( 83: 92 ) CHAR ( 10) ,
TARGET_SUB_DEPT POSITION( 93: 102 ) CHAR ( 10) ,
TARGET_COST_CTR POSITION( 103: 112 ) CHAR ( 10) ,
AXRATIO_IND POSITION( 113: 113 ) CHAR ( 1) ,
GXRATIO_IND POSITION( 114: 114 ) CHAR ( 1) ,
SOURCE_IND POSITION( 115: 115 ) CHAR ( 1) ,
UPDTBY POSITION( 116: 123 ) CHAR ( 8)
)

Notes on Using Fast Load

1. The data to be loaded should be located in the dataset defined by the SYSULD ddname. This should correspond to the dataset defined by the SYSREC01 ddname in the Fast Unload job.

2. The Fast Load syntax, defined by the SYSIN ddname, will be automatically generated by the Fast Unload job (SYSCTL01 ddname). Most likely, the only change you may need to make would be to change the name of the table you are loading. This would primarily need to be done in a situation where you are moving data from one environment to another, such as from production to test, where the physical table names are different (different creator).

3. As with the Fast Unload utility, there are many features and options that are provided by the Fast Unload utility. The examples shown here are the most common options that you may need to use. Please contact your favorite DBA if you would like more information about the other options and features for the Fast Unload utility.


Operational Considerations for Running Fast Unload and Fast Load

Phoenix is only licensed to run the CA DB2 utilities on the R44 CPU. This means that the software is only licensed to run on the MVS3 LPAR. If you attempt to run the Fast Unload or Fast Load job on any other LPAR, you will receive a RACF error, as shown here:

15.39.53 JOB04605 ICH408I USER(TECWRG ) GROUP(TECHSUPT) NAME(GALLAGHER,WILLIAM M ) PTLDRIVM CL(PROGRAM )
INSUFFICIENT ACCESS AUTHORITY
ACCESS INTENT(READ ) ACCESS ALLOWED(NONE )

To ensure that you run on the correct OS/390 LPAR, you will need to include a SYSAFF card in your JCL. This card should appear immediately after your job card, and would look like this:

/*JOBPARM SYSAFF=(MVS3)

Displaying and Terminating CA Utilities

If a Fast Unload or Fast Load job abends, it will leave the utility in a "pending" status. There are ways to restart a failed utility, but often it is much easier to just terminate the utility and try running again after you have determined and resolved the cause of the abend. In order to terminate the utility, you will need to first determine the name of the utility and find out which tablespaces and/or indexes have been placed in restricted status. This can be done with the following JCL:

//STEP001 EXEC PGM=PTLDRIVM,PARM='EP=PTLDB2C/DB0G',
//             REGION=6M
//STEPLIB  DD DISP=SHR,DSN=PDB2.PLAT.TEST.LOADLIB
//         DD DISP=SHR,DSN=SYSD1.DB0G.SDSNLOAD
//PTILIB   DD DISP=SHR,DSN=PDB2.PLAT.TEST.LOADLIB
//         DD DISP=SHR,DSN=SYSD1.DB0G.SDSNLOAD
//PTIPARM  DD DISP=SHR,DSN=PDB2.PLAT.TEST.PARMLIB
//PTIXMSG  DD DISP=SHR,DSN=PDB2.PLAT.TEST.XMESSAGE
//PTIMSG   DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
-DISPLAY CA (*)
-DISPLAY DATABASE(DB76FAVU) SPACENAM(*) RESTRICT LIMIT(*)

Here is the kind of output you might get from running this job:

-DISPLAY CA (*)
DATABASE = DB76FAVU UTILID = FLUCKIG.BRUNIJL4 UTILITY = LOAD
TABLESPACE = TFAVTI ROW COUNT 0 USERID =
FLUCKIG
TIME ACCESSED = 2001-12-03 09.14.07
PARTITIONS SELECTED 000

******** 1 C A - D B 2 U T I L I T I E S ********

-DISPLAY DATABASE(DB76FAVU) SPACENAM(*) RESTRICT LIMIT(*)
DSNT360I -DB02 ***********************************
DSNT361I -DB02 * DISPLAY DATABASE SUMMARY
* RESTRICTED
DSNT360I -DB02 ***********************************
DSNT362I -DB02 DATABASE = DB76FAVU STATUS = RW
DBD LENGTH = 20180
DSNT397I -DB02
NAME TYPE PART STATUS PHYERRLO PHYERRHI CATALOG PIECE
-------- ---- ---- ------------------ -------- -------- -------- -----
TFAVTI TS UT
IFAVTI1 IX UT
IFAVTI2 IX UT
IFAVTI3 IX UT
******* DISPLAY OF DATABASE DB76FAVU ENDED **********************
DSN9022I -DB02 DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION

In this example, a job that was performing a Fast Load utility for the table contained in the DB76FAVU.TFAVTI tablespace abended. The abend left the tablespace and it's three associated indexes in "UT" (utility) status.

In order to terminate the utility and bring the tablespace and indexes back to a usable state, you would need to replace the "-DISPLAY" commands in the above JCL with the following, and rerun the JCL:

-TERM CA UTILID (FLUCKIG.BRUNIJL4)
-START DATABASE(DB76FAVU) SPACENAM(TFAVTI) ACCESS(RW)
-START DATABASE(DB76FAVU) SPACENAM(IFAVTI1) ACCESS(RW)
-START DATABASE(DB76FAVU) SPACENAM(IFAVTI2) ACCESS(RW)
-START DATABASE(DB76FAVU) SPACENAM(IFAVTI3) ACCESS(RW)

Please note that these steps will only bring the tablespace and indexes back to a reusable state for the Fast Load utility to be rerun. It will not necessarily make the table usable for normal processing as the data that is actually contained in it may have been corrupted by the abended load utility. If for whatever reason you decide that you do not want to rerun the Fast Load utility, you should contact your favorite DBA to request a restore of that tablespace to a previous image copy.

DB2 z/OS: PLAN DSNESPRR NOT AUTHORIZED FOR SUBSYSTEM DBxx AND AUTH ID xxxxxxx

This is a common problem for new users, getting into SPUFI for the first time. The default isolation level for SPUFI is RR (repeatable read), which we've observed on many occasions to cause problems as users will get into SPUFI, run a query (in RR isolation level), and leave their SPUFI session open, thus leaving locks on the table being queried.

In response to this, we have "locked down" the default DSNESPRR plan for SPUFI in order to prevent people from using it. The more appropriate plan to use would be the DSNESPCS plan, which sets the isolation level to CS (cursor stability).

If you have received the above error, the appropriate way to set you SPUFI plan to the "approved" DSNESPCS plan is as follows:

On the main SPUFI menu, select option 5 "Change Defaults". This will bring you to the "Current SPUFI Defaults" panel. Change option 2 "Isolation Level" from "RR" to "CS" and hit enter. This should take care of the problem you've experienced.