ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

RC/Migrator for DB2 for Z/OS : Add a WHERE clause to the UNLOAD in a Migration Strategy.

book

Article ID: 20102

calendar_today

Updated On:

Products

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

Issue/Introduction

User requirement of a migration strategy requires that a WHERE clause be added to the UNLOAD of data from a table. This information can be added in the Implode/Explode Services option of the strategy by specifying a name for the WHERE clause.

 

Environment

Release: RCM
Component: R20

Resolution

On the RC/M Implode/Explode Services screen, when updating the Strategy,  after scrolling to the right with PF11 the following will display:

In this sample, table authid1.CARS is the primary object of the migration. 

         20.0   --------- RC/M Implode/Explode Services -------- yy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

STRATEGY ===> CARS                 DESCRIPTION  ===>
CREATOR  ===> authid1              SHARE OPTION ===> U (U,Y,N,X,L)
SRC SSID ===> ssid                 NEW OBJECTS  ===> N (U,Y,N)
------------------------------------------------------------------ authid1  < >

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

Under the WHERE heading, specify Y and a name for the WHERE clause...in this case the EQF is called REDCARS.

20.0  ----------- Data Query Edit ----------- ( Caps Off )   yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

Query Name  ==> REDCARS   For Table: authid1.CARS                            >
Description ==>                        >  Share  ==> Y        Default   ==> N
Confirm Replace ==> Y                  Auto Cast ==> Y        View SQL  ==> N
Test Count  ==> N         Shrink ==> N Row Limit ==>          Userid: authid1

Sel C Ord Column Name           Type    ----------- Where Condition ------- AND
S     __   1 CARCOLOR           C(30)   = 'RED'
S     __   2 CARMODEL           C(64)
S     __   3 CARPRICE           D(4,2)
S     __   4 CARPOWER           D(2,0)
******************************* BOTTOM OF DATA ********************************

The Data Query Edit screen will then display on which your WHERE conditions can be specified.

The above predicate will be searching table authid1.CARS for records to unload where column CARCOLOR = 'RED' during the unload stage of the migration. 

The Analysis output would look like this:

For a model utilising PTIUNLD:

.DATA
  FILE(PTIUNLD)
SELECT  CARCOLOR , CARMODEL , CARPRICE , CARPOWER
   FROM authid1.CARS
  WHERE ( CARCOLOR = 'RED' )
  ORDER BY CARCOLOR
  FOR FETCH ONLY
  ;
  LIMIT(ALL)
.ENDDATA

For a model utilising FASTUNLOAD

.DATA
  FASTUNLOAD
   UNLDDN          SYSREC
   LIMIT           0
   OUTPUT-FORMAT   LOAD
   INPUT-FORMAT    TABLE
   VSAM-BUFFERS    80
   SORTNUM         2
   SORTDEVT        SYSDA
   ESTIMATED-ROWS  0
   SQL-ACCESS      EXTENSION
   TRUNCATE        NO
   SELECT CARCOLOR, CARMODEL, CARPRICE, CARPOWER
   FROM authid1.CARS
   NEWOBID 3
   WHERE  (  CARCOLOR  =  'RED'  )
   ORDER BY CARCOLOR
   ;
.ENDDATA

Additional Information

RC/M Implode/Explode Services Dependent Object Selection

12. To create and selected extended data queries, scroll right (PF11) to view the Where field. Data queries let you specify the data to migrate.
The Where field has two prompts. The first prompt lets you create or select a new query. Valid values are as follows:
Y -- Create a new data query.
S -- Select a previously saved data query.
N -- Do not use a data query. This is the default.
Press PF3 (End) to save the strategy.

As Extended Query Facility is used across many of the Database Management for Db2 for Z/OS tools please see:

Extended Query Facility (EQF)