Technical Blog Post
Abstract
TECH TIP: How to avoid SQL0873N error when accessing an ASCII Stored Procedure or UDF in DB2 LUW/SAP environments from application using IBM Data Server Driver for JDBC & SQLJ (JCC) when ALT_COLLATE and DB2_IMPLICIT_UNICODE set on DB2 server?
Body
Application using IBM Data Server Driver for JDBC & SQLJ (db2jcc.jar) is an UNICODE client.
When accessing an ASCII Stored Procedure or UDF from a UNICODE client in a DB2 LUW/SAP environments where ALT_COLLATE and DB2_IMPLICIT_UNICODE set on DB2 server, SQL0873N error is expected and is working as designed.
To Workaround the issue, pre-empt the query with a select from SYSIBM.SYSDUMMY1 (a view in DB
code page), so that DB2 commits to the codepage of the database, instead of Unicode and prevents the error.
Here are the steps to recreate the issue:
1) Created db with codeset 1252:
db2 "create database test using codeset IBM-1252 Territory US"
2) Updated ALT_COLLATE
db2 "update db cfg for test using ALT_COLLATE IDENTITY_16BIT "
3) db2set DB2_IMPLICIT_UNICODE=YES
4) db2stop
5) db2start.
6) Sample Java program the reproduces the issue, has the following Statment:
PreparedStatement stmt = con.prepareStatement("select * from TABLE(SNAPSHOT_DATABASE(?,0)) AS T");
7) During Runtime, it throws error SQl0873
C:\>java jccselect
Exception: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-873,SQLSTATE=53090, SQLERRMC=Different encoding schemes in one statement,DRIVER=3.64.106
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-873,
SQLSTATE=53090, SQLERRMC=Different encoding schemes in one statement, DRIVER=3.64.106
8) Once a select from SYSIBM.SYSDUMMY1 is added to the query, it completes successfully:
PreparedStatement stmt = con.prepareStatement("select T.* from SYSIBM.SYSDUMMY1, TABLE(SNAPSHOT_DATABASE(?,0)) AS T");
C:\>java jccselect
Resultset= 2013-03-26 08:14:37.386235
UID
ibm11141672