Before you implement a database, you should understand the following prerequisite tasks:
You may need to start or stop DB2 during normal business operations; for example, you must start an instance before you can perform the following tasks:
To start a DB2 instance on your system:
. INSTHOME/sqllib/db2profile (for Bourne or Korn shell) source INSTHOME/sqllib/db2cshrc (for C shell)where INSTHOME is the home directory of the instance you want to use.
|
db2start
| Note: | The db2start command starts the instance according to the rules in Setting the Current Instance. |
The db2start command will launch DB2 as an NT service. DB2 on Windows NT can still be run as a process by specifying the "/D" switch when invoking DB2START. DB2 can also be started as a service using the Control Panel or "NET START" command.
In order to successfully launch DB2 as a service from DB2START, the user account must have the correct privilege as defined by the Windows NT operating system to start an NT service. The user account can be a member of the Administrators, Server Operators, or Power Users group.
When running in a partitioned database environment, each database partition server is started as an NT service.
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 create the following environments:
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.
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. Refer to the Quick Beginnings guide appropriate to your platform for information on defining multiple DB2 instances on one machine.
To attach to another instance, which may be remote, use the ATTACH command as described in the Command Reference manual.
To use the Control Center:
|
To attach to an instance using the command line, enter:
db2 attach to <database_name>
For example, to attach you to the instance called testdb2 that was previously cataloged in the node directory:
db2 attach to testdb2
After performing maintenance activities for the testdb2 instance, you can then detach from that instance by executing the following command:
db2 detach
Database object names may be made up of a single identifier or they may be schema qualified objects made up of two identifiers. The schema, or high-order part, of a schema qualified object provides a means to classify or group objects in the database. 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.
Explicit use of the schema occurs when you use the high-order part of a two-part object name when referring to that object in a statement. For example, USER A issues a CREATE TABLE statement in schema C as follows:
CREATE TABLE C.X (COL1 INT)
Implicit use of the schema occurs when you do not use the high-order part of a two-part object name. When this happens, the CURRENT SCHEMA special register is used to identify the schema name used to complete the high-order part of the object name. The initial value of CURRENT SCHEMA is the authorization ID of the current session user. If you wish to change this during the current session, you can use the SET SCHEMA statement to set the special register to another schema name. Refer to the SQL Reference for more information.
As described in Definition of System Catalog Tables, some objects are created within certain schemas when the database is created.
In dynamic SQL statements, a schema qualified object name implicitly uses the CURRENT SCHEMA special register value as the qualifier for unqualified object name references. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified database object names.
Before creating your own objects, you need to consider whether you want to create them in your own schema or by using a different 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 multi-processor (SMP) machine.
The Control Center can be used to find out, or modify, the values of individual entries in a specific database, or in the database manager configuration file.
You could also 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. See Chapter 32, Configuring DB2 for more information on configuration parameters.
In order for intra-partition query parallelism to occur, you must modify database configuration parameters and database manager configuration parameters.
See Chapter 32, Configuring DB2 for more information on the configuration parameter settings, and how to enable applications to process in parallel.
Inter-partition parallelism occurs automatically based on the number of database partitions and the distribution of data across these partitions.
This section provides an overview of how to enable intra-partition parallelism for the following utilities:
Inter-partition parallelism for utilities occurs automatically based on the number of database partitions.
The Load utility automatically makes use of parallelism, or you can use the following parameters on the LOAD command:
Refer to the Data Movement Utilities Guide and Reference for information about the LOAD command.
You can enable multiple split processes for the AutoLoader by specifying the MODIFIED BY ANYORDER parameter for the LOAD specification in the autoloader.cfg file. For more information, refer to Data Movement Utilities Guide and Reference.
To enable parallelism when creating an index:
Refer to the SQL Reference for information on the CREATE INDEX statement.
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, refer to the Command Reference and Administrative 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. All federated database objects (wrappers, servers, nicknames, etc.) are stored in the system catalog tables at this node.
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 partitioned 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.
See Chapter 32, Configuring DB2 for more information on configuration parameters.
To enable I/O parallelism when backing up a database or table space:
Also, use a backup buffer size that is:
Refer to the Command Reference for information on the BACKUP DATABASE command.
To enable I/O parallelism when restoring a database or table space:
Also, use a restore buffer size that is:
Refer to the Command Reference for information on the RESTORE DATABASE command.
The db2stop 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.
| Note: | If command line processor sessions are attached to an instance, you must run the terminate command to end each session before running the db2stop command. The db2stop command stops the instance defined by the DB2INSTANCE environment variable. |
To stop a DB2 instance on your system, you must do the following:
. INSTHOME/sqllib/db2profile (for Bourne or Korn shell) source INSTHOME/sqllib/db2cshrc (for C shell)
where INSTHOME is the home directory of the instance you want to use.
|
To stop the instance using the command line, enter:
db2stop