Tuesday, October 23, 2012

Logic of assigning Conversation Id for polling BPEL process – SOA 11g



        Recently we ran into an issue where we were getting issues with Conversation ID in SOA .Since we are not directly involved in generating the Conversation ID it was something we had not expected.
So one of the learning’s that we got out of this issue was that the payload data was also being used to create the conversation Id’s.
In our case we were having a composite primary key which was a collection of 4 to 5 columns. There was a DB Adapter polling these records. The error message was thrown not from the process but from the SOA engine.
Error Message: 
0000000000004fd1,0] [APP: soa-infra] JCABinding=>  db_poll2 db_poll2Adapter Service db_poll2 was unable to perform delivery of inbound message to the composite default/ConvIdPollerTest!1.0*soa_15be5514-092f-44ce-b44d-95726a0486f1 due to: Exception [TOPLINK-4002] (Oracle TopLink - 11g Release 1 (11.1.1.6.0) (Build 111018)): oracle.toplink.exceptions.DatabaseException[[
Internal Exception: java.sql.BatchUpdateException: ORA-12899: value too large for column "DEV116_SOAINFRA"."COMPOSITE_INSTANCE"."CONVERSATION_ID" (actual: 125, maximum: 100)

Since it was to do with SOAINFRA tables we had a look at the SOAINFRA COMPOSITE_INSTANCE table. The CONVERSATION_IS column was of 100 char.

The issue was coming because the SOA engine used the primary key values from polled records and concatenated them to a unique Id to make the conversation ID.


Since there were records where the combined concatenated value was more than 100 chars those records where failing when SOA Engine tries to make an entry into COMPOSITE_INSTANCE table for
Instance tracking.


There is no straight forward solution as this is a product limitation. Solution was to have a workaround to find a key which is unique but not too lengthy J

Monday, October 22, 2012

Xpath to retrieve Group Attribute values from Oracle B2B message


It was on basic xpaths for retrieving the invoice id and other information based on segments.
//*[local-name()=' Element-143']/text()  -  This works for fetching data from the ST segment (Transaction Type– highlighted below in green).
B2B uses the information from edi file and agreement information to populate Group Information – Group Control Number (GS06) , Group Functional Identifier (GS01).
But for fetching the Group Information – Group Control Number (GS06) , Group Functional Identifier (GS01) XPath expression is bit complex and I am listing them below.

So for example to list GroupSenderId value::

//*[local-name()='Property' and 'GroupSenderID'=@*[local-name()='Name']]/text()

So for example to list GroupReceiverID value ::
//*[local-name()='Property' and 'GroupReceiverID'=@*[local-name()='Name']]/text()

 

B2B message snippet

-------------------------------------------
<?xml version = '1.0' encoding = 'UTF-8'?>
<Transaction-810  XDataVersion="1.0"  Standard="X12"  Version="5010"  GUID="{38353832-3932-3330-3134-323936313736}">
<ns1:Internal-Properties>
<ns1:Data-Structure  Name="Interchange">
<ns1:Lookup  Name="InterchangeSenderID">THO123</ns1:Lookup>
<ns1:Property  Name="InterchangeSenderID">THO123</ns1:Property>
<ns1:Lookup  Name="InterchangeReceiverID">GEO456</ns1:Lookup>
<ns1:Property  Name="InterchangeReceiverID">GEO456</ns1:Property>
<ns1:Data-Structure  Name="Group">
<ns1:Lookup  Name="GroupSenderID">THO123</ns1:Lookup>
<ns1:Property  Name="GroupSenderID">THO123</ns1:Property>
<ns1:Lookup  Name="GroupReceiverID">GEO456</ns1:Lookup>
<ns1:Property  Name="GroupReceiverID">GEO456</ns1:Property>
<ns1:Data-Structure  Name="Transaction"/>
</ns1:Data-Structure>
</ns1:Data-Structure>
</ns1:Internal-Properties>
<ns1:Segment-ST>
<ns1:Element-143>810</ns1:Element-143>
<ns1:Element-329>#ControlNumber#</ns1:Element-329>
</ns1:Segment-ST>
<ns1:Segment-BIG>
<ns1:Element-373_1>20120910</ns1:Element-373_1>
</ns1:Segment-BIG>

……………………………….
<ns1:Segment-TDS>
<ns1:Element-610_1>391262</ns1:Element-610_1>
</ns1:Segment-TDS>

……………………

<ns1:Segment-SE>
<ns1:Element-96>#SegmentCount#</ns1:Element-96>
<ns1:Element-329>#ControlNumber#</ns1:Element-329>
</ns1:Segment-SE>
</Transaction-810>

Thanks To Girish for the help.

Friday, October 19, 2012

Issue with DBAdapter Update due to char datatype & shouldTrimStrings property


Recently I looked into a technical issue with DB adapter where the update was not working even though it was not throwing any error. Everything seems to be working fine but the field is never updated.

All the settings looked fine but the update was not happening. So we checked the table which revealed that the all the string fields were of char type. It didn’t seem to value much as my understanding was that char will work similar to varchar. I was puzzled to see the Note from Oracle @Oracle DBAdapter documentation ,but it was true.

It reads as :: Oracle recommends that you use varchar instead of char for primary key columns, otherwise you must set the weblogic-ra.xml property shouldTrimStrings to false. The truncation of trailing spaces could cause the primary key to be read incorrectly, making it impossible to update read rows as processed.

Issue :: The Primary Key fields in Database were of char type and data contains trailing spaces. Since by default shouldTrimStrings property is set to true, weblogic will trim these spaces, there by it will not be able to fetch the correct rows from DB. So update fails as it will not be able to fetch the row properly.
Solution::
Set the shouldTrimStrings property to false in weblogic DBAdapter Outbound Connection Properties
By default the property shouldTrimStrings is not visible in Outbound Connection Properties, so we will not able to change it.
So to modify the value a new entry needs to added to the DBAdapter config  properties. This is one time configuration task that needs to done by updating the ra.xml from filesystem.
Steps
1.    Take a backup of DBAdapter.rar
2.     Extract the entire content.(use unzip tools)
3.     Edit the META-INF/ra.xml.
Add a property in ra.xml file
           <config-property>
               <config-property-name>shouldTrimStrings</config-property-name>
               <config-property-type>java.lang.Boolean</config-property-type>
               <config-property-value>false</config-property-value>
            </config-property>
   
4.    Re-Create the DbAdapter  rar file.
>>>>jar.exe -cvf DbAdapter.rar -C DbAdapter/ .
added manifest
adding: DBAdapter.jar(in = 551405) (out= 519781)(deflated 5%)
ignoring entry META-INF/
ignoring entry META-INF/MANIFEST.MF
adding: META-INF/ra.xml(in = 13473) (out= 2866)(deflated 78%)
adding: META-INF/weblogic-ra.xml(in = 6713) (out= 1775)(deflated 73%)
adding: toplink-grid.jar(in = 102529) (out= 91349)(deflated 10%)
5.    Update the DBAdapter from Deployments
From Console the property should be available for all the database adapter connection under Outbound Connection Properties.

Thanks to Thani, Lee, Sampath for helping with this issue.