Skip to content

Tips & Tricks - Data from Views/Tables into multiple LUs

We use the procedure MIGRATE_SOURCE_DATA for these kind of jobs. Data Migration offers a very flexible way of specifying both dataset to be selected as well as methods to be executed and configuration of these. To understand the basic principles behind execution of this procedure, look here >>.

View Name

If you specify a View Name on insert of a new Header, this view name will also be applied to the Method List folder and your first method will be inserted by default.

Source Name/Owner

In the Formatting tab, Source Name may contain any Oracle data source; table/view/DB-link, JOINs and even UNION. Look here >> for advanced settings.
Source Owner is used only to specify another owner than Appowner. It is not recommended to specify Appowner here, in case this job is exported to a database with another name for Appowner.

Where/Order by /Group by

Use these to build complete SQL-statements. Remember that this is SQL-syntax.

Method List

When entering data for a standard New__/Modify__, it is enough just to enter Sequence and View Name.
On INSERT,  Data Migration will perform following actions:

  1. Check that there is a Package with the name <view_name>_API, containig New__/Modify__ methods.
  2. Lookup dictionary to find all columns for this View.
  3. Perform New__ with PREPARE option, to catch Business Logics default-values.
  4. Lookup LUs belonging to IIDs in order to Enumerate these, showing all alternatives.
  5. Generating data into Method List Attributes (Method List Attribute page)
  6. Generating data into folder Source Mapping according to defined Rules

Rules

Important Rules:

No. in Screenshot Rule Description
1 ADDOBJID Create extra column detail for OBJID. Creates a column at the end of Source Mapping. This column enables us to Restart jobs
2 CREATEDET Create new column details based on views in Method List. If activated, rows will be inserted into Source Mapping.
3 MAPDBCOL Remap IIDs client-default to DB-default. If this rule is active, default-values for IID's with client-values will be remapped to DB-values
- SAVEJOBDAYS Saves info in history-tables, enables us to review previous executions, including error lines. It is recommended always to set this rule Active

Source Mapping

  1. Columns are fetched from the View Name in folder Method List.

  2. If PrefixOption is set to 'ViewName' , this will automatically prefix column names. This is convenient if you have several methods in the method list, and especially if there is lot of columns in the views, like in this example. Key-columns are not prefixed.

  3. Source Column is automatically mapped if there is a match in Column Name between Source Name and View Name

  4. Remember to map ROWID on last row. This will enable Restart of jobs if errors occur.

Method List Attributes

The items in each methods Attribute-string are listed in Method List Attributes page.(select method list method and click on Method List Attribute comman)

  1. Only columns that have got a value from the Source Mapping dataset, will appear on the attribute-string, and in the same order as Column Seq.

  2. If values are entered into Fixed Value, this value will override selected value. No apostrophes in Fixed Value.

  3. The Modify-option will use columns with flag equal P or K to lookup the view for Objid/Objversion to perform method Modify__ with. These flags can be changed if you want to use other columns for this lookup.

  4. Check Box for New and Modify are mapped according to Flags from the View-comments ('I' = OnNew, 'U'=OnModify). Changing values for OnNew is not recommended unless special requirements in Business Logic are present. OnModify may be changed, but remember item 1 above.

  5. LU Reference show what references are connected to this column in ViewComments :

    1. Master references for key columns (Flags = P or K)
    2. IID columns, alternate values are shown in column 'Iid Values'
    3. If Flags starts with M, this is a mandatory basic data that has to be present in the reference LU before migration can start.

Restart Error Jobs

For details about error handling look here >>