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 tha can only be done at an instance level, such as creating a database, forcing off applications, monitoring a database, or updating the database manager onfiguration. 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 ith that instand Related concepts: Multiple instances on a UNIX operating system"on page 22 Multiple instances on a Windows operating system"on page 23 Related tasks: Related reference ATTACH"in the Command Reference Attaching to another instance of the database manager To attach to another instance, which may be remote, use the attACh command Prerequisites More than one instance must already exist. Procedure To attach to another instance of the database manager using the Control 1. Expand the object tree until you see the Instances folder 2. Click on the instance you want to attach. 3. Right-click the selected instance name 4.In the Attach-DB2 window, type your user ID and password, and click OK. To attach to an instance using the command line, enter Chapter 1.Before Creating a Database 7
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. Related concepts: v “Multiple instances on a UNIX operating system” on page 22 v “Multiple instances on a Windows operating system” on page 23 Related tasks: v “Creating additional instances” on page 24 Related reference: v “ATTACH” in the Command Reference Attaching to another instance of the database manager To attach to another instance, which may be remote, use the ATTACH command. Prerequisites: More than one instance must already exist. Procedure: To attach to another instance of the database manager using the Control Center: 1. Expand the object tree until you see the Instances folder. 2. Click on the instance you want to attach. 3. Right-click the selected instance name. 4. In the Attach-DB2 window, type your user ID and password, and click OK. To attach to an instance using the command line, enter: Chapter 1. Before Creating a Database 7
db2 attach to <instance name> For example, to attach to an instance called testdb2 that was previousl 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 Related reference: ATTACH"in the Command Reference DETACH"in the Command Reference Grouping objects by schema Database object names may be made up of a single identifier or they may be hema-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 Some objects are created within certain schemas and stored in the system catalog tables 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 8 Administration Guide: Implementation
db2 attach to <instance name> For example, to attach to an 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 Related reference: v “ATTACH” in the Command Reference v “DETACH” in the Command Reference Grouping objects by schema 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. Some objects are created within certain schemas and stored in the system catalog tables 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. 8 Administration Guide: Implementation
Before creating your own objects, you to consider whether you want to reate them in your own schema or a different schema that logically groups the objects. If you are creating that will be shared, using a different schema name can be very be Related concepts: Definition of system catalog tables"on page 75 Related tasks: Creating a schema"on page 92 Related reference: SET SCHEMA statement" in the SQL Reference, Volume 2 CURRENT SCHEMA sPecial register"in the SQL Reference, Volume 1 Parallelism You must modify configuration parameters to take advantage of parallelism within a database partition or within a non-partitioned database. For example ntra-partition parallelism can be used to take advantage of the multiple processors on a symmetric multi-processor(SMP)machine Enabling inter-partition query parallelism Procedure Inter-partition parallelism occurs automatically based on the number of database partitions and the distribution of data across these partitions Related concepts Partition and processor environments"in the Administration guide: Data partitioning" in the Administration Guide: Planning Database partition group design"in the Administration Guide: Planning Partitions in a partitioned database in the Administration guide Related tasks: Enabling intra-partition parallelism for queries"on page 10 Enabling data partitioning in a database"on page 13 Redistributing data across partitions"in the Administration Guide Performance Chapter 1. Before Creating a Database 9
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. Related concepts: v “Definition of system catalog tables” on page 75 Related tasks: v “Creating a schema” on page 92 Related reference: v “SET SCHEMA statement” in the SQL Reference, Volume 2 v “CURRENT SCHEMA special register” in the SQL Reference, Volume 1 Parallelism 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. Enabling inter-partition query parallelism Procedure: Inter-partition parallelism occurs automatically based on the number of database partitions and the distribution of data across these partitions. Related concepts: v “Partition and processor environments” in the Administration Guide: Planning v “Data partitioning” in the Administration Guide: Planning v “Database partition group design” in the Administration Guide: Planning v “Partitions in a partitioned database” in the Administration Guide: Performance Related tasks: v “Enabling intra-partition parallelism for queries” on page 10 v “Enabling data partitioning in a database” on page 13 v “Redistributing data across partitions” in the Administration Guide: Performance Chapter 1. Before Creating a Database 9
Enabling intra-partition parallelism for queries Procedure: 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. In order for intra-partition query parallelism to occur, you must modify one or more database configuration parameters, database manager configuration parameters, precompile or bind options, or a special register. intra-parallel Database manager configuration parameter that specifies whether the database manager can use intra-partition parallelism. The default is not to use intra-partition parallelism 7iax-queryaegree Database manager configuration parameter that specifies the maximum degree of intra-partition parallelism that is used for any SQL statement running on this instance. An SQL statement will not use more than the number given by this parameter when running parallel operations within a partition. The intra-parallel configuration parameter must also be set to"YES"for the value in max-querydegree is used. The default value for this configuration parameter is -1. This value means that the system uses the degree of parallelism determined by the optimizer; otherwise, the user-specified value is dft- degre Database configuration parameter. Provides the default for the DEGREE bind option and the CURRENT DEGREE special register The default value is 1. A value of ANY means the system uses the degree of parallelism determined by the optimizer 10 Administration Guide: Implementation
Enabling intra-partition parallelism for queries Procedure: 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. In order for intra-partition query parallelism to occur, you must modify one or more database configuration parameters, database manager configuration parameters, precompile or bind options, or a special register. intra_parallel Database manager configuration parameter that specifies whether the database manager can use intra-partition parallelism. The default is not to use intra-partition parallelism. max_querydegree Database manager configuration parameter that specifies the maximum degree of intra-partition parallelism that is used for any SQL statement running on this instance. An SQL statement will not use more than the number given by this parameter when running parallel operations within a partition. The intra_parallel configuration parameter must also be set to “YES” for the value in max_querydegree is used. The default value for this configuration parameter is -1. This value means that the system uses the degree of parallelism determined by the optimizer; otherwise, the user-specified value is used. dft_degree Database configuration parameter. Provides the default for the DEGREE bind option and the CURRENT DEGREE special register. The default value is 1. A value of ANY means the system uses the degree of parallelism determined by the optimizer. 10 Administration Guide: Implementation
DEGREE Precompile or bind option for static SQL CURRENT DEGREE Special register for dynamic SQL. Related concepts: Parallel processing for applications" in the Administration Guide Performance Parallel processing information"in the Administration Guide: Performance Related tasks: Configuring DB2 with configuration parameters"in the Administration Guide: Performance Related reference: Maximum Query Degree of Parallelism configuration parameter max_-querydegree"in the Administration Guide: Performance Enable Intra-Partition Parallelism configuration parameter -intra_parallel in the Administration Guide: Performance Default Degree configuration parameter-dft_ degree" in the Administration BIND"in the Command Reference PRECOMPILE"in the Command Reference CURRENT DEGREE special register"in the SQL Reference, Volume 1 Enabling intra-partition parallelism for utilities This section provides an overview of how to enable intra-partition parallelism for the following utilities: Create index Backup database or table space Restore database or table space iter-partition parallelism for utilities occurs automatically based on the number of database partitions Enabling parallelism for loading data: The load utility automatically makes use of parallelism, or you can use the following parameters on the LOAD CPU PARALLELISM DISK PARALLELISM Chapter 1. Before Creating a Database 11
DEGREE Precompile or bind option for static SQL. CURRENT DEGREE Special register for dynamic SQL. Related concepts: v “Parallel processing for applications” in the Administration Guide: Performance v “Parallel processing information” in the Administration Guide: Performance Related tasks: v “Configuring DB2 with configuration parameters” in the Administration Guide: Performance Related reference: v “Maximum Query Degree of Parallelism configuration parameter - max_querydegree” in the Administration Guide: Performance v “Enable Intra-Partition Parallelism configuration parameter - intra_parallel” in the Administration Guide: Performance v “Default Degree configuration parameter - dft_degree” in the Administration Guide: Performance v “BIND” in the Command Reference v “PRECOMPILE” in the Command Reference v “CURRENT DEGREE special register” in the SQL Reference, Volume 1 Enabling intra-partition parallelism for utilities This section provides an overview of how to enable intra-partition parallelism for the following utilities: v Load v Create index v Backup database or table space v Restore database or table space Inter-partition parallelism for utilities occurs automatically based on the number of database partitions. Enabling parallelism for loading data: The load utility automatically makes use of parallelism, or you can use the following parameters on the LOAD command: v CPU_PARALLELISM v DISK_PARALLELISM Chapter 1. Before Creating a Database 11