Re: JDBC connection string not finding sort table in iASP

For anyone else running into this issue:

I've been working off line with John Eberhard of IBM.

This issue stems from the com.ibm.as400.access.AS400JDBCConnection class in
jt400.jar

In this class, the "sort" code is executed before the "database name" code.
In my situation, I have a sort table that resides in the iASP.
Because this class tried to set the sort table before it set the database,
the sort table came back with the CPF9810 - Library MYLIB not found.

A change was made to AS400JDBCConnection to move the sort code after the
database name code.
Now the database name is set -then- the sort table is looked for (and
found) in the iASP.

The changed code is reflected in the open source JTOpen project on
sourceforge.net:
http://sourceforge.net/projects/jt400/

I was provided a test jt400.jar. I ran my own code using this .jar file
and confirmed that it now finds the sort table.

I'm told that the next build of JTOpen that will include the new jt400.jar
file will possibly be in December 2014.

Thanks to John for his quick resolution to this problem.


-doug




On Fri, Oct 10, 2014 at 11:12 AM, Doug Davie <dougdavie@xxxxxxxxx> wrote:

JDBC connection string not finding sort table in iASP



I’m creating a connection with the URL string:

jdbc:as400://MYSYSTEM;translate binary=true;date
format=iso;prompt=false;database name=IASP01;sort=table;sort
table=MYLIB/MYTABLE



I use “database name=IASP01” to indicate that my database exists in the
iASP.

I use “sort=table” along with the “sort table=MYLIB/MYTABLE” to indicate
what sort table to use.

MYTABLE *TBL does indeed exist in MYLIB

MYLIB *LIB does indeed exist in IASP01

IASP01 is varied on and available.



When i execute
java.sql.DriverManager.getConnection(jdbcURL,jdbcUser,jdbcPassword)

I can see a new QZDASOINIT job started with my user as the current user.

The job attributes indicate the Auxiliary storage pool group is set to
IASP01.



--HOWEVER--

The job log indicates CPF9810 – Library MYLIB not found



The connection then throws an exception:

SQL0332 Character conversion between CCSID 0 and CCSID 37 not valid.



This message comes from:

Program: QZDASRV

Library: QSYS

Module: QZDACMDP

Procedure: RTVSSTBL_MAC

Statement: 13545



This is a v7.1 box.





Why would the library not be found in this case?



If I leave off the sort properties I can get a connection and access my
tables in MYLIB.