Troubleshooting
Problem
When running dbverify.cmd/sh in IBM Sterling B2B Integrator (B2Bi), it was found that the script would try to change certain tables which had NUMBER or INT data type columns.
Symptom
The EFrame scripts created by the dbverify.cmd/sh <si_install>/bin) will have unnecessary SQL in the EFrame_TableChanges.sql, EFrame_IndexDrops.sql and the EFrame_IndexAdds.sql files.
Cause
The problem was found to be with certain Oracle 11g Drivers (Oracle Bug 12775220) where the drivers were sending an incorrect decimalDigits value for data_type NUMBER and INT back to SBI and then dbverify.cmd/sh would interpret this as a difference from the expected database design and try to correct these columns back to the correct format. For example:
column name=WFD_ID size=0 decimalDigits=-127 type name=NUMBER
column name=WFD_VERSION size=0 decimalDigits=-127 type name=NUMBER
column name=WORKFLOW_ID size=0 decimalDigits=-127 type name=NUMBER
Instead of
column name=WFD_ID size=22 decimalDigits=0 type name=NUMBER
column name=WFD_VERSION size=22 decimalDigits=0 type name=NUMBER
column name=WORKFLOW_ID size=22 decimalDigits=0 type name=NUMBER
Diagnosing The Problem
In the reverse.xml file created when dbverify.cmd/sh is run you will find entries for each table containing number/int columns showing DecimalDigits with value -127. For example:
<Entity Cacheable="true" Name="WORKFLOW_CONTEXT" TableName="WORKFLOW_CONTEXT">
<Attributes>
<Attribute ColumnName="WFD_ID" DecimalDigits="-127"
Name="Wfd_Id" Nullable="false" Size="0" Type="NUMBER"/>
<Attribute ColumnName="WFD_VERSION" DecimalDigits="-127"
Name="Wfd_Version" Nullable="false" Size="0" Type="NUMBER"/>
Resolving The Problem
There is an Oracle Bug 12775220 related to this behavior. Oracle reports this bug is fixed in 12.1.0.1 (Base Release) or higher and or 11.2.0.4 for 11g
In order to correct this behavior with the dbverify.sh/cmd you will need to do the following:
1) Update the Oracle ojdbc6.jar driver inside of SBI to a minimum of 11.2.0.4 for 11g. No need to update the ojdbc7.jar driver for Oracle 12c installs
2) Modify the dbverify.cmd/sh script and add the following JVM argument,
"-Doracle.jdbc.J2EE13Compliant=true"
Windows dbverify.cmd Example:
%JAVA% -Doracle.jdbc.J2EE13Compliant=true -DTEXT_SEARCH_ENABLED= etc:
Unix/Linux dbverify.sh Example:
${JAVA} -Xms256m -Xmx512m -Doracle.jdbc.J2EE13Compliant=true -classpath /si/SI_INSTALLS/SI524_ORA11_4000_Node2/jar/bootstrapper.jar etc:
Note:
As of SBI 5020500_5 the dbverify.sh/cmd script has been updated to include the "-Doracle.jdbc.J2EE13Compliant=true" reference.
Additional Note:
If patching/upgrading a 5.2.x install of B2Bi using an Oracle DB you may experience the following linked issues:
1) Upgrading, Patching a Fix Pack or Interim Fix of Sterling B2B Integrator is Taking an Exceedingly Long Time Applying the Schema ChUpgrading, Patching a Fix Pack or Interim Fix of Sterling B2B Integrator is Taking an Exceedingly Long Time Applying the Schema Changesanges
2) ORA-00054 Resource Busy and Acquire with NOWAIT Specified or Timeout Expired Error when Upgrading, Applying Fix Pack or Interim Fix
Was this topic helpful?
Document Information
Modified date:
14 November 2019
UID
swg21882781