Restrict the rows unloaded from a table during a migration.

Document ID : KB000016367
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

Users who are carrying out 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.

Question:

How do I restrict the rows unloaded from a table during a migration using an sql predicate?
Do I have to alter the generated unload cards after the analysis?

Environment:
DB2 for Z/OS
Answer:

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 ===> Migtable             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))
_   _  _  _   TBROLE             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 ===> Migtable             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.TBROLE                          >
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 ROLE_ID            C(6)    = '9999'
S     __   2 ROLE_DESC          C(50)
S     __   3 ROLE_SALARY        D(16,0)
S     __   4 ROLE_SALARY_CODE   C(1)
S     __   5 ROLE_SALARY_CODE2  C(1)
S     __   6 ROLE_SALARY_CODE3  C(1)
S     __   7 ROLE_SALARY_CODE4  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 ROLE_ID, ROLE_DESC, ROLE_SALARY, ROLE_SALARY_CODE,
   ROLE_SALARY_CODE2, ROLE_SALARY_CODE3, ROLE_SALARY_CODE4
   FROM AUTHID.TBROLE
   NEWOBID 28
   WHERE  (  ROLE_ID  =  9999  )    <<<<<<<<<<-----the WHERE Clause
   ORDER BY ROLE_ID
   ;
.ENDDATA


Batch Processor Unload(UNLOAD model)
.CALL UNLOAD
.DATA
  FILE(PTIUNLD)
SELECT  ROLE_ID , ROLE_DESC , ROLE_SALARY , ROLE_SALARY_CODE ,
        ROLE_SALARY_CODE2 , ROLE_SALARY_CODE3 , ROLE_SALARY_CODE4
   FROM AUTHID.TBROLE
  WHERE ( ROLE_ID = 9999 )     <<<<<<<<<<-----the WHERE Clause
  ORDER BY ROLE_ID
  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)