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.
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:
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:
db2 attach to testdb2
will 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:
db2 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.
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 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 20. "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 20. "Configuring DB2".