Lin Hong's TECH Blog!

【学习】 2025年阅读清单

[原创]DB2 参数：DB2_OVERRIDE_BPF

2016-04-06

DB2

DB2 参数 DB2_OVERRIDE_BPF

最近碰到比较棘手的app，上次实例不正常停止后，db2start启动不了，临时扩大了实例内存（系统有多余内存，扩大一倍40g后，运行一段时间db2 force application后实例直接停止了，需要重新启动实例。） DB2 db2set参数：DB2_OVERRIDE_BPF介绍和使用场景

Read All
转：基于CDC复制技术实现DB2大版本升级迁移

2016-03-28

DB2

DB2 CDC

当前IT系统的软硬件迭代越来越频繁，特别是软件产品，客户生产系统中采用的软件都面临原厂的EOS风险。EOS即End Of Support，这是每个客户的IT部门都要面临的运维风险。当生产系统出现问题却又得不到原厂售后服务支持的时候，是一个多么悲催的事情，其中的麻烦谁遇谁知道，所以说跟着原厂的步伐进行大版本更新升级乃上上策。这是一个软件生命周期管理的课题，软件大版本的升级迁移是客户IT运维部门都要接受的重大挑战，同时也是IT运维服务厂商的重大挑战，当然如果IT运维服务厂商有合适的解决方案，那将是重大机遇。

Read All
Oracle性能调优工具-Statpack

2016-03-23

Oracle

Oracle,statpack,性能调优

Oracle性能调优工具-Statpack:什么是statpack？怎么使用？怎么查看？

Read All
VIM命令图解

2016-03-22

Bigdata

bigdata

PDF桌面图解VIM命令。

Read All
[原创]DB2 简单入门系列-1.安装

2016-03-10

DB2

DB2 原创

DB2 安装

Read All
[原创]DB2 简单入门序列-0.Introduction

2016-03-10

DB2

DB2 原创

DB2 简单入门系列

Read All
[原创]DB2 licence report

2016-03-01

DB2

DB2 db2licm licence

“Problem(Abstract): When db2licm is used to generate a compliance report, it shows Violation against a certain feature. This indicates that the feature is not licensed, but has been used. “

DB2 License compliance report returns a VIOLATION status against a certain feature

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

2016-03-01

DB2

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

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 ——————————-
Read All
[原创]DB2 db2top 介绍和使用

2016-02-24

DB2

DB2工具 db2top 原创

“DB2中的db2top使用实战终结”

打算汇总db2top的使用，正在编辑文档。

Read All
[原创]DB2 db2expln/db2advis/db2batch简单介绍和实战

2016-02-23

DB2

原创 DB2 db2batch db2expln db2advis
- 摘要
摘要

db2expln将存取计划以文本形式输出，它只提供存取计划中主要的信息，并不包含每一个操作占用多少 CPU、I/O、占用 Buffer 的大小以及使用的数据库对象等信息，方便阅读。但是 db2expln 也会将各项有关存取计划的信息存入 Explain 表中，用户可以使用 db2exfmt 察看详细的格式化文本信息。

db2advis是DB2提供的另外一种非常有用的命令。通过该命令DB2可以根据优化器的配置以及机器性能给出提高查询性能的建议。这种建议主要集中于如何创建索引，这些索引可以降低多少查询代价，需要创建哪些表或者 Materialized Query Table(MQT) 等。

db2advis结合db2top重放抓取top5 sql语句可以使用db2advis进行简单的分析

前面工具和命令只提供了查询的估算代价，有些时候估算代价和实际的执行时间并不是完全呈线形关系，有必要实际执行这些查询。db2batch就是这样一个 Benchmark工具，它能够提供从准备到查询完成中各个阶段所花费地具体时间，CPU时间，以及返回的记录。
Read All
[原创]DB2 存储结构（db2dart分析）

2016-02-23

DB2

DB2 db2dart 原创

“通过db2dart数据块/索引块,了解DB2存储结构”

Read All
Benchmark-TPC

2016-02-22

DB2

DB2 MON_GET 原创
- TPC
TPC

资源：

http://www.pilhokim.com/index.php?title=Project/EFIM/TPC-H

http://tech.it168.com/a2012/0828/1390/000001390560_6.shtml

http://www.tpc.org/tpch/default.asp
Read All

34/37

DB2 v10.5 C2090-311认证大纲

Section 1 - DB2 Server Management (17%)

Section 2 - Physical Design (37%)

Section 3 - Monitoring DB2 Activity (17%)

Section 4 - High Availability (17%)

Section 5 - Utilities (13%)

Tips:

摘要

TPC