IBM Support

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?

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   

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11141672