Technical Blog Post
Abstract
75 ways to demystify DB2: #55: Techtip: How to troubleshoot and fix Data Truncation warning - CLI0002W returned from a Unicode CLI application?
Body
How to replicate and fix CLI0002W - Data Truncation warning returned by Unicode CLI application using a CLI script?
Connect to the database:
C:\Program Files\IBM\SQLLIB_02\BIN>db2 connect to sample
Database Connection Information
Database server = DB2/NT64 10.5.5
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
Create a Stored Procedure:
C:\Program Files\IBM\SQLLIB_02\BIN>db2 "create procedure testsp(out c1 char(50))
language sql begin set c1 = 'Developer Works'; end"
DB20000I The SQL command completed successfully.
CLI Script:
opt calldiag on
quickc 1 1 sample
sqlallocstmt 1 1
getmem 1 1 sql_c_wchar 20
sqlprepare 1 "CALL DB2ADMIN.TESTSP(?)" -3
sqlbindparameter 1 1 sql_param_output sql_c_wchar sql_char 20 0 1
sqlexecute 1
Execute the CLI script:
C:\Program Files\IBM\SQLLIB_02\BIN>db2cli < cli0002
IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
> > SQLAllocEnv: rc = 0 (SQL_SUCCESS)
CLI henv = 1, Test Driver henv = 1
SQLAllocConnect: rc = 0 (SQL_SUCCESS)
CLI hdbc = 1, Test Driver hdbc = 1
SQLConnect: rc = 0 (SQL_SUCCESS)
> SQLAllocStmt: rc = 0 (SQL_SUCCESS)
CLI hstmt = 1, Test Driver hstmt = 1
> GetMem: memory buffer 1 for statement handle 1 allocated.
> SQLPrepare: rc = 0 (SQL_SUCCESS)
> SQLBindParameter: rc = 0 (SQL_SUCCESS)
> SQLExecute: rc = 1 (SQL_SUCCESS_WITH_INFO)
SQLError: rc = 0 (SQL_SUCCESS)
SQLGetDiagRec: SQLState : 01004
fNativeError : -99999
szErrorMsg : [IBM][CLI Driver] CLI0002W Data truncated. SQLSTATE=01004
cbErrorMsg : 58
> >
Memory count is 44
Parsed DB2 trace snippet:
[Record=47468, pid 2172 tid 8456 cpid 21260 node 0]
Var[00] Type: 0X3D Len: 0X0032
[Record=47468, pid 2172 tid 8456 cpid 21260 node 0]
Row:1
000: NCHAR(50): "Developer Works "
"Developer Works" with a bunch of spaces, the total length is 50 bytes.
CLI trace snippet:
[07/31/2015 17:49:57.186284] SQLBindParameter( hStmt=1:1, iPar=1, fParamType=SQL_PARAM_OUTPUT, fCType=SQL_C_WCHAR, fSQLType=SQL_CHAR, cbColDef=20, ibScale=0, rgbValue=&000000000055fd80, cbValueMax=20, pcbValue=&000000000055fd34 )
[07/31/2015 17:49:57.188369] ---> Time elapsed - +1.640000E-003 seconds
:
[07/31/2015 17:49:57.190296] SQLExecute( hStmt=1:1 )
[07/31/2015 17:49:57.190783] ---> Time elapsed - +1.531000E-003 seconds
[07/31/2015 17:49:57.190994] ( Package="SYSSH200 ", Section=4 )
[07/31/2015 17:49:57.191541]
sqlccsend( Handle - 0000000005633696 )
sqlccsend( ulBytes - 498 )
sqlccsend( ) rc - 0, time elasped - +4.000000E-006
sqlccrecv( timeout - +0.000000E+000 )
sqlccrecv( ulBytes - 276 ) - rc - 0, time elapsed - +9.180000E-004
Elapsed Server Processing Time - +8.850000E-004
[07/31/2015 17:49:57.193520] ( Row=1, iPar=1, fCType=SQL_C_WCHAR, rgbValue="Developer" - x'44006500760065006C006F007000650072000000', pcbValue=100, piIndicatorPtr=100 )
[07/31/2015 17:49:57.208816]
Elapsed Server Processing Time - +1.100000E-004
[07/31/2015 17:49:57.209248] ( return=0 )
[07/31/2015 17:49:57.209614]
[07/31/2015 17:49:57.209808] SQLExecute( )
[07/31/2015 17:49:57.209996] <--- SQL_SUCCESS_WITH_INFO Time elapsed - +1.970000E-002 seconds
:
:
[07/31/2015 17:49:57.217858] SQLGetDiagRec( pszSqlState="01004", pfNativeError=-99999, pszErrorMsg="[IBM][CLI Driver] CLI0002W Data truncated. SQLSTATE=01004", pcbErrorMsg=58 )
[07/31/2015 17:49:57.218732] <--- SQL_SUCCESS Time elapsed - +3.997000E-003 seconds
As seen in the CLI trace, the length of cbColDef is 20 bytes, that is equivalent to 20 double bytes since its SQL_C_WCHAR, so it comes to 40 bytes.
40 bytes < 102 (50*2+2 null terminator) so it throws CLI0002W, you should increase cbColDef to 102 or higher.
So, when you use SQLBindParameter make sure the column size is at least 102 bytes (50 * 2 for SQL_C_WCHAR, then 2 bytes for the Null terminator) otherwise the your application/script will fail with Data Truncation messages.
Modified script (with increased cbColDef resolved the CLI0002W):
opt calldiag on
quickc 1 1 sample
sqlallocstmt 1 1
getmem 1 1 sql_c_wchar 102
sqlprepare 1 "CALL DB2ADMIN.testsp(?)" -3
sqlbindparameter 1 1 sql_param_output sql_c_wchar sql_char 102 0 1
sqlexecute 1
Execute the CLI script:
C:\Program Files\IBM\SQLLIB_02\BIN>db2cli < cli0002
IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
> > SQLAllocEnv: rc = 0 (SQL_SUCCESS)
CLI henv = 1, Test Driver henv = 1
SQLAllocConnect: rc = 0 (SQL_SUCCESS)
CLI hdbc = 1, Test Driver hdbc = 1
SQLConnect: rc = 0 (SQL_SUCCESS)
> SQLAllocStmt: rc = 0 (SQL_SUCCESS)
CLI hstmt = 1, Test Driver hstmt = 1
> GetMem: memory buffer 1 for statement handle 1 allocated.
> SQLPrepare: rc = 0 (SQL_SUCCESS)
> SQLBindParameter: rc = 0 (SQL_SUCCESS)
> SQLExecute: rc = 0 (SQL_SUCCESS)
> >
Memory count is 60
CLI trace snippet:
[07/31/2015 18:09:31.723112] SQLExecute( hStmt=1:1 )
[07/31/2015 18:09:31.723621] ---> Time elapsed - +1.765000E-003 seconds
[07/31/2015 18:09:31.723849] ( Package="SYSSH200 ", Section=4 )
[07/31/2015 18:09:31.724418]
sqlccsend( Handle - 0000000035255968 )
sqlccsend( ulBytes - 498 )
sqlccsend( ) rc - 0, time elasped - +6.000000E-006
sqlccrecv( timeout - +0.000000E+000 )
sqlccrecv( ulBytes - 276 ) - rc - 0, time elapsed - +1.970000E-004
Elapsed Server Processing Time - +1.100000E-004
[07/31/2015 18:09:31.725784] ( Row=1, iPar=1, fCType=SQL_C_WCHAR, rgbValue="Developer Works " - x'44006500760065006C006F00700065007200200057006F0072006B00730020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000', pcbValue=100, piIndicatorPtr=100 )
[07/31/2015 18:09:31.727990]
Elapsed Server Processing Time - +1.440000E-004
[07/31/2015 18:09:31.728397] ( return=0 )
[07/31/2015 18:09:31.728798]
[07/31/2015 18:09:31.729002] SQLExecute( )
[07/31/2015 18:09:31.729199] <--- SQL_SUCCESS Time elapsed - +6.087000E-003 seconds
References:
Unicode CLI applications: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.cli.doc/doc/c0007910.html?lang=en
Hope it helps!
Please leave a comment if you have any feedback or question.
Thanks!
~Mary Kassey
UID
ibm11140970