webMethods

Insert xml into the B2B database table and insert EDI846 with 32 entries at the same time, but 0013 is not inserted.It is normal to execute it again.
It happened twice a year last year.What’s the problem? Thank you for telling me.

Error log:

Adapter Runtime: Error Logged. See Error log for details. Error: [ADA.1.316] Cannot execute the SQL statement "SELECT DISTINCT T.BATCH_ID,T.MESSAGE_ID,T.ISA_CONTROL_NO,T.GS_CONTROL_NO,T.ST_CONTROL_NO  
FROM BS_IN_MESSAGE_STATUS T 
WHERE ((T.MESSAGE_ID = '5086562175' 
AND T.TRANSACTIONSET_ID='846')
OR (T.ISA_CONTROL_NO = '000181376' 
AND T.GS_CONTROL_NO = '34240' 
AND T.ST_CONTROL_NO = '0013'))
AND T.ISA_SENDER_ID = '0607047'
AND T.ISA_RECEIVER_ID = 'ZDT         '
AND T.PROCESS_STATUS != 'EE' 
--AND T.PROCESS_STATUS IN ('IC','CO')". "
(17410) 沒有從埠座讀取資料"
沒有從埠座讀取資料
2023-02-01 01:02:22 TST [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service APP.APP_ZDT_846.DB:sel_Check_Duplicate.
[ADA.1.316] Cannot execute the SQL statement "SELECT DISTINCT T.BATCH_ID,T.MESSAGE_ID,T.ISA_CONTROL_NO,T.GS_CONTROL_NO,T.ST_CONTROL_NO  
FROM BS_IN_MESSAGE_STATUS T 
WHERE ((T.MESSAGE_ID = '5086562175' 
AND T.TRANSACTIONSET_ID='846')
OR (T.ISA_CONTROL_NO = '000181376' 
AND T.GS_CONTROL_NO = '34240' 
AND T.ST_CONTROL_NO = '0013'))
AND T.ISA_SENDER_ID = '0607047'
AND T.ISA_RECEIVER_ID = 'ZDT         '
AND T.PROCESS_STATUS != 'EE' 
--AND T.PROCESS_STATUS IN ('IC','CO')". "
(17410) 沒有從埠座讀取資料"
沒有從埠座讀取資料
2023-02-01 01:02:22 TST [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ADA.1.107] Failed to rollback local transaction."java.sql.SQLException: IO 異常: Software caused connection abort: socket write error"
IO 異常: Software caused connection abort: socket write error
2023-02-01 01:02:22 TST [SCC.0121.0017E] rollback failed: [ADA.1.107] Failed to rollback local transaction."java.sql.SQLException: IO 異常: Software caused connection abort: socket write error"
IO 異常: Software caused connection abort: socket write error javax.transaction.xa.XAException xid = eaiap02.sg.fat.com_5555/1664503741065 resource = DB_Connections.JDBCAdapter:B2B_Conn
2023-02-01 01:02:22 TST [ISP.0090.0004E] APP.APP_ZDT_846.Component.InsertToDB:execute -- MessageID:5086562115 insert to message status table error:[ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service APP.APP_ZDT_846.DB:sel_Check_Duplicate.
[ADA.1.316] Cannot execute the SQL statement "SELECT DISTINCT T.BATCH_ID,T.MESSAGE_ID,T.ISA_CONTROL_NO,T.GS_CONTROL_NO,T.ST_CONTROL_NO  
FROM BS_IN_MESSAGE_STATUS T 
WHERE ((T.MESSAGE_ID = '5086562175' 
AND T.TRANSACTIONSET_ID='846')
OR (T.ISA_CONTROL_NO = '000181376' 
AND T.GS_CONTROL_NO = '34240' 
AND T.ST_CONTROL_NO = '0013'))
AND T.ISA_SENDER_ID = '0607047'
AND T.ISA_RECEIVER_ID = 'ZDT         '
AND T.PROCESS_STATUS != 'EE' 
--AND T.PROCESS_STATUS IN ('IC','CO')". "
(17410) 沒有從埠座讀取資料"
沒有從埠座讀取資料
2023-02-01 01:02:22 TST [ISP.0090.0004E] Composite_Framework.Svc:startComposite -- insert to db have error

Product webMethods Integration Server
Version 9.12.0.0
Updates IS_9.12_Core_Fix7
IS_9.12_SPM_Fix2
TNS_9.12_Fix4
Build Number 91
SSL Strong (128-bit)

#webMethods
#B2B-Integration
#edi

Attach adapter connection information：

DB_Connections.JDBCAdapter:B2B_Conn Details
Connection Type webMethods Adapter for JDBC Connection
Package Name ZDT_DB_Connections
Connection Properties
Transaction Type LOCAL_TRANSACTION
DataSource Class oracle.jdbc.pool.OracleConnectionPoolDataSource
serverName
user ZDT_EDI
password ******
databaseName
portNumber
networkProtocol
Other Properties url=jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST =...)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = ...)(PORT = 1521)) (LOAD_BALANCE = yes) )(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = zdtedidb) (failover_mode =(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 30)(DELAY = 5))))
Connection Management Properties
Enable Connection Pooling true
Minimum Pool Size 0
Maximum Pool Size 50
Pool Increment Size 1
Block Timeout (msec) 1000
Expire Timeout (msec) 1000
Startup Retry Count 0
Startup Backoff Timeout (sec) 10

#B2B-Integration
#webMethods
#edi

Hi Sarah,

it is difficult to investigate this without further informations.
Connection Info you shared is a local transaction connection, but the error message refers to an XA transaction related issue.
Which JDBC Adapter version are you using with which Fix level?
Which Jar versio are you using for the ojdbc jar?
What is your Database version?
Any Fixes for the EDI packages applied?

Regards,
Holger

#edi
#B2B-Integration
#webMethods

Hi, Holger,
Thank you for your suggestion.

There is no error record in TN, and 32 item records are in successful status.
The database connection where table(BS_IN_MESSAGE_STATUS) is located is DB_Connections.JDBCAdapter:B2B_Conn, Attach B2B_ Conn and JDBC Pool connection settings below.

The system has not had this problem for six years, but it has occurred twice in the past six months. Should we use ojdbc6.jar?
Looking forward to your reply.

#B2B-Integration
#edi
#webMethods

Hi Sarah,

for IS 9.12 you can use latest available ojdbc8.jar as IS 9.12 is running in a Java 8 JVM.
Remember to remove the other ojdbc-jars completely from IS classpath to avoild class loading issues.
The number in the ojdbc-jar file name indicate the Java version it was built with and can be used with (or newer Java version).
ojdbc14.jar and classes12.jar are fairly outdated now as they were referring to Java 1.4 and Java 1.2 and are not implementing the latest JDBC specs.

You might want to consider upgrading your Database to Oracle 18c or 19c.
To do so get in contact with your DBA.
Oracle 21c might be a bit to new for webMethods 9.12.

Please check the JDBC Adapter Users Guide for informations about Transaction Handling in JDBC Adapter related services.

Please check your logs for informations about Insert related error messages.
When insert fails it is obvious that select cannot find the data.

Still wondering why the server log reports an XA transaction related socket write error when the connection being used is configured as a LOCAL transaction.

Regards,
Holger

#B2B-Integration
#edi
#webMethods

Hi Holger,
Thank you for your reply.
I know what you mean. However, the corresponding relationship between oracle version and driver ojdbc and jdk version is shown in the figure below. So is ojdbc6.jar more suitable? After all, the Oracle version is 11.2, not higher.

You have mentioned local transaction and XA transaction again. What is the biggest difference between them? You mean this error has nothing to do with ojdbc-jars, right?

I just reviewed the logs under D:\SoftwareAG912\IntegrationServer\instances\default\logs. There was a database connection exception before data insertion. Is it related to this? Attach log file.
PS: Are there any other logs recorded? What am I missing here?
server.20230201.log (119.3 KB)

Hoping for your replies

#B2B-Integration
#edi
#webMethods

Hi Sarah,

please check the error log in IS Admin, there should be a related error message there.

Please check the service Composite_Framework.Svc:startComposite as there are some messages in the server.log related to it.
Please check your Fix set as there seems to be an issue between the WmTN and the WmPRT package.
There are some messages related with invalid IP-Adresses when trying to send out emails, please check that.

A Select service for db usually does not require transaction handling, so you can use a NO_TRANSACTION connection here.
XA Tranasctions are only required when inserting/updating into multiple db schema or in combination with other adapters which support transaction handling to make sure that all systems participating in one transaction are committed or rolled back altogether. When only one schema is affected you should use LOCAL_TRANSACTION instead, which has less overhead for managing the transaction compared to XA_TRANSACTION.
This has nothing to do with the ojdbc jars directly.

Regarding the ojdbc jars, you can use an ojdbc jar developed for Oracle 19c to connect to any db version between 11.2 and 19.x. Only requirement is that your IS uses a JVM matching the number in the jar file name or newer.

Regards,
Holger

#edi
#B2B-Integration
#webMethods

Hi Holger,

Thank you for your suggestion.
The screenshot of the log in IS Admin is as follows. Do you mean this log?

Invalid IP-Adresses has been fixed, please ignore this.
The same service composite_ Framework. Svc: startComposite is running in another system, and the other system has not experienced this exception, and I have checked again, which should be able to eliminate this service problem,
I will continue to look for reasons. Thank you for your help.

#webMethods
#B2B-Integration
#edi

Hi Sarah,

I found the following answer in the following article:

This is a very low-level exception, which is ORA-17410.

It may happen for several reasons:
1.     A temporary problem with networking.
2.     Wrong JDBC driver version.
3.     Some issues with a special data structure (on database side).
4.     Database bug.

In my case, it was a bug we hit on the database, which needs to be patched.

Regards,
Holger

#B2B-Integration
#edi
#webMethods

Hi Holger,

Thank you for your suggestion.
Confirm with you again that the jdk version of IS 9.12 is based on the path of IS classpath, not the system variable Java_ Home, right?
The DBA is still checking the log.
Later, I will remove the JDBC driver and replace it with ojdbc8.jar or ojdbc6.jar. I will continue to track this message.

Thank you again.

#webMethods
#edi
#B2B-Integration

Hi Sarah,

IS has its own java path settings in wrapper.conf, custom_wrapper.conf and/or setenv.sh.

Regards,
Holger

#B2B-Integration
#edi
#webMethods

Hi Holger,

After viewing the configuration file of IS and the Server Environment (as shown in the following picture), it should be confirmed that IS 9.12 is running in a Java 8 JVM.

wrapper.conf:
wrapper.java.command=D:\SoftwareAG912\jvm\jvm\jre\bin\java

Consider upgrading the ojdbc jar file, but because the running environment is a formal system and cannot be monitored for a long time in the test environment, it is not easy to catch this exception. So I think there is a certain risk of replacing directly in the formal environment. What do you think?

I will add this kind of exception email reminder later.
Thank you for your timely reply recently.

#edi
#B2B-Integration
#webMethods

Hi Sarah,

as you can see in the screen shot, the IS is running in Java-Version 1.8.0_101, which is the internal numbering schema for java versions from SUN/Oracle while publically the Java Versions are mentioned as Java 8 (beginning with Java 5 resp. 1.5.).

You can check this in a Command Window by navigating to the specified path “D:\SoftwareAG912\jvm\jvm\jre\bin” and call “java.exe -version”.
This will the show the complete java version info (more detailed than that which IS shows in its About page).

The value “(52.0)” in the Java Version entry refers to the maximum class file version which can be used with this JVM.
All class file version up to 52.0 can be loaded into this JVM, but 53.0 (or newer) will result in an incompatible class file version when trying to load it.

Regards,
Holger

#edi
#B2B-Integration
#webMethods

Hi Holger,

Thank you again.
I will continue to update the solution later.

#B2B-Integration
#webMethods
#edi