The management of Db2 is not an easy affair. It is not like any open source relational database. It is a little like Oracle, but there are differences. This is because Db2 for Linux, Unix, and Windows is a lot like maintaining a mainframe database, just on a smaller and easier scale. While there are many tools to help manage the Db2 environment contained in the product itself, the key to proper management is understanding the architecture of the product and how it manages hardware resources. Without this knowledge, it is easy to get into trouble and overextend the hardware of the machine. With proper management, a single piece of hardware can manage either a few large databases or many smaller ones without stretching the resources on the machine too far.
This chapter will give you enough knowledge so that you can set up the Db2 environment properly without overburdening you with facts that have few, if any, consequences.
Db2 Instances
Instances are the top-level architectural structure in Db2. When you installed Db2, you created the first Db2 Instance, which had the default name of db2inst1. This is also the Instance that holds the sample database we installed in the previous chapter.
Any number of databases can be installed in an Instance. But it is possible to overload an Instance and cause performance problems. This is because Db2 sets aside a fixed number of processes and threads for each Instance, and it does not dynamically add or remove resources during the active lifetime of an Instance. You must stop the instance, adjust the number of resources, and then restart the Instance. Of course, the databases owned by the Instance will have to be stopped and restarted as well. That is the key: stopping and starting an Instance can have impacts on availability and the contained databases. If you are continually stopping and restarting Instances, your users will not be very happy with your management skills.
Instances are easy to describe, but harder to justify. You have to know some history to be able to properly understand where they came from as well as why they even exist. Instances were first introduced just after the year 2000. Their justification came as the databases on multi-drive machines started to become very large. They were so large and had so many users that the network interface became a bottleneck in processing database requests. It seems that the machines now far outpaced the network in being able to handle database requests. Db2 had plenty of idle time while it waited on the network interface to process the next request. This was in the days when the fastest common networks were only 10 MB capacity. What was needed was a way to divide the databases among multiple network ports. Thus, instances were born.
Instances were not only given their own network port, they were also given their own processes, threads, and semaphores. Thus, if a database crashed for any reason, it would only disrupt the databases within the containing Instance. The other Instances and databases would continue to run normally while the database administrator dealt with the downed Instance.
Instances can be used in many ways by the database administrator, but the most common use is to prevent network port congestion. The second most common use is to separate sets of databases from each other for security purposes. Since each Instance has its own set of administrators and users, this helps to enforce security between Instances.
One other attribute of an instance is that it has no physical presence on your disk system, or at least not much of one. It is very hard to point to a place in the disk system and say, “This is where an instance resides.” There are a few files created with the instance, but these do not describe where the instance is. You should just think of it as a logical entity and not a physical one.
There is a set of commands that administrators can use to manage instances. The following are some example Db2 commands that manage Instances.
All the commands used in this chapter can be run in one of two ways. They can be run by logging into the db2inst1 user, or they can be run by using the sudo db2inst1 prefix to the command.
db2ilist
Instance Environment Commands
These commands are useful to work with arrangement of instances in the Db2 CLI.
Creating an Instance
Let us see how to create a new Instance .
If you want to create a new Instance, you need to log in with root. An Instance ID is not a root ID or a root name.
Here are the steps to create a new Instance:
Step 2: Go to the Db2 Instance directory as a root user to create a new Instance.
Arranging a Communication Port and Host for an Instance
Updating an Instance
This command is used to update the Instance within the same version release. Before executing this command, you need to stop the Instance database manager using the “db2stop” command. The following syntax “inst_name” indicates the previously released or installed Db2 server Instance name, which you want to update to a newly released or installed Db2 server version.
Upgrading an Instance
-d : Turns debugging mode on
-k : Keeps the pre-upgrade Instance type if it is supported in the Db2 copy, from which you are running this command
If you are using the super user (su) on Linux for the db2iupgrade command, you must issue the “su” command with the “-” option.
Dropping an Instance
On Linux and UNIX operating systems, this command is located in the DB2_installation_folder/instance directory.
Using Other Commands with an Instance
The following is the command to find out which Db2 instance we are working on now.
Databases
Databases are the next level of manageable objects in Db2. Every database is contained inside of a single Instance, but there can be multiple databases contained inside an Instance.
The database has no communications interface to the outside world. Instead, the Instance controls communications between each contained database and the outside world. This reduces the redundancy of the communication code when multiple databases are owned by the Instance.
The database catalogs
The global deadlock files
Tablespace information
Storage group control files
Temporary tablespaces
Global configuration file
History files
Logging files
Automated storage containers
Each one of these objects will be discussed at length in the following sections and in Chapter 4 later in this book.
Db2 Catalog Views
The Db2 catalog tables are used to hold information about all the database tables, views, indexes, and other entities contained in the database. Each database contains its own catalog tables, and they are not shared with any other database.
The catalog tables are extremely important and should only be updated by the Db2 system. To that end, you can no longer even read the catalog tables. Instead, the system creates a read-only view of each table. To read a catalog table, you must use the view; you cannot read the catalog tables directly.
The current 11.x release of Db2 contains 140 catalog views plus 9 other views containing statistical information. Many of these views contain very useful information for both administrators and advanced users. Thus, the catalog views are read-only for everyone who has control privileges to that portion of the database. For instance, if a user has no read permissions on a database table, information on that table will not be available to the user in catalog views.
For more detailed information on catalog views, see the IBM documentation at
www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0008443.html
This documentation details the complete layout of each view with additional information about all possible values for many columns.
Locking Event Monitor
New in Db2 11 is the locking event monitor. This replaces the deprecated deadlock event monitor. The old monitor had many deficiencies, which caused some deadlocks to not be detected. The new logging event monitor is much better at detecting deadlocks as they occur instead of waiting for some kind of deadlock timeout. It provides a more robust system to users and administrators.
Deadlock information can be written to two different database tables, but it is always in binary format. Choosing between these two tables can be tricky, so careful thought should be given to your choice. By using SQL to query the table, the system will interpret the binary data and translate to a supported language such as English.
The lock that resulted in the event
The application holding the lock that resulted in the lock event
The applications that were waiting for or requesting the lock that resulted in the lock event
What the applications were doing during the lock event
Tablespace Information
Tablespaces are used to contain database tables. When a database is created, three tablespaces are created automatically. The first tablespace, SYSCATSPACE, contains the database catalog and views. The second tablespace, USERSPACE1, is the default space used to hold the database tables. The third, TEMPSPACE1, is used to hold temporary tables for query results. All these tablespaces are created by default in the location specified when the Db2 system was installed.
There is a lot of information on tablespaces, and this book has an entire chapter devoted to that topic. See Chapter 4 for more information.
If the tblsp_name is NULL or an empty string, then all tablespaces will be returned.
The member specifies the member number of the tablespace. If it is –1, the current member information is returned. If it is –2, then all member information is returned. If NULL is specified, it is the same as specifying –1.
Storage Group Control Files
Storage groups are an intermediate level between the database and its tablespaces. You use storage groups to group together tablespaces. This allows a whole set of tablespaces to reside in the same location. A database can have many storage groups spread across any number of disk devices.
Unless you specify otherwise, a default storage group is created when you create a database. All of the tablespaces will reside in that storage group. See Chapter 4 for more information.
Global Configuration File
The global configuration file is created by an administrator. It is used to set backup procedures for the Db2 system. See the IBM documentation for more information.
History Files
History files are instance specific and are stored in the same place as the instance information. They contain some global information on the instance like the last start and stop times and other sometimes useful data. The number of these files can change from release to release, so refer to your release-specific documentation for more information.
Logging Files
The logging files are specific to a database and store information logged by the Db2 system. The number and types of the files will be specific to your database configuration.
Automated Storage Containers
These are discussed at length in Chapter 4. A default storage container is created when a database is created.
Creating a Database
When a database is created, it is automatically attached to the current instance, that is, the instance you are currently logged into. If this is the very first database ever created, a new default instance will be created for you. The default owner of the database is always the administrator who creates the database. All database system tables are placed in the default storage group known as “IBMSTOGROUP.”
There are two types of database that can be created, a restrictive and a nonrestrictive database. The difference between the two databases is the restrictive database is not granted the “PUBLIC” privilege. This means that only database administrators have access to a restrictive database.
The default privileges on all database tables are CREATETAB, BINDADD, CONNECT, IMPLICIT_SCHEMA, and SELECT. Nonrestrictive databases also have the PUBLIC privilege. These privileges apply to all tables in the database.
This will create the database in the folder /path_location/dblocation. Note that the final file path is reversed from the way it is shown in the statement syntax.
Listing Databases
You should note here the different paths to each database. It demonstrates how you can distribute databases throughout the file system of the server.
Activating a Database
The dbname is the name of the database to be activated.
You should note that this is different from the db2start command. That command activates all databases in an instance, whereas this command only activates a single database. No matter which command is used, all the necessary processes and threads are activated within the instance.
Deactivating a Database
This will stop a single database named dbname. If this is the only active database, then all the processes and threads for the instance will also be stopped.
Connecting to a Database
This will connect you to the database named dbname. You can now issue SQL commands to the database.
Dropping a Database
The dbname is the name of the database. Note that even if this is the only database contained within the instance, the instance will not be dropped.
Tables
Tables are the linchpin of all relational databases. They hold the data that we want to save and organize, no matter what kind of data it might be. Tables are what makes a relational database different from other kinds of databases.
When first encountered, you may think of them as a kind of spreadsheet, and that is a logical analogy. But they have some fundamental differences that set them apart. First, they have a limited number of columns. Second, each column is limited to a single data type. Third, one or more of the columns have a relationship to columns in other table(s), in most cases. The third case is what defines the relational database. Relationships between columns are the key to unlocking both visible and hidden relationships between tables. This is key to discovering interesting things about the data that may have been previously unknown or not obvious.
Tables are made up of columns and rows. While the columns in each row will contain data of the same data type, the values in each column of a row will be mostly different. The number of rows in a table is not necessarily determined by any factor except what is needed by the kind of data being stored and the application(s) using the data.
- 1.
Usability: The database must be as easy to use as possible. Many times there will be users of the data that have limited training on how to create efficient queries, and these must be taken into account.
- 2.
Reporting: There will always be the need to create reports form the data contained in the database. The structure of the data should make this as easy and efficient as possible.
- 3.
Security: The relational database has some really great mechanisms to secure the data it stores. This needs to be designed such that only the data needed by a user is actually available to them.
- 4.
Support and development: The database needs to be designed so that it is easy to maintain with minimal staff. This could mean that during development, some extra time and human resources are needed to accomplish this goal.
- 5.
Functionality: The database is there to solve problems and reveal things previously unknown to the user group. The design of the database needs to accommodate this need.
- 6.
Integration: The database must not only fit into the organization’s infrastructure, but it must accommodate possible new innovation in the infrastructure itself. It must also anticipate new databases being integrated into the infrastructure.
- 7.
Cost: Is the database cost effective? Will it continue to be cost effective? These questions must be answered, sometimes by changing the design of the database. In other cases, different changes may be needed.
- 8.
Scalability: Is the database scalable? What happens when the amount of data doubles or quadruples? What happens when the organization changes (either the data or the organization)? All these questions should be answered with considerations toward cost, efficiency, and performance.
- 9.
Hosting: What kind of hardware will be hosting the database? Can it be spread across multiple systems? All of this will have a direct impact on hardware and support costs.
- 10.
Updates: This is often ignored and left to the support personnel to figure out. This is a grievous mistake. Relational database are all different concerning system updates. Some systems require to be completely halted to apply updates, some require a reboot to apply updates, and others can continue to run even as updates are being applied. In any case, there are always “gotchas” for each update process.
The design of a database from a task perspective is mostly about the design of the tables and their layout. This process will be discussed at length in Chapter 4.
Table Types
Base tables: These tables are the ones that all users will know and use. They hold persistent data.
Temporary tables: Some types of queries will require the creation of temporary tables. These tables never appear in the system in the system catalog and can never have XML columns. As the name implies, these tables are temporary in nature and are destroyed when the transaction closes.
Materialized query tables: These tables are used for performance improvements in queries. They are defined by the query, which also determines what is stored in them. These tables are also destroyed by the system when the transaction closes.
The base table type is the one used by everyone. The other two table types are mostly used by more highly trained individuals for specialized reporting needs. Both of these tables are like the base tables in that they both use the standard data types, which we will look at next.
Built-in Data Types
There are a number of very useful built-in data types in Db2. In addition to these, you can define your own data types, but we will not cover those in this book.
TIME: Represents the time of day in hours, minutes, and seconds. All the parts are numeric.
TIMESTAMP: Represents a specific date and time through seven values in the form of year, month, day, hours, minutes, seconds, and microseconds. All the parts are numeric.
DATE: Represents the date in three parts in the form of year, month, and day. All the parts are numeric.
CHAR(fixed length): Represents a fixed-length character string. Strings shorter than the fixed length will have blanks appended to them when assigned to this data type. The length of the defined string must be 0 < fixed length < 256.
VARCHAR(max length): Represents a variable-length string. The defined length must be less than 32763.
LONG VARCHAR: Represents a variable-length string up to 500k in length.
CLOB: Represents large character blocks up to 500k in length.
GRAPHIC(length): This is a binary type of graphic data up to 500k in length.
VARGRAPHIC(max length): This is a binary type of graphic data up to a max of 500k in length.
DBCLOB: Represents a double-byte binary string of up to 500k in length.
BLOB(max length): Represents a binary blob string of up to 500k in length.
BOOLEAN: Represents a Boolean value of either 0 or 1.
SMALLINT: Represents a small signed integer (a signed 16-bit value).
INTEGER: Represents a signed integer (a signed 32-bit value).
BIGINT: Represents a large signed integer (a signed 64-bit value).
DECIMAL(p,s) or DEC(p,s): A signed decimal number specified by p and s. p is the precision, or the number of decimal digits, up to 31 digits. s is the scale of the digits, or the number of digits to the right of the decimal point, in the range of 1-10**31 to 10**31-1.
NUMERIC(p,s): Same as DECIMAL(p,s).
REAL: Represents a single-precision floating-point number.
DOUBLE: Represents a double-precision floating-point number.
XML: Represents a well-formed XML document. There is not an effective size limit to the document except when it is transmitted to another Db2 server. Here the size limit is 2 GB.
Creating a Table
The SQL statement for creating a table is not used by many standard users, but it is used by database administrators. Users can create materialized query tables (MQTs), but only administrators can create regular tables. The SQL statement for both kinds of tables is essentially the same with only minor differences.
The CREATE TABLE SQL Statement Syntax
The hard part of this statement is the list of columns/constraints. Essentially this is the list of columns the table contains. The list is read from top to bottom, and this turns into a left-to-right list in a SELECT * statement. Each column will always have one of the associated built-in data types described in the previous section or one of the defined data types usually created by the database admin. We will discuss how columns are defined later in this section.
Following the list of columns are all the table options. We will not discuss all of these options except the ones that have an associated tablespace name (TS_Name). This is unique in a few relational database systems including Db2. Tablespaces are always defined on a particular disk drive or a set of drives. This allows a busy table to be placed on one or more disk drives that is at least somewhat dedicated to the tables in the tablespace. The IN, INDEX IN, and LONG IN clauses specify the tablespace in which the table and/or index will be placed. The table and the index do not have to be in the same tablespace.
The parts table defined here has several columns defined with the NOT NULL keywords. This specifies that the column will not allow a NULL value to be stored in it. The id column is defined as the PRIMARY KEY of the table, and thus an index of that column’s values will be created. You should note that the index for this column is not unique as it does not specify the UNIQUE keyword. Last, since no tablespace has been specified, the tables and indexes will be placed in the default tablespace.
There are many options for the CREATE TABLE statement, many more than we can document here. Just assume the options for the Db2 statement are different from other relational database systems you may be familiar with and do not assume you know the syntax since you know system X’s syntax.
The full syntax of the CREATE TABLE statement is available online on the IBM website. Just search for “db2 create table syntax” in your favorite web search engine.
Alter a Table
The ALTER TABLE and ALTER COLUMN statements are able to make changes to a table in a somewhat limited manner. For instance, the ALTER COLUMN statement may only change the ability of a column to store/not store NULLS under certain circumstances.
What these statements can do is add/remove columns, drop tables/indexes, and change referential integrity constraints and a few other items.
All of these limitations apply to normal tables as well as MQTs. However, the ALTER statements are not limited to administrators for MQTs. Users with permissions can ALTER an MQT.
It is suggested that you look up the syntax of the ALTER statements before attempting to use them. These statements are not used very often, so do not assume you remember the details of the syntax.
Other Table SQL Statements
There are some other SQL statements that can supply you with information about a table. With these statements, you have to have at least “read” permissions in order for the system to provide you information about a table, tablespace, index, or any other entity you wish to query.
This will list all the database table names, tables schemas (if any), and tablespace names that hold the tables.
This will show all the columns in the parts table along with the data types and attributes of each column.
Dropping a Table
The SQL statement to delete a table no longer in use is the DROP statement. You should note that any data contained by the table will be lost after you DROP the table. By default, the triggers and relations for a table are NOT dropped.
This SQL statement (both versions) is limited to the owner of the table, usually a database administrator.
Summary
This chapter has presented a variety of Db2 management commands that are used frequently by a Db2 administrator. These commands have a number of syntax variations, which can be used to add, alter, or remove Db2 objects from the database including the database itself, tables, indexes, and other entities.