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

Understanding DB2® Learning Visually with Examples
By Raul F. Chong, Clara Liu, Sylvia F. Qi, Dwaine R. Snow

Publisher	: Prentice Hall PTR
Pub Date	: January 25, 2005
ISBN	: 0-13-185916-1
Pages	: 928

6.2. The DB2 Directories

This section describes the DB2 directories and how they are related. Consider the following statement used to connect to the database with the alias

sample :

CONNECT TO sample

Given only the database alias, how does DB2 know how to find the database

sample ? If

sample resides on a remote server, how does the client know how to connect to the server?

All connect information is stored in the DB2 directories. Table 6.1 lists these directories and the corresponding commands to view, insert, and delete the contents. More information about the directories and commands is available in the next sections.

Table 6.1. The Commands to View, Insert, and Delete the Contents of the DB2 Directories
Directory Name	Command to View Contents	Command to Insert Contents	Command to Delete Contents
System database	`list db directory`	`catalog db` (for remote and local databases) or `create database` (for local databases only)	`uncatalog db` (for remote and local databases) or `drop database` (for local databases only)
Local database	`list db directory on` `path/drive`	`create database` (for local databases only)	`drop database` (for local databases only)
Node	`list node directory`	Depends on the protocol. For example, for TCP/IP use: `catalog TCPIP node`	`uncatalog node`
DCS	`list dcs directory`	`catalog DCS databas` `e`	`uncatalog DCS database`

Note that you cannot update an entry you entered with the

catalog command. You have to delete the entry with the

uncatalog command first, and then insert the new updated entry with the

catalog command.

6.2.1. The DB2 Directories: An Analogy Using a Book

To understand how the DB2 directories work let's use an analogy. Above the dotted line in Figure 6.2 is the table of contents for a book called

The World . This table of contents shows that the book is divided into several parts. If you jump to any of these parts, you will see a subset of the table of contents. The Resources section presents information about other books; with that information you can find a given book in a library or bookstore or on the Internet, and once you find the book, the process repeats itself where you first review the table of contents for that book and then look at its different parts.

Figure 6.2. The DB2 directories: a book analogy

Similarly, with DB2 directories (shown below the dotted line), whenever you issue a

CONNECT TO

database statement, DB2 looks for the information in system database directory, which is equivalent to the table of contents: it shows all the databases available for you to connect to from this machine. When an entry in this directory has the type

indirect , it means the database is local (it resides on the current database server). To look for more information about this local database, you can review the local database directory, which is equivalent to the subset of the table of contents. When an entry in the system database directory is

remote , it means that the database resides in a different system than the current one. Thus, you need to review the node directory for information about how to access this remote server. This is similar to the Resources (or bibliography) section of a book, where information points to a different book with more information about a given topic.

The Database Connection Services (DCS) directory (not shown in Appendix E, Setting Up Database Connectivity for DB2 UDB for z/OS and DB2 UDB for iSeries, for the connectivity setup required for using host machines as clients.

6.2.2. The System Database Directory

As mentioned earlier, the system database directory is like a table of contents: it shows you all the databases you can connect to from your system. The system database directory is stored in a binary file with name

SQLDBDIR and is in:

DB2_install_directory \

instance_name \sqldbdir

on Windows systems

DB2_instance_home /sqllib/sqldbdir

on Linux/UNIX systems

You should not modify this file manually. To display the contents of the system database directory, use the

list db directory command, as shown in Figure 6.3.

Figure 6.3. A sample DB2 system database directory

The system database directory shown in Local database directory instead of

Database Drive .

The relevant fields in Database 2 entry that have not been described yet are

Directory entry type =
Remote . An entry type of
Remote means that the database resides on a different server than the one on which you are currently working.
Node name =
MYNODE1 . From the previous field you know this database is remote. The node name field tells the name of the entry in the node directory where you can find the information about the server that stores the database and how to access it.

The relevant field in the Database 1 entry that has not been described earlier is

Authentication = SERVER. This entry indicates that security is handled at the server system. Other options are discussed in Chapter 10, Implementing Security.

6.2.3. The Local Database Directory

The local database directory is also stored in a file called

SQLDBDIR . However, this file is different from the

SQLDBDIR file for the system database directory in that it resides on every

drive path (in Linux/UNIX) that contains a database. It contains information only for databases on that drive/path, and it is a subset of the system database directory. Use the

list db directory on

drive/path command to display the local database directory, as shown in Figure 6.4.

Figure 6.4. A sample DB2 local database directory

Chapter 8, The DB2 Storage Model, explains the

create database command in detail.

The relevant information in the entry of Figure 6.4 is:

Database directory =
SQL00001 . This is the subdirectory where the database is physically stored in your server.

6.2.4. The Node Directory

The node directory stores information about how to communicate to a remote instance where a given database resides. It is stored in a file called

SQLNODIR and is in:

DB2_install_directory \

instance_name \sqlnodir

on Windows systems

DB2_instance_home /sqllib/sqlnodir

on Linux/UNIX systems

One important field in the node directory is the communication protocol used to communicate with the server, as several other fields are displayed depending on this entry. For example, if the node directory contains a TCP/IP entry, then other fields provided are the IP address (or host name) of the server and the service name (or port number) of the instance where the database resides. Figure 6.5 shows an example of the contents of the node directory.

Figure 6.5. A sample DB2 node directory

There are two entries in Figure 6.5. We explain the first one in detail below; the second entry has a similar explanation, and thus will not be described.

Node 1 entry has these relevant fields:

Node name =
MYNODE1 . This is the name of this node entry. It was chosen arbitrarily.
Protocol =
TCPIP . This is the communication protocol that is used to communicate with the remote system.
Host name =
aries.myacme.com . This is the host name of the remote database server. Alternatively, the IP address can be provided. This entry appears because it was cataloged as a TCP/IP node. If the entry is cataloged as a node using a different protocol, other items would be displayed.
Service Name =
50000 . This is the TCP/IP port used by the instance in the remote server to listen for connections.

Chapter 5, Understanding the DB2 Environment, DB2 Instances, and Databases.

6.2.5. The Database Connection Services Directory

The DCS directory is required only when connecting to a host server like DB2 for OS/390, z/OS, and iSeries. This directory is available only when the DB2 Connect software is installed. If you are running DB2 UDB Enterprise Server Edition (ESE), DB2 Connect support is built into the DB2 database product, so the DCS directory will also be available. Figure 6.6 shows the contents of a sample DCS directory.

Figure 6.6. A sample DCS directory

[View full size image]

In Figure 6.6 the relevant fields are:

Local database name =
MYHOSTDB . This name must match the corresponding entry in the system database directory.
Target database name =
HOSTPROD . Depending on the host, this entry corresponds to the following:
- For DB2 for OS/390 and z/OS: The location name of the DB2 subsystem
- For DB2 for iSeries: The local RDB name

6.2.6. The Relationship Between the DB2 Directories

Now that you have a good understanding of the DB2 directories, let's see how all of them are related by using a few figures.

6.2.6.1 A Local Connection

Figure 6.7 illustrates the process of connecting to a local DB2 database. When a user issues the statement:

CONNECT TO mylocdb USER raul USING mypsw

Figure 6.7. The local database connection process

[View full size image]

DB2 follows these steps.

Looks for the system database directory.

Inside the system database directory, looks for the entry with a database alias of

MYLOCDB.

Determines the database name that corresponds to the database alias (in Database Directory. In Figure 6.7, it is

SQL00001.

6.2.6.2 A Remote Connection to a DB2 Server

Figure 6.8 illustrates the process of connecting to a remote DB2 database. When a user issues the statement:

CONNECT TO myrmtdb USER raulrmt USING myrmtpsw

Figure 6.8. The remote database connection process

[View full size image]

DB2 follows these steps:

Looks for the system database directory.

Inside the system database directory, looks for the entry with a database alias of

MYRMTDB .

Determines the database name that corresponds to the database alias. In Service Name. In Figure 6.8, their values are aries.myacme.com and

50000 respectively. With this information and the database name obtained in step 3, DB2 initiates the connection.

6.2.6.3 A Remote Connection to a Host DB2 Server

Figure 6.9 illustrates the process of connecting to a remote DB2 host server, which can be DB2 for z/OS, OS/390, or DB2 for iSeries. When a user issues the statement:

CONNECT TO myhostdb USER raulhost USING myhostpsw

Figure 6.9. The remote host DB2 database connection process

[View full size image]

DB2 follows these steps:

Looks for the system database directory.

Inside the system database directory, looks for the entry with a database alias of

MYHOSTDB.

Determines the database name that corresponds to the database alias. (in Service Name. In Figure 6.9, their values are mpower.myacme.com and

446 respectively.

DB2 detects that this is a host database server and thus, with the database name obtained in step 3, it accesses the DCS directory.

10.

Inside the DCS directory, DB2 looks for the entry with a local database name of

MYHOSTDB.

11.

Determines the target database name that corresponds to

MYHOSTDB . In this example it is

HOSTPROD . With this information and the connectivity information obtained in step 8, DB2 initiates the connection.



•	Index

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

6.2. The DB2 Directories

Table 6.1. The Commands to View, Insert, and Delete the Contents of the DB2 Directories

6.2.1. The DB2 Directories: An Analogy Using a Book

Figure 6.2. The DB2 directories: a book analogy

6.2.2. The System Database Directory

Figure 6.3. A sample DB2 system database directory

6.2.3. The Local Database Directory

Figure 6.4. A sample DB2 local database directory

6.2.4. The Node Directory

Figure 6.5. A sample DB2 node directory

6.2.5. The Database Connection Services Directory

Figure 6.6. A sample DCS directory

6.2.6. The Relationship Between the DB2 Directories

6.2.6.1 A Local Connection

Figure 6.7. The local database connection process

6.2.6.2 A Remote Connection to a DB2 Server

Figure 6.8. The remote database connection process

6.2.6.3 A Remote Connection to a Host DB2 Server

Figure 6.9. The remote host DB2 database connection process