In a partitioned database environment, inter-partition parallelism for data loading occurs automatically when the target table is defined on multiple partitions. Inter-partition parallelism for data loading can be overridden by specifying OUTPUT_ DBPARTNUMBS The load utility also intelligently enables data partitioning parallelism depending on the size of the target partitions. MAX NUM_PART- AGENTS can be used to control the maximum degree of parallelism selected by the Load utility. Data partitioning parallelism can be overridden by specifying PARTITIONING_DBPARTNUMS when ANYORDER is also specified Related concepts Load Overview"in the Data Mouement Utilities Guide and reference Loading Data in a Partitioned Database -Overview"in the Data Movement Utilities Guide and Reference Enabling parallelism when creating indexes: To enable parallelism when ting The intra_parallel database manager configuration parameter must be ON The table must be large enough to benefit from parallelism Multiple processors must be enabled on an SMP machine Related reference: Enable Intra-Partition Parallelism configuration parameter-intra_parallel in the Administration Guide: Performance "CREATE INDEX statement"in the SQL Reference, Volume 2 Enabling I/o parallelism when backing up a database or table space: To enable I/O parallelism when backing up a database or table space one target media Configure table spaces for parallel I/o by defining multiple containers, or use a single container with multiple disks, and the appropriate use of the DB2- PARALLEL_IO registry variable. If you want to take advantage of parallel I/O, you must consider the implications of what must be done before you define any containers. This cannot be done whenever you see a need; it must be planned for before you reach the point where you need to backup your database or table space Use the PARAllElISM parameter on the BaCKUP command to specify the degree of parallelism. Use the WITH num-buffers BUFFERS Parameter on the BACKUP command to ensure enough buffers are available to accommodate the degree of parallelism. The number of buffers should equal the number of target media you have plus the degree of parallelism selected plus a few extra Also, use a backup buffer size that is 2 Adr tion Guide: Implementatie
In a partitioned database environment, inter-partition parallelism for data loading occurs automatically when the target table is defined on multiple partitions. Inter-partition parallelism for data loading can be overridden by specifying OUTPUT_DBPARTNUMBS. The load utility also intelligently enables data partitioning parallelism depending on the size of the target partitions. MAX_NUM_PART_AGENTS can be used to control the maximum degree of parallelism selected by the Load utility. Data partitioning parallelism can be overridden by specifying PARTITIONING_DBPARTNUMS when ANYORDER is also specified. Related concepts: v “Load Overview” in the Data Movement Utilities Guide and Reference v “Loading Data in a Partitioned Database - Overview” in the Data Movement Utilities Guide and Reference Enabling parallelism when creating indexes: To enable parallelism when creating an index: v The intra_parallel database manager configuration parameter must be ON v The table must be large enough to benefit from parallelism v Multiple processors must be enabled on an SMP machine. Related reference: v “Enable Intra-Partition Parallelism configuration parameter - intra_parallel” in the Administration Guide: Performance v “CREATE INDEX statement” in the SQL Reference, Volume 2 Enabling I/O parallelism when backing up a database or table space: To enable I/O parallelism when backing up a database or table space: v Use more than one target media. v Configure table spaces for parallel I/O by defining multiple containers, or use a single container with multiple disks, and the appropriate use of the DB2_PARALLEL_IO registry variable. If you want to take advantage of parallel I/O, you must consider the implications of what must be done before you define any containers. This cannot be done whenever you see a need; it must be planned for before you reach the point where you need to backup your database or table space. v Use the PARALLELISM parameter on the BACKUP command to specify the degree of parallelism. v Use the WITH num-buffers BUFFERS parameter on the BACKUP command to ensure enough buffers are available to accommodate the degree of parallelism. The number of buffers should equal the number of target media you have plus the degree of parallelism selected plus a few extra. Also, use a backup buffer size that is: 12 Administration Guide: Implementation
As large as feasible. 4 MB or 8 MB(1024 or 2048 pages) is a good rule of thumb At least as large as the largest (extentsize number of containers product of the table spaces being backed up Related reference: BACKUP DATABASE"in the Command Reference enable I/O parallelism when restoring a database or table space,. Enabling 1O parallelism when restoring a database or table space: To Use more than one source media Configure table spaces for parallel I/O. You must make the decision to use this option before you define your containers. This cannot be done whenever you see a need; it must be planned for before you reach the point where you need to restore your database or table space Use the PARALLELISM parameter on the RESTORE command to specify the degree of parallelism Use the WITH num-buffers BUFFERS Parameter on the RESTORE command to ensure enough buffers are available to accommodate the degree of parallelism. The number of buffers should equal the number of target media you have plus the degree of parallelism selected plus a few extra Also, use a restore buffer size that is As large as feasible. 4 MB or8 MB(1024 or 2048 pages)is a good rule of thumb At least as large as the largest(extentsize number of containers product of the table spaces being restored The same as, or an even multiple of, the backup buffer size. Related reference RESTORE DATABASE" in the Command Reference Enabling data partitioning in a database The decision to have your database working in a partitioned environment must be made before you create your database. As part of the database design decisions you make, you will have to determine if you should take advantage of the performance improvements through partitioning your database Some of the considerations surrounding your decision to create a partitioned database are made here Pr rocedure Chapter 1. Before Creating a Database 13
– As large as feasible. 4 MB or 8 MB (1024 or 2048 pages) is a good rule of thumb. – At least as large as the largest (extentsize * number of containers) product of the table spaces being backed up. Related reference: v “BACKUP DATABASE” in the Command Reference Enabling I/O parallelism when restoring a database or table space: To enable I/O parallelism when restoring a database or table space: v Use more than one source media. v Configure table spaces for parallel I/O. You must make the decision to use this option before you define your containers. This cannot be done whenever you see a need; it must be planned for before you reach the point where you need to restore your database or table space. v Use the PARALLELISM parameter on the RESTORE command to specify the degree of parallelism. v Use the WITH num-buffers BUFFERS parameter on the RESTORE command to ensure enough buffers are available to accommodate the degree of parallelism. The number of buffers should equal the number of target media you have plus the degree of parallelism selected plus a few extra. Also, use a restore buffer size that is: – As large as feasible. 4 MB or 8 MB (1024 or 2048 pages) is a good rule of thumb. – At least as large as the largest (extentsize * number of containers) product of the table spaces being restored. – The same as, or an even multiple of, the backup buffer size. Related reference: v “RESTORE DATABASE” in the Command Reference Enabling data partitioning in a database The decision to have your database working in a partitioned environment must be made before you create your database. As part of the database design decisions you make, you will have to determine if you should take advantage of the performance improvements through partitioning your database. Some of the considerations surrounding your decision to create a partitioned database are made here. Procedure: Chapter 1. Before Creating a Database 13
When running in a partitioned database environment, you can create a database from any node that exists in the db2nodes. cfg file using the CREATE DATABASE command or the sqlecreaO application programming interface (API Before creating a pa tioned database, you must select which database partition will be talog node for the database. You can then create the database directly from that partition, or from a remote client that is attached to that partition. The database partition to which you attach and execute the CREATE DATABASE command becomes the catalog node for that particular database he 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 not possible(that is, you must create more than one database per instance), ou should spread the catalog nodes among the available database partitions Doing this reduces contention for catalog information at a single database tuition Note: You should regularly do a backup of the catalog node and avoid putting user 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. When working on UNIX, the system database directory is sqldbdir and is located in the sql lib directory under your home directory, or under the directory where DB2 was installed. When working on UNIX, 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. When working on Windows, he system database directory is located in the instance directory 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 14Administration Guide: Implementation
When running in a partitioned database 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). Before creating a partitioned database, you must select which database partition will be the catalog node for the database. You can then create the database directly from that partition, or from a remote client that is attached to that partition. 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 user 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. When working on UNIX, the system database directory is sqldbdir and is located in the sqllib directory under your home directory, or under the directory where DB2 was installed. When working on UNIX, 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. When working on Windows, the system database directory is located in the instance directory. 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. 14 Administration Guide: Implementation
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 onfiguration 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, fam_num_anchors, fcmnum- buffers, fcm_num_connect, fcm. qb, max_connretries, max_coordagents, max__time_diff, num_poolagents, and stop_start_time Related tasks: Configuring DB2 with configuration parameters"in the Administration Related reference: sqlecrea-Create Database"in the Administrative API Reference "CREATE DATABASE"in the Command Reference Stopping a DB2 instance on UNIX You may need to stop the current instance of the database manager(DB2) Prerequisites: To stop a DB2 instance on your system, you must do the following: 1. Log in or attach to an instance with a user ID or name that has SYSADM SYSCTRL, or SYSMAINT authority on the instance; or, log in as the nstance owner Display all applications and users that are connected to the specific database that you want to stop. To ensure that no vital or critical applications are running, list applications. You need SYSADM, SYSCTRL or SySmaint authority for this 3. Force all applications and users off the database. You require SYSADM or SYSCTRL authority to force users Restrictions. are allowed when running this command; however, if there are any instance s The db2stop command can only be run at the server. No database connection attachments, they are forced off before DB2 is stopped Chapter 1.Before Creating a Database 15
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. Related tasks: v “Configuring DB2 with configuration parameters” in the Administration Guide: Performance Related reference: v “sqlecrea - Create Database” in the Administrative API Reference v “CREATE DATABASE” in the Command Reference Stopping a DB2 instance on UNIX You may need to stop the current instance of the database manager (DB2). Prerequisites: To stop a DB2 instance on your system, you must do the following: 1. Log in or attach to an instance with a user ID or name that has SYSADM, SYSCTRL, or SYSMAINT authority on the instance; or, log in as the instance owner. 2. Display all applications and users that are connected to the specific database that you want to stop. To ensure that no vital or critical applications are running, list applications. You need SYSADM, SYSCTRL, or SYSMAINT authority for this. 3. Force all applications and users off the database. You require SYSADM or SYSCTRL authority to force users. Restrictions: 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. Chapter 1. Before Creating a Database 15
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. Procedure: stop 1. To stop the instance using the Control Center a.Expand the object tree until you find the Instances folder b. Click each instance you want to stop C. Right-click any of the selected instances, and select stop from the pop-up menu. d.On the Confirm stop window, click OK. 2. To stop the instance using the command line, enter: dstop You can use the db2stop command to stop, or drop, individual partitions within a partitioned database environment. When working in a partitioned database and you are attempting to drop a logical partition using db2stop drop nodenum <0> you must ensure that no users are attempting to access the database. If they are, you will receive an error message SQL6030N Related reference: db2stop-Stop DB2"in the Command Reference TERMINATE" in the Command Reference Stopping a DB2 instance on Windows You may need to stop the current instance of the database manager(DB2) Prerequisite To stop a DB2 instance on your system, you must do the following 1. The user account stopping the DB2 service must have the correct privilege as defined by the Windows operating system. The user account can be a member of the Administrators, Server Operators, or Power Users group Display all applications and users that are connected to the specifi database that you want to stop. To ensure that no vital or critical applications are running, list applications. You need SYSADM, SYSCTRL or SYsmaint authority for this 16 Administration Guide: Implementation
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. Procedure: Use one of these two methods to stop the instance: 1. To stop the instance using the Control Center: a. Expand the object tree until you find the Instances folder. b. Click each instance you want to stop. c. Right-click any of the selected instances, and select stop from the pop-up menu. d. On the Confirm stop window, click OK. 2. To stop the instance using the command line, enter: db2stop You can use the db2stop command to stop, or drop, individual partitions within a partitioned database environment. When working in a partitioned database and you are attempting to drop a logical partition using db2stop drop nodenum <0> you must ensure that no users are attempting to access the database. If they are, you will receive an error message SQL6030N. Related reference: v “db2stop - Stop DB2” in the Command Reference v “TERMINATE” in the Command Reference Stopping a DB2 instance on Windows You may need to stop the current instance of the database manager (DB2). Prerequisites: To stop a DB2 instance on your system, you must do the following: 1. The user account stopping the DB2 service must have the correct privilege as defined by the Windows operating system. The user account can be a member of the Administrators, Server Operators, or Power Users group. 2. Display all applications and users that are connected to the specific database that you want to stop. To ensure that no vital or critical applications are running, list applications. You need SYSADM, SYSCTRL, or SYSMAINT authority for this. 16 Administration Guide: Implementation