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:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.