Finger Tips for DB2 Universal Database

If you would like to submit a finger tip to be listed on this page e-mail the title, author, description, and examples to Webmaster. Manageability Larger Default Database 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. CREATE DATABASE TENDIGIT ALIAS TENDIGIT PAGESIZE 8192        CATALOG TABLESPACE MANAGED BY SYSTEM USING ('SYSCATSPACE')        EXTENTSIZE 32 PREFETCHSIZE 32        TEMPORARY TABLESPACE MANAGED BY SYSTEM USING ('TEMPSPACE1')        EXTENTSIZE 32 PREFETCHSIZE 32        USER TABLESPACE MANAGED BY SYSTEM USING ('USERSPACE1')        EXTENTSIZE 32 PREFETCHSIZE 32        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. Tracking of 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-18.16.32.647000-240 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 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 Enhanced Database Backup, Restore, and Recovery: A number of recovery features have been introduced or enhanced in DB2 UDB v8.2 for Linux, UNIX, and Windows. These include: 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. The BACKUP DATABASE and RESTORE DATABASE operations have been enhanced to include automatic optimization of the number of agents, number and size of buffers. 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. 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.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. Scalability 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 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). SET CURRENT LOCK TIMEOUT WAIT Example 2: Set the lock time out value to 30 seconds. SET CURRENT LOCK TIMEOUT = 30 Example 3: Set the lock time out value to not wait (0). SET CURRENT LOCK TIMEOUT NOT WAIT Example 4. Set the lock time out value back to the default. SET CURRENT LOCK TIMEOUT NULL   Note: To find out more about this new feature go to the DB2 Information Center and search on SET CURRENT LOCKTIMEOUT.