DB2 9.7 for Linux, UNIX, and Windows Database Administration: Certification Study Notes
160
DB2 9.7 for Linux, UNIX, and Windows Database Administration: Certification Study Notes
160eBook
Related collections and offers
Overview
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. SandersAll 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 2Physical 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]
(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,