Understanding DB2: Learning Visually with Examples (2nd Edition)
5.3. The DB2 Instance
From a user's perspective, a DB2 instance provides an independent environment where database objects can be created and applications can run. Several instances can be created on one server, and each instance can have a multiple number of databases, as illustrated in Figure 5.5. Figure 5.5. A DB2 instance from a user's perspective
Because of these independent environments, one instance cannot "see" the contents of another instance; therefore, objects of two or more different instances can have the same name. In Figure 5.5, the database called MYDB1 is associated with instance Development, and another database also called MYDB1 is associated with instance Test. Instances allow users to have different environments for production, test, and development purposes. In addition, independent environments let you perform instance and database operations without affecting other instances. For example, if you stop and start the instance Test, the other two instances are not affected. From an architectural perspective, an instance serves as a layer between the DB2 binary code and the user database objects. It is important to understand that this is just an association of the DB2 code to the database objects. There is a common misconception among new DB2 users that dropping an instance also drops the databases associated to that instance; this is not necessarily true. When an instance is dropped, the association to the user databases is broken, but it can later be reestablished, as discussed in Chapter 2, DB2 at a Glance: The Big Picture. Figure 5.6 shows an example of two instances in a Linux/UNIX environment. Databases MarketDB and SalesDB are associated to instance #1. Databases TestDB and ProdDB are associated to instance #2. Each instance has its own configuration files. In this example, both instances are pointing to the same DB2 binary code for Version 8.2 using soft links. On Linux and UNIX, a soft link behaves like an alias to another file. Soft links are also referred to as symbolic links or logical links. Figure 5.6. The DB2 instance in Linux/UNIX from an architectural perspective
NOTE On Linux and UNIX, soft links are used as pointers from the instance sqllib directory to the DB2 binary code. On Windows, there is a shared install path and all instances access the same libraries and executables. 5.3.1. Creating DB2 Instances
When you install DB2 on the Windows platform, an instance called DB2 is created by default. On Linux and UNIX you can choose to create the default instance during the installation, change the instance owner's name, or not create an instance so that you can create one later. If you choose to create the default instance on these platforms, this instance is named db2inst1. DB2 will create an operating system user with the same name as the instance. This user is known as the instance owner. You can also create new, additional instances on your server using the db2icrt command. Figure 5.7 summarizes the db2icrt command and provides examples. Figure 5.7. The db2icrt command
On Windows the db2icrt command can be run by a user with Local Administrator authority. The command creates a subdirectory under the SQLLIB directory with the name of the instance just created. In addition, a Windows service DB2 - instance_ name will be created. On Linux and UNIX you must have root authority or else you need to have the system administrator run the db2icrt command for you. You can either use the fully qualified path name to the program or change into the directory to run this command as shown below:
or
In addition, on Linux and UNIX, the instance name must match an existing operating system user ID, which becomes the instance owner. This operating system user must exist prior to executing the db2icrt command. The db2icrt command will create the subdirectory sqllib under the home directory of this user. DB2 on Linux and UNIX also requires a fenced user to run stored procedures and user-defined functions (UDFs) as fenced resources, that is, in a separate address space other than the one used by the DB2 engine. This ensures that problems with these objects do not affect your database or instance. If you are not concerned about this type of problems, you can use the same ID for the fenced user and the instance owner. NOTE The terms instance and a DB2 instance are used interchangeably. On Windows, the default name of the DB2 instance is DB2. This sometimes confuses new DB2 users. 5.3.2. Creating DB2 64-bit Instances
You need to have the correct DB2 version and operating system to create 64-bit instances. At this time only AIX 5L, HP-UX, and the Solaris Operating Environment support this. To create a 64-bit instance, include the -w option. For example: db2icrt -w 64 -u db2fenc1 my64inst creates a 64-bit instance called my64inst and uses a fenced id of db2fenc1. 5.3.3. Creating Client Instances
In general, when we talk about instances in this book we are referring to server instances: fully functional instances created at the DB2 server where your database resides. There are other types of instances that can be created. One of them, the client instance, is a scaled down version of a server instance. A client instance cannot be started or stopped, and databases cannot be created in this type of instance. You create a DB2 client instance using the -s option. For example: db2icrt -s CLIENT myclinst creates the client instance myclinst. On Linux/UNIX, the operating system user myclinst must exist before executing this command. On Windows, an instance does not map to a user ID, so this would not be a requirement. On a Windows client, the entire machine is considered the DB2 client regardless of the user. On a Linux/UNIX client machine the DB2 client is associated to an operating system user. You need to have a client instance if you have two physically separate Linux/UNIX machines, one containing the DB2 client code (assume it is an application server machine) and the other one containing the DB2 server code (the DB2 server machine). Although the client machine contains the DB2 client code, a client instance must be created that will associate it to a given operating system user. Logging on as this user lets you perform the commands required to set up connectivity to the DB2 server machine. In this example, if the client and server resided on the same machine, there would be no need to create a client instance, because the operating system user used as the client can "source" the instance owner profile in sqllib/db2profile as described in section 5.2.1, Environment Variables. 5.3.4. Creating DB2 Instances in a Multi-Partitioned Environment
In a multi-partitioned environment, an instance is only created once: in the machine where the disks to be shared by the other partitions reside. The instance owner's home directory is then exported to all the servers participating in the multi-partitioned environment (see Chapter 2, DB2 at a Glance: The Big Picture). NOTE You can only create a multi-partitioned database if you have DB2 UDB Enterprise Server Edition (ESE) installed and you have purchased the database partitioning feature (DPF). The DPF is a paper-only license that you need to acquire; you do not need to install any additional products to use this feature. 5.3.5. Dropping an Instance
You can drop an instance if you no longer require it. Before you drop an instance, make sure that it is stopped, and that all memory and inter-process communications (IPCs) owned by the instance have been released. You can then run the db2idrop command to drop the DB2 instance. For example, to drop the instance myinst, use the command: db2idrop myinst
NOTE On Linux and UNIX, you can use the ipclean command to remove all IPCs associated with the ID that runs the command.
5.3.6. Listing the Instances in Your System
You can list all instances on your server using the db2ilist command. On Windows you can run this command from any Command Window. On Linux and UNIX you need to change into the path where DB2 was installed to run this command. Alternatively, you can list your instances using the DB2 Control Center. Figure 5.8 shows the steps that are needed.
Figure 5.8. Adding instances to the Control Center
Clicking on Discover displays a list of all available instances. You can then select the desired instance(s) to add to the Control Center. NOTE You cannot create an instance from the Control Center. You can only add an existing instance to the Control Center so it can be displayed and managed more easily with this tool. 5.3.7. The DB2INSTANCE Environment Variable
The environment variable DB2INSTANCE determines the active instance. It is particularly important to have this variable set correctly when you have multiple instances in the same DB2 server. For example, if you have two instances, myinst1 and myinst2, and DB2INSTANCE is set to myinst2, any command you execute will be directed to the myinst2 instance. Because DB2INSTANCE is an operating system environment variable, you set its value like any other environment variable for your operating system, as discussed in section 5.2.1, Environment Variables. Figure 5.9 illustrates setting the DB2INSTANCE environment variable temporarily in the Windows platform using the set operating system command. It also illustrates the methods used to determine its current value. Figure 5.9. Working with the DB2INSTANCE variable
The get instance command works on any platform. The other methods were described in section 5.2.1, Environment Variables. 5.3.8. Starting a DB2 Instance
An instance must be started to work with it. You can choose to start the instance manually or automatically every time you reboot your machine. To start an instance manually, use the db2start command. On Windows, since DB2 instances are created as services, you can also start an instance manually using the NET START command. To start an instance automatically on Windows, look for the service corresponding to the DB2 instance by opening the Control Panel, choosing the Administration Tools folder, and then double-clicking on Services. A Services window similar to the one displayed in Figure 5.10 will appear. Figure 5.10. Windows services for DB2
Several DB2 services are listed in Figure 5.10. All of the DB2 services can be easily identified as they are prefixed with DB2. For example, the service DB2 - MYINST represents the instance MYINST. The service DB2 - DB2-0 represents the instance named DB2 (highlighted in the figure). The 0 in the service name represents the partition number. As you can see from the figure, this service is set up to be manually started, so you would need to execute a db2start command every time the system is restarted for the DB2 instance to be able to work with your databases. You can set up the instance to be automatically started by right-clicking on the DB2 - DB2-0 service and choosing Properties from the drop-down menu. Once the Properties panel appears, you can change the Startup type from Manual to Automatic (see Figure 5.11). Figure 5.11. Service properties for the instance DB2
On Linux and UNIX, to automatically start the DB2 instance every time the server is started, use the db2iauto command. To set up the db2inst1 instance to be started automatically, run the command: db2iauto on db2inst1 NOTE If you are setting up your system for High Availability failover, you should not set up the instance to be started automatically. When you run the db2start command on your Linux or UNIX server, a number of processes start. If you run the ps ef command you will notice a db2sysc process that is associated with the instance owner's ID. This is the DB2 main system controller for the instance. DB2 processes are discussed in more detail in Chapter 14, The DB2 Process Model. In a multi-partitioned environment you only need to run the db2start command once, and it will start all of the partitions defined in the db2nodes.cfg file. Notice in the output of the db2start command below that there is one message returned for each partition, and each message has the partition number associated with it in the third column. Since the instances are started in parallel, they are not likely to complete in the order specified in the db2nodes.cfg file. db2inst1@aries db2inst1]$ db2start 01-14-2005 14:42:26 1 0 SQL1063N DB2START processing was successful. 01-14-2005 14:42:26 0 0 SQL1063N DB2START processing was successful. 01-14-2005 14:42:26 2 0 SQL1063N DB2START processing was successful. 01-14-2005 14:42:26 3 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. There may be times when a database administrator needs to be the only user attached to an instance to perform maintenance tasks. In these situations, use the db2start option admin mode user userId so only one user has full control of the instance. You can also do this from the Control Center by right-clicking on the desired instance in the Object Tree and choosing Start Admin. 5.3.9. Stopping a DB2 Instance
You can use the db2stop command to stop a DB2 instance that is currently running. Verify that the DB2INSTANCE environment variable is correctly set before issuing this command, as discussed in section 5.2.1, Environment Variables. On Windows, since the DB2 instances are created as services, you can also stop the instances using the NET STOP command or stop the service from the Control Panel. To stop an instance from the Control Panel on Windows, right-click on the service and select Stop from the drop-down menu. Once the service is stopped the Status column will be blank, as the highlighted line shows in Figure 5.12. Figure 5.12. A stopped instance
You will not be able to stop the instance if there is a database that is active in the instance or if there are databases with one or more connections. You must first deactivate the database and/or reset the connections. In many cases you will have a large number of DB2 client machines running applications that connect to the database server, and you will not be able to go to each machine to close the application to terminate the connection. In this case you can use the force option with the db2stop command to force off all active connections and/or activations to stop the instance: db2stop force NOTE A db2stop force command has the same effect as issuing the force applications all command followed by the db2stop command. However, db2stop force prevents new connections from happening while the instance is being stopped. The force applications command is discussed in detail in Chapter 11, Understanding Concurrency and Locking.
In a multi-partitioned environment you only need to run the db2stop command once, and it will stop all of the partitions defined in the db2nodes.cfg file. NOTE In many DB2 customer environments, the process of issuing a db2stop followed by a db2start command is called one or more of the following:
5.3.10. Attaching to an Instance
To perform instance-level maintenance tasks, you first need to attach to the instance with the attach command. Some instance-level operations are
Users often confuse attaching to an instance and connecting to a database. When in doubt as to which one to use, determine if the operation is to affect the instance or a particular database. For example, the list applications command lists all the applications connected to all the databases in your active instance. This is not an operation that you would perform at the database level, since you want to list all connections to all databases, so an attachment is what is required in this case. (Chapter 6, Configuring Client and Server Connectivity, discusses setting up database connections in detail. In that chapter we describe the node directory, which is used to encapsulate connectivity information, such as the hostname of a remote DB2 database server and the port number of the instance.) NOTE Attachments are only applicable at the instance level; connections are only applicable at the database level. When you attach to an instance, it can be a local instance or a remote one, and there will be corresponding entries for each in the node directory. A local instance resides on the same machine where you issue the attach command, while a remote instance resides on some other machine. Other than the active instance specified in the DB2INSTANCE variable, DB2 will look for connectivity information in the node directory for any other instance. The syntax to attach to the active instance is: attach to instance_name_as_indicated_in_DB2INSTANCE
For example: attach to DB2 To attach to a local or remote instance that is not your active instance, use: attach to node_name [user userId] [using password]
For example: attach to mynode user peter using myudbpsw where mynode is an entry in the node directory. Attaching to the active instance (as specified in DB2INSTANCE) is normally done implicitly. However, there are special occasions where you do need to explicitly attach to the active instance, as you will see in following sections. To detach from the current attached instance, issue the detach command: attach to mynode detach
5.3.11. Configuring an Instance
You can set DB2 configuration parameters at the instance level (also known as the database manager level) and at the database level. At the instance level, variables are stored in the Database Manager (DBM) Configuration file. Changes to these variables affect all databases associated to this instance. At the database level, variables are stored in the Database Configuration file. Changes to these variables only affect that specific database. In this section we discuss the DBM Configuration file in detail. When you install DB2 and create an instance, the instance is assigned a default DBM configuration. You can view this configuration by running the get dbm cfg command. Figure 5.13 shows the output of this command on a Windows machine. Figure 5.13. Contents of the DBM Configuration file
C:\Program Files\SQLLIB\BIN>db2 get dbm cfg Database Manager Configuration Node type = Database Server with local and remote clients Database manager configuration release level = 0x0a00 Maximum total of files open (MAXTOTFILOP) = 16000 CPU speed (millisec/instruction) (CPUSPEED) = 1.113945e-006 Max number of concurrently active databases (NUMDB) = 8 Data Links support (DATALINKS) = NO Federated Database System Support (FEDERATED) = NO Transaction processor monitor name (TP_MON_NAME) = Default charge-back account (DFT_ACCOUNT_STR) = Java Development Kit installation path (JDK_PATH) = C:\PROGRA~1\SQLLIB\ja va\jdk Diagnostic error capture level (DIAGLEVEL) = 3 Notify Level (NOTIFYLEVEL) = 3 Diagnostic data directory path (DIAGPATH) = Default database monitor switches Buffer pool (DFT_MON_BUFPOOL) = OFF Lock (DFT_MON_LOCK) = OFF Sort (DFT_MON_SORT) = OFF Statement (DFT_MON_STMT) = OFF Table (DFT_MON_TABLE) = OFF Timestamp (DFT_MON_TIMESTAMP) = ON Unit of work (DFT_MON_UOW) = OFF Monitor health of instance and databases (HEALTH_MON) = ON SYSADM group name (SYSADM_GROUP) = SYSCTRL group name (SYSCTRL_GROUP) = SYSMAINT group name (SYSMAINT_GROUP) = SYSMON group name (SYSMON_GROUP) = Client Userid-Password Plugin (CLNT_PW_PLUGIN) = Client Kerberos Plugin (CLNT_KRB_PLUGIN) = IBMkrb5 Group Plugin (GROUP_PLUGIN) = GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) = Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) = Server Userid-Password Plugin (SRVCON_PW_PLUGIN) = Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED Database manager authentication (AUTHENTICATION) = SERVER Cataloging allowed without authority (CATALOG_NOAUTH) = NO Trust all clients (TRUST_ALLCLNTS) = YES Trusted client authentication (TRUST_CLNTAUTH) = CLIENT Bypass federated authentication (FED_NOAUTH) = NO Default database path (DFTDBPATH) = C: Database monitor heap size (4KB) (MON_HEAP_SZ) = 66 Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 512 Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0 Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC Backup buffer default size (4KB) (BACKBUFSZ) = 1024 Restore buffer default size (4KB) (RESTBUFSZ) = 1024 Agent stack size (AGENT_STACK_SZ) = 16 Minimum committed private memory (4KB) (MIN_PRIV_MEM) = 32 Private memory threshold (4KB) (PRIV_MEM_THRESH) = 20000 Sort heap threshold (4KB) (SHEAPTHRES) = 10000 Directory cache support (DIR_CACHE) = YES Application support layer heap size (4KB) (ASLHEAPSZ) = 15 Max requester I/O block size (bytes) (RQRIOBLK) = 32767 DOS requester I/O block size (bytes) (DOS_RQRIOBLK) = 4096 Query heap size (4KB) (QUERY_HEAP_SZ) = 1000 Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10 Priority of agents (AGENTPRI) = SYSTEM Max number of existing agents (MAXAGENTS) = 200 Agent pool size (NUM_POOLAGENTS) = 100(calculated) Initial number of agents in pool (NUM_INITAGENTS) = 0 Max number of coordinating agents (MAX_COORDAGENTS) = MAXAGENTS Max no. of concurrent coordinating agents (MAXCAGENTS) = MAX_COORDAGENTS Max number of client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS Keep fenced process (KEEPFENCED) = YES Number of pooled fenced processes (FENCED_POOL) = MAX_COORDAGENTS Initial number of fenced processes (NUM_INITFENCED) = 0 Index re-creation time and redo index build (INDEXREC) = RESTART Transaction manager database name (TM_DATABASE) = 1ST_CONN Transaction resync interval (sec) (RESYNC_INTERVAL) = 180 SPM name (SPM_NAME) = RAULCHO1 SPM log size (SPM_LOG_FILE_SZ) = 256 SPM resync agent limit (SPM_MAX_RESYNC) = 20 SPM log path (SPM_LOG_PATH) = NetBIOS Workstation name (NNAME) = TCP/IP Service name (SVCENAME) = Discovery mode (DISCOVER) = SEARCH Discover server instance (DISCOVER_INST) = ENABLE Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY Enable intra-partition parallelism (INTRA_PARALLEL) = NO No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 1024 Number of FCM request blocks (FCM_NUM_RQB) = AUTOMATIC Number of FCM connection entries (FCM_NUM_CONNECT) = AUTOMATIC Number of FCM message anchors (FCM_NUM_ANCHORS) = AUTOMATIC
Note that the Node type entry field at the top of the output identifies the type of instance. For example, in Figure 5.13 this field has the value Database Server with local and remote clients. This means it is a server instance. For a client instance the value of this field would be Client. In this book you will learn some of the more important parameters for the DBM Configuration file. For a full treatment of all DBM Configuration parameters, refer to the DB2 UDB Administration Guide: Performance. To update one or more parameters in the DBM Configuration file, issue the command: update dbm cfg using parameter_name value parameter_name value ...
For example, to update the INTRA_PARALLEL DBM Configuration parameter, issue the command: update dbm cfg using INTRA_PARALLEL YES Issuing the get dbm cfg command after the update dbm cfg command shows the newly updated values. However, this does not mean that the change will take effect right away. Several parameters in the DBM Configuration file require a db2stop followed by a db2start for the new values to be used. For other parameters, the update is dynamic, so a db2stop/db2start is not required as the new value takes effect immediately. These parameters are called configurable online parameters. If you are updating a configuration parameter of a DB2 client instance, the new value takes effect the next time you restart the client application or if the client application is the CLP, after you issue the terminate command. NOTE Configurable online parameters of the DBM Configuration file can be updated dynamically only if you first explicitly attach to the instance. This also applies to local instances. If you have not performed an attach, the parameter won't be changed until you perform a db2stop/db2start.
Refer to the file ConfigurationParameters.pdf included on the CD-ROM accompanying this book for a list of DBM Configuration parameters that are configurable online. The Control Center provides this information as well. Refer to section 5.3.12, Working with an Instance from the Control Center, for details. To get the current, effective setting for each configuration parameter and the value of the parameter the next time the instance is stopped and restarted, use the show detail option of the get dbm cfg command. This option requires an instance attachment. If you run this command after changing the INTRA_PARALLEL configuration parameter as above, you will see that the current value is NO, but the next effective or delayed value is YES. The related output from the get dbm cfg show detail command would look like the following: C:\Program Files\SQLLIB\BIN>db2 get dbm cfg show detail Description Parameter Current Value Delayed Value ------------------------------------------------------------------------------------- ... Enable intra-partition parallelism (INTRA_PARALLEL) = NO YES
The show detail option is also helpful for determining the actual value of parameters listed as AUTOMATIC. For example, when you issue the get dbm cfg command while attached to an instance, you may see output as follows for the INSTANCE_MEMORY parameter: C:\Program Files\SQLLIB\BIN>db2 get dbm cfg ... Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC
If you use the show detail option, the actual value is displayed: C:\Program Files\SQLLIB\BIN>db2 get dbm cfg show detail Description Parameter Current Value Delayed Value ------------------------------------------------------------------------------------- ... Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(8405) AUTOMATIC(8405) To reset all the DBM Configuration parameters to their default value, use the command reset dbm cfg. 5.3.12. Working with an Instance from the Control Center
The instance operations described in the previous sections can also be performed from the Control Center. Figure 5.14 shows the Control Center with the instance MYINST selected. When you right-click on the instance, a menu with several options displays. Figure 5.14 highlights some of the menu items that map to the instance operations we have already described. Figure 5.14. Performing instance operations from the Control Center
Figure 5.15 shows the DBM Configuration window that appears after selecting Configure Parameters from the menu shown in Figure 5.14. In Figure 5.15, the column Pending Value Effective indicates when the pending value for the parameter will take effect; for example, immediately or after the instance is restarted. The column Dynamic indicates whether the parameter is configurable online or not. The rest of the columns are self-explanatory. Figure 5.15. Configuring an instance from the Control Center
Figure 5.15 also illustrates how you can update a Database Manager Configuration parameter from the Control Center. For example, after selecting the parameter FEDERATED and clicking on the three dots button (...), a pop-up window displays that lists the possible values this parameter can accept. Choose the desired option and click OK. 5.3.13. The DB2 Commands at the Instance Level
Table 5.2 summarizes the most common DB2 commands used at the instance level.
|