Here is a useful OMB scripting example to illustrate the basic mechanics of the OMB scripting grammar to create an expert. The expert has a couple of steps to get information from the user to load an object (file, table etc.) from one place to somewhere else, quite simple...and the expert itself is pretty simple too! The expert can be found on the utility exchange here, and can be found in OMB form here (just need to source the OMB to define the expert).
For details of the OMB command set see the OWB Api and Scripting reference (here) the OMB commands are simply extensions to a tcl shell which is based on a java implementation so you can also integrate java code.
When the expert is run the user is prompted for;
1. the location of the source
2. the object to be moved (let's call it source)
3. the location of the target (an object named T_source will be created)
After this information is collected the new target is created and the mapping to load it generated.
Select Source
The Select Source task in an expert will display a dialog that lets the user choose the source type and the location for that source type (or create a new location). The task was added in the tcl script using the following command;
OMBALTER EXPERT 'DATA_MOVER' ADD SELECT_SOURCE TASK 'SELECT_SOURCE_TASK'
A new location can be defined and the path entered (see the file example here);
The select source task has a number of inputs and outputs, these are bound to variables as follows in the expert;
| Parameter | Direction | Variable |
| OBJECT_TYPE | Out | G_SRC_OBJ_TYPE |
| MODULE_NAME | Out | G_SRC_MODULE |
| LOCATION_NAME | Out | G_SRC_LOC |
| SOURCE_TYPE | Out | G_SRC_MODULE_TYPE |
| PARENT_CONTEXT | In | OMB_CURRENT_PROJECT |
This parameter to variable binding is done by using the following grammar (for example);
OMBALTER EXPERT 'DATA_MOVER' \
MODIFY PARAMETER 'OBJECT_TYPE' OF TASK 'SELECT_SOURCE_TASK' BIND TO VARIABLE 'G_SRC_OBJ_TYPE' \
MODIFY PARAMETER 'MODULE_NAME' OF TASK 'SELECT_SOURCE_TASK' BIND TO VARIABLE 'G_SRC_MODULE' \
MODIFY PARAMETER 'LOCATION_NAME' OF TASK 'SELECT_SOURCE_TASK' BIND TO VARIABLE 'G_SRC_LOC' \
MODIFY PARAMETER 'SOURCE_TYPE' OF TASK 'SELECT_SOURCE_TASK' BIND TO VARIABLE 'G_SRC_MODULE_TYPE' \
MODIFY PARAMETER 'PARENT_CONTEXT' OF TASK 'SELECT_SOURCE_TASK' BIND TO VARIABLE 'OMB_CURRENT_PROJECT'
That's it for selecting the location of the source, next we have to define what to import (for a file location this will be the file within the directory, for a database, the table within the schema etc.).
Object to Import
The import dialog will be
The Source Import (to import metadata) task in an expert will display a dialog that lets the user choose the object to import, this could be a file, a database table etc. The task was added in the tcl script using the following command;
OMBALTER EXPERT 'DATA_MOVER' ADD SOURCE_IMPORT TASK 'SOURCE_IMPORT_TASK'
Now with the file selected, the source import task will launch the file sampler wizard to define the metadata for the file. Had the object been a database table, the tables and columns would be imported (since the metadata is known, unlike with files).
So with this example you will then define the flat file metadata;
This wizard is the standard OWB flat file sample wizard. The Source Import task has a number of inputs and outputs, these are bound to variables as follows in the expert;
| Parameter | Direction | Variable/Value |
| RETURN_VALUE | Out | G_IMPORT_RESULT |
| IMPORT_MODE | In | the value MINIMAL_MODE |
| OBJECT_TYPE | In | G_SRC_OBJ_TYPE (an output in previous task) |
| PARENT_CONTEXT | In | G_SRC_MODULE (an output in previous task) |
When this is complete the final information is requested, where is the data to be moved to, what is the target?
Location of the target
The Select Target task in an expert will display a dialog that lets the user choose the target type and the location for that target type (or create a new location). The task was added in the tcl script using the following command;
OMBALTER EXPERT 'DATA_MOVER' ADD SELECT_TARGET TASK 'SELECT_TARGET_TASK'
Just like the select source task, the select target will let you create a new location for the target type desired.
The select target task has a number of inputs and outputs, these are bound to variables as follows in the expert;
| Parameter | Direction | Variable |
| OBJECT_TYPE | Out | G_TGT_OBJ_TYPE |
| MODULE_NAME | Out | G_TGT_MODULE |
| LOCATION_NAME | Out | G_TGT_LOC |
| SOURCE_TYPE | Out | G_TGT_MODULE_TYPE |
| PARENT_CONTEXT | In | OMB_CURRENT_PROJECT |
As you see from all of these tasks they rely on variables to cooperate, one task produces information that is consumed by another and variables are used for this and any custom code the task may have/need.
What was produced?
When the expert is run using a file as a source the expert produces the file definition, an external table based on this file, a target table where the data is moved and a mapping that moves the data (along with the locations for these items). All in a simple guided example! Here is a snapshot of what was produced after a simple execution;
The OMB task at the end produced the target table and the mapping;
A neat trick was used in scripting to derive the target table; an unbound table was added to the mapping, the source mapped to it getting all of the columns and types, then the table operator was used with an outbound reconcile/synchronize to create the table. This was achieved by using the OMBRECONCILE command in the OMB task, see the datamover.tcl script referenced earlier.
This is a nice example which illustrates using OMB to create an expert itself, for some background on experts see the paper here and send on any comments and questions.
