Overflow On SNP_VAR_DATA Work Repository Table When ODI 10g Executions Contain Variables
(Doc ID 472120.1)
Last updated on JUNE 16, 2022
Applies to:
Oracle Data Integrator - Version 3.2.03.01 to 10.1.3.5.6_01Information in this document applies to any platform.
Purpose
The purpose of this document is to resolve the situations resulting in unexpected and unexplained failures, session hanging or very slow performance of Oracle Data Integrator (ODI) 10g process executions which previously ran successfully with no issue.
Behavior and error messages
If a Scenario has been scheduled to be executed on a Scheduler Agent, the corresponding Session hangs or tale a considerable time to start execution (20 minutes have been encountered in some limiting conditions) without any error message.
In some situations, a violation of PK_VAR_DATA is signaled during the execution.
Messages such as:
java.sql.SQLException: Numeric Overflow
or
java.lang.NullPointerException
at com.sunopsis.dwg.dbobj.SnpVarSess.setValue(SnpVarSess.java)
are signaled in any other case.
Identify the origin of problem
The consultation of ODI Operator module records shows that the problem arises when the Session comes to a step which refreshes or sets an ODI Variable.
Further analysis of Oracle Data Integrator (ODI) Variables defined in Designer indicates that a large number of them are of type "Historize" or "Last Value".
After having checked the Work Repository tables, one of the following situations may be observed:
- The SNP_VAR_DATA table contains records with negative I_VAL values.
This is due to a Java numeric overflow problem and was fixed in version 10.1.3.2.0 of ODI.
- ID_NEXT in SNP_ID table (for record having ID_TBL equal to 'SNP_VAR_DATA') has reached its largest value (which appears to be 9,999,999).
This is due to the very large number of Variable refresh operations already performed during executions.
- Starting with ODI 10.1.3.2.0, ID_NEXT in SNP_ID table (for record having ID_TBL equal to 'SNP_VAR_DATA') is lower than the largest value of I_VAL in SNP_VAR_DATA table.
- The SNP_VAR_DATA table contains a considerable number of records (into the millions)
Troubleshooting Steps
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Purpose |
Behavior and error messages |
Identify the origin of problem |
Troubleshooting Steps |
References |