Understanding DB2: Learning Visually with Examples (2nd Edition)

2017-07-07 02:10:07

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 database`	`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 the 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 Figure 6.2) contains extra information required when you connect to a host database server like DB2 for OS/390, z/OS and iSeries.

NOTE

In this chapter host database servers like DB2 for OS/390 and z/OS and DB2 for iSeries will only be used as database servers and not as clients. See 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 Figure 6.3 indicates that you can connect to three different databases from this system: MYHOSTDB, MYRMTDB, and MYLOCDB. Let's examine each of these database entries in detail starting from the bottom (Database 3 entry) to the top (Database 1 entry).

The relevant fields in Database 3 entry are

Database alias = MYLOCDB. This indicates the alias you need to use in the CONNECT statement. It must be a unique name within the system database directory.

Database name = MYLOCDB. This is the actual database name. For this particular entry it is the same as the alias name.

Directory entry type = Indirect. An entry type of Indirect means that the database is local; that is, it resides on the same server where you are currently working.

Database Drive = H:\MYINST2. From the previous field you know this database is local. This field tells where on the server this database is stored. Note that the example in Figure 6.3 is for a Windows system. For a Linux/UNIX system the field would be 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 (in Windows) or 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

Figure 6.4 shows MYLOCDB is the only database stored in H:\MYINST2. Note that MYLOCDB also showed up in the system database directory in Figure 6.3, since the local database directory is a subset of the system database directory. On Windows, the create database command can only specify a drive, not a path, where a database can be created; therefore, the command list db directory on H: should return the same output as list db directory on H:\MYINST2. On Linux/UNIX, a path can be specified with the create database command; therefore, when using the list db directory command, specify the full path. 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.

NOTE

Since the node directory contains the information required to connect to an instance, it is not only used by the CONNECT statement but also by the attach command as described in 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

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

DB2 follows these steps.

1.	Looks for the system database directory.
2.	Inside the system database directory, looks for the entry with a database alias of MYLOCDB.
3.	Determines the database name that corresponds to the database alias (in Figure 6.7 the database alias and name are the same).
4.	Determines if the database is local or remote by reviewing the Directory entry type field. In the figure, the entry type is Indirect, so the database is local.
5.	Since the database is local, DB2 reviews the Database drive field, which indicates the location of the local database directory. In Figure 6.7, it is H:\MYINST2.
6.	Looks for the local database directory.
7.	Inside the local database directory, DB2 looks for the entry with a database alias that matches the database name of MYLOCDB.
8.	Determines the physical location where the database resides by looking at the field 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

DB2 follows these steps:

1.	Looks for the system database directory.
2.	Inside the system database directory, looks for the entry with a database alias of MYRMTDB.
3.	Determines the database name that corresponds to the database alias. In Figure 6.8 the database name is RMTDB. This information will later be used in step 8.
4.	Determines if the database is local or remote by reviewing the Directory entry type field. In the figure, the entry type is Remote, so the database is remote.
5.	Since the database is remote, DB2 reviews the Node name field, which indicates the entry name to look for in the node directory. In the figure, the node name is MYNODE1.
6.	Looks for the node directory.
7.	Inside the node directory, looks for the entry with a node name of MYNODE1.
8.	Determines the physical location where the database resides. In this example, the TCP/IP protocol is used, so DB2 looks for the fields Hostname and 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