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:

CLI0002W: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.messages.cli.doc/doc/mcli00002w.html?lang=en

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