© 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.
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 charge||Comes with iSeries, free of charge|
|Handles DDL well, but not DML||Handles DDL and DML|
|Library||Schema (a.k.a. **Collection)|
|Keyed Logical File||Index|
|Can be used to create screens and reports||Basic '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.
Example 1. DDS - Physical File coding for file EMPPF
T.Name++++++RLeng++TDpB......Functions+++++++++++++++++++++++++ R EMPREC 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') K EMPID
Example 2. DDS - Keyed Logical File coding for file EMPLF
.....A..........T.Name++++++.Len++TDpB......Functions+++++++++++++++ *************** Beginning of data ********************************** R EMPLFREC PFILE(MYLIB/EMPPF) EMPID 4A K EMPID ****************** End of data *************************************
Example 3. SQL - Table Creation
CREATE TABLE MYLIB.EMPPF (EMPID CHAR(4) NOT NULL WITH DEFAULT, EMPTITLE CHAR(8) NOT NULL WITH DEFAULT, DEPTID CHAR(3) NOT NULL WITH DEFAULT, DEPTNAME CHAR(8) NOT NULL WITH DEFAULT);
Example 4. SQL - Index Creation
CREATE INDEX EMPIX ON MYLIB.EMPPF(EMPID);
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:
CRTPF FILE(MYLIB/EMPPF) SRCFILE(MYLIB/QDDSSRC) SRCMBR(EMPPF).
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. DB2 system structure on Linux, UNIX, and Windows
Figure 2. 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.
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.
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.
- DB2 UDB Database Navigator and Reverse Engineering
- DB2 UDB for AS/400 Visual Explain
- Using AS/400 Database Monitor and Visual Explain to Identify and Tune SQL Queries
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Experiment with new directions in software development.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.