Move can insert the data from the Extract File into destination tables and files with the same names or different names.
When the destination tables or files do not exist, Move can create them and then insert the data. When Move determines that destination tables or files must be created, you are prompted to specify whether primary keys, relationships, indexes, and so forth as contained in the Extract File are also created. (If the definitions for these objects have not been extracted, only the tables are listed.) This is useful when moving source data from one subsystem to another and the destination tables have not been defined. You can let Move do it for you automatically.
By default, Move assumes the base destination table names are the same as the source table names. However, at the very least, you must specify a default Creator ID for the destination using the TABLE MAP option.
When you select Option 1 TABLE MAP from the INSERT Process menu, the following panel is displayed.
--------------------------- INSERT Process Table Map --------------------------
Command ===> Scroll ===> PAGE
Available Commands: APPLY,SAVE,LIST,MAP,POPULATE,ACM,CLEAR,END when Done
MORE>>
Src CID: FOPDEMO Column
Dest CID: >> Map ID ===>
Extract Tables Destination Table Name Type Column Map or "LOCAL"
--------------------- ------------------------->> ------- ---------------------
************************************* TOP *************************************
CUSTOMERS CUSTOMERS UNKNOWN
DETAILS DETAILS UNKNOWN
ITEMS ITEMS UNKNOWN
ORDERS ORDERS UNKNOWN
BKORDER BKORDER UNKNOWN
*********************************** BOTTOM ************************************Move automatically displays the Source Creator ID in Src CID and the names of all tables in the Extract File in Extract Tables. Move inserts these same values for the destination tables in Destination Table Name. You must supply the Destination Creator ID in Dest CID. (To re-insert or update the source data, you can use a Destination Creator ID that is the same as the Source Creator ID, although this is not typical.)
The Type is supplied by Move and specifies whether the destination is a DB2® or Legacy Table, User-maintained Materialized Query Table, a view, or unknown. On initial display, the destination is UNKNOWN. This type is revised after you specify the Dest CID.
You can overtype the Dest CID and the Destination Table Names to specify the names you want. Move automatically adjusts the Type.
Assume you have a test database that has the same structure as the production system. The Creator ID is used to distinguish between these two sets of tables. To insert the source data extracted from the production system into the test tables of the same name, specify the Dest CID and do not change the Destination Table Names. Move automatically identifies the Type for each destination using the Dest CID. In this example, the Dest CID is specified as FOPDEMO2 and all destinations are DB2 or Legacy Tables.
--------------------------- INSERT Process Table Map --------------------------
Command ===> Scroll ===> PAGE
Available Commands: APPLY,SAVE,LIST,MAP,POPULATE,ACM,CLEAR,END when Done
MORE>>
Src CID: FOPDEMO Column
Dest CID: FOPDEMO2 >> Map ID ===>
Extract Tables Destination Table Name Type Column Map or "LOCAL"
--------------------- ------------------------->> ------- ---------------------
************************************* TOP *************************************
CUSTOMERS CUSTOMERS TABLE
DETAILS DETAILS TABLE
ITEMS ITEMS TABLE
ORDERS ORDERS TABLE
BKORDER BKORDER LEGACY
*********************************** BOTTOM ************************************You can also map each table column-by-column using Column Maps. Column Maps provide enormous flexibility. You can map columns with different names or use a subset of columns. You can also insert literal data, use an exit routine to modify the data before inserting it, insert the same source data into more than one column, insert the defined DB2 default value, specify an expression, or insert NULL if valid for the column. You can age the date in DATE, TIMESTAMP, character, and numeric columns.
The INSERT Process Table Map is presented on two “pages.” MORE, preceded or followed by two arrows, indicates the presence of another page. Use the primary commands LEFT and RIGHT or the assigned function keys to scroll the page horizontally.
To provide processing overrides for inserting selected tables, you can scroll to Overriding on the INSERT Process Table Map panel.
--------------------------- INSERT Process Table Map --------------------------
Command ===> Scroll ===> PAGE
Available Commands: APPLY,SAVE,LIST,MAP,POPULATE,ACM,CLEAR,END when Done
<<MORE
Src CID: FOPDEMO --Overriding--
Dest CID: FOPDEMO2 >> Process Delete
Mode Before
Extract Tables Destination Table Name Type U/I/B Insert
--------------------- ------------------------->> ------- ------- ------
************************************* TOP *************************************
CUSTOMERS CUSTOMERS TABLE
DETAILS DETAILS TABLE
ITEMS ITEMS TABLE
ORDERS ORDERS TABLE
BKORDER BKORDER LEGACY
*********************************** BOTTOM ************************************Process Mode and Delete Before Insert allow you to set the processing options for any table on an individual basis. These overriding parameters are blank by default. You can specify the processing mode as update (U), insert (I), or both insert and update (B) for any table. If you specify I for any table, you can also choose to delete the current contents of the destination table prior to inserting data from the Extract File. The global default processing options from the Specify INSERT Parameters and Execute panel are used for tables with no overriding parameters. (For a description of the global default processing options, see Process Options.)
In this example, leave the overriding parameters blank for all tables.
If necessary, you can use the ACM command to open the Choose Access Method popup dialog.
+-------- Choose Access Method -------+
¦ 1 of 4 ¦
¦ Access Method Values: ¦
¦ K - Key Lookup ¦
¦ S - Table Scan ¦
¦ blank - Software Chooses ¦
¦ ¦
¦ Access ¦
¦ Destination Table Name Method ¦
¦ --------------------------- ------ ¦
¦ ************** TOP **************** ¦
¦ FOPDEMO.CUSTOMERS ¦
¦ FOPDEMO.DETAILS ¦
¦ FOPDEMO.ORDERS ¦
¦ FOPDEMO.ITEMS ¦
¦ ************ BOTTOM *************** ¦
+-------------------------------------+The ACM command allows you to override the default method (scan or key lookup) for accessing each table in the Extract or Archive File. A scan reads all rows in a table at one time; whereas a key lookup locates rows using a WHERE clause to search for primary or foreign key values.
Use END to return to the INSERT Process menu.
If a suitable Table Map has been defined and stored in the Optim™ Directory, you can use the APPLY command to insert the specifications for that Table Map into the current one. You can determine the specifications that are included─the Dest CID, Destination table names, any Column Maps, or all.
For details, see the Common Elements Manual.
Use END to return to the INSERT Process menu.
If the Table Map references one or more Legacy Tables, the Associate Legacy Tables with Data Destinations panel is displayed when you exit the Table Map Editor. A Legacy Table (for IMS™ data, or a VSAM or sequential file) that is referenced as a Destination Table Name in a Table Map must be associated with a specific data source (i.e., the location of the data and how the data is accessed) in order to be used in an Insert Process. For VSAM or sequential files, you must provide the data set name. For IMS, you must provide the PSB and PCB for the destination table's DBD, as well as the data set name.
For example, a single Legacy Table can describe records in your production environment and in your test environment. The Associate Legacy Tables with Data Destinations panel contents vary depending on whether the Legacy Tables in the Access Definition reference IMS data, VSAM or sequential files, or both.
The Associate Legacy Tables with Data Destinations panel shows the default data set name for the VSAM Legacy Table. This panel shows the data set names of any previously specified data destinations.
--------------- Associate Legacy Tables with Data Destinations ----------------
Command ===> Scroll ===> PAGE
Source Dataset Prefix : FOPRT.PROD 1 of 1
Overriding Destination Dataset Prefix ===> MORE>>
Source Legacy Table / Source Dataset / Dest
Destination Legacy Table Destination Dataset Status
--------------------------- -----------------------------------------------
********************************** TOP ************************************
FOPDEMO.BKORDER FOPDEMO.BKORDER LEGACY
FOPDEMO.BKORDER FOPDEMO.BKORDER
******************************** BOTTOM ***********************************If you specify a data set name and do not enclose it in quotes, the name is automatically prefixed with the Overriding Destination Dataset Prefix from this panel or the default prefix specified in User Options.
To save time, if you have more than one Legacy Table, you can enter the data set name in the list and use the “=” shortcut on subsequent lines. Use “=” to copy the Destination Dataset name from the preceding entry or use “=s” to copy the Source Dataset from the current entry into Destination Dataset. For example, if you had more than one Legacy Table, you would enter “=” under FOPDEMO.BKORDER in Source Dataset/Destination Dataset and the previous entry would be duplicated.
Use END to save the Table Map and display the previous panel.