Skip to main content

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.
Labels:

Comments

Post a Comment

Popular posts from this blog

All about READ in RPGLE & Why we use it with SETLL/SETGT?

READ READ is one of the most used Opcodes in RPGLE. As the name suggests main purpose of this Opcode is to read a record from Database file. What are the different READ Opcodes? To list, Below are the five Opcodes.  READ - Read a Record READC - Read Next Changed Record READE - Read Equal Key Record READP - Read Prior Record READPE - Read Prior Equal Record We will see more about each of these later in this article. Before that, We will see a bit about SETLL/SETGT .  SETLL (Set Lower Limit) SETLL accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Corresponding Record (or Next Record if exact match isn't found).  SETGT (Set Greater Than) SETGT accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Next Record (Greater Than the Key value). Syntax: SETLL SEARCH-ARGUMENTS/KEYFIELDS FILENAME SETGT  SEARCH-ARGUMENTS/KEYFIELDS FILENAME One of the below can be passed as Search Arguments. Key Fiel
4 comments
Read more

What we need to know about CHAIN (RPGLE) & How is it different from READ?

CHAIN READ & CHAIN, These are one of the most used (& useful) Opcodes by any RPG developer. These Opcodes are used to read a record from file. So, What's the difference between CHAIN & READ?   CHAIN operation retrieves a record based on the Key specified. It's more like Retrieving Random record from a Database file based on the Key fields.  READ operation reads the record currently pointed to from a Database file. There are multiple Opcodes that start with READ and all are used to read a record but with slight difference. We will see more about different Opcodes and How they are different from each other (and CHAIN) in another article. Few differences to note.  CHAIN requires Key fields to read a record where as READ would read the record currently pointed to (SETLL or SETGT are used to point a Record).  If there are multiple records with the same Key data, CHAIN would return the same record every time. READE can be used to read all the records with the specified Ke
Post a Comment
Read more

Extract a portion of a Date/Time/Timestamp in RPGLE - IBM i

%SUBDT Extracting Year, Month, Day, Hour, Minutes, Seconds or Milli seconds of a given Date/Time/Timestamp is required most of the times.  This can be extracted easily by using %SUBDT. BIF name looks more similar to %SUBST which is used to extract a portion of string by passing from and two positions of the original string. Instead, We would need to pass a value (i.e., Date, Time or Timestamp ) and Unit (i.e., *YEARS, *MONTHS, *DAYS, *HOURS, *MINUTES, *SECONDS or *MSECONDS) to %SUBDT.  Valid unit should be passed for the type of the value passed. Below are the valid values for each type. Date - *DAYS, *MONTHS, *YEARS Time - *HOURS, *MINUTES, *SECONDS Timestamp - *DAYS, *MONTHS, *YEARS, *HOURS, *MINUTES, *SECONDS, *MSECONDS Syntax: %SUBDT(value : unit { : digits { : decpos} }) Value and Unit are the mandatory arguments.  Digits and Decimal positions are optional and can only be used with *SECONDS for Timestamp. We can either pass the full form for the unit or use the short form. Below i
2 comments
Read more