Set the Runtime Code Template Variable

-

Runtime Variables (RT) got introduced in OWB11gr2.

Details on how to configure and use the runtime variable can be found here. Thank you David Allan and OWB team.

Every project has its own requirement.If you get a RT variable value then you might have a need to set the RT variable value.

If there is a pattern to set the RT variable value for many CT mappings then adding a task to set its value within the Code Template can save a lot of development effort.

Beauty of code templates is you can design one and use it across many CT mappings.

Dates and Timestamps are little tricky to handle, lets take an example to set the date.
Source tables where updates and inserts can be tracked by a date column (LAST_UPDATE_DATE).
Most of the source system should behave. But you gotta be lucky to have such a one. For the example lets assume source tables have good audits.

Every time a mapping is run, it should extract data which was changed (updated/inserted) after last successful extraction done by the mapping(incremental load).
For this example, just need to do MAX on the source column LAST_UPDATE_DATE to set the value of RT variable for the next run.

Code template can be easily designed\modified to accept RT variable name, the column name and action( max , min , etc) to be carried out. This will set the value for the next run.

Figured out two ways of updating the RT Variable within Code Template
1) JDBC task
2) Jython task

1) JDBC task - Code within them is easier to read and write. Update statement to OWB_CT_VARIABLE table can do the job.
But updating RT variables value will need to hardcode the OWB_CT_VARIABLE table name within the JDBC task. If you have more than one target schema in your target database. Then update privilege on table OWB_CT_VARIABLE needs to be given to different schemas.

2) Jython task - More code to write but no hardcoding of table/schema names needed. No need to worry about privileges. The RT variables can easily be set by calling the "public class ctvariable.set" within the owbutils.jar file. I think its a cleaner approach.

Assuming source is non-oracle, will be using LCT_SQL_TO_ORACLE and DEFAULT_ORACLE_TARGET_CT in the ct mapping. Copied and Modified an exisiting code template LCT_SQL_TO_ORACLE to add the Jython task "UPDATE_CT_VARIABLE" before the
"DROP_WORK_TABLE" JDBC task.

Work table is one good place to capture the new value. Its the place where the result of SQL which executed on source is stored.
Expensive SQL can be avoided and new value to be set can be captured.

Note: Depending upon the code templates in the CT mapping , a decision can be taken
whether to choose LCT or ICT code template to add the task to update the CT variable.
Not all the Load Code Templates generate Work Table.

Make sure you have "AFTER ICT" checked to avoid executing this new task before ICT execution gets successfully completed.



Add parameters for input into code template. Added CT_VARIABLE_NAME and CT_VARIABLE_ACTION



Mapping with code template assigned



Input parameter CT_VARIABLE_NAME and CT_VARIABLE ACTION in the above figure are assigned with values RT variable name and action (MAX with date formatting) to be carried on the source column LAST_UPDATE_DATE.

Dates\Timestamps can be stored in the format of the source database (oracle\non-oracle) by using the date\timestamp functions of oracle.

Need to do TO_CHAR for dates\timestamps since all the runtime variables are stored in character format.

Filter condition like this works for source which do implicit date conversion like MS SQL Server



In case if a source which does not does a implicit date conversion, need to modify the filter condition with date functions related to source.

For eg: Oracle Source



Note: If source is oracle then its better to use "LCT_ORACLE_TO_ORACLE_DBLINK" and some ICT of your choice. And the task to update the RT variable can be added in any one of these code templates.


Pros:
No extra Mappings\transformations to update the value of RT variable.
Development effort can be easily reduced by applying the same code template across various CT mappings.
Simpler process flows.
Can be easily used across different databases.

Cons:
If the project involves Complex processing to calculate RT Variable value then its trickier to design a task but not impossible.


In case if you are interested

Jython Task Logic
-----------------

import java.sql as sql
import java.lang as lang
import ctvariable as ctvar

def SetCTVariable(VarName,VarValue): 
  ctvar.set (VarName,VarValue) 

def getvariableValue (): 
  myCon = snpRef.getJDBCConnection("DEST") 
  selCmd = """ 
            SELECT <%=odiRef.getUserExit("CT_VARIABLE_ACTION")%>  NEW_VALUE 
            FROM <%=odiRef.getTable("L", "COLL_NAME","A")%>
           """ 
  prepSelStmt = myCon.prepareStatement(selCmd)
  rs = prepSelStmt.executeQuery()
  while (rs.next()):
     varVal=rs.getString("NEW_VALUE")
  prepSelStmt.close()
  myCon.commit()
  return varVal

#### start of main #####
VarName = '<%=odiRef.getUserExit("CT_VARIABLE_NAME")%>'
VarValue = getvariableValue()
SetCTVariable(VarName,VarValue)