DB2 DB Creation Considerations
21 December 2009
When you create a new database there are several options that you can set, some of which can’t be changed. If you get one of those options that can’t be changed wrong upon creation, you either have to live with the problem or you must drop and recreate the database, exporting and reloading all data at the appropriate times. This is a major outage for many production databases, so you want to get these right the fist time. Some of these options are set by default if you don’t specify a value for the option so it is important to know all of them and what they mean. In this article I’ll discuss all of the options of the CREATE DATABASE command and indicate which ones can’t be changed. You want to understand these options whether you create your database at the command line or with a GUI.
Please note that this is a discussion about creating new
databases. When you are upgrading a database from an older version, you are
often stuck with the choices made in the original version.
Click CREATE
DATABASE to view the syntax diagram of the command to create a new
database. This link also shows all parameters and options, and has lots of
other good information about creating a database especially in the Usage Notes
as the bottom of the page. You can also read more about creating a database at
the creating
databases link.. You can use the GET
DB
CONFIGURATION command to view the values of most of the following
parameters.
Database Name (NOT Changeable)
The first parameter for the create database name is the database name. You can never change the physical name of the database, but you can catalog a different alias on the database server that can be referenced from all clients. Use the CATALOG DATABASE command with the “ON” option to catalog an alias for your database. You can even use the UNCATALOG DATABASE command to remove the original name if you want to prevent anyone from using it.
ALIAS (Changeable)
Automatically catalogs the name you specify in the database directory.
CODE PAGE (NOT Changeable)
Did you know that there are several different ways to encode ASCII characters? Well there are. In addition, some code pages allow only single byte characters like those letters and numbers used in English while others allow multi-byte characters like Kanji to be used. The code page discussion is quite involved, so I’ll let you read more about it yourself. There are performance ramifications of your code page choice so you really should get a basic understanding of it before you implement any system where performance is important. If you do not specify the CODE PAGE parameter, a default will be selected for you. Starting in DB2 v9.5 UNICODE is the default code page for all new databases. In DB2 9.1 and earlier versions, DB2 would pick the code page of the server as the code page for the database. If you use the pureXML feature of DB2, you should note that any tablespace that has a table with the XML data type must be UNICODE (UTF-8 code set). Starting in DB2 v9.5 you could create UNICODE tablespaces in a non UNICODE database if you needed to use pureXML. In v9.1 you had to have a UNICODE database to use that feature.
TERRITORY (NOT Changeable)
Territory generally refers to a country such as the United States. Not all code pages can be used for all territories. See this link for the list of valid code pages for each territory.
COLLATE USING (NOT Changeable)
The collate using parameter defines how objects will be ordered or sorted when such actions are performed. For most applications that I’ve encountered the collating sequence has not really mattered and the default has been fine.
AUTOMATIC STORAGE (Sort of Changeable starting in v9.7)
Indicates whether the database will use the automatic storage feature. The default is to use automatic storage. Prior to v9.7 this could not be changed. Starting with v9.7 it can be changed for particular tablespaces using the ALTER TABLESPACE command or with a redirected restore. Therefore, if you are upgrading from an earlier version of DB2 and you want to use automatic storage, then you should upgrade to v9.7.
ON (NOT Changeable)
The meaning of this option depends on the value of the AUTOMATIC STORAGE option. This text is copied directly from the DB2 Information Center.
· If AUTOMATIC STORAGE NO is specified, automatic storage is disabled for the database. In this case, only one path can be included as part of the ON option, and it specifies the path on which to create the database, i.e. the three initial tablespaces – catalog, temp and userspace1. If a path is not specified, the database is created on the default database path that is specified in the database manager configuration file (dftdbpath parameter). This behavior matches that of DB2® Universal Database™ Version 8.2 and earlier.
· Otherwise, automatic storage is enabled for the database by default. In this case, multiple paths may be listed here, each separated by a comma. These are referred to as storage paths and are used to hold table space containers for automatic storage table spaces. For multi-partition databases the same storage paths
DBPATH ON (NOT Changeable)
This option was introduced with the AUTOMATIC STORAGE feature in a DB2 v8.2 fix pack. This option specifies on which paths to create the database. If the DBPATH ON option is not specified, the database is created on the first path listed in the ON option. If no paths are specified with the ON option, the database is created on the default database path that is specified in the database manager configuration file (dftdbpath parameter). This will also be used as the location for the single storage path associated with the database.
The database path is the location where a hierarchical directory structure is created. The structure holds the following files needed for the operation of the database:
· Buffer pool information
· Table space information
· Storage path information
· Database configuration information
· History file information regarding backups, restores, loading of tables, reorganization of tables, altering of table spaces, and other database changes
· Log control files with information about active logs
The DBPATH ON option can be used to place these files and information in a directory that is separate from the storage paths where the database data is kept (ON parameter). It is suggested that the DBPATH ON option be used when automatic storage is enabled to keep the database information separate from the database data.
The maximum length of a database path is 215 characters and the maximum length of a storage path is 175 characters.
RESTRICTIVE (NOT Changeable)
By default several basic privileges are granted to PUBLIC when a database is created such as the privilege to connect to the database. This parameter can not be changed, but you can grant any privilege to PUBLIC that you want as long as you have the necessary privileges. For a complete list of privileges not granted when this parameter is specified, please see the definition of RESTRICTIVE in the information center for the CREATE DATABASE command.
AUTOCONFIGURE (Can be Run Anytime)
When you create a database the automatic configuration process is run by default to create the initial DB and DBM configuration settings. This process uses information about the server such as number of processors and the amount of memory to make a good initial configuration for your database and bufferpools. Using this parameter you can specify additional information about how the database will be used that will help DB2 make even better decisions about configuration settings. You can read more about those settings under AUTOCONFIGURE in the description of CREATE DATABASE command.
PAGESIZE (Changeable)
This parameter specifies the page size of the initial bufferpool created with the new database as well as the initial three tablespaces described below. It also is used as the default value for any subsequent bufferpools or tablespaces that you create without specifying a page size. You can change this after database creation using the UPDATE DBM CFG command.
DATABASE TABLESPACES
Three tablespaces are always created when a database is created:
|
CATALOG TABLESPACE |
Contains tables defining the database and structures. |
|
USER TABLESPACE |
Default tablespace where user tables are placed if no tablespace is specified for them. Can be dropped after you create the database. |
|
TEMPORARY TABLESPACE |
Used by DB2 for sorts, intermediate results and other times when DB2 needs a work area. You must always have at least one temporary tablespace for a database. If you don’t like anything about the initial temporary tablespace, you can always create another one with the same page size and then drop the initial one. Remember that you should always have at least one temporary tablespace for each page size used by data tablespaces. Those temporary tablespaces should have the same page size as the data tablespaces. |
You can configure all of the same tablespace parameters on these initial tablespaces that you can on any other tablespace such as containers and performance parameters. See the definition of the above three tablespaces in the description of the CREATE DATABASE command.
NUMSEGS
Starting in DB2 v9.5 and above this parameter is ignored. I never saw anyone use it anyway.
DFT_EXTENT_SZ (Changeable)
Extent size to be used on the initial tablespaces and all subsequently created tablespaces if none is specified when creating those tablespaces. This parameter can be viewed and updated in the database configuration.
WITH (Changeable)
Allows you add a short comment about the database of up to 30 characters. The comment must be in quotes. It can viewed using the LIST DB DIRECTORY command, and changed by un-cataloging and cataloging the entry again. It is a good thing to use if there is a chance that someone might actually use it. If not, have some fun with it by entering comments like “Larry Ellison prefers DB2” or “Soylent Green is people”. See if anyone notices. When someone connects from the command line, this comment is displayed so don’t say anything embarrassing.
After Creating the Database
There are several other architectural decisions that you should make when planning for the database that will be implemented soon after the database is created. Some of the major considerations are:
Ÿ Default Isolation Level
Ÿ Self Tuning Memory Manager
Ÿ Compression
Ÿ Oracle Compatibility
Ÿ Turning off File System Caching
Ÿ Placement of the transaction logs
Ÿ Type of logging
All of these and more can be found in the BEST PRACTICES papers. Design topics include physical database design, storage, security, tuning and several others. These short papers contain a lot of information and it is a very good idea to read them before creating your production database.