We are trying to use an OLE DB provider for DB2 UDB to connect to DB2/400. We're having problems sorting tables by fields containing Polish characters. This problem occurs both in the Command Center and in the Command Line Processor.
We created the following tables: CCSID=870 (table QLA20366S, library QSYS), LANGID=PLK. System values: QCCSID=870, QCHRID: Character ID=959, Code Page = 870; QCNTRYID=PL. In user profile we set SORT SEQUENCE as *SYSVAL or LANGIDUNQ.
Unfortunately, when we try to sort, we treceive a list with fields, which begin with Polish characters. When we sort from the SQL session (STRSQL) or OpsNav everything's OK. Someone told me that I have to change from iSeries NetServer/400 to ASCI. I tried to change the iSeries NetSerwer configuration and wasn't sure where, so I changed OpsNav in the iSeries NetSerwer properties. I set code page parameter to zero value. Is this enough? After I checked, we have temporary applied PTF SF64770.
The middleware (DB2 Connect, etc) that you are using as part of the DB2 Command Center does not support the specification of sort sequences for dynamic SQL interfaces like OLE DB against a DB2 UDB for iSeries server.
The MSDASQL OLE DB provider essentially allows the OLE DB interface to be used, but the database requests are passed to an ODBC driver. Most iSeries customers use the iSeries Access for Windows ODBC driver (formerly Client Access ODBC driver) which is included with every iSeries Server and does support Sort Sequences (like your Operations Navigator tests proved). Sort sequences are supported via this interface because this ODBC driver connects with the server in a different way than the DB2 UDB middleware you were testing with.
You can specify the Polish sort sequence on the ODBC Data Source or on the connection string ("SORTTYPE=3;SORTWEIGHT=0;LANGUAGEID=PLK"). Search the iSeries Information Center (www.iseries.ibm.com) on ODBC programming.
MORE INFORMATION ON THIS TOPIC
Search400.com's targeted search engine: Get relevant information on DB2/400.
The Best Web Links: tips, tutorials and more.
Check out this online event, Getting the Most out of SQL & DB2 UDB for the iSeries.
Dig Deeper on DB2 UDB (universal databases)
Related Q&A from Kent Milligan
Create a host variable of the where in statement on the fly with dynamic SQL. Continue Reading
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ... Continue Reading
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading