Section 5.7. Case Study | Understanding DB2: Learning Visually with Examples (2nd Edition)

5.7. Case Study

You have just returned from a DB2 training class and would like to practice what you have learned by changing some DB2 registry variables and configuration parameters. The system you are working with has one instance called DB2 and two databases, mydb and testdb.

First, you save the contents of your configuration files by simply redirecting the output to files. This is just as a precaution in case you need to go back to your current configuration.
```
 db2set -all > db2set.bk db2 get dbm cfg > dbmcfg.bk db2 get db cfg for mydb > dbcfg_mydb.bk db2 get db cfg for testdb > dbcfg_testdb.bk set > environmentVariables.bk 
```
If you do need to go back to your current configuration, review the contents of these files and enter the appropriate commands to set your environment variables correctly (set/export), DBM Configuration file (update dbm cfg), database configuration file (update db cfg), and DB2 registry variables (db2set).
Then, you want to verify which instances have been created and which one is the current active one:
```
 db2ilist db2 get instance 
```
The first command lists all instances in your server; in this case, there is only one instance, DB2. The second command shows you that the DB2 instance is the current active instance.
Next, since you like to reuse CLP commands that you have typed before, you decide to increase the number of CLP commands that are kept as "history." You don't quite remember which DB2 registry variable has to be modified, so you issue the command:
```
 db2set -lr 
```
This lists all the DB2 registry variables you can set. You review the list and recognize the registry variable you need: DB2_CLP_HISTSIZE (discussed in Chapter 4, Using the DB2 Tools). You issue the command:
```
 db2set DB2_CLP_HISTSIZE=50 
```
This command sets the DB2_CLP_HISTSIZE registry variable only for the active instance because the -i option is the default.
You decide to make this change globally, so you issue:
```
 db2set DB2_CLP_HISTSIZE=50   -g 
```
You make sure that there are no spaces before and after the equal (=) sign to avoid getting an error.
You confirm that the registry variable is set by issuing the command:
```
 db2set -all 
```
You notice the same variable is set twice: once at the instance level (denoted by [i]), the other at the global level (denoted by [g]). You change your mind and decide to set this registry variable only for the current instance, not globally. You unset the registry variable as follows:
```
 db2set DB2_CLP_HISTSIZE=   -g 
```
As indicated in Table 5.1, to unset the value of a DB2 registry variable, leave a blank after the equal sign.
Next, you have to bring the instance down and then up by issuing the commands db2stop and db2start to ensure that the new registry variable value takes effect. Since you are planning to make other changes that may require an instance restart, you decide to wait until you finish all your changes.
After issuing the get dbm cfg command, you decide to make a small change to the SHEAPTHRESH parameter from 20000 to 20005. You will reverse the change afterwards, as you are only testing what you have learned about instance commands for now. You issue the command:
```
 db2 update dbm cfg using SHEAPTHRES 20005 
```
You want to see the current and delayed values for this parameter, so you issue the command:
```
 db2 get dbm cfg show detail 
```
Next, you want to make changes to your database configuration. You check your system with the list applications command. You know there are two databases in the instance, mydb and testdb. The output of the command shows that there are no connections to mydb, but testdb has 10 users connected to it. Also, other users are working heavily on the test machine, which is running other software. Since you don't want to interfere with their work, you don't want to connect to the mydb database as this would allocate memory for the different database resources. Nonetheless, you do realize that making changes to a Database Manager Configuration parameter does not require you to be connected to the database. After all, the database configuration is a binary file, so you are simply updating this file, and the database does not need to be active. You issue this command to increase the sort heap:
```
 db2 update db cfg for mydb using SORTHEAP 1024 
```
Since you are not connected to the database, you must specify the database name as part of the command. Given that the database is down, you don't really care whether the parameter SORTHEAP is configurable online or not. The next time there is a connection to the database, the new value will take effect. You do want to make sure the value has indeed been set to 1024, so you issue the command:
```
 db2 get db cfg for mydb 
```
Your boss is calling you, so you need to finish this exercise immediately. You write a note reminding yourself to revert the changes you didn't really want back to the way they were and then issue the db2stop and db2start commands once the other users in your system finish with their tests.