A Db2 instance is an environment responsible for managing system resources and the databases that fall under their control. Basically, an instance is made up of a set of processes, threads, and memory areas. The following diagram illustrates the relationship between a Db2 server, its instances, and its associated databases:

The server is at the highest level in the hierarchy, and you can have multiple Db2 versions installed on a given server. It is recommended that you check the system requirements for IBM Db2 for Linux, Unix, and Windows installations at https://www-01.ibm.com/support/docview.wss?uid=swg27038033.

Instances are second in the hierarchy and are responsible for managing the system resources required to run the database. You can create multiple instances on a server, providing a unique...

The Db2 database environment contains a number of operating system environment variables and Db2-specific profile registry variables that are used to manage, monitor, and control the behavior of the Db2 system.

Environment variables are set at the operating system level using standard operating system commands, such as set on a Windows operating system and export on a UNIX operating system. Examples are shown here:

       set DB2INSTANCE=db2inst1 [On Windows]
       export DB2INSTANCE=db2inst1 [On UNIX]

The Db2 profile registry settings are categorized as:

• Db2 global-level profile registry: All server-wide environment variable settings reside in this registry, and these settings are applicable to all of the instances that pertain to a particular version of Db2.
• Db2 instance-level profile registry: The environment variable settings for a...

In today's dynamic world, we need databases that are self-configurable and self-optimizable, based on changes in workload characteristics. Db2 comes with a rich set of autonomic computing features to appropriately sense and respond to many situations, and to optimize the database for the best possible performance and use of available resources.

The following table, provides a summary of Db2 autonomic computing features, and a short description of each:

The Db2 WLM is a feature that can help identify, manage, monitor, and control the database workload, to better reflect the organization and priorities of your business.

The WLM environment can be designed using the following four stages:

1. Definition of the business goals: It is necessary to understand the overall business goal you are trying to achieve for this system. For example, you may want to limit certain business processes to no more than 10% of system resources, while allowing a few critical business processes to utilize up to 80% of the resources.
2. Identification of the work entering the database server: Identification of the activities, in detail, for each of the business goals identified in step 1. For example, for critical business processes, identify the application user name or authorization ID that submits the query.
3. Management...

The IBM Data Server Manager is an integrated database management tool platform for Db2 on Linux, UNIX, and Windows, and is built based on four key features:

• Identify:
  • Receive early notifications of problems before they impact the service
  • Quickly isolate problems across the database, network, and application layers
  • Get visibility into problems by workload, to prioritize response
• Diagnose:
  • Follow the well-proven guided workflows to diagnose
  • Provide a complete view of data, from real time to any specific time in the past
  • Dive deeply into root causes, with detailed drill-down capabilities
  • Use built-in integration to leverage data from other IBM solutions
• Solve:
  • Receive easy-to-understand, actionable recommendations for problem resolution
  • Tune entire workloads to balance costs across query and insert actions
• Prevent:
  • Capture all...

The objective of this chapter was to acquaint you with the following:

• The Db2 instance-specific commands, such as create, list, update, and drop
• The Db2 registry and operating system environment variables
• The Db2 instance and database configuration parameters and commands
• The Db2 autonomic computing features, and associated application or business benefits with STMM and automatic storage
• An introduction to the WLM feature and associated objects
• An introduction to IBM Data Server Manager and its capabilities

• Question 1: Which command can be used to display only the instance level registry settings for an instance db2inst1?
  1. db2set -all db2inst1
  2. db2set -i db2inst1
  3. db2set -lr
  4. db2val

• Question 2: Which of the following commands can be used to retrieve CURRENT and PENDING values of database manager configuration parameters for the instance db2inst1? (Choose two.)
  1. db2look -dbmcfg
  2. db2 "ATTACH TO db2inst1"; db2 "GET DBM CFG SHOW DETAIL"
  3. db2 "GET DBM CFG SHOW DETAIL"
  4. db2pd -dbmcfg
  5. db2greg
• Question 3: Which of the following table functions displays the registry setting?
  1. MON_GET_DATABASE ()
  2. MON_GET_INSTANCE ()
  3. ENV_GET_REG_VARIABLES ()
  4. MON_GET_UTILITY()
• Question 4: Which of the following parameters contributes to DATABASE_MEMORY usage? (Choose two.)
  1. dbheap
  2. stmtheap
  3. applheapsz
  4. appl_memory
  5. util_heap_sz
• Question 5: What is the order...

• Question 1:
  The correct answer is (2). Option (3) displays all of the supported registry variables. Option (4) is a Db2 copy validation command, and option (1) is invalid.
• Question 2:
  The correct answers are (2) and (4). Option (3) needs an instance attachment before running the SHOW DETAIL clause of the GET DBM CFG command. Option (5) is a global registry display command. Option (1) is invalid.
• Question 3:
  The correct answer is (3). Options (1), (2), and (4) are monitoring table functions, and will be discussed in the monitoring chapter.
• Question 4:
  The correct answers are (1) and (5). The parameter database_memory specifies the amount of shared memory that is reserved for the database shared memory region. When you set this parameter to AUTOMATIC, the initial memory requirement is calculated based on the underlying configuration settings:
  • Buffer pools: This specifies...