Archive for 2014
How to capture Error in Load plan Exception step?
The title is pretty self explanatory. In the good old days of 10G when there was no load plans and when we were dealing with just packages it was quiet easy to get the previous step log using odiRef.getPrevStepLog() function or if you wish to be little fancy you can get use this(http://odiexperts.com/get-the-error-message-of-any-failed-odi-objects-inside-package/) blog post from ODI Experts
Anyway with load plans we are starting to write the failure notifications in the exception step section. Now the challenge is that there is no substitution api functions or any tools for getting the error message from the failed load plan.
After analyzing the load plan tables in ODI repository I wrote the following query which has to be run in “Exception step” to get the log of the step which raised triggered the exception step.
Lets a Load plan step “Load Geo Dimension” step failed and raised an error and ran the exception step “Send load failed message” to get the error message in “Load Geo Dimension” we have to use the following query in “Send load failed message”
SELECT ERROR_MESSAGE
FROM SNP_LPI_STEP_LOG
WHERE (I_LP_STEP,I_LP_INST) IN
(SELECT I_LP_STEP,
I_LP_INST
FROM snp_lpi_exc_log
WHERE I_LP_INST = <%=odiRef.getLoadPlanInstance("BATCH_ID")%>
AND NB_RUN = <%=odiRef.getLoadPlanInstance("RESTART_ATTEMPTS")%>
AND SESS_NO = <%=odiRef.getSession( "SESS_NO" )%>
)
Since error message is a CLOB, my recommendation is to use OdiSqlUnload tool to dump the error message in a file and attach the file to the email notification (If you are using an email).
Note: Very Less has been written about the structure of the load plan tables as compared to session tables (SNP_SESS). Future blog posts will try to detail the structure of the load plan tables.
BIAPPS External authentication for ODI support for IPLANET (ODISEE)
Switching to external authentication is documented in the Oracle note
Doc: Steps to Configure BI Applications 11.1.1.7.1 With External Authentication (Doc ID 1606637.1)
However, By default it 11.1.1.7.1 version only works if the external authenticator is OID. For non OID identity store to be supported we need to change the file switch_odiToExternalAuth.py.
BiApps Data load performance tuning variable hints
1. SDE_ORAR1213_ADAPTOR_SDE_ORA_GLJOURNALSFACT
a. BIAPPS_ETL_BULK_MODE
Default value : F
Default value : ‘No value’
Changed to: /*+ APPEND */
SOURCE: GL_JE_LINES
2. SDE_ORA_PersistedStage_GLLinkageInformation_GLExtract
a. BIAPPS_ETL_BULK_MODE
Default value : F
Change to: Y
Default value : ‘No value’
Changed to: /*+ APPEND */
TARGET: W_ORA_GL_LINKAGE_GL_JRNL_PS
GL_LEDGERS, GL_PERIODS
3. SDE_ORA_GLBalanceFact
Default value : F
Change to: Y
Default value : ‘No value’
Changed to: /*+ APPEND */
TARGET: W_GL_BALANCE_FS
4. SILOS_SIL_GLOTHERFACT
a. BIAPPS_ETL_BULK_MODE
Default value: F
Change to: Y
b. ETL_HINT_UPDATE
Default value: ‘No value’
Changed to: /*+ APPEND */
TARGET: W_GL_OTHER_F
SOURCES: LKP+FS
a. BIAPPS_ETL_BULK_MODE
Default value: F
Change to: Y
b. ETL_HINT_UPDATE
Default value: ‘No value’
Changed to: /*+ APPEND */
TARGET: W_GL_OTHER_F, W_GL_BALANCE_F
6. SIL_GLLinkageInformationGeneral
a. BIAPPS_ETL_BULK_MODE
Default value: F
Change to: Y
b. ETL_HINT_UPDATE
Default value: ‘No value’
Changed to: /*+ APPEND */
7. PLP_GLOtherGroupAccount_A_Stage
Default value : F
Change to: Y
Tips from the ODI Certified!
Here is a good authoritative link, which we used to successfully prepare for the exam. Being a fan of no repetition I am going to point to that website.
http://devepm.com/category/odi-certification/
Also we followed instructions on odiexperts.com.
Here are some of the topics specifically which were asked:
- web_cat folder file in OBIEEE Location
- Questions about Oracle EDQ date parser
- Quite a few questions on EDQ parsing
- EDQ AV
- EDQ Data profiling
- Perform a matching process, identify duplicated records, balance performance and duplicate detection accuracy
- Configure EDQ to work with Data Stores, Snapshots, Staged Data, Exports
- Describe profiling results and the use of Resultbooks and Publish features
- Explain address verification and Address Verification statistics (this is also related to EDQ.
- Advanced package editing: In package step -->advanced tab -->log steps in journal.
- Monitor the execution of interfaces
- Explain the parsing process and referencing review results.
- Questions on how to deal with partitioning in ODI.
The only way to pass the exam is to answer a few questions correctly on EDQ. At the least study the basics of EDQ on how profiling, matching process is done. If you don't study EDQ, you have to answers all the other questions related to ODI and golden gate correctly. I was not that confident that I would pass the exam as they were a lot of questions on EDQ (atleast 10--15)
- FYI: This really works.
BIACM Import Error
General rule of thumb is that when we do migrations and it fails, we ought to just redo it multiple times and it just works. Lets say if the import fails in the middle we try importing one by one starting with System setup, Data Load Parameters, Reporting Parameters and Domain member mappings.
Error 1:
oracle.jbo.DMLConstraintException: JBO-26048: Constraint "CDM31_PK" is violated during post operation "Insert" using SQL statement "INSERT INTO C_DOMAIN_MEMBER(DOMAIN_MEMBER_CODE,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,DOMAIN_KEY) VALUES (:1,:2,:3,:4,:5,:6,:7)".Fetching Data...
Solution:
This was simply fixed by re-importing domain member mappingError 2:
java.lang.Exception: oracle.jbo.TooManyObjectsException: JBO-25013: Too many objects match the primary key oracle.jbo.Key[US ].Open languages.xml from the zip file and delete all the XML nodes.
ODI 12c Upgrade Process
Step 1 – ODI Studio Install
Step 2 – Installing 12c Patches
2) Install JDK/Weblogic/DB and ODI 12c in Machine2
3) Configure the ODI12c configuration in Machine2
4) once the above steps completion, please stop all services like managed servers/nodemanager and Adminserver in Machine 2
5) export the ODI 11g repository into machine2 (.dmp file)
6) run the below select query and check whether you able to see the ODI 11g schema in the list
8) now you apply the patch from command prompt
9) once you patch applied successfully, then you ran same select query (from point 6) and see the output
if the version has changed from 11.2.0.3 to 12.1.2.0, then you ran the ua.bat file
in the middle of the upgradation, when it is asking to select the ODi schema that time you have to choose ODI 11g repository schema
and proceed with further steps to complete the upgradation.
10) once it is done, goto ODI_HOME\odi\studio location and run odi.exe to open the ODI 12c studio
11) it is open you just configure the ODI 11g repository schema where it is upgrated to 12.1.2.0 and conne to the repository and verify the code in ODI 12c
1) You can upgrade ODI 11g version (11.1.1.6.0 and 11.1.1.70) to ODI 12c and you can't upgrade from ODI 11g 11.1.1.5.0 to ODi 12c.
2) You can easily upgrade code from ODI 11g 11.1.1.6.0 to ODI 11.1.1.7.0 and for this no need to apply any patch.
3) You can upgrade ODi 11g 11.1.1.5.0 to higher version of ODI11g without applying any patch.
ODI and Heartbleed
OpenSSL Security Bug - Heartbleed / CVE-2014-0160. Please check out the link http://www.oracle.com/technetwork/topics/security/opensslheartbleedcve-2014-0160-2188454.html
However, if your ODI stack includes any web server or proxies or is installed on OEL6 you might want to get check it out right away.
.
OpenTool for Executing a command on Remote machine
I created an open tool which can remote log in into a machine via ssh and execute a command.
You can find the source code and the jar file of the open tool here. This tool is released under Free BSD license.
Instructions for the installation can be found here
Some limitations of the tools:
- Only password based authentication is possible.
- Synchronous parameter is not yet implemented.
Call OS command on a Remote machine
How do you use ODI to call OS command on Remote machine?
Use case:
Solution:
If agent and farFarAwayScript were present in the same machine it would be a simple matter of using OdiOSCommand tool, but the tool does not execute command on remote machines. So, I decided to write a procedure to invoke the farFarAwayScript. Logic of the procedure would be same as a work flow of a human being executing the farFarAwayScript form his/her laptop. It (the human in question, I am trying to be gender neutral here) would login to the machine2 using ssh/telnet/X11 and execute the command. He/She would need the following details to login to the machine and execute the script.- IP address/ Hostname of the remote machine
- Username and Password to log into the remote machine.
- Script name and the complete path.
- Open the SSH client like putty
- Enter the credentials and login
- Execute the script
- Close the client and the connection.
Note: You can use variable or procedure options to pass USER_NAME, PASSWORD, HOST_NAME, SSH_PORT and COMMAND. Additionally error stream and the out stream can be printed to the local files also, this code snippet is meant to be guidance.
import com.jcraft.jsch.*;
String USER_NAME = "user_name";
String HOST_NAME = "host_name";
int SSH_PORT = 22;
String PASSWORD = "password";
String COMMAND = "absolute_path_to_the_command";
JSch jsch = new JSch();
Session session = jsch.getSession(USER_NAME,HOST_NAME, SSH_PORT);
session.setPassword(PASSWORD);
session.setConfig("PreferredAuthentications", "password");
session.setConfig("StrictHostKeyChecking", "no");
session.connect();
Channel channel = session.openChannel("exec");
((ChannelExec) channel).setCommand(COMMAND);
channel.setInputStream(null);
((ChannelExec) channel).setErrStream(System.err);
InputStream in = channel.getInputStream();
channel.connect();
while (true) {
if (channel.isClosed()) {
break;
}
try {
Thread.sleep(1000);
} catch (Exception ee) {
throw ee;
}
}
channel.disconnect();
session.disconnect();
ODI Long running sessions and the firewall. (Happy Valentines day)
Problem:
Long running DB session tasks from ODI are ending with a timeout error.Diagnosis:
When a long running task on the DB is kicked of from ODI. After a certain time ODI session is ending with a timeout error. Irrespective of the status of the task kicked of on the db side. The culprit here was the firewall setting. Due to the corporate policies our firewall was configured to kill any "inactive" connections after an hour. Some of the DB tasks took more than hour, in this time there is no data movement between the agent and the database because these specific task are DML in nature and the processing is confined to the DB session itself. Since, there is no data movement firewall categorized this as an inactive connection and killed it.Now comes the romance of the app and the network team. App blames network, network blames app and they live happily every after. Alas, only if it were true. Since, it was a corporate security policy, this rule could not be relaxed. It was the Apps responsibility to maintain an active connection.
Solution:
IKM SQL Incremental Update (row by row) log file error [Goovy file path seperator exception in Windows]
This exception is specific to windows. It should file perfectly in a UNIX system (I did not test it) .
ODI introduced log for a row by row incremental update KM. This is very exciting as it would help keep track all the rows which miss during update.
To understand this blog post we have to understand the concept of path separators, typically we use backslash as a path separator in windows i.e. when we are referring to a file we use '\' to specify the path to the file from root directory.
IKM in question fetches the path and the separator from the Java properties the default value is
<default>:<?= java.lang.System.getProperty("user.home")?><?=java.lang.System.getProperty("file.separator") ?>ikm_rowbyrow.log
Please see the screenshot below.
This default value gets translated to the path C:\Users\xxxxx\ikm_rowbyrow.log
This value causes the following error in interface execution:
org.apache.bsf.BSFException: exception from Groovy: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
Insert_flow_into_I$_table: 6: unexpected char: '\' @ line 6, column 18.
logFileName = 'C:\Users\xxxxx\ikm_rowbyrow.log'
^
1 error
at org.codehaus.groovy.bsf.GroovyEngine.exec(GroovyEngine.java:110)
at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:376)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:309)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:301)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:865)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:137)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)
Caused by: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
Insert_flow_into_I$_table: 6: unexpected char: '\' @ line 6, column 18.
logFileName = 'C:\Users\lagiseta\ikm_rowbyrow.log'
^
1 error
at org.codehaus.groovy.control.ErrorCollector.failIfErrors(ErrorCollector.java:296)
at org.codehaus.groovy.control.ErrorCollector.addFatalError(ErrorCollector.java:143)
at org.codehaus.groovy.control.ErrorCollector.addError(ErrorCollector.java:113)
at org.codehaus.groovy.control.ErrorCollector.addError(ErrorCollector.java:125)
at org.codehaus.groovy.control.SourceUnit.addError(SourceUnit.java:353)
at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:100)
at org.codehaus.groovy.antlr.AntlrParserPlugin.parseCST(AntlrParserPlugin.java:72)
at org.codehaus.groovy.control.SourceUnit.parse(SourceUnit.java:249)
at org.codehaus.groovy.control.CompilationUnit$1.call(CompilationUnit.java:158)
at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:814)
at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:511)
at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:487)
at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:464)
at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:298)
at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:279)
at groovy.lang.GroovyShell.parseClass(GroovyShell.java:727)
at groovy.lang.GroovyShell.parse(GroovyShell.java:739)
at groovy.lang.GroovyShell.evaluate(GroovyShell.java:574)
at groovy.lang.GroovyShell.evaluate(GroovyShell.java:614)
at groovy.lang.GroovyShell.evaluate(GroovyShell.java:595)
at org.codehaus.groovy.bsf.GroovyEngine.exec(GroovyEngine.java:108)
... 19 more
It seems like there is a problem with Groovy and it does not seems to accept "\" as the file seperator.
Changing the file separator to "/" (forward slash seems to work)
Interface options have to changed as shown below: In the following example I specified absolute path in the interface options for convenience but its recommended to use a variable value to specify the log path.