Learning Visually with Examples [Electronic resources] نسخه متنی

2.3. The DB2 Environment

Several items control the behavior of your database system. We first describe section 2.6, Database Partitioning Feature, we expandChapter 4, Using the DB2 Tools, describes equivalent methods to perform these commands from the DB2 graphical tools.

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, 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."

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 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.