content:
Introduction Case Study Caring for About the author to the evaluation of this article
subscription:
DeveloperWorks Newsletter
Raul F. CHONGIBM Toronto Labs, January 2003
Are you accustomed to learning intuitive learning? This illustration of DB2 will save some time in the main concept of learning DB2 environments.
Introduction If you are a newer, or you are preparing to participate in the certification exam for IBM® DB2® Universal DatabaseTM (UDB) for Linux, or you are preparing to participate in the certification exam, this article may make you try to master the main concepts about the DB2 environment. Save some time. Talking: A picture reaches a thousand words. Although the Certification Guide is a lot of work for you, it does not provide you with DB2 overview of the form of illustration. Please read this article before reading the certification guide, I believe this greatly shortens your preparation time. In this article, I use DB2 to refer to DB2 Universal Databases V8.1 for UNIX, Linux, and Windows. I suggest you print this article (using a color printer!) Instead of online reading, because I will always reference the following picture. The concept will be briefly explained, and then in the last section we will use case studies to string these concepts. In the figure, we use blue to represent commands for querying the contents of the configuration file, with black to represent the syntax of these content, and use purple to display examples about how to use the command. Figure 1 - DB2 V8 Environment In DB2, an instance provides an independent environment that can create database objects and run applications for these objects. Since these environments are independent, the objects of two or more separate instances can have the same name. In Figure 1, there is a database called "MyDB2", which is related to the instance "DB2"; there is a database of the same name, but it is related to the instance "MyInst". The instance allows users to have different environments for production, testing, and development purposes. Here are some commands related to example:
Explicitly create an instance by issuing the following commands from the command line processor (CLP):
DB2ICRT
DB2IDROP
DB2START To stop the instance, use this command:
After DB2STOP creates an instance, it will generate a link to the DB2 code. For the example in FIG. 1, an instance "DB2" and instance "MyInst" have been created, both of which are linked to the same paragraph DB2 code. Before V8, there are two example types: "DB2" type and "DAS" type. A DAS type is removed in V8; its function has been integrated into the product as a server process. Like many other relational database management systems (RDBMS), DB2 uses different mechanisms to manage, monitor and control the behavior of DB2 systems. These mechanisms include: environment variable environment variables are variables defined at the operating system level. For example, in the Windows 2000® platform, you can create a new item for a variable or by selecting Control Panel -> System -> Advanced Tab -> Environment Variables to edit the existing variable. In UNIX, you can usually add the scripts DB2Profile (Bourne, or Korn shell) or DB2CSHRC (C shell) installed after the DB2 is installed to .login or .profile UNIX initialization file. The DB2PROFILE / DB2CSHRC file contains the "Export" UNIX command, which ensures a UNIX environment variable when the shell is called each time. The most commonly used DB2 environment variable is the "db2instance" variable. This environment variable allows you to specify the current active instance, all commands will be applied to this instance. For this example, if DB2Instance is set to "MyInst", then "CREATE DATABASE MYDB" will create a database related to instance "MyInst". But if you want to create the database related to the instance "DB2", first you must change the value of the DB2Instance variable to "DB2". Use the control panel (in Windows) / DB2Profile (in UNIX) to set the value of the environment variable, this value will not change when you open the window / session next time; however, if you want to be in a given window / session To change this value, then in Windows you can use the "set" command of the operating system, or use the "export" command in UNIX. For example, in the Windows platform, the following command: SET DB2Instance = DB2
The value of the environment variable DB2INSTANCE will be set to "DB2". When using the set command, the error is often made of space before and after the equal sign (=). Never have spaces! To view the current settings of this variable, you can use any of the following three methods:
Echo% DB2Instance% (Windows Only)
SET DB2Instance
DB2 Get Instance
To get a list of all available instances you can do in the system, issue the following command:
DB2ILIST
DB2 Summary Registry Use DB2 on a Windows platform, the use of the word "registry" will often be confused. The DB2 Profile Registry has no relationship with the Windows registry variable. In the past, when certain environment variables were changed, users were required to reboot the machine. If all DB2 variables have been defined as an environment variable, it will be very troublesome to make changes to variable values. Therefore, at the time, it was decided to make most DB2 variables into a special DB2 registry, and the changes were made without rebooting the machine. The DB2 summary registration is divided into four categories; however, the following two is the most commonly used: Global-Level Profile Registry Registry (Global-Level Profile Registry) The main difference between the instance level profile registry ( As you can see from their name) is the level of variable applicable. The value of the global level summary registry variable is suitable for all instances. Therefore, it can be seen from the figure that the registration table is in the outside of the two example boxes. Example level profile The value of the registry variable is suitable for a particular instance. Therefore, you can see that the two instances in the figure have a separate "instance-level" box. To see the current registry variable set, issue the following command from the CLP:
DB2SET -All
You may get an output similar to the following:
[i] db2instprof = c: / program files / sqllib
[g] db2system = raulchong
As you may have already guess, [i] indicates that the variable is defined in instance level, and [G] indicates that it is defined at the global level. Here are some commands related to variables:
To see all registry variables that can be defined in DB2, use this command:
DB2SET -LR
To set a specific variable (DB2InstProf in this example) on the global level, use:
DB2SET DB2INSTPROF = "C: / Program Files / SQLLIB" -g
To set a variable for instance "MyInst" at instance level, use:
DB2SET DB2INSTPROF = "c: / my files / sqllib" -i myinst Please note the above example, set the same variable on two levels (instance level and global level). When the registry variable is defined at different levels, DB2 always select the lowest level value; in this case, it will select the value of the instance level. The db2set command is the same as the set command in the previous section, and there is no space before and after the equal sign (=). Some registry variables are required to stop the changes, requiring you to stop and launch instances (DB2Stop / DB2Start). Other registry variables do not have this requirement. For security reasons, it is recommended that you always stop and start instances after changing registry variables. The configuration parameter configuration parameters are defined on two different levels (instance levels, and database levels). Variables on each level are different (the same variables can be defined at different levels as the registry variables). In the instance level, the variable is stored in the Database Manager Profile (DBM CFG); changes made to these variables affect the "all" database related to this instance, which is shown in the figure (each The instance defines one), and the box is displayed outside the database box. Here are some commands related to configuration parameters: To view the contents of DBM CFG from the CLP, please issue the following command:
DB2 Get DBM CFG To update the value of a specific variable, issue the following command:
DB2 Update DBM CFG Using
DB2 Update DBM CFG Using Intra_ParalLlel Yesv8, the changes made to "All" DBM CFG variables require you to stop and start instances (DB2Stop / DB2Start). Now, approximately 40% of the parameters in V8 are "connected" parameters; that is, no longer need to stop and start instances. Please refer to the DB2 V8 Administration Guide (DB2 V8 Administration Guide "for more details. At the database level, the variable is stored in the Database Profile (DB CFG); changes made to these variables affect specific databases. From Figure 1, you can see that there is a DB CFG box inside each defined database.
To view the contents of DB CFG, issue the following command from the CLP:
DB2 GET DB CFG for
E.g:
DB2 GET DB CFG for MYDB1 To update the value of a specific variable, issue the following command:
DB2 UPDATE DB CFG for
E.g:
DB2 UPDATE DB CFG for MyDB1 UPDATE DB CFG for MyDB1 USIING MINCOMMIT 3V8 Before, "All" DB CFG variables are changed to disconnect all connections to the database; then, these changes will take effect when the first new connection is performed. About 50% of the parameters in V8 can be online configuration; that is, there is no need to disconnect to all connections to the database in order to make changes to effect Please refer to the DB2 V8 Administration Guide (DB2 V8 Administration Guide "for more details. In DB2, the directory is a binarily stored in the database and its connection information. There are four main directories described below in DB2. Also included in the corresponding connection command; however, many users find the client configuration assistant GUI Tool (Client Configuration Assistant GUI Tool) is very convenient to establish connectivity. The system database directory (or system DB directory) system database directory is the primary "Table of Contents", which contains information about all databases that can be connected from your DB2 system. As you can see from Figure 1, the system DB directory is stored in instance levels; therefore, if you intend to delete an instance, then you should consider backing up its content. To list the contents of the system DB directory, issue the following command from the CLP: DB2 List DB DIRECTORY This command output contains words "indirect", which means: This item is suitable for local databases (ie, residing Databases on the machine you currently under use. This item also points to the local database directory indicated by the "Database Drive" item (in Windows) or "local Database Directory" item (in UNIX). Any item containing the word "Remote" means that this item is for a remote database (ie, a database residing on other machines instead of your currently being used). This item will also point to the node directory item indicated by the "Node Name" item. To enter an information input system db directory, you need to use the Catalog command:
DB2 Catalog DB
For example: DB2 Catalog DB MyDB As YourDB At Node MyNode
The node name is a pointer to a certain item in the node directory. This must already exist before issuing this command. A Catalog command is usually only used when adding information to a system DB directory of a remote database. For the local database, you will automatically create a Catalog item after creating a database with the CREATE DATABASE command. Local database directory (or local DB directory) The local database directory contains information about the local database (ie, residing on a database on the machine you are currently using). As you can see from Figure 1, the local database directory resides within the database structure. Note It is also possible to see that there is no special command to enter information in this directory from this figure. When you create a database with the Create Database command, an item will be added in this directory. To list the contents of the local database directory, issue the following command:
DB2 List DB Directory on
Among them,
DB2 List Node Directory To enter a node directory from the CLP, issue a CAATALOG command:
DB2 Catalog Tcpip Node
Remote
Server
E.g:
DB2 Catalog Tcpip Node MyNode
REMOTE 9.26.138.35
Server 60000 To get the port number of the remote instance you want to connect, you can view the SVCENAME parameter in the DBM CFG of this instance. This value typically corresponds to one of the TCP / IP Services files. The DCS Directory DCS directory contains connectivity information that usually resides on the host database on the ZSERIESTM (S / 390®) or iSeriesTM (AS / 400®) machine. You need to install DB2 Connect software. Here are some commands:
To list the contents of the DCS directory, issue the following command from the CLP:
DB2 List DCS Directory To enter a DCS directory, issue a CAATALOG command from the CLP:
DB2 Catalog DCS DB AS
DB2 Catalog DCS DB AS DB1G Please refer to the DB2 V8 connectivity shortcut to obtain a summary of those commands described in this section. The database is closed and separate units associated with examples. Due to this independence, the objects of two or more databases can have the same name. For example, Figure 1 shows a table space called "MyTablesPace1", which is located inside the database "MYDB1" related to the instance "DB2". There is also a table space with the same name, which is located inside the database "MYDB2" related to the example "DB2". Since the database is a closed unit, you cannot perform queries involving two different database tables (unless you use the Information Integrator (II) / RELATIONAL Connect, this has exceeded the discussion category of this article). For example, queries involving "Table1" and database "mydb2" in the database "MYDB1" are not allowed. The database is created with command CREATE DATABASE. Note that this is considered to be a command, not a SQL statement. When you create a database, table space, log, and buffer pool, the configuration file is automatically created, which is why the command takes a few seconds. Table space is a logic object used as an intermediate layer between logic meter and physical containers. When you create a table space, you can associate it with a specific buffer pool (database cache) and a specific container. Containers are places where physical storage data can be divided into files, directories, and original devices. Catalog (syscatspace), system temporary space (Tempspace1) and user space (userSpace1) are table space and are automatically created when the database is created. Catalog and system temporary space can be considered as a system structure because they are required for the normal operation of the database. Catalog contains metadata (data about data). Some other RDBMS call this structure as "data dictionary". Don't confuse this term "catalog" and the previously mentioned Catalog command; they have no relationship. The system temporary table space is a work area that performs operations (such as connection and sorting) of the database manager. At least there must be a system temporary table space. UserSpace1 tablespace is created by default, but it can be deleted. It is used to store the default location of the user table. Figure 1 shows other tablespaces explicitly created with the CREATE TABLESPACE command with the peach brown as the title color of this section. Please refer to "DB2 V8 SQL Reference Daquan (DB2 V8 SQL Reference" for more details. The table is a disorderly data record set consisting of rows and columns. The index is an ordered normset set related to the table for performance purposes and ensures uniqueness. Video, audio and scanning documents, etc. can be stored as large objects (LOB) in the database. Table, index and lob reside in table space. The log is a file used to resume the purpose. The log records each operation performed on the database. In case of a failure, the log is critical to restore the database to a consistent point. The buffer pool is a memory area, and all indexes and data pages (except for the LOB) must be elapsed in this area in order to process. It is the main cache used by the Database Manager. In terms of database performance issues, the buffer pool is the most important object for tuning. Case Studies Since you are already familiar with the DB2 environment, let us review all the concepts mentioned in this article through simple case studies: DB2 database administrator (DBA) leaving the company, manager wants you All DB2 systems. This is the first day you serve as the new DB2 DBA, so you want to be familiar with the system.
You are logged in to the development machine where DB2 is installed and opens the CLP window. Below is the steps you want: First, you want to know how many instances in the machine:
DB2ILIST then you want to know which instance is active:
DB2 get instance uses the db2ilist command, you find two instances - instances "DB2" and instance "MyInst" on the machine. With the DB2 Get Instance command, you find that the "DB2" instance is the current active instance. Now you want to list the databases in the "MyInst" instance. Since this example is not a current activity instance, you first have to switch to this instance in the current CLP window:
SET DB2INSTANCE = MyInst Send DB2 GET Instance again to see if MyInst is now the current instance, then list the database to list the database defined in this instance:
DB2 List DB DIRECTORY This command displays only one database (MyDB2) in this instance. You want to try to create a new database (you will then delete it), do it for this:
DB2 CREATE DATABASE TEMPORAL database is created for some time because several objects are created inside the database in the database. Two databases will be displayed in another DB2 List DB DIRECTORY command: "MYDB2" and "Temporal". Connect to the "MYDB2" database (DB2 Connect to MYDB2) and see how many tables in the database (DB2 List Tables). You can also view how many tablespaces defined (DB2 List TableSpaces). After lunch time, you decide to recover some things. First, you decide to delete a temporary database. When the three seconds power-down causes the machine to automatically reboot, you are in the process of entering the command. You don't worry about the DB2 database status, because you know that DB2 crash recovery will ensure that it is unanimously; so you open a new CLP window after rebooting, then remove the database.
DB2IDROP MyInst ah! Error, you use the wrong command and delete the entire MyInst instance without just deleting the database "Temport". Will you lose the database "MYIDB2" and "Temport" related to instance "MyInst"? View Figure 1, you may feel lost; however, the situation is not the case. When you delete an instance, you have not deleted the database related to it. An instance provides you with the environment of the link DB2 code so that these codes use instances based on user data. The database is "associated" to an instance. But when you install a new version of DB2, you usually use the instance "upgrade" into new code versions; and the database remains unchanged. Since you have calmed down, you know that the database is not lost, then you should start thinking about the working principle of the db2idrop command. Usually, if your instance is active, then the command will give you a warning. Then you realize that after rebooting the machine, the instance "MyInst" is not automatically started, and the "DB2" instance has been configured to start automatically after booting the machine. Also, when the new CLP window is opened, the value of the DB2Instance environment variable is set to "DB2" instead of "MyInst", assuming "DB2" is the value specified by the environment variable in the control panel of the machine. Since you realize this, then you should continue to work hard to make all recovery:
First, you need to create instance MyInst: DB2icRT MyInst now, switch to this instance (SET DB2Instance = MyInst). If you issue a List DB Directory command, you get a mistake indicating that the system DB directory does not exist. If you look at Figure 1, you will understand that when you delete an instance, all instances of profiles and directories are deleted. Therefore, instance-level profile registry, DBM CFG, system DB directory, node directory, and DCS directory are gone. When you create an instance, a DBM CFG and instance-level registry with default values are also created. Fortunately, the former DBA stores the content backup of all of these files. He issued a command similar to the following:
DB2 GET DBM CFG> DBMCFG.BK
DB2SET -All> db2set.bk
DB2 List DB Directory> SystemDbdir.bk
DB2 List Node Directory> NodeDir.bk
DB2 List DCS DIRECTORY> DCSDIR.BK
You must use the appropriate command to manually restore the values based on these file contents. For the "Temport" database you created, it is a local database that needs to be recorded again. A command "DB2 Catalog DB Temporal ON
DB2 DROP DATABASE TEMPORAL sets the value of DB2Instance back to "DB2" (SET DB2Instance = DB2). Ok, I'm big! End the language in vivid illustrations of the full picture of the DB2 V8 environment. We discuss the concepts, configuration files, registry variables, directories, databases, etc. The picture makes you understand the resident location of all these objects. The case studies are used to review all the concepts discussed herein. It includes commands executed using the command line processor (CLP). The GUI tools have not been discussed in this article. Although using the GUI tool easier to manage DB2 environments, it is best to use CLP for learning purposes. Once you have a solid foundation for DB2 environments using CLP, you should be a simple task that learns to use the GUI tool.
About the author Raul F. Chong is a database consultant in IBM Toronto laboratory, mainly working with IBM business partners. Raul has been working in IBM for 5 years, and 3 years is working in the DB2 Technical Support, and two years of consultants, specializing in database applications and migrating from other RDBMS to DB2. You can contact him through the following email address: rfchong@ca.ibm.com.
