Monthly Archives: October 2008

谁说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原创文章,转载请注明出处