Restrict the rows unloaded from a table during a RC/Migrator migration

Products

• RC/Migrator for DB2 for z/OS
• RC Compare for DB2 for z/OS

Issue/Introduction

Users who are carrying out RC/Migrator for Db2 for z/OS (RCM) migrations of tables and their data often wish to restrict the amount of data selected.
This is due to various reasons associated with the new environment being created. It could be a new test environment which does not require all the data from the source.

How to restrict the rows unloaded from a table during a migration using an SQL predicate?
Is there a requirement to alter the generated unload cards after the analysis?

Resolution

When a Migration Strategy is created and tables are selected there is a WHERE clause available that is able to be seen when a PF11 is used to move to the right on the Implode/Explode services screen. This WHERE clause is used when the unload is generated to restrict the rows unloaded based on that predicate. This WHERE clause is not the same as the other WHERE clauses seen in RC/Migrator that are used to select records from the catalog to be migrated. 



         RR.R   --------- RC/M Implode/Explode Services -------- YY/MM/DD HH:MM
COMMAND ===>                                                  SCROLL ===> CSR

STRATEGY ===> xxxxxxxx            DESCRIPTION  ===> Migrate a Table
CREATOR  ===> AUTHID              SHARE OPTION ===> U (U,Y,N,X,L)
SRC SSID ===> ssid                NEW OBJECTS  ===> N (U,Y,N)
------------------------------------------------------------------- AUTHID  >

   +IMPLODE+ +----- PRIMARY OBJECT -----+ +---------- EXPLODE OPTIONS -------->
S   SG DB TS  TABLE NAME         CREATOR   MQ(IX VW S/A)  IX VW(MQ(IX VW S/A))
_   _  _  _   table              AUTHID    A  A  A  A     A  A  A  A  A  A


PRESS PF11
==========


         RR.R   --------- RC/M Implode/Explode Services -------- YY/MM/DD HH:MM
COMMAND ===>                                                  SCROLL ===> CSR

STRATEGY ===> xxxxxxxx             DESCRIPTION  ===> Migrate a Table
CREATOR  ===> AUTHID               SHARE OPTION ===> U (U,Y,N,X,L)
SRC SSID ===> ssid                 NEW OBJECTS  ===> N (U,Y,N)
------------------------------------------------------------------- AUTHID  <

    >-EXPL OPT-+ AUX RTN MQT SEQ      SECU ALL  NUMBER
S     S/A TG     IMP IMP IMP IMP   RI RITY ROWS OF ROWS   WHERE
_     _   _      _   _   _   _     _   _   _    ________  Y MYQUERY


Here is the help for it.

Field:  WHERE

Description:  A two-part field, with a 1-character action code and an 8-character data query name.  The action code allows you to save, update, or select a new query.

You can use data queries to limit the data to migrate.

Values:  Y    Create a new data query.  The Data Query Edit screen appears.
             S    Select a previously saved data query.  The Query List screen appears.
             N    (Default)  Do not use a data query.
Displays only when the primary object is a table.



SQL predicate can then be added to the columns or a more complex extended query can be written. As with any SQL predicate one should be mindful of performance considerations of any given predicate which might impact on the elapsed time of a migration job.

In the query screen below I have added a where condition(= '9999')  to the ROLE_ID column to select only rows where this is true. 

RR.R  ----------- Data Query Edit ----------- ( Caps Off )   YY/MM/DD HH:MM
COMMAND ===>                                                  SCROLL ===> CSR

Query Name  ==> MYQUERY           For Table: AUTHID.table                          >
Description ==> Migrate a Table     >  Share  ==> U        Default   ==> N
Confirm Replace ==> Y                  Auto Cast ==> Y        View SQL  ==> N
Test Count  ==> N         Shrink ==> N Row Limit ==>          Userid: AUTHID 

Sel C Ord Column Name           Type    ----------- Where Condition ------- AND
S     __   1 xxxx_xx            C(6)    = '9999'
S     __   2 xxxx_xxxx          C(50)
S     __   3 xxxx_xxxxxx        D(16,0)
S     __   4 xxxx_xxxxxx_xxxx   C(1)
S     __   5 xxxx_xxxxxx_xxxxx  C(1)
S     __   6 xxxx_xxxxxx_xxxxx  C(1)
S     __   7 xxxx_xxxxxx_xxxxx  C(1)

Extended Where:
01
02
03
04

Read more about this: Extended Query Facility (EQF)

The results can be seen depending on the Utility Model that is selected.

FAST UNLOAD(FUNLD model)

.DATA
  FASTUNLOAD
   UNLDDN          SYSREC
   LIMIT           0
   OUTPUT-FORMAT   LOAD
   INPUT-FORMAT    TABLE
   VSAM-BUFFERS    80
   SORTNUM         2
   SORTDEVT        SYSALLDA
   ESTIMATED-ROWS  1
   SQL-ACCESS      EXTENSION
   TRUNCATE        NO
   SELECT xxxx_xx, xxxx_xxxx, xxxx_xxxxxx, xxxx_xxxxxx_xxxx,
   xxxx_xxxxxx_xxxxx, xxxx_xxxxxx_xxxxx, xxxx_xxxxxx_xxxxx
   FROM AUTHID.table
   NEWOBID 28
   WHERE  (  xxxx_xx  =  9999  )    <<<<<<<<<<-----the WHERE Clause
   ORDER BY xxxx_xx
   ;
.ENDDATA


Batch Processor Unload(UNLOAD model)
.CALL UNLOAD
.DATA
  FILE(PTIUNLD)
SELECT  xxxx_xx , xxxx_xxxx , xxxx_xxxxxx , xxxx_xxxxx_xxxx ,
        xxxx_xxxxxx_xxxxx , xxxx_xxxxxx_xxxxx , xxxx_xxxxxx_xxxxx
   FROM AUTHID.table
  WHERE ( xxxx_xx = 9999 )     <<<<<<<<<<-----the WHERE Clause
  ORDER BY xxxx_xx
  FOR FETCH ONLY
  ;
  LIMIT(ALL)
.ENDDATA


IBM Unload(IBMULD model)
The IBM Unload Model does not support the use of the EQF.

Additional Information

Read more about this: Extended Query Facility (EQF)