ODI Tip: Get Physical Schema from Logical Schema in an ODI variable

When it comes to file paths you do not want to hard-code them because they will need to be manually updated when you migrate from one environment to another.
The best way to retrieve a file path at runtime is to get it from the physical schema that you have configured in the Topology.
You can create an ODI variable and use the following code for its refresh:


select '<%=odiRef.getSchemaName("EXTRACT_FILES_LS","W")%>' 
from dual

where EXTRACT_FILES_LS is the Logical Schema name.

When you migrate to a new environment, the only thing that will need to be updated is the Topology Physical Schema and by refreshing the variable you will have the updated file path.

ODI Tip: Generating flat files with spaces in column names

I had a requirement of generating a .csv file with header out of a database table.
I had created the model as follows:

• File Format: Delimited
• Record Separator: Unix  
• Field Separator: , (comma)

In the interface, I had placed the file as Target Datasource and used 'IKM SQL to File Append' with the following options:

Unfortunately, the header columns were truncated at the first space and adding double quotes did not help. The column header were all truncated after the first space found.
After investigating, I came to the conclusion that there is was no quick or easy way to deal with spaces. I guess the only way is creating a custom version of the IKM.
Therefore, since I have very little time, I have replaced the spaces with underscores and added a OdiOSCommand in the package to replace the underscores with spaces in the output file.

ODIOSCommand is a more flexible version of OSCommand and allows you to invoke a sed command to do the replacement in place (-i) of the underscores with spaces.

sed -i 's/_/ /g' <filename>

Oracle Tip: Exception in thread "DwgCmdScheduler"

A customer had an issue with a StandAlone ODI Agent. Apparently the agent was up and running, but it was not possible to schedule any job.
ODI version was 11.1.1.6 installed on Unix Server.
I manually stopped the agent and restarted it using the following commands:


./agentstop.sh -NAME=<AGENT_NAME> -PORT=<PORT_NUMBER>

./agent.sh -NAME=<AGENT_NAME> -PORT=<PORT_NUMBER>

2014-03-11 10:36:33.325 NOTIFICATION ODI-1128 Agent <AGENT_NAME> is starting. Application Server: STANDALONE. Agent Version: 11.1.1.6.0 - 19/12/2011. Port: 20910. JMX Port: 21910.

2014-03-11 10:36:36.132 NOTIFICATION ODI-1136 Starting Schedulers on Agent <AGENT_NAME>.

2014-03-11 10:36:36.538 NOTIFICATION ODI-1111 Agent <AGENT_NAME> started. Agent version: 11.1.1.6.0 - 19/12/2011. Port: 20910. JMX Port: 21910.

Exception in thread "DwgCmdScheduler-WORK_REP_PRO" java.lang.NullPointerException

at com.sunopsis.timer.filterbased.SnpsBetweenHoursFilter.equals
(SnpsBetweenHoursFilter.java:112)

at java.util.AbstractList.equals(AbstractList.java:524)

at com.sunopsis.timer.filterbased.FilterBasedTaskScheduleDefinition.equals
(FilterBasedTaskScheduleDefinition.java:50)

at com.sunopsis.timer.SnpsTaskManager.updateDefinitionList(SnpsTaskManager.java:94)

at com.sunopsis.timer.SnpsAbstractScheduler.run(SnpsAbstractScheduler.java:161)

at oracle.odi.runtime.agent.scheduler.OdiAgentScheduler.run(OdiAgentScheduler.java:138)

at java.lang.Thread.run(Thread.java:679)

2014-03-11 10:36:36.805 NOTIFICATION ODI-1137 Scheduler started for work repository WORK_REP on Agent <AGENT_NAME>

The error shows an issue with starting scheduler components of the agent. I googled the error and I did not find anything useful. So I tried creating a new agent with the same parameters (server and port number) and I could start it and schedule jobs. Then, what was wrong with the original agent?
Thanks to Oracle Support, I found out that there is a bug that raises this issue whenever just one column out of S_BEGIN_HOUR and S_END_HOUR in table SNP_PLAN_AGENT of the Work Repository has values in it.

“Problem Description

-------------------

There are two columns in the SNP_PLAN_AGENT, S_BEGIN_HOUR and S_END_HOUR.

Generally, S_BEGIN_HOUR and S_END_HOUR columns either both shows the correct 

time value or both NULL.

However, our ODI customer has faced major problems due to only one of the 

columns showed NULL and the other column showed the time value.”

I connected to the Work Repository and executed the following query:

select *

from SNP_PLAN_AGENT

where LAGENT_NAME = <AGENT_NAME>

and I have detected a line having S_BEGIN_HOUR = '01/01/70' and S_END_HOUR = null. 

I have updated the line setting S_BEGIN_HOUR to null in order to have both columns set to null. 

update SNP_PLAN_AGENT 

set S_BEGIN_HOUR = NULL  

where LAGENT_NAME = <AGENT_NAME> AND S_BEGIN_HOUR IS NOT NULL;  

COMMIT;  

I then tried to start the agent and it worked. No warnings and scheduling back up and running!