How to Resolve Error Message "CLI0111E", "SQL0406", "OS400QL016" in Enterprise Server JDE.LOG

Purpose of Document

The error message: CLI0111E Numeric value out of range is specific to DB2 database. However a similar error can also come from a SQL Server or Oracle database:

Example of the Enterprise Server JDE.LOG:

6256/5544 WRK:Starting jdeCallObject Mon Apr 09 18:11:46.540001 Jdbodbc.c6840
ODB0000163 - wSQLExecute failure. rc=-1

6256/5544 WRK:Starting jdeCallObject Mon Apr 09 18:11:46.555001 Jdbodbc.c6840
ODB0000164 - STMT:00 [22003][-99999] [IBM][CLI Driver] CLI0111E Numeric value out of range. SQLSTATE=22003

6256/5544 WRK:Starting jdeCallObject Mon Apr 09 18:11:46.587001 Odbc_p2.c1613
ODB0000123 - Execute buffered insert, record 1 of 1 failed. ODBC:

6256/5544 WRK:Starting jdeCallObject Mon Apr 09 18:11:46.618000 Jdb_drvm.c1854
JDB9900197 - Failed to flush/clear JDB insert buffer

Note:

The same issue can show up from an iSeries:

718/99 WRK:Starting jdeCallObject Tue Jan 15 18:00:12.853784 dbdrv_log.c196
OS400QL016 - ExecuteSQLRequest: QSQLMSG *LIBL - SQL0406 - Conversion error on assignment to column &2

718/99 WRK:Starting jdeCallObject Tue Jan 15 18:00:12.859848 jdb_drvm.c1143
JDB9900401 - Failed to execute db request

Depending on your RDBMS the error can show up in Call Ojbect Kernel logs as below:

ODBC[Jdbodbc.c,3981] STMT:00 [22003][30160] [IBM][iSeries Access ODBC Driver]Numeric value 1917846520 out of range.
ODB0000163 - wSQLExecute failure. rc=-1
ODB0000164 - STMT:00 [22003][30160] [IBM][iSeries Access ODBC Driver]Numeric value 1917846520 out of range.

OCI0000179 - Error - ORA-31495: error in synchronous change table on "PRODDTA"."F4105"
ORA-01438: value larger than specified precision allowed for this column

OS400QL016 - ExecuteSQLRequest: QSQLMSG *LIBL - SQL0302 - Conversion error on variable or parameter &2

OS400QL016 - ExecuteSQLRequest: QSQLMSG *LIBL - SQL0406 - Conversion error on assignment to column &2

What causes this message and how can it be resolved?

Message Explanation:

Definition of the CLI0111E error:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.messages.cli.doc/doc/mcli00111e.html

Excerpt:

CLI0111E Numeric value out of range

Explanation
Returning the numeric data would have caused the whole part of the number to be truncated. SQLPutData is called more than once for a parameter and the input data is not of type character or binary.

User response
Re-specify the output bindings either through SQLBindCol or SQLGetData to avoid creating a numeric data truncation. Do not call SQLPutData for a parameter if the application data type specified for that parameter through SQLSetParam or SQLBindParameter is not SQL_C_CHAR or SQL_C_BINARY. Additional information: From DB2 documentation: SQLBindCol (and others) belong to standard C Library for any database (in this case, SQL, Oracle, DB2 etc.) which binds columns and ODBC always binds columns (this can be found in any UBE log for Oracle or DB2 which indicates BND1, BND2, etc. without indicating an actual value to assign).

Case Study 1

This error and the source is described in <Bug 13963743> PRP FINISH COMPLETELY WITH JOB STATUS ERROR

Cause
After running PRP, "Allocate Inventory to Project" (message type A) is created for a component. At this time, PRP assigns F3111.TRQT into F3411.LNID. And PRP job ends with job status Error. However, "Generation Successful" is printed on PRP PDF. Side effect of this problem can cause error in DB2 when demand quantity is greater than 1000. F3411.LNID overflows because its maximum value is 999.999.

Case Study 2

Inventory Transaction ends in Commit Fail error in updating Average Cost for a specific set of data. (Data Specific)

Inventory Transaction Applications
|-- xxx End Doc of master business functions called in above listed applications
|-- F4111EndDocument
|-- AverageCostUpdate

Calling Business function UpdateItemCost from F4111EndDocument
IN->[ 1] szCostingMethod [02]
IN->[ 2] cCostLevel [2]
IN->[ 3] mnShortItemNumber [60011]
IN->[ 4] szBranchPlant [ M30]
IN->[ 5] szLocation [A. ]
IN->[ 6] szLot [ ]
IN->[ 7] mnCost [-682542037686393.161600000]
IN->[ 8] szProgramID [EP4205]
IN->[ 9] idF4105RowPointer [1]
IN->[ 10] szSecondItemNumber [210 ]
IN->[ 11] szThirdItemNumber [210 ]
IN->[ 12] cPurchasingSelection []
IN->[ 13] cInventorySelection []
IN->[ 14] cGetDefaultFlag []
IN->[ 15] cCostChangedFlag [1]
Entering JDB_UpdateTable (Table F4105)
UPDATE PRODDTA/F4105 SET COITM=60011.000000,COLITM='400005400',COAITM='110001',COMCU=' 10010',COLOCN=SPACE,COLOTN=SPACE,COLOTG=SPACE,COLEDG='02',COUNCS=-6825420376863931400.000000,COCSPO=SPACE,COCSIN=SPACE, COURCD=SPACE,COURDT=0,COURAT=0.000000,COURAB=0.000000,COURRF=SPACE,COUSER='WANGMM',COPID='EP4205',COJOBN='K3SERVERBA', COUPMJ=110232,COTDAY=140605.000000,COCCFL='1',COCRCS=0.000000,COOSTC=0.000000,COSTOC=0.000000 WHERE ( COLEDG = '02' AND COITM = 60011.000000 AND COMCU = ' M30' AND COLOCN = SPACE AND COLOTN = SPACE ) AND ( COMCU <> ' 10020' )
Entering DBPerformRequest
ODBC[Jdbodbc.c,3981] wSQLExecute failure. rc = -1
ODBC[Jdbodbc.c,3981] STMT:00 [22003][30160] [IBM][iSeries Access ODBC Driver]Numeric value 1917846520 out of range.
ODB0000163 - wSQLExecute failure. rc=-1
ODB0000164 - STMT:00 [22003][30160] [IBM][iSeries Access ODBC Driver]Numeric value 1917846520 out of range.
Exiting DBPerformRequest
JDB9900401 - Failed to execute db request
JDB3600011 - Failed to perform Update for F4105
JDB9901232 - Canceling transaction because: TC033 UpdateTable: update failed
Cancelling Transaction : 1444986890_1256_3948_325084637
Note: The value 1917846520 in log is representation of negative value into positive value so ignore it.

Example 2:

UPDATE PRODDTA.F4105 SET COITM=14499.000000,COLITM='1061954 ',COAITM='1061954 ',COMCU=' 1195400',COLOCN=' ',COLOTN=' ',COLOTG=' ',COLEDG='02',COUNCS=-2745580031710329800000.000000,COCSPO=' ',COCSIN=' ',COURCD=' ',COURDT=0,COURAT=0.000000,COURAB=0.000000,COURRF=' ',COUSER='AGGARWA7',COPID='EP4205',COJOBN='NZAKLEVFN6',COUPMJ=113163,COTDAY=190621.000000,COCCFL='1',COCRCS=0.000000,COOSTC=0.000000,COSTOC=0.000000 WHERE ( COLEDG = '02' AND COITM = 14499.000000 AND COMCU = ' 1195400' AND COLOCN = ' ' AND COLOTN = ' ' )
OCI0000178 - Unable to execute - UPDATE PRODDTA.F4105 SET COITM=:BND1,COLITM=:BND2,COAITM=:BND3,COMCU=:BND4,COLOCN=:BND5,COLOTN=:BND6,COLOTG=:BND7,COLEDG=:BND8,COUNCS=:BND9,COCSPO=:BND10,COCSIN=:BND11, COURCD=:BND12,COURDT=:BND13,COURAT=:BND14,COURAB=:BND15,COURRF=:BND16,COUSER=:BND17,COPID=:BND18,COJOBN=:BND19,COUPMJ=:BND20,COTDAY=:BND21, COCCFL=:BND22,COCRCS=:BND23,COOSTC=:BND24,COSTOC=:BND25 WHERE ( COLEDG = :KEY1 AND COITM = :KEY2 AND COMCU = :KEY3 AND COLOCN = :KEY4 AND COLOTN = :KEY5 )
OCI0000179 - Error - ORA-31495: error in synchronous change table on "PRODDTA"."F4105"
ORA-01438: value larger than specified precision allowed for this column
JDB9900401 - Failed to execute db request
JDB3600011 - Failed to perform Update for F4105
JDB9901232 - Canceling transaction because: TC033 UpdateTable: update failed


Example 3:
In performing Inventory Transfer through P4113, user are getting error as below (iSeries),

UPDATE SEUPDDTA/F4105_ADT SET COITM=76008460.000000,COLITM='76008460',COAITM='201F12/50607',COMCU=' 201F22100',COLOCN=SPACE,COLOTN=SPACE,COLOTG=SPACE,COLEDG='02',COUNCS=1427246608782783.000000,COCSPO=SPACE,COCSIN=SPACE,COURCD=SPACE, COURDT=0,COURAT=0.000000,COURAB=0.000000,COURRF=SPACE,COUSER='AKOLHE2',COPID='EP4113',COJOBN='KPLCPDEU',COUPMJ=113161,COTDAY=85225.000000, COCCFL='1',COCRCS=0.000000,COOSTC=0.000000,COSTOC=0.000000,COCFRGUID='159.82.220.208:5426813708579738280',COCFRMKEY='10.176.6.136', COCFRPID='P4113',COCFRUSER='AKOLHE2',COCFRSEQN=COCFRSEQN+1 WHERE ( COLEDG = '02' AND COITM = 76008460.000000 AND COMCU = ' 201F22100' AND COLOCN = SPACE AND COLOTN = SPACE ) AND ( ( COMCU = ' 201' OR COMCU = ' ALL' OR COMCU = ' 202F' OR COMCU BETWEEN ' 201AA' AND ' 201ZZ' OR COMCU BETWEEN ' 999AA' AND ' 999ZZ' OR COMCU BETWEEN ' 2010000' AND ' 201ZZZZ' OR COMCU BETWEEN ' 20100000' AND ' 201ZZZZZ' OR COMCU BETWEEN ' 201000000' AND ' 201ZZZZZZ' OR COMCU BETWEEN ' 201F000000' AND ' 201FZZZZZZ' OR COMCU BETWEEN ' 201J000000' AND ' 201JZZZZZZ' OR COMCU = ' ' ) )
OS400QL016 - ExecuteSQLRequest: QSQLMSG *LIBL - SQL0302 - Conversion error on variable or parameter &2
OS400QL016 - ExecuteSQLRequest: QSQLMSG *LIBL - SQL0302 - Conversion error on variable or parameter &2
JDB9900401 - Failed to execute db request
JDB3600011 - Failed to perform Update for F4105
JDB9901232 - Canceling transaction because: TC033 UpdateTable: update failed
Cancelling Transaction : 2672965900_305081_413678173
Exiting JDB_UpdateTable with Failure (Table F4105)

Notes:


Case Study 3

In performing Purchase Order Receipt through P4312 with 'Supplier Analysis' option ON transaction ends in COMMIT FAIL ERROR when F4311.PDDJ (Promised Delivery Date) and PO Receipt Date (Transaction Date) difference is more than 2.74 year (e.g., Order is placed 3 years ago).

Cause of issue occurs when computing lead time to update F43090.ALTD and LLTD but defined value for these columns are:

To overcome this type of issue in DB2 database system, apply <Bug 12335832> TRANSACTION ERROR IN P4312 WITH AS400 (if applicable). The change through this fix is that if date difference (long data type) exceeds 999.99, ALTD and LLTD are to be set with value of 999.99.

Snapshot for Call Object Kernel log:

Entering JDB_OpenTable(Table = F43090)
UPDATE E8111DTA/F43090 SET PCAN8=4343.000000,PCITM=60011.000000,PCLITM='210',PCAITM='210',PCMCU=' M30',PCCEFJ=113015,PCCXPJ=125365,PCCERS=SPACE,PCNROU=SPACE,PCTROU=SPACE,PCTFRQ=0.000000,PCORDS=0.000000,
PCTFRD=0.000000,PCLTDJ=0,PCDAYE=0.000000,PCDAYL=0.000000,PCLTQP=9000.000000,PCLTNM=0.000000,PCLTTS=0.000000,
PCALTD=208700.000000,PCLLTD=208700.000000,PCACST=0.000000,PCLCST=0.000000,PCNOOD=1.000000,PCQTYS=20000.000000,
PCQTYR=0.000000,PCQTYC=0.000000,PCQTYW=0.000000,PCQTYJ=0.000000,PCQTYA=0.000000,PCUODR=0.000000,
PCUREC=20000.000000,PCUPTD=0.000000,PCUONT=0.000000,PCUEAR=0.000000,PCULAT=20000.000000,PCAODR=0.000000,
PCAREC=40000.000000,PCAPTD=0.000000,PCAONT=0.000000,PCAEAR=0.000000,PCALAT=40000.000000,PCRONT=0.000000,
PCREAR=0.000000,PCRLAT=1.000000,PCURCD=SPACE,PCURDT=0,PCURAT=0.000000,PCURAB=0.000000,
PCURRF=SPACE,PCROTP=SPACE,PCPOC='0' WHERE ( PCAN8 = 4343.000000 AND PCMCU = ' M30' AND PCITM = 60011.000000 )
OS400QL016 - ExecuteSQLRequest: QSQLMSG *LIBL - SQL0406 - Conversion error on assignment to column &2
OS400QL016 - ExecuteSQLRequest: QSQLMSG *LIBL - SQL0406 - Conversion error on assignment to column &2
JDB9900401 - Failed to execute db request
JDB3600011 - Failed to perform Update for F43090
JDB9901232 - Canceling transaction because: TC033 UpdateTable: update failed
Cancelling Transaction : 176918074_749069_401198248

How to Debug

If this error is encountered in a customer log file, also obtain/review the Call Object Kernel log.

The size of numeric value is smaller than 15 digits (e.g., LNID, SEQ, JELN and so on). Though the size assigned numeric value is longer than the defined size error may not occur.

<Internal_Only>

If the issue is caused by a custom object, the customer should review their code. If this issue is caused by a standard object (application/UBE/BSFN), obtain all logs, name of object and engage the correct application team for additional support.

</Internal_Only>

Case Study 4

P4310 (Purchase Orders) ends in Transaction Failure (or, Commit Fail Error, Asychronous Business Function error) error in AS400 when,

Error in JDE.log:

629/145 WRK:SDONGMYE_80000000_P4310 Tue Feb 4 19:53:22.005096 dbdrv_log.c196
OS400QL016 - ExecuteSQLRequest: QSQLMSG *LIBL - SQL0406 - Conversion error on assignment to column &2

629/145 WRK:SDONGMYE_80000000_P4310 Tue Feb 4 19:53:22.015152 jdb_drvm.c1005
JDB9900401 - Failed to execute db request

629/145 WRK:SDONGMYE_80000000_P4310 Tue Feb 4 19:53:22.015304 jdb_exet.c6157
JDB3400009 - Failed to perform Insert for F43090

629/145 WRK:SDONGMYE_80000000_P4310 Tue Feb 4 19:53:22.015376 jdb_exet.c8112
JDB9901232 - Canceling transaction because: TC052 InsertTable: Insert failed


Error in jdedebug.log:

INSERT INTO DATAE910/F43090 (PCAN8, PCITM, PCLITM, PCAITM, PCMCU, PCCEFJ, PCCXPJ, PCCERS, PCNROU, PCTROU, PCTFRQ, PCORDS, PCTFRD, PCLTDJ, PCDAYE, PCDAYL, PCLTQP, PCLTNM, PCLTTS, PCALTD, PCLLTD, PCACST, PCLCST, PCNOOD, PCQTYS, PCQTYR, PCQTYC, PCQTYW, PCQTYJ, PCQTYA, PCUODR, PCUREC, PCUPTD, PCUONT, PCUEAR, PCULAT, PCAODR, PCAREC, PCAPTD, PCAONT, PCAEAR, PCALAT, PCRONT, PCREAR, PCRLAT, PCURCD, PCURDT, PCURAT, PCURAB, PCURRF, PCROTP, PCPOC) VALUES (4343.000000,60011.000000,'210','210',' M30',114035,125365,' ',' ',SPACE,0.000000,0.000000,0.000000,0,0.000000,0.000000,900000.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000, 0.000000,0.000000,0.000000,0.000000,0.000000,10000.000000,0.000000,0.000000,0.000000,0.000000,0.000000,53010.000000,0.000000,0.000000, 0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,SPACE,0,0.000000,0.000000,SPACE,SPACE,'0')
OS400QL016 - ExecuteSQLRequest: QSQLMSG *LIBL - SQL0406 - Conversion error on assignment to column &2
OS400QL016 - ExecuteSQLRequest: QSQLMSG *LIBL - SQL0406 - Conversion error on assignment to column &2
JDB9900401 - Failed to execute db request
JDB3400009 - Failed to perform Insert for F43090
JDB9901232 - Canceling transaction because: TC052 InsertTable: Insert failed
Cancelling Transaction : 176918074_74495_434326848


Further explanation on error:


This issue is specific to:

: Then nDecimalPosition returns 0 whereas actual value is 2 so 100 is to be multiplied twice (one by this API and the other by DD display decimal) so value computed by this API changes to 900000 which is longer than defined length 5.

How to resolve:
Apply fix against <Bug 18185838> - DEFAULT VALUE FOR LTQP IGNORING DECIMAL PLACES when it is applicable