If the table to load does not yet exist how to create a new table without leaving RC/Extract
Article ID: 49293
Updated On:
Products
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
Feedback
