This section describes how you would get started in a DB2 UDB for iSeries environment. The appendix first describes the DB2 UDB for iSeries architecture, followed by some typical activities and associated interfaces you would use to get started. Where appropriate, comparisons to DB2 LUW will be called out, and equivalent concepts will be highlighted.
There are several interfaces for working with iSeries platforms and databases on that platform. Possible options are
This section focuses on the first two interfaces. However, in some cases, some other interfaces may be discussed as well.
Launching the Interface
In order to start working with an iSeries environment, you will need access to an iSeries server. You can use the native interface to work with iSeries, in which case you would log on directly on the server, or use an emulator from a Windows environment to log on as shown in Figure A.19.
Figure A.19. Logged in to a native environment.
Alternatively, you can install the IBM iSeries Access for Windows software and use the iSeries Navigator for Windows. To launch this environment, from the Windows Start menu click Programs > IBM iSeries Access for Windows > iSeries Navigator (see Figure A.20).
Figure A.20. Launching the iSeries Navigator.
Once the environment is launched, you will need to create a connection to your server. By using the connection, you can then explore and navigate through your iSeries environment (see Figure A.21).
Figure A.21. iSeries Navigator interface.
Overview of the DB2 UDB for iSeries Architecture
DB2 UDB for iSeries is part of the i5/OS operating system. It has always been a part of the operating system and was branded as DB2 UDB in February 1999. As new functions are added, DB2 UDB on iSeries remains fully integrated with the operating system.
Any database operations on a DB2 UDB for iSeries database can be performed using one of two methods:
Either one of the methods can be used to access DB2 UDB of iSeries data. Table A.2, taken from the iSeries V5R3 Information Center, shows some of the differences in terminology and concepts between the two methods.
Now that you've been introduced at a high level to the iSeries platform, we can delve a little deeper into the architecture of the database within the platform. Refer to Figure A.22.
Figure A.22. DB2 UDB for iSeries architecture.
As you can see from Figure A.22, an iSeries server (in general) has only one system-wide database. All database objects are available to the entire system, as long as the user has authority to access them.
The concept of an instance does not exist. Another way of looking at it is that only a single instance is supported on an iSeries server. Because the database is part of the operating system, there is no explicit CREATE DATABASE command. The database already exists and is available.
Typically, additional databases are not created. Different database environments are handled using different schemas. Objects from different schemas can be accessed using a single query. It is possible, however, to create additional independent databases by creating independent disk pools. When an independent disk pool is created, it appears to the system as another database. These independent databases can be made available (varied on) and not-available (varied off) to the system. Typical uses for them are for infrequently used data or as a high availability solution for clustered environments.
The CREATE SCHEMA statement can be used to create schemas explicitly. Explicit creation of a schema will automatically create catalog views (discussed later) to database objects created under that schema.
Additionally, a Journal and a Journal Receiver is also created. The Journal is used to log database activity, and records this activity to a Journal Receiver. A key benefit in employing Journal Management is faster recovery time after an outage. If you are familiar with DB2 on distributed platforms, Journal Management is comparable archive logging. Journaling can be turned on and off at the table level. In this case, changes made to the table are not logged. This is equivalent to using the NOT LOGGED INITIALLY table option on the distributed platforms.
Disk management is performed automatically by the underlying operating system. When a table is created, its association to the physical disk is managed by the operating system. The table is automatically striped across physical disks, and data is balanced across them as required. iSeries administrators can have some influence over how physical disk is utilized through the management and control of disk pools; however, this topic is beyond the scope of this book. In contrast to the distributed platforms, the concept of a table space does not exist on iSeries.
Memory management is also performed automatically by the underlying operating system. Through management and control of memory pools, iSeries administrators can have some influence over how memory is utilized; however, this topic is beyond the scope of this book. When an SQL statement is issued, the operating system will determine how much memory to allocate to the executing statement. In contrast to the distributed platforms, the concept of a buffer pool does not exist on iSeries.
As you've probably determined by now, any configuration on a DB2 UDB for iSeries database is performed at the operating system level. Tuning and configuring a DB2 UDB for iSeries database requires a more indepth understanding of the iSeries platform and operating system. However, many administrative tasks, including the collecting of statistics on tables and indexes, are handled automatically on the iSeries platform.
Executing SQL in DB2 UDB for iSeries
You can use several methods to issue SQL commands; however, as specified earlier, we will focus on two methods: the interactive SQL interface from the i5/OS native system, and the iSeries Navigator for Windows.
To start the interactive SQL interface form the i5/OS native system, issue the following command, which invokes the utility with the option of using SQL naming conventions:
You will be presented with a screen similar to the one shown in Figure A.23.
Figure A.23. iSeries Interactive SQL screen.
Alternatively, you can use the Run SQL Scripts window of the iSeries Navigator. Drill down to the left-hand side in the Database folder and click on one of the databases. In the bottom-right hand pane entitled Database Tasks, you should see a link for Run an SQL Script. Click on that, and it will open a window as shown in Figure A.24.
Figure A.24. iSeries Navigator Run SQL Scripts window.
SQL statements can be entered in either one of these interfaces. iSeries system commands can be entered through the Run SQL Scripts interface by prefixing the command with CL:, for example:
CL: CHGQRYA QRYOPTLIB(DB2ADMIN)
The default isolation level for the Run SQL Scripts window is NO COMMIT. This means that if this environment is used to create SQL procedures or functions, The SQL procedures or functions will be created with the NO COMMIT isolation level. This isolation level is often not desired for managing transactions, because COMMIT and ROLLBACK statements are not allowed.
Change the default isolation level of the Run SQL Scripts window using the Connection -> JDBC Setup menu option, or explicitly set it prior to issuing your statement using the SET statement:
SET TRANSACTION ISOLATION LEVEL CS
Other interfaces, such as Query for iSeries, can be used in addition to the two methods shown here.
If you are using a DB2 UDB for Windows Client and want to use the CLP, then refer to the previous section on how to use that environment.
So far, you've seen that a database already exists on an iSeries system. Once a user logs on, he or she will be able to execute SQL statements. The first step in starting to work with a database is to create your own work area or schema. Typically, you would name the schema after your user ID to indicate the owner. For production databases, the schema could be something more relevant to the database and its application.
CREATE SCHEMA <schemaname>;
The CREATE SCHEMA command will create a schema-level set of catalog views and set up Journal Management for all tables created under that schema as well.
Working with Tables
Once you've created a schema, you can then create database objects such as tables. Because there is no concept of table spaces, you do not need to specify a table space in which to create the table. Recall that you can use special registers to define the default schema and path. So if you wanted to create a schema and objects within that schema, without qualifying the objects, you can use a script similar to the one shown in Figure A.25.
Figure A.25. Example of table creation on iSeries.
CREATE SCHEMA appendixA; SET SCHEMA appendixA; SET PATH appendixA, SYSTEM; CREATE TABLE classes ( classid INTEGER NOT NULL , classname VARCHAR(50) NOT NULL , instructor VARCHAR(50) , classdate DATE , PRIMARY KEY ( classid ) ) -- (1)
The table will also create an associated primary key on the column classid. Notice that there is no high-level qualifier for the table name. In this case, the value of the CURRENT SCHEMA special register will be used as the high-level qualifier. The CURRENT SCHEMA special register defaults to the current user ID, if it is not explicitly set. Alternatively, a high-level qualifier could have been specified in the table definition itself.
The command LIST TABLES, which is available on the DB2 UDB distributed platforms, is not supported on DB2 UDB for iSeries. Similarly, the DESCRIBE TABLE statement is not supported interactively on iSeries. It is supported through a programming interface using the DESCRIBE TABLE ... INTO clause. The commands, however, can be mimicked interactely by querying the catalog tables.
The terms schema-wide catalog and system-wide catalog have been mentioned earlier in the iSeries section. The catalog is a series of system tables and views that holds the metadata for any database object which is created.
The system-wide catalog tables and views are stored under the QSYS and QSYS2 schema, and contain information about every database object that is created on the system.
ODBC and JDBC catalog tables and views reside under the SYSIBM schema. This catalog supports ODBC and JDBC catalog API requests.
The schema-wide catalog is created when the CREATE SCHEMA statement is issued. It consists of a set of views against the system-wide catalog tables, but restricted to objects in that schema. Table A.3 lists the views that are created when the CREATE SCHEMA statement is issued and their associated description.
If you wanted to use the catalog views to list the tables in the current schema, you could issue the statement as shown in Figure A.26. The associated output is also shown.
Figure A.26. An example of querying a catalog view.
In addition to using the schema-wide catalog views to query catalog information, the IBM-supplied objects for iSeries under the SYSIBM schema can also be used. This schema contains a richer set of views on the system-wide catalog, as compared to the schema-wide views. Refer to the SQL Reference for a listing of the views under the SYSIBM schema.