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:

SET CURRENT_SCHEMA=MYSCHEMA

That can be abbreviated:

SET SCHEMA=MYSCHEMA

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:

SET CURRENT_PATH=APP_OBJECTS, APP_UTIL

Or abbreviated as:

SET PATH=APP_OBJECTS, APP_UTIL

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

DataSource=as400.acme.com;UserID=MyUser;Password=MyPassword;Naming=System;LibraryList=QIWS,

*USRLIBL;DefaultCollection=QIWS

DataSource=My_System_i;UserID=MyUser;Password=MyPwd;Naming=System;LibraryList=*USRLIBL

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

jdbc:as400://My_iSeries;naming=system;libraries=*LIBL

jdbc:as400://My_iSeries;naming=system;libraries=*LIBL,MYLIB1

jdbc:as400://My_iSeries/MyLib

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.