Finger Tips for DB2 LUW

Finger tips for DB2 for LUW are simple tips to help you do more with more from DB2 for LUW.

Scripting Tips

How to Launch a DB2 Script in a DB2 Command Window (Automatically):

The DB2 Command Window a.k.a. db2cmd.exe is essentially a DB2 shell that allows you to run both operating system commands like echo, DB2 system commands like db2diag.exe, as well as DB2 commands like DB2 CONNECT making it the ideal shell to run DB2 scripts except for one small problem;

How do you make sure your DB2 script runs in a DB2 Command Window (db2cmd.exe) and not in a Windows shell (cmd.exe) when it is executed from a Windows command prompt or the Windows Task Scheduler?

Just add this code at the beginning your DB2 script.

:: Run in DB2 Command Window db2cmd.exe
db2 quit
   db2cmd.exe -c -w %~nx0 %*

Basically all it does is determine if the script is already running in a DB2 Command Window by executing the DB2 QUIT command. If the ERRORLEVEL returned from DB2 QUIT is anything other than zero is simply launches itself in a DB2 Command Window (db2cmd.exe).

Manageability Tips

How to Create a Database with A Larger Default Page Sizes:

The default database page size in DB2 UDB has been fixed at 4k (4096 bytes) since Database Manager v1.1 shipped back in the late 1980's. If you have one or more tables that are wider than 4k, you have to create a separate table space using a larger 8k, 16k, or 32k page size. The new larger table space also required that you create a separate temporary table space and buffer pool with matching page sizes for the larger table space.

DB2 UDB v8.2.2 (a.k.a. Fixpak 9) now allows you to create a database with an 8k, 16k, or 32k default page size allowing you to have consistent page sizes for the system catalog, system temporary table spaces, user temporary table spaces, and user index, data, and large table spaces.

Example 1: Create a database with 8k default table spaces size.

       WITH 'Ten Digit Database';

Note: To find out more about this new feature go to the DB2 Information Center and search on CREATE DATABASE Command.

How to Track Instance and Database Configuration Changes:

One of the new and improved features included in Stinger (a.k.a. DB2 UDB v8.1 Fixpak 7) is the automatic logging of changes made to the instance (DBM) and database configuration parameters. Prior to Stinger changes to these critical configuration parameters where not recorded and only sure way to automatically track changes was to schedule a nightly GET DBM CFG or GET DB CFG script to pipe the current values of theses parameters to a history file. Now DB2 UDB v8.2 keeps track of these changes for you in the DB2 diagnostic log file.

Example 1: Update DBM Configuration using diaglevel 4

2005-04-29- I5099589H359       LEVEL: Event
PID     : 2312                 TID  : 3756        PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000
APPHDL  : 0-405                APPID: *LOCAL.DB2.050729184736
FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:30
CHANGE  : CFG DBM: "Diaglevel" From: "3" To: "4"

Security Tips

How to Use DB2’s Enhanced Security Features:

DB2 UDB v8.2 introduces a number of new or enhanced security features. Many of these features are designed specifically for the Windows platform. These include:

  • Group & User Accounts – Support for group and user account names has been enhanced to include better integration into the Windows operating system. Group names are no longer restricted to 8 characters on the Windows platform. Group names can now be up to 30 characters in length and the names can now include the &, - , and blank characters. User account names can now also include the &, - , and blank characters. The instance ATTACH and database CONNECT statements now support two part names including domain\userid and userid@domain. This support has been provided to reduce the overhead typically associated with locating the domain a user account belongs too when only one part user accounts are used for the ATTACH and CONNECT statements. Note that these features are only supported on the Windows platform (not on UNIX or Linux).
  • Group Enumeration – Support for group enumeration has been extended to enable the use of Access Tokens. Group enumeration which occurs by default at the server where the user account is authenticated provides an enumerated list of groups for the user account. The location in which group enumeration is performed can be changed from the default to either local or domain allowing the user account group enumeration process to be performed on the local database server or on the domain in which the database server is a member, regardless of where the user account is actually authenticated. The enablement of group enumeration to utilize Access Tokens allows the database server to use the information contained within the access token to enumerate both local groups and domain groups including global groups, domain local groups, and universal groups. In the event that the domain controller is not available to authenticate a user account, the database server can reference the information contained within the access token cached on the server from a previous user logon.
  • Local System Account – Support for the Windows Local System Account (LSA) has been extended in version 8.2 of the product. In addition to previous support for the various DB2 UDB services that can run under this Windows built-in account, support has been extended to allow “LocalSystem” to be specified during the installation process of DB2 UDB products. Support has also been extended to allow processes running under the LSA to both ATTACH to an instance and CONNECT to a database. This support has been provided to allow ISVs the option to utilize the built-in Local System Account to install and run DB2 UDB products without the maintenance typically associated with managing a user account and password.
  • External System Level Security – Support for external system level security has been added to the product in version 8.2. This new security feature is enabled by default during installation and provides additional security for DB2 UDB objects at the system level. During a typical or minimal installation of DB2 UDB the DB2 registry variable db2_extsecurity is enabled and two security groups, DB2ADMNS and DB2USERS, are defined at the operating system level. These groups are given privileges to the DB2 UDB file system \SQLLIB\ and granted various User Rights Assignments. During a custom installation the names of these security groups can be changed from the defaults to any supported group name or the external security feature can be disabled altogether.
  • Data Encryption – Support for data encryption has been enhanced to include encryption of user data flows between DB2 clients and servers. The default authentication type for DB2 servers is SERVER and provides no support for data encryption. The SERVER_ENCRYPT authentication type provides support for encryption of userid and password. In order to support the encryption of user data two new authentication types (DATA_ENCRYPT and DATAENCRYPT_CMP) were introduced in version 8.2 of the product. Both authentication types DATA_ENCRYPT and DATA_ENCRYPT_CMP provide support for encryption of; SQL statements, SQL program variable data, Output data from the server processing of an SQL statement and including a description of the data, some or all of the answer set data resulting from a query, large object (LOB) data streaming and SQLDA descriptors.

Recovery Tips

How to Leverage DB2’s Enhanced Backup, Restore, and Recovery Options:

A number of recovery features have been introduced or enhanced in DB2 UDB v8.2 for Linux, UNIX, and Windows. These include:

  • A new COMPRESS option for the BACKUP database allows the database backup image to be compressed which can result in in database backups that are up to 10 times smaller. Simply add the COMPRESS option at the end of your DB2 backup command.
  • A new INCLUDE LOGS option for the BACKUP database allows the database backup image to contain a copy of every database transaction log file required to restore an online database backup image. This option for the BACKUP database operation greatly simplifies the RESTORE or RECOVERY database operation if and when this operation is used to move a database from a primary to a standby database server such as in the initialization procedures related to Log Shipping or HADR solutions. Simply add INCLUDE LOGS to your online DB2 backup command.
  • The BACKUP DATABASE and RESTORE DATABASE operations have been enhanced to include automatic optimization of the number of agents, number and size of buffers. These database operations have all been enhanced to include automatic tuning of the size and number of buffers as well as the number of agents (parallelism) used to perform the operation taking the guess work out of tuning these operations for optimal performance. Simply let DB2 take the guess work out of your backup and restore operations.
  • A new RECOVER DATABASE operation has been introduced to simplify the database recovery process by combining the functionality of the RESTORE DATABASE and ROLLFORWARD DATABASE operations into a single RECOVER DATABASE operation. This greatly simplifies the database restore operation when multiple database backup images are available as it knows which backup image is the most recent and appropriate image to begin and complete the recovery process.

Scalability Tips

How to Enable 3 GB Memory Support:

This Microsoft Windows memory tuning feature, which can be enabled with the /3GB boot.ini switch, allows 32-bit applications that are aware of the /3GB switch to increase their virtual address space by an additional 1 GB of memory for a total of 3 GB of virtual address space. Windows 2000 Advanced Server and Datacenter Server as well as all Windows Server 2003 editions supported this tuning feature. In versions of Windows prior to Windows Server 2003, the 4 GB Tuning feature was not available with the entry-level editions of Windows NT Server or Windows 2000 Server. However, in response to customer requests, Microsoft has expanded the availability of this memory tuning feature to all 32-bit versions of Windows XP Professional and Windows Server 2003 operating systems.

All editions of DB2 UDB for Windows leverage this memory management feature to address up to 3 GB of real physical memory once this feature is enable. After adding the /3GB smith to the Window's boot.ini file and rebooting the server you will be able to aggressively increase the database servers memory utilization beyond 2 GB and as high as 3 GB, allowing for much larger buffer pools, sort and utility heaps.

Note: To find out more about this feature download the IBM Redbook Scaling DB2 UDB on Windows Server 2003.

Concurrency Tips

How to Use A More Granular Lock Time Out :

The LOCKTIMEOUT database configuration parameter specifies the amount of time in seconds any one application will wait on a lock before the lock manager rolls back the application's transaction. Prior to DB2 UDB v8.2, lock time outs were managed at the database level, which was frequently a problem for mixed workloads. DB2 UDB v8.2 (a.k.a. Fixpak 7) introduces a special register that increases the granularity of the lock time out to the connection level.

An individual application can now use the  SET CURRENT LOCK TIMEOUT statement to change its lock time out value from the default value specified in the database configuration parameter LOCKTIMEOUT to any other value (including -1) as well as setting it back to the default value. You can also query the value of the special register using the VALUES (CURRENT LOCK TIMEOUT) special register.

Examples 1: Set lock time out value to wait forever (-1).


Example 2: Set the lock time out value to 30 seconds.


Example 3: Set the lock time out value to not wait (0).


Example 4. Set the lock time out value back to the default.


Note: To find out more about this new feature go to the DB2 Information Center and search on SET CURRENT LOCKTIMEOUT.



Copyright © 1998 - 2016 Ten Digit Consulting, LLC | All rights Reserved.