Hit enter to search

Using *SYS or *SQL in the JDBC Configuration

02/07/2021
Author Avatar
Rudi van Helvoirt

As always, everything is in the details when using Run SQL scripts. So how do you get started?

1) When using Run SQL Scripts (RSS) from IBM i Access Client Solutions (ACS) the current default for the “Naming convention” is SQL(*SQL). This setting can be found in the RSS menu “Connection” => “Connected – <System>(<Database>) => Edit JDBC Configuration – Default => Format Tab.

2) Next to the “SQL(*SQL)”, another option is available, the “System(*SYS)” value. The impact of this change is not limited to using the “/” instead of a “.” as you may think. There is more to it. Please allow me to demonstrate this by running some SQL statements using both of these values for the Naming convention.
 
3) Let us start with “SQL(*SQL), after all that is the default. Before running a select statement, go to the RSS menu “Connection” => “Connected – <System>(<Database>) => Edit JDBC Configuration – Default => System Tab. Enter the values as shown below and press the “Save” button:

SQL pic 1

4) Please be aware that instead of using a comma between values, a space is also an option.

5) Now run the following SQL statement: “select current path as path from sysibm.sysdummy1”. The result will show that “QIWS” and “SYSTOOLS” are now in your path.

6) When running the SQL statement “select * from SERVICES_INFO” you will get an error, with the text: “SERVICES_INFO in QIWS type *FILE not found”. The statement “select current schema as schema from sysibm.sysdummy1” will tell you that “QIWS” is the current schema. When you run the statement before changing anything of the JDBC configuration, the current schema will correspond with your user profile you used when connecting in RSS.

7) The blue question mark button on the screen will bring you to a document where this all is explained in more detail.

8) Now change the “Naming Convention” as shown below and press the “Save” button afterwards:

SQL Pic 2

9) Repeat the statement: “select * from SERVICES_INFO”. This time you will get a result. The difference is created by the fact that as a side effect of using the *SYS naming convention, now the library list is searched for a file with this name. When running the statement “select current schema as schema from sysibm.sysdummy1” you will see why.

10) Below some SQL statements used above and some extra SQL statements to review the impact of your changes.

SQL PIC 3

11) Also in text to save you some typing:

May the path be with you

select current path as path from sysibm.sysdummy1;
select current schema as schema from sysibm.sysdummy1;

-- Changing the path
set current path="QSYS","QSYS2","SYSPROC","SYSIBMADM","QGPL","QTEMP","usrsys","QIWS";

-- Please notice that this does not impact the current schema
select current schema as schema from sysibm.sysdummy1;

-- Changing the schema
set current schema QIWS;
select current schema as schema from sysibm.sysdummy1;

-- Statement working with *SYS naming convention if QSYS2 is in your path
select * from services_info;

-- Statement with addition of Schema needed for *SQL naming convention
select * from QSYS2.services_info;

-- something extra to get you going with DB2 for i Services
select Example from qsys2.services_info;

  • Please be aware that you can cut and paste an example directly in RSS and run it
  • Below, an example by cut and paste the first field from the result of the previous command
  • Description: Will an IPL impact PTF state?

SELECT PTF_IDENTIFIER, PTF_IPL_ACTION, A.*
FROM QSYS2.PTF_INFO A
WHERE PTF_IPL_ACTION <> 'NONE';

Have fun learning the impact of *SQL or *SYS as a naming convention

AutoDBE - AI powered virtual database engineer for IBM i

Current job openings

We are constantly looking for new colleagues!

If you share our values and you're looking for a challenging job in Belgium's Best Workplace, visit our website.

Apply now

Get our top stories in your inbox every month

Follow us

  

Share this article