If the table to load does not yet exist how to create a new table without leaving RC/Extract
search cancel

If the table to load does not yet exist how to create a new table without leaving RC/Extract

book

Article ID: 49293

calendar_today

Updated On: 05-23-2023

Products

RC/Extract for DB2 for z/OS

Issue/Introduction

At the Target Definition creation stage during Target mapping it is sometimes discovered that the target tables
does not currently exist. This is evident when the target names are entered when the "Map" column changes
from YES to NEW. This means that the table does not exist or was not found by that name.
The target table needs to be created if the name is the correct one.

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

 GCHANGE: Global Changes        DEPSW: Show Dependent Objects
Extract Object:    AUTHID.TBROLE2.EXTOBJ
Target Definition: AUTHID.@TRGDEF1
 Description  ===> NEW TARGET DEFINITION
 Share Option ===> U
Registry SSID: ssid ------------------------------------------------ authid
Source SSID  ===> ssid                  Target SSID   ===> ssid
    Location  ===> LOCAL                    Location   ===> LOCAL

C S Source Object        Creator Type  Target Object      Creator Type  Map
_ S TBROLE2              AUTHID T    TBROLE4            AUTHID T    NEW

Environment

Release: r20
Component: RC/Extract for Db2 for z/OS

Resolution

To create the target table the "T" for Template line command is entered in the "C" column on the far left
of the Target Mapping screen next to the source table name.

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

 GCHANGE: Global Changes        DEPSW: Show Dependent Objects
Extract Object:    authid.TBROLE2.EXTOBJ
Target Definition: authid.@TRGDEF1
 Description  ===> NEW TARGET DEFINITION
 Share Option ===> U
Registry SSID: ssid ------------------------------------------------ authid
Source SSID  ===> ssid                  Target SSID   ===> ssid
    Location  ===> LOCAL                    Location   ===> LOCAL

C S Source Object        Creator Type  Target Object      Creator Type  Map
T S TBROLE2              authid  T    TBROLE4            authid  T    NEW

The template command takes you to the RC/Update Table Template screen in order to create the new table.
The target table information that was entered has been copied into the screen along with the tablespace and
database information from the source table for convenience as well as all the columns from the source table
and the other source table attributes.

ROPTBTM --------------- CA - Table Template -------------- yyyy/mm/dd hh:mm:ss
COMMAND ===>                                                  SCROLL ===> CSR

Option      => T                     Object  => T           Mode  => O ONLINE
Item Name   => TBROLE2             > Creator => authid   > Where => N
SSID: DT32 ------------------------------------------------------ authid    >
Table       => TBROLE4             > Creator   => authid   > Comm/Lab => N
Database    => xxxxxx                Editproc  =>          Data Cap => NONE
Tablespace  => TSROLE2               Validproc =>          OBID     =>
Partitioning > NO  (TS Parts:NONE)   Audit     => NONE     Volatile => N
Table Type  => REGULAR               Restrict  => N        CCSID    => EBCDIC
Row Size    => 66/-3,982             Forgn Key => N        Chk Const > N
                                                           Append   => N

CMD ### PS COLUMN NAME        COLUMN TYPE        SIZE       N D FORDAT PK UK FK
___ 1      ROLE_ID            CHAR               6          N _ SBCS   1
___ 2      ROLE_DESC          CHAR               50         N _ SBCS   __
___ 3      ROLE_SALARY        DECIMAL            16,0       N Y _____  __
___ 4      ROLE_SALARY_CODE   CHAR               1          N C SBCS   __

These can then be changed if required and when ready the PF3 key can be pressed to generate the DDL.

When the DDL is displayed it can be submitted online or the EDIT command can be used to further edit the code before execution.

RUTALTT --------- RC/Update Template Confirmation -------- yyyy/mm/dd hh:mm:ss
COMMAND ===>                                                  SCROLL ===> CSR

                    ONLINE  TEMPLATE  CONFIRMATION

  Press ENTER to perform creation.  Press PF3/15 to return to template session.
SSID: ssid ---------------------------------------------------------- authid

-- authid.TBROLE4 WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.


  SET CURRENT SQLID = 'authid';

CREATE  TABLE authid.TBROLE4
        ( ROLE_ID CHAR ( 6 ) NOT NULL
          FOR SBCS DATA
        , ROLE_DESC CHAR ( 50 ) NOT NULL
          FOR SBCS DATA
        , ROLE_SALARY DECIMAL ( 16 , 0 ) NOT NULL
          WITH DEFAULT
        , ROLE_SALARY_CODE CHAR ( 1 ) NOT NULL
          WITH DEFAULT
        'A'
          FOR SBCS DATA
        , CONSTRAINT ROLE_ID
    PRIMARY KEY
     (ROLE_ID
     )
        )
      IN xxxxxx.TSROLE2
   CCSID         EBCDIC
   ;

    SET CURRENT SQLID = USER;

Return to the Target Definition screen using PF3 when the DDL has been executed successfully.

The above table has a primary key so a unique index is needed before data can be loaded to it.
The index can be created from the Target Definition screen using the RCU command which will
transfer to RC/Update so that an index can be created on the table. 

Command ===> RCU

After that is completed the user is returned to the Target Definition screen. The table will be ready
to be loaded now and the target definition can be saved.

Choosing a load option on the target definition....

I - INSERT ROWS USING TARGET DEFINITION

The execution shows the new table being loaded:

.SYSTEM LOAD

.CALL RCXLOAD
.DATA
REGISTRY(ssid)
EXTOBJ(authid.TBROLE2.EXTOBJ)
TRGDEF(authid.@TRGDEF1)
 MODE(TI)
TRGSSID(ssid)
 DELETE(N)
 UPDATE(Y)
 SKIP(N)
 COMMIT(20)
 SORTALLOC(SYSDA,01)
.ENDDATA

 RCX0001I:  RC/EXTRACT SQL LOAD PROCESS BEGINNING.


SQL Engine Summary:

  authid.TBROLE4                Total Rows                   10
                                   Rows Inserted:               10
                                   Rows Updated:                 0
                                   Rows Skipped:                 0

 RCX0002I:  RC/EXTRACT SQL LOAD PROCESS COMPLETE.

RETCODE =     0


Note: A valid RC/Update license is required to use this feature.

Additional Information

Create and Template During Load