Learning Visually with Examples [Electronic resources]

Figure 5.5. A DB2 instance from a user's perspective

Figure 5.6. The DB2 instance in Linux/UNIX from an architectural perspective

Figure 5.7. The db2icrt command

[View full size image]

Figure 5.8. Adding instances to the Control Center

[View full size image]

Figure 5.9. Working with the DB2INSTANCE variable

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.

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).

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 

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.

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 

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:

• Recycling the instance

• Bringing the instance down and up

• Stopping and (re)starting the instance

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

• Listing applications connected to your databases

• Forcing off applications

• Monitoring a database

• Updating the Database Manager Configuration parameters

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 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.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 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