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.

Comments

Post a Comment

Popular posts from this blog

What is the importance of User Open (USROPN) in RPGLE - IBM i

User Open (USROPN) USROPN (User Open) in RPGLE provides the flexibility for the programmer to open a file at a specific point in the program execution. In this post, let's explore what is the use of a USROPN and what is the importance of USROPN and how to use it with a simple example. What is the use of USROPN? By default, any file in the RPGLE (defined in F-Spec) opens as soon as the program execution is started and file is closed at the end of the program (by setting LR indicator ON). A simple RPGLE program looks something like this on a high level with out the use of a USROPN.  1 2 3 4 5 6 7 8 9 10 11 **Free                                  Dcl-F TestFile;                            ...
Read more

What is Deep Learning? Beyond the Basics of AI

What is Deep Learning? Beyond the Basics of AI AI’s everywhere—unlocking your phone, suggesting playlists, even driving cars. But how does it get *so* smart? Enter deep learning—a game-changer in artificial intelligence. Welcome to Decoding Complexities , where we unravel tech’s toughest puzzles. If you read “What Are Neural Networks?” you’ve brushed against this. Now, we’re going deeper. In this post, we’ll decode what deep learning is, how it powers AI’s wildest feats, and why it’s more than just buzz. From self-driving Teslas to ChatGPT, it’s the engine behind the magic. Let’s break it down—ready? Deep Learning: The Basics Deep learning (DL) is a subset of machine learning—itself a chunk of AI. Remember neural networks from my last post? DL cranks them up—think neural networks on steroids. It’s all about *deep* neural networks—piles of layers that dig into data to find patterns no human could spot. Here’s the gist: More Layers: Regula...
Read more

What is Artificial Intelligence?

Artificial Intelligence Artificial Intelligence (In short, AI) is a highly sought-after skill in the Tech Industry today. In this post, let's try to understand " What is Artificial Intelligence? ". Before we dive in, let's first understand "What is Intelligence?" As humans, we think and act. This involves understanding our environment, predicting outcomes, and behaving accordingly. Similarly, AI focuses on building machines that can understand, predict, and act/respond effectively and safely. AI isn't exclusive to any specific industry. Its applications spread across different industries, including playing chess, proving mathematical theorems, driving cars, writing poetry, composing music, generating videos, diagnosing diseases, and much more. The field is continually evolving. Initially, AI was often defined in relation to matching ' Human performance '. However, AI has surpassed human capabilities in several areas and ...
Read more