Pages

Wednesday, November 18, 2020

What is SQL Path? And, How to change SQL Path on IBM i?

SQL PATH:

SQL Path is an ordered list of libraries. Database manager uses this path to find any unqualified objects (like functions, stored procedures, variables...) except for operations like ALTER, CREATE, DROP, COMMENT, LABEL, GRAND or REVOKE on main object. 

Objects will be scanned from left to right on the library list (SQL Path) for its existence and first occurrence would be considered (for functions number of parameters should match as well along with name).

SQL Path for a job would be assigned based on the Naming convention used. 
  • If the Naming Convention is '*SYS', SQL Path would be set to '*LIBL'. 
  • If the Naming Convention is '*SYS', SQL Path would be set to 'SYSTEM PATH' along with User under which job is running on. System Path would be "QSYS","QSYS2","SYSPROC","SYSIBMADM"
This is applicable for both STRSQL (Interactive SQL) and Run SQL Session from IBM i ACS. 

For STRSQL, Naming Convention can be setup using 'NAMING' parameter on STRSQL command. 

For Run SQL Scripts, Naming Convention can be setup under 'Format' tab of JDBC Configuration.

Also, SQL Path used depends on the SQL statement. 
  • For Static SQL statements, Path used is the value of the SQLPATH parameter on CRTSQL*** command. SQL Path can also be set using 'SET PATH' inside the program. Objects created by CRTSQL* command would store the SQL Path on the Programs, Modules, Procedures, Routines... and is composed based on the 'System Schema Name' at the time of creation. if this is changed later, These objects may need to be recompiled.
  • For Dynamic SQL statements, Path used is from the CURRENT PATH special register. Current Path can be changed by using SET PATH.
'SET PATH' can be run inside the program or in Interactive SQL session. 

SQL Path can also be set inside a program using 'SET OPTION SQLPATH'. This can be used along with other parameters specified on SET OPTION inside a program. 

Library names can be specified directly by separating with comma (,) on PATH to change the SQL PATH. 

E.g.: 

SET PATH = LIBRARY1, LIBRARY2 

This would store the SQL Path as below in CURRENT PATH special register. 

"LIBRARY1","LIBRARY2"

Apart from specifying the library names directly, Below are the other ways to set up Path. 

SYSTEM PATH

Specified the schema names for the system path. This value is same as specifying schema names "QSYS","QSYS2","SYSPROC","SYSIBMADM".

SET PATH = SYSTEM PATH

SESSION_USER or USER

Specifies the value from SESSION_USER special register (User currently logged on). 

SET PATH = USER
SET PATH = SESSION_USER

CURRENT USER

Specifies the value from CURRENT USER special register.

SET PATH = CURRENT USER

SYSTEM USER

Specifies the value from SYSTEM USER special register. 

SET PATH = SYSTEM USER

Variable/Constant

Variable/Constant inside a program with the Library name(s) separated by commas can be used. 

Also, Multiple values can be setup at once. 

E.g.: If the SQL Path is to be setup with System Path followed by Session User, Both SYSTEM PATH and USER can be set. 

SET PATH = SYSTEM PATH, USER

There are few important points to note while setting up SQL Path. 
  • A Library name should not appear more than once. 
  • Number of Libraries allowed is dependent on the length of CURRENT PATH special register (3483 characters) and Maximum number of Libraries allowed are 268.

No comments:

Post a Comment

Popular Posts