DB2 10.5 DBA for LUW Upgrade from DB2 10.1: Certification Study Notes (Exam 311)
160
DB2 10.5 DBA for LUW Upgrade from DB2 10.1: Certification Study Notes (Exam 311)
160Paperback(Study Guide)
-
SHIP THIS ITEMIn stock. Ships in 6-10 days.PICK UP IN STORE
Your local store may have stock of this item.
Available within 2 business hours
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781583474822 |
|---|---|
| Publisher: | MC Press, LLC |
| Publication date: | 05/09/2016 |
| Series: | DB2 DBA Certification |
| Edition description: | Study Guide |
| Pages: | 160 |
| Product dimensions: | 6.00(w) x 9.00(h) x 0.40(d) |
About the Author
Read an Excerpt
DB2 10.5 DBA for LUW Upgrade from DB2 10.1
Certification Study Notes (Exam 311)
By Roger E. Sanders
MC Press
Copyright © 2016 Roger E. SandersAll rights reserved.
ISBN: 978-1-58347-482-2
CHAPTER 1
DB2 10.5 Overview
A brief overview of some of the new features and functionality introduced with DB2 10.5 for Linux, UNIX, and Windows.
DB2 10.5 With BLU Acceleration Design Principles
DB2 10.5 with BLU Acceleration was designed around the following seven "big ideas":
1. Simple to implement and use
• "It's just DB2 and standard SQL"
• Load data and start getting the performance gains of DB2 with BLU Acceleration immediately
2. Compute-friendly encoding and compression
• "Always on" compression with approximate Huffman encoding
• Encoded values do not need to be decompressed during evaluation
3. Multiply the power of the CPU
• Takes advantage of special hardware instructions to work on multiple data elements with a single instruction (SIMD)
4. Column data store
• Minimal I/O
· I/O is only performed on the columns and values that match query predicates
· As queries progress through a pipeline, the working set of pages is reduced
• Work performed directly on columns
· Predicates, joins, scans, etc. all work on individual columns
· Rows are not materialized until absolutely necessary to build a result data set
• Improved memory density
· Columnar data is kept compressed in memory
• Extreme compression
· More data values can be packed into a very small amount of memory or disk space
• Cache efficiency
· Data is packed into cache-friendly structures
5. Core-friendly parallelism
• Designed to take advantage of the cores available and to always drive multi-core parallelism when processing queries
· Maximizes CPU cache
6. Scan-friendly memory caching
· New algorithms cache data in RAM more effectively
· Higher percentage of interesting data fits in memory
· Enhanced caching strategy improves buffer pool utilization
7. Data skipping
· Automatic detection and avoidance of large sections of data that is not needed to resolve a query
· Order of magnitude savings in I/O, RAM, and CPU
· No user action required to define or use – truly invisible
What Else Is New In DB2 10.5?
In addition to BLU Acceleration, DB2 10.5 offers the following new features and functionality:
* Simplified product packaging
* Simplified Fix Pack installs
* Enhanced tooling
* Extended row size support
* Expression-based indexes
* Ability to exclude NULL index keys
* NOT ENFORCED primary keys and unique constraints
* DB2 Advanced Copy Services (ACS) scripting for snapshot backup and restore operations
* Online topology changes to DB2 pureScale clusters
* Explicit hierarchical locking in DB2 pureScale environments
* Workload balancing on subsets of DB2 pureScale members (multi-tenancy)
* In-place table REORGs in DB2 pureScale environments
* Member-specific self-tuning memory manager (STMM) in DB2 pureScale environments
* Mobility of backup images between DB2 10.5 Enterprise Server Edition and DB2 pureScale
* Support for HADR in DB2 pureScale environments
DB2 Server Management
Seventeen percent (17%) of the DB2 10.5 Database Administration for LUW Upgrade Exam (Exam 311) is designed to test your knowledge of basic DB2 10.5 server management.
Servers, Instances, and Databases
DB2 for Linux, UNIX, and Windows (otherwise known as DB2 for LUW) sees the world as a hierarchy of objects. Servers occupy the highest level of this hierarchy, instances occupy the second level, and databases make up the third.
DB2 Servers, Instances, and Databases – Illustrated
Configuring The DB2 Server Environment
The behavior of a DB2 server is controlled, in part, by a collection of environment variables, whose values are stored in the following profile registries:
* The DB2 Global Level Profile Registry
* The DB2 Instance Level Profile Registry
* The DB2 Instance Node Level Profile Registry
The contents of each of these profile registries can be examined using the db2set command; this command is also used to assign values to profile registry variables.
Configuring DB2 Instances
The behavior of a DB2 instance is controlled, in part, by a special configuration file. This file consists of several different parameters; the values assigned to each parameter can be viewed or altered using the following commands:
* GET DATABASE MANAGER CONFIGURATION
* UPDATE DATABASE MANAGER CONFIGURATION
* RESET DATABASE MANAGER CONFIGURATION
Configuring DB2 Databases
As with DB2 instances, the behavior of a DB2 database is controlled, in part, by a special configuration file. This file consists of more than 100 different parameters and the value assigned to each parameter can be viewed or modified using the following commands:
* GET DATABASE CONFIGURATION
* UPDATE DATABASE CONFIGURATION
* RESET DATABASE CONFIGURATION
Configuring a DB2 10.5 Database Environment For BLU Acceleration
The easiest way to configure a DB2 10.5 database environment for BLU Acceleration is by assigning the value ANALYTICS to the DB2_WORKLOAD registry variable before any databases are created. For example:
db2set DB2_WORKLOAD=ANALYTICS
Assigns appropriate values to a set of configuration parameters to enable DB2 10.5 BLU Acceleration
Behavior When DB2_WORKLOAD Is Set To ANALYTICS
When the DB2_WORKLOAD registry variable is assigned the value ANALYTICS:
* The dft_table_org (default table organization for user tables) database configuration parameter is set to COLUMN.
* The dft_degree (default degree of intrapartition parallelism) database configuration parameter is set to ANY.
* The pagesize (default database page size) database configuration parameter is set to 32 KB.
* The dft_extent_sz (default extent size) database configuration parameter is set to 4.
* The intra_parallel (use intrapartition query parallelism) database manager configuration parameter is set to YES.(Intrapartition parallelism is turned on at the instance level; however, this behavior does not take effect until the instance is stopped and restarted.)
* The values of the sortheap (sort heap) and sheapthres_shr(sort heap threshold for shared sorts) database configuration parameters are calculated and set specifically for an analytics workload.
* The util_heap_sz (utility heap size) database configuration parameter is set to a value that takes into account the additional memory that is required to load data into column-organized tables.
* The auto_reorg (automatic reorganization) database configuration parameter is set to ON.
* A default space reclamation policy is installed and automatic table maintenance is configured so that empty extents are automatically returned to table space storage for reuse whenever data is deleted from column-organized tables.
Prerequisites For Creating BLU Acceleration Databases
Once the value ANALYTICS has been assigned to the DB2_WORKLOAD registry variable:
* Only single-partition databases can be created.
* All databases must use the UNICODE code set and IDENTITY collation.
* The auto_runstats (automatic table RUNSTATS operations) database configuration parameter must be set to OFF.
* The util_heap_sz (utility heap size) database configuration parameter should be set to at least 1,000,000 pages or assigned the value AUTOMATIC.
DB2 Workload Manager
DB2 Workload Manager (WLM) is a comprehensive workload management feature that can help identify, manage, and control database workloads (applications, users, and so on) so that database server throughput and resource utilization are maximized.
WLM is designed to limit the number of disruptive activities that can run concurrently and to stop the execution of activities that exceed predefined boundaries.
DB2 Workload Manager Objects
The DB2 Workload Manager architecture consists of the following objects:
* Service classes
A service class acts as a unique execution environment for any grouping of work that you can assign resources to, control, and monitor.
* Workloads
A workload is an object that is used to identify submitted database work or a user connection so it can be managed.
* Thresholds
A threshold is an object that sets a predefined limit over specific criteria, such as the consumption of a specific resource or duration of time.
* Work action sets
A work action set is an object that dictates what is to happen when the work of interest is detected.
* Work class sets
A work class set is an object that defines the characteristics of the work of interest.
* Histogram templates
A histogram is a graphical display of tabulated frequencies; a histogram template is an object with no predefined measurement units that is used to specify what a histogram should look like.
WLM Objects That Aid In Concurrency Control
To aid in concurrency control, the following default workload management objects are created automatically for new (or upgraded) DB2 10.5 databases:
* A SYSDEFAULTMANAGEDSUBCLASS service subclass (under the SYSDEFAULTUSERCLASS superclass)
The service subclass where heavyweight queries against column-organized tables run and can be controlled and monitored as a group.
* A SYSDEFAULTCONCURRENT threshold (under the CONCURRENTDBCOORDACTIVITIES threshold)
The threshold that is applied to the SYSDEFAULTMANAGEDSUBCLASS subclass to control the number of concurrently running queries that are running in that subclass.
The threshold on the SYSDEFAULTMANAGEDSUBCLASS service subclass is enabled by default for newly created databases only.
* A SYSMANAGEDQUERIES work class and a SYSDEFAULTUSERWCS work class set
The work class and work class set that identify the class of heavyweight queries to control.
* A SYSMAPMANAGEDQUERIES work action and a SYSDEFAULTUSERWAS work action set
The work action and work action set that maps all queries that fall into the SYSMANAGEDQUERIES work class to the SYSDEFAULTMANAGEDSUBCLASS service subclass.
The SYSDEFAULTUSERWAS work action set is enabled by default so that queries that meet the criteria specified for the SYSMANAGEDQUERIES work class run in the SYSDEFAULTMANAGEDSUBCLASS service subclass.
DB2 Warehouse
DB2 Warehouse is a suite of products that combines the strength of DB2 with a data warehousing infrastructure; the following components are provided in DB2 Warehouse:
* DB2 Warehouse Data Server
* DB2 Warehouse Application Server
* DB2 Warehouse Client
New Functionality In The SQL Warehousing Tool (SQW)
Starting with DB2 10.5, the following features are available with the SQL Warehousing Tool (SQW) component of the DB2 Warehouse Client:
* BLU Acceleration support
* Oracle-compatibility mode support
* Secure Shell (SSH) protocol support in the Administration Console
* Ability to establish a secure connection without a password in Design Studio
Physical Design
Thirty-seven percent (37%) of the DB2 10.5 Database Administration for LUW Upgrade Exam (Exam 311) is designed to test your ability to use many of the new features that were introduced with DB2 10.5.
Table Spaces
Table spaces are used to control where data for a database is physically stored and to provide a layer of indirection between database objects (i.e., tables, indexes, etc.) and the storage containers where an object's data resides. Two types of table spaces exist:
* System Managed Space (SMS)
* Database Managed Space (DMS)
Automatic Storage Table Spaces
If a database is using automatic storage, another type of table space known as an automatic storage (AS) table space can exist. Automatic storage table spaces are an extension of SMS and DMS table spaces:
* Regular and large automatic storage table spaces are created as auto-resizing DMS table spaces (that use files for containers)
* Temporary automatic storage table spaces are created as SMS table spaces (that use directories for containers)
The Traditional Approach To Database Storage
Traditionally, storage for DB2 databases has been managed at the table space level, using explicit SMS and DMS table space container definitions. But there are some disadvantages to this approach:
* When DMS table spaces are used, the DBA is responsible for extending or adding containers to them whenever additional space is required; consequently, storage space consumption must be closely monitored
* When SMS table spaces are used, the operating system automatically grows them as space is consumed; but, the overhead of using SMS table spaces has an impact on performance
The Traditional Approach To Database Storage – Illustrated
Automatic Storage Databases
Automatic storage databases make storage management easier. Instead of relying on a DBA to manage storage at the table space level using explicit container definitions, the DB2 database manager is responsible for managing storage at the database level, creating, extending or adding containers as needed, to support table space growth.
Automatic Storage Databases – Illustrated
Tables
In a relational database, a table represents a set of data values that have been organized into vertical columns (or fields) and horizontal rows (or records); where a row and column intersect is referred to as a value (or cell).
Extended Row Size Support
Normally, records in a table are organized into pages that are 4, 8, 16, or 32 KB in length. And prior to DB2 10.5, the maximum number of bytes allowed in a single row was dependent upon the page size of the table space being used to store the table – an attempt to create a table with a row length that exceeded the maximum length allowed by the underlying table space would result in an error.
With DB2 10.5's extended row size support, it is now possible to create a table whose row length exceeds the underlying table space's page size.
Enabling Extended Row Size Support
Extended row size support is enabled by assigning the value ENABLE to the extended_row_sz database configuration parameter.
Once this assignment has been made, a table whose row length exceeds the maximum length allowed by the underlying table space can be created – provided the table has at least one column that has a varying-length string data type (i.e., VARCHAR or VARGRAPHIC).
How Extended Row Size Support Is Implemented
If extended row size support is enabled, whenever a row is inserted or updated in a table and the physical length of the data for the row exceeds the maximum record length allowed (by the underlying table space), a subset of the data stored in a varying-length string column is moved out of the row and stored as large object (LOB) data.
It's important to note that when this happens, the varying-length string column's data type does not change (i.e., the column's data type is not converted to CLOB or DBCLOB).
Column-Organized Tables
The most prominent feature found in DB2 10.5 is a new, in-memory columnar table type. Unlike row-organized tables, which store data for complete records (rows) in pages and extents, column-organized tables store data for individual columns in pages and extents.
Storing data by column reduces the amount of I/O needed to process workloads that consist of complex queries (which are commonly characterized by multi-table joins, grouping, aggregation, and table scans).
How Row-Organized Table Data Is Stored
How Column-Organized Table Data Is Stored
Other Differences Between Row and Column-Organized Tables
Other ways in which column-organized tables differ from row-organized tables include:
* Indexes are not needed (nor can they be created)
* REORGs do not have to be manually performed (they're done automatically)
* Multidimensional clustering (MDC) is not needed (nor is it allowed)
* Materialized query tables (MQTs), materialized views, and statistical views are not needed (nor are they allowed)
* Table partitioning is not supported
(Continues...)
Excerpted from DB2 10.5 DBA for LUW Upgrade from DB2 10.1 by Roger E. Sanders. Copyright © 2016 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
About the Author vi
Introduction vii
Part 1 DB2 10.5 Overview 1
Part 2 DB2 Server Management 5
Part 3 Physical Design 15
Part 4 Monitoring DB2 Activity 41
Part 5 High Availability 59
Part 6 Utilities 77
Appendix A DB2 10.5 DBA for LUW Upgrade from DB2 10.1 Exam (Exam 311) Objectives 93
Appendix B Practice Questions 97
Appendix C Answers to Practice Questions 121