DB2 s Directory Files | DB2 9 Fundamentals: Certification Study Guide

DB2's Directory Files

Earlier we saw that, when a database is created, that database is cataloged in the system and local database directories. 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 as how to establish connections to those databases on behalf of users and applications. To keep track of this information, DB2 uses a special set of files 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 name assigned to the database 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 was specified when the database was created/cataloged)
Descriptive information about the database (if that information is available)
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 under which the database was created

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, the contents of the local database directory file that exists at that particular location will be displayed.

The Local Database Directory

Any time a DB2 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 name assigned to the database 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 was specified when the database was created/cataloged)
Descriptive information about the database (if that information is available)
The name of the root directory of the hierarchical tree used to store information about the database
Other system information, including the code page under which the database was created

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 that identifies 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 databases stored on remote servers.

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

Named pipe
Transmission Control Protocol/Internet Protocol (TCP/IP) (which is used today in an overwhelming majority of cases)
Lightweight Directory Access Protocol (LDAP)

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

Using an add-on product called DB2 Connect, it is possible for DB2 for Linux, UNIX, and Windows clients to establish a connection to a DRDA Application Server, 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. If an entry in the DCS directory 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) can forward SQL requests to the database that resides on a remote DRDA server.

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 exists only if the DB2 Connect product has been installed.

Cataloging and Uncataloging a DB2 Database

Because a database is implicitly cataloged as soon as it is created, most users never have to concern themselves with the cataloging process. However, if you need to catalog a previously uncataloged database, if you want to set up an alternate name for an existing database, or if you need to access a database stored on a remote server, you will need to become familiar with the tools that can be used to catalog DB2 databases. Fortunately, cataloging a database is a relatively straightforward process and can be done by using the Control Center, by using the Configuration Assistant, or by executing the CATALOG DATABASE command.

Earlier, we saw that by highlighting the Databases object shown in the objects pane of the Control Center and right-clicking the mouse button, it is possible to bring up a menu that contains a list of options available for database objects. The dialog used to catalog databases (the Add Database dialog) is invoked by selecting "Add… from this menu. Figure 4-5 shows the Control Center menu items that must be selected in order to activate the Add Database dialog; Figure 4-6 shows what the Add Database dialog looks like when it is first activated.

image from book
Figure 4-5: Invoking the Add Database dialog from the Control Center.

image from book
Figure 4-6: The Add Database dialog.

You can also catalog a database by executing the CATALOG DATABASE command. The syntax for this command is:

 CATALOG [DATABASE | DB] [DatabaseName] <AS [Alias]> <ON [Path] | AT NODE [NodeName]> <AUTHENTICATION [AuthenticationType]> <WITH "[Description]">

where:

DatabaseName	Identifies the name that has been assigned to the database to be cataloged.
Alias	Identifies the alias that is to be assigned to the database when it is cataloged.
Path	Identifies the location (drive and/or directory) where the directory hierarchy and files associated with the database to be cataloged are physically stored.
NodeName	Identifies the node where the database to be cataloged resides. The node name specified should match an entry in the node directory file (i.e., should correspond to a node that has already been cataloged).
AuthenticationType	Identifies where and how authentication is to take place when a user attempts to access the database. The following values are valid for this parameter: SERVER, CLIENT, SERVER_ENCRYPT, KERBEROS TARGET PRINCIPAL [PrincipalName] (where PrincipalName is the fully qualified Kerberos principal name for the target server), DATA_ENCRYPT, and GSSPLUGIN.
Description	A comment used to describe the database entry that will be made in the database directory for the database to be cataloged. The description must be enclosed by double quotation marks.

Thus, if you wanted to catalog a database that physically resides in the directory /home/db2data and has been given the name TEST_DB, you could do so by executing a CATALOG DATABASE command that looks something like this:

 CATALOG DATABASE test_db AS test ON /home/db2data AUTHENTICATION SERVER

Since a database must be cataloged before a user or application can connect to it, you're probably wondering why you would ever want to uncatalog a database. Suppose you are running an older version of DB2, and when you upgrade, you decide to uninstall the old DB2 software completely before installing the latest release. To prevent this software upgrade from having an impact on existing databases, you could simply uncatalog them before you uninstall the old version of DB2, then catalog them again after the new DB2 software has been installed. (Migration may or may not be necessary.)

Just as there are multiple ways to catalog a DB2 database, there are multiple ways to uncatalog one: by using the Control Center, by using the Configuration Assistant, or by executing the UNCATALOG DATABASE command. By highlighting the object shown in the objects pane of the Control Center that corresponds to the database to be uncataloged and right-clicking the mouse button, you can display a menu that contains a list of options that are available for that particular database. If you then select the "Remove" item from this menu, you will be presented with a Confirmation dialog, where you will be asked to confirm your decision to uncatalog the database (the name of the database you are about to uncatalog will be displayed in this dialog in an effort to prevent you from accidentally uncataloging the wrong one). Once you confirm that the database specified is to be uncataloged, its entry is removed from both the system and the local database directory; however, the database itself is not destroyed, nor are its table space storage containers made available for other databases to use.

A database can also be uncataloged by executing the UNCATALOG DATABASE command. The syntax for this command is:

 UNCATALOG [DATABASE | DB] [DatabaseAlias]

where:

DatabaseAlias

Identifies the alias assigned to the database to be uncataloged.

So, if you wanted to uncatalog a database that has the name and alias TEST_DB, you could do so by executing an UNCATALOG DATABASE command that looks like this:

 UNCATALOG DATABASE test_db

Cataloging and Uncataloging a Node

The process used to catalog nodes (servers) is significantly different from the process used to catalog databases. Instead of being explicitly cataloged as needed, nodes are usually implicitly cataloged whenever a remote database is cataloged via the Configuration Assistant. However, if you desire to explicitly catalog a particular node (server), you can do so by executing the CATALOG…NODE command that corresponds to the communications protocol that will be used to access the server being cataloged. Several forms of the CATALOG…NODE command are available, including:

CATALOG LOCAL NODE
CATALOG LDAP NODE
CATALOG NAMED PIPE NODE
CATALOG TCPIP NODE

The syntax for all of these commands is very similar, the major difference being that many of the options available with each are specific to the communications protocol the command has been tailored for. Because TCP/IP is probably the most common communications protocol in use today, let's take a look at the syntax for that form of the CATALOG…NODE command.

The syntax for the CATALOG TCPIP NODE command is:

 CATALOG <ADMIN> [TCPIP | TCPIP4 | TCPIP6] NODE [NodeName] REMOTE [IPAddress | HostName] SERVER [ServiceName | PortNumber] <SECURITY SOCKS> <REMOTE INSTANCE [InstanceName]> <SYSTEM [SystemName]> <OSTYPE [SystemType]> <WITH "[Description]">

where:

NodeName	Identifies the alias to be assigned to the node to be cataloged. This is an arbitrary name created on the user's workstation and used to identify the node.
IPAddress	Identifies the IP address of the server where the remote database you are trying to communicate with resides.
HostName	Identifies the host name, as it is known to the TCP/IP network. (This is the name of the server where the remote database you are trying to communicate with resides.)
ServiceName	Identifies the service name that the DB2 Database Manager instance on the server uses to communicate with.
PortNumber	Identifies the port number that the DB2 Database Manager instance on the server uses to communicate with.
InstanceName	Identifies the name of the server instance to which an attachment is to be made.
SystemName	Identifies the DB2 system name that is used to identify the server workstation.
SystemType	Identifies the type of operating system being used on the server workstation. The following values are valid for this parameter: AIX, WIN, HPUX, SUN, OS390, OS400, VM, VSE, and LINUX.
Description	A comment used to describe the node entry that will be made in the node directory for the node being cataloged. The description must be enclosed by double quotation marks.

Thus, if you wanted to catalog a node for an AS/400 server workstation named DB2HOST that has a DB2 instance that listens on port 5000 and assign it the alias RMT_SERVER, you could do so by executing a CATALOG TCPIP NODE command that looks something like this:

 CATALOG TCPIP NODE rmt_server REMOTE db2host SERVER 5000 OSTYPE OS400 WITH "A remote TCP/IP node"

Regardless of how a node was cataloged, it can be uncataloged at any time by executing the UNCATALOG NODE command. The syntax for this command is:

 UNCATALOG NODE [NodeName]

where:

NodeName

Identifies the alias assigned to the node to be uncataloged.

So if you wanted to uncatalog the node that was cataloged in the previous example, you could do so by executing an UNCATALOG NODE command that looks like this:

 UNCATALOG NODE rmt_server

Cataloging and Uncataloging a DCS Database

Aside from the fact that neither the Control Center nor the Configuration Assistant can be used, the process for cataloging a Database Connection Services (DCS) database is very similar to that used to catalog a regular DB2 database. A DCS database is cataloged by executing the CATALOG DCS DATABASE command. The syntax for this command is:

 CATALOG DCS [DATABASE | DB] [Alias] <AS [TargetName]> <AR [LibraryName]> <PARMS "[ParameterString]"> <WITH "[Description]">

where:

Alias	Identifies the alias of the target database that is to be cataloged. This name should match an entry in the system database directory associated with the remote node.
TargetName	Identifies the name of the target host or iSeries database to be cataloged.
LibraryName	Identifies the name of the Application Requester library that is to be loaded and used to access the remote database listed in the DCS` directory.
ParameterString	Identifies a parameter string to be passed to the Application Requestor when it is invoked. The parameter string must be enclosed by double quotation marks.
Description	A comment used to describe the database entry that will be made in the DCS directory for the database to be cataloged. The description must be enclosed by double quotation marks.

So, if you wanted to catalog a DCS database that has the alias TEST_DB and is a DB2 for z/OS database, you could do so by executing a CATALOG DCS DATABASE command that looks something like this:

 CATALOG DCS DATABASE test_db AS dsn_db WITH "DB2 z/OS database"

Keep in mind that an entry for the database TEST_DB would also have to exist in the system database directory before the entry in the DCS database directory could be used to connect to the z/OS database.

Entries in the DCS database directory can be removed by executing the UNCATALOG DCS DATABASE command. The syntax for this command is:

 UNCATALOG [DCS DATABASE | DB] [DatabaseAlias]

where:

DatabaseAlias

Identifies the alias assigned to the DCS database to be uncataloged.

Thus, if you wanted to uncatalog the DCS database cataloged in the previous example, you could do so by executing an UNCATALOG DCS DATABASE command that looks like this:

 UNCATALOG DCS DATABASE test_db