RMRTPE and RMRSQPE Stored Procedure Implementation Detail

Products

• RC Compare for DB2 for z/OS

Issue/Introduction

Description:

Checklist for implementing the Stored Procedures RMRTPE and RMRSQPE. These include WLM setup, procedure creation, Post Install process, and the location of documentation.

Solution:

Additional Information on the implementation and use of RMRTPE and RMRSQPE

RMRTPE : This REXX procedure resets identity column numbering after a table reload. When a table involved in an Alter or Compare strategy contains an identity column, this procedure is called prior to a DROP to save the current MAXASSIGNEDVAL of the identity column and add +1 to the value. This is stored as an internal sequence object. Once the table is recreated and loaded the RMRTPE procedure is called again where it retrieves the value saved off from the first call. Behind the scenes an ALTER TABLE, ALTER Column RESTART WITH is used.

RMRSQPE: This REXX procedure prevents duplicates for altered sequences. It is called before and after any sequence object is dropped and recreated. The procedure restarts numbering for the sequence object from the previous MAXASSIGNEDVAL after the sequence object is recreated.

Note: The stored procedure creates Job-temporary DB2 sequences. Verify that the proper authorizations exist to permit your jobs to create sequences.

WLM ENVIRONMENT

• A WLM environment must exist; NUMTCB = 1.
• If a SYSEXEC already exists, then you need to concatenate the CA DB2 tools clist to the SYSEXEC DD, or copy the RMRTPE and/or RMRSQPE members from the CA DB2 tools CLIST to the dataset referenced by SYSEXEC. If you decide to concatenate the CA DB2 tools CLIST on the SYSEXEC DD statement, please ensure all the CLIST datasets are the same format. (fixed or variable)

DB2 TOOLS POST INSTALL

Post Install Tailor RCM (Informational RCMCUST member) for RMRTPE and RMRSQPE creation.

• Post Install Task RCM - generate stored procedure processor job: ssid1453. . Note; the ssid1453 JCL is a template for creating the stored procedures. As generated the job will create the RMRSSP procedure. To create RMRTPE and/or RMRSQPE, change the procedure name.
• JCL to create the procedures, use for RMRSSP, RMRTPE, RMRSQPE.
• Add WLM ENVIRONMENT statement with the correct environment name for Stored Procedures.

MODEL CHANGES REQUIRED

• Customize the Model Services Model being used to add the new entries required for these SP's.
• Each of these stored procedures requires two entries be added within the model. This is detailed in the hilvl.CDBASAMP (RCMCUST). They can all be added to one model or placed in separate models.
• RMRTPE: Add 2 entries, GETIDENT and SAVIDENT (Object type is T)
• RMRSQPE: Add 2 entries, GETMXSGN and PUTMXSGN (Object type is SQ)
• In Model Services the entries will automatically be activated, to deactivate the entry place an 'X' next to it.

THE STRATEGY

• When the model containing these entries is used, any strategy using that model with a TABLE will cause RMRTPE to be called and any strategy containing a sequence object will cause RMRSQPE to be called. The procedure must be called to determine if the table contains an IDENTITY column. The same is true for a SEQUENCE object, RMRSQPE will be called.

CURRENT DOCUMENTATION

• HILVL.CDBASAMP (RCMCUST)
• Migrator r15 User Guide:
• Chapter 20, under Table, Restart of Identity Column
• Chapter 23, How to Prevent Duplicate Sequences After a Drop-and-Recreate
  
  
• Implementation Guide:
• Chapter 5, How to Customize RC/Migrator. See area - How to Restart Identity Column Numbering after a Table Reload
• Chapter 5, How to Customize RC/Migrator. See area - How to Restart Sequence Object Numbering to Prevent Duplicates
• Both of the above sections contain the JCL to create the procedures and the utilities that will need to be added to the model. This information is also within HILVL.CDBASAMP (RCMCUST).

Environment

Release:
Component: RCM