Performance Enhancement in Inventory Transaction

Some of Inventory Transaction may perform slowly in updating F4102.MRPC - NetChangeFlag (Flag - Net Change) without Table Lock information from jdedebug.log (or Call Object Kernel Log),

Example of SQL2005:

ODBC[Jdbodbc.c,4646] wSQLCloseCursor - warning: invalid cursor state failure. rc = -1
ODBC[Jdbodbc.c,4646] STMT:00 [24000][0] [Microsoft][SQL Native Client]Invalid cursor state
SELECT IBITM, IBMCU, IBVEND, IBMRPC, IBUSER, IBPID, IBJOBN, IBUPMJ, IBTDAY FROM JDE_CRP.CRPDTA.F4102 WHERE ( IBITM = 12.000000 AND IBMCU = ' 2500' )
ODBC[Jdbodbc.c,7804] wSQLExecute failure. rc = -1
ODBC[Jdbodbc.c,7804] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired

ODBC[Jdbodbc.c,7834] STMT:00 [24000][0] [Microsoft][SQL Native Client]Invalid cursor state
SELECT IBITM, IBMCU, IBVEND, IBMRPC, IBUSER, IBPID, IBJOBN, IBUPMJ, IBTDAY FROM JDE_CRP.CRPDTA.F4102 WITH (NOLOCK) WHERE ( IBITM = 12.000000 AND IBMCU = ' 2500' )
Fetched the record

Note:

In some database there may not be locking against table F4102 in issuing SELECT.
In Some database, UPDATE may cause deadlock if update of F4102.MRPC occurs in Transaction Processing (or MANUAL COMMIT).

To avoid locking/deadlock, install ESU which contains P4114 Slow Processing (Bug ID 11033723)

This fix/enhancement can be applied when:

1. ENTER: F4113FSEndDoc
   2. ENTER: F4111EndDocument
      3. ENTER: UpdateF4102MRPNetChg
      SQL> SELECT IBITM, IBMCU, IBVEND, IBMRPC, IBUSER, IBPID, IBJOBN, IBUPMJ, IBTDAY FROM JDE_CRP.CRPDTA.F4102 WHERE ( IBITM = 12.000000 AND IBMCU = ' 2500' )
      SQL> UPDATE JDE_CRP.CRPDTA.F4102 SET IBITM=12.000000,IBMCU=' 2500',IBVEND=0.000000,IBMRPC='1',IBUSER='YNASSAR',IBPID='EP4113',IBJOBN='PMSENT01',IBUPMJ=110087,IBTDAY=133721.000000 WHERE ( IBITM = 12.000000 AND IBMCU = ' 2500' )
      3. EXIT: UpdateF4102MRPNetChg
   2. EXIT: F4111EndDocument
1. EXIT: F4113FSEndDoc

1. ENTER: F4113FSEndDoc
   2. ENTER: F4111EndDocument
   SQL> SELECT IBITM, IBMCU, IBVEND, IBMRPC, IBUSER, IBPID, IBJOBN, IBUPMJ, IBTDAY FROM JDE_CRP.CRPDTA.F4102 WHERE ( IBITM = 12.000000 AND IBMCU = ' 2500' )
   SQL> UPDATE JDE_CRP.CRPDTA.F4102 SET IBITM=12.000000,IBMCU=' 2500',IBVEND=0.000000,IBMRPC='1',IBUSER='YNASSAR',IBPID='EP4113',IBJOBN='PMSENT01',IBUPMJ=110087,IBTDAY=133721.000000 WHERE ( IBITM = 12.000000 AND IBMCU = ' 2500' )
   2. EXIT: F4111EndDocument
1. EXIT: F4113FSEndDoc

Note:

For this example, two BSFNs (F4111EndDocument and UpdateF4102MRPNetChg) opens table F4102 which (may) cause locking.
By removing calling of UpdateF4102MRPNetChg it minimizes possible locking issue against table F4102.
Currently this fix/enhancement is available for E1 Release 9.0 or E900 only.
In some DB, above transaction cause Zombie Kernel caused by deadlock.
Fix/Enhancement is done for BSFN XT4111Z1 - F4111EndDoc.

Performance Enhancement in Inventory Transaction

Goal

Solution