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 14
Date of change: May 23, 2014
Topic: Multiple
Change description: APAR PI04864 adds the following enhancements to the product:

- IFDISCARDS option: An enhancement has been added to the LOAD utility to prevalidate data. DB2 UET validates records in the SYSREC file against the check constraints and data types of the table that you specify in the LOAD utility syntax. If DB2 UET detects errors in the load, you can either fail the load or pause the load so that you can examine the errors. You can then decide whether to restart the load despite the errors.
- SHRLEVEL REFERENCE option: An enhancement has been added to implement support for the LOAD utility to enable LOAD REPLACE SHRLEVEL REFERENCE processing.

The following topics and messages were added or updated in support of these enhancements:
1. Topic: “Set up your environment prior to customization” in the “Preparing to customize DB2 Utilities Enhancement Tool” topic
2. Topic: “Security requirements” in the “Preparing to customize DB2 Utilities Enhancement Tool” topic
3. Topic: “Worksheets: Gathering parameter values for Tools Customizer” in the “Preparing to customize DB2 Utilities Enhancement Tool” topic
4. Topic: “Validating records before committing changes (IFDISCARDS option)” in the “Manipulating data in input records before loading (LOAD utility enhancements)” topic
5. Topic: “Loading a table space in RO access mode (SHRLEVEL REFERENCE option)” in the “Manipulating data in input records before loading (LOAD utility enhancements)” topic
6. Topic: “Messages and codes” in the “Troubleshooting” topic

1. Topic: “Set up your environment prior to customization”

Add the following software and storage requirements:

Software requirements for using features that prevalidate data before loading it (IFDISCARDS and SHRLEVEL REFERENCE options):
- DB2 V10.1 for z/OS or later
- z/OS Version 1.11 or later
- IBM Workload Manager for z/OS (WLM): Ensure that your WLM environment is set up so that DB2 UET can run the SYSPROC.DSNUTILU stored procedure.
- If the DB2 UET load modules are in the WLM STEPLIB concatenation, or if the DB2 Analytics Accelerator Loader product is installed, ensure that DB2 UET APAR PI04864 is applied and that you are using the modules that are provided with it. Specifically, verify that the compile date in the DSNUTILF module in the WLM address space is the same as the date of the module in the enhancement load library.

Storage requirements:
DFSMSdss requirements are as follows:
When performing load prevalidation and LOAD REPLACE SHRLEVEL REFERENCE processing, DB2 UET uses DFSMSdss to copy data sets from production to shadow objects. If SMS is active, you must set up SMS rules to allow the shadow data sets to reside on volumes that are allowed for DB2 objects. The underlying data sets for the shadow objects will be renamed to become the active page set for the DB2 table and index spaces; they will not be moved from the location in which SMS places them when the shadow objects are created.
The renaming or DFSMSdss COPY of DB2 VSAM objects follows the standard naming conventions for such objects:

catname.DSNDBx.dbname.psname.y0001.znnn

where y is a letter in the range A through Z. DB2 UET checks for existing data set names and uses the first available letter to rename the objects.


2. Topic: “Security requirements”

Revise as follows:

- If your site uses ACF2 to restrict TSO command use, you may need to add the TSO command that DB2 UET uses, ABPFMAIN, to the ACF2 Command Limiting table.

- Started task authorization requirements: The DB2 UET started task must run under a user ID that has SYSADM or SYSCTRL privileges; SYSOPR with MONITOR1 is no longer sufficient.

- Authorization requirements for LOAD utility enhancements: DB2 UET creates shadow objects for use with IFDISCARDS and SHRLEVEL REFERENCE options. DDL associated with the shadow objects is executed in the DB2 UET started task, and the load processing runs in its own address space. To use these features, the user ID that submits the load job must have one of the following authorizations:

- SYSCTRL or SYSADM authority
- ownership of the table
- LOAD privilege for the database
- STATS privilege for the database (if the STATISTICS keyword is specified)
- DATAACCESS authority


In addition, the user ID that submits the load job must have authority to complete the following actions:
- run the load against the production table
- rename, copy, delete, and define the DB2 object page sets
- execute the DFSMSdss COPY command with the TOLERATE(ENQFAILURE) keyword to create the shadow data sets. As stated in the DFSMSdss Storage Administration documentation, when the RACF FACILITY class is active and profile STGADMIN.ADR.COPY.TOLERATE.ENQF is defined, you must have READ access authority to use the COPY command.


3. Topic: Worksheets: Gathering parameter values for Tools Customizer”

Revise and add parameters as follows:
- The DB2 UET started task user ID security requirements have changed.
- New parameters Shadow database prefix and Shadow schema have been added to support the IFDISCARDS and SHRLEVEL REFERENCE options.

You must SMP/E APPLY the PTF and use Tools Customizer to recustomize DB2 UET. Using Tools Customizer, regenerate and run all of the customization jobs.

Revised parameter description:

DB2 UET started task user ID

The IBM® RACF® user ID under which the started task will run. This ID will also be used as the OWNER identifier when binding the plan and packages and as the CURRENT SQLID (SQL authorization ID) when creating the product's DB2 objects and when issuing GRANT statements during object creation and following bind processing.
Important: Ensure that this user ID has SYSADM or SYSCTRL authority on each DB2 subsystem where the DB2 UET plan will be bound.

New parameters:

Shadow database prefix
This new options module parameter specifies a prefix for the names of shadow objects that are used with the IFDISCARDS and SHRELEVEL REFERENCE options. The prefix can be one to four characters. DB2 UET uses this prefix and appends a numeric suffix to make each shadow object name unique. For each invocation of the Load Prevalidate or LOAD REPLACE SHRLEVEL REFERENCE feature, DB2 UET generates a unique set of shadow object names using the specified prefix.
Discovered? Yes
Default value: ABPS

Shadow schema
This new options module parameter specifies the schema (creator) to be used for the shadow objects that DB2 UET creates. When executing the Load Prevalidate or LOAD REPLACE SHRLEVEL REFERENCE feature, DB2 UET creates a unique set of shadow objects using the value of the Shadow database prefix option as the prefix for the object names. DB2 UET uses the value that you specify for the Shadow schema option as the schema name for the shadow objects that it creates.
Discovered? Yes
Default value: ABPSTC


4. Topic: “Validating records before committing changes (IFDISCARDS option)”

Add the following topic:

Use the IFDISCARDS option for the DB2® LOAD utility to take advantage of the Load Prevalidate feature. Before committing changes to the database, DB2 UET validates records in the SYSREC file against the check constraints and data types of the table that is specified in the LOAD utility syntax. If issues are found, you can review them before performing the load. You then have the opportunity to correct the errors and rerun the job without the need to perform a recovery of the objects.

The IFDISCARDS option indicates to the DB2 UET DSNUTILB intercept program that you want to use the Load Prevalidate feature and the action that you want DB2 UET to take when rows are flagged for discard. Rows found to be in violation are written to the data set that is specified by the DISCARDDN option.

When DB2 UET detects the IFDISCARDS option in the LOAD utility job, it performs the following processing:
1. Creates a new set of shadow objects against which to perform the load.

Shadow objects are duplicates that mirror the original database, table spaces, tables, and indexes. Shadow tables are created with same OBID as the original. DB2 UET creates a unique name for the shadow objects by using the prefix that is specified in the Shadow database prefix option and adding a numeric suffix. In the shadow database, DB2 UET creates shadow copies of the table space, table, and all indexes on the table that is being loaded, and uses the value of the Shadow schema option as the schema name for the objects.

2. Copies check constraints from production to shadow objects and determines the data set list of the objects that are being loaded.

3. Places the production (original) objects in read-only access mode during processing. DB2 UET preserves existing data in the target table.

4. If you have specified RESUME YES, the existing production data sets are copied to shadow data sets (FlashCopy is used, if available). After copying the data DB2 UET runs a repair utility on the shadow objects to make the OBID, version, and level information of the copied data sets match the shadow objects. During this process, DB2 UET places the production object in read-only (RO) mode and briefly stops the shadow objects (allowing no access).

5. Loads the data into the shadow table instead of the production table.
During the load, the production table is available for select processing.

6. Completes additional processing based on whether the load is successful:

  • If errors are encountered, completes processing as described in Syntax for the IFDISCARDS option.
  • After a successful LOAD utility job:
    1. Runs a repair utility on the shadow objects to set identifiers to match production objects.
    2. If an inline image copy was requested, matches identifiers in the inline copy to the production objects, and updates the SYSCOPY row to point to the production table.
    3. Stops the production spaces, and a brief outage begins.
    4. Swaps the underlying VSAM data sets for the production and shadow objects to make the loaded data accessible in the new production object.
    5. Repairs identifiers in the shadow data sets to match the production data sets.
    6. Updates real-time statistics (RTS) for the production table.
    7. If referential constraints on the old production table were detected, sets CHECK-pending (CHKP) status on the new production object, if appropriate.
    8. Starts the new production object in the state that it was in before the load, and the outage ends.
    9. Deletes renamed temporary data sets and drops the shadow objects.


Syntax for the IFDISCARDS option

The IFDISCARDS option is specified for the entire LOAD statement as shown in the following syntax diagram. (This syntax diagram is a fragment of the LOAD syntax diagram and does not show other native LOAD options that can be specified at this level.)


     .-FAIL------.   
>>-IFDISCARDS--+-+-------+-+-----------------------------------><
                 '-PAUSE-'     

If the IFDISCARDS option is specified and no operand is present, FAIL is the default value. If the option IFDISCARDS is not present in the LOAD utility syntax, prevalidation does not occur, and the LOAD utility processes the table as usual.

FAIL
IFDISCARDS FAIL (the default) tells DB2 UET to discontinue the LOAD utility, delete the shadow objects, and leave the production table unaltered in the same state as before the load started. The utility and its corresponding utility ID are terminated automatically and cannot be restarted. The return code is set to 8. The table data is untouched by the load prevalidation process.

PAUSE
IFDISCARDS PAUSE tells DB2 UET to pause the LOAD utility after performing load prevalidation processing. You can review the data rows that were flagged for discard and decide whether to restart the LOAD utility and discard the data rows, or terminate the LOAD utility, which leaves the production table data untouched.

The LOAD utility ID is not terminated, allowing the LOAD utility job to be restarted. The return code is set to 4. The job will not appear in the report generated by the –DIS UTIL command. That is, the job is not in a stopped state that DB2 has no record of in the SYSUTIL directory table.

The job is recorded in the DB2 UET worklist table as paused, and you can restart it by rerunning the job with the same utility ID. The LOAD utility processes the table by discarding rows in the standard manner and loading data into the production table.

If you choose to terminate the LOAD utility job and revert to the original table as it was before the LOAD utility began, you must terminate the utility ID by using the DB2 UET ABPMAINT utility program with the TERM_UTILITY option.


Usage considerations and restrictions

Consider the following usage issues:

- Support for this feature requires DB2 version 10 or later.

- DB2 UET does not perform referential integrity checks during load prevalidation processing. If it detects referential integrity constraints, after a successful load, DB2 UET places the affected production objects in check-pending status.

- For versioned objects, if you specify the REPLACE option, the current version number is reset to zero.

- Because DB2 UET requires random access to the image copy during the load process, the image copy cannot be on tape. (After the load, you can move the image copy to back to tape.)

- When using the LOG NO option, specify the NOCOPYPEND option so that the LOAD utility does not set the table space in the COPY-pending status. For more information, see the "Syntax and options of the LOAD control statement" topic in the DB2 utilities reference documentation.

- The IFDISCARDS option cannot be used in conjunction with the following options and objects:
  • DISCARDS integer with an integer value greater than 0
    If specified, then the job ends with return code 8. The utility job and its corresponding ID are terminated and cannot be restarted.
  • SHRLEVEL CHANGE
    If this option and IFDISCARDS are specified, then the job ends, the LOAD utility ID is terminated, and an error message is issued. Delete the IFDISCARDS option or change SHRLEVEL CHANGE to SHRLEVEL REFERENCE or SHRLEVEL NONE.
  • LOG YES
  • BY PARTITION for spaces with nonpartitioned secondary indexes (NPSI)
    If BY PARTITION is specified, then the job ends and an error message is issued. You can load the entire table instead of partitions.
  • LOB and XML objects
  • VCAT-defined table spaces
  • Simple table spaces
  • Table spaces with rotated partitions
  • RESUME NO (Use RESUME YES or REPLACE.)
  • RESUME YES for table spaces with versioned rows
  • Image copy templates that contain variables

5. Topic: “Loading a table space in RO access mode (SHRLEVEL REFERENCE option)”

Add the following topic:

You can specify the SHRLEVEL REFERENCE option with the REPLACE or RESUME NO options of the DB2® LOAD utility. During the load, the table space is available in read-only access mode.

When DB2 UET detects the SHRLEVEL REFERENCE option in the LOAD utility job, it performs the following processing:

1. Creates a new set of shadow objects against which to perform the load.
Shadow objects are duplicates that mirror the original database, table spaces, tables, and indexes. Shadow tables are created with same OBID as the original. DB2 UET creates a unique name for the shadow objects by using the prefix that is specified in the Shadow database prefix option and adding a numeric suffix. In the shadow database, DB2 UET creates shadow copies of the table space, table, and all indexes on the table that is being loaded, and uses the value of the Shadow schema option as the schema name for the objects.

2. Copies check constraints from production to shadow objects and determines the data set list of the objects that are being loaded.

3. Places the production (original) objects in read-only access mode during processing. DB2 UET preserves existing data in the target table.

4. If you have specified RESUME YES, the existing production data sets are copied to shadow data sets (FlashCopy is used, if available). After copying the data DB2 UET runs a repair utility on the shadow objects to make the OBID, version, and level information of the copied data sets match the shadow objects. During this process, DB2 UET places the production object in read-only (RO) mode and briefly stops the shadow objects (allowing no access).

5. Loads the data into the shadow table instead of the production table.
During the load, the production table is available for select processing.

6. After a successful LOAD utility job:
    a. Runs a repair utility on the shadow objects to set identifiers to match production objects.

    b. If an inline image copy was requested, matches identifiers in the inline copy to the production objects, and updates the SYSCOPY row to point to the production table.

    c. Stops the production spaces, and a brief outage begins.

    d. Swaps the underlying VSAM data sets for the production and shadow objects to make the loaded data accessible in the new production object.

    e. Updates real-time statistics (RTS) for the production table.

    f. If referential constraints on the old production table were detected, sets CHECK-pending (CHKP) status on the new production object, if appropriate.

    g. Starts the new production object in the state that it was in before the load, and the outage ends.

    h. Deletes renamed temporary data sets and drops the shadow objects.


Syntax diagram

The following syntax diagram illustrates the correct syntax to use for adding the SHRLEVEL REFERENCE option to a DB2 LOAD utility statement that contains the REPLACE or RESUME NO option. (This syntax diagram is a fragment of the LOAD syntax diagram and does not show other native LOAD options that can be specified at this level.)


>>-+---------------------+-------------------------------------><
   '-SHRLEVEL -REFERENCE-'   


Usage considerations and restrictions

Consider the following usage issues:

- Support for this feature requires DB2 version 10 or later.

- When an inline image copy is requested, the image copy is taken against the shadow objects using its table space DBID and OBID. DB2 UET ensures that the image copy is updated so that it is valid for the production object.

- For versioned objects, if you specify the REPLACE option, the current version number is reset to zero.

- Because DB2 UET requires random access to the image copy during the load process, the image copy cannot be on tape. (After the load, you can move the image copy to back to tape.)

- When using the LOG NO option, specify the NOCOPYPEND option so that the LOAD utility does not set the table space in the COPY-pending status. For more information, see the "Syntax and options of the LOAD control statement" topic in the DB2 utilities reference documentation.

- SHRLEVEL REFERENCE cannot be used in conjunction with the following options and objects:
  • PART RESUME NO
  • LOG YES
  • BY PARTITION for spaces with nonpartitioned secondary indexes (NPSI)
    If BY PARTITION is specified, then the job ends and an error message is issued. You can load the entire table instead of partitions.
  • LOB and XML objects
  • VCAT-defined table spaces
  • simple table spaces
  • Table spaces with rotated partitions
  • RESUME NO (Use RESUME YES or REPLACE.)
  • RESUME YES for table spaces with versioned rows
  • Image copy templates that contain variables

6. Topic: Messages and codes

Multiple messages were added or updated. Double-click the following PDF file to see the changes:

PI04864_msgcode.pdfPI04864_msgcode.pdf




Update 13
Date of change: May 26, 2013
Topic: “Date, time, and timestamp options” (In chapter entitled, “DB2 LOAD utility enhancements”)
Change description:

In a previous APAR, an enhancement was made to support the IBM DB2 High Performance Unload Utility (HPU) date, time, and timestamp formats for the LOAD utility. In APAR PM83589, DB2 UET was enhanced to perform data validations for the HPU date, time, and timestamp formats. The attached PDF describes the changes.




Update 12
Date of change: May 26, 2013
Topic: “DB2 Utilities Enhancement Tool customization and options module parameters” (In “Reference” appendix)
Change description:

In APAR PM83589, the following DB2 UET Customization Parameters were added to the Tools Customizer Product Parameters panel (CCQPPRD):

DB2 UET system table spaces STOGROUP

Specifies the name of the storage group that is used internally by DB2 UET for table spaces that contain audit and logging tables and the thread-blocker and DSNUTILB intercept tables. Valid values must follow standard DB2 naming conventions for storage groups.

Discovered? No
Default value: SYSDEFLT


DB2 UET system table spaces buffer pool

Specifies the buffer pool that is used for table spaces that contain the DB2 UET audit and logging tables and the thread-blocker and DSNUTILB intercept tables. Valid values must follow standard DB2 naming conventions for buffer pools.
Discovered? No
Default value: BP0


DB2 UET system index spaces STOGROUP

Specifies the name of the storage group that is used internally by DB2 UET for index spaces that contain indexes on audit and logging tables and the thread-blocker and DSNUTILB intercept tables. Valid values must follow standard DB2 naming conventions for storage groups.

Discovered? No
Default value: SYSDEFLT


DB2 UET system index spaces buffer pool

Specifies the buffer pool that is used for index spaces that contain indexes on the DB2 UET audit and logging tables and the thread-blocker and DSNUTILB intercept tables. Valid values must follow standard DB2 naming conventions for buffer pools.

Discovered? No
Default value: BP0




Update 11
Date of change: May 26, 2013
Topic: “Discovering DB2 UET information automatically” and “DB2 Utilities Enhancement Tool customized product information”
Change description:

In APAR PM83589, DB2 UET was enhanced to support TCz multiple configurations. The following changes were made to support this enhancement:

Panel CCQPDSC now includes the field "DB2 UET V2.2 Configuration ID"
Default value: ABP1
Required parameter? Yes
Parameter description: This parameter identifies the configuration ID for which the current DISCOVER process is to be performed.





Update 10
Date of change: May 26, 2013
Topic: Messages
Change description:

The following new message was added.

ABPP9925E
Storage obtain failed. Module <moduleName>, storage area <storageAreaName>, RC <returnCode>

Explanation: The specified module failed while attempting to obtain the specified storage area.

User response: Increase the region size that is available to the DB2® UET program and run the product again. If the problem persists, contact IBM Software Support. Provide the Support representative with the complete text of this message.





Update 9
Date of change: May 26, 2013
Topic: “Thread cancellation overview”
Change description:

The following restriction was documented:

DB2 UET does not block threads on the DB2 catalog tables or directory tables. It will cancel threads, but will not block additional threads from coming active.

Publication number

SC19-3417-02

Copyright date

2012

Rate this page:

(0 users)Average rating

Add comments

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 #:

7038584

Modified date:

2014-05-23

Translate my page

Machine Translation

Content navigation