I have a question about using “system” naming with the iSeries driver. Usually this allows you to provided a list of schemas that may contain your tables. So instead of having one default schema you may have several schemas that will be checked for unqualified tables. It is called a library list. Setting the “library list” can either be done as an option on the driver or it can automatically be defined for a given user. It seems that when Aqua Data Studio is automatically setting the default/current schema to the user ID, and while this may be OK for ever other database, for a iSeries connection using system naming it breaks the “library list” feature.
Is there a way to stop Aqua Data Studio from setting the current schema to the user ID? That should allow the library list to function as normal.
Response
Sachin Prakash over 10 years ago
ADS is setting the default schema to USERNAME when it establishes a connection. This behavior breaks SYSTEM naming and keeps it from working as intended. IBM automatically configures the default schema based on naming scheme used. For SQL naming it will be set to USERNAME. For SYSTEM naming it will be set to *LIBL (which means library list).
To workaround this issue:
1) (See attached screenshot) Right click on your Registered Server -> Server Properties -> Script and enter the following:
set schema default
go
2) Save your server properties and open a new Query Analyzer window. See if you get the desired behavior.
3) If not, you could also try going to File->Options->General->Query Windows->[Changeable DB2 iSeries Schema] and disable (uncheck) this option.
Robert Rogerson over 10 years ago
Sorry for the delay in getting back to you…darn work getting in the way :-).
1) (See attached screenshot) Right click on your Registered Server -> Server Properties -> Script and enter the following:
set schema default
go
I changed this and it no longer initialized my schema to my username but the library list was not searched for an unqualified table name
3) If not, you could also try going to File->Options->General->Query Windows->[Changeable DB2 iSeries Schema] and disable (uncheck) this option.
I also tried this but now was unable to change the schema (in the QA window) and the schema was being set to my username. The library list was not searched for an unqualified table name
I was able to get it find an unqualified table in the library list by adding libraries=*LIBL to the server parameters so my url now is
jdbc:as400://xx.xx.xx.xx:446/rrogerson;naming=system;libraries=*LIBL. I still have the set schema default script.
I found information at the link (pic.dhe.ibm.com/…/index.jsp
Thanks for the help.
Robert