In a previous article we detailed the new SQL enhancements that were delivered as part of the IBM^® DB2^® Universal Database™ (DB2 UDB) v8.1.2 update. In this second article of the four-part series, we will discuss the new administration features in that update.

Throttled Utilities

Prior to DB2 Version 8.1.2, database utilities would use whatever resources or CPU cycles were available to them, without regard to the current state of the operating system or database. The result of running DB2 utilities could negatively impact the performance and throughput of a system. A DBA could schedule utilities to run at certain times of the day, but events such as adding a new container to the database would start the rebalance utility automatically.

DB2 Version 8.1.2 introduces the notion of utility throttling. This feature allows the DBA to limit the amount of resources consumed by running utility applications. In this initial implementation, only two utilities are supported, BACKUP and REBALANCE. Future releases of DB2 will add more utilities to this list, such as REORG, RUNSTATS, and LOAD.

There are two methods by which to control the resource consumption of a utility. In order to control the impact of utilities at an instance level, a DBA must change the UTIL_IMPACT_LIM configuration parameter.

update dbm configuration using util_impact_lim 60

This parameter specifies, as a percentage of an idle system, the total impact to the workload from all throttled utilities running on an instance. The example above would tell the system that the utilities are allowed to impact the current workload by 60%. This throttling would only occur on a heavily used system. In the event that the system is quiet, the utilities can use whatever resources are available.

The UTIL_IMPACT_LIM parameter can be set with a value between 1 and 100 and it affects the performance of all throttle-enabled utilities. The priority can be set to one of the following values.

•   100 implies that all utilities should run unthrottled (default)
•   1-99 forces throttled utilities to be managed by DB2 UDB to reduce resource consumption
•   0 (zero) is not valid and is reserved for future use

These values can also be set programmatically by using the db2Cfgset() and db2CfgGet() routines. A new token SQLF_KTN_UTIL_IMPACT has been added to the sqlutil.h file. The UPDATE command is immediate, so any throttle-enabled utility will be managed once the command completes.

Throttling of a particular utility can be prioritized through the use of a Command Line Processor (CLP) command. The SET UTIL_IMPACT_PRIORITY command can be used to set the priority of a particular utility compared to other utilities running at the same time. The syntax of this command is:

set util_impact_priority for <utility_id> to <###>

A DBA would use this to increase the priority of some utilities over others. However, all of the utilities are still governed within the limit set aside by the UTIL_IMPACT_LIM parameter. If the UTIL_IMPACT_LIM is not set (100), this command has no impact.

• 1-100 sets the priority of this utility relative to the other utilities running. By default, the BACKUP utility starts at a priority of 50.
• 0 (zero) forces a throttled utility to run unthrottled.

The BACKUP command includes a new UTIL_IMPACT_PRIORITY keyword to specify the running priority without having to issue the SET command. This way the DBA can set the appropriate priority on the backup without having to issue the CLP command. However, in the case of a rebalance, there is no way to initially set the priority of the command. The rebalance is initiated by changes to the containers in a table space, so the DBA must change the priority after the rebalance has been initiated.

To view the utilities that are currently running, the DBA would issue the GET SNAPSHOT FOR DBM command. Once the ID of the rebalance utility is found, its priority can be changed using the SET UTIL_IMPACT_PRIORITY command.

Support for Lightweight Directory Access Protocol on Linux

Lightweight Directory Access Protocol (LDAP) directories allow DBAs that manage client-server operations to greatly simplify deployment of such applications. When deploying client-server applications, each client workstation must be locally made aware of the location of each DB2 UDB database that its applications will work with. Traditionally, this has been accomplished by cataloging directories (that include database and network location information) on every client workstation. This creates a high management cost in that any time a new database is added, each and every client catalog needs to be updated. DB2 UDB v8.1.2 enhances its LDAP support to include DB2 UDB on Linux. This allows those that choose to leverage the cost-savings of Linux an easier-to-manage alternative for database connections. Any changes or additions to a central catalog are made in one central location and transparently available to all applications.

All the client workstations refer to the LDAP directory for information on the database they need to connect to and make the connection. It is important to note here that only the connection information is stored on the LDAP server; the connection is still made from the client.

LDAP support has been in the DB2 product since DB2 Version 7.1. In DB2 Version 8.1.2, the IBM LDAP v3.2.2 client has been enhanced to:

1. Allow DB2 UDB and DB2 Connect servers running on 32-bit Linux on Intel/AMD or 31-bit zSeries® servers running Linux to publish their databases’ connection information in an LDAP directory.
2. Allow DB2 clients running on these operating systems to be able to refer to the LDAP directory to find databases they need to connect to.

An example of a DB2 UDB configuration that uses LDAP is shown below.

DB2 v8.1.2 introduces support for Dynamic LPARS in the AIX operating system, and Dynamic System Domains in the Solaris operating environment.

With the introduction of larger machines with numerous CPUs, customers have been taking advantage of Logical Partition support (LPARs). An LPAR is a subset of the machine’s processors, memory and storage, running with its own instance of the operating system, database, and applications.

Dynamic LPARS for AIX 5.2B

Dynamic LPARs allows for “on-the-fly” resource adjustments from LPAR to LPAR, without the need to reboot the machine. In Version 8.1.2, DB2 UDB allows users of an AIX 5.2B environment to dynamically add or remove resources from an active DB2 LPAR. Whether resources are added or removed, a DB2 script is run before the action takes place. In the case of resource addition, DB2 UDB will update any database configuration values that may be impacted by the change. The same holds true for a removal of resources, but DB2 UDB will prevent the removal of memory or processors that will impact the ability of the database to continue running. In this case, the dynamic reconfiguration will not be allowed to continue.

When an LPAR is changed, DB2 UDB will check the memory size, the number of CPUs, and the storage system. Currently, DB2 will not allow storage (disks) to be removed. All other resource changes will be checked to make sure that the database can continue to run after the change.

Version 8.1.2 currently only supports AIX 5.2B for dynamic resource allocation, but the intent is to support other hardware vendors as capabilities become available.

Dynamic System Domain (DSD) for the Solaris Operating Environment

Dynamic System Domain (DSD) support is available on Solaris 9 operating environment. DSD makes it possible to run multiple, independent operating system images on a single server. Dynamic reconfiguration allows for resource adjustments (memory, CPUs, I/O) from DSD to DSD without the need to reboot the machine.

DB2 UDB has scripts that will allow changes to occur to a DSD configuration, similar to that done in the AIX environment. However, DB2 UDB must be stopped and restarted in the new environment. The DB2 script will reject the reconfiguration if DB2 UDB is running.

In a Solaris environment, CPUs, memory, and I/O can be changed, but DB2 UDB must be stopped and restarted to work in the new environment.

Infinite Logging Enhancements

DB2 UDB v8.1 added support for infinite logging. When infinite logging is enabled, live transactions can be archived and a new active log started. The database will now allow the transaction to live in an archive state. To add more flexibility to this feature, and to prevent a transaction from accidentally writing to multiple logs (which can be up to 256 GB in DB2 UDB v8.1), two new database configuration parameters were added.

Both configuration parameters are useful in preventing incorrectly written applications (for example, a COMMIT statement was missing from using up all the active log space and having to eventually roll back all its work.

• MAX_LOG — This parameter is very similar to the existing MAXLOCK parameter and represents the maximum amount of space, as a percentage, that a transaction can take in any one log file. This parameter can be used to prevent a transaction from taking up all of the active log space in a multi-user environment. If an application exceeded this threshold, the default behavior would be to force the application off the database and roll back any work that had been done. This behavior can be overridden by setting the DB2_FORCE_APP_ON_MAX_LOG registry variable to FALSE (its default value is TRUE). If you set this registry variable to TRUE, the offending application would receive an SQL0964N error as soon as it exceeded the defined threshold, the current statement would fail, but the application could still issue a COMMIT or ROLLBACK statement to complete the work done by the previous statements in the unit of work. When a transaction exceeds the MAX_LOG threshold, a message is logged to the Administrative and db2diag.logs files upon exceeding the MAX_LOG threshold.
• NUM_LOG_SPAN —This parameter can be used to limit an application's consumption of log space with respect to the number of logs the transaction can consume. For example, you might enable infinite logging to remove the administration of log files. However, at the same time, you might want to stop any transaction that was logged to more than two log files. When a transaction exceeds this threshold, even if it is idle, it will receive an SQL1224N error, will be rolled back, and forced off the system. When a transaction exceeds the NUM_LOG_SPAN threshold, a message is logged to the Administrative and db2diag.logs files upon exceeding the MAX_LOG threshold.

Although these parameters can be used even if infinite logging is not enabled, they become a crucial tool when enabling this feature.

ALTER Table for VARGRAPHIC Columns

DB2 UDB v8.1.2 extends the previous capability to ALTER the length of a VARCHAR column to the VARGRAPHIC data type. DBAs can now extend the length of VARGRAPHIC columns in existing tables without dropping the table.

In previous versions of DB2 UDB, DBAs would have to export data from the table that contains the VARGRAPHIC column whose length they want to change, drop the table, recreate the table with the revised column length, and then import the data back into the table. Furthermore, all objects that were dependent on the table would also have to be re-created, such as indexes, views, and triggers.

This new feature helps to improve database manageability and makes it unnecessary to take the table offline to perform this type of maintenance activity. This is a continual evolution for online database schema.

An example of this feature is shown below:

Historical Tracking of DB2 UDB Command Line Processor Commands

The CLP’s interactive prompt in DB2 UDB v8.1.2 has been enhanced with a new retention feature through the introduction of a new history command, the syntax for which is as follows:

This command will display the 20 most recent commands in the command history list, along with corresponding command numbers that sequence the order in which the commands were run. The most recently run command is listed last.

If the REVERSE keyword is specified, the order of the command history is reversed, with the most recently run command listed first. If the <num> option is specified, it will display the most recent <num> commands.

To invoke the commands in the CLP’s history, use the runcmd command. This command has the following syntax:

This command will run the command corresponding to the <num> keyword in the command history list. If the <num> option is not specified, the default is to run the most recent command.

The size of the command history cache can be controlled using the DB2 Registry Variable DB2_CLPHISTSIZE. The range of values permissible for DB2_CLPHISTSIZE is between 1 and 500 inclusive. If this variable is set to a value outside this range, the DB2 UDB CLP will use the default size of 20.

There is no persistence from session to session for the command history. (If you leave the prompt, the history is lost) If you start two separate interactive CLP sessions, each session’s command history is separate.

An example of using the history and runcmd command is shown below:

Roundtrip Command Line Processor Editing

The previous section introduced the ability to keep a historical list of commands in a CLP session. While simple commands can be edited using the native command-line editor, a better method was introduced in DB2 Version 8.1.2.

The EDIT command is introduced to the CLP syntax. This command will let you edit any of the commands currently in the history stack. The syntax of the command is:

EDIT {EDITOR <editor>} {num}

The EDIT command can be shorted to the letter E for brevity. If this command is executed with no command arguments, it will edit the previous command. The editor that is used depends on the setting of a number of registry or environment variables. The following steps are taken to determine which editor is used:

1. If EDITOR <editor> is specified, this program will be invoked and the command placed into the editor window. Note that the specified editor must be a valid editor contained in the PATH of the operating system.
2. If the DB2_CLP_EDITOR registry variable is set, that editor is used.
3. If the VISUAL environment variable is set, that editor is used.
4. If the EDITOR environment variable is set, that editor is used.
5. If none of these values is set, the NOTEPAD editor is invoked in the Windows® environment, and the VI editor on UNIX® and Linux.

The following CLP session shows the previous history of commands, and the editor being invoked on the third command.

Since no editor was specified in the command, the NOTEPAD editor is invoked with the third command in the edit window.

At this point, the user could modify the command to include additional SQL commands or correct invalid syntax.

Once the FILE is saved and the session closed, the CLP session will prompt the user to execute the changes.

If you select “y”, the command is executed; “n” cancels the command. You must take care when editing your SQL commands to insure that you use proper delimiters when editing the text. For instance, if you start a CLP session without a delimiter (commonly a semi-colon), the end of a command is represented by a carriage-return/line feed pair. In our editor session, if we placed the “and bonus > 10000” on a separate line by using the ENTER key, the system would not recognize this second line.

The previous example strung the command out over two lines. If an ENTER key was used instead, the following command would be displayed:

Note how the second portion of the command is missing? To avoid this problem, start your CLP session with the –t flag. This will use semi-colons as a delimiter instead of CR/LF pairs.

The only downside to this approach (of course there must be one!) is that every command needs to end in a semi-colon! Pick whichever method is most convenient for you.

Health Center Enhancements

DB2 UDB v8.1.2 added some minor enhancements to the Health Center. After you apply this update, the Alert information contains more details. The recommendations associated with each of the Alerts have more description text and the SMART technology is enhanced with more suggested corrective actions. Finally, there are new Health Center Alert Filters that have corresponding descriptions (Objects in Alarm State, Objects in Alarm or Warning State, Objects in Any Alert State, and All Objects) that provide DBAs with a better indication of filtering based on a system’s health.

Miscellaneous Control Center Enhancements

In any version of DB2 UDB, users of the Control Center (CC) are given a tree view of catalogued systems, instances, and databases. This tree view is always a reflection of the catalog files.

DB2 UDB v8.1.2 introduces some new flexibility into the presentation of this tree; it is a first step on the road to a more extensive solution of grouping and customization capabilities. In DB2 UDB v8.1.2 you can create custom folders. These folders will be empty top-level folders at the same level as the “All Cataloged Systems" folder. A DBA may give the folder any arbitrary name. You can create custom folders by right-clicking the Control Center icon and selecting the Create Customer Folder option. Also added to the Control Center in DB2 UDB v8.1.2 is the ability to save commands to the Task Center. This feature was available in DB2 UDB v7.2 and saved scripts to the Script Center. This makes the automation of specific tasks easier for DBAs.

LOAD: BCP File Format Support

The DB2 LOAD command supports a variety of input formats. In DB2 Version 8.1.2, the ability to read and write files in BCP format has been added to the LOAD, IMPORT, and EXPORT utilities.

BCP, or Bulk Load Copy, is a text file format used by Sybase that is very similar to the DEL file format. Note that the native BCP file format is not supported. The ability to read and produce files in this format is of benefit for multi-vendor database installations that need to move data between DB2 and Sybase databases.

The EXPORT utility modifies two options, NOCHARDEL, and TIMESTAMPFORMAT, to produce a delimited (DEL) file that is an acceptable BCP text file for input into a Sybase system.

The two parameters have the following behaviors during a load.

NOCHARDEL

The load utility will assume all bytes found between the column delimiters to be part of the column's data. Character delimiters will be parsed as part of column data. This option should not be specified if the data was exported using DB2 (unless NOCHARDEL was specified at export time). It is provided to support vendor data files that do not have character delimiters. Improper usage may result in data loss or corruption.

TIMESTAMPFORMAT

The new elements in DB2 UDB Version 8.1.2 are shown in italics.

The significant feature of the TIMESTAMPFORMAT is the ability to EXPORT and IMPORT timestamps that have English character representations for the months of the year, along with shorter microsecond values.

Paul C. Zikopoulos, BA, MBA, is an IDUG keynote and award-winning speaker with the IBM Global Sales Support team. He has more than seven years of experience with DB2 and has written numerous magazine articles and books about it. Paul has written articles on DB2 for many magazines and has co-authored the books: DB2 - The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 For Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at paulz_ibm@msn.com.

George Baklarz, B Math, M Sc (Comp Sci) is the manager of the DB2 Worldwide Pre-sales Support Group. He has more than 19 years of experience with DB2 and has co-authored the DB2 UDB Version 8.1 Database Administration Certification Guide (Prentice-Hall, 2003). In his spare time, he teaches database theory at the University of Guelph and presents at a variety of conferences, including the International DB2 Users Group. You can reach George when he's not traveling at baklarz@yahoo.com.

Trademarks

DB2, DB2 Universal Database, and IBM are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Copyright International Business Machines Corporation, 2003. All rights reserved.

Microsoft and Windows are registered trademarks of Microsoft Corporation in the United States, other countries, or both.

Other company, product and service names may be trademarks or service marks of others.