The distinction between performing an Insert Process with insert only, update only, or both insert and update processing is important. The difference rests on how existing rows in the table are processed when inserting rows that have matching primary key values.
When the primary key value of the source row does not already exist in the destination table, there is no conflict and the row is inserted only if you select insert or both as the processing method. If you select update as the processing method, the source row is marked as failed and is not inserted. However, when the primary key value of the source row already exists in the destination table, the source row updates the existing row only if you select update or both as the processing method. If you select insert as the processing method, the source row is marked as failed and is not inserted.
For example, you may want to avoid blindly updating existing rows with duplicate primary key values. You can specify insert processing to identify all rows that are not unique, then review the Extract File using the Browse Extract File option to determine which rows were duplicates and take action based on your findings. You can delete specific destination rows in the database to avoid these conflicts and retry the Insert Process. (For details, see the Common Elements Manual.)
When the primary key for the source data is defined in the Optim Directory and is not unique, more than one row in the Extract File may exist with the same primary key value. If this data is then used to insert into a destination table defined with a unique primary key, only the first row with that primary key value in the Extract File is processed.
The additional rows are not processed and are identified as such in the Control File.
When the primary key for the destination table is also defined in the Optim Directory and is not unique, more than one row in the destination table may exist with the same primary key value. For insert processing, the rows in the Extract File are inserted. If you select either update or both as the processing method, the processing is performed as follows:
Determine when to use insert, update, or both insert and update processing by the results you want to obtain. Use insert to insert only new rows, update to replace existing rows, or both to insert new rows and update existing rows. You can also request that the rows in the destination table are deleted prior to performing an insert. This enables you to recreate the original set of test data exactly.
When you select Option 2 PERFORM from the INSERT Process menu, the following panel is displayed to prompt for the parameters needed to perform the Insert Process.
--------------------- Specify INSERT Parameters and Execute -------------------
Command ===>
Names for Extract File and Control File:
Extract File DSN : SAMPLE.FOP.INSERT.DATA
Control File DSN : EXTRACT.CTRL
Process Options:
Default Options (Overrides are not currently set in the Table Map):
Processing Method to Use ===> B (I-Insert, U-Update, B-Both)
For Tables Processed by Insert Only:
Delete All Rows in Target Tables ===> N (Y-Yes, N-No)
If YES, Commit Frequency ===> T (T-After Each Table, E-At End)
Lock Tables During Process ===> N (Y-Yes, N-No)
Age Date Values ===> N (Y-Yes, N-No)
Commit Every Nth Row ===> (1-1000, Blank/SL)
Limit Number of Discarded Rows ===> 1 (1-4294967295, Blank/NL)
Review Propagation Key Sets ===> (A-Always, E-Error)
Run Process in Batch or Online ===> O (B-Batch, O-Online)
If Batch, Review or Save JCL ===> R (N-No, R-Review, S-Save)
Process Report Type ===> D (D-Detailed, S-Summary)This panel includes:
If you specify YES, a commit is performed only when Move has completed processing a table. This is because a commit causes the table lock to be relinquished.
Site management has the option to establish that tables are not to be locked during Insert. If so established, this option is set to NO and cannot be modified.
The site-defined limit is displayed at your site to indicate the maximum value you may specify.
One or more missing indexes may cause performance problems in an Insert Process. Use the SHOW INDEXES command to display the Index Analysis pop-up window listing the destination tables of the Insert Process with the status of the supporting indexes. You can use the Index Analysis pop-up window as a diagnostic tool for determining whether to create the missing indexes. If the status of the index is Partial or None, creation of the missing index may enhance processing performance.
+----------------------- Index Analysis ----------------------+
¦ 1 of 4 ¦
¦ Table Name Index Name Index Status ¦
¦ -------------------------- ------------------ ------------ ¦
¦ ************************** TOP **************************** ¦
¦ FOPDEMO.CUSTOMERS XCUSTPK DBPK ¦
¦ FOPDEMO.DETAILS Partial ¦
¦ FOPDEMO.ITEMS XITEMPK DBPK ¦
¦ FOPDEMO.ORDERS XORDRPK Unique ¦
¦ ************************ BOTTOM *************************** ¦
+-------------------------------------------------------------+This panel includes the following:
The Move AGE function requires a separate Data Privacy license.
When you specify Y to the prompt Age Date Values, the Specify Aging Parameters panel is displayed. The Specify Aging Parameters panel prompts for the values used to age date values. Date values are identified in Column Maps using the AGE function. The values specified on the Specify Aging Parameters panel are used when explicit values are not defined with the function in the Column Map. All explicit values override any specifications on this panel. (For details about the AGE function, see the Common Elements Manual.)
You have the option of applying these values to DB2-defined DATE and TIMESTAMP columns also.
-------------------------- Specify Aging Parameters ---------------------------
Command ===>
Aging Specification
Explicit Date ===> YYYY/MM/DD
Or INCREMENTAL,
Years ===> (-2500 to +1581)
Months ===> (-30000 to +30000)
Weeks ===> (-30000 to +30000)
Days ===> (-99999 to +99999)
Business Rules ===> (0 to 30000)
Or TARGET DATING,
Base Date ===> YYYY/MM/DD (Default:Today)
Target Date ===> YYYY/MM/DD
Default Aging Rule Table ===> (Default: FOP2RUSA)
Default Aging Rule ===>
Century Pivot Year ===> (00 - 99)
Process Date Columns ===> (A-All, U-User Defined)
Report Invalid Dates ===> (Y-Yes, N-No)
Report Skipped Dates ===> (Y-Yes, N-No)
Output Rows w/ Invalid Dates ===> (Y-Yes, N-No)
Output Rows w/ Skipped Dates ===> (Y-Yes, N-No) This panel includes:
A plus or a minus sign preceding the value indicates that the date is to be incremented or decremented. Increment is the default.
A plus or a minus sign preceding the value indicates whether the date is to be incremented or decremented. Increment is the default.
A plus or a minus sign preceding the value indicates whether the date is to be incremented or decremented. Increment is the default.
A plus or a minus sign preceding the value indicates whether the date is to be incremented or decremented. Increment is the default.
You can specify either calendar units (years, months, weeks, and days) or business units, not both.
To display a selection list of rules in the current table, specify an asterisk as the first or only character. Use S to select a rule. (For details about aging rule tables, see the Customization Guide.)
If Default Aging Rule is blank, an aging rule is not applied to any aged data that is not explicitly assigned a rule.
If a Column Map is not defined or AGE is not specified on the selected Column Map, these date values apply only to DATE and TIMESTAMP columns.
When you have completed the aging parameter specifications, use END to proceed with the Insert Process.
The Propagating Key Set(s) panel is displayed prior to the execution of the Insert Process, according to your specification for the Review Propagation Key Sets prompt. When you specify A, this panel is always displayed prior to performing the process. When you specify E, this panel is displayed only when errors are encountered in the PROP function.
The Propagating Key Set(s) panel groups the tables affected by a single PROP function together and identifies the table for which the PROP function is specified. The specifications for determining the value to propagate are also displayed. (For details on specifying the PROP function on the Column Map see the Common Elements Manual.)
The following figure demonstrates:
This ensures that Move does not insert child rows inappropriately. Note that if the propagated primary key value duplicates an existing value, that row is discarded. Move then discards the rows from related tables whose foreign key columns contain the propagated value. Therefore, if changing the CUST_ID in CUSTOMERS causes a duplicate row, the related ORDERS rows are discarded.
However, to ensure that the related DETAILS rows are also discarded, specify propagate for the ORDER_ID column used to relate ORDERS and DETAILS.
--------------------------- Propagating Key Set(s) ----------------------------
Command ===> Scroll ===> PAGE
ROW 0 OF 11
******************************** Top of Data **********************************
Press PF12 to cancel. Press END to continue.
Set No. Table Name Column Column Map Specification
------ -------------------------- ----------------- ------------------------
Set:1 FOPDEMO.SALES SALESMAN_ID
FOPDEMO.CUSTOMERS SALESMAN_ID
FOPDEMO.ORDERS *ORDER_SALESMAN 'JONES'
Set:2 FOPDEMO.CUSTOMERS *CUST_ID SEQ(1,1)
FOPDEMO.ORDERS CUST_ID
Set:3 FOPDEMO.ORDERS *ORDER_ID ORDER_ID
FOPDEMO.DETAILS ORDER_ID
******************************* Bottom of Data ********************************This panel includes:
In the figure, in Set 1, propagate is specified on a child table, ORDERS. In Set 2 and Set 3, it is specified on the parent, CUSTOMERS for Set 2 and ORDERS for Set 3.
You can scroll the display, as necessary.
Use END to perform the process, or press PF12 to cancel the process request and return to the INSERT Process menu. (PF12 has been assigned this special use on the Propagating Key Set(s) panel only. Move automatically restores your ISPF values when the Propagating Key Set(s) panel is exited.)
When Move is inserting data, rows may be discarded in two ways:
Immediate discards -
During the Insert Process, a row is immediately discarded if a condition exists that cannot be rectified by Move. For example, the primary key value for the row already exists in the destination table.
Pending discards -
A pending discard occurs when the row cannot be inserted at the present time, but the condition preventing the insert may not exist later in the Insert Process. These rows fail the Insert but are held in a pending status while processing continues. As the Insert request proceeds, Move will attempt to insert these pending rows one or more additional times.
This occurs only when there are referential integrity cycles. An RI rule may prevent a row from being added because it references another row that is not present in a related table.
For example, you may not be able to add an ORDERS row that contains a customer ID for a CUSTOMERS row that does not exist in the CUSTOMERS table. Later in the processing, the related CUSTOMERS row is added. When Move re-tries the insert on the pending ORDERS row, the insert completes successfully.
It cannot be determined whether pending discards will be discarded until the end of the Insert Process. Therefore, there could be many more discarded rows than the discard limit. For example, assume the discard limit is 10. There are 5 immediate discards, but during the Insert there are 100 pending discards. If all pending discards are inserted sometime during the process, then the number of discards, 5, is well within the limit. If, however, only 50 of the pending discards are inserted, then the number of discards is 55 and well over the limit but this cannot be determined until the process terminates.
Each time Move completes the Insert Process for a table, it issues a COMMIT statement. This occurs even when Move is processing a cycle and will return to a table later in the Insert Process. That means table locks are held only during the time Move is processing a table. When processing switches to another table, the COMMIT causes the table lock to be relinquished. If that table is processed again, the lock is re-established.
Before starting the Insert Process, Move checks for any UNKNOWN destination tables. If any destination tables are UNKNOWN, Move displays the CREATE Object List panel with a message indicating that the unknown tables must be created. All object definitions in the Extract File are included. Those object definitions that do not exist, are identified. Assume three tables and their primary keys, relationships, and indexes are included in the Extract File. Also assume that the ORDERS table is UNKNOWN at the destination and the other two tables exist.
The following panel is displayed:
---------------------------- CREATE Object List ---------CREATE MISSING TABLES
Command ===> Scroll ===> PAGE
Primary : CREATE ALL, DROP ALL, DROP EXISTS, DROP CONFLICTS, DROP CHANGED
DEFAULTS, SHOW 1 of 10
Line : S, U, I, CR(A), DR(A), DB2, OPT, SQL
Cmd Status Type Object Name Database Tablespace
--- -------- --------- ---------------------------->> -------- ----------
*** ******************************** TOP **********************************
___ SELECT TABLE SOFTECH.ORDERS DSOFTECH SSOFTCH2
___ SELECT INDEX FOPDEMO.XORDERPK
___ SELECT PK(DB2)
___ SELECT FK(DB2) RCO
___ EXISTS TABLE SOFTECH.CUSTOMERS DSOFTECH SSOFTCH1
___ EXISTS INDEX FOPDEMO.XCUSTPK
___ EXISTS PK(DB2)
___ EXISTS TABLE SOFTECH.DETAILS DSOFTECH SSOFTCH1
___ EXISTS INDEX FOPDEMO.XORDETPK
___ EXISTS PK(DB2)
*** ****************************** BOTTOM *********************************
Review SQL Before Create ===> Y (Y-YES, N-NO)The UNKNOWN table and its subordinate objects are identified by the SELECT status. (For this example, it is the first table listed in the figure.) The tables and other objects that exist at the destination are identified by the EXISTS status.
To create any table in SELECT status, a Database Name is required; a Table Space Name is optional. If you do not explicitly specify values, Move will use default values if they have been established. If a default is not specified for the database name and you do not supply a value, Move will prompt for a database name. If a default is not specified for the table space name and you do not supply a value, the table space name is automatically generated based on the table name.
You can establish default values for the database name and the table space name using Option 5 Create Process on the Data Migration menu or use the DEFAULTS command. Either displays a menu from which you select the defaults you want to establish. See Create Process for more information on specifying defaults.
You can select and unselect individual objects using the Select line command, S, and the Unselect line command, U. Any unselected object definitions are assigned the status UNSEL and are not included when the SQL to create the objects is generated by Move.
You can scroll the list using the UP, DOWN, TOP, and BOTTOM commands.
You can use the CREATE ALL command to direct Move to generate and execute the SQL DDL statements necessary to create the table and other objects that have the SELECT status. Alternatively, you can use the CR line command to create selected objects individually or the CRA line command to create a table and the objects related to it.
You can display the generated SQL statements prior to execution by specifying Yes to the prompt Review SQL. (This prompt is provided after the last object on the list.) The SQL statements are displayed in the ISPF editor and may be saved or edited as desired. The SQL is executed when you use END. Use CANCEL to abandon executing the SQL.
For more information on the CREATE Object List panel, see Perform Create Process. This panel is also displayed when you explicitly request the PERFORM option of the Create Process and the details of this panel are discussed in that section.
If the missing tables are successfully created, the Insert Process proceeds to load the data after the SQL is executed. (If objects are not created, the Insert Process cannot proceed and a message is displayed. If you do not want to create the missing tables, you can remove their names from Destination Table Name on the INSERT Process Table Map panel.)
If the Insert Process is executed online, a panel is displayed noting the progress of the process.
-------------------- Specify INSERT Parameters and Execute --------------------
Command ===>
Names for Extract File and Control File:
+----------------------UPDATE/INSERT Process Status-------------------------+
| |
| UPDATE/INSERT Process in Progress |
| |
| Number of Rows Processed: 2053 of 10340 |
| |
| Completed Table: FOPDEMO.CUSTOMERS |
| Inserted Rows: 523 |
| Updated Rows: 0 |
| Failed Rows: 0 |
+---------------------------------------------------------------------------+The total number of rows that have been inserted out of the total number of rows to be inserted is displayed. Also, the name of the currently processing table and total rows that have been inserted, updated, or failed for that table are displayed. This is revised:
If you entered YES to the Prompt for Changes Before Job Submission prompt on the Job Card and Print Options panel, the default Job card, as indicated on that panel, is displayed prior to job submission. You may edit the Job card and print options and specify whether your changes are to apply only to the current job submission or to be applied permanently. (See the Common Elements Manual for details about job card and print options.)
The information on the Job Card and Print Options panel is used, along with the Insert parameters, to build the JCL and control statements required to perform the Insert Process. If you enter Review to If Batch, Review or Save JCL on the Specify INSERT Parameters and Execute panel, the entire JCL and control statements are displayed in the ISPF editor. The JCL and control statements can be edited and saved. (See the Batch Utilities Guide for the INSERT statement keywords and values.)
If you have entered Save to If Batch, Review or Save JCL, you are prompted for the name of the file in which to save the JCL and control statements and whether the job should be submitted after saving. (Details are discussed later in this section.)
END is used to return from the ISPF editor to Move, however, your response at the prompt, Submit Jobs with END on the User Options panel, determines whether the job is automatically submitted. If you enter NO to the prompt, you must submit the job explicitly from the ISPF editor using the SUBMIT command.
If you enter YES, the job is automatically submitted. Use the CANCEL command to return to the Specify INSERT Parameters and Execute panel without submitting the job. You can modify the specifications or cancel the insert request from this panel.
(See the Common Elements Manual for information on establishing whether jobs are automatically submitted when END is used.)
If you submit the job and an error in the Job Card is encountered, a message is displayed. You can review the Job Card and correct the error or terminate the Insert Process.
If you save the generated batch job to a data set, you can submit the job directly from the ISPF editor instead of from within an online session. When you do so, you can override the default destination Creator ID defined in the Table Map used for the Insert Process. This is especially convenient when you want to apply different Extract Files to a single set of tables or a single Extract File to multiple sets of tables using common Insert Process JCL.
Any Extract File can be used in an Insert Process as long as at least one table name on the file matches one table name on the Table Map. The Creator IDs do not have to match. If any table does not match on the Table Map, it is not included in the process.
Use the PSDFOVRD DD statement in the JCL to provide the desired overrides.
To override the commit count that was specified when the job was created, specify:
COMMIT_COUNT valueThe value can range from zero to the site limit.
To change commit processing from number of updates to elapsed time, specify:
COMMIT_MINUTES valueThe value is specified in minutes and will override the commit count. The value can range from 1 to 1440. The process report will reflect the change from the number of updates to elapsed time.
To override the default destination Creator ID specified on the Table Map specify:
DEFCID cidwhere cid is the default Creator ID to be used. This applies only to destination tables that are not explicitly qualified in the Table Map. Only one DEFCID parameter may be specified for an Insert Process.
To override the date aging specifications for the Insert Process, specify one or more of the following:
The value must be numeric. Specify whether the value increments (+) or decrements (-). Increment is the default. For example, +15 or 15 increments the dates and -15 decrements the dates.
The combination of AGE_AMT parameters specifies the aging.
YEAR -2500 to +1581
MONTH -30000 to +30000
WEEK -30000 to +30000
DAY -99999 to +99999This
must be specified in the format:
yyyy/mm/dd or yyyy/ddd
The current date is the default.
This
must be specified in the format:
yyyy/mm/dd or yyyy/ddd
This is required if AGE_BASE_DATE is specified.
If not specified, no aging rule is applied.
Specify a two-digit value from 00 through 99. This value determines the threshold. If not specified, a pivot year is not used.
For example, assume the value is 65. All two-digit years 65 or over are assumed to be in the 20th century (19xx); all two-digit years that are less than 65 are assumed to be in the 21st century (20xx).
Specify Y to list the details or N to not list them. The default is Y.
For example, “000000” or “999999” are not valid dates but may be special indicators for the application. Other examples of skipped dates are those containing only spaces, hex zeroes, or hex “FF”.
Specify Y to list the details or N to not list them. The default is Y.
Specify Y to write the rows or N to not write them. The default is Y.
Specify Y to write the records or N to not write them. The default is Y.
To ignore any tables referred to in the Table Map that do not exist when performing the Insert Process, specify:
UNKNOWN { FAIL | ALLOW }UNKNOWN ALLOW is most frequently used when you override the default Creator ID with DEFCID. Changing the default Creator ID may result in naming destination tables that do not exist. The UNKNOWN ALLOW parameter enables you to direct the Insert Process to skip these “unknown” tables.
You can store these parameters in a sequential file or a partitioned data set rather than specify them directly in the jobstream. However, these parameters must be the only data in the file. (You cannot use the same file used for a batch Extract Process if selection criteria, SQL WHERE Clause, or Group Selection Processing parameters are also included. Also you cannot use this file for an Extract Process if the YEAR parameter is included.)
You can save the JCL and control statements, modify them and execute the process without re-invoking Move. Specify S to the prompt, If Batch, Review or Save JCL prompt. The following prompts for the information to save the JCL and control statements.
+---------------------------- Save JCL Parameters --------------------------+
| |
| DSN to Save JCL to ===> |
| Member (if PDS) ===> |
| Replace Existing Data ===> Y-Yes, N-NO |
| |
| DSN to Hold SYSIN Data ===> |
| Member (if PDS) ===> |
| Replace Existing Data ===> Y-Yes, N-NO |
| |
| Submit JCL, or Review ===> S-Submit, R-Review, N-Neither |
+---------------------------------------------------------------------------+This panel includes:
If you specify a partitioned data set, specify the member name in Member.
If you specify a partitioned data set, specify the member name in Member.