iACS Run SQL Scripts - library list?

[Vectorspace]

Is there a way to set a proper library list for Run SQL Scrips in i Access Client Solutions?

I found the JDBC Configuration manager, in which you can edit configurations, and within each configuration to can specify a schema list. The tooltip says it's a comma or space separated list of library names used for resolving unqualified names. So I input a few libraries into it and reconnect using that configuration. But when I run SQL with unqualified table names it only seems to look in the first library, not all of them.

E.g say that FILE1 is in LIB2. If I specify LIB1,LIB2,LIB3 and then do "select * from FILE1", I get an error saying FILE1 in LIB1 not found

On the Special Registers tab the CURRENT PATH entry lists all my libraries, but the next line, CURRENT SCHEMA, lists only the first library.

Any advice?

[Brian Rusch]

I believe that you need to configure the JDBC driver to use the *SYS naming convention instead of *SQL naming. To do that, select Edit JDBC Configuration from the Connection drop down menu, select the Format tab, change the naming convention, and click Save.

[Vectorspace]

Unfortunately that did not work.

Also - correct me if I'm wrong, but specifying *SYS for naming convention means I should be able to use LIB/FILE instead of LIB.FILE? Because it didn't, it still wants me to use LIB.FILE.

Maybe those features are not working properly in iACS yet, since it is still relatively new?

[tomliotta]

iACS has been around for a while, certainly long enough for those options to work. They work for me.

Did you change the settings before connecting? Or before disconnecting and connecting again?

[Vectorspace]

Initially, I tried after connecting. But when that did not work I immediately tried disconnecting and reconnecting. I tried that several times, and I have even tried entirely closing and reopening iACS.

[Brian Rusch]

Yes, you should be able to use LIB/FILE with *SYS naming. When you click on the Special Registers Tab, do you still see the first library for CURRENT SCHEMA? When I made the change to *SYS naming, CURRENT PATH and CURRENT SCHEMA both showed *LIBL and it worked properly for me. If you go back to edit the JDBC configuration, does naming show as *SYS or *SQL? Do you have more than one JDBC driver configured? What version of ACS are you using?

[B.Hauser]

How old is your ACS? First try to get the current version.
Sometimes ago there was a bug, that the library list was ignored even with *SYS (System) Naming convention set.
When using System Naming Convention, never set the CURRENT SCHEMA to any schema.
The current schema is not the same as the Current Library set with the CHGCURLIB CL command.
If a current schema is set, the library list is ignored even when using *SYS Naming Conventions.

SQL Naming Contentions will only search through a single schema/library, the CURRENT SCHEMA.
If the CURRENT SCHEMA is not explicitly set, a schema with the same name as the current user profile is searched.

Birgitta

[Vectorspace]

I have v1.1.6.0, which I can now see on https://www-03.ibm.com/systems/power...solutions.html is several versions behind.

I am on a corporate PC/network, so I don't have authority to download and install a newer version. Our PCs actually have the old i Access for Windows installed, I only found out we had iACS as well by accident - I think it was packaged with our March update to RDi 9.5.1.1 (iACS's exe is located in C:\Program Files\IBM\SDP\rdi\acs)

I guess I will have to see if I can request the install of a newer version of iACS somehow.

[chrisr]

I have v1.1.6.0, which I can now see on https://www-03.ibm.com/systems/power...solutions.html is several versions behind.

I am on a corporate PC/network, so I don't have authority to download and install a newer version. Our PCs actually have the old i Access for Windows installed, I only found out we had iACS as well by accident - I think it was packaged with our March update to RDi 9.5.1.1 (iACS's exe is located in C:\Program Files\IBM\SDP\rdi\acs)

I guess I will have to see if I can request the install of a newer version of iACS somehow.

It may be helpful to know that the iACS software does not need to be installed. If you are able to download it then simply extract and run. I put it on a memory stick with all my preferences set and then take this to wherever I am working.

[Vectorspace]

Finally got my hands on v1.1.7.1, and *SYS naming now works. So does Schema List for a library list for resolving objects.

[chrisr]

Good news, thanks for the update.