|TechTip: Do You Understand the DB2 for i Environment Settings?|
|Tips & Techniques - Database|
|Written by Michael Sansoterra|
|Friday, 04 June 2010 00:00|
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…
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
-- Call procedure MyStoredProc in DatabaseC
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:
DB2 for i SQL sessions have a "naming convention" setting that determines which group it will please. There are two possible settings:
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:
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.
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:
SET CURRENT_PATH=APP_OBJECTS, APP_UTIL
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
|Last Updated on Friday, 04 June 2010 00:00|