search cancel

RC/Extract for Db2 for Z/OS : Combine source definitions to load as a group.

book

Article ID: 241653

calendar_today

Updated On:

Products

RC/Extract for DB2 for z/OS

Issue/Introduction

Is there a way to run a group of extracts in a single job and combine the extract data into one extract dataset for loading to a target location.
Is it required to run the extracts individually and then combine the data later?
or would it be required to create a new extract definition with all the tables that are required for the extract?

Environment

DB2 for Z/OS

Component : RCX

Resolution

RC/Extract is able to combine any existing Extract Definitions into one extract job called an Extended Extract. This is done using the RC/Extract Extended Command Center.

The Extended Command Center can view and manage the RC/Extract objects.
These functions are provided:
. Group multiple source definitions into an extended extract definition.
. Extract data from a group of source objects to the extended extract object.
. Load the extracted data into the objects defined in the target definition.


For this example there are four existing Extract Definitions to be combined:

S NAME
_ ________
_ TABLE1
_ TBEMP
_ TBROLE
_ TBROLE2

Start by entering the E  EXTENDED COMMAND CENTER from the main RCX Menu.

Select 1 - Extended Extract Management

Enter the details of the new extended definition with a TP of M for extended.

RXMCC 20.0  ----- RC/EXTRACT Extended Extract Management ---- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

Registry SSID ===> ssid
Extended Extract => *         Creator  ===> authid1  Source SSID  ===> *
--------------------------------------------------------------------- authid1

                                                    T S      *---- LAST UPDATE
S I NAME          DESCRIPTION              CREATOR  P O SSID USER     DATE
C   EXTENDED      NEW EXTENDED EXTRACT____ authid1  M u ssid <==== OBJECT CREA>


On the next screen search for the source definitions required using wildcards if needed.

RXSCC 20.0  ------- RC/EXTRACT SRCDEF Command Center  ------- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

Registry SSID ===> ssid
SRCDEF Name ===> T%          Creator  ===> authid1  Source SSID  ===> ssid
--------------------------------------------------------------------- authid1

                                             T S    * --- LAST UPDATE -----*
S NAME     DESCRIPTION              CREATOR  P O SSID USER     DATE       TIME
s TABLE1                            authid1  S U ssid authid1  yyyy/mm/dd hh:mm
s TBEMP                             authid1  S U ssid authid1  yyyy/mm/dd hh:mm
s TBROLE                            authid1  S U ssid authid1  yyyy/mm/dd hh:mm
s TBROLE2                           authid1  S U ssid authid1  yyyy/mm/dd hh:mm


Select those required definitions with an "S" in the "S" column.

Press PF3 to create the new definition.

On the next screen the "E" "Expand" line command in the "S" column next to the Extended Definition name
will show the underlying source definitions previously selected.

RXMCC 20.0  ----- RC/EXTRACT Extended Extract Management ---- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

Registry SSID ===> ssid
Extended Extract => *         Creator  ===> authid1  Source SSID  ===> *
--------------------------------------------------------------------- authid1

                                                    T S      *---- LAST UPDATE
S I NAME          DESCRIPTION              CREATOR  P O SSID USER     DATE
_   ________      ________________________ authid1  _ N ssid <==== OBJECT CREA>
X - EXTENDED      NEW EXTENDED EXTRACT     authid1  M U ssid authid1  yyyy/mm/>
_     TABLE1                               authid1  S U ssid authid1  yyyy/mm/>
_     TBEMP                                authid1  S U ssid authid1  yyyy/mm/>
_     TBROLE                               authid1  S U ssid authid1  yyyy/mm/>
_     TBROLE2                              authid1  S U ssid authid1  yyyy/mm/>


The Extended Extract Definition is now able to be extracted to obtain the data from the tables that are defined in the underlying Source Definitions using the "X" line command on the Extended Extract definition Line.

The Execution now proceeds in the same manner as a standard extract of a Source Definition. An SQL or Utility extract is able to be performed as normal.

The extract report from this example using SQL extract shows that the tables from the source definitions have been used:


                            Extract Summary Report
                            ----------------------

 Source SSID:        ssid
 Source Location:    LOCAL
 Extract Definition: authid1.EXTENDED
 Extract Object:     authid1.EXTENDED.MDXO

 Start Date/Time:    yyyy/mm/dd     hh:mm:ss
 End Date/Time:      yyyy/mm/dd     hh:mm:ss

 Execution Mode:     ONLINE
 Extract Type:       SQL EXTRACTION
 Output Format:      DSNTIAUL

 Extracted Data:                               ExtID           Count
 --------------:                               -----           -----
 SOURCE DEF NAME:TABLE1   ,   SOURCE DEF CREATOR:authid1
   authid2.TABLE1                               0004               0
   authid2.TABLE2                               0005               0
 SOURCE DEF NAME:TBEMP    ,   SOURCE DEF CREATOR:authid1
   authid1.TBEMP                                0009               2
 SOURCE DEF NAME:TBROLE   ,   SOURCE DEF CREATOR:authid1
   authid1.TBROLE                               0003              10
 SOURCE DEF NAME:TBROLE2  ,   SOURCE DEF CREATOR:authid1
   authid1.TBROLE2                              0001              10

RCX0002I:  RC/EXTRACT EXTRACT PROCESS COMPLETE.

The report above displays the table name and the original source definition names that the table information has been obtained.

After returning to the Extended Extract Management screen the new Extract dataset can be seen on the last line.

RXMCC 20.0  ----- RC/EXTRACT Extended Extract Management ---- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

Registry SSID ===> ssid
Extended Extract => *         Creator  ===> authid1  Source SSID  ===> *
--------------------------------------------------------------------- authid1

                                                    T S      *---- LAST UPDATE
S I NAME          DESCRIPTION              CREATOR  P O SSID USER     DATE
_   ________      ________________________ authid1  _ N ssid <==== OBJECT CREA>
X - EXTENDED      NEW EXTENDED EXTRACT     authid1  M U ssid authid1  yyyy/mm/>
_     TABLE1                               authid1  S U ssid authid1  yyyy/mm/>
_     TBEMP                                authid1  S U ssid authid1  yyyy/mm/>
_     TBROLE                               authid1  S U ssid authid1  yyyy/mm/>
_     TBROLE2                              authid1  S U ssid authid1  yyyy/mm/>
_     authid1.EXTENDED.MDXO                         X * ssid authid1  yyyy/mm/>

Having completed the extract of all the tables, the loading stage can be started by creating a Target definition with the "C" line command on the Extract dataset.

RXMCC 20.0  ----- RC/EXTRACT Extended Extract Management ---- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

Registry SSID ===> DT32
Extended Extract => *         Creator  ===> authid1  Source SSID  ===> *
--------------------------------------------------------------------- authid1

                                                    T S      *---- LAST UPDATE
S I NAME          DESCRIPTION              CREATOR  P O SSID USER     DATE
_   ________      ________________________ authid1  _ N ssid <==== OBJECT CREA>
_ - EXTENDED      NEW EXTENDED EXTRACT     authid1  M U ssid authid1  yyyy/mm/>
_     TABLE1                               authid1  S U ssid authid1  yyyy/mm/>
_     TBEMP                                authid1  S U ssid authid1  yyyy/mm/>
_     TBROLE                               authid1  S U ssid authid1  yyyy/mm/>
_     TBROLE2                              authid1  S U ssid authid1  yyyy/mm/>
C     authid1.EXTENDED.MDXO                         X * ssid authid1  yyyy/mm/>

After completing the naming of the Target definition this screen is displayed:

----------------- RC/EXTRACT Target Mapping ----------------- yyyy/mm/dd hh:mm
Command ===>                                                  Scroll ===> CSR

 GCHANGE: Global Changes        DEPSW: Show Dependent Objects
Extract Object:    authid1.EXTENDED.MDXO
Target Definition: authid1.EXTENDED
 Description  ===> NEW TARGET DEFINITION
 Share Option ===> N
Registry SSID: ssid ------------------------------------------------ authid1
 Source SSID  ===> ssid                  Target SSID   ===> ssid
    Location  ===> LOCAL                    Location   ===> LOCAL

C S Source Object        Creator Type  Target Object      Creator Type  Map
_ S TABLE1               authid2  T    TABLE1             authid2  T    YES
_ S TABLE2               authid2  T    TABLE2             authid2  T    YES
_ S TBEMP                authid1  T    TBEMP              authid1  T    YES
_ S TBROLE               authid1  T    TBROLE             authid1  T    YES
_ S TBROLE2              authid1  T    TBROLE2            authid1  T    YES

From here, the normal process for loading the table data proceeds. 

RXMCC 20.0  ----- RC/EXTRACT Extended Extract Management ---- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

Registry SSID ===> DT32
Extended Extract => *         Creator  ===> authid1  Source SSID  ===> *
--------------------------------------------------------------------- authid1

                                                    T S      *---- LAST UPDATE
S I NAME          DESCRIPTION              CREATOR  P O SSID USER     DATE
_   ________      ________________________ authid1  _ N ssid <==== OBJECT CREA>
_ - EXTENDED      NEW EXTENDED EXTRACT     authid1  M U ssid authid1  yyyy/mm/>
_     TABLE1                               authid1  S U ssid authid1  yyyy/mm/>
_     TBEMP                                authid1  S U ssid authid1  yyyy/mm/>
_     TBROLE                               authid1  S U ssid authid1  yyyy/mm/>
_     TBROLE2                              authid1  S U ssid authid1  yyyy/mm/>
_     authid1.EXTENDED.MDXO                         X * ssid authid1  yyyy/mm/>
_     EXTENDED    NEW TARGET DEFINITION    authid1  T N ssid authid1  yyyy/mm/>

The new target definition for the Extended Extract can be seen on the last line after it is created.

For the purpose of this example it will be sufficient to note that the data loading stage can be performed as normal from this stage.

See section : Load the Extracted Data

Additional Information

Using the Extended Command Center

Perform a Load Using the Command Center