Create Database SmartGuide -- Fields and controls

Database Name page

User Tables page

Catalog Tables page

Temporary Tables page

Performance page

Region page

Back

Next

Done

Show Command

Cancel

Database Name page

New database name

Default drive or directory

Comment

Database alias

Catalog node

New database name

Database names are the identifiers assigned to databases in the database manager. These names must be unique within this database manager.

Type a name for the new database. The name:

• Must contain 1 to 8 characters
• Must begin with one of the letters A through Z (uppercase or lowercase), or @, #, or $
• Can include the letters A through Z (uppercase or lowercase), the digits 0 through 9, and @, #, $, and _ (underscore)

Note:

To avoid potential problems, do not use the special characters @, #, and $ in a database name if you intend to use the database in a communications environment. Also, because the characters @, #, and $ are not common to all keyboards, do not use them if you plan to use the database in another country.

Default drive or directory

Unless you override the default drive, the User Tables, Catalog Tables, and Temporary Tables will be stored here. The database configuration file and the database recovery log will also be stored here.

You can create new table spaces on different drives or directories after you create the database. You can also use the newlogpath parameter to change the location of the database log files.

For UNIX-based implementations of DB2, this field is titled Default directory, while in OS/2 and Microsoft Windows implementations, it is titled Default drive.

For OS/2 and Windows, click on the down arrow to display a list of possible drive choices. Highlight the one you want to use.

For OS/2, if the drive you want does not appear in the list, type the letter in this field.

For UNIX, either type the directory you want to use or use the Browse button to select a directory.

Comment

Optional: Type a description of your database for clarification. It can be up to 30 characters in length.

The description will appear as a comment in the details view of databases.

Click mouse button 2 on the Database object and select Alter from the pop-up menu to change the comment.

Database alias

Database alias names are local synonyms given to local or remote databases. These names must be unique to the System Database Directory, in which all aliases are stored for the individual instance of the database manager. You cannot create a database with the same name as a database alias, because the alias for a new database defaults to the database name.

Optional: Type an alias for the new database. The alias:

• Must contain 1 to 8 characters
• Must begin with one of the letters A through Z (uppercase or lowercase, or @, #, or $
• Can include the letters A through Z (uppercase or lowercase), the digits 0 through 9, and @, #, $, and _ (underscore)

Note:

To avoid potential problems, do not use the special characters @, #, and $ in a database alias if you intend to use the database in a communications environment. Also, because the characters @, #, and $ are not common to all keyboards, do not use them if you plan to use the database in another country.

Catalog node

Click on the down arrow to display a list of possible nodes. Highlight the one you want to use.

The system catalog tables will be created on this node.

User Tables page

Space management

Container list

Space management

Select how you want the table space containers managed.

The space management can be changed later, by adding new table spaces to an existing database.

Select either of the following:

Low maintenance -- System-Managed Space (SMS)

The table's files are created by the database and stored in the specified directory. The operating system manages the files. If you do not specify a path for the container, the database manager will create one on the default drive.

High performance -- Database-Managed Space (DMS)

The database creates one large file (container) as specified. Data is stored in this container and managed by the database. You must specify a size and the location of a DMS container.

If you can use more than one physical drive, you can improve the performance of your database by adding a container to each drive.

There are a number of trade-offs to consider when determining which type of table space you should use to store your data.

Advantages of an SMS Table Space:

• Space is not allocated by the system until it is required.
• Creating a database requires less initial work since you do not have to predefine the containers.

Advantages of a DMS Table Space:

• The size of a table space can be increased by adding containers, using the ALTER TABLESPACE statement.
• A table can be split across multiple table spaces based on the type of data being stored:
  • Long field and LOB data
  • Indexes
  • Regular table data

  You might want to separate your table data for performance reasons, or to increase the amount of data stored for a table. For example, you could have a table with 64GB of regular table data, 64GB of index data and 2TB of long data.

• The location of the data on the disk can be controlled, if the operating system allows this.
• If all table data is in a single table space, a table space can be dropped and redefined with less overhead than it would take to drop and redefine a table.
• In general, a well-tuned set of DMS table spaces will outperform SMS table spaces.

In general, small personal databases are easiest to manage with SMS table spaces. On the other hand, for large, growing databases you will probably only want to use SMS table spaces for the temporary table spaces, and separate DMS table spaces, with multiple containers, for each table. In addition, long fields and indexes would be stored in their own table spaces.

Container List

This list shows the containers that are currently being used to store the table space. For SMS containers, the list shows the path of the container. For DMS containers, the list shows the full path, the type, and the size of the container.

Use the buttons to add more containers, change an existing container, or remove a container.

Add

Opens the Add Container window, where you can specify the path and, if applicable, the type and size of the container.

Change

Opens the Change Container window, where you can specify the path and, if applicable, the type and size of the selected container.

Remove

Removes the selected container from this table space.

Catalog Tables page

Space management

Container list

Space management

Select how you want the table space containers managed.

The space management can be changed later, by adding new table spaces to an existing database.

Select either of the following:

Low maintenance -- System-Managed Space (SMS)

The table's files are created by the database and stored in the specified directory. The operating system manages the files. If you do not specify a path for the container, the database manager will create one on the default drive.

High performance -- Database-Managed Space (DMS)

The database creates one large file (container) as specified. Data is stored in this container and managed by the database. You must specify a size and the location of a DMS container.

If you can use more than one physical drive, you can improve the performance of your database by adding a container to each drive.

There are a number of trade-offs to consider when determining which type of table space you should use to store your data.

Advantages of an SMS Table Space:

• Space is not allocated by the system until it is required.
• Creating a database requires less initial work since you do not have to predefine the containers.

Advantages of a DMS Table Space:

• The size of a table space can be increased by adding containers, using the ALTER TABLESPACE statement.
• A table can be split across multiple table spaces based on the type of data being stored:
  • Long field and LOB data
  • Indexes
  • Regular table data

  You might want to separate your table data for performance reasons, or to increase the amount of data stored for a table. For example, you could have a table with 64GB of regular table data, 64GB of index data and 2TB of long data.

• The location of the data on the disk can be controlled, if the operating system allows this.
• If all table data is in a single table space, a table space can be dropped and redefined with less overhead than it would take to drop and redefine a table.
• In general, a well-tuned set of DMS table spaces will outperform SMS table spaces.

In general, small personal databases are easiest to manage with SMS table spaces. On the other hand, for large, growing databases you will probably only want to use SMS table spaces for the temporary table spaces, and separate DMS table spaces, with multiple containers, for each table. In addition, long fields and indexes would be stored in their own table spaces.

Container List

This list shows the containers that are currently being used to store the table space. For SMS containers, the list shows the path of the container. For DMS containers, the list shows the full path, the type, and the size of the container.

Use the buttons to add more containers, change an existing container, or remove a container.

Add

Opens the Add Container window, where you can specify the path and, if applicable, the type and size of the container.

Change

Opens the Change Container window, where you can specify the path and, if applicable, the type and size of the selected container.

Remove

Removes the selected container from this table space.

Temporary Tables page

Space management

Container list

Space management

Select how you want the table space containers managed.

The space management can be changed later, by adding new table spaces to an existing database.

Select either of the following:

Low maintenance -- System-Managed Space (SMS)

The table's files are created by the database and stored in the specified directory. The operating system manages the files. If you do not specify a path for the container, the database manager will create one on the default drive.

High performance -- Database-Managed Space (DMS)

The database creates one large file (container) as specified. Data is stored in this container and managed by the database. You must specify a size and the location of a DMS container.

If you can use more than one physical drive, you can improve the performance of your database by adding a container to each drive.

There are a number of trade-offs to consider when determining which type of table space you should use to store your data.

Advantages of an SMS Table Space:

• Space is not allocated by the system until it is required.
• Creating a database requires less initial work since you do not have to predefine the containers.

Advantages of a DMS Table Space:

• The size of a table space can be increased by adding containers, using the ALTER TABLESPACE statement.
• A table can be split across multiple table spaces based on the type of data being stored:
  • Long field and LOB data
  • Indexes
  • Regular table data

  You might want to separate your table data for performance reasons, or to increase the amount of data stored for a table. For example, you could have a table with 64GB of regular table data, 64GB of index data and 2TB of long data.

• The location of the data on the disk can be controlled, if the operating system allows this.
• If all table data is in a single table space, a table space can be dropped and redefined with less overhead than it would take to drop and redefine a table.
• In general, a well-tuned set of DMS table spaces will outperform SMS table spaces.

In general, small personal databases are easiest to manage with SMS table spaces. On the other hand, for large, growing databases you will probably only want to use SMS table spaces for the temporary table spaces, and separate DMS table spaces, with multiple containers, for each table. In addition, long fields and indexes would be stored in their own table spaces.

Container List

This list shows the containers that are currently being used to store the table space. For SMS containers, the list shows the path of the container. For DMS containers, the list shows the full path, the type, and the size of the container.

Use the buttons to add more containers, change an existing container, or remove a container.

Add

Opens the Add Container window, where you can specify the path and, if applicable, the type and size of the container.

Change

Opens the Change Container window, where you can specify the path and, if applicable, the type and size of the selected container.

Remove

Removes the selected container from this table space.

Performance page

Read/Write specifications

Drive specifications (OS/2, Windows NT, Windows 95, and Windows 98 only)

Read/Write specifications

This group box shows the extent and prefetch for the user table space.

Use the Change push button to change the extent and prefetch for the table space.

Drive specifications (OS/2, Windows NT, Windows 95, and Windows 98 only)

This list shows the performance parameters for the drives where any containers will be stored. The overhead and transfer rates are used by DB2 to optimize database performance.

Use the Change push button to change the overhead and transfer rates for the selected drive.

Region page

Database locale

String comparison

Database locale

The locale group specifies the territory and code set for data entered in this database. The territory and code set are used for string comparisons (default collating sequence) and character conversions.

These values are stored in the Database Configuration file. You can read these values on the Environment page of the Configure -- Database window.

Once you have created the database, you cannot go back and change these values. You would have to create another database.

To specify a locale for this database, provide the following information:

Country:

Click on the down arrow to display a list of countries and languages, and then select one. Valid territory codes and code sets for the selected country and language appear in the boxes below this box.

Territory:

Click on the down arrow to display a list of valid territory codes for the selected country, and then select one.

Code set:

Click on the down arrow to display a list of valid code sets for the selected country, and then select one.

String comparison

Select a radio button to determine how characters are sorted, merged, and compared.

The string comparison method, or collating sequence, cannot be changed once the database has been created.

Local alphabet:

Items are sorted according to the database locale and characters are compared using the database code set and locale. This is also known as the System Collating Sequence.

Byte for byte:

Items are sorted according to their binary representation. This is also known as the Identity Collating Sequence.