Leverage Your Distributed DB2 Skills to Get Started on DB2 UDB for iSeries (AS/400)

This article shows DB2 UNIX, Linux, and Windows specialists how to leverage their DB2 knowledge in these platforms to gain skills on DB2 for iSeries.

Raul Chong (rfchong@ca.ibm.com), DB2 Universal Database Consulting Services, IBM Toronto Laboratory

Raul F.Chong is a database consultant at the IBM Toronto Laboratory and works primarily with IBM Business Partners. Raul has worked for five years at IBM, three of them in DB2 Technical Support, and two of them as a consultant specializing in database application development and migrations from other RDBMS to DB2. He can be reached at rfchong@ca.ibm.com.

developerWorks Contributing author

31 October 2002

© 2002 International Business Machines Corporation. All rights reserved.


This article is written for IBM® DB2® UNIX®, Linux, and Windows® users who would like to leverage their DB2 knowledge in these platforms to gain some skills on DB2 Universal DatabaseTM (UDB) for iSeriesTM (a.k.a. AS/400®). With analogies using DB2 UDB UNIX, Linux, and Windows terms, we will explain the equivalent concepts on DB2 UDB for the iSeries. In this article we use the term "DB2 LUW" to reference "DB2 Universal Database Version 8.1 for Linux, UNIX, and Windows" and the term "DB2 UDB for iSeries" to reference "DB2 Universal Database for iSeries version 5.2." Though the focus is on DB2 LUW specialists gaining skills about DB2 on the iSeries, DB2 UDB for iSeries specialists can also gain skills on DB2 LUW by reading this material.

Some DB2 UDB for iSeries facts

Since the inception of AS/400, a relational database has been part of the OS/400® operating system. This integrated, fully relational database was branded "DB2 UDB for iSeries" in February of 1999. Though new functions and features are constantly being added with each new version of the product as in other DB2 platforms, the original database engine remains the same. Because DB2 UDB for iSeries is part of the OS/400 operating system, the version and release of the database system is the same as the one for the operating system. The most recently announced version is V5R2. See the article What's New with DB2 Universal Database for iSeries and V5R2 for more information on the new release and DB2 UDB Family On Common Ground for more information on DB2 UDB for iSeries.

With DB2 UDB for iSeries, database Data Definition Language (DDL) operations can be performed using either or both of these methods:

  • DDS (Data Description Specification)
    DDS is the AS/400 proprietary, native interface that was originally used with the database before SQL became popular. It is still used heavily for database operations.
  • SQL
    DB2 UDB for iSeries is fully compliant with the SQL-92 Entry Level Standard, and has implemented more of the core SQL-99 standard (ISO International Standard (IS), Information technology - Database language SQL, ISO/IEC 9075-1:1999, July 1999) than any other DB2 family member. Prior to V5R2, SQL support was available with the product; however, development of applications using SQL (e.g., SQL Stored Procedures, triggers, functions) required users to purchase the Query Manager and DB2 SQL Development Kit. With V5R2, there is no longer a need to purchase this kit as this functionality has been added as part of the iSeries base product. For all other SQL support such as the pre-compilers, the ISQL interface and the Query Manager, this kit is still chargeable.

Regardless of the method used, any programming language or database interface should be able to use these methods interchangeably to access DB2 UDB for iSeries data. Thus, for example, SQL statements can reference objects created with DDS. Table 1 below compares DDS and SQL concepts and terminology.

Table 1 - DB2 UDB for iSeries DDS vs SQL concepts and terminology comparison

Comes with iSeries, free of chargeComes with iSeries, free of charge
Handles DDL well, but not DMLHandles DDL and DML
LibrarySchema (a.k.a. **Collection)
Physical FileTable
Logical FileView
Keyed Logical FileIndex
Can be used to create screens and reportsBasic 'reports' based on the SQL output
The notation used to reference a file in a library is: <library>/<file name>
This is called the *SYS naming convention.
The notation used to reference a table as part of a schema is: <schema>.<table name>
This is called the *SQL naming convention.

**The term "Collection" is now deprecated. "Schema" should be used instead.

The examples below show how table "EMPPF" can be created with DDS (Examples 1 and 2) and with SQL (Examples 3 and 4):

Example 1. DDS - Physical File coding for file EMPPF

  EMPID           4A         TEXT('Employee ID') 
                             COLHDG('Emp' 'ID') 
  EMPTITLE        8A         TEXT('Employee title') 
                             COLHDG('Emp' 'Title') 
  DEPTID          3A         TEXT('Department ID') 
                             COLHDG('Dept' 'ID') 
  DEPTNAME        8A         TEXT('Department name') 
                             COLHDG('Dept' 'Name') 

Example 2. DDS - Keyed Logical File coding for file EMPLF

*************** Beginning of data ********************************** 
                R EMPLFREC                  PFILE(MYLIB/EMPPF)    
                  EMPID          4A                                  
                K EMPID                                              
****************** End of data *************************************

Example 3. SQL - Table Creation


Example 4. SQL - Index Creation


For the DDS version in these examples, we assume the library "MYLIB" was created prior to issuing the above DDS commands. This can be achieved by issuing the DDS command:


In the SQL version, this has explicitly been indicated by using the schema "MYLIB." You can create the library/schema "MYLIB" with the SQL statement CREATE SCHEMA MYLIB. For a DB2 LUW specialist trying to gain DB2 UDB for iSeries skills, it will be convenient to perform database operations using only SQL.

When developing SQL applications using the tools provided in the Query Manager and SQL Development Kit, only one iSeries machine needs to have this software installed. If you have written and compiled an iSeries program that includes embedded SQL, you can run the executable program on any iSeries system, as they all come with SQL parser and run-time support; the Query Manager and SQL Development Kit need not be installed in these other machines.

Automated DBA tasks in DB2 UDB for iSeries

Many tasks that a DBA is responsible for are completely automated in DB2 UDB for iSeries. The list below shows some of these automated tasks:

  • Management of physical space allocation
  • Review of tablespace allocation and extents
  • Application rebinding
  • Database integrity maintenance
  • Update of database statistics

DB2 UDB for iSeries SQL products and interfaces

DB2 UDB for iSeries provides a GUI interface called iSeries Navigator (previously known as Operations Navigator) that facilitates DBA operations. Some of these operations are:

  • Loading data into the database
  • Building and managing database backup and recovery
  • Creating and reviewing indexes for tables
  • Performance analysis and tuning for the database and iSeries system as a whole
  • Creating and maintaining database schema

The iSeries Navigator GUI tool is somewhat equivalent to the DB2 LUW Control Center. The iSeries Navigator, however, is used to administer entire iSeries systems from a workstation, not only database systems. The "Database Navigator" item in this tool contains all the relevant database operations. Besides iSeries Navigator, there are non-GUI interfaces (a.k.a. "green screen" interfaces) provided with the product:

  • Interactive SQL (STRSQL)
    This tool allows you to prototype SQL statements. It is equivalent to DB2 LUW Command Line Processor (CLP). Unlike the CLP, however, Interactive SQL provides substantial prompting, option listings and help.
  • DB2 Query Manager for iSeries
    This is a tool that helps you create SQL queries, create and maintain data, and run reports on the database. This tool is not equivalent to any DB2 LUW tools.

DB2 UDB for iSeries provides the following performance and monitoring tools:

  • SQL Performance Monitor
    The SQL Performance Monitor is a GUI tool invoked from the iSeries Navigator. It is mainly used to monitor and tune SQL queries. In DB2 LUW, SQL queries are monitored and tuned using different tools like the explain facility, the governor, and snapshots.
  • Database Monitor
    The Database Monitor is a green interface that can be started with the command strdbmon. This tool provides similar functionality to the SQL Performance Monitor.
  • Explain Facility
    This facility provides the same functionality as DB2 LUW's explain facility. As in DB2 LUW, there is a Visual Explain GUI tool that allows users to analyze the access path of queries.
  • Index Advisor
    This tool is similar to DB2 LUW's Index Advisor. It can be invoked from Visual Explain.
  • Performance Tool
    The Performance Tool is not specific to DB2, but is used in the entire iSeries system. It is a green interface with different options related to iSeries performance. If we just pick the options of this tool related to database performance, we could somewhat compare them to the DB2 LUW Health Center, as this tool monitors and detects database problems in general.

Comparing DB2 LUW and DB2 UDB for iSeries architecture

Figure 1 shows a simplified view of the DB2 LUW system structure, and Figure 2 displays the DB2 UDB for iSeries system structure.

Figure 1. DB2 system structure on Linux, UNIX, and Windows
DB2 system structure on Linux, UNIX, and Windows
Figure 2. DB2 system structure on the iSeries
DB2 system structure on the iSeries

Instances, databases, and tablespaces

In DB2 LUW, an instance provides an independent environment where database objects can be created and applications can be run against them. Within an instance, a DB2 user can create databases that are closed, independent units containing their own catalog, logs and configuration files. Within a database, tablespaces can be created that would establish an interface to the tables it contains with physical devices. You cannot perform queries that would involve tables of two different databases (unless Federated database support is set up). The catalog (SYSCATSPACE), temporary space (TEMPSPACE1) and the user space (USERSPACE1) are all tablespaces. These are created automatically when you issue a CREATE DATABASE command.

With DB2 UDB for iSeries, in general, there is only one environment where all objects reside. All of these objects are available system-wide as long as you have the authority to access them. The concept of instance does not exist. A database is not explicitly created with a command like CREATE DATABASE. In fact, databases are not normally needed under this architecture; schemas are used to group related objects. With V5R2, however, you can create independent databases that can be varied off at one iSeries system, and then varied on at another iSeries system. Creating this type of independent user databases requires the use of independent disk pools, which can be set up in the Disk Management function of iSeries Navigator. Once an independent disk pool is set up, it appears as another database under the Databases function of iSeries Navigator.

When a schema is created, a schema-wide catalog, journal and journal receiver are automatically created. This catalog stores database information particular to the schema where it resides. Objects from different schemas can interact with each other; thus, tables in different schemas can be used in the same SQL query. Tablespaces do not exist; allocation of your tables to physical devices is performed automatically by iSeries and the data is automatically striped and balanced across disks.

Besides the schema-wide catalog, there is another catalog consisting of libraries QSYS, QSYS2 and SYSIBM. This catalog stores database information for all schemas. Under library SYSIBM, the ODBC and JDBCTM catalog views reside. These views are compatible with views on DB2 UDB for OS/390® and z/OSTM and DB2 UDB LUW Version 8. These views will be modified as ODBC or JDBC enhances or modifies their metadata APIs.

Connecting to a database

In DB2 LUW, you attach to an instance to perform some administrative operations, and you connect to a database to perform database operations. In DB2 UDB for iSeries, when you connect to the iSeries system you are automatically connected to a database and can perform administrative as well as database operations. There are several methods to connect to iSeries; some of these methods are described below:

  • iSeries Access.
    This is the workstation client provided with iSeries
  • iSeries Navigator.
    This is the graphical user interface for managing and administering an iSeries server from a Windows desktop. In iSeries Navigator, as soon as you expand a database, you connect to it.
  • Operations Console.
    Operations Console is an installable component of iSeries Access for Windows. It allows you to use one or more PCs to access and control, either remotely or locally, the iSeries console and control panel functions.

If connecting from a DB2 LUW client, you need the DB2 Connect Software.

Logging vs. journaling

DB2 LUW uses "active" and "archive" logs for recovery purposes. It keeps track of its logs using the file SQLOGCTL.LFH, which is stored in the same directory where the database files are stored (<instance name>.NODE0000.SQL0000x). All changes to the tables of a database are logged. The NOT LOGGED INITIALLY clause of the CREATE TABLE statement, or the ACTIVATE NOT LOGGED INITIALLY clause of the ALTER TABLE statement, can be used to prevent logging on operations that are performed in the same unit of work in which the table is created; however, other operations against the table in subsequent units of work are logged.

DB2 UDB for iSeries logs changes to tables through a process called "journaling." The Journal Receiver is used to capture these changes. Journaling can be turned on and off on individual tables. Tables that don't have journaling enabled do not log any changes in the Journal Receiver at all. Besides reviewing the DB2 UDB for iSeries manuals, for more details about journaling you can review the IBM Redbook Striving for Optimal Journal Performance on DB2 Universal Database for iSeries, (SG24-6286-00), published on May 2nd, 2002.

Configuration parameters

DB2 LUW has parameters at the instance level (database manager configuration file) as well as at the database level (database configuration file). In DB2 UDB for iSeries, most configuration parameters affecting the database are set at the operating system level (e.g., RDB entries). There are no configuration files, but system values are used. Other configuration parameters related to performance like priority, timeslice, etc., are set at the job level. For example, by issuing the command chgqrya, any settings in file QAQQINI would be used for the given job. In addition, the chgqrya command allows you to change other settings dynamically (besides the ones read from file QAQQINI), like the query time limit processing, and the parallel processing degree.

Database interfaces

DB2 UDB for iSeries, like DB2 LUW, supports the following database interfaces: ODBC, CLI, JDBC, and SQLJ. In addition, it also provides SQL precompilers for processing of embedded static and dynamic SQL in application programs. Client-side interfaces with the "iSeries Access" client provide the ODBC and JDBC drivers, as well as support for OLE DB, ADO, and so on at no extra charge.


This article provided an overview of DB2 UDB for iSeries concepts by comparing them to similar concepts in DB2 LUW. Many of the topics covered are conceptually similar. For example, DB2 UDB for iSeries SQL syntax, support for common interfaces (DRDA, JDBC, ODBC, CLI), common tools, and so on are similar to DB2 LUW.

DB2 UDB for iSeries is integrated with the OS/400 operating system, and as such, its architecture is somewhat different from DB2 LUW. DB2 UDB for iSeries database objects are available system wide, with no concept of instance or tablespace. Many of the DBA tasks have been automated.


Special thanks to John Mascarenhas of the DB2 UDB for iSeries Application Enabling Support Systems team for his feedback about this document.



developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Dig deeper into Information management on developerWorks

Zone=Information Management
ArticleTitle=Leverage Your Distributed DB2 Skills to Get Started on DB2 UDB for iSeries (AS/400)