© 2003 International Business Machines Corporation. All rights reserved.

Introduction

If you are new to IBM® DB2® Universal Database™ (UDB) for Linux®, UNIX® and Windows®, or are preparing to take the certification exams, this article may save you some time trying to grasp the main concepts about the DB2 environment.

It is commonly said that a picture is worth a thousand words. Though the DB2 Certification Guide does a great job preparing you for the exams, it does not provide you with a graphical overview of DB2. Read this article before reading the certification guide, and I believe you will shorten your preparation time considerably.

In this article, I use DB2 to refer to DB2 Universal Database Version 8.1 for UNIX, Linux and Windows. I suggest you print this article and the larger figure that can be downloaded (using a color printer!) rather than reading it online, as I will reference the following figure all along. Concepts will be explained briefly at first, and then in the last section we will tie them all up using a case study .

In the figure , we use blue for the commands used to inquiry about the contents of a configuration file, black to indicate the syntax to modify these contents, and purple to show an example about how to use the command.

In DB2, an instance provides an independent environment where database objects can be created and applications can be run against them. Because of these independent environments, objects of two or more separate instances can have the same name. In Figure 1 , there is a database called 'mydb2' associated to instance 'DB2', and another database with the same name associated to instance 'MyInst'. Instances allow users to have different environments for production, test and development purposes, for example.

Below are some commands related to instances:

• Create an instance explicitly by issuing the following command from the Command Line processor (CLP):

  db2icrt <instance name>

  
  

  When DB2 is first installed in the Windows platform, an instance called 'DB2' is created by default. In the UNIX environment, the default instance name that is created should you choose to do so is called 'db2inst1'.

• To drop an instance, use the command:

  db2idrop <instance name>

  
  
• To start an instance, use the command:

  db2start

  
  
• To stop an instance, use the command:

  db2stop

  
  

When an instance is created, links to the DB2 code are generated. For the example in Figure 1 , instance 'DB2' and instance 'MyInst' have been created, and both of them are linked to the same DB2 code.

Before Version 8, there were two types of instances: the 'DB2' type, and the 'DAS' type. The DAS-type of instance has been eliminated with V8; its functionality has been integrated into the product as a server process.

Like many other Relational Database Management Systems (RDBMSs), DB2 uses different mechanisms to manage, monitor and control the behavior of the DB2 system. These include:

Environment variables

Environment variables are variables defined at the operating system level. In the Windows 2000 ® platform, for example, 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 . In UNIX, you would normally add the script db2profile (Bourne or Korn shell) or db2cshrc (C shell), provided after DB2 installation, to the .login or .profile UNIX initialization files. The db2profile/db2cshrc files contain the 'export' UNIX commands that will ensure a UNIX environment variable is passed every time a shell is invoked.

The most frequently used DB2 Environment variable is the 'DB2INSTANCE' variable. This environment variable allows you to specify the current active instance to which all commands would apply to. For the example, if DB2INSTANCE is set to 'MyInst', then issuing the command ' create database mydb ' will create a database associated with instance 'MyInst'. If you wanted to create this database on instance 'DB2' instead, then you would first have to change the value of the DB2INSTANCE variable to 'DB2'.

Using the Control Panel (in Windows) / db2profile (in UNIX) to set the value of an environment variable would guarantee that value the next time you open a window/session; however, if you would like to change this value temporarily while at a given window/session, you can use the operating system ' set ' command. For example, the following command:

set DB2INSTANCE=DB2

would set the value of environment variable DB2INSTANCE to 'DB2'. A common mistake when using the set command is to leave spaces before and/or after the equal sign (=). Absolutely no spaces should be specified!

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

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

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

db2ilist

DB2 profile registry

The use of the word 'Registry' always causes confusion when working with DB2 on the Windows platform. The DB2 profile registry variables have no relation whatsoever with Windows Registry variables. In the past, changes to some Environment Variables required users to reboot the machine. Had all DB2 variables been defined as environment variables, it would have been cumbersome to make changes to the variable values. Thus, it was decided at that time to group most DB2 variables in a specific DB2 registry where there would not be a need to reboot the machine.

The DB2 Profile Registry is divided into four categories; however, these two are the most common ones:

• DB2 Global-Level Profile Registry
• DB2 Instance-Level Profile Registry

The main difference between these two, as you can tell from their names, is the level to which the variables apply. Global-Level Profile Registry variables' value is applicable to all instances. Thus, as you can see from the figure, this registry has been drawn outside of the two instance boxes. Instance-Level Profile Registry variables' value is applicable to a specific instance. Thus, you can see separate 'Instance-Level Profile Registry' boxes inside each of the two instances in the figure .

To view the current Registry variables that are set, issue the following command from the CLP:

db2set -all

You may get an output like this:

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

As you may have already guessed, [i] indicates this variable has been defined at the instance level, while [g] indicates it has been defined at the global level. Below are some commands related to 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 example, the same variable has been set at both levels, the instance level and the global level. When a registry variable is defined at different levels, DB2 will always choose the value at the lowest level; in this case, it would be the instance level.

For the db2set command, as with the set command in the previous section, there should not be spaces specified before and/or after the equal sign (=).

Some registry variables require you to stop and start the instance ( db2stop/db2start ) in order for the change to take effect. Some other registry variables don't have this requirement. To be on the safe side, it is recommended to always stop and start your instance after making a change to a registry variable.

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 registry variables where the same variables could be defined at different levels).

At the instance level, variables are stored in the Database Manager Configuration file (dbm cfg); changes to these variables would affect ALL databases associated to this instance, which is why the picture shows a dbm cfg box defined per instance and outside the databases. Below are some commands related to configuration parameters:

• To view the contents of the dbm cfg, from the CLP, issue the command:

  db2 get dbm cfg

  
  
• To update a value of a specific variable, issue the command:

  db2 update dbm cfg using <parameter> <value>

  
  

  For example:

  db2 update dbm cfg using INTRA_PARALLEL YES

  
  

Prior to V8, changes to ALL dbm cfg variables required you to stop and start the instance ( db2stop/db2start ). With V8, about 40% the parameters are now 'configurable online' parameters; that is, there is no longer a need to stop and start the instance. Please refer to the DB2 Version 8 Administration Guide for more details.

At the database level, variables are stored in the Database Configuration file (db cfg); changes to these variables would affect the specific database. From Figure 1 , you can see there is a db cfg box inside each of the databases defined.

• To view the contents of the db cfg, from the CLP, issue the command:

  db2 get db cfg for <dbname>

  
  

  For example:

  db2 get db cfg for mydb1

  
  
• To update a value of a specific variable, issue the command:

  db2 update db cfg for <dbname> using <parameter> <value>

  
  

  For example:

  db2 update db cfg for mydb1 using MINCOMMIT 3

  
  

Prior to V8, changes to ALL db cfg variables required you to disconnect all connections from the database; then, at the first new connection, the changes would take effect. With V8, about 50% of the parameters can be configurable online; that is, there is no need to disconnect all connections to the database for the change to take effect. Please refer to the DB2 Version 8 Administration Guide for more details.

In DB2, directories are binary files storing information about the databases and their connectivity. There are four main directories, which are described below. The corresponding commands to set up connectivity are also included; however, many users find the Client Configuration Assistant GUI Tool very convenient to set up connectivity.

System database directory (or system db directory)

The system database directory is the main 'table of contents' that contains information about all the databases you can connect to from your DB2 system. As you can see from Figure 1 , the system db directory is stored at the instance level; thus, you should consider backing up its contents if you are planning to drop an instance.

To list the contents of the system db directory, from the CLP, issue the command:

db2 list db directory

Any entry from the output of this command containing the word 'indirect' means that the entry is for a local database; that is, a database residing in the machine where you are currently working. The entry will also point to the local database directory indicated by the 'Database drive' item (In Windows) or 'Local database directory' (In UNIX).

Any entry containing the word 'Remote' means that the entry is for a remote database; that is, a database residing in a machine other than the one you are currently working on. The entry will also point to the node directory entry indicated by the 'Node name' item.

To enter information into the System db directory, you need to use the catalog command:

db2 catalog db <db_name> as <alias> at node <nodename>

For example:

db2 catalog db mydb as yourdb at node mynode

The node name is a pointer to an entry in the node directory. This entry must exist prior to issuing this command.

The catalog command would normally be used only when adding information to the System db directory for remote databases. For local databases, a catalog entry is automatically created after creating the database with the create database command.

Local database directory (or local db directory)

The local database directory directory contains information about local databases; that is, the databases residing in the machine where you are currently working. As you can see from Figure 1 , a local database directory resides inside the database structure. Note also from the figure that there is no specific command used to enter information into this directory. When you create a database with the create database command, an entry will be added to this directory.

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

db2 list db directory on <path>

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

Node directory

The node directory is used to store all connectivity information for remote databases. With version 8, only the TCPIP protocol is supported; thus, most entries in this directory will show TCPIP information like the host name or IP address of the machine where the database you want to connect to resides, and also the port number of the associated DB2 instance. Here are some commands related to the node directory:

• To list the contents of the node directory, from the CLP issue the command:

  db2 list node directory

  
  
  
  
• To enter information into the node directory, from the CLP issue the catalog command:

  db2 catalog tcpip node <node_name> remote <hostname or IP_address> server <port_name or port_number>

  
  

  For example.:
  

  db2 catalog tcpip node mynode remote 9.26.138.35 server 60000

  
  

The port number of the remote instance you want to connect to can be obtained by looking at the svcename parameter in the dbm cfg of that instance. This value would normally correspond to an entry in the TCP/IP services file.

DCS directory

The DCS directory contains connectivity information for host databases normally residing on a zSeries ^TM (S/390 ® ) or iSeries ^TM (AS/400 ® ) machine. You need to have the DB2 Connect software installed. Here are some commands:

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

  db2 list dcs directory

  
  
  
  
• To enter information into the DCS directory, from the CLP issue the catalog command:

  db2 catalog dcs db as <location name>

  
  

  For example:
  

  db2 catalog dcs db as db1g

  
  

Please refer to DB2 Version 8 Connectivity Cheat Sheet for a summary of the commands described in this section.

Databases are closed and independent units associated to an instance. Because of this independence, objects of two or more databases can have the same name. For example, Figure 1 shows a table space called 'MyTablespace1' inside database 'MYDB1' associated to instance 'DB2'. Another table space with the same name is used for a table space inside database 'MYDB2' also associated to instance 'DB2'.

As databases are closed units, you cannot perform queries involving tables of two different databases (unless you use Information Integrator (II) / Relational Connect, which are out of the scope of this document). For example, a query involving 'Table1' in database 'MYDB1' and 'TableZ' in database 'MYDB2' is not allowed.

A database is created with the command create database . Note that this is considered a command, and not a SQL statement.

When you create a database, table spaces, logs, a buffer pool, and configuration files are automatically created, which is why this command takes a few seconds to complete.

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

The Catalog (SYSCATSPACE), system temporary space (TEMPSPACE1) and the user space (USERSPACE1) are all table spaces, and are automatically created when the database is created. 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 meta data (data about your data). Some other RDBMs call this structure a 'data dictionary'. 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 sorts. There must be at least one system temporary table space.

The USERSPACE1 table space is created by default, but it can be dropped. It is the default location for storing user tables.

Figure 1 shows in the peachy-brown color that is the same as this section header other table spaces that were explicitly created with the create tablespace command. Please refer to the DB2 Version 8 SQL Reference for more detail.

A table is an unordered set of data records consisting of columns and rows. Indexes are ordered sets of pointers associated with a table, and are used for performance purposes and to ensure uniqueness. Video, audio, scanned documents, and so on can be stored in databases as large objects (LOBs). Tables, indexes and LOBs reside in table spaces.

Logs are files used for recovery purposes. Logs record every operation against a database. In case of a failure, logs are crucial to restore a database to a consistent point.

A buffer pool is an area in memory where all index and data pages (other than LOBs) go through in ordered to be processed. It is basically a cache used by the database manager. Buffer pools are the most important objects to tune in database performance problems.

Back to top

Case study

Now that you are familiar with the DB2 environment, let's review all the concepts in this article with a simple case study.

Important: Do not follow the steps in this case study. This a case study of accidentally dropping an instance - probably not something you want to do.

The DB2 database administrator (DBA) has left your company due to emergency personal reasons, and your manager has asked you to be in charge of all DB2 systems. This is your first day as the new DB2 DBA, and you want to get familiar with the system. You log on to a development machine (Windows 2000) where DB2 has been installed, and open a CLP window. Here are the steps you would take:

1. First, you want to know how many instances you have in this machine:

   db2ilist

   
   
2. Then, you want to know which of these instances is the current active one:

   db2 get instance

   
   

   With the db2ilist command, you found out there were two instances defined on this machine, instance 'DB2' and instance 'MyInst'. With the db2 get instance command, you found out that the 'DB2' instance is the current active instance.

3. You would now like to list the databases in the 'MyInst' instance. Since this one is not the current active instance, you first switch to this instance temporarily in the current CLP window:

   set DB2INSTANCE=MyInst

   
   
4. You issue again a db2 get instance to check MyInst is now the current instance, and then, to list the databases defined on this instance you issue:

   db2 list db directory

   
   

   This command shows you only have one database (mydb2) in this instance. You want to try creating a new database, which you will drop afterwards, so you execute:

   db2 create database temporal

   
   

   The creation of the database takes some time because several objects are created by default inside the database. Issuing another db2 list db directory will show you now two databases: 'mydb2' and 'temporal'.

5. You connect to the 'mydb2' database (db2 connect to mydb2), and check how many tables you have in this database ( db2 list tables ). You also check how many table spaces are defined ( db2 list tablespaces ).

Lunch time is approaching, so you decide you want to put things back the way they were. First, you decide to drop the temporal database. You are in the process of typing the command when a three-second black out causes your machine to reboot automatically. You are not concerned about DB2 databases status, because you know DB2 crash recovery will make sure things are consistent; so you open a new CLP window after reboot, and proceed to drop the database.

db2idrop MyInst

OOPS!... You made a mistake, you used the wrong command and dropped the entire MyInst instance instead of just dropping the database 'temporal'. Did you lose database 'mydb2' and 'temporal', which were associated to instance 'MyInst'?

Reviewing Figure 1 , you may think you did; however, this is not the case. When you drop an instance you are not going to drop the databases associated to it. An instance provides you an environment that links the DB2 code to use against your user data. Databases are 'associated' to an instance. When you install a new version of DB2, you would normally 'upgrade' the instance to the new code version, however; your databases remain intact.

Now that you have calmed down, knowing that you have not lost your databases, you start thinking why the db2idrop command worked. Normally, this command would give you a warning if your instance was active. Then you realized that after the machine rebooted, the instance 'MyInst' had not started automatically while the 'DB2' instance had been configured to start automatically after booting up the machine. Also, when you opened a new CLP window, the value of the db2instance environment variable was set to 'DB2', not 'MyInst', given that 'DB2' is the value specified for this environment variable in the Control Panel for your machine.

Now that you have figured this out, you start working on putting things the way they were:

1. First, you need to create the instance MyInst again:

   db2icrt MyInst

   
   
2. Now, you switch to this instance (set DB2INSTANCE=MyInst). If you issue a list db directory command, you will get an error indicating the system db directory does not exist. If you look at Figure 1 , you realize that when you dropped the instance the first time, all the instance-wide configuration files and directories were also dropped. Thus, the instance-level profile registry, the dbm cfg, the system db directory, the node directory, and dcs directory are all gone. When creating an instance, a dbm cfg and instance level registry is created with default values. Fortunately, the previous DBA stored backups of the contents of all of these files. He issued commands like this:

   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

   
   
3. Using the appropriate commands, you will have to manually restore the values based on the contents of these files. For the case of the 'temporal' database you created, this is a local database that needs to be cataloged again. Issuing the command ' db2 catalog db temporal on <drive/path> ' will be enough to create an entry in the system db directory, which will point to the existing local db directory since you created this database using default values. The local db directory is part of the database; thus, it was not dropped when you dropped the instance.
4. Now that things have been restored, you are now able to drop the database temporal:

   db2 drop database temporal

   
   
5. Set the value of DB2INSTANCE back to 'DB2' ( set DB2INSTANCE=DB2 ).

And you're done!

Back to top

Summary

This article explains in a colorful and graphical manner a whole picture of the DB2 V8 environment. We discussed the concept of instances, configuration files, registry variables, directories, databases, and so on. The figure makes it easy to understand where all of these objects reside. The case study presented was used as a review of all the concepts covered in this article. It includes the commands executed using the Command Line Processor (CLP). GUI Tools were not discussed in this article. Though it is easier to manage the DB2 environment using GUI Tools, for learning purposes, it is better to use the CLP. Once you have a solid foundation of the DB2 environment using the CLP, gaining skills using the GUI Tools should be a straightforward task.

Resources

• Download the larger version of figure (pdf 65 KB)
  
  
• Download the entire article (pdf 147 KB)
  
  
• Understanding DB2 - Learning Visually With Examples. This book uses the same writing style of this article to explain DB2 UDB topics, and is an excellent resource to prepare you for the DB2 UDB certification exams.
  
  
  
  

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Case study
• Summary
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers