Section 2.3. The DB2 Environment

2.3. The DB2 Environment

Several items control the behavior of your database system. We first describe the DB2 environment on a single-partition database, and in section 2.6, Database Partitioning Feature, we expand the material to include concepts relevant to a multipartition database system (we don't want to overload you with information not required at this stage in the chapter).

Figure 2.3 provides an overview of the DB2 environment. Consider the following when you review this figure:

• The figure may look complex, but don't be overwhelmed by first impressions! Each item in the figure will be discussed in detail in the following sections.

• Since we reference Figure 2.3 throughout this chapter, we strongly recommend that you bookmark page31 . Alternatively, since this figure is available in color as a GIF file on the CD-ROM provided with this book (Figure_2_3.gif), consider printing it.

• The commands shown in the figure can be issued from the Command Window on Windows or the operating system prompt on Linux/UNIX. Chapter 4, Using the DB2 Tools, describes equivalent methods to perform these commands from the DB2 graphical tools.

• Each arrow points to a set of three commands. The first command in each set (in blue if you printed the figure using a color printer) inquires about the contents of a configuration file, the second command (in black) indicates the syntax to modify these contents, and the third command (in purple) illustrates how to use the command.

• The numbers in parentheses in Figure 2.3 match the superscripts in the headings in the following subsections.

Figure 2.3. The DB2 environment

2.3.1. An Instance⁽¹⁾

In DB2, an instance provides an independent environment where databases can be created and applications can be run against them. Because of these independent environments, databases in separate instances can have the same name. For example, in Figure 2.3 the database called MYDB2 is associated to instance DB2, and another database called MYDB2 is associated to instance myinst. Instances allow users to have separate, independent environments for production, test, and development purposes.

When DB2 is installed on the Windows platform, an instance named DB2 is created by default. In the Linux and UNIX environments, if you choose to create the default instance, it is called db2inst1.

To create an instance explicitly, use:

 db2icrt instance_name 

To drop an instance, use:

 db2idrop instance_name 

To start the current instance, use:

 db2start 

To stop the current instance, use:

 db2stop 

When an instance is created on Linux and UNIX, logical links to the DB2 executable code are generated. For example, if the machine in Figure 2.3 was a Linux/UNIX machine and the instances DB2 and myinst were created, both of them would be linked to the same DB2 code. A logical link works as an alias or pointer to another program. In Windows, there is a shared install path, and all instances access the same libraries and executables.

2.3.2. The Database Administration Server

The Database Administration Server (DAS) is a daemon or process running on the database server that allows for remote graphical administration from remote clients using the Control Center. If you don't need to administer your DB2 server using a graphical interface from a remote client, you don't need to start the DAS. There can only be one DAS per server machine regardless of the number of instances on the machine. Note that the DAS needs to be running at the database server you are planning to administer remotely, not at the DB2 client.

To start the DAS, use the command:

 db2admin start 

To stop the DAS, use the command:

 db2admin stop 

2.3.3. Configuration Files and the DB2 Profile Registries⁽²⁾

Like many other RDBMSs, DB2 uses different mechanisms to influence the behavior of the database management system. These include:

• Environment variables

• DB2 profile registry variables

• Configuration parameters

2.3.3.1 Environment Variables

Environment variables are defined at the operating system level. On Windows you can create a new entry for a variable or edit the value of an existing one by choosing Control Panel > System > Advanced Tab > Environment Variables. On Linux and UNIX you can normally add a line to execute the script db2profile (Bourne or Korn shell) or db2cshrc (C shell) (provided after DB2 installation), to the instance owner's .login or .profile initialization files.

The DB2INSTANCE environment variable allows you to specify the current active instance to which all commands apply. If DB2INSTANCE is set to myinst, then issuing the command CREATE DATABASE mydb will create a database associated to instance myinst. If you wanted to create this database in instance DB2, you would first change the value of the DB2INSTANCE variable to DB2.

Using the Control Panel (Windows) or the user profile (Linux/UNIX) to set the value of an environment variable guarantees that value the next time you open a window or session. If you only want to change this value temporarily while in a given window or session, you can use the operating system set command on Windows, or export on Linux/UNIX. The command

set DB2INSTANCE=DB2 (on Windows)

or

export DB2INSTANCE=DB2 (on Linux and UNIX)

sets the value of the DB2INSTANCE environment variable to DB2. A common mistake when using the command is to leave spaces before and/or after the equal sign (=)no spaces should be entered.

To check the current setting of this variable, you can use any of these three commands:

echo %DB2INSTANCE%  (Windows only)
set DB2INSTANCE
db2 get instance

For a list of all available instances in your system, issue the following command:

 db2ilist 

2.3.3.2 The DB2 Profile Registry

The word "registry" always causes confusion when working with DB2 on Windows. The DB2 profile registry variables, or simply the DB2 registry variables, have no relation whatsoever with the Windows Registry variables. The DB2 registry variables provide a centralized location where some key variables influencing DB2's behavior reside.

NOTE

Some of the DB2 registry variables are platform-specific.

The DB2 Profile Registry is divided into four categories.

• The DB2 instance-level profile registry

• The DB2 global-level profile registry

• The DB2 instance node-level profile registry

• The DB2 instance profile registry

The first two are the most common ones. The main difference between the global-level and the instance-level profile registries, as you can tell from their names, is the level to which the variables apply. Global-level profile registry variables apply to all instances on the server. As you can see from Figure 2.3, this registry has been drawn outside of the two instance boxes. Instance-level profile registry variables apply to a specific instance. You can see separate instance-level profile registry boxes inside each of the two instances in the figure.

To view the current DB2 registry variables, issue the following command from the CLP:

 db2set -all 

You may get output like this:

 [i] DB2INSTPROF=C:\PROGRAM FILES\SQLLIB [g] DB2SYSTEM=PRODSYS 

As you may have already guessed, [i] indicates the variable has been defined at the instance level, while [g] indicates that it has been defined at the global level.

The following are a few other commands related to DB2 Registry variables.

To view all the registry variables that can be defined in DB2, use this command:

 db2set -lr 

To set the value of a specific variable (in this example, DB2INSTPROF) at the global level, use:

 db2set DB2INSTPROF="C:\PROGRAM FILES\SQLLIB" -g 

To set a variable at the instance level for instance myinst, use:

 db2set DB2INSTPROF="C:\MY FILES\SQLLIB" -i myinst 

Note that for the above commands, the same variable has been set at both levels: the global level and the instance level. When a registry variable is defined at different levels, DB2 will always choose the value at the lowest level, in this case the instance level.

For the db2set command, as with the set command discussed earlier, there are no spaces before or after the equal sign.

Some registry variables require you to stop and start the instance (db2stop/db2start) for the change to take effect. Other registry variables do not have this requirement. Refer to the DB2 UDB Administration Guide: Performance for a list of variables that have this requirement.

2.3.3.3 Configuration Parameters

Configuration parameters are defined at two different levels: the instance level and the database level. The variables at each level are different (not like DB2 registry variables, where the same variables can be defined at different levels).

At the instance level, variables are stored in the Database Manager Configuration file (dbm cfg). Changes to these variables affect all databases associated to this instance, which is why Figure 2.3 shows a Database Manager Configuration file box defined per instance and outside the databases.

To view the contents of the Database Manager Configuration file, issue the command:

 db2 get dbm cfg 

To update the value of a specific variable, use:

 db2 update dbm cfg using parameter value 

For example:

 db2 update dbm cfg using INTRA_PARALLEL YES 

With Version 8, many of the Database Manager Configuration parameters are now "configurable online," meaning the change is dynamicyou don't need to stop and start the instance. The file ConfigurationParameters.pdf included on the CD-ROM accompanying this book provides a short description of the Database Manager Configuration parameters and indicates whether they are configurable online.

At the database level, parameter values are stored in the Database Configuration file (db cfg). Changes to these parameters only affect the specific database. In Figure 2.3 you can see there is a Database Configuration file box inside each of the databases defined.

To view the contents of the Database Configuration file, issue the command:

 db2 get db cfg for dbname 

For example:

 db2 get db cfg for mydb2 

To update a value of a specific variable, use:

 db2 update db cfg for dbname using parameter value 

For example:

 db2 update db cfg for mydb2 using MINCOMMIT 3 

With Version 8 many of these parameters are configurable online, meaning that the change is dynamic, and you no longer need to disconnect all connections to the database for the change to take effect. The file ConfigurationParameters.pdf included on the book's CD-ROM provides a short description of the Database Configuration parameters and indicates whether they are configurable online.

2.3.4. Connectivity and DB2 Directories⁽³⁾

In DB2, directories are used to store connectivity information about databases and the servers on which they reside. There are four main directories, which are described in the following subsections. The corresponding commands to set up database and server connectivity are also included; however, many users find the Configuration Assistant graphical tool very convenient to set up database and server connectivity.

Chapter 6, Configuring Client and Server Connectivity, discusses all the commands and concepts described in this section in detail, including the Configuration Assistant.

2.3.4.1 System Database Directory

The system database directory (or system db directory) is the main "table of contents" that contains information about all the databases to which you can connect from your DB2 system. As you can see from Figure 2.3, the system db directory is stored at the instance level.

To list the contents of the system db directory, use the command:

 db2 list db directory 

Any entry from the output of this command containing the word Indirect indicates that the entry is for a local database, that is, a database that resides on the database server on which you are working. The entry also points to the local database directory indicated by the Database drive item (Windows) or Local database directory (Linux/UNIX).

Any entry containing the word Remote indicates that the entry is for a remote databasea database residing on a server other than the one on which you are currently working. The entry also points to the node directory entry indicated by the Node name item.

To enter information into the system database directory, use the catalog command:

 db2 catalog db dbname as alias  at node nodename 

For example:

 db2 catalog db mydb    as yourdb at node mynode 

The catalog commands are normally used only when adding information for remote databases. For local databases, a catalog entry is automatically created after creating the database with the CREATE DATABASE command.

2.3.4.2 Local Database Directory

The local database directory contains information about databases residing on the server where you are currently working. Figure 2.3 shows the local database directory overlapping the database box. This means that there will be one local database directory associated to all of the databases residing in the same location (the drive on Windows or the path on Linux/UNIX). The local database directory does not reside inside the database itself, but it does not reside at the instance level either; it is in a layer between these two. (After you read section 2.3.10, The Internal Implementation of the DB2 Environment, it will be easier to understand this concept.)

Note also from Figure 2.3 that there is no specific command used to enter information into this directory, only to retrieve it. When you create a database with the CREATE DATABASE command, an entry is added to this directory.

To list the contents of the local database directory, issue the command:

 db2 list db directory on drive / path 

where drive can be obtained from the item Database drive (Windows) or path from the item Local database directory (Linux/UNIX) in the corresponding entry of the system db directory.

2.3.4.3 Node Directory

The node directory stores all connectivity information for remote database servers. For example, if you use the TCP/IP protocol, this directory shows entries such as the host name or IP address of the server where the database to which you want to connect resides, and the port number of the associated DB2 instance.

To list the contents of the node directory, issue the command:

 db2 list node directory 

To enter information into the node directory, use:

 db2 catalog tcpip node node_name     remote hostname or IP_address     server service_name or port_number 

For example:

 db2 catalog tcpip node mynode     remote 192.168.1.100     server 60000 

You can obtain the port number of the remote instance to which you want to connect by looking at the SVCENAME parameter in the Database Manager Configuration file of that instance. If this parameter contains a string value rather than the port number, you need to look for the corresponding entry in the TCP/IP services file mapping this string to the port number.

2.3.4.4 Database Connection Services Directory

The Database Connection Services (DCS) directory contains connectivity information for host databases residing on a zSeries (z/OS or OS/390) or iSeries (OS/400) server. You need to have DB2 Connect software installed unless the server you are working on has DB2 UDB Enterprise Server Edition (ESE) installed. DB2 ESE comes with DB2 Connect support built in.

To list the contents of the DCS directory, issue the following command:

 db2 list dcs directory 

To enter information into the DCS directory, use:

 db2 catalog dcs db dbname as location_name 

For example:

 db2 catalog dcs db mydb as db1g 

2.3.5. Databases⁽⁴⁾

A database is a collection of information organized into interrelated objects like table spaces, tables, and indexes. Databases are closed and independent units associated to an instance. Because of this independence, objects in two or more databases can have the same name. For example, Figure 2.3 shows a table space called MyTablespace1 inside the database MYDB1 associated to instance DB2. Another table space with the name MyTablespace1 is also used inside the database MYDB2, which is also associated to instance DB2.

Since databases are closed units, you cannot perform queries involving tables of two different databases in a direct way. For example, a query involving Table1 in database MYDB1 and TableZ in database MYDB2 is not readily allowed. For an SQL statement to work against tables of different databases, you need to use federation (see section 2.4, Federation).

You create a database with the command CREATE DATABASE. This command automatically creates three table spaces, a buffer pool, and several configuration files, which is why this command can take a few seconds to complete.

NOTE

While CREATE DATABASE looks like an SQL statement, it is considered a DB2 CLP command.

2.3.6. Table Spaces⁽⁵⁾

Table spaces are logical objects used as a layer between logical tables and physical containers. Containers are where the data is physically stored in files, directories, or raw devices. When you create a table space, you can associate it to a specific buffer pool (database cache) and to specific containers.

Three table spacesthe catalog (SYSCATSPACE), system temporary space (TEMPSPACE1), and the default user table space (USERSPACE1)are automatically created when you create a database. The catalog and the system temporary space can be considered system structures, as they are needed for the normal operation of your database. The catalog contains metadata (data about your database objects) and must exist at all times. Some other RDBMSs call this structure a "data dictionary."

NOTE

Do not confuse the term "catalog" in this section with the catalog command mentioned earlier; they have no relationship at all.

A system temporary table space is the work area for the database manager to perform operations, like joins and overflowed sorts. There must be at least one system temporary table space in each database.

The USERSPACE1 table space is created by default, but you can delete it. To create a table in a given table space, use the CREATE TABLE statement with the IN table_space_name clause. If a table space is not specified in this statement, the table will be created in the first user-created table space. If you have not yet created a table space, the table will be created in the USERSPACE1 table space.

Figure 2.3 shows other table spaces that were explicitly created with the CREATE TABLESPACE statement (in brown in the figure on the CD-ROM). Chapter 8, The DB2 Storage Model, discusses table spaces in more detail.

2.3.7. Tables, Indexes, and Large Objects⁽⁶⁾

A table is an unordered set of data records consisting of columns and rows. An index is an ordered set of pointers associated with a table, and is used for performance purposes and to ensure uniqueness. Nontraditional relational data, such as video, audio, and scanned documents, are stored in tables as large objects (LOBs). Tables and indexes reside in table spaces. Chapter 8 describes these in more detail.

2.3.8. Logs⁽⁷⁾

Logs are used by DB2 to record every operation against a database. In case of a failure, logs are crucial to recover the database to a consistent point. See Chapter 13, Developing Backup and Recovery Solutions, for more information about logs.

2.3.9. Buffer Pools⁽⁸⁾

A buffer pool is an area in memory where all index and data pages other than LOBs are processed. DB2 retrieves LOBs directly from disk. Buffer pools are one of the most important objects to tune for database performance. Chapter 8, The DB2 Storage Model, discusses buffer pools in more detail.

2.3.10. The Internal Implementation of the DB2 Environment

We have already discussed DB2 registry variables, configuration files, and instances. In this section we illustrate how some of these concepts physically map to directories and files in the Windows environment. The structure is a bit different in Linux and UNIX environments, but the main ideas are the same. Figures 2.4, 2.5, and 2.6 illustrate the DB2 environment internal implementation that corresponds to Figure 2.3.

Figure 2.4. The internal implementation environment for DB2 for Windows

Figure 2.5. Expanding the DB2 instance directory

Figure 2.6. Expanding the directories containing the database data

Figure 2.4 shows the directory where DB2 was installed: H:\Program Files\IBM\SQLLIB. The SQLLIB directory contains several subdirectories and files that belong to DB2, including the binary code that makes DB2 work, and a subdirectory is created for each instance that is created on the machine. For example, in Figure 2.4 the subdirectories DB2 and MYINST correspond to the instances DB2 and myinst respectively. The DB2DAS00 subdirectory corresponds to the DAS.

At the top of the figure there is a directory H:\MYINST. This directory contains all the databases created under the H: drive for instance myinst. Similarly, the H:\DB2 directory contains all the databases created under the H: drive for instance DB2.

Figure 2.5 shows an expanded view of the H:\Program Files\IBM\SQLLIB\DB2 directory. This directory contains information about the instance DB2. The db2systm binary file contains the database manager configuration (dbm cfg). The other two files highlighted in the figure (db2nodes.cfg and db2diag.log) are discussed later in this book. For now, the description of these files in the figure is sufficient. The figure also points out the directories where the system database, Node, and DCS directories reside. Note that the Node and DCS directories don't exist if they don't have any entries.

In Figure 2.6, the H:\DB2 and H:\MYINST directories have been expanded. The subdirectories SQL00001 and SQL00002 under H:\DB2\NODE0000 correspond to the two databases created under instance DB2. To map these directory names to the actual database names, you can review the contents of the local database directory with this command:

 list db directory on h: 

Chapter 6, Configuring Client and Server Connectivity, shows sample output of this command. Note that the local database directory is stored in the subdirectory SQLDBDIR. This subdirectory is at the same level as each of the database subdirectories; therefore, when a database is dropped, this subdirectory is not dropped. Figure 2.6 shows two SQLDBDIR subdirectories, one under H:\DB2\NODE0000 and another one under H:\MYINST\NODE0000.

Knowing how the DB2 environment is internally implemented can help you understand the DB2 concepts better. For example, looking back at Figure 2.3 (that one you should have printed!), what would happen if you dropped the instance DB2? Would this mean that databases MYDB1 and MYDB2 are also dropped? The answer is no. Figure 2.4 clearly shows that the directory where the instance information resides (H:\Program Files\IBM\SQLLIB\DB2) and the directory where the data resides (H:\DB2) are totally different. When an instance is dropped, only the subdirectory created for that instance is dropped.

Similarly, let's say you uninstall DB2 at a given time, and later you reinstall it on the same drive. After reinstallation, can you access the "old" databases created before you uninstalled DB2 the first time? The answer is yes. When you uninstalled DB2, you removed the SQLLIB directory, therefore the DB2 binary code as well as the instance subdirectories were removed, but the databases were left untouched. When you reinstall DB2, a new SQLLIB directory is created with a new default DB2 instance; no other instance is created. The new DB2 instance will have a new empty system database directory (db2systm). So even though the directories containing the database data were left intact, you need to explicitly put the information in the DB2 system database directory for DB2 to recognize the existence of these databases. For example, if you would like to access the MYDB1 database of the DB2 instance, you need to issue this command to add an entry to the system database directory:

 catalog db mydb1 on h: 

If the database you want to access is MYDB2 that was in the myinst instance, you would first need to create this instance, switch to the instance, and then issue the catalog command as shown below.

 db2icrt myinst set DB2INSTANCE=myinst catalog db mydb2 on h: 

It is a good practice to back up the contents of all your configuration files as shown below.

 db2 get dbm cfg > dbmcfg.bk db2set -all > db2set.bk db2 list db directory > systemdbdir.bk db2 list node directory > nodedir.bk db2 list dcs directory > dcsdir.bk 

Notice that all of these commands redirect the output to a text file with a .bk extension.

CAUTION

The purpose of this section is to help you understand the DB2 environment by describing its internal implementation. We strongly suggest that you do not tamper with the files and directories discussed in this section. You should only modify the files using the commands described in earlier sections.