DB2 UDB s Directory Files

DB2 UDB's Directory Files

Earlier we saw that when a database is created, that database is cataloged in the system and local database directory. So what are the system and local database directories and why must a database be cataloged in them? If you recall, when we examined the CREATE DATABASE command, we saw that a database can physically reside anywhere on a system. Because of this, each DB2 Database Manager instance must know where databases that fall under its control physically reside, as well how to establish connections to those databases on behalf of users and/or applications. To keep track of this information, DB2 UDB uses a special set of files that are known as directory files ( or directories) . Four types of directories exist:

• System database directory

• Local database directory

• Node directory

• Database Connection Services (DCS) directory

The System Database Directory

The system database directory resides in a file named sqldbdir that is created automatically when the first database for an instance is created. Information about the new database is then recorded in the system database directory, and as additional databases are cataloged, information about those databases is recorded as well. (Databases are implicitly cataloged when they are created; databases can be explicitly cataloged using the Control Center or the CATALOG DATABASE command.) Each entry recorded in the system database directory contains the following information:

• The database name provided when the database was created (or explicitly cataloged).

• The alias assigned to the database (which is the same as the database name, if no alias has been specified).

• Descriptive information about the database (if that information has been provided).

• The location of the local database directory file that contains additional information about the database.

• The database entry type, which tells whether or not the database is an indirect database (which means that it resides on the same workstation as the system database directory file).

• Other system information, including the code page the database was created under.

The contents of the system database directory or a local database directory file can be viewed by executing the LIST DATABASE DIRECTORY command. The syntax for this command is:

 LIST [DATABASE  DB] DIRECTORY <ON [  Location  ]> 

where:

Location

Identifies the drive or directory where one or more databases are stored.

If no location is specified when this command is executed, the contents of the system database directory file will be displayed. On the other hand, if a location is specified when this command is executed, the contents of the local database directory file that exists for that particular location will displayed.

The Local Database Directory

Any time a DB2 UDB database is created in a new location (i.e., a drive or a directory), a local database directory file is also created at that location. Information about that database is then recorded in the local database directory, and as other databases are created in that location, information about those databases is recorded in the local database directory as well. Thus, while only one system database directory exists for a particular instance, several local database directories can exist, depending upon how databases have been distributed across the storage available.

Each entry recorded in a local database directory contains the following information:

• The database name provided when the database was created.

• The alias assigned to the database (which is the same as the database name, if no alias has been specified).

• Descriptive information about the database (if that information has been provided).

• The name of the root directory of the hierarchical tree used to store information in the database.

• Other system information, including the code page the database was created under.

As mentioned earlier, the contents of a local database directory file can be viewed by executing the LIST DATABASE DIRECTORY command.

The Node Directory

Unlike the system database directory and the local database directory, which are used to keep track of what databases exist and where they are stored, the node directory contains information regarding how and where remote systems or instances can be found. A node directory file is created on each client workstation the first time a remote server or instance is cataloged. As other remote instances/servers are cataloged, information about those instances/servers is recorded in the node directory as well. Entries in the node directory are then used in conjunction with entries in the system database directory to make connections and instance attachments to DB2 UDB databases stored on remote servers.

Each entry in the node directory contains, among other things, information on the type of communication protocol to be used to communicate between the client workstation and the remote database server. DB2 UDB supports the following communications protocols:

• Named pipe

• NetBIOS

• Transmission Control Protocol/Internet Protocol (TCP/IP) (which is used today in an overwhelming majority of cases)

• Advanced Peer-to-Peer Networking (APPN)

• Advanced Program-to-Program Communications (APPC)

• Advanced Program-to-Program Communications/Logical Unit (APPCLU)

The contents of the node directory file can be viewed by executing the LIST NODE DIRECTORY command. The syntax for this command is:

 LIST <ADMIN> NODE DIRECTORY <SHOW DETAIL> 

(If the ADMIN option is specified when this command is executed, information about administration servers will be displayed.)

The Database Connection Services (DCS) Directory

In Chapter 2, we saw that DB2 Connect is an add-on product used to provide connections to DRDA Application Servers, such as:

• DB2 for OS/390 or z/OS databases on System/370 and System/390 architecture host computers.

• DB2 for VM and VSE databases on System/370 and System/390 architecture host computers.

• iSeries databases on Application System/400 (AS/400) and iSeries computers.

Because the information needed to connect to DRDA host databases is different from the information used to connect to LAN-based databases, information about remote host or iSeries databases is kept in a special directory known as the Database Connection Services (DCS) directory. Whenever an entry is found in the DCS directory that has a database name that corresponds to the name of a database stored in the system database directory, the specified Application Requester (which in most cases is DB2 Connect) will forward all SQL requests made to the remote DRDA server where the database resides.

The contents of the DCS directory file can be viewed by executing the LIST DCS DIRECTORY command. The syntax for this command is:

 LIST DCS DIRECTORY 

It is important to note that the DCS directory only exists if the DB2 Connect product has been installed.