INSERT Process Table Map

Edit online

When you select Option 1 from the INSERT Process menu, the INSERT Process Table Map panel is displayed. Use this panel to specify the destination tables and views and provide processing overrides for individual tables.

Figure 1. INSERT Process Table Map
--------------------------- 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: SOFTECH                                   >>  Map ID ===> 

   Extract Tables       Destination Table Name     Type   Column Map or "LOCAL"
--------------------- ------------------------->> ------- ---------------------
************************************* TOP *************************************
CUSTOMERS             CUSTOMERS                   TABLE   
ORDERS                ORDERS                      UNKNOWN 
DETAILS               DETAILS                     VIEW    
ITEMS                 ITEMS                       LEGACY  
*********************************** BOTTOM ************************************

Panel

This panel includes:

Src CID
The default Creator ID for the source tables as specified in the Extract File. This value cannot be modified.
Dest CID
The default Creator ID for the destination tables. Initially, Dest CID is blank. A valid value must be specified. Specify the Src CID as the Dest CID only when you want to reinsert or update the data in the source tables or when inserting into a different subsystem having tables with the same name.
Column Map ID
1- to 8-character Map ID. Use DB2® LIKE syntax or leave blank to display a selection list.
Extract Tables
Names of the DB2, Materialized Query Tables or Legacy Tables in the Extract File. The Creator ID is included only when it differs from the Src CID. These values cannot be modified.
Destination Table Name
Names of the destination DB2, Materialized Query Tables or Legacy Tables that correspond to the listed sources. Initially, Move supplies names for the destinations that match the source names.

To edit the list:

  • Replace a name by typing over it or selecting a new table from a list. You can request a selection list using the LIST TABLES primary command.
  • Clear all names using the CLEAR primary command.
  • Prefix all names with a string using the PREFIX primary command.
  • Append a string to all names using the SUFFIX primary command.

If you leave a destination table name blank, the Extract File data from the corresponding source table is ignored for the current Insert Process.

Type
The type of the object named in Destination Table Name. Move supplies this value and it is not modifiable. The possible values are:
TABLE
A DB2 table.
VIEW
A view.
UNKNOWN
Non-existent or initial display.
A-TABLE
An alias of a table.
A-VIEW
An alias of a view.
S-TABLE
A synonym of a table.
S-VIEW
A synonym of a view.
UNUSED
A destination table is not specified.
TEMPTBL
Temporary table.
LEGACY
A Legacy Table.
U-MQT
A User-maintained Materialized Query Table
NOT INS
Not insertable (e.g., a joined view or a System-maintained Materialized Query Table).
MOVE automatically creates any destination table that is UNKNOWN. You are prompted for the required information before the process is performed.
Column Map or "LOCAL"
The name of a Column Map to be used to insert the data into the destination table. You must provide a Column Map ID or specify the fully qualified Column Map Name.

Enter LOCAL to define a Column Map for the current Insert Process only.

Type the Column Map names or request a selection list, using the LIST MAPS command. Leave blank if a Column Map is not used.

You can use the POPULATE command to insert the name of a Column Map.

If you enter a new Column Map name or LOCAL, Move automatically invokes the Column Map editor to allow you to create the Column Map. To edit an existing Column Map, use the MAP command.

Processing Overrides

Edit online

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.

Note: You must enter the Dest CID before you can scroll the page horizontally.

To provide processing overrides for selected tables, you can scroll to Overriding on the INSERT Process Table Map panel.

Figure 2. Table Map – Processing Overrides
--------------------------- 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: SOFTECH                                   >>  Process Delete 
                                                            Mode  Before 
   Extract Tables       Destination Table Name     Type    U/I/B  Insert 
--------------------- ------------------------->> ------- ------- ------ 
************************************* TOP *************************************
CUSTOMERS             CUSTOMERS                   TABLE   
ORDERS                ORDERS                      UNKNOWN 
DETAILS               DETAILS                     VIEW    
ITEMS                 ITEMS                       LEGACY  
*********************************** BOTTOM ************************************

Process Mode and Delete Before Insert are displayed on the panel, and are blank by default. These prompts allow you to set the processing options for any table on an individual basis. The default processing options from the Specify INSERT Parameters and Execute panel are used for tables with no overriding parameters. (For a description of the default processing options, see page Insert Parameters Panel.)

Process Mode
The overriding processing mode for selected tables. Specify:
U
Update only.
I
Insert only.
B
Both (update and insert).
Leave Process Mode blank to insert data from a table using the default processing mode, specified on the Specify INSERT Parameters and Execute panel.
Delete Before Insert
Option to delete all rows in the destination table prior to inserting data from the Extract File. Specify:
Y
Delete all rows prior to the Insert. (Process Mode must be I.)
N
Do not delete all rows prior to the Insert.

If you specify overriding parameters for any table, a message on the panel indicates that you can view active table-level processing options by scrolling.

Selection List of Destination Objects

The LIST command is available for aliases, maps, synonyms, DB2 and Legacy Tables, and views. For example, to obtain a selection list of available DB2 and Legacy Tables, use the command LIST TABLES. A selection list of available tables with the destination Creator ID is displayed and, at the same time, a number is displayed by each name in Extract Tables on the Table Map editor. You can override the destination Creator ID with the LIST TABLES command as in LIST TABLES FOPDEMO.%.

To insert a destination name, type the number of the extract table in Num next to the name in the selection list. You can select as many tables from the list at one time as are listed in Extract Tables on the Table Map editor. Move automatically places a selected name in Destination Table Name, according to the number.

Use Existing Table Map

You can use the APPLY command to populate the Table Map with specifications from a previously defined Table Map. If the source tables in the process match the source tables in the Table Map, Move populates the destination table names from the existing Table Map. APPLY can be used to populate all table names and the Dest CID regardless of any entry, or populate only the blank areas.

Destination Table Type

Move automatically revises the Type each time you modify a destination table name. If you modify the Dest CID, any tables that are specified without an explicit Creator ID automatically use the new Dest CID. This may change the Type value and require review.

For example, assume the Dest CID is SOFTECH and the Destination Table Name is CUSTOMERS. The resultant table name is SOFTECH.CUSTOMERS. However, when the Dest CID is changed to COHEND, the table name becomes COHEND.CUSTOMERS and may be unknown. Move prompts for the information to create the table COHEND.CUSTOMERS before performing the Insert Process. (This prompt is displayed prior to entry onto the Specify INSERT Parameters and Execute panel.) You can explicitly supply the Creator ID with the Destination Table Name. Then, changing the Dest CID does not affect the table name.

Duplicate table names are not allowed. Therefore, the same table name cannot be specified twice as a destination table.

ACM Command

If necessary, you can use the ACM command to open the Choose Access Method pop-up window.

Figure 3. Choose Access Method
 +-------- 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 the parent or child table for each relationship. 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.

Note: The default access method is overridden only if the rows are being updated. If the rows are inserted, any changes to the access method are ignored.

For more information, see the Common Elements Manual.

Available Commands

The following commands are available on this panel:
  • ACM
  • APPLY
  • BOTTOM
  • CANCEL
  • CLEAR
  • DOWN
  • END
  • EXPAND
  • LIST
  • MAP
  • OPTIONS
  • POPULATE
  • PREFIX
  • SAVE
  • SUFFIX
  • TOP
  • UP

See the Common Elements Manual for details on these commands.

More Information

The Common Elements Manual provides detailed information on Column Maps and Table Maps.

Unless one or more Legacy Tables are referenced in the Table Map, use END to return to the Choose a Table Map panel from the display. Use the CANCEL command to abandon any changes made on this panel.

Associate Legacy Tables with Data Destinations

Edit online

A Legacy Table that is referenced in a Table Map must be associated with a specific data source to be used in an Insert Process. Each time you exit the Table Map Editor using the END command, the Associate Legacy Tables with Data Destinations panel is displayed. The contents of the panel vary based on whether the Legacy Tables in the Table Map reference IMS™ Data, VSAM or sequential files, or both.

Legacy Tables provides detailed information about Legacy Tables.

In the following example, the Table Map references two Legacy Tables as the Source Tables (FOPLEG.LITEMS and FOPIMS.I$DETAILS) and as the Destination Tables (FOPLEG2.LITEMS and FOPIMS2.I$DETAILS). FOPLEG.LITEMS and FOPLEG2.LITEMS reference VSAM files and FOPIMS.I$DETAILS and FOPIMS2.I$DETAILS reference IMS data.

Figure 4. Associate Legacy Tables with Data Destinations
--------------- Associate Legacy Tables with Data Destinations ----------------
Command ===>                                                  Scroll ===> PAGE 

Source Dataset Prefix                    :                              1 of 2
Overriding Destination Dataset Prefix ===>                              MORE>>

Source Legacy Table /       Source Data /                       Dest           
                            Destination Dataset                 Status         
Destination Legacy Table    IMS--Segment    DBD      PSB    PCB IMSID DBRC LOG 
--------------------------- ---- -------- -------- -------- --- ---------- --- 
*********************************** TOP *************************************  
FOPLEG.LITEMS               'FOPRT.LEGACY.ITEMS'                LEGACY         
  FOPLEG2.LITEMS              'FOPRT2.LEGACY.ITEMS'                            
FOPIMS.I$DETAILS            IMS  DETAILS  SALEHDAM SALHDAMA 1   IMS9           
  FOPIMS2.I$DETAILS                                SALHDAMA 1   IMS9   N    N  
********************************* BOTTOM ************************************
Note: The labels displayed on this panel vary based on what type of legacy tables are displayed (i.e., IMS, VSAM, or sequential files). The following labels are displayed for IMS files only: IMS--Segment, DBD, PSB, PCB, IMSID, DBRC, and Log. The following labels are displayed for VSAM or sequential files only: Source Dataset Prefix, Overriding Destination Dataset Prefix, and Dest Status. If more than one file type is displayed, a combination of the labels is displayed.

Panel

This panel includes

Source Dataset Prefix
Displays the prefix for the data sets associated with the VSAM Legacy Tables specified on the Associate Legacy Tables with Data Sources panel.
Overriding Destination Dataset Prefix
Optional 1- to 8- character prefix used by the data sets associated with the VSAM Legacy Tables in the Table Map.
Source Legacy Table/ Destination Legacy Table
Name of the source and destination Legacy Tables referenced in the Table Map. This column cannot be modified.
Source Dataset/ Destination Dataset IMS--Segment
The Source Dataset is specified in the Access Definition used to create the Extract File. The Source Dataset cannot be modified.

The Destination Dataset is specified as a default in the Destination Legacy Table. If no data set is specified, this field is blank. To enter or modify the Data Destination:

  • Type the fully qualified Destination Dataset name, enclosing it in single quotes.
  • Type a data set name, without delimiting quotes. The data set name is prefixed with the default prefix specified on the User Options panel unless you provide an Overriding Dataset Prefix. A maximum of 17 characters can be entered as the Overriding Dataset.
  • Use a shortcut. Use “=” to copy the Destination Dataset name from the preceding entry. Use “=s” to copy the Source Dataset from the current entry into Destination Dataset (in the preceding panel, using the “=s” shortcut for FOPLEG.LITEMS copies the Source Dataset name, FOPRT.LEGACY.ITEMS, to the Destination Dataset).

If the Table Map contains a Legacy Table that references IMS data, IMS is designated and the source and destination segments within the DBD associated with the Legacy Table are displayed.

DBD
If the Legacy Table references IMS data, displays the DBDs associated with the source and destination Legacy Tables. This column cannot be modified.
PSB
Lists the PSB associated with the source Legacy Table in the Table Map, and allows you to specify the 1- to 8-character name of the PSB used to override the PSB for the destination Legacy Table specified in the Retrieval Definition. If no Retrieval Definition has been created for the destination Legacy Table, the PSB field is blank and a valid PSB must be entered.

The PSB provides access to the IMS services that Move requires to access the database records.

The PSB must be included in the PSB library referenced in the Environment Definition for the destination Legacy Table. Specify an asterisk to generate a selection list that includes PSBs referenced in the Environment Definition for the IMS Legacy Table.

PCB
Lists the PCB number associated with the source Legacy Table in the Table Map, and allows you to specify the PCB number used to override the PCB number specified in the Retrieval Definition for the destination Legacy Tables. If no Retrieval Definition has been created for the Legacy Table, the PCB field is blank and a valid PCB number must be entered.

The PCB must exist within the specified PSB and grant Move the authorization to manipulate the data. Specify an asterisk to generate a selection list that includes PCBs in the specified PSB.

Dest Status IMSID
If the Table Map contains a Legacy Table that references a VSAM or sequential file, the status of the destination table is displayed here.

If the Table Map contains a Legacy Table that references IMS data, IMSID lists the IMS System ID associated with the source Legacy Table in the Table Map. You can specify the IMS ID used to override the ID specified in the Environment Definition for the destination Legacy Tables. The IMS ID is required to access the IMS data when allocated to a control region (i.e., the data is online to IMS).

DBRC
This entry is valid only for IMS processing in DL/I mode (i.e, when an IMS ID is not specified). If appropriate, enter Y for yes to use Database Recovery Control (DBRC) to control logging and perform database recovery; otherwise enter N for no. IMS uses the online log datasets (OLDS) if the database is accessed in BMP or DBB mode.

The default for a HALDB (High Availability Large DataBase), is Y, and that entry cannot be changed.

DBRC use is optional for a non-HALDB, such as HIDAM, HDAM, HISAM, etc. Thus, you may specify Y for a non-HALDB, but it is not required.

LOG
This entry is valid only for IMS processing in DL/I mode (i.e, when an IMS ID is not specified). If appropriate, enter Y for yes to use an IMS log to perform database recovery; otherwise enter N for no.

If you specify Y, you must specify a dataset name for the IMS log on the Associate IMS Segments with IMS Database Datasets panel. The DD Name “IEFRDER” is used to allocate the log dataset on that panel.

If a PSB with a Processing Option (PROCOPT) other than G (Get) is used while accessing a HALDB in DL/I mode, you must specify the name of the dataset to be allocated for DD Name IEFRDER on the Associate IMS Segments with IMS Database Datasets panel.

After you specify the IEFRDER dataset name and exit the Associate IMS Segments with IMS Database Datasets panel, the Allocate Dataset panel automatically displays. You must provide sufficient Primary and Secondary space units on that panel to allocate the IEFRDER dataset. Failing to do so will cause IMS to abort processing and lock the database from further updates until a recover/rollback is done.

If no IMS Legacy Tables are referenced in the Table Map, use END to return to the INSERT Process menu. If one or more Legacy Tables are referenced and an IMS ID was not specified, use END to display the Associate IMS Segments with IMS Database Datasets panel. Use the CANCEL command to abandon any changes made on this panel and then return.

Associate IMS Segments with IMS Database Datasets

Edit online

When you use END to exit the Associate Legacy Tables with Data Destinations panel and one or more Legacy Tables in the Access Definition reference IMS data and an IMS ID was not specified, the Associate IMS Segments With IMS Database Datasets panel is displayed.

This panel allows you to override the default IMS Database Dataset Name specified in the Retrieval Definition.

Figure 5. Associate IMS Segments With IMS Database Datasets
------------- Associate IMS Segments With IMS Database Datasets ---------------
Command ===>                                                  Scroll ===> PAGE 

Overriding Destination Dataset Prefix ===>                              1 of 7 


      DBD    Segment  DD Name  Destination IMS Database Dataset Name 
    -------- -------- -------- -------------------------------------------
    ******************************** TOP *********************************
    ITEMS    ITEMSDBD ITEMDD 
    ****************************** BOTTOM ********************************

Panel

This panel includes:

Overriding Destination Dataset Prefix
Optional 1- to 8-character prefix used when specifying IMS Database Dataset Names.
DBD
Lists the DBDs referenced by the Destination Legacy Tables in the Table Map. This column cannot be modified.
Segment
Lists segments within the specified DBD. This column cannot be modified.
DD Name
Lists the names of the DD (i.e., the physical data sets) associated with each segment. This column cannot be modified. If IMS logging was requested, the DD Name IEFRDER is displayed (along with the pseudo-Segment IMSLOG) to identify the Log dataset.
Destination IMS Database Dataset Name
Specify the IMS Database Dataset Name to override the database data set name specified in the Retrieval Definition.

Enter the location of the IMS Database Dataset associated with each DD name in the DBD. This data is then associated with the named Legacy Table, during processing.

A Site Option (Require IMS Data Set Names) determines whether you can omit the data set name to allow IMS to dynamically allocate the data set. All users can specify '$MDA' as the data set name to choose dynamic allocation, regardless of this Site Option.

If IMS logging was requested, you must specify the Log dataset for DD Name IEFRDER. If a default name is displayed from the Provide Retrieval Information panel, you can override that name, if needed.

After you specify the IEFRDER dataset name and exit the Associate IMS Segments with IMS Database Datasets panel, the Allocate Dataset panel automatically displays. You must provide sufficient Primary and Secondary space units on that panel to allocate the IEFRDER dataset. Failing to do so will cause IMS to abort processing and lock the database from further updates until a recover/rollback is done.

Use END to return to the INSERT Process menu from the display. Use the CANCEL command to abandon any changes made on this panel.

Note: You do not have to specify a dataset name for a HALDB because the appropriate dataset name will already be known to the IMS subsystem, but you do have to specify the IEFRDER dataset if it was not defaulted from the retrieval information specified on the Provide Retrieval Information panel.