Before you implement a database, you should understand the following concepts:
You may need to start or stop DB2 during normal business operations; for example, to do maintenance. To start DB2 on your system, enter the command:
db2start
This command can be run through the Control Center (on Windows 95, Windows NT, or OS/2 operating systems), or at the server as an operating system command or as a command line processor command. You must have SYSADM, SYSCTRL, or SYSMAINT authority to run this command.
To stop DB2 on your system, you must do the following:
db2stop
The db2stop command can be run as an operating system command or as a Command Line Processor command. This command can only be run at the server. No database connections are allowed when running this command; however, if there are any instance attachments, they are forced off before DB2 is stopped.
Multiple instances of the database manager may be created on a single server. This means that you can create several instances of the same product on a physical machine, and have them running concurrently. This provides flexibility in setting up environments.
You may wish to have multiple instances to:
DB2 program files are physically stored in one location on a particular machine. Each instance that is created points back to this location so the program files are not duplicated for each instance created. Several related databases can be located within a single instance.
Instances are cataloged as either local or remote in the node directory. Your default instance is defined by the DB2INSTANCE environment variable. You can attach to other instances to perform maintenance and utility tasks that can only be done at an instance level, such as creating a database, forcing off applications, monitoring a database, or updating the database manager configuration. When you attempt to attach to an instance that is not in your default instance, the node directory is used to determine how to communicate with that instance.
To attach to another instance, which may be remote, use the ATTACH command as described in the Command Reference manual. For example,
attach to testdb2will attach you to the instance called testdb2 that was previously cataloged in the node directory.
After performing maintenance activities for the testdb2 instance, you can then detach from that instance by executing the following command:
detach
The Command Reference provides information about the type of connection that is required to execute each command.
DB2 support for multiple instances varies by operating system. See the Quick Beginnings guide appropriate to your platform for information on defining multiple DB2 instances on one machine.
The objects in a relational database are organized into schemas, which provide a logical classification of objects in the database. The schema is an object identified in the high-order part of a two-part object name. When an object such as a table, view, alias, distinct type, function, index, package or trigger is created, it is assigned to a schema. This assignment is done either explicitly or implicitly.
For example, USER A issues a CREATE TABLE statement in schema C as follows:
CREATE TABLE C.X (COL1 INT)
As described in "Definition of System Catalog Tables", some objects are created within certain schemas when the database is created.
Before creating your own objects, you need to consider whether you want to create them in your default schema (identified by your user ID) or by using a separate schema that logically groups the objects. If you are creating objects that will be shared, using a different schema name can be very beneficial. For more information on how to explicitly create a schema, see "Creating a Schema".
You must modify configuration parameters to take advantage of parallelism within a database partition or within a non-partitioned database. For example, intra-partition parallelism can be used to take advantage of the multiple processors on a symmetric muti-processor (SMP) machine.
Use the GET DATABASE CONFIGURATION and the GET DATABASE MANAGER CONFIGURATION commands to find out the values of individual entries in a specific database, or in the database manager configuration file. To modify individual entries for a specific database or in the database manager configuration file, use the UPDATE DATABASE CONFIGURATION and the UPDATE DATABASE MANAGER CONFIGURATION commands respectively.
Configuration parameters that affect intra-partition parallelism include the max_querydegree and intra_parallel database manager parameters, and the dft_degree database parameter. For more information on configuration parameters, see Chapter 19. "Configuring DB2".
When running in a multiple partition environment, you can create a database from any node that exists in the db2nodes.cfg file using the CREATE DATABASE command or the sqlecrea() application programming interface (API). For information, see the Command Reference and API Reference manuals.
Before creating a partitioned database, you must determine if you will be a local or remote client to the instance where the database is to be created. Second, you must attach to the instance. You must also select which database partition will be the catalog node for the database. The database partition to which you attach and execute the CREATE DATABASE command becomes the catalog node for that particular database.
The catalog node is the database partition on which all system catalog tables are stored. All access to system tables must go through this database partition.
If possible, you should create each database in a separate instance. If this is not possible (that is, you must create more than one database per instance), you should spread the catalog nodes among the available database partitions. Doing this reduces contention for catalog information at a single database partition.
| Note: | You should regularly do a backup of the catalog node and avoid putting data on it (whenever possible), because other data increases the time required for the backup. |
When you create a database, it is automatically created across all the database partitions defined in the db2nodes.cfg file.
When the first database in the system is created, a system database directory is formed. It is appended with information about any other databases that you create. The system database directory is sqldbdir and is located in the sqllib directory under your home directory. This directory must reside on a shared file system, (for example, NFS on UNIX platforms) because there is only one system database directory for all the database partitions that make up the parallel database.
Also resident in the sqldbdir directory is the system intention file. It is called sqldbins, and ensures that the database partitions remain synchronized. The file must also reside on a shared file system since there is only one directory across all database partitions. The file is shared by all the partitions making up the database.
Configuration parameters have to be modified to take advantage of data partitioning. Use the GET DATABASE CONFIGURATION and the GET DATABASE MANAGER CONFIGURATION commands to find out the values of individual entries in a specific database, or in the database manager configuration file. To modify individual entries in a specific database, or in the database manager configuration file, use the UPDATE DATABASE CONFIGURATION and the UPDATE DATABASE MANAGER CONFIGURATION commands respectively.
The database manager configuration parameters affecting a partitioned database include conn_elapse, fcm_num_anchors, fcm_num_buffers, fcm_num_connect, fcm_num_rqb, max_connretries, max_coordagents, max_time_diff, num_poolagents, and stop_start_time.
For more information on configuration parameters, see Chapter 19. "Configuring DB2".