IBM Support

Running dbverify.cmd/sh using an Oracle 12g or 11g database can cause table modification SQL to be unnecessarily created even though the tables are already defined correctly.



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.


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.


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">
<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 (Base Release) or higher and or for 11g

In order to correct this behavior with the you will need to do the following:
1) Update the Oracle ojdbc6.jar driver inside of SBI to a minimum of 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,

Windows dbverify.cmd Example:
%JAVA% -Doracle.jdbc.J2EE13Compliant=true -DTEXT_SEARCH_ENABLED= etc:

Unix/Linux Example:
${JAVA} -Xms256m -Xmx512m -Doracle.jdbc.J2EE13Compliant=true -classpath /si/SI_INSTALLS/SI524_ORA11_4000_Node2/jar/bootstrapper.jar etc:

As of SBI 5020500_5 the 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

[{"Product":{"code":"SS3JSW","label":"IBM Sterling B2B Integrator"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"5.2","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

Modified date:
14 November 2019