ODI Variables – General Know-How

  • Using a variable within a variable
    • If you want to use a ODI variable within a variable simply type variable name in refresh statement. Here is an exampls how you can do. Below SQL statement connects to MSSQL Server and returns if job (named “My_BI_Job”) succesfully finished or not in specific date . Variable #V_SYSDATE is a variable that is refreshed as SYSDATE from DUAL in Oracle database. If this doesn’t work try typing PROJECT_NAME after “#” sign. If you have multiples projects ODI confuses which project to get variable.

select count(1) from msdb.dbo.sysjobhistory
where step_id = 0
  and run_status = 1
  and job_id = (select job_id from msdb.dbo.sysjobs where name = ‘My_BI_Job’)
  and run_date = CAST(SUBSTRING(‘#V_SYSDATE’, 1,8) as integer)

  • Using ODI functions within a variable
    • If you need to use ODI reference functions within a variable you need to use below syntax. This SQL statement gives the current session’s explanation if ODI gets error on runtime. Current session’s session number is gathered from ODI reference function.

SELECT * FROM ODIWD.SNP_SESSION SESS, ODIWD.SNP_STEP_LOG STEP, ODIWD.SNP_SESS_TASK SESS_TASK, ODIWD.SNP_EXP_TXT EXP_TXT
WHERE SESS.SESS_NO = STEP.SESS_NO
  AND SESS.SESS_NO = SESS_TASK.SESS_NO
  AND STEP.NNO = SESS_TASK.NNO
  AND STEP.I_TXT_STEP_MESS = EXP_TXT.I_TXT
  AND STEP.STEP_STATUS = ‘E’
  AND EXP_TXT.TXT_ORD = 0
  AND SESS.SESS_NO = <%=odiRef.getSession(“SESS_NO”)%>

  • Gathering schema name from ODI ref  functions.
    • It is obvious that you may change your infrastructure and migrate to any other database due to business or technical needs. You need to find and replace all the inner select statements and/or variables to replace new schema name. Here is a tooltip to avoid this problem. All you need to do is to use ODI Ref function in order to use Logical Schema instead of writing plain SQL statement. Underlined statement always gives the physical schema name of database “MYDB” and schema “DWH”. If logical schema is chaned somehow, you don’t need to change the code, this code always looks at physical schema in Topology Manager in execution context.

SELECT NVL(MAX(ALARM_ID), 0) FROM <%=odiRef.getSchemaName(“MYDB.DWH”, “D”)%>.TABLE_NAME

Returns below statement if Logical Schema of “MYDB.DWH” is assigned to MYDB database and DWH schema in appropiate Execution Context in Topology Manager. And you will see below statement in Operator.sd

SELECT NVL(MAX(ALARM_ID), 0) FROM DWH.TABLE_NAME

  • Using database functions within a variable
    • If you need to use database packages and/or functions, you can use it as follows. Below statement calls “get_param_value” function within “my_lib” package with variable “ETL_DATE”.

select to_number(my_lib.get_param_value(‘ETL_DATE’)) from dual

About these ads

4 thoughts on “ODI Variables – General Know-How

    • Since ODI is working with repository, we have the limit with the column size which is 400. You may check SNP_VAR.VAR_NAME column in your work repository.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s