Creating a DB2 9 Database

Now that you have a basic understanding of servers, instances, and databases, let's look at how a DB2 9 database is created. There are two ways to create a DB2 9 database: by using the Create Database Wizard or by using the CREATE DATABASE command. Because the Create Database Wizard is essentially a graphical user interface (GUI) for the CREATE DATABASE command, we will look at the command method first.

In its simplest form, the syntax for the CREATE DATABASE command is:

 CREATE [DATABASE | DB] [DatabaseName] 

where:

DatabaseName

Identifies a unique name that is to be assigned to the database once it is created.

The only value you must provide when executing this command is a name to assign to the new database. This name:

• Can consist of only the characters a through z, A through Z, 0 through 9, @, #, $, and _ (underscore)

• Cannot begin with a number

• Cannot begin with the letter sequences "SYS", "DBM", or "IBM"

• Cannot be the same as the name already assigned to another database within the same instance

Of course, a much more complex form of the CREATE DATABASE command that provides you with much more control over database parameters is available, and we will examine it shortly. But for now, let's look at what happens when this form of the CREATE DATABASE command is executed.

What Happens When a DB2 9 Database Is Created

Regardless of how the process is initiated, whenever a new DB2 9 database is created, the following tasks are performed, in the order shown:

1. All directories and subdirectories needed are created in the appropriate location.

Information about every DB2 9 database created is stored in a special hierarchical directory tree. Where this directory tree is actually created is determined by information provided with the CREATE DATABASE command. If no location information is provided, this directory tree is created in the location specified by the dftdbpath DB2 Database Manager configuration parameter associated with the instance the database is being created under. The root directory of this hierarchical tree is assigned the name of the instance the database is associated with. This directory will contain a subdirectory that has been assigned a name corresponding to the partition's node. If the database is a partitioned database, this directory will be named NODExxxx, where xxxx is the unique node number that has been assigned to the partition; if the database is a nonpartitioned database, this directory will be named NODE0000. This node-name directory, in turn, will contain one subdirectory for each database that has been created, along with one subdirectory that contains the containers that are used to hold the database's data.

The name assigned to the subdirectory that holds the containers that are used to house the database's data is the same as that specified for the database; the name assigned to the subdirectory that contains the base files for the database corresponds to the database token that is assigned to the database during the creation process. Thus the subdirectory for the first database created will be named SQL00001, the subdirectory for the second database will be named SQL00002, and so on. Figure 4-2 illustrates how this directory hierarchy typically looks in a nonpartitioned database environment.

Figure 4-2: Typical directory hierarchy tree for a nonpartitioned database.

Tip 

Never attempt to modify this directory structure or any of the files stored in it. Such actions could destroy one or more databases or make them unusable.

2. Files needed for management, monitoring, and database recovery are created.

After the subdirectory that was assigned the name of the database's token is created, the following files are created in it:

• db2rhist.asc: This file contains historical information about backup operations, restore operations, table load operations, table reorganization operations, table space alterations, and similar database changes (i.e., the recovery history file).

• db2rhist.bak: This file is a backup copy of db2rhist.asc.

• SQLBP.1: This file contains buffer pool information.

• SQLBP.2: This file is a backup copy of SQLBP.1.

• SQLDBCON: This file contains database configuration information.

• SQLDBCONF: This file is a backup copy of SQLDBCON.

• SQLINSLK: This file contains information that is used to ensure that the database is assigned to only one instance of the DB2 Database Manager.

• SQLOGCTL.LFH: This file contains information about active transaction log files. Recovery operations use information stored in this file to determine how far back in the logs to begin the recovery process.

• SQLOGMIR.LFH: This file is a mirrored copy of SQLOGCTL.LFH.

• SQLSGF.1: This file contains storage path information associated with automatic storage.

• SQLSGF.2: This file is a backup copy of SQLSGF.1.

• SQLSPCS.1: This file contains table space information.

• SQLSPCS.2: This file is a backup copy of SQLSPCS.1.

• SQLTMPLK: This file contains information about temporary table spaces.

Two subdirectories named DB2EVENT and SQLOGDIR are also created; a detailed deadlocks event monitor is created and stored in the DB2EVENT subdirectory, and three files named S0000000.LOG, S0000001.LOG, and S0000002.LOG are created and stored in the SQLLOGDIR subdirectory. These three files are used to store transaction log records as SQL operations are performed against the database.

3. A buffer pool is created for the database.

During the database creation process, a buffer pool is created and assigned the name IBMDEFAULTBP. By default, on Linux and UNIX platforms, this buffer pool is 1,000 4K (kilobyte) pages in size; on Windows platforms, this buffer pool is 250 4K pages in size. The actual memory used by this buffer pool (and for that matter, by any other buffer pools that may exist) is allocated when the first connection to the database is established, and it is freed when all connections to the database have been terminated.

4. Two regular table spaces and one system temporary table space are created.

Immediately after the buffer pool IBMDEFAULTBP is created, three table spaces are created and associated with this buffer pool. These three table spaces are:

• A regular table space named SYSCATSPACE, which is used to store the system catalog tables and views associated with the database.

• A regular table space named USERSPACE1, which is used to store all user-defined objects (such as tables, indexes, and so on) along with user data, index data, and long value data.

• A system temporary table space named TEMPSPACE1, which is used as a temporary storage area for operations such as sorting data, reorganizing tables, and creating indexes.

Unless otherwise specified, SYSCATSPACE and USERSPACE1 will be database-managed (DMS) FILE table spaces, while TEMPSPACE1 will be a system-managed (SMS) table space; characteristics for each of these table spaces can be provided as input to the CREATE DATABASE command or the Create Database Wizard.

5. The system catalog tables and views are created.

After the table space SYSCATSPACE is created, a special set of tables, known as the system catalog tables, is constructed within that table space. The DB2 Database Manager uses the system catalog tables to keep track of such information as database object definitions, database object dependencies, database object privileges, column data types, table constraints, and object relationships. A set of system catalog views is created along with the system catalog tables, and these views are typically used when accessing data stored in the system catalog tables. The system catalog tables and views cannot be modified with SQL statements (however, their contents can be viewed). Instead, they are modified by the DB2 Database Manager whenever:

• A database object (such as a table, view, or index) is created, altered, or dropped.

• Authorizations and/or privileges are granted or revoked.

• Statistical information is collected for a table.

• Packages are bound to the database.

In most cases, the complete characteristics of a database object are stored in one or more system catalog tables when the object is created. However, in some cases, such as when triggers and constraints are defined, the actual SQL used to create the object is stored instead.

6. The database is cataloged in the system and local database directory (the system and local database directory are created first if they don't already exist).

DB2 uses a set of special files to keep track of where databases are stored and to provide access to those databases. Because the information stored in these files is used in the same way as the information stored in an office building directory is used, these files are referred to as directory files. Whenever a database is created, these directories are updated with the database's name and alias. If specified, a comment and code set values are also stored in these directories.

7. The database configuration file for the database is initialized.

Some of the parameters in the database configuration file (such as code set, territory, and collating sequence) will be set using values that were specified as input for the CREATE DATABASE command or the Create Database Wizard; others are assigned system default values.

8. Four schemas are created.

Once the system catalog tables and views are created, the following schemas are created: SYSIBM, SYSCAT, SYSSTAT, and SYSFUN. A special user named SYSIBM is made the owner of each.

9. A set of utility programs are bound to the database.

Before some of the DB2 9 utilities available can work with a database, the packages needed to run those utilities must be created. Such packages are created by binding a set of predefined DB2 Database Manager bind files to the database (the set of bind files used are stored in the utilities bind list file db2ubind.lst).

10. Authorities and privileges are granted to the appropriate users.

To connect to and work with a particular database, a user must have the authorities and privileges needed to use that database. Therefore, whenever a new database is created, unless otherwise specified, the following authorities and privileges are granted:

• DBADM authority, along with CONNECT, CREATETAB, BINDADD, CREATE_NOT_FENCED, IMPLICIT_SCHEMA, and LOAD privileges, are granted to the user who created the database.

• USE privilege on the table space USERSPACE1 is granted to the group PUBLIC.

• CONNECT, CREATETAB, BINDADD, and IMPLICIT_SCHEMA privileges are granted to the group PUBLIC.

• SELECT privilege on each system catalog table is granted to the group PUBLIC.

• EXECUTE privilege on all procedures found in the SYSIBM schema is granted to the group PUBLIC.

• EXECUTE WITH GRANT privilege on all functions found in the SYSFUN schema is granted to the group PUBLIC.

• BIND and EXECUTE privileges for each successfully bound utility are granted to the group PUBLIC.

11. The Configuration Advisor is launched.

The Configuration Advisor is a tool that is designed to help you to tune performance and balance memory requirements for a database by suggesting which configuration parameters to modify based on information you provide about the database. In DB2 9, the Configuration Advisor is automatically invoked whenever you create a database, unless the default behavior is changed by assigning the value NO to the DB2_ENABLE_AUTOCONFIG_DEFAULT registry variable.

The Complete CREATE DATABASE Command

When the simplest form of the CREATE DATABASE command is executed, the characteristics of the database created, such as the storage and transaction logging method used, are determined by several predefined defaults. If you wish to change any of the default characteristics, you must specify one or more options available when executing the CREATE DATABASE command. The complete syntax for this command is:

 CREATE [DATABASE | DB] [DatabaseName] <AT DBPARTITIONNUM> 

or

 CREATE [DATABASE | DB] [DatabaseName] <AUTOMATIC STORAGE [YES | NO]> <ON [StoragePath, ...] <DBPATH [DBPath]>> <ALIAS [Alias]> <USING CODESET [CodeSet] TERRITORY [Territory]> <COLLATE USING [CollateType]> <PAGESIZE [4096 | Pagesize <K>]> <NUMSEGS [NumSegments]> <DFT_EXTENT_SZ [DefaultExtSize]> <RESTRICTIVE> <CATALOG TABLESPACE [TS_Definition]> <USER TABLESPACE [TS_Definition]> <TEMPORARY TABLESPACE [TS_Definition]> <WITH "[Description]"> <AUTOCONFIGURE <USING [Keyword] [Value], ... > <APPLY [DB ONLY | DB AND DBM | NONE>> 

where:

DatabaseName	Identifies the unique name that is to be assigned to the database to be created.
StoragePath	If AUTOMATIC STORAGE NO is specified, identifies the location (drive and/or directory) where the directory hierarchy and files associated with the database to be created are to be physically stored; otherwise, identifies one or more storage paths that are to be used to hold table space containers used by automatic storage.
DBPath	If AUTOMATIC STORAGE YES (the default) is specified, identifies the location (drive or directory) where the directory hierarchy and files associated with the database to be created are to be physically stored.
Alias	Identifies the alias to be assigned to the database to be created.
CodeSet	Identifies the code set to be used for storing data in the database to be created. (In a DB2 9 database, each single-byte character is represented internally as a unique number between 0 and 255. This number is referred to as the code point of the character; assignments of code points to every character in a particular character set are called the code page; and the International Organization for Standardization term for a code page is code set.)
Territory	Identifies the territory to be used for storing data in the database to be created.
CollateType	Specifies the collating sequence (i.e., the sequence in which characters are ordered for the purpose of sorting, merging, and making comparisons) that is to be used by the database to be created. The following values are valid for this parameter: COMPATIBILITY, IDENTITY, IDENTITY_16BIT, UCA400_NO,UCA400_LSK, UCA400_LTH, NLSCHAR, and SYSTEM.
NumSegments	Specifies the number of directories that are to be created and used to store files for the default SMS table space used by the database to be created (TEMPSPACE1).
DefaultExtSize	Specifies the default extent size to be used whenever a table space is created and no extent size is specified during the creation process.
Description	A comment used to describe the database entry that will be made in the database directory for the database to be created. The description must be enclosed by double quotation marks.
Keyword	One or more keywords recognized by the AUTOCONFIGURE command. Valid values include mem_percent, workload_type, num_stmts, tpm, admin_priority, is_populated, num_local_apps, num_remote_apps, isolation, and bp_resizable. Refer to the DB2 9 Command Reference for more information on how the AUTOCONFIGURE command is used.
Value	Identifies the value that is to be associated with the Keyword specified.
TS_Definition	Specifies the definition that is to be used to create the table space that will be used to hold the system catalog tables (SYSCATSPACE), user-defined objects (USERSPACE1), and/or temporary objects (TEMPSPACE1).

The syntax used to define a system managed (SMS) table space is:

 MANAGED BY SYSTEM USING (‘[Container]', ...) <EXTENTSIZE [ExtentSize]> <PREFETCHSIZE [PrefetchSize]> <OVERHEAD [Overhead]> <TRANSFERRATE [TransferRate]> 

The syntax used to define a database managed (DMS) table space is:

 MANAGED BY DATABASE USING ([FILE | DEVICE] '[Container]' NumberOfPages, ...) <EXTENTSIZE [ExtentSize]> <PREFETCHSIZE [PrefetchSize]> <OVERHEAD [Overhead]> <TRANSFERRATE [TransferRate]> <AUTORESIZE [NO | YES]> <INCREASESIZE [Increment] <PERCENT | K | M | G>> <MAXSIZE [NONE | MaxSize <K | M | G> ]> 

And the syntax used to define an automatic storage table space is:

 MANAGED BY AUTOMATIC STORAGE <EXTENTSIZE [ExtentSize]> <PREFETCHSIZE [PrefetchSize]> <OVERHEAD [Overhead]> <TRANSFERRATE [TransferRate]> <AUTORESIZE [NO | YES]> <INITIALSIZE [InitialSize] <K | M | G>> <INCREASESIZE [Increment] <PERCENT | K | M | G>> <MAXSIZE [NONE | MaxSize <K | M | G> ]> 

where:

Container	Identifies one or more containers to be used to store data that will be assigned to the table space specified. For SMS table spaces, each container specified must identify a valid directory; for DMS FILE containers, each container specified must identify a valid file; and for DMS DEVICE containers, each container specified must identify an existing device.
NumberOfPages	Specifies the number of pages to be used by the table space container.
ExtentSize	Specifies the number of pages of data that will be written in a round-robin fashion to each table space container used.
PrefetchSize	Specifies the number of pages of data that will be read from the specified table space when data prefetching is performed.
Overhead	Identifies the I/O controller overhead and disk-seek latency time (in number of milliseconds) associated with the containers that belong to the specified table space.
TransferRate	Identifies the time, in number of milliseconds, that it takes to read one page of data from a table space container and store it in memory.
InitialSize	Specifies the initial size an automatic storage table space should be.
Increment	Specifies the amount by which a table space that has been enabled for automatic resizing will be increased when the table space becomes full and a request for space is made.
MaxSize	Specifies the maximum size to which a table space that has been enabled for automatic resizing can be increased.

Thus, if you wanted to create a DB2 database that has the following characteristics:

• It will be physically located on drive E:.

• It will not use automatic storage.

• It will be assigned the name SAMPLEDB.

• It will recognize the United States/Canada code set. (The code set or code page, along with the territory, is used to convert alphanumeric data to binary data that is stored in the database.)

• It will use a collating sequence that is based on the territory used (which in this case is United States/Canada).

• It will store the system catalog in a DMS table space that uses the file SYSCATSPACE.DAT as its container. (This file is stored on drive E: and is capable of holding up to 5,000 pages that are 4K in size.)

You would execute a CREATE DATABASE command that looks something like this:

 CREATE DATABASE sampledb AUTOMATIC STORAGE NO ON E: USING CODESET 1252 TERRITORY US COLLATE USING SYSTEM PAGESIZE 4096 CATALOG TABLESPACE MANAGED BY DATABASE   (FILE ‘E:\syscatspace.dat', 5000) 

Creating a DB2 9 Database with the Create Database Wizard

If you are the type of individual who prefers using graphical user interfaces to typing long commands, you can use the Create Database Wizard to construct a DB2 9 database. The Create Database Wizard is designed to collect information that defines the characteristics of a database and then create a database that has those characteristics. (These same characteristics can be specified through the various options that are available with the CREATE DATABASE command.)

In Chapter 2, we saw that the Control Center is the most important and versatile GUI tool DB2 9 has to offer. We also saw that the Control Center is comprised of several elements, including:

• An objects pane (located on the left-hand side of the Control Center), which contains a hierarchical representation of every object type that can be managed from the Control Center.

• A contents pane (located on the right-hand side of the Control Center), which contains a listing of existing objects that correspond to the object type selected in the objects pane. (For example, if the Tables object type were selected in the objects pane, a list of all tables available would be listed in the contents pane.)

By highlighting the All Databases object shown in the objects pane of the Control Center and right-clicking the mouse button, you will bring up a menu that contains a list of options available for database objects. The Create Database Wizard is invoked by selecting "Create Database", followed by "Standard…" from this menu. Figure 4-3 shows the Control Center menu items that must be selected in order to activate the Create Database Wizard; Figure 4-4 shows what the first page of the Create Database Wizard looks like when it is first initiated.

Figure 4-3: Invoking the Create Database Wizard from the Control Center.

Figure 4-4: The first page of the Create Database Wizard.

Once the Create Database Wizard is displayed, you simply follow the directions shown on each panel presented to define the characteristics of the database that is to be created. When you have provided enough information for the DB2 Database Manager to create a database, the "Finish" push button displayed in the lower right corner of the wizard (see Figure 4-4) will be enabled. Once this button is selected, a database will be created using the information provided.