Getting Started with DB2 UDB for LUW

The following section covers the fundamentals of interacting with DB2 for LUW. After reviewing this section, you should have a good understanding of basic DB2 architecture, how to perform several important administrative duties, and how to work with DB2 for everyday activities.

Launching the DB2 Command Window

The first thing you must learn is how to launch the DB2 Command window. From the Windows Start menu, click Programs > IBM DB2 > Command Line Tools and select Command Window (see Figure A.1).

Figure A.1. Launching the DB2 Command window using the Windows Start menu.

Another way to launch the Command window is by running db2cmd from the Windows Run window (see Figure A.2).

Figure A.2. Launching the DB2 Command window using db2cmd.

The DB2 Command Window is the command-line interface to DB2. At first glance, it appears exactly the same as the standard Windows DOS prompt, except that the title bar says "DB2 CLP." DB2 commands cannot be executed within the normal Windows DOS prompt because the DB2 command-line environment has not been initialized. If you have problems executing the commands presented in this appendix, make sure that you are in the DB2 command-line environment by verifying that the title bar reads "DB2 CLP," as shown in Figure A.3.

Figure A.3. The DB2 Command window with "DB2 CLP" in the title bar.

Launching the DB2 Command Line Processor

Besides the DB2 Command window, you can also issue DB2 commands in DB2's interactive mode by launching the DB2 Command Line Processor. From the Windows Start menu, click Programs > IBM DB2 > Command Line Tools and select Command Line Processor (see Figure A.4).

Figure A.4. Launching the DB2 Command Line Processor using the Windows Start menu.

You should see a window similar to Figure A.5.

Figure A.5. The DB2 Command Line Processor.

Notice that the DB2 Command Line Processor window is loaded with a db2 => prompt as compared to a DOS prompt in DB2 Command Line window. The db2 => prompt indicates that you are in the DB2 interactive mode. You can issue any DB2 commands in this mode without a db2 prefix. To exit from the interactive mode, simply issue quit. This will bring you back to the DB2 Command window described in Figure A.3.

Overview of DB2 Architecture

There is no better place to start describing the DB2 architecture than with a diagram. Figure A.6 shows the basic components of a DB2 environment. We will start from the top at the instance level, and drill down into the details of databases, buffer pools, table spaces, and so on.

Figure A.6. Overview of the DB2 architecture.

DB2 Instances

A DB2 instance is a logical context in which DB2 commands and functions are executed. You can think of an instance as a service or a daemon process that manages access to database files. More than one instance can be defined on a server machine. Each instance is independent of the others, meaning that they are managed, manipulated, and tuned separately.

With the default installation of DB2 v8 (including v8.1 and v8.2), one instance is automatically created called DB2 on Windows and db2inst1 on UNIX. The default instance is sufficient for creating a database to run the examples contained in this book.

If you want to list the instances defined on your machine, you can issue the following command in a DB2 Command window or a DOS window:

 db2ilist 

Because this command is an executable, it is not recognized in the DB2 interactive mode.

Creating Databases

Each DB2 database is made up of buffer pools, table spaces, tables, metadata information, database log files, and many other components. Once an instance has been created and started, databases can be created within it.

A SAMPLE database comes with every DB2 server installation, but you must manually initiate its creation. The database is used by all the examples illustrated in this book. The command db2sampl -k will create the SAMPLE database, create tables and primary keys in the database, and populate the tables with data. Notice that the command is db2sampl without the letter e. Table structures and content for all examples used in this book can be found in Appendix F, "DDL."

If you want to create your own database, use the CREATE DATABASE command. Many options are supported by this command, which allows for customization such as the location of the database, database code page settings, default table space characteristics, and so on. However, the command for creating a database can be as simple as the following:

 db2 CREATE DATABASE <dbname> 

A complete syntax diagram of the CREATE DATABASE command is available in the DB2 Command Reference.

Before you can work with database objects such as tables, you must first connect to the database. The command to make a database connection is very simple:

 db2 CONNECT TO <dbname> 

This command will connect to the database with the user ID currently logged on to the operating system. If you want to connect to the database as a different user ID, specify the USER option:

 db2 CONNECT TO <dbname> USER <username> 

DB2 will then ask for the password and send it to the operating system for authentication. Figure A.7 illustrates the creation of the SAMPLE database and establishment of a connection from the command line.

Figure A.7. Creating the SAMPLE database and establishing a connection from the command line.

To disconnect from a database, use

 db2 CONNECT RESET 

Note that DB2 does not manage and authenticate user IDs and passwords. They are validated by the operating system or other supported external security facilities. Therefore, there is no need to create database users in order to connect to the database.

Executing SQL in the DB2 Command Window

At this point, you are all set to perform some data manipulation. Just like other database management systems, data is manipulated by means of SQL statements. To execute a single SQL statement from the DB2 Command window, pass the SQL statement as a parameter to the db2 command:

 db2 "SELECT * FROM EMPLOYEE" 

It is often useful to group SQL statements together in a script and execute them using one command. To do this, save the set of SQL statements to a file and use the db2 command with the -tf option. For example, assume you have a file called sqlstmt.db2 that contains a number of SQL statements as shown in Figure A.8.

Figure A.8. Example of a text file with multiple SQL statements.

In Figure A.8, each statement is separated by the default statement terminator, the semicolon (;). Use the following command to execute the statements contained in the input file:

 db2 -tf sqlstmt.db2 

In some cases, the semicolon cannot be used as the statement terminator. For example, scripts containing the CREATE PROCEDURE statement cannot use the default terminator because SQL procedure bodies themselves already use semicolons for statement terminators. An additional option, -td, is available to change the termination character. If sqlstmt.db2 had instead used @ as the statement terminator, you would use this command to execute the script:

 db2 -td@ -f sqlstmt.db2 

Note that there is no space between the -td option and the delimiter character.

All the examples presented in this book are executed using the DB2 Command window. However, you should also learn about the DB2 interactive mode because it supports some unique features.

Note

Remember to first connect to the target database before the script can be executed successfully.

Executing SQL in DB2 Interactive Mode

If you choose to use the DB2 interactive mode, you must enter the statement one at a time. Execution of multiple statements in an input file is not supported. A single SQL statement is executed in DB2 interactive mode like this:

 db2 => select * from employee 

The DB2 interactive mode has a memory of all the commands and statements issued in the current session. You can use the History or H (case is not sensitive) command to obtain a list of commands executed thus far (see Figure A.9).

Figure A.9. Example of using the History command in DB2 interactive mode.

 db2 => History 1    connect to sample 2    select * from employee 3    h 4    update employee set salary=10000 where empno='000010' 5    insert into class values (a, b, c) 6    History 

You can also pick a particular command from the list and edit it in your default word processor. The Edit or E (case is not sensitive) command is used to accomplish that. For example, using the history list from Figure A.9, the following command will bring up the Notepad as shown in Figure A.10.

Figure A.10. Edit a previously executed DB2 statement in Notepad.

 db2 => Edit 2 

Configuring the DB2 Environment

Now we'll discuss how to configure the DB2 environment. Proper setup of the DB2 environment is very important because it controls how DB2 operates and functions. It can also be used to customize creation, development, and deployment of SQL procedures. The DB2 configuration parameters, operating system environment variables, and DB2 profile registry variables make up the DB2 environment.

You can change instance and database level configuration parameters that affect the behavior of DB2. With the following commands, you can obtain the instance (also called database manager) and database configuration parameters:

 db2 GET DATABASE MANAGER CONFIGURATION db2 GET DATABASE CONFIGURATION FOR <dbname> 

To modify database manager or database configuration parameters, use

 db2 UPDATE DATABASE MANAGER CONFIGURATION USING <parameter> <new value> db2 UPDATE DATABASE CONFIGURATION FOR <dbname> USING <parameter> <new value> 

More than 150 configuration parameters are online-configurable, which means that changes to these parameters can be in effect immediately if the requested resource is available. Changes to the other parameters become effective only after the instance is re-started or the database is re-activated.

This behavior can be explicitly specified with the IMMEDIATE option in the UPDATE DATABASE MANGER CONFIGURATION command, like this:

 db2 UPDATE DATABASE MANAGER CONFIGURATION USING <parameter> <new value> IMMEDIATE db2 UPDATE DATABASE CONFIGURATION FOR <dbname> USING <parameter> <new value> IMMEDIATE 

You can also choose to defer the changes until the instance is re-started or the database is re-activated. Simply use the same command described previously, but specify the DEFERRED option:

 db2 UPDATE DATABASE MANAGER CONFIGURATION USING <parameter> <new value> DEFERRED db2 UPDATE DATABASE CONFIGURATION FOR <dbname> USING <parameter> <new value> DEFERRED 

The majority of the parameters are related to performance, and they are not covered in detail here in this book. The DB2 Administration Guide provides detailed descriptions of each configuration parameter at both instance and database levels.

Operating system environment variables, as the name implies, are set at the operating system level. The syntax for setting an operating system environment variable depends on the platform and the type of shell you are using. For example, to set the current instance environment to PRODINST on a Windows platform, you use

 set DB2INSTANCE=PRODINST 

On AIX with the korn shell, you use

 export DB2INSTANCE=PRODINST 

DB2 Profile Registries

DB2 profile registries are DB2-specific variables that affect the management, configuration, and performance of the DB2 system. DB2 profile registries have no relation to the Windows registries. The db2set command is used to view and update the variables. To view all available options, you can use

 db2set -? 

To list out the values of the DB2 profile registry variables currently set on the server, use the -all option as illustrated in Figure A.11.

Figure A.11. Usage and sample output of db2set -all.

 db2set -all [e] DB2PATH=C:\SQLLIB [i] DB2INSTPROF=C:\SQLLIB [i] DB2COMM=TCPIP,NPIPE                -- (1) [g] DB2SYSTEM=DB2NTSERV [g] DB2PATH=C:\SQLLIB [g] DB2INSTDEF=DB2 [g] DB2COMM=TCPIP,NPIPE                -- (2) [g] DB2ADMINSERVER=DB2DAS00 

The db2set -all command displays the settings of the environment. Indicators such as [e], [i], and [g] represent the scope of the setting:

 [e] represents the setting of the environment [u] represents the user level registry [n] represents node level registry [i] represents instance level registry [g] represents global level registry 

In Figure A.11, you may notice that some registry variables appear twice: one at the instance level on Line (1) and one at the global level on Line (2). Which one does DB2 use if values are set differently? The instance level profile registry variables take precedence over the same named global level registry variables. Some operating system environment variables that DB2 recognizes may also have the same identifier as the profile registry variables. In this case, the operating system environment variable is used.

Figure A.12 illustrates some options on how to set DB2 profile registry variables.

Figure A.12. Example of the db2set command.

 db2set VARIABLE=VALUE                      -- (1) db2set VARIABLE=VALUE -i <instname>        -- (2) db2set VARIABLE=VALUE -g                   -- (3) 

Line (1) of Figure A.12 sets the variable for your current instance as defined by the DB2INSTANCE environment variable. Line (2) uses the -i option to set the variable for the <instname> instance. Line (3) uses the -g option to set the variable globally for all instances defined on the server.

Changes made to DB2 profile registry variables are not dynamic, meaning that new values do not immediately take effect. To implement the new changes, you need to stop and start the instance.

Before you can use an instance, you must start the instance using the db2start command. If you select the default install options, the default instance is configured to start up automatically. To stop an instance, use db2stop.

If there are connections already made to the database and you try to stop the instance, you will receive this error:

 SQL1025N The database manager was not stopped because databases are still active. 

All connections made to databases that are defined in an instance must be terminated before that instance can be stopped. To list the applications currently connected to the databases managed by the current instance, you can use the LIST APPLICATIONS command (see Figure A.13).

Figure A.13. Sample output of the LIST APPLICATIONS command.

 db2 LIST APPLICATIONS Auth Id   Application    Appl.      Application Id               DB      # of           Name           Handle                                  Name    Agents --------  -------------- ---------- ---------------------------- ------- ----- SQLSPUSER db2bp.exe      5          *LOCAL.DB2.020606173650      EFORMDB 1 DB2ADMIIN db2cc.exe      4          *LOCAL.DB2.020606173635      SAMPLE  1 SQLSPUSER java.exe       6          7F000001.5505.020606173800   SAMPLE  1 SQLSPUSER db2bp.exe      7          *LOCAL.DB2.020606173811      SAMPLE  1 

Figure A.13 shows a sample output of LIST APPLICATIONS. The Auth Id column of the output represents the authentication ID used to connect. The second column is the application name for the connection. Each connection has a unique application handle (Appl. Handle) and Application Id. The DB Name column tells you which database each connection is connected to. The last column is the number of database agent processes working for the connection.

With this information, you can identify and ask the users to log out from the database. Sometimes, expecting users to do this is not feasible. In these circumstances, you can use the FORCE APPLICATION command to terminate their connections. The following command will disconnect every connection from all the databases defined in an instance:

 db2 FORCE APPLICATION ALL 

To selectively terminate a single or a number of connections, you specify the application handles obtained from the output of the LIST APPLICATIONS command in the FORCE APPLICATION command.

For example, you might want to terminate every connection made to the SAMPLE database. Using the sample output in Figure A.13, application handles 4, 6, and 7 can be terminated by executing

 db2 "FORCE APPLICATION (4, 6, 7)" 

Note

Double quotes ("") are used here because different operating systems may treat parentheses differently. Enclosing the command with double quotes ensures that the whole command is sent to DB2 for processing.

Internally, this command will terminate the appropriate database processes and roll back any uncommitted changes for the terminated connections asynchronously. Thus, you may not see the effect of the command immediately.

Understanding Buffer Pools

Instances and databases have been discussed at a high level, and we can now dig deeper into the details of databases, starting with buffer pools. To reduce the number of I/O operations to the physical disk where data is stored, DB2 uses buffer pools to cache data and index pages in memory. Every database must have at least one buffer pool defined. The default buffer pool created in each database is called IBMDEFAULTBP. The default buffer pool is sufficiently large enough for you to work with the examples presented in this book. If you are creating a database for real workloads, however, the default size of IBMDEFAULTBP is typically not sufficient. You should refer to the DB2 Administration Guide for a more in-depth discussion on buffer pools.

You can find out what buffer pools are currently defined by connecting to the database and executing the following SQL statement:

 db2 "SELECT * FROM SYSCAT.BUFFERPOOLS" 

Note

Similar to the example shown above asterisk (*) are treated as special characters in most operating systems, enclose the SQL statement with double quotes ("") so that it can be executed successfully on any Linux, UNIX, and Windows platforms.

Creating a buffer pool is also quite easy. To create a buffer pool called BP16K that uses a 16K page size and is 1,000 pages large (page size and pages will be discussed shortly), use the CREATE BUFFERPOOL statement:

 db2 CREATE BUFFERPOOL BP16K size 1000 pagesize 16K 

You can also change the size of a buffer pool using the ALTER BUFFERPOOL statement. For example, to reduce the size of the previously listed buffer pool to just 500 pages, execute this code:

 db2 ALTER BUFFERPOOL BP16K size 500 

Working with Table Spaces

A table space can be viewed as a container for tables, and its responsibility is to manage how table data is physically stored on disk. In other words, tables are logical objects that are created within table spaces.

One of the main characteristics of table spaces is page size. When table data is stored on disk, data is stored on data pages that can range in size. DB2 supports 4K, 8K, 16K, and 32K page sizes. If you create a table space TS16K that uses a 16K page size and create a table T1 in TS16K, then all data inserted into T1 is stored physically on disk using 16K data pages.

Each table space is mapped to a buffer pool, and the buffer pool must use the same page size as the table space. So naturally, buffer pool page sizes of 4K, 8K, 16K, and 32K are also supported. For example, if you want to create a table space of 16K page size, a 16K page size buffer pool must exist before the table space can be created. The IBMDEFAULTBP buffer pool uses a 4K page size.

Three default table spaces are created at database creation time:

• SYSCATSPACE (catalog)

• TEMPSPACE1 (temporary)

• USERSPACE1 (user)

All use a 4K page size and use IBMDEFAULTBP as their buffer pool.

The catalog table space, SYSCATSPACE, stores tables that contain metadata about the database. These metadata tables are commonly referred to as the system catalog tables. SYSCATSPACE is created during database creation. No user objects can be defined in it.

The temporary table space, TEMPSPACE1, is used to store temporary data. Temporary table spaces are used by DB2 implicitly for tasks such as sorting, and can be used explicitly for tasks such as table reorganization. Therefore, you must have at least one temporary table space defined in each database.

USERSPACE1 is a REGULAR table space that stores user-defined objects like tables and indexes. You can create more than one user table space with different characteristics in a database.

When creating a table space, in addition to page size you also need to specify one or more table space containers that map to actual physical storage such as an operating system directory, file, or raw device. You will also need to specify whether the table space will be managed by the database (known as Database Managed Space, or DMS), or by the operating system (known as System Managed Space, or SMS). Containers for SMS table spaces are operating system directories; whereas containers for DMS table spaces can be pre-allocated files or raw devices. If you create a database without any options to the CREATE DATABASE command, the three default table spaces are created as SMS table spaces.

Figure A.14 shows some simple examples of how REGULAR, LARGE, and TEMPORARY table spaces can be created.

Figure A.14. Examples of creating REGULAR, LARGE, and SYSTEM TEMPORARY table spaces.

 CREATE REGULAR TABLESPACE userdata_ts              -- (1)     MANAGED BY SYSTEM     USING ( 'c:\userdata1'           , 'e:\userdata2' ); CREATE LARGE TABLESPACE largedata_ts               -- (2)     MANAGED BY DATABASE     USING ( file 'c:\largedata\largefile.f1' 3M           , file 'd:\largedata\largefile.f2' 3M ); CREATE TEMPORARY TABLESPACE temp_ts                -- (3)     MANAGED BY SYSTEM     USING ( 'c:\tempdata1' ); 

On Line (1), userdata_ts is a REGULAR table space. The keyword REGULAR is optional. It is MANAGED BY SYSTEM that means it is an SMS table space. User-defined database objects can be stored in it. Data will be striped across two containers, and they are operating system directories.

The second table space on Line (2), largedata_ts, is a LARGE table space. LARGE table spaces are used to store only large objects such as Binary Large Objects (BLOB) or Character Large Objects (CLOB). LARGE table spaces must be defined as DMS with the MANAGED BY DATABASE clause. Notice that two files are defined as the containers for this table space, and each of them is 3MB in size.

The last example on Line (3) creates an SMS system temporary table space with only one container.

Detailed characteristics and differences between SMS and DMS table spaces are not discussed here. More information can be obtained from the DB2 Administration Guide or references listed in Appendix G, "Additional Resources," of this book. By using some DB2 commands, you can easily obtain the list of table spaces defined in a database, their status, and location of the containers for each table space.

To list all the table spaces of a database, connect to the database and issue

 db2 LIST TABLESPACES 

You will receive output similar to Figure A.15.

Figure A.15. Sample output of the LIST TABLESPACES command.

           Tablespaces for Current Database Tablespace ID                        = 0 Name                                 = SYSCATSPACE Type                                 = System managed space Contents                             = Any data State                                = 0x0000   Detailed explanation:     Normal Tablespace ID                        = 1 Name                                 = TEMPSPACE1 Type                                 = System managed space Contents                             = System Temporary data State                                = 0x0000   Detailed explanation:     Normal Tablespace ID                        = 2 Name                                 = USERSPACE1 Type                                 = System managed space Contents                             = Any data State                                = 0x0000   Detailed explanation:     Normal Tablespace ID                        = 3 Name                                 = LARGEDATATS Type                                 = Database managed space Contents                             = Any data State                                = 0x0000   Detailed explanation:     Normal 

The output provides the unique table space ID, table space name, type of the table space (for example, SMS or DMS), and status of the table space.

To obtain information about the containers used for a specific table space, execute

 db2 LIST TABLESPACE CONTAINERS FOR <table-space-id> 

You need to provide the table space ID in this command and this information is available from the output of the LIST TABLESPACES command. An example of using this command and the result is shown in Figure A.16.

Figure A.16. Example of the LIST TABLESPACE CONTAINERS command.

 db2 LIST TABLESPACE CONTAINERS FOR 3             Tablespace Containers for Tablespace 3 Container ID                         = 0 Name                                 = c:\largedata\largefile.f1 Type                                 = File Container ID                         = 1 Name                                 = d:\largedata\largefile.f2 Type                                 = File 

Working with Tables

Now it's time to learn how to create some tables. Again, tables are created within table spaces. A default table space called USERSPACE1 is created for you upon database creation so that you can begin creating tables right away.

Figure A.17 shows an example of creating a table with a primary key defined on the classid column on Line (1).

Figure A.17. Example of table creation.

 CREATE TABLE classes     ( classid INTEGER NOT NULL     , classname VARCHAR(50) NOT NULL     , instructor VARCHAR(50)     , classdate DATE     , PRIMARY KEY ( classid ) )    -- (1)     IN userdata_ts                 -- (2) 

To explicitly specify which table space you want a table to be created in, use the IN <table-space-name> clause as illustrated on Line (2). If the IN clause is not specified, the table will be created in a table space that has a sufficiently large page size, given the row size of the table, and where the user has privileges to do so. Every user object in DB2 must belong to a schema. A schema is a logical grouping of database objects such as tables and stored procedures. It is possible to have tables with the same name but in different schemas. You can explicitly reference a database object by its schema and object name using <schema-name>.<db-object>. Notice that schema is not specified as part of the statement in Figure A.17. In such cases, the authorization ID used to connect and execute the CREATE TABLE statement will be used for the schema.

In some situations, you may want to create tables in a schema other than your authorization ID but don't want to fully qualify all the object names. There is a DB2 special register called CURRENT SCHEMA that can be used to obtain or set the current schema. To obtain the current schema, simply do this:

 db2 VALUES CURRENT SCHEMA 

The default value of the CURRENT SCHEMA is the authorization ID used to connect to the database.

To change the current schema, use this command:

 db2 SET CURRENT SCHEMA = NEWSCHEMA 

Once the new schema is set, any references to database objects that are not explicitly qualified with a schema will implicitly use this new schema for the duration of the session.

To list the tables defined in a database, you can use the LIST TABLES command. This command has several options.

 db2 LIST TABLES 

This command lists tables that belong to the schema assigned to the CURRENT SCHEMA register mentioned above.

If you want to list all the tables under a specific schema, you can extend the previous command to:

 db2 LIST TABLES FOR SCHEMA <schema-name> 

The FOR ALL option shown below will then list all the tables under every schema defined in the database.

 db2 LIST TABLES FOR ALL 

One other useful thing to know is how to easily find out the structure of a table. For example, you may want to insert a row in a table for which you do not know the data type for a specific column. This command will definitely save you time in searching for the answer:

 db2 DESCRIBE TABLE <table-name> 

To describe the STAFF table of the SAMPLE database, you can issue

 db2 DESCRIBE TABLE staff 

You should receive output similar to Figure A.18.

Figure A.18. Example output of the DESCRIBE TABLE command.

 Column                       Type      Type name                         schema    name               Length   Scale  Nulls ---------------------------- --------- ------------------ -------- ------ ---- ID                           SYSIBM    SMALLINT                  2     0  No NAME                         SYSIBM    VARCHAR                   9     0  Yes DEPT                         SYSIBM    SMALLINT                  2     0  Yes JOB                          SYSIBM    CHARACTER                 5     0  Yes YEARS                        SYSIBM    SMALLINT                  2     0  Yes SALARY                       SYSIBM    DECIMAL                   7     2  Yes COMM                         SYSIBM    DECIMAL                   7     2  Yes   7 record(s) selected. 

Introducing the System Catalog Tables

Most commands demonstrated in this appendix so far obtain information about the database. For example, the LIST TABLESPACES and LIST TABLES commands list the table spaces and tables defined in a database, respectively. Where is the information actually stored? The information about data (also known as metadata) is contained in the system catalog tables. These tables are defined under the SYSIBM schema. Because the amount of information stored in the SYSIBM tables is usually more than what database administrators or users want to know, filtered information is extracted through the use of views. These views are defined under the SYSCAT schema.

Table A.1 shows some common SYSCAT views that may be useful when developing your SQL procedures. For a complete list, refer to the DB2 SQL Reference Guide.

Table A.1. Common System Catalog Views

CatalogViews	Descriptions
SYSCAT.BUFFERPOOLS	Buffer Pool configuration
SYSCAT.COLUMNS	Columns
SYSCAT.FUNCTIONS	Functions
SYSCAT.PACKAGES	Packages
SYSCAT.PROCEDURES	Stored procedures
SYSCAT.TABLES	Tables
SYSCAT.TABLESPACES	Table spaces
SYSCAT.TRIGGERS	Triggers