Sun, Jul
2 New Articles

TechTip: Do You Understand the DB2 for i Environment Settings?

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Learn how to configure a DB2 for i SQL session, and discover the essential differences between DB2 for i and other relational databases.


There are many non-IBM i developers out there who have opportunities to develop applications that interact with the i and its DB2 data store (aka AS/400, iSeries, i5, System i). These developers are many and varied and can include .NET and Java developers, Visual Basic and VBA developers, and Web and SQL developers of many stripes. Generally, these folks already have some level of experience with SQL. Basic SQL is generally portable among databases, but because of the unique nature of DB2 for i, developers need to understand some introductory concepts before tackling a DB2 for i project. This TechTip introduces a few important concepts essential for the i SQL environment.


While the article was designed for helping the non-i developer understand this foreign environment in a client/server setting, it is equally important that i developers understand these concepts thoroughly in order to…

  • understand the roadblocks non-i developers will encounter when working on an i project
  • build their own understanding of how the SQL environment works
  • be able to migrate code between platforms or create portable code

If you're an i developer and have wondered why there are so many different settings that can mess up your SQL session, read on!


This overview covers three important concepts essential to working with DB2 for i successfully: the library list, naming conventions, and the way DB2 for i resolves unqualified object names. After I've explained these topics, I'll provide examples to show how these concepts may be established in a number of environments.

Understanding the Library List

To begin, a non-i developer needs to understand the library list concept, which isn't present in traditional relational databases. Most database developers are accustomed to storing all related data (tables and views) and objects (triggers, procedures, and functions) in a single "container" variously named a catalog, database, or schema. Generally, an application connects to one container database and thereby has access to all the objects contained therein (subject to security authorizations).


An application may connect to a second database container either by opening a second connection or by specifying within an SQL statement a special container qualifier that will allow the SQL engine to access objects outside of its normal container boundary. For instance, if an application connects to DatabaseA hosted by Microsoft's SQL Server, it can still access objects in other databases by using the following syntax:


-- Access Table1 data in DatabaseB
Select * From DatabaseB.dbo.Table1

-- Call procedure MyStoredProc in DatabaseC

Exec DatabaseC.dbo.MyStoredProc


The drawback to this method is that a database container name must be hard-coded into the SQL statements or soft-coded using dynamic SQL.


However, i developers are accustomed to a different way of organizing and accessing objects within these containers. On the i, various database objects are stored in a library (aka "schema" in DB2 terminology) similar to how they're stored in a container on other systems. However, i application jobs have a special definable property called the library list. A library list specifies a set of libraries (schemas) that an application will scan for required unqualified objects while a job is running.


For instance, if a COBOL or RPG program requests access to a database table, under the covers i5/OS will search sequentially through the libraries in the library list from first to last, looking for the first library that contains the requested table. If you're familiar with the old DOS path concept (where DOS searches for a program by iterating through a list of directories specified in the PATH environment variable), you'll understand the library list idea; it's very similar.


Because traditional i applications are geared to use multiple libraries in a library list, it's very important for non-i and i developers to discuss the library list requirements at the outset of a new project. If non-i developers will be calling an RPG or COBOL program through the SQL interface, they must know what tables, utility programs, and other resources the application will depend on and what libraries contain the objects the program will need to do its job. Once the library list is identified, the non-i developer can simply specify the library list in the connection string or, if a dynamic library list is needed, can override the library list at run time.


In summary, whereas traditional database developers are used to having a majority, if not all, of the data and routine objects (stored procedures, triggers, etc.) in one database container, in most cases, the i developer is accustomed to having access to these objects spread out in multiple library (aka schema) containers. The library list is a key setting that allows developers to access objects in all of these containers without resorting to hard-coded schema qualifiers or dynamic SQL.


I'll explain how the library list is used in DB2 for i SQL later in this article.

Choosing a Naming Convention

DB2 for i is a "shape shifter" of sorts because it has to please two groups of people:

  • i/OS developers, who know and love the library list concept and are comfortable with how objects are qualified in i/OS: a library name and an object name separated by a forward slash (/), e.g., Library/Object.
  • Traditional database developers, who are used to accessing one database container at a time. These people are familiar with the generic database object qualification syntax consisting of a database name or owner name, a period separator, and an object name, e.g., OWNER.OBJECT or DATABASE.OWNER.OBJECT.

DB2 for i SQL sessions have a "naming convention" setting that determines which group it will please. There are two possible settings:

  • System naming convention—This option is intended for those who are familiar with the library list concept. When DB2 is instructed to get data from an unqualified table name or to call an unqualified procedure name, it will search through the libraries specified in the library list to find the requested object.
  • SQL naming convention—This choice will make DB2 for i behave like a traditional database server. Unqualified database names are expected to be in the default schema that is specified within the connection string. The library list will not be used to search for unqualified table and view names.

When writing SQL statements, note that the separator character for qualified object names differs, depending on which naming convention is in specified. The system naming convention uses the forward slash (/) as a separator, just as i/OS does; the SQL naming convention uses a period (.), like many other relational databases use. So, if you need to access table MYTABLE in a library (schema) called SPECIALLIB, you would write your SQL statement in one of two ways, depending on your naming convention:


Select * From SpecialLib/MyTable  -- System Naming convention

Select * From SpecialLib.MyTable  -- SQL Naming convention


These statements can be generalized as follows:


Select * From library/dataobject

Select * From schema.dataobject


Remember, schema is a DB2 term roughly equivalent to the i/OS library. The same separator character holds true for making a stored procedure call:


Call library/program    -- System Naming convention

Call schema.procedure   -- SQL Naming convention


For user-defined scalar functions, qualified function names are not allowed in the system naming convention (by default, the library list will be searched to locate the function).


Select MyFunction() From MyTable

Select MySchema.MyFunction() From MyTable


Finally, the rules for user-defined table functions are the same as for normal data object access:


Select * From Table(MyLib/MyTableFunction()) T

Select * From Table(MySchema.MyTableFunction()) T


The forward slash (/) separator can be a stumbling block to those unfamiliar with i/OS, so make sure this concept is covered thoroughly if you're assisted by a non-i developer.


Interestingly, in the 5250 green-screen world where legacy AS/400 developers often live, the SQL tools will default to use the system naming convention. However, client/server interfaces (ODBC, JDBC, PHP, .NET, OLE DB, etc.) generally default to the SQL naming convention. So, if you get on a green-screen session, expect your SQL statements to follow the system naming convention rules unless the defaults have been changed. If you change to the PC world to use a tool like the Run SQL scripts utility in System i Navigator (which uses JDBC), it will default to use the SQL naming convention and you will have to code your statements a little differently or change the naming convention setting.

So Which Naming Convention Do I Use in My Application?

So how does one decide which naming convention to use? I have a general rule:

  1. If the application's required objects (including stored procedures, user-defined functions, tables, views, aliases, etc.) are stored in a single schema (library), use the SQL naming convention and specify the application's schema as the "current schema" (more on this later). Also, if the potential for portability is a concern, use the SQL naming convention.
  2. If the application objects will use multiple libraries and access program objects heavily dependent on the library list, use the system naming convention and set the library list.

If you're somewhere in between these two extremes, the answer becomes somewhat gray. If all of your data is stored in a single schema but you require access to program objects in, say, a utility schema that is unlikely to change much, then I would lean toward the SQL naming convention and just qualify the objects from the utility library. Really, either way is fine as long as the developers on the project understand the environment and the separator they'll be required to use.

Qualified Database Objects, Naming Conventions, and Search Paths

Now that I have you confused, I'll make the waters a little murkier. A big task for the developer is deciding whether or not to include a schema name qualifier within the SQL code. In general, I prefer not to include the name for a variety of reasons, including the potential that a schema name could change or an object within the schema could move to a new schema. Non-i developers may be surprised by how often database objects can be moved between schemas within the i world. Placing schema names within the SQL code can break things in a hurry!


So if database objects aren't qualified, how does DB2 know where to find them? The answer depends on the naming convention setting and the type of database object being sought.


Understand that DB2 objects can be divided into two broad categories: data objects and routine (program) objects.

  • Data Objects—Tables, views, aliases, and sequences
  • Routine Objects—Stored procedures, user-defined functions, triggers, and distinct types

When an object is unqualified, DB2 will attempt to locate it, as outlined below.

Finding Unqualified Data Objects

DB2 will attempt to find unqualified data objects by using the value stored in the CURRENT_SCHEMA special register. If the SQL naming convention is used, CURRENT_SCHEMA will default to the i/OS user  profile name that is used to establish the database connection. Therefore, if user JOHNSMITH is signed in using the SQL naming convention, by default SQL will look for table MYTABLE in schema JOHNSMITH in this SQL query: SELECT * FROM MyTable. Usually, this default SQL naming convention is undesirable in situations where individual users sign in, because there will not be a schema for each application user! Incidentally, when DB2 for i can't locate an object, an SQL0204 error will result.


When the system naming convention is used, the CURRENT_SCHEMA register will contain the special value *LIBL for library list. This means that, for the SQL query  SELECT *  FROM MyTable, DB2 will search the current job's library list to find MyTable.


This default behavior can be overridden by setting this special register to a different schema name or to one of the IBM-supplied special values: SESSION_USER, USER, or SYSTEM_USER. Here's an example of changing the CURRENT_SCHEMA special register:




That can be abbreviated:




Once CURRENT_SCHEMA is set, DB2 assumes that all unqualified data object names are in the specified schema. An exception to this rule involves embedded SQL programs that have the DFTRDBCOL (default relational database collection) value specified. In this case, the embedded SQL program's DFTRDBCOL value will take precedence over the CURRENT_SCHEMA register for the duration of the routine call.


In general, setting CURRENT_SCHEMA works very well for applications that have all of the required data objects contained in a single schema. For applications that have data objects in more than one library, my preference is to use the system naming convention and set the CURRENT_SCHEMA register to *LIBL and then set the job library list accordingly. For client/server users, most data providers (JDBC, ODBC, OLE DB, etc.) have options to set the naming convention, CURRENT_SCHEMA, and initial library list (i.e., schema list) within the connection string (more on this in a bit).


When using the SQL naming convention, the CURRENT_SCHEMA register cannot be set to *LIBL. However, an application using the SQL naming convention may still require the library list to be set correctly in addition to having the CURRENT_SCHEMA register set correctly. For instance, if the application will invoke external routines (such as a trigger, function, or procedure written in a high-level language such as C, COBOL, or RPG), in many cases the library list will need to be set correctly for these programs to access the objects they need. Very often, these programs rely on the library list to locate resources such as files (tables), data areas, and other programs.


The library list will also need to be set correctly when using the SQL naming convention when calls are made to SQL routines that were created using the system naming convention. For example, if stored procedure MYPROC was created using the system naming convention, when invoked, it will use the library list to locate its unqualified objects. This is true even if the client calling procedure MYPROC has established a connection using the SQL naming convention. I generally create my SQL routines (stored procedures, functions, and triggers) using the system naming convention, which carries the requirement of having the job's library list set up correctly in order for them to function correctly.

Finding Unqualified Routine Objects

DB2 will attempt to find unqualified routine objects using the values specified in the CURRENT_PATH special register. CURRENT_PATH differs from CURRENT_SCHEMA in that it stores multiple schemas. If the SQL naming convention is used, by default CURRENT_PATH will be preset to a few select system schemas (QSYS, QSYS2, SYSPROCS, etc.) followed by a schema matching the user name.


For instance, if user JOHNSMITH is signed in using an application that utilizes the SQL naming convention, the SQL statement CALL MYPROC will by default look for stored procedure MYPROC in schemas QSYS, QSYS2, SYSPROCS, and JOHNSMITH. If MYPROC is not found in these schemas, the CALL will fail.


When an application uses the system naming convention, the CURRENT_PATH register will initially be set to the *LIBL special value. This means that DB2 for i will search through all the schemas specified in the library list when locating an unqualified routine object.


The CURRENT_PATH register is set using a comma-delimited list of schemas:



Or abbreviated as:




The special value *LIBL is allowed to be specified as a standalone option for either naming convention.

Applied Learning: Sample DB2 for i Connection Strings

Now that you know how the DB2 for i naming convention, current schema, and library list concepts intertwine, I've collected a few sample connection strings to demonstrate how these parameters can be configured.


We'll look at the data providers that come with the IBM System i Access product (aka iSeries Access, Client Access) as well as the IBM Toolbox for Java JDBC driver. All of these providers default to use the SQL naming convention.


ODBC Driver Connection String Example (Using ADO)


Consider this ODBC connection string:


Provider=MSDASQL;Data Source=AS400; DBQ=QIWS,*USRLIBL;NAM=0

Here is a breakdown of the connection string properties. The Data Source keyword specifies the ODBC DSN name the connection should use.


The NAM keyword establishes the naming convention for the connection. A value of 0 specifies the SQL naming convention, while a value of 1 specifies the system naming convention.


The DBQ keyword specifies a comma-delimited library list. The first library specified in this list will be used to set the CURRENT_SCHEMA register. If you do not wish to set an explicit CURRENT_SCHEMA in your library list, specify a leading comma in this list. If DBQ is not specified and the naming convention is set to system (NAM=1), then CURRENT_SCHEMA will default to *LIBL. If the naming convention is set to SQL, and DBQ is not set, then CURRENT_SCHEMA will default to the user name.


For more information on the ODBC connection string keywords, click here.


Note for non-i developers: The library list can be established either by the application or by a "job description" that is associated with a specific user. The *USRLIBL special value will tell the ODBC driver to simply use the user's predefined library list. So, in some cases, the i/OS user can be depended on to establish the desired library list. But always check with your i admin to determine how the library list should be set.


IBMDASQL/IBMDA400 OLE DB Providers Connection String Examples


Provider=IBMDASQL; Data Source=as400.acme.com;Default Collection=QIWS;Library List=QGPL,MYLIB;Naming Convention=1

Provider=IBMDA400; Data Source=as400.acme.com;Default Collection=QIWS;Library List=QGPL,MYLIB;Naming Convention=0

The specific keywords covered here for these two providers are identical. Default Collection specifies the CURRENT_SCHEMA register, Library List sets the library list using a comma-delimited list of library names, and Naming Convention accepts a value of 0 (SQL) or 1 (System).


IBM DB2 UDB for iSeries .NET 2.0 Managed Provider (System i Access 6.1/V6R1 or Higher) Connection String Example







In this string, DataSource specifies the host name or IP address of your target AS/400 (aka IBM i!), and the rest of the keywords are self-explanatory. In contrast to the ODBC and OLE DB providers, the Naming keyword accepts the text values "System" or "SQL" instead of 1 or 0.


In the first connection string, library QIWS is considered the default collection (collection is an older DB2 term for schema) and will also be placed as the first library in the library list. Note that the library name specified in the default collection cannot be duplicated in the user library list. The DefaultCollection keyword is used to set the CURRENT_SCHEMA register for this connection.


The second connection string specifies no default schema.


IBM Toolbox for Java/IBM Native Java JDBC Driver








Finally, we'll end with some JDBC examples. In the JDBC connection string, the current schema will be set when an additional forward slash (/) and schema name is specified after the host name, as shown in the last example. Easy enough; the naming keyword controls the naming convention, and the libraries keyword specifies a comma-delimited library list.


For a list of available connection string properties for the IBM Toolbox JDBC driver, click here.


What's in a Name?

Understanding the purpose of the naming convention setting is key to removing the complexities of writing code for the i (aka the beloved AS/400!). Knowing its relationship to the library list—and the CURRENT_SCHEMA and PATH special registers—will resolve most of the difficulties new i or non-i developers have when creating applications that work with DB2 for i. 


Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..



Support MC Press Online


Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: