Category Archives: db2

About Aggregation in Oracle and DB2

聚合分析相关的语句、函数往往是数据仓库项目中最常用的知识,而使用Oracle来进行OLTP项目的人往往不需要了解这些。Oracle 10.2把这些相关的内容放在Data Warehousing Guide文档中,专门使用了一个章节进行了详细的介绍;DB2 9.5则将其放在数据库基本知识SQL中,分散在Functions和Queries两个章节。相比之下,本人更喜欢Oracle的文档结构。

总体介绍

除了grouping_id和group_id这两个函数外,Oracle有的特性,在DB2中都有,而且作用和用法都是一样的:

Aggregation Feature Oracle DB2
cube extension
rollup extension
grouping() function
grouping_id() function  
group_id() function  
grouping sets expression
with clause

关于以上特性,我有一个常用的查询:

select decode(grouping(a.subscription_id),1,’All’,subscription_id) subscription_id,
            decode(grouping(a.service_num),1,’All’,service_num) service_num,
            decode(grouping(a.subject_id),1,’All Subject’,a.subject_id) subject_id,
            decode(grouping(b.subject_name),1,’All Subject’,b.subject_name) subject_name,
            sum(a.total_amount)/1000 pre_fee,
            sum(a.disc_total_amount)/1000 disc_fee,
            sum(a.total_amount-a.disc_total_amount)/1000 bill_fee
from zjuinv1o.acc_charge_201001 a
            left join zjuinv1o.acc_subject b
            on a.subject_id=b.subject_id
where a.account_id=109600235
group by rollup((a.subscription_id,a.service_num),
            (a.subject_id,b.subject_name));

该查询的返回结果如下:

图中红色框中的记录就是聚合分析的效果。

聚合分析特性中,grouping_id和group_id这两个函数,DB2不支持,那么这两个函数到底是干什么用的呢?

grouping_id函数

grouping_id函数,我的理解,它是从grouping函数延伸而来的,它们的相同点是:

  1. 当只带1个字段作为参数时,grouping和grouping_id的返回值相同。

它们的不同点是:

  1. grouping函数只能带1个字段作为参数,grouping_id没有限制;
  2. grouping函数只能返回1或0,而grouping_id返回值有更多可能。

Oracle官方文档中有个表格,使用BitVector的方式说明了grouping_id值的生成:

Table 20-2 GROUPING_ID Example for CUBE(a, b)

Aggregation Level Bit Vector GROUPING_ID
a, b 0 0 0
a 0 1 1
b 1 0 2
Grand Total 1 1 3

我要补充的是,bit vector是看grouping_id的参数而不是cube、rollup或grouping sets的参数,也就是说grouping_id的值与其后的参数个数、顺序有关系,对于cube(a,b),相同的记录中,grouping_id(a,b) 和grouping_id(b,a)、grouping_id(a) 、grouping_id(b)的值都不尽相同。

group_id函数

group_id函数,它的作用其实很简单,就是用来判断group是否重复,用来剔除重复的记录。如果是重复记录,它的返回值会大于0,第一次重复返回1,第二次重复返回2,etc.一般情况下,如果查询中使用了grouping sets和rollup或cube,有可能会出现重复记录,我一般这么用:

select …
where …
having group_id() = 0
group by grouping sets(…)


Data Warehouse For Ever原创文章,转载请注明出处

关于DB2v9.5的审计

如何进行DB2的审计,网上有很多文章介绍,基本上都是关于DB2版本8或者更老的。而新版的DB2审计功能,关于用户执行的SQL命令的记录,与老版本的有较大区别。

老版本的SQL命令,在CONTEXT审计事件STMTTEXT字段中,而新版本的SQL statement,在EXECUTE审计事件STMTTEXT字段中。以下是在DB2 v9.5版本中使用SQL审计的步骤:

  1. 准备工作:修改audit_buf_sz参数并重启实例,否则审计活动将严重影响数据库性能:

    db2 update dbm cfg using audit_buf_sz 32
    db2stop force
    db2start

  2. 将secadm权限授权给安全管理员用户,编辑sqllib/misc目录下的脚本db2audit.ddl,创建容纳审计数据的表:

    db2 “GRANT SECADM ON DATABASE TO USER BI”
    db2 create schema audit
    db2 connect to mybi user bi using bipwd
    db2 set current schema audit
    db2 -tvf /db2home/db2inst1/sqllib/misc/db2audit.ddl

  3. 创建EXECUTE类型的审计POLICY,在db2 -t命令行执行(注意此类命令必须要COMMIT):

    CREATE AUDIT POLICY STATEMENTS CATEGORIES EXECUTE WITH DATA
       STATUS BOTH ERROR TYPE AUDIT;
    COMMIT;

  4. 启用该数据库审计,在db2 -t命令行执行:

    AUDIT DATABASE USING POLICY STATEMENTS;
    COMMIT;

  5. 建立审计文件的备份目录和装载文件目录:

    cd /db2home/db2inst1/sqllib/security
    mkdir auditarchive
    mkdir auditdelasc

  6. 使用以下命令,将审计文件装载到数据库:

    \rm /db2home/db2inst1/sqllib/security/auditarchive/*
    \rm /db2home/db2inst1/sqllib/security/auditdelasc/*
    db2audit archive database mybi to /db2home/db2inst1/sqllib/security/auditarchive
    db2audit extract delasc to /db2home/db2inst1/sqllib/security/auditdelasc from files /db2home/db2inst1/sqllib/security/auditarchive/*
    db2 connect to mybi user bi using bipwd
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/validate.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.validate”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/context.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.context”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/audit.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.audit”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/checking.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.checking”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/sysadmin.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.sysadmin”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/objmaint.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.objmaint”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/secmaint.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.secmaint”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/execute.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.execute”
    db2 terminate

    如果不是系统管理员而是安全审计员BI用户,则可以使用SYSPROC.AUDIT_ARCHIVESYSPROC.AUDIT_DELIM_EXTRACT两个存储过程替代db2audit archive和db2audit extract命令。


Data Warehouse For Ever原创文章,转载请注明出处

谁说DB2不支持竖线分隔符?

我们ETL开发人员在做数据装载时,发现如果使用“|”作为分隔符,无论是export还是load,DB2都会报SQL3017N的错误。

SQL3017N  A delimiter is not valid or is used more than once.

SQL3017N错误的产生,原因如下(摘自官方文档):

对于“定界”ASCII (DEL) 文件,发生以下两种错误之一:

  • 对列定界符、字符串定界符或小数点字符指定的字符无效。
    • 对于 SBCS 或 UTF-8 数据,定界符的有效范围是 0×00 – 0x7F(包括 0×00 和 0x7F 在内)。
    • 对于 MBCS 数据,定界符的有效范围是 0×00 – 0x3F(包括 0×00 和 0x3F 在内)。
    • 对于 EBCDIC MBCS 数据,定界符的有效范围是 0×00 – 0x3F(包括 0×00 和 0x3F 在内),但是,有一种例外情况就是,定界符不能为 SHIFT-OUT(0x0E)或者 SHIFT-IN(0x0F)字符。
  • 为多个前述项目指定了同一个字符。

经过查询,我们数据库的字符集是1386,属于MBCS数据,因此定界符最大是0x3F,而“|”的ASCII码为0x7C,超过了有效范围。解决办法,是做Export或者Load操作时,增加codepage选项,让DB2自动进行字符集转换:

EXPORT TO "/data/temp/card.dat" OF DEL
MODIFIED BY codepage=1208 COLDEL|
MESSAGES "/data/temp/card.log"
SELECT * FROM BI.STG_CDR_OBS_CARD

LOAD CLIENT FROM "/data/temp/card.dat" OF DEL
MODIFIED BY CODEPAGE=1208 COLDEL|
MESSAGES "/data/temp/card.log"
INSERT INTO BI.STG_CDR_OBS_CARD


Data Warehouse For Ever原创文章,转载请注明出处

查看DB2表空间容器的方法

方法一:查询CONTAINER_UTILIZATION或SNAPCONTAINER管理视图

SELECT * FROM SYSIBMADM.CONTAINER_UTILIZATION
WHERE TBSP_ID=7
ORDER BY DBPARTITIONNUM,CONTAINER_ID;

SYSIBMADM.CONTAINER_UTILIZATION视图提供了表空间的Container和空间使用情况等信息,其实这个管理视图是LIST TABLESPACE CONTAINERS命令的SQL版本。

SELECT * FROM SYSIBMADM.SNAPCONTAINER
WHERE TBSP_ID=7
ORDER BY DBPARTITIONNUM,CONTAINER_ID;

SYSIBMADM.SNAPCONTAINER视图与CONTAINER_UTILIZATION视图提供的信息基本相同,除了表空间使用情况的单位没有从字节转换成KB之外,其他看不出啥区别。这个管理视图是GET SNAPSHOT FOR TABLESPACES ON database-alias 命令的SQL版本。实际上,IBM的官方文档上解释,CONTAINER_UTILIZATION的数据是基于SNAPCONTAINER的信息。

IBM DB2 9.5与该视图相关的官方文档请看这里

方法二:执行list tablespace containers或get snapshot for tablespaces命令


$db2 list tablespace containers for 5

Tablespace Containers for Tablespace 5

Container ID = 0
Name = /dev/rlvtmp_n00_01
Type = Disk


$db2 list tablespace containers for 5 show detail

Tablespace Containers for Tablespace 5

Container ID = 0
Name = /dev/rlvtmp_n00_01
Type = Disk
Total pages = 1064960
Useable pages = 1064928
Accessible = Yes

get snapshot for tablespaces on database-alias的信息是最丰富的,额外提供了表空间bufferpool、直接读写、table space map等信息,可以据此发现表空间可能存在的性能问题。但是需要注意的是,通过命令的方式,只能看到当前所连接节点的相关信息,如果是多节点(DPF)环境,默认为0节点。如果要看其它节点的情况,则需要指定DB2NODE来显示指定连接节点,或者通过db2_all来在所有节点上运行相关命令。

方法三:执行db2pd -tablespaces命令


$db2pd -db zjbi2 -alldbp -tablespaces 7

Database Partition 1 — Database ZJBI2 — Active — Up 5 days 18:18:32

Tablespace 7 Configuration:
Address            Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg Name
0x0700000039F3E1A0 DMS  Large   32768  24       No   192      2     2         Off 4        0          23           TBS_CDR

Tablespace 7 Statistics:
Address            TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM        State      MinRecTime NQuiescers
0x0700000039F3E1A0 63897600   63897504   25619832   0          38277672   25644504   0×00000000 0          0         

Tablespace 7 Autoresize Statistics:
Address            AS  AR  InitSize    IncSize     IIP MaxSize     LastResize                 LRF
0x0700000039F3E1A0 No  No  0           0           No  0           None                       No 

Containers:
Address            ContainNum Type    TotalPgs   UseablePgs StripeSet  Container
0x0700000039F3E840 0          Disk    15974400   15974376   0          /dev/rlvcdr_n01_01
0x0700000039F3E9A0 1          Disk    15974400   15974376   0          /dev/rlvcdr_n01_02
0x0700000039F3EB00 2          Disk    15974400   15974376   0          /dev/rlvcdr_n01_03
0x0700000039F3EC60 3          Disk    15974400   15974376   0          /dev/rlvcdr_n01_04

Database Partition 2 — Database ZJBI2 — Active — Up 5 days 18:18:32

Tablespace 7 Configuration:
Address            Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg Name
0x0700000039F3E1A0 DMS  Large   32768  24       No   192      2     2         Off 4        0          23           TBS_CDR

Tablespace 7 Statistics:
Address            TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM        State      MinRecTime NQuiescers
0x0700000039F3E1A0 63897600   63897504   25694472   96         38202936   25719240   0×00000000 0          0         

Tablespace 7 Autoresize Statistics:
Address            AS  AR  InitSize    IncSize     IIP MaxSize     LastResize                 LRF
0x0700000039F3E1A0 No  No  0           0           No  0           None                       No 

Containers:
Address            ContainNum Type    TotalPgs   UseablePgs StripeSet  Container
0x0700000039F3E840 0          Disk    15974400   15974376   0          /dev/rlvcdr_n02_01
0x0700000039F3E9A0 1          Disk    15974400   15974376   0          /dev/rlvcdr_n02_02
0x0700000039F3EB00 2          Disk    15974400   15974376   0          /dev/rlvcdr_n02_03
0x0700000039F3EC60 3          Disk    15974400   15974376   0          /dev/rlvcdr_n02_04

以上db2pd命令带了三个参数,第一个参数-db指定了数据库的名称是zjbi2,第二个参数-alldbp指定该命令在所有数据库分区上执行,第三个命令-tablespaces指定了该命令只查看id为7的表空间情况。

db2pd命令不需要连接数据库,但必须在该表空间物理所在的主机上执行,该命令的执行结果反映的是该主机上所有节点的情况。如本人的DB2 DPF环境,有三台主机,主机A仅有管理节点(0节点),主机B和主机C各有4个数据节点。如果我要查询应用数据表空间的使用情况,则需要在主机B和主机C上分别执行db2pd命令(考虑数据表空间一般不占用0节点的情况)。


Data Warehouse For Ever原创文章,转载请注明出处

日均线的SQL写法

近期得到一个需求,是计算某调度流程一段时间的5日平均运行时长,并与当前运行时长一起展示,形成类似于股票行情的K线图。

SCHEDULE.RUNTIMEFLOWLOG表结构如下:

名称 数据类型 长度 备注
LOGID DECIMAL 20 ID,主键
FLOWID DECIMAL 11 流程ID
STATUS DECIMAL 11 流程状态
STARTTIME TIMESTAMP 10 开始时间
ENDTIME TIMESTAMP 10 结束时间
DATATIME VARCHAR 32 数据时间
FLOWTYPE DECIMAL 11  

两个计算字段:
cursec:当前运行时长,单位:秒
avgsec:5日平均运行时长,单位:秒
SQL如下(以DB2为例):

SELECT logid,
status,
datatime,
starttime,
MIDNIGHT_SECONDS (ENDTIME) - MIDNIGHT_SECONDS (STARTTIME) cursec,
AVG(MIDNIGHT_SECONDS (ENDTIME) - MIDNIGHT_SECONDS (STARTTIME))
OVER (PARTITION BY flowid, status
ORDER BY logid DESC
ROWS BETWEEN 1 FOLLOWING AND 5 FOLLOWING) avgsec
FROM SCHEDULE.RUNTIMEFLOWLOG
WHERE FLOWID = 471
ORDER BY logid DESC;

如为Oracle,只需要将cursec和avgsec计算秒数的部分做相应调整:
(CAST(ENDTIME AS DATE)-CAST(STARTTIME AS DATE))*3600*24
而分析函数的用法此处Oracle和DB2均通用,不需要任何调整。

以下是5日均线的效果图:5日均线图


Data Warehouse For Ever原创文章,转载请注明出处