Updates to IBM® DB2® Utilities Enhancement Tool for z/OS® V2.2 User's Guide (SC19-3417-02)

Product documentation


Abstract

Updates that apply to IBM® DB2® Utilities Enhancement Tool for z/OS® V2.2 User's Guide (SC19-3417-02)

Content

----------------------------------------------------------------------------------------------------------------------------

Update 6
Date of change: February 28, 2013
Topic: Multiple
Change description:

In APAR PM77500, an enhancement was made to support the IBM DB2 High Performance Unload Utility (HPU) date, time, and timestamp formats for the LOAD utility. The following documentation changes were made to support the enhancement.

- In the chapter "Preparing your environment for DB2 Utilities Enhancement Tool":

The section "Software prerequisites" has been updated with the following information:

To use the date, time, and timestamp formats enhancement for the LOAD utility, z/OS Version 1.9 or later is required. (This requirement is in addition to planning necessary for installing DB2 UET and applying APAR PM77500.)

The section "Storage requirements" has been updated with the following information:

To use the date, time, and timestamp formats enhancement for the LOAD utility, a minimum amount of storage will be required in CSA/ECSA. All other storage is allocated from private address spaces or data spaces. (This requirement is in addition to planning necessary for installing DB2 UET and applying APAR PM77500.)

- In the chapter "DB2 LOAD utility enhancements," the section "Date, time, and timestamp options" has been added with the following information:

The DB2 Utilities Enhancement Tool (DB2 UET) enhances the LOAD utility functionality to support as input the external date, time, and timestamp formats that the IBM DB2 High Performance Unload Utility (HPU) provides as output. The LOAD utility enhancement is implemented only through the use of the DB2 UET DSNUTILB intercept component. The HPU product is not required; DB2 UET simply converts HPU formats for use by the LOAD utility.

Example:
Following is an example of a standard LOAD using basic syntax:
LOAD DATA INDDN SYSREC REPLACE
SORTDEVT SYSDA
DISCARDDN DISC1
ERRDDN ABPERR
MAPDDN ABPMAP
LOG YES
INTO TABLE BACTEST.ABPPBR_TB1
(
"NAME1"
POSITION( 00004:00004) CHAR(00001)
, "NAME2"
POSITION( 00006:00017) VARCHAR
, "NAME3"
POSITION( 00019:00028) DATE EXTERNAL
, "NAME4"
POSITION( 00079:00082) TIME EXTERNAL
, "NAME5"
POSITION( 00226:00244) TIMESTAMP EXTERNAL
)

To use the extended date, time, and timestamp values, specify the extended value in an INTO-TABLE specification for the LOAD utility in place of the DATE EXTERNAL, TIME EXTERNAL, and TIMESTAMP EXTERNAL options. The formats are as follows:

DATE_A (format MM-DD-YYYY)
DATE_B (format MM-DD-YY)
DATE_C (format YYYY-MM-DD)
DATE_D (format YY-MM-DD)
DATE_E (format DD-MM-YYYY)
DATE_F (format DD-MM-YY)
DATE_G (format YYYY-DDD)
DATE_H (format YY-DDD)
DATE_I (format MMDDYYYY)
DATE_J (format MMDDYY)
DATE_K (format YYYYMMDD)
DATE_L (format YYMMDD)
DATE_M (format DDMMYYYY)
DATE_N (format DDMMYY)
DATE_O (format YYYYDDD)
DATE_P (format YYDDD)
TIME_A (format HH.MM.SS)
TIME_B (format HH.MM)
TIME_C (format HH.MM AM)
TIME_D (format HHMMSS)
TIME_E (format HHMM)
TIMESTAMP_A (format YYYY-MM-DD-HH.MM.SS)
TIMESTAMP_C (format YYYYMMDDHHMMSS)
TIMESTAMP_D (format YYMMDDHHMMSS)

Example:
Following is an example that shows LOAD syntax using the extended formats:
LOAD DATA INDDN SYSREC REPLACE
SORTDEVT SYSDA
DISCARDDN DISC1
ERRDDN ABPERR
MAPDDN ABPMAP
LOG YES
INTO TABLE BACTEST.ABPPBR_TB1
(
"NAME1"
POSITION( 00004:00004) CHAR(00001)
, "NAME2"
POSITION( 00006:00017) VARCHAR
, "NAME3"
POSITION( 00019:00028) DATE_A
, "NAME4"
POSITION( 00079:00082) TIME_A
, "NAME5"
POSITION( 00226:00244) TIMESTAMP_A
)


Restrictions:

This enhancement only supports padded records with the POSITION parameter defined in the field definitions.

Variable-length timestamps are not supported (TIMESTAMP_B, TIMESTAMP_E, TIMESTAMP_F).

Considerations:

* Before the LOAD utility executes, DB2 UET converts the extended LOAD syntax to the following formats:
o All dates are converted to DB2 ISO format YYYY-MM-DD.
o HH:MM:SS (PM time is converted to military time.)
o HH.MM PM (Military time cannot be entered because it converts to military time.)

* Two-digit years (that is, MM-DD-YY) are expanded to four digits using 100 Year Fixed Window, 1930-2029.

* DB2 UET performs no validation of data. Ensure that you specify the correct data for the control card:
o Leading blanks are not allowed in the date, time, or timestamp. For example, for the date 1-18-2012, you must enter 01-18-2012.
o The valid Julian date range is 1 through 365 (366 for leap years).

* Julian date conversion uses packed decimal operations.

* Conversions are performed after CONSTANT and VALUEIF substitution. Values that you specify for substitution using the CONSTANT and VALUEIF options must match exactly the original data in the record. For example, the correct format for DATE_I is MMDDYYYY. The following example shows the correct format for the VALUEIF value. Any other DATE format for the VALUEIF value in this example would be incorrect.

, "NAME11"
POSITION( 00095:00102) DATE_I
NULLIF(00094)=X'FF'
VALUEIF "NAME11" = '12312999'


* DEFAULTIF criteria on which to match must be specified in ISO format. If you specify "COLUMN NAME" (instead of the exact position) for SELECTION_CRITERIA, you must specify the entire value for comparison, including all trailing zeroes for the timestamp.

----------------------------------------------------------------------------------------------------------------------------

Update 7
Date of change: February 28, 2013
Topic: Multiple
Change description:

Several documentation changes were made for the various usability fixes delivered in APAR PM77500:
- Clarified partition range specification
- Added new messages

1. Clarified partition range specification: Only a colon can be used to separate partition ranges.
All occurrences were changed to read as follows:

A partition number can be an integer from 1 through 4096 for DB2 Version 8 or later. Specify multiple partitions as follows:
- A series of individual partitions: integer1,integer2,integer3
- A range of partitions: integer1:integer3 (The first value must be less than the second value.)
- Both ranges and individual partitions: integer1,integer2:integer4,integer5 (The first value must be less than the second value.)

2. New messages
ABPP9942E - Invalid specification for keyword <keyword>.
Explanation: The specified partition numbers are not valid, the partition numbers are outside the valid range (from 1 to 4096), or in the specified range, the first value is lower than the second value.
User response: Correct the specified partition numbers. For information about specifying partition numbers, see the product documentation. After you make the correction in the POLICY, restart the started task.

ABPP9863E - Multiple <USE_PRACTICE> elements were specified within one <DB2SYSTEM> element, DB2 SSID: <db2_ssid>.
Explanation: In the DSNUTILB policy, multiple <USE_PRACTICE> elements were specified within one <DB2SYSTEM> element. Each <DB2SYSTEM> element can contain only one <USE_PRACTICE> element.
User response: Make sure that all <DB2SYSTEM> elements contain only one <USE_PRACTICE> element.

ABPP9864E - <SUBSTITUTE> or <FAIL> attributes must be specified for <VALUE> attribute in <SYNTAX> element.
Explanation: In the DSNUTILB intercept policy, the <VALUE> attribute in the <SYNTAX> element was specified without the required <SUBSTITUTE> or <FAIL> attribute.
User response: Make sure that the <VALUE> attribute in the <SYNTAX> element was specified with the <SUBSTITUTE> or <FAIL> attribute.

ABPP9865E - Attributes are not specified in <SYNTAX> element.
Explanation: In the DSNUTILB intercept policy, the <SYNTAX> element was specified with no attributes; however, at least one attribute is required.
User response: Make sure that the <SYNTAX> element contains at least one attribute. For information about the available attributes, see the section about the DSNUTILB intercept policy in the product documentation.

ABPP9866E - <VALUE> attribute must be specified for <SUBSTITUTE> or <FAIL> attributes in <SYNTAX> element.
Explanation: In the DSNUTILB policy, the <SUBSTITUTE> or <FAIL> attributes in the <SYNTAX> element were specified without specifying the <VALUE> attribute.
User response: Make sure that the <SUBSTITUTE> or <FAIL> attributes are specified in the <SYNTAX> element when you specify the <VALUE> attribute.

ABPP9944E - Value length of attribute <attributeName> is more than <attributeLength> characters.
Explanation: In the DSNUTILB policy, the length of the specified attribute value exceeds the maximum allowable length.
User response: Correct the attribute value. For more information about the attribute, see the product documentation.


----------------------------------------------------------------------------------------------------------------------------

Update 8
Date of change: February 28, 2013
Topic: Multiple
Change description:

In APAR PM80231, an enhancement was made to substitute IBM DB2 High Performance Unload for z/OS (HPU) for the DB2 UNLOAD utility. The following documentation changes were made to support the enhancement.

- In the chapter "Preparing your environment for DB2 Utilities Enhancement Tool":

The section "Software prerequisites" has been updated with the following information:

To substitute the IBM DB2 High Performance Unload for z/OS (HPU) for the DB2 UNLOAD utility, HPU must be installed on the DB2 subsystem on which the UNLOAD is to be executed. (This requirement is in addition to planning necessary for installing DB2 UET and applying APAR PM80231.)

- In the chapter "The DSNUTILB intercept policy," the section "Reading and understanding the policy", the subsection "<UTILITY>" has been updated with the following information:

The attribute <REPLACE> has been added to the element <UTILITY> to support the UNLOAD utility enhancement.

Substituting HPU for the UNLOAD utility
To control which objects are targets for use of HPU, add the attribute <REPLACE> to the element <UTILITY> and specify HPU.
Values: The only value is HPU.
Default value: HPU
Required?: No

Example:

<PRACTICE NAME="STANDARDS_1">
<UTILITY NAME="UNLOAD" REPLACE=”HPU”/>
.
.
</UTILITY>
</PRACTICE>



- The new chapter "DB2 UNLOAD utility enhancement" has been added and includes the following information.

DB2 UET can substitute the IBM DB2 High Performance Unload for z/OS (HPU) product for the DB2 UNLOAD utility.

After you implement the enhancement, the UNLOAD utility is changed to an HPU unload with no alterations to the syntax other than those that you specify in element <MONITOR>. By using a policy that indicates that DB2 UET is to replace DB2 UNLOAD for HPU, DB2 UET treats the syntax as a native DB2 UNLOAD utility. Normal syntax validation occurs through DSNUTILB. DB2 UET invokes HPU, and processing is controlled by HPU options.

Considerations:

* Any utility syntax that is used must comply with both native DB2 UNLOAD utility and High Performance Unload (HPU) utility syntax requirements.

* DB2 UET does not convert syntax to HPU syntax.

* For more information about DB2 High Performance Unload for z/OS (HPU), see the following Web page:

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2tools.inz42.doc.ug%2Finzhome.htm

Implementing the UNLOAD utility enhancement:
To use the enhancement, complete the following steps:

1. Install IBM DB2 High Performance Unload for z/OS (HPU) on the DB2 subsystem on which unload utilities are to be substituted for HPU.

2. Add the HPU SINZLINK library to either the UNLOAD job STEPLIB (see Figure 1) or the DB2 UET started task (STC) STEPLIB (see Figure 2).

Note: If you add HPU SINZLINK to the DB2 UET STC STEPLIB, you do not need to modify job JCL to substitute HPU for the UNLOAD utility.

3. Provide a policy that includes attribute <REPLACE> on element <UTILITY>, as shown in Figure 3.

Examples:

Figure 1. UNLOAD utility JCL example

Note: Any utility syntax that is used must comply with both native DB2 UNLOAD utility and HPU utility syntax requirements.

//******************************************************************
//* UNLOAD
//******************************************************************
//*
//UNLOAD EXEC PGM=DSNUTILB,
// PARM=’ssid,utilid'
//*
//* INVOKE DB2 UET INTERCEPT
//*
//STEPLIB DD DISP=SHR,DSN=DSN.SDSNLOAD
//*
//UNLDD DD DSN=unload.dsn,
//* UNIT=SYSDA,
//* SPACE=(CYL,(2,2)),
// DISP=(NEW,CATLG,CATLG)
//*
//SYSIN DD *
UNLOAD TABLESPACE DBNAME.TSNAME
UNLDDN UNLDD
/*
//SYSPRINT DD SYSOUT=*


Figure 2. DB2 UET started task (STC) STEPLIB example

//ABPSTC EXEC PGM=ABPSINIT,TIME=1440,REGION=0M
//*
//STEPLIB DD DISP=SHR,DSN=ABP.SAPLOAD
// DD DISP=SHR,DSN=DSN.SDSNLOAD
// DD DISP=SHR,DSN=HPU.SINZLINK
.


Figure 3. Policy example

<PRACTICE NAME="STANDARDS_1">
<UTILITY NAME="UNLOAD" REPLACE="HPU">
.
.
</UTILITY>
</PRACTICE>

<POLICY>
<DB2SYSTEM SSID="LAA" ACTION="MONITOR_UTILITY">
<USE_PRACTICE NAME="STANDARDS_1"/>
<INCLUDE>
<RULE DATABASE="PROD"/>
<RULE TABLESPACE="BIG%"/>
</INCLUDE>
</DB2SYSTEM>
</POLICY>

----------------------------------------------------------------------------------------------------------------------------

Update 9
Date of change: February 28, 2013
Topic: Multiple
Change description:

In APAR PM77500 and APAR PM80231, updates were made to support DB2 V11 parameters MAPPINGTABLE and MAPPINGDATABASE, and LRSN enhancements in mapping tables in DB2 V11. The following documentation changes were made to support the updates.

- In the chapter " DB2 Utilities Enhancement Tool overview," in the section "Summary of changes," the following information has been added:

DB2 UET supports the following features of DB2 Version 11:

* Expanded RBA/LRSN values to 10 bytes, and LRSN enhancements in mapping tables.
- The following examples were updated:
- Display Threads Detail panel example
- Threads Canceled Unit of Recovery Report example
- All Active Threads Unit of Recovery Report example

- DB2 UET creates the mapping table with the LRSN column defined as a CHAR(10) when the DB2 version is later than V10.

* MAPPINGTABLE and MAPPINGDATABASE parameters
In DB2 V11, the MAPPINGTABLE and MAPPINGDATABASE keywords are mutually exclusive. Therefore, after a subsystem has been migrated to DB2 V11 or later, if MAPPINGDATABASE is specified, then DB2 UET does not add MAPPINGTABLE to the syntax.

- In the chapter "DB2 REORG TABLESPACE utility enhancements" introduction, the following information has been added:

* The DB2 UET REORG TABLESPACE enhancement enables DB2 UET to automatically add the MAPPINGTABLE option on the SHRLEVEL CHANGE parameter, according to the DB2 REORG TABLESPACE syntax rules. In DB2 V11, the MAPPINGTABLE and MAPPINGDATABASE parameters are mutually exclusive. Therefore, after a subsystem has been migrated to DB2 V11 or later, if MAPPINGDATABASE is specified, then DB2 UET does not add MAPPINGTABLE to the syntax.


- In the chapter "Canceling threads by using the ISPF interface," in the section "Viewing detailed information for a thread," the Display Thread Detail panel has been updated as follows.

DB2 Utilities Enhancement Tool Display Thread Detail 18:53:42
Command ===>

Commands: INFO REFRESH ABPID . . . : ABP1
DB2 system. : DBP1
Cancel thread . . . NO (Yes/No) User ID . . : PDABCD
Escalated cancel. . NO (Yes/No)

-------------------------------- Thread Detail -------------------------------
More: +
Thread token . . . . : 19 In-DB2 elapsed time. : 00:00:07.060944
Elapsed time . . . . : 00:00:07.06 In-DB2 CPU time. . . : 00:00:00.067937
Status . . . . . . . : T Commit count . . . . : 1
Active . . . . . . . : In-DB2 Abort count. . . . . : 0
Auth ID. . . . . . . : PDUSER1 SQL calls. . . . . . : 362
Original auth ID . . : PDUSER1 GETPAGE count. . . . : 1790
Job name . . . . . . : PGMAJOB Correlation ID . . . : PGMAPLAN
ASID . . . . . . . . : 012E Connection ID. . . . : DB2CALL
UR start date. . . . : 04/03/2012 Connection type. . . : CAF
UR start time. . . . : 15:39:52.270 Log pages. . . . . . : 1
UR state . . . . . . : Log records. . . . . : 946
UR elapsed time. . . : 07:13:50.593
UR log start RBA . . : 000000000000B6F60C89
UR log end RBA . . . : 000000000000B6F60FA6

Plan name. . . . . . : PGMAPLAN
Member name. . . . . :
Program name . . . . : PROGRAMA >
Package name . . . . : PROGRAMA >
Collection ID. . . . : PROGRAMA >
DDF location . . . . : RS123456 >

DDF IP port. . . . . :
DDF IP address . . . :


- In the chapter "Canceling threads by using the batch interface," in the section, "Checking batch job output," in the subsection "Reports," "All Active Threads Unit of Recovery" and "Threads Canceled Unit of Recovery" have been updated as follows:

** All Active Threads Unit of Recovery Report for CANCEL_THREADS Request=1 PAGE=1 **
____________________________________________________________________________________________

SEQNO CAN TTOKEN PLANNAME AUTHID UR_STRT_DT UR_STRT_TIME UR_STATE UR_ELAP_TIME UR_LOG_SRBA/ LOG_PAGES LOG_RECS
UR_LOG_ERBA
----- --- ------ -------- -------- ========== --------------- -------- --------------- -------------------- --------- ---------
1 987 DK27PLN ABPSTC 07/06/2012 08:12:14.254096 00:00:16.223643 0000000000003080A908 1 42
0000000000003080AD05

2 988 DK27PLN ABPSTC 07/06/2012 08:12:14.259392 00:00:16.220520 0000000000003080B72E 1 82
0000000000003080B9AF

** END OF REPORT **


** Threads Canceled Unit of Recovery Report for CANCEL_THREADS Request=1 PAGE=1 **
__________________________________________________________________________________________

SEQNO TTOKEN PLANNAME AUTHID UR_STRT_DT UR_STRT_TIME UR_STATE UR_ELAP_TIME UR_LOG_SRBA/ LOG_PAGES LOG_RECS
UR_LOG_ERBA
----- ------ -------- -------- ========== --------------- -------- --------------- -------------------- --------- ---------
1 387 ABPDKCYP PDKILI 07/10/2012 13:18:12.187296 00:00:11.044358 000000000002C58011DF 50632 2000152
000000000002D1DC8F28

** END OF REPORT **


- The following messages support the updated reports:

ABPB6545
SEQNO CAN TTOKEN PLANNAME AUTHID UR_STRT_DT UR_STRT_TIME UR_STATE UR_ELAP_TIME UR_LOG_SRBA/ LOG_PAGES LOG_RECS
Explanation: This message is the first line of the column header in the All Active Threads Unit of Recovery Report. This message appears in the report without the message identifier.
User response: No action is required.

ABPB6546
UR_LOG_ERBA
Explanation: This message is the second line of the column header in the All Active Threads Unit of Recovery Report. This message appears in the report without the message identifier.
User Response: No action is required.

ABPB6547
----- --- ------ -------- -------- ========== --------------- -------- --------------- -------------------- --------- ---------
Explanation: This message is the last line of the column header in the All Active Threads Unit of Recovery Report. This message appears in the report without the message identifier.
User response: No action is required.

ABPB6548
data_entry
Explanation: This message is a data entry in the All Active Threads Unit of Recovery Report. It is issued when Unit of Recovery information is available for the specified thread. (Otherwise, ABPB6533I is issued.) This message corresponds to the column header message ABPB6545I and appears in the report without the message identifier.

User response: No action is required.
ABPB6549
URLERBA
Explanation: This message is the second line of a data entry in the All Active Threads Unit of Recovery Report. It is issued when Unit of Recovery information is available for the specified thread. (Otherwise, ABPB6533I is issued.) This message corresponds to the column header message ABPB6546I and appears in the report without the message identifier.
User response: No action is required.

ABPB6550
SEQNO TTOKEN PLANNAME AUTHID UR_STRT_DT UR_STRT_TIME UR_STATE UR_ELAP_TIME UR_LOG_SRBA/ LOG_PAGES LOG_RECS
Explanation: This message is the first line of the column header in the Threads Canceled Unit of Recovery Report. This message appears in the report without the message identifier.
User response: No action is required.

ABPB6551
UR_LOG_ERBA
Explanation: This message is the second line of the column header in the Threads Canceled Unit of Recovery Report. This message appears in the report without the message identifier.
User response: No action is required.

ABPB6552
----- --- ------ -------- -------- ========== --------------- -------- --------------- -------------------- --------- ---------
Explanation: This message is the last line of the column header in the Threads Canceled Unit of Recovery Report. This message appears in the report without the message identifier.
User response: No action is required.

ABPB6553
data_entry
Explanation: This message is a data entry in the Threads Canceled Unit of Recovery Report. It is issued when Unit of Recovery information is available for the specified thread. (Otherwise, ABPB6516I is issued.) This message corresponds to the column header message ABPB6550I and appears in the report without the message identifier.
User response: No action is required.

ABPB6554
URLERBA
Explanation: This message is the second line of a data entry in the Threads Canceled Unit of Recovery Report. It is issued when Unit of Recovery information is available for the specified thread. (Otherwise, ABPB6516I is issued.) This message corresponds to the column header message ABPB6551I and appears in the report without the message identifier.
User response: No action is required.

Report columns
This section describes the columns in the All Active Threads Unit of Recovery Report and the Threads Canceled Unit of Recovery Report:

SEQNO: The sequence number of a report line.

CAN: Whether DB2 UET selected the thread for cancellation processing. If the thread was selected for cancellation, the field displays three asterisks (***).

TTOKEN: The thread token value. A thread token value is a unique numeric identifier that DB2 assigns to each active thread on a DB2 subsystem. A token value must be unique on a subsystem or within a data-sharing group. DB2 UET does not report information for threads that have a thread token value of zero.

PLANNAME: The name of the DB2 plan under which the thread was running.

AUTHID: The current authorization ID of the process for which the thread was running.

UR_STRT_DT: The start date (or creation date) of the UR. A UR exists only if the thread modified DB2 objects or resources. The date is in the format MM/DD/YYYY, where MM is month, DD is day, and YYYY is year.

UR_STRT_TIME: The start time (or creation time) of the UR. This time is in the format HH:MM:SS:xxx, where HH is hours, MM is minutes, SS is seconds, and xxx is milliseconds.

UR_STATE: The status of the UR, which can be one of the following values:
• INFLIGHT: The UR is an active, inflight unit of work.
• IN PHAS1: The UR is currently in phase 1 of commit processing.
• IN PHAS2: The UR is currently in phase 2 of commit processing.
• COMMITED: Commit processing has ended.
• IN ABORT: The UR is being aborted. Rollback processing has begun but has not yet completed.
• ABORTED: The UR has been aborted. Rollback processing is complete.
• INDOUBT: The UR is in the in doubt state.

UR_ELAP_TIME: The amount of time that elapsed between the start of the UR and the retrieval of information for this report. This time is in the format HH:MM:SS:xxx, where HH is hours, MM is minutes, SS is seconds, and xxx is milliseconds.

UR_LOG_SRBA: The log start relative byte address (RBA) for the active UR.

UR_LOG_ERBA: The log end RBA for the active UR. This value is the RBA of the last log record written by the active UR.

LOG_PAGES: The number of physical log pages that contain information for the current UR. If no UR exists or no UR information is available for a thread, this field displays a zero.

LOG_RECS: The number of log records that were created for the active UR. A single log page is composed of multiple log records. If no UR exists or no UR information is available for a thread, this field displays a zero.

Publication number

SC19-3417-02

Copyright date

2012

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 Tools for z/OS
DB2 Utilities Enhancement Tool

Software version:

2.2.0

Operating system(s):

z/OS

Reference #:

7037975

Modified date:

2013-03-01

Translate my page

Machine Translation

Content navigation