DB2 9.7 for Linux, UNIX, and Windows Database Administration: Certification Study Notes

by Roger E. Sanders

Overview

The relational database-management system DB2 9.7 is given detailed and comprehensive treatment in this exam-preparation resource. Compiled from presentation material used in the popular “Crammer Course” at the IBM Information On Demand Conference, everything required for certification is presented here, including server management, design, business rules implementation, activity monitoring, security, and networking. An essential resource, this guide is helpful when studying to pass the official DB2 9.7 for LUW DBA certification exam.

Product Details

ISBN-13:	9781583476086
Publisher:	Mc Press
Publication date:	11/02/2011
Sold by:	Barnes & Noble
Format:	eBook
Pages:	160
File size:	5 MB

About the Author

Roger E. Sanders is a consultant corporate-systems engineer and the president of Roger Sanders Enterprises, Inc. He lives in Fuquay Varina, North Carolina.

Read an Excerpt

DB2 9.7 for Linux, UNIX, and Windows Database Administration

Certification Study Notes

---

By Roger E. Sanders

MC Press

Copyright © 2011 Roger E. Sanders
All rights reserved.
ISBN: 978-1-58347-608-6

---

CHAPTER 1

DB2 Server Management

* * *

Ten percent (10%) of the DB2 9.7 for LUW Database Administration Exam is designed to test your knowledge of basic DB2 server management.

Servers, Instances, and Databases

* * *

DB2 9.7 sees the world as a hierarchy of objects. Workstations (or servers) occupy the highest level, instances occupy the second level, and databases make up the third level.

Configuring the DB2 System Environment

* * *

During normal operation, the behavior of a DB2 server is controlled, in part, by a collection of special DB2-specific system-level values. These values are stored in one of three different environment or registry profiles:

-> The DB2 Global Level Profile Registry

-> The DB2 Instance Level Profile Registry

-> The DB2 Instance Node Level Profile Registry

Values assigned to these registry profiles can be seen or set with the db2set command.

The db2set Command

* * *

db2set <[Variable] = [Value]>
<-g>
<-i[InnstanceName] >
<-all>
<-null>
<-r[InstanceName]>
<-n[DASNode]<u[UserID]<-p[Password]>
<-l| -lr>
<-v>
<-ul | -ur>
<-h | -?>

Examples of the db2set Command

* * *

db2set

Lists all registry variables that have been set for the current instance

db2set -lr

Displays a list of all registry variables that are supported by DB2

db2set DB2_ATS_ENABLE=YES

Enables the administrative task scheduler for the current instance

Configuring Instances

* * *

The behavior of a DB2 instance is controlled, in part, by a Database Manager configuration file. This file is composed of many different parameters; the values assigned to those parameters can be seen or modified using the following commands:

->GET DATABASE MANAGER CONFIGURATION

-> UPDATE DATABASE MANAGER CONFIGURATION

-> RESET DATABASE MANAGER CONFIGURATION

GET DATABASE MANAGER CONFIGURATION

* * *

GET
[DATABASE MANAGER | DB MANAGER | DBM]
[CONFIGURATION | CONFIG | CFG]

GET DBM CFG SHOW DETAIL

Displays detailed information about an instance's configuration

UPDATE DATABASE MANAGER CONFIGURATION

* * *

UPDATE
[DATABASE MANAGER | DB MANAGER | DBM]
[CONFIGURATION | CONFIG | CFG
USING [[Keyworld][Value], ...]

UPDATE DBM CFG USING NOTIFYLEVEL 4

Indicates that DB2 is to collect the maximum amount of diagnostic information available and write it to the db2diag.log file and the administration notification log when warnings or errors occur

RESET DATABASE MANAGER CONFIGURATION

* * *

RESET
[DATABASE MANAGER | DB MANAGER | DBM]
[CONFIGURATION | CONFIG | CFG]

RESET DBM CFG

Resets an instance's configuration; all configuration parameters are assigned their original default values

DB2 Administration Server (DAS)

* * *

The DAS is a separate server process that operates independently of, yet concurrently with, all other instances. The DAS ...

-> Provides remote clients with the information needed to establish communications with other instances

-> Allows remote administration of an instance

-> Assists in task (job) management

-> Provides a way to "discover" information about other DAS processes, DB2 instances, and DB2 databases

[check] In DB2 9.7, the Control Center tools and the DAS have been deprecated and may be removed at a later date.

Configuring the DAS

* * *

The DAS is configured via the DB2 Administration Server configuration file. This file also consists of several different parameters; the values assigned to those parameters can be seen or modified using the following commands:

->GET ADMIN CONFIGURATION

-> UPDATE ADMIN CONFIGURATION

-> RESET ADMIN CONFIGURATION

GET ADMIN CONFIGURATION

* * *

GET ADMIN [CONFIGURATION | CONFIG | CFG]

GET ADMIN CFG

* * *

Displays information about the DAS's configuration

UPDATE ADMIN CONFIGURATION

* * *

UPDATE ADMIN
[CONFIGURATION | CONFIG | CFG]
USING [[KeyWord][Value], ...]

UPDATE ADMIN CFG USING SCHED_ENABLE OFF

Turns the DB2 Task Scheduler OFF

RESET ADMIN CONFIGURATION

* * *

RESET ADMIN
[CONFIGURATION | CONFIG | CFG]

RESET ADMIN CFG

Resets the DAS configuration; all configuration parameters are assigned their original default values

Configuring Databases

* * *

The behavior of a DB2 database is controlled, in part, by a database configuration file. This file also consists of several different parameters; the values assigned to those parameters can be seen or modified using the following commands:

->GET DATABASE CONFIGURATION

-> UPDATE DATABASE CONFIGURATION

-> RESET DATABASE CONFIGURATION

GET DATABASE CONFIGURATION

* * *

GET [DATABASE | DB]
[CONFIGURATION | CONFIG | CFG]
FOR [DBAlias]

GET DB CFG FOR sample SHOW DETAIL

Displays configuration information for a database named SAMPLE

UPDATE DATABASE CONFIGURATION

* * *

UPDATE [DATABASE | DB]
[CONFIGURATION | CONFIG | CFG]
FOR [DBAlias]
USING [[KeyWorld][Value], ...]

UPDATE DB CFG FOR sample

USING LOCKTIMEOUT 1000

Sets the amount of time an application will wait to obtain a lock to 1,000 seconds

RESET DATABASE CONFIGURATION

* * *

RESET
[DATABASE | DB]
[CONFIGURATION | CONFIG | CFG]
FOR [DBAlias]

RESET DB CFG FOR sample

Resets the configuration for a database named SAMPLE; all configuration parameters are assigned their original default values

The AUTOCONFIGURE Utility

* * *

Using information you provide about a single database, the AUTOCONFIGURE utility will recommend optimal Database Manager configuration parameter values, database configuration parameter values, and initial buffer pool sizes to use.

Recommendations made can be displayed, or they can be automatically applied to an instance and a database.

The AUTOCONFIGURE Command

* * *

AUTOCONFIGURE
USING [[Keyworld]Value], ...]
APPLY [DB ONLY] | DB AND DBM | NONE]

AUTOCONFIGURE USING MEM_PERCENT 60

APPLY DB ONLY

Determines the best configuration to use when 60% of the available memory will be available for the instance; makes changes to the database configuration only

Self-Tuning Memory Manager (STMM)

* * *

When enabled, DB2's self-tuning memory manager (STMM) dynamically distributes available memory resources (as the workload changes) among the following:

-> Buffer pools

-> Package cache

-> Locking memory

-> Sort memory

-> Database shared memory

Enabling STMM

* * *

To enable STMM, set the self_tuning_mem database configuration parameter to ON and:

-> Set two or more relevant database configuration parameters (pckcachesz, locklist, or maxlocks, and sheapthres_shr) to AUTOMATIC.

or

-> Set the da.tabase_memory database configuration parameter to AUTOMATIC, and set either one other relevant configuration parameter or the size of one buffer pool to AUTOMATIC.

or

-> Set the sortheap database configuration parameter to AUTOMATIC.

Obtaining STMM Information

* * *

To determine which memory consumers have self-tuning enabled, examine the database's configuration file and the system catalog:

GET DB CFG SHOW DETAIL; SELECT BPNAME FROM SYSIBM. SYSBUFFERPOOLS WHERE NPAGES = -2;

To obtain information about memory configuration changes made by STMM, examine the memory tuning log files (stmm.nn.log) found in the stmmlog directory.

Automatic Maintenances

* * *

Beginning with DB2 9, the following maintenance operations can be performed automatically:

-> Database backups

-> Data defragmentation (table and index reorganization)

-> Statistics collection

-> Statistics profiling

To enable automatic maintenance, set the auto_maint database configuration parameter to ON. (This is the default.)

Enabling Individual Automatic Maintenance Features

* * *

To enable or disable individual automatic maintenance features, assign the value ON or OFF to the following database configuration parameters:

- auto_db_backup (Default: OFF)
- auto_tbl_maint (Default: ON)
- auto_runstats (Default: ON)
- auto__stmt_state (Default: OFF)
- auto__stats_prof (Default: OFF)
- auto_prof_upd (Default: OFF)
- auto_reorg (Default: OFF)

Automatic Maintenance Policies

* * *

For some operations, a policy (a defined set of rules or guidelines) can be used to specify the automatic maintenance behavior:

-> Database backup operations (auto_db_backup)

-> Table and index reorganization (auto_reorg)

-> Statistics collection (auto_runstats)

The stored procedures AUTOMAINT_SET_POLICY () and AUTOMAINT_SET_POLICYFILE () can be used to configure the automated maintenance policy for a database.

Utility Throttling

* * *

DB2's throttling system allows maintenance utilities to be run concurrently during critical production periods, while keeping their performance impact on production work within acceptable limits. The following can be throttled:

-> Statistics collection

-> Backup operations

-> Rebalancing operations

-> Asynchronous index cleanups

Controlling Utility Throttling

* * *

To use utility throttling, perform the following steps:

1. Define an impact policy for all throttled utilities. This is done by assigning a value between 1 and 100 to the utii_iB5pact_iim Database Manager configuration parameter.

2. Invoke the desired utility with the UTIL_IMPACT_PRIORITY [Priority] Option specified (where Priority is a value between 1 and 100).

Determining Resource Consumption – An Example

* * *

If the value 50 is assigned to the utll_impact_lim Database Manager configuration parameter as follows: UPDATE DBM CFG USING UTIL_IMFACT_LIM 50

And if a backup operation is started by executing a command that looks like this:

BACKUP DB sample UTIL_IMPACT_PRIORITY 70

What percentage of available resources will be consumed by the backup operation?

Answer: 35% (0.7x0.5 = 0.35 or 35%)

Changing the Priority of a Running Utility

* * *

To change the impact setting for a utility that is already running, execute the SET UTIL_IMPACT__PRIORITY command:

SET UTIL_IMPACT_PRIORITY [UtilityID] TO [Priority]

SET UTIL_IMPACT_PRIORITY 1 TO 20

A value of 100 represents the highest priority; 1 represents the lowest priority. Setting Priority to 0 will force a throttled utility to continue running unthrattled; a non-zero value will force an unthrottled utility to continue running in throttled mode.

The LIST UTILITIES Command

* * *

To get the ID of a running utility, execute the LIST UTILITIES command:

LIST UTILITIES

LIST UTILITIES

LIST UTILITIES

ID = 1
Type = RUNSTATS
Database Name = PROD
Description = krrose.some_table
Start Time = 03/17/2011 11:54:45.773215
Priority = 10

Using the Task Center to Schedule Maintenance

* * *

The Task Center is a GUI tool that lets users organize task flow, schedule frequently occurring tasks, and report the status of tasks that have been completed.

Several individual tasks can be combined to create a grouping task, which is then treated as a single task.

[check] In order to use the Task Center, the TOOLS catalog must be created, either in a new and separate database or in an existing database.

Task Center Operations

* * *

Regardless of whether a task completes successfully or fails, when a task has finished, any number of actions can be performed, including:

-> Running another task Scheduling another task

-> Disabling another scheduled task

-> Deleting the task itself

By default, the status of every completed task is recorded in the Journal (which is another GUI tool).

The ADMIN Configuration and the Task Center

* * *

To a certain extent, the behavior of the Task Center is controlled through parameters found in the ADMIN configuration file:

-> toolscat_inst TOOLS catalog instance

-> toolscat_db
TOOLS catalog instance

-> toolscat_schema TOOLS catalog scherma

-> sched_enable
Turns the DB2 Task Scheduler OFF
and ON

-> exec_exp_task Specifies whether expired tasks are
to be executed (YES or NO) when the

DB2 Task Schedule is turned ON

The Administrative Task Scheduler

* * *

Unlike the DB2 Task Scheduler, which is used to automate the execution of user-defined tasks (that have been defined via the Task Center), the administrative task scheduler is used to manage and run administrative tasks that have been created.

Administrative tasks must be encapsulated in either user-defined or system-defined procedures; you can add, update, and remove tasks from the scheduler's task list by using a set of system-defined procedures:

ADMIN_TASK_ADD, ADMIN_TASK_UPDATE, and ADMIN_TASK_REMOVE.

How the Administrative Task Scheduler Works

* * *

Scheduled tasks are executed by the DB2 autonomic computing daemon (db2acd), which is started and stopped in conjunction with an instance.

Internally, the daemon maintains a list of active tasks; every five minutes, it connects to each active database and retrieves any new or updated task definitions.

When a task's scheduled execution time arrives, the daemon connects to the appropriate database and calls the procedure associated with the task. (You can obtain information about tasks using two views:

ADMIN_TASK_LIST and ADMIN_TASK_STATUS.)

Enabling the Administrative Task Scheduler

* * *

The administrative task scheduler is disabled by default. To set up the administrative task scheduler, assign the value YES to the DB2_ATS_ENABLE registry variable and create the SYSTOOLSPACE table space:

db2set DB2_ATS_ENABLES=YES;

CREATE TABLESPACE systoolspace
IN ibcatgroup
MANAGED BY AUTOMATIC STORAGE
EXTENSIVE 4;

What Happens if a Task Takes Longer Than Expected to Run?

* * *

The DB2 autonomic computing daemon (db2acd) will not execute a task again if a previous instance of the task is running.

For example, if a task is scheduled to run every 5 minutes and, for some reason, the task takes 6.5 minutes to complete, the daemon will not execute another instance of the task at the next 5-minute interval. Instead, the task will run again at the 10-minute mark.

What Happens If a Task Fails?

* * *

If the needed database is not active, the daemon will not execute the task, and a message will be written to both the administration notification log and the DB2 diagnostic log.

If, for some other reason, the daemon fails to execute the task, a message will be written to both logs, and the daemon will attempt to execute the task again every 60 seconds.

Problem Determination

* * *

When a problem is encountered, there are several ways to obtain helpful information:

-> The ? command

-> The GET ERROR MESSAGE API

-> First Failure Data Capture (FFDC) output

- DB2 diagnostic log (db2diag.log) Administration notification log {[lnstanceName].v\fy)

- Dump files

- Trap files

- Core files (Linux and UNIX only)

CONTROLLING FIRST FAILURE DATA CAPTURE (FFDC) OUTPUT

* * *

To a certain extent, FFDC output is controlled through three Database Manager configuration parameters:

->dlaglevel

Controls the type of diagnostic errors that will be recorded in the db2diag log file. Range: 0 (nothing) to 4 (everything)

->notifylevel

Controls the type of messages that will be written to the administration notification log. Range: 0 (nothing) to 4 (everything)

->diagpath

Identifies the fully qualified path where DB2 diagnostic information is stored

DB2 Diagnostic/Administration Notification Log Entry Headers

* * *

The db2diag command can be used to filter and format the contents of the db2diag.log file:

db2diag –g level=Severe, pid =2384

CHAPTER 2

Physical Design

* * *

Twenty percent (20%) of the DB2 9.7 for LUW Database Administration Exam is designed to test your ability to create a DB2 9.7 database and to test your knowledge of how data storage is managed.

Simplest Form of the CREATE DATABASE Command

* * *

A new database can be created by executing the CREATE DATABASE command. The simplest form of this command is:

CREATE DATABASE sample

Creates a new database in the default location, assigns it the name SAMPLE, and configures it to use automatic storage

To override any default behavior, a more complex form of the CREATE DATABASE command must be used.

The CREATE DATABASE Command

* * *

CREATE (DATABSE | DB][DBName] AUTOMATIC STORAGE [YES } NO} ON [Path] DBPATH ON [DBPath] ALIAS [Alias] [CodeSet] TERRITORY [Territory] COLLATE USING [CollateType] PAGESIZE [PageSize] NUMSEGS [NumSegments] DFT_EXTENT_SZ [DefaultExtentSize]RESTRICTIVE CATALOG TABLESPACE [TS_Definition] USER TABLESPACE [TS Definition TEMPORARY TABLESPACE [TS_Definition] WITH "[Description]" AUTOCONFIGURE [AutoConfigOptions]

(Continues...)

---

Excerpted from DB2 9.7 for Linux, UNIX, and Windows Database Administration by Roger E. Sanders. Copyright © 2011 Roger E. Sanders. Excerpted by permission of MC Press.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---

Table of Contents

Contents

About the Author,
Introduction,
Part 1: DB2 Server Management,
Part 2: Physical Design,
Part 3: Business Rules Implementation,
Part 4: Monitoring DB2 Activity,
Part 5: Utilities,
Part 6: High Availability,
Part 7: Security,
Part 8: Connectivity and Networking,
Exam 541 Summary,