In the life of system administrator SQL now a necessity. It can no longer be seen as something for developers only. The new Navigator for i is the living proof for that.

When working with IBM i Access Client Solutions (ACS) Run SQL Scripts (RSS) you have the option to JDBC Configuration Default settings.

The default setting for the “Naming convention” is SQL (*SQL), but among developer it is good practice to change this setting to System (*SYS).

From what I have heard and learned, one of main reasons to do this is being able to use the library list in RSS. Having a developer background myself, I know that you want write/use the same code for test and production. It is there where the power of a library list comes into play.

This article is all about the path and the library list and how they work together. Let me first start with some basics.

The default “Naming convention” set to *SQL.

With the setting *SQL for the “Naming convention”, the path can be reviewed with the SQL statement shown below:

select current path as path from sysibm.sysdummy1

Below the result, when executing that statement:

In order to understand where these names come from, we need to have a look at another tab of the “JDBC Configuration – Default”, the System tab.

Here we can see that based on the value for “Default SQL Schema” the library list is taken from the server job:

Which server job? you may ask yourself. The answer is that in this case the path is set based on the System Path, which consists of “QSYS”, “QSYS2”, “SYSPROC” and “SYSIBMADM”.

The last one in the row show is “RUDI”, which equals the name of the user profile in this RSS session. In order to check this we can have a look at the link below:

SET PATH

There we can see the following

In order to set the path as is shown in the RSS screen we need to run the following SQL statement:

set current path=system path, user;

When changing the “JDBC Configuration – defaults” as shown below we can change the path as we wish:

When checking the current path again, we will see the following:

“QGPL” and “QTEMP” have now made their entrance into the current path. The SQL statement shown below will give you the same result:

set current path=system path, QGPL, QTEMP, QIWS, QRPLOBJ

Adding an extra library or schema, can be done on the spot, with the statement shown below:

set current path=current path, <Library/schema_name>

The default “Naming convention” set to *SYS.

When using the system for the “Naming convention”, running the SQL statement to have a look at the path value will tell you that it is set to the value “*LIBL”. It there where we need to bring a DB2 for i Service into action:

In order to manipulate the library list, we can use two options:

1. Use CL commands in RSS
   CL:ADDLIBLE LIB(<Library_Name>) POSITION(*LAST);
   
   
2. Use a DB2 for i Service in RSS
   CALL QSYS2.QCMDEXC(‘ADDLIBLE <Library_Name>’);

The outcome is the same so it is up to you to decide what to use.

When you are a developer, it is my guess that you have more need to manipulate the path whether you use the *SYS or *SQL for the “Naming convention”.   Again, there are two option to get this job done:

1. Instead of using the Default JDBC configuration, create your own.
   By doing the following: RSS => Edit menu => JDBC Configurations…

Use the “Copy” button and Enter a Name and Description as shown below:

Once that is done you can manipulate the path or library list as you desire:

The next time you connect to a server, you can directly make the right choice:

2. The alternative is to use DB2 for i Services as shown in the example given by IBM:

When you are a developer you might want to take this one step further and create a procedure, that based on a parameter your path is set according to your wishes.

If you are using the value “*SYS” just for keeping control for the library list, know that using the path is the SQL way to do things in SQL. Those not familiar with IBM i starting as an IBM i developer will raise their eyebrows when you start talking about a library list. Using the path in the way it was intended for SQL will help young people to adopt IBM i more easily.