OWB 11gR2 – Template mapping variables?

Here we'll see how you can add runtime variables and reference them from code template mappings in OWB 11gR2; variables can be used in many places including expressions, filters, joiners and execution unit code template assignments. This lets you control how some aspects of the mapping will behave at execution time. Traditional database resident mappings support mapping input parameters, in 11gR2 the template mappings can be extended in many ways and this is one illustration.

This is essentially a small extension where a table holding the variable names and values is defined, a data source in the agent to identify where the variable table is defined and there is a JAR file with the support for setting and getting variable values.

Setup

There are a few steps to configure OWB to support this (the zip is here) using a small custom extension I have created. Firstly copy the owbutils.jar file to the owb/lib/ext directory of the CCA host, then stop and start the agent. The table OWB_CT_VARIABLES should be defined in a schema of the user's choice, there is a SQL script supplied for Oracle named variables_oracle.sql in the zip, execute this in a schema of your choice.

The table OWB_CT_VARIABLES is defined with the following columns to represent the variable name and the variable value:

  • variable_name
  • variable_value

So to define a new variable insert a name and value into this table, for example the variable VAR_REGEXP is added below with the vale orderitems.*gz;

insert into owb_ct_variables values ('VAR_REGEXP', 'orderitems.*gz');

commit;

You can refer to variables inside expressions in OWB, for example within filter, join and expression operators, they can also refer to variables from within code template options in a mapping.

Configuring the Agent

Finally create a data source named jdbc/OWBCTVariablesDS in the Control Center Agent (CCA). The data source points to the schema containing the table used for storing the variables. Below is an example of a data-sources.xml file (in owb/jrt/config) that has a data sources added.

owb_data_source

The data source named jdbc/OWBCTVariablesDS is used by the variable API that provides operations on variables to get and set variable values (the API is available when the jar file is copied to owb/lib/ext) - there are also increment/decrement operations for integers.

Illustrations

To illustrate lets look at an example that loads sets of files into a target table. The file list is dynamic and needs to be a regular expression that can be supplied to load all order items files with the gz (compression) suffix in one run. Another example will load all records from files with a transaction date greater than some date we define at runtime. The table OWB_CT_VARIABLES has the variable names and values;

variable_name

variable_value

VAR_REGEXP

orderitems.*gz

VAR_TXNDATE

5-MAY-2009

Taking the mapping from the post on bulk loading files, if we look at code template options we can also use variables here too, so we can solve our challenge by getting the variable value for the filename regular expression to use, the example will use the VAR_REGEXP variable name to obtain the expression for file names at runtime.

 owb_file_reg_exp_variables
This lets us deploy the mapping once and execute the mapping with whatever values we desire for the regular expression since the value is pulled at runtime.

Another illustration using a filter

In this example we define a filter to get all records after the date defined in the variable, we define the condition below, using the API (ctvariable.get) passing the variable name;
clip_image002[4]
Note the \u0022 character which will give us the required quotes through the code generation. Depending on where the expression is defined will change how the code is written, inside a mapping expression the Unicode character is required, in the mapping template options use the quotation mark.

Summary

Here we have seen how you can add runtime variables and reference them from code template mappings in OWB 11gR2; the variables can be used in many places including expressions, filters, joiners and execution unit code template assignments letting you control aspects of the execution at runtime.

Comments:

Can you please explain how to create a data source named jdbc/OWBCTVariablesDS in the Control Center Agent (CCA)? I am stuck here. Don't where how to create this.

Posted by Savita on September 15, 2010 at 04:13 PM PDT #

Hi Savita Did you figure this out? The file is in owb/jrt/config/data-sources.xml and is a standard OC4J definition file that you can define. You can either manually type into the file copying what I did, or use the OC4J admin console. Cheers David

Posted by David Allan on October 04, 2010 at 03:39 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today