Below script shows how to create a mapping in Oracle Warehouse Builder with operators using OMBPlus as scripting language based on a list of source tables and a list of target tables. In this example target tables are located in the Staging area and have the same structure as the Source tables except that the target tables have a view attributes extra.
The mapping detects a delta on the data to be loaded using a filter on load date (“laaddatum”).
After that the data is marked with a Mutation_Code for Updating, Deleting or Inserting records when loading to the next stage (Datawarehouse).
foreach sourceTableName $tableList {
puts -nonewline "CREATE MAPPING E_$sourceTableName .. "
OMBCREATE MAPPING 'E_$sourceTableName' SET PROPERTIES (DESCRIPTION )
VALUES ('mapping comment')
ADD TABLE OPERATOR 'A_$sourceTableName_CUM'
BOUND TO TABLE '../$sourcemodule/$sourceTableName_CUM'
ADD TABLE OPERATOR 'B_$sourceTableName_CUM'
BOUND TO TABLE '../$sourcemodule/$sourceTableName_CUM'
ADD CONSTANT OPERATOR 'PROCES_VARIABLES'
ADD ATTRIBUTE 'PROCES_CDE' OF GROUP 'OUTGRP1' OF OPERATOR 'PROCES_VARIABLES' SET PROPERTIES (DATATYPE, LENGTH, EXPRESSION )
VALUES ('VARCHAR2', 30, '''E_$sourceTableName''')
ADD ATTRIBUTE 'LAAD_DATUM' OF GROUP 'OUTGRP1' OF OPERATOR 'PROCES_VARIABLES' SET PROPERTIES (DATATYPE, EXPRESSION )
VALUES ('DATE', '"SA_ALGEMEEN"."GEEF_LAADDATUM"(''E_$sourceTableName'')')
ADD FILTER OPERATOR 'A_LAADDATUM' SET PROPERTIES ( FILTER_CONDITION )
VALUES ( 'INOUTGRP1.CREATE_DTT > INOUTGRP1.LAAD_DATUM')
ADD FILTER OPERATOR 'B_LAADDATUM' SET PROPERTIES ( FILTER_CONDITION )
VALUES ( 'INOUTGRP1.CREATE_DTT > INOUTGRP1.LAAD_DATUM')
ADD JOINER OPERATOR 'JOIN_DETECT_WIJZIGINGEN' SET PROPERTIES ( JOIN_CONDITION )
VALUES ( 'INGRP1.MUT_CODE != INGRP2.MUT_CODE' )
ADD SET_OPERATION OPERATOR 'S_INSERT_EN_DELETE' SET PROPERTIES ( SET_OPERATION )
VALUES ( 'MINUS' )
ADD FILTER OPERATOR 'ALLEEN_UPDATE' SET PROPERTIES ( FILTER_CONDITION )
VALUES ( 'INOUTGRP1.MUT_CODE = ''>'' ' )
ADD SPLITTER OPERATOR 'SPLIT_INSERT_EN_DELETE'
ADD CONSTANT OPERATOR 'CONST_PROCES_VARIABLE'
ADD ATTRIBUTE 'I_MUT_CDE' OF GROUP 'OUTGRP1' OF OPERATOR 'CONST_PROCES_VARIABLE' SET PROPERTIES (DATATYPE, LENGTH, EXPRESSION)
VALUES ('VARCHAR2', 15, '''I''')
ADD ATTRIBUTE 'D_MUT_CDE' OF GROUP 'OUTGRP1' OF OPERATOR 'CONST_PROCES_VARIABLE' SET PROPERTIES (DATATYPE, LENGTH, EXPRESSION)
VALUES ('VARCHAR2', 15, '''D''')
ADD ATTRIBUTE 'U_MUT_CDE' OF GROUP 'OUTGRP1' OF OPERATOR 'CONST_PROCES_VARIABLE' SET PROPERTIES (DATATYPE, LENGTH, EXPRESSION)
VALUES ('VARCHAR2', 15, '''U''')
ADD ATTRIBUTE 'PROCES_DAT' OF GROUP 'OUTGRP1' OF OPERATOR 'CONST_PROCES_VARIABLE' SET PROPERTIES (DATATYPE, EXPRESSION)
VALUES ('DATE', 'TRUNC(SYSDATE)')
ADD TABLE OPERATOR 'I_$sourceTableName'
BOUND TO TABLE '$sourceTableName'
ADD TABLE OPERATOR 'D_$sourceTableName'
BOUND TO TABLE '$sourceTableName'
ADD TABLE OPERATOR 'U_$sourceTableName'
BOUND TO TABLE '$sourceTableName'
ADD CONNECTION FROM GROUP 'INOUTGRP1' OF OPERATOR 'A_$sourceTableName_CUM'
TO GROUP 'INOUTGRP1' OF OPERATOR 'A_LAADDATUM'
ADD CONNECTION FROM GROUP 'INOUTGRP1' OF OPERATOR 'B_$sourceTableName_CUM'
TO GROUP 'INOUTGRP1' OF OPERATOR 'B_LAADDATUM'
ADD CONNECTION FROM GROUP 'OUTGRP1' OF OPERATOR 'PROCES_VARIABLES'
TO GROUP 'INOUTGRP1' OF OPERATOR 'A_LAADDATUM'
ADD CONNECTION FROM GROUP 'OUTGRP1' OF OPERATOR 'PROCES_VARIABLES'
TO GROUP 'INOUTGRP1' OF OPERATOR 'B_LAADDATUM'
ADD CONNECTION FROM GROUP 'INOUTGRP1' OF OPERATOR 'A_LAADDATUM'
TO GROUP 'INGRP1' OF OPERATOR 'JOIN_DETECT_WIJZIGINGEN'
ADD CONNECTION FROM GROUP 'INOUTGRP1' OF OPERATOR 'A_LAADDATUM'
TO GROUP 'INGRP1' OF OPERATOR 'S_INSERT_EN_DELETE'
ADD CONNECTION FROM GROUP 'OUTGRP1' OF OPERATOR 'JOIN_DETECT_WIJZIGINGEN'
TO GROUP 'INGRP2' OF OPERATOR 'S_INSERT_EN_DELETE'
ADD CONNECTION FROM GROUP 'OUTGRP1' OF OPERATOR 'JOIN_DETECT_WIJZIGINGEN'
TO GROUP 'INOUTGRP1' OF OPERATOR 'ALLEEN_UPDATE'
ADD CONNECTION FROM GROUP 'OUTGRP1' OF OPERATOR 'S_INSERT_EN_DELETE'
TO GROUP 'INGRP1' OF OPERATOR 'SPLIT_INSERT_EN_DELETE'
ADD CONNECTION FROM GROUP 'OUTGRP1' OF OPERATOR 'SPLIT_INSERT_EN_DELETE'
TO GROUP 'INOUTGRP1' OF OPERATOR 'I_$sourceTableName'
BY NAME
ADD CONNECTION FROM GROUP 'OUTGRP2' OF OPERATOR 'SPLIT_INSERT_EN_DELETE'
TO GROUP 'INOUTGRP1' OF OPERATOR 'D_$sourceTableName'
BY NAME
ADD CONNECTION FROM GROUP 'INOUTGRP1' OF OPERATOR 'ALLEEN_UPDATE'
TO GROUP 'INOUTGRP1' OF OPERATOR 'U_$sourceTableName'
BY NAME
ADD CONNECTION FROM GROUP 'INOUTGRP1' OF OPERATOR 'B_LAADDATUM'
TO GROUP 'INGRP2' OF OPERATOR 'JOIN_DETECT_WIJZIGINGEN'
ADD CONNECTION FROM ATTRIBUTE 'PROCES_DAT' OF GROUP 'OUTGRP1' OF OPERATOR 'CONST_PROCES_VARIABLE'
TO ATTRIBUTE 'PROCES_DAT' OF GROUP 'INOUTGRP1' OF OPERATOR 'I_$sourceTableName'
ADD CONNECTION FROM ATTRIBUTE 'I_MUT_CDE' OF GROUP 'OUTGRP1' OF OPERATOR 'CONST_PROCES_VARIABLE'
TO ATTRIBUTE 'MUTATIE_CDE' OF GROUP 'INOUTGRP1' OF OPERATOR 'I_$sourceTableName'
ADD CONNECTION FROM ATTRIBUTE 'PROCES_DAT' OF GROUP 'OUTGRP1' OF OPERATOR 'CONST_PROCES_VARIABLE'
TO ATTRIBUTE 'PROCES_DAT' OF GROUP 'INOUTGRP1' OF OPERATOR 'D_$sourceTableName'
ADD CONNECTION FROM ATTRIBUTE 'D_MUT_CDE' OF GROUP 'OUTGRP1' OF OPERATOR 'CONST_PROCES_VARIABLE'
TO ATTRIBUTE 'MUTATIE_CDE' OF GROUP 'INOUTGRP1' OF OPERATOR 'D_$sourceTableName'
ADD CONNECTION FROM ATTRIBUTE 'PROCES_DAT' OF GROUP 'OUTGRP1' OF OPERATOR 'CONST_PROCES_VARIABLE'
TO ATTRIBUTE 'PROCES_DAT' OF GROUP 'INOUTGRP1' OF OPERATOR 'U_$sourceTableName'
ADD CONNECTION FROM ATTRIBUTE 'U_MUT_CDE' OF GROUP 'OUTGRP1' OF OPERATOR 'CONST_PROCES_VARIABLE'
TO ATTRIBUTE 'MUTATIE_CDE' OF GROUP 'INOUTGRP1' OF OPERATOR 'U_$sourceTableName'
OMBCOMMIT
Script was accomplished with the help of Ludo de Heus.
Email: l.deheus@dba.nl
April 20th, 2006 at 19:28:43
Nice script that you made in OMBplus. I would like to fill you in on some extra information that you probably already know, but for the other readers of this weblog. For instance the targettables you making with :
ADD TABLE OPERATOR ‘A_$sourceTableName\_CUM’ \
BOUND TO TABLE ‘../$sourcemodule/$sourceTableName\_CUM’ \
, could be expended with setting the properties for the loading type, i.e.
ADD TABLE OPERATOR ‘U_$sourceTableName’ \
SET PROPERTIES ( LOADING_TYPE ) VALUES ( ‘INSERT’ )
BOUND TO TABLE ‘$sourceTableName’ \
would be the default set to insert but if you would like to change it, this can be set to ‘UPDATE’ (not that you want it in this case. Other loadingtype values are possible but it’s tricky because they arent all supported. I dont have OMB running here atm but i do remember i have had problems with setting ‘UPDATE/INSERT’ via OMBplus. This isn’t an issue with this delta mapping because you would like to have all values set to ‘INSERT’, but does get more interesting in building skeleton mapping for the ‘DWH’layer where ‘UPDATE/INSERT’ are no uncommon values.
If you would like to fit in some more text over where it says ‘mapping comment’, its possible to put down there a local variable and declare it before the actual OMBCREATE:
OMBCREATE MAPPING ‘E_$sourceTableName’ SET PROPERTIES (DESCRIPTION ) \
VALUES (‘mapping comment’) \
would become before OMBCREATE:
set nameit “————————————————————–\n”
append nameit ” Mapping : E_$sourceTableName \n”
append nameit ” Developer : by script \n”
append nameit “-Version—————————————————–\n”
append nameit ” Date Who Why\n”
append nameit “-End———————————————————\n”
(then:)
OMBCREATE MAPPING ‘E_$sourceTableName’ SET PROPERTIES (DESCRIPTION ) \
VALUES (‘$nameit’) \
April 20th, 2006 at 23:27:36
Hi Sjoerd,
Thanks for your update. In the meanwhile we enhanched the code for creating mappings by adding a preprocess mapping operator to them.
So, here’s the code to that needs to be added to the code above.
ADD PREMAPPING_PROCESS OPERATOR ‘PREM_TRUNCATE_TARGET’ \
SET PROPERTIES ( MAPPING_RUN_CONDITION, ROWBASED_ONLY ) \
VALUES ( ‘ON SUCCESS’, 1 ) \
BOUND TO PROCEDURE ‘TRUNCATE_TARGET_TABLES’
Regards, Ilona
December 8th, 2006 at 06:55:33
Ilona and Sjoerd,
Great information. I’ve been working with OMBPlus for a while and would like to ask either one of you, do you have a good list of properties for each operator (or an object model for OMBPlus)?
OMBLIST gives you down to the ATTRIBUTE level, but then you have to GET PROPERTIES() by specific name. It doesn’t seem to be in a list that .tcl can take advantage through via foreach syntax
The documentation is a bit cryptic and I stumbled on a property in FLAT_FILE OPERATOR that is not referenced in the help – I think it was START_POS or something like that. I’m on a computer that doesn’t have OWB installed right now. I found it in the property window of OWB MAPPING, but when I looked in OMBPlus documentation, it was not there.
Any help would be appreciated.
Thanks, Fred
December 8th, 2006 at 12:46:50
Fred,
Unfortunatly, there is no option to get a list op Properties in OMBPLus. So, you’ll need to keep using the doc’s on OMBPlus.
Depending on your OWB version, the code of OMBplus could be slidely different. For START_POSITION, which you were looking for, it is the same in OWB 10G R1 an OWB 10G R2.
I created some kind of summarize script which shows the different properties of a Flat_file. The script can be made more dynamic by using prompt to ask for a specific FLAT_FILE name but I leave that to you. What is does is that you first need to create a list with property names which you have to lookup first in the documentation. But then after placing those into your script you have a summary of all properties. See below script for an example.
set ff_prop_list { DATA_FILE_NAME IS_DELIMITED CHARACTERSET RECORD_DELIMITER RECORD_LENGTH RECORD_TYPE_COLUMN_NUMBER RECORD_TYPE_START_POSITION RECORD_TYPE_END_POSITION NUMBER_OF_RECORDS_TO_SKIP FIELD_DELIMITER FIELD_LEFT_ENCLOSURE NUMBER_OF_PHYSICAL_RECORDS_PER_LOGICAL CONTINUE_IF_ENDS_WITH CONTINUE_IF_STARTS_WITH }set record_prop_list { RECORD_TYPE_VALUE }
set field_prop_list { DATATYPE LENGTH PRECISION SCALE START_POSITION SQL_DATATYPE SQL_LENGTH SQL_PRECISION SQL_SCALE MASK NULL_IF DEFAULT_IF }
foreach ff_prop $ff_prop_list {
set v_result [ OMBRETRIEVE FLAT_FILE 'T999_KODE_DEF_TXT' GET PROPERTIES($ff_prop) ];
puts -nonewline "Flat File property: $ff_prop is: $v_result \n"
}
foreach record_prop $record_prop_list {
set v_result [ OMBRETRIEVE FLAT_FILE 'T999_KODE_DEF_TXT' RECORD 'T999_KODE_DEF_TXT' GET PROPERTIES($record_prop) ];
puts -nonewline "Record property: $record_prop is: $v_result \n"
}
set field_list [ OMBRETRIEVE FLAT_FILE 'T999_KODE_DEF_TXT' RECORD 'T999_KODE_DEF_TXT' GET FIELDS ]
foreach field $field_list {
puts "\n==== Field Properties of FIELD: $field ==== \n"
foreach field_prop $field_prop_list {
set v_result [ OMBRETRIEVE FLAT_FILE 'T999_KODE_DEF_TXT' RECORD 'T999_KODE_DEF_TXT' FIELD '$field' GET PROPERTIES($field_prop) ];
puts -nonewline "Field property: $field_prop is: $v_result \n"
}
}
Regards, Ilona
December 16th, 2006 at 20:37:35
Thanks for the response. I have the documentation, but that’s just it. The start_position is not even mentioned in the documentation for flat_files.
The way I found the correct property name was through the OWB UI.
Thanks again – Fred
March 8th, 2007 at 19:11:43
I’m trying to create a script that lists the values of each property for each operator. The code above for flat file is a great start, and for each operator type, I can get this list of properties. The problem I’m having now is that OMBRETRIEVE MAPPING ‘X’ GET OPERATORS tells me the name of the operator, but not the type. Is there a OPERATOR property that tells me what type of operator it is? If I try to pull a property such as ‘RECORD_TYPE_START_POSITION’ for a TABLE OPERATOR, I receive an error.
Any suggestions?
Thanks – Fred
March 9th, 2007 at 22:38:54
I found the answer to my question looking at some other OMBRETRIEVE examples. As previously stated, I was using OMBRETRIEVE MAPPING ‘X’ GET OPERATORS which gave me all the operators. I found that you can specify what type of OPERATOR you are looking for (OMBRETRIEVE MAPPING ‘X’ GET TABLE OPERATORS) will only return the TABLE operators. OMBPLUS will return blank if there are none, w/o raising an error. So… based on the list of acceptable OPERATORS —
ADVANCED_QUEUE, AGGREGATOR, CONSTANT, CUBE, DATA_GENERATOR,DEDUPLICATOR, DIMENSION, EXPRESSION, EXTERNAL_PROCESS, EXTERNAL_TABLE, FILTER, FLAT_FILE, INPUT_PARAMETER, JOINER, KEY_LOOKUP,MATCHMERGE, MATERIALIZED_VIEW, NAME_AND_ADDRESS, OUTPUT_PARAMETER, PIVOT, POSTMAPPING_PROCESS, PREMAPPING_PROCESS,
SEQUENCE, SET_OPERATION, SORTER, SPLITTER, TABLE, TRANSFORMATION,UNPIVOT, VIEW.
I can create a loop of each operator type, then another loop for each property of each operator and retrieve all my properties for each mapping.
Thanks – Fred
June 25th, 2008 at 14:27:41
Hi Fred
This blog is a big help for me.
I’v just started to deal with OMB and need exactly the kind of script you’v been
working on: retrieve all properties for each mapping
Could you share that with me?
It saved me a lot of time.
Thanks&Regards
Zsolt