Tips & Tricks - Load File to Table¶
Very often, we do not have all required data on the file to update a LU directly, or we want to update more than one LU at the same time.
In these cases, we have to make a 2-step routine in order to achieve what we want, and step #1 is to load data from the file into a temporary table.
Define Job Header¶
We set up the job header in same manner as when updating single LUs from file but use a dedicated Procedure Name.
We use View Name just as an aid to get the columns generated automatically under File Mapping.
When executing the job, View Name serve no purpose.
Define Job Columns¶
In the File Mapping tab define the columns by specifying the position it appears in the file as the value for the Pos column.
The table name will be derived from the Job ID, using this convention: IC_<Job ID>_TAB.
For the job below, the table-name will be IC_PART_CATALOG_IMPORT_TAB.
Note that a table name can not exceed 30 characters (Oracle restrictions), so the Job ID itself must not exceed 23 characters.
If a job is executed more than once, Data Migration will rename the old IC-table to IC_<Job ID>_BKP before creating an new table.
From third execution onwards, previous backup table is dropped before new backup is taken. This means we always have 1 generation backup of data.
Note: This backup routine is a standard feature and can not be turned off.
Keep Track of File Sequence¶
When you create a table, the column IC_ROW_NO is always added to your specification. This column contains the sequence in which the rows were loaded from the file. This because in some cases it is essential to read data from the IC-table in same order as they were loaded.
Sometimes, our IC-table will be used in a JOIN in another job, and it will be convenient to have an index on the table.
The Rule CREINDEX will create an index for the column(s) in File Mapping that have Flags=K or P.
If you need a unique index, add UNIQUE to column Rule Value.
If more than one column is a Key column, a concatenated index is made. Use column Attr Seq in File Mapping to decide the order of the columns in the concatenated index.
Storage parameters for IC-tables and indexes can be maintained from the System Parameters window. The parameters are under the Category Data Migration.
For more information about how to connect jobs together, look here >>