20130116

OdiStartScen with Variable


It is possible to execute scenarios with OdiStartScen using variables as package names goes with naming convention as below. You don't need to use package_names one by one.


Table named T_PACKAGE holds pkg_no and pkg_name that we will use for loop and variables.

pkg_no pkg_name  
1 Pkg_001
2 Pkg_002
3 Pkg_003
4 Pkg_004
5 Pkg_005
6 Pkg_006
7 Pkg_007
8 Pkg_008
9 Pkg_009
10 Pkg_010
11 Pkg_011
12 Pkg_012



ODI_VAR_PACKAGE_NO, Value: Default 1 Type:Number ODI variable is created.
ODI_VAR_PACKAGE_NAME, Type:Text ODI variable that gets package name according to pkg_no from the table.


SELECT 'PKG_'||PKG_NAME from T_PACKAGE
WHERE PKG_NO=#ODI_VAR_PACKAGE_NO


ODI_VAR_MAX_PACKAGE_NO, Type:Number ODI variable which gets max pkg_no to know where to end loop.

SELECT MAX(PKG_NO) FROM T_PACKAGE

ODI Variable values cheched with show_parameter procedure during main package execution.
Language: Jython,  Option:VAR




raise  '  \n THE VALUE OF THE VARIABLE <%=odiRef.getOption("VAR")%>  IS  –  <%=odiRef.getOption("VAR")%> '


Enter #ODI_VAR_PACKAGE_NAME  for Scenario name in ODIStartScen properties. If you need, you can also get Scenario Version from variables.


OdiStartScen "-SCEN_NAME=#ODI_VAR_PACKAGE_NAME" "-SCEN_VERSION=001" "-SYNC_MODE=1"

 If you had problem with variable take value, add project before variable:
#PROJECT_NAME.ODI_VAR_PACKAGE_NAME




In the Evaluate step, we  check #ODI_VAR_PACKAGE_NO <> ODI_VAR_MAX_PACKAGE_NO, if so then go to next step, Increment #ODI_VAR_PACKAGE_NO and increment variable +1.



After refreshing variables, you can continue from ODIStartScen step and then evaluate variables, as such you can execute scenarios with loop.

1 comment:

  1. This is the best to learn datastage online FOR THE best practices.


    Datastage Tutorial

    ReplyDelete