Why P4312 (Purchase Order Receipt) ends in commit fail error when RTRECOUT is activated with error below,
2331/17 WRK:Commit Remote User Mon May 7 10:04:50.269056 dbdrv_log.c196
OS400QL016 - ExecuteSQLRequest: QSQLMSG *LIBL - SQL0723 - SQL trigger &1 in &2 failed with SQLCODE &3 SQLSTATE &4
This issue is specific to PY920 and same is working fine in DV920. Even in PY920 the application P01012 works fine.
Assumption:
When issue is specific to the AS400 platform, below two items need considering,
The outcome between P01012 and P4312 can be different because the latter is running with Transaction Processing on which requires journal to handle rollback or roll forward for a certain transaction. In case you have other RTE implementation you may try other applications (P4210 for instance). The reason for this suggestion is that the error SQL0723 - SQL trigger &1 in &2 failed with SQLCODE &3 SQLSTATE &4 through ODBC is the native error from IBM side. In this case, the only possible cause I can think of is journal when there is no native trigger from DB2. (And again I am not familiar with how to set it up and so on).
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/codes/src/tpc/n723.html
Explanation
During execution of an UPDATE, MERGE, INSERT, or DELETE statement, a trigger was activated. One of the triggered SQL statements received an SQL error condition.
- The issue is specific to RTE routine in iSeries
- Hence I reckon Interop can support this client's issue better
- Based on the native error SQL0723 - SQL trigger &1 in &2 failed with SQLCODE &3 SQLSTATE &4 the issue seems to be related with Journal
* Because same functionality is working fine in running P01012 (which does not require journal or, no transaction processing)
* P4312 is running with transaction processing so in writing any table equivalent table (journal) has to be written
- Client updated that same was working fine in DV920 so make it sure all sql package are deleted for DV920 (this is common practice when all configurations are identical and issue is specific to a certain library or data source)
- JDEDEBUG.log
DB2/400 DBInitReq Connhdl = '800000000000000000008000B4118720' M DBReqHand = '800000000000000000008000B48C6170' NEW ENT02T(Config - PY)
INSERT INTO CONFIGPY/F90710 (ETEVNTID, ETEVNTSEQ, ETEVNTTIME, ETEVNTNAME, ETEVNTTYPE, ETEVNTST, ETENV, ETEVNTUSER, ETUGRP, ETOBNM, ETVER, ETEVNTSNS, ETEVNTSCOPE, ETEVNTHOST, ETEVNTSRT, ETEVNTBSFN, ETFCTNM, ETEVNTPRID, ETEDATA, ETPID, ETUSER, ETMKEY, ETUPMJ, ETUPMT) VALUES ('ENT02T_174353963_6017_2331_050720180957032',0.000000,0/0/0 0:0:0,'RTRECOUT','RTE',2,'JPY920','91395','*ALL','P4312','OSK0001','80000000',' ','ENT02T',' ',' ','XT4312Z1-F43121EndDoc',2331,LONGVARBINARY_DATA,' ',' ',' ',0,0.000000)
OS400QL016 - ExecuteSQLRequest: QSQLMSG *LIBL - SQL0723 - SQL trigger &1 in &2 failed with SQLCODE &3 SQLSTATE &4
JDB9900401 - Failed to execute db request
JDB3400009 - Failed to perform Insert for F90710
JDB9901232 - Canceling transaction because: TC052 InsertTable: Insert failed
: From AppTech point of view the cause of Insert failed is SQL0723 where JDEDB_COMMIT_MANUAL (which is inherited from calling routine with connection string - DB2/400 DBInitReq Connhdl = '800000000000000000008000B4118720' M). So this complaint is like it fails to insert journal not F90710.