Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

[原创]DB2 v10.5 C2090-311认证

2016-03-01
DB2

DB2 v10.5 C2090-311认证大纲内容和笔记

考试只有30题,题量不多

DB2 v10.5 C2090-311认证大纲

Test C2090-311: IBM DB2 10.5 DBA for LUW Upgrade from DB2 10.1 C2090-311

IBM Certified Database Administrator - DB2 10.5 DBA for LUW Upgrade from DB2 10.1

C2090-311-Path


Section 1 - DB2 Server Management (17%)

Demonstrate the ability to configure DB2 workload for analytics system

DB2_WORKLOAD=ANALYTICS

DB CFG DFT_TABLE_ORG COLUMN

DB CFG DATABASE PAGE SIZE = 32K

DB CFG DFT_DEGREE=ANY

DB CFG DFT_EXTENT_SZ=4

Importance of UTIL_HEAP_SZ CFG for COLUMN ORGANIZED tables load

Demonstrate the ability to use autonomic features in DB2 10.5 BLU Acceleration

AUTO_RUNSTATS is not supported for COLUM ORGANIZED tables

Automatic Space Reclaims in BLU acceleration tables

Demonstrate knowledge of Data Studio 4.1

Demonstrate the ability to use Automated workload management when workload set to ANALYTICS

Section 2 - Physical Design (37%)

Demonstrate the ability to understand industry leading DB2 10.5 BLU Acceleration 7 big ideas

Simple to Impement and Use - No Indexes, No MDC, No MQT, No Partitioning, No Statistical Views

Friendly Huffman encoding - More frequent the values, the fewer bits in dictionary and packed registers. Performing most operations on encoded values.

Usage of SIMD Instruction Sets

Maximize CPU cache

Column Store - Minimal IO / Work performed directly on columns / Improved memory density / Cache efficiency

Memory Caching Facility - Leave the interesting data instead of traditional LRU or MRU algorithms

Data Skipping through SYNOPSIS Tables

Demonstrate the ability to understand and implement compression features

Table Compression - Data Compression / Value Compression / Adaptive Compression / COLUMN ORGANIZED table compression

Database backup compression

Demonstrate the ability to use expression based indexes and the statistics on those expression based indexes

Using RUNSTATS

Using Statistics Profiles

Automatic Statistics Collection

Knowledge of Oracle Migration and Compatibility Features in DB2 10.5

Demonstrate the ability to use Information Uniqueness Constraints

Knowledge of SQL compatibility enhancements

Extended row size support

Exclude null keys support

Section 3 - Monitoring DB2 Activity (17%)

Demonstrate the ability to use new monitor elements

Demonstrate the ability to use new monitor elements to monitor dynamic prefetch requests for data in column-oriented tables.

Demonstrate the ability to use new new monitor elements to measure column data size.

Demonstrate the ability to use new monitor elements to measure time spent in Columnar Data Engine (CDE)

Demonstrate knowledge of the new EXPLAIN enhancements Knowledge of SQL compatibility enhancements

Demonstrate the ability to understand new CTQ (Column Table Queue) Operator in the explain

Knowledge of new monitoring function - MON_GET_ROUTINE

Demonstrate knowledge of HADR monitoring enhancements

HEARTBEAT_MISSED

HEARTBEAT_EXPECTED

STANDBY_SPOOL_PERCENT

STANDBY_ERROR_TIME

Section 4 - High Availability (17%)

Knowledge of enhancements to pureScale

Understand the ability to use Online REORG in pureScale

Knowledge of replay member in pureScale standby cluster

Knowledge of HADR setup in a pureScale cluster environment

Demonstrate the ability to undestand Rolling Fix Pack Updates in pureScale

Demonstrate the ability to undestand STMM Multi-Tenancy Feature in pureScale

Demonstrate the ability to understand Explicit Hierarchical Locking Multi-Tenancy in pureScale system

Knowledge of DB2 Advanced Copy Services customized scripts

Section 5 - Utilities (13%)

Demonstrate the ability to use IBM Optim Query Workload Tuner

The Workload Table Organization Advisor

Support for comparing access plans before and after

Access Plan Explorer and in access plan graphs for the new CTQ operator

Demonstrate the ability to use DB2 10.5 Utilities

db2convert

LOAD Command - Additional ANALYZE phase for COLUMN ORGANIZED tables

Default AUTOMATIC STATISTICS COLLECTION in LOAD command for COLUMN ORGANIZED tables


练习题如下:

C2090-311

Tips:

1.Query Workload Tuner license 安装和激活有效:

通过:

DataStudio, license activation kit

参考:

Installing Data Studio and the license activation kit for IBM InfoSphere Optim Query Workload Tuner through a wizard

http://www.ibm.com/support/knowledgecenter/SS7LB8_3.1.1/com.ibm.datatools.qrytune.installconfig.doc/topics/ioqwt311bothwiz.html?cp=SS7LB8_3.1.1&lang=en

A license activation kit for InfoSphere Optim Query Workload Tuner, Version 4.1.1 This kit allows you to activate the full set of features for tuning query workloads and single SQL statements. This activation is required once only. One person installs the license activation kit together with the Data Studio client, Version 4.1.1.

http://www.ibm.com/support/knowledgecenter/SS7L9Q_4.1.1/com.ibm.datatools.qrytune.installconfig.doc/topics/ioqwt411_top.html?cp=SS7L9Q_4.1.1&lang=en

2.

3.EXTENDED_ROW_SZ 设置:默认设置

创表超过4k大小的表 4k的pagesize之后容许overflow后找不到8k pagesize会报错。

[db2inst1@oc0644314035 ~]$ db2 connect to my_db

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.5
 SQL authorization ID   = DB2INST1
 Local database alias   = MY_DB

[db2inst1@oc0644314035 ~]$ db2 get db cfg | grep -i extended_row_sz
 Extended row size support             (EXTENDED_ROW_SZ) = ENABLE
[db2inst1@oc0644314035 ~]$ db2 "create table tab_a(col1 int not null, col2 varchar(5000)) organize by (col1)"
DB20000I  The SQL command completed successfully.
[db2inst1@oc0644314035 ~]$ db2 "create table tab_b(col1 int not null,col2 varchar(5000))"
DB20000I  The SQL command completed successfully.
[db2inst1@oc0644314035 ~]$ db2 "create table tab_c(col1 int not null,col2 varchar(5000)) organize by key sequence (col1 starting from 1 ending at 100000) allow overflow"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0286N  A table space could not be found with a page size of at least "8192" 
that authorization ID "DB2INST1" is authorized to use.  SQLSTATE=42727
[db2inst1@oc0644314035 ~]$ db2 "create table tab_d(col1 int not null,col2 varchar(5000)) partition by range(co1) (starting 0 exclusive ending 10 inclusive)"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL8027N  The table partitioning feature is being used without a DB2 
Enterprise Server Edition license.  DB2 has detected that the table 
partitioning feature is being used without a DB2 Enterprise Server Edition 
license.  Ensure that you have purchased a valid DB2 Enterprise Server Edition 
entitlement from your IBM representative or authorized dealer and have updated 
your license using the db2licm command.
[db2inst1@oc0644314035 ~]$  

9.column-orgnized talbe压缩:

Actionable compression

10.column-organized的限制:

Queries using the RR or RS isolation level are not supported with column-organized tables.

Currently Committed可以使用

11.POOL_FAILED_ASYNC_TEMP_COL_REQS

POOL_FAILED_ASYNC_TEMP_COL_REQS

The number of times an attempt to queue a column-organized prefetch request for temporary table spaces was made but failed.

One possible reason is the prefetch queue is full and a request could not be obtained from the free list.

尝试让临时表空间的列组织预取请求排队但失败的次数。一个可能原因是预取队列已满并且未能从空闲列表获取请求。

注: 在 DB2® V10.5 中,由于 列组织 临时表当前不受支持,因而此元素返回 0。

12.rolling FixPack update for DB2 pureScale

db2iupdt -commit_level

db2iupdt -commit_level

-commit_level
	Commits the pureScale instance to a new level of code. This parameter is mandatory in DB2 pureScale environments.


-update
	This parameter is used to update the interconnect netnames used by the CF or member. To update the netname of a member or CF, the instance can be running but the specific target member or specific target CF must be stopped. The db2iupdt -update command must be run from the target CF or target member.

13.DB2 Advanced Copy Services(ACS) scripts用于:

To perform snapshot backup and restore operations on storage devices that do not have a DB2 ACS API vendor library.

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.ha.doc/doc/c0061163.html?lang=zh

14.HADR for DB2 v10.5 pureScale

同步模式都不行,支持ASYNC/SUPERASYNC

The HADR synchronization modes SYNC and NEARSYNC are not supported. You must specify either the ASYNC or SUPERASYNC option for the hadr_syncmode configuration parameter.

15.EHL for DB2 v10.5 pureScale

显式分层锁定

缺省情况下,未在 DB2 pureScale 环境中启用 EHL。但是,可通过使用 opt_direct_wrkld 数据库配置参数来启用或禁用 EHL。

四种状态:NOT_SHARED / DIRECTED_ACCESS SHARED/FULLY SHARED

不适用于 系统目录表:通常不在成员之间共享的表,例如,临时表

EHL is designed to improve DB2 pureScale performance by avoiding CF communications for tables that are accessed from only one member.

16.column-organized table 执行会fail的动作

Setting the AUTO_RUNSTATS database configuration parameter to ON

17.expression-based indexes true的:

基于表达式的索引

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0061101.html?lang=en

简单的索引键由一连串一个或多个指定表列组成。与简单的索引相比,基于表达式的索引的索引键值与表列中的值不相同。会按您指定的表达式转换这些值。

18.which type of database are column-organized tables supported

C. Databases whose code set is Unicode.

19.基于表达式的索引的runstats

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0061106.html?lang=en

20.列组织表的Space reclamation

All rows in the extent where the update took place must be deleted before any space reclamation can occur. http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0061069.html?lang=en

After all rows in an extent where updates took place have been deleted.

21.列组织表执行计划的获取:

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/r0061828.html?lang=en

1.Set the EXPLAIN mode on by using the CURRENT EXPLAIN MODE special register as follows:

db2 SET CURRENT EXPLAIN MODE YES

2.Issue your query against column-organized tables.

3.Issue the db2exfmt command to format the contents of the explain tables and obtain the access plan. The following example shows you how to use this command against the SAMPLE database:

db2exfmt -d sample -1 -o output.exfmt

22.pureScale STMM

DB2 pureScale now allows per-member STMM tuning

INSTANCE_MEMORY DBM parm - per member setting

STMM does not control CF memory

STMM can manage same member memory pools as with DB2 ESE

Prior to DB2 10.5 – One member (known as the tuning member) monitors member STMM memory configuration and propagates real-time changes to all other members – When the tuning member is specified as -1, the tuning member is randomly selected every time the database is activated – If the member on which the tuner is running deactivates, the tuner will automatically start on another member – In order for a member to run the tuner, the database must be active on that member and that member must have self_tuning_mem set to ON

DB2 10.5

– STMM can tune every member independently
– Default setting

23.

section_exec_with_col_references -“具有按列组织的引用的节执行”监视元素

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0053945.html?lang=zh

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.mon.doc/doc/r0060865.html?lang=zh

24.staticstics use profile / Load

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0023577.html?lang=en

STATISTICS USE PROFILE Instructs load to collect statistics during the load according to the profile defined for this table. This is the default for column-organized tables. The profile must be created before issuing the LOAD command. The profile is created by the RUNSTATS command. For row-organized tables, if the profile does not exist and this parameter is specified, a warning is returned and no statistics are collected. For column-organized tables, if the profile does not exist and this parameter is specified, the load utility uses the same default RUNSTATS command options as those that are used during an automatic runstats operation.

During load, distribution statistics are not collected for columns of type XML.

25.DB2_COMPATIBILITY_VECTOR

DB2 环境以便启用 Oracle 应用程序 http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.porting.doc/doc/t0054729.html?lang=zh

DB2_COMPATIBILITY_VECTOR http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.porting.doc/doc/r0052867.html?lang=zh-cn

  1. HADR for pureScale的switch 什么情况下可以takeover:

You can only perform a role switch between the primary and standby databases if the databases are in one of the following states (for DB2® pureScale® environments, if every stream meets one of these conditions):

peer state
remote catchup state, when the synchronization mode is SUPERASYNC
assisted remote catchup state (DB2 pureScale environments only)

对等状态
同步方式为 SUPERASYNC 时的远程同步复制状态
受助远程同步复制状态(仅限 DB2 pureScale 环境)

27.

什么表可以通过db2convert来转换为 column-organized 表

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0060728.html?cp=SSEPGG_10.5.0%2F3-6-2-6-36&lang=en

You cannot convert the following table types into column-organized tables:

Range clustered tables

Typed tables

Materialized query tables

Declared global temporary tables

Created global temporary tables

Important: Range partitioned tables, MDC tables, and ITC tables are not converted by default. To convert these table types, use the -force option.

-force

Specifies that all table types are to be converted, including range partitioned tables, multidimensional clustering (MDC) tables, and insert time clustering (ITC) tables.

db2convert - Convert row-organized tables into column-organized tables

Converts one or all row-organized user tables in a specified database into column-organized tables. The row-organized tables remain online during command processing. For monitoring purposes, the command displays statistics about the conversion.

28.关于NOT ENFORCED unique constraints的描述:

29.DB2 Storage Optimization Feature的压缩type:

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.licensing.doc/doc/r0053238.html?lang=en

backup compression for all db2 edition

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0059054.html?lang=en

30.intraquery parallelism:

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/t0061529.html?lang=en

All DML operations that reference column-organized tables

The ALTER TABLE ADD UNIQUE / PRIMARY KEY CONSTRAINT statement against a column-organized table if rows were inserted into the table

The RUNSTATS command against a column-organized table

The LOAD command against a column-organized table

空表加primary key约束就不需要

——————————- EOF ——————————-


Comments