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.
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.
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.
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.
NOTEIn 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. |
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_directoryinstance_namesqldbdir | 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.
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.
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 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.
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_directoryinstance_namesqlnodir | 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.
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.
NOTESince 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. |
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.
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
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.
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
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. |
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
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. | |
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. |
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
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 MYHOSTDB. |
3. | Determines the database name that corresponds to the database alias. (in Figure 6.9 the database name are the same). This information will later be used in step 9. |
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. | |
6. | Looks for the node directory. |
7. | Inside the node directory, DB2 looks for the entry with a node name of MYNODE2. |
8. | Determines the physical location where the database resides. In this example, the TCP/IP protocol is used, therefore DB2 looks for the fields Hostname and Service Name. In Figure 6.9, their values are mpower.myacme.com and 446 respectively. |
9. | 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. |