Monthly Archives: September 2008

几个常用的find命令

1.查找指定文件且包含指定文字:
find /etc -name "passwd*" -exec grep "ljp" {} \;

2.删除当前目录下所有0字节的文件:
find . -size 0c -exec rm {} \;

3.删除当前目录下修改时间为30天以前的文件:
find . -mtime +30 -exec rm {} \;

4.查找1000字节以下的文件:
find . -size -1000c -exec grep "MAXLOGIN" {} \;

5.查找目录下0字节的、修改时间1天前的文件并删除
find /db2home/db2inst1/dumpfile -size 0 -mtime +1 -exec rm -f {} \;

6.查找同一行中含”download”和”ftp”的文件:
find . -iname "*.c" |xargs grep -l download |xargs grep -l ftp

7.查找所有2天内变更的文件并tar到一个文件中:
find /db2home -type f -mtime -2 | xargs tar -cvf db2homenew.tar


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

勿混用过滤条件(where)和连接条件(on)

SQL中,where子句是过滤条件,on子句是连接条件,两者不能混用。如果混用,可能会导致DB2产生错误的执行计划,从而引发严重的性能问题。很多新手比较容易犯此类错误,在没有发生严重的的性能问题时,往往因为没有引起注意而不会得到重视。下面是我在监控DB2性能问题时发现的一个问题SQL:

select t.* , case when u.user_id is not null then 1 else 0 end as flag
from shenll_gprs_flow_200908 t left join dw_user_m_200908 u
on t.user_id=u.user_id and u.is_bill=1 and u.user_type not in(2,3,4) and t.area_id='570'

该SQL语句将过滤条件写到关联条件中,导致DB2生成了错误的执行计划,使用了内连接NLJoin对两张大表进行了关联,引起了严重的性能问题。以下是上述语句的执行计划:

错误的执行计划

对该语句进行修改,使用正确的on语句和where语句:

select t.* , case when u.user_id is not null then 1 else 0 end as flag
from shenll_gprs_flow_200908 t left join dw_user_m_200908 u
on t.user_id=u.user_id
where u.is_bill=1 and u.user_type not in(2,3,4) and t.area_id='570'

上述SQL的执行计划如下:

正确的执行计划

从上面的执行计划中可以看到,DB2使用了哈希连接,整个SQL的执行成本从5亿降低到6万,执行效率得到了数据级的提升。


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

DB2归档及恢复试验

[试验环境]
1.单节点:Windows XP, DB2 9.5
2.8+1节点:AIX 5.2, DB2 9.5 + DPF

[试验步骤]
1. 修改日志文件大小参数

修改日志文件大小参数,改到最小,否则很难搞到日志爆满。
这是日志的原始大小:

$ls -l
total 24048
-rw——- 1 db2inst1 db2iadm1 4104192 Apr 09 10:46 S0000000.LOG
-rw——- 1 db2inst1 db2iadm1 4104192 Apr 09 10:46 S0000001.LOG
-rw——- 1 db2inst1 db2iadm1 4104192 Apr 09 10:46 S0000002.LOG

执行以下命令:

db2 “force applications all”
db2 terminate
db2 connect to sample
db2 “update db cfg for sample using logfilsiz 4″

这里执行完即可发现C:\DB2\NODE0000\SQL00002\SQLOGDIR目录下的日志都变成很小的了:

$ls -l
total 144
-rw——- 1 db2inst1 db2iadm1 24576 Apr 09 10:47 S0000000.LOG
-rw——- 1 db2inst1 db2iadm1 24576 Apr 09 10:47 S0000001.LOG
-rw——- 1 db2inst1 db2iadm1 24576 Apr 09 10:47 S0000002.LOG

注意改成4个4kpagesize后,实际上日志大小有6个4kpagesize。
说明日志的实际大小为:logfilsiz*4k+2k。

另:日志最小为4个4k pagesize,否则会报错:

$db2 “update db cfg for sample using logfilsiz 1″
SQL5130N The value specified for the configuration parameter “logfilsiz” is
not in the valid range of “4″ to “1048572″.

2. 修改LOGARCHMETH1参数以指定备份目录

修改LOGARCHMETH1 参数,LOGARCHMETH2这里就不改了,这样只备份至一个地方

db2 “update db cfg for sample using LOGARCHMETH1 disk:/db2home2/db2inst2/dbbackup”

注意,修改该参数之前,必须保证该备份目录存在,否则会报错:

$db2 “update db cfg for sample using LOGARCHMETH1 disk:c:/db2/backup”
SQL5099N 由数据库配置参数 “LOGARCHMETH1″ 指示的值 “c:/db2/backup”
无效,原因码为 “2″。 SQLSTATE=08004

完了之后,尝试连接,可以发现归档目录自动创建了一些文件夹:
F:\db2backup\DB2\SAMPLE\NODE0000\C0000000
这时,如果连接数据库会报错:
中文环境:

$db2 connect to sample
SQL1116N 因为 BACKUP PENDING,所以不能连接或激活数据库 “SAMPLE”。
SQLSTATE=57019

英文环境:

$db2 connect to sample
SQL1116N A connection to or activation of database “SAMPLE” cannot be made
because of BACKUP PENDING. SQLSTATE=57019

3. 发出备份命令备份数据库

如果是单节点数据库,则执行如下命令:

db2 backup db sample TO /db2home2/db2inst2/dbbackup

如果是DPF多节点数据库,必须在所有节点上执行备份,否则查询跨分区表空间仍会报错:

$db2 “select count(*) from act”
1
———–
SQL1116N A connection to or activation of database “SAMPLE” cannot be made
because of BACKUP PENDING. SQLSTATE=57019

$db2 “select * from act”
ACTNO ACTKWD ACTDESC
—— —— ——————–
10 MANAGE MANAGE/ADVISE
20 ECOST ESTIMATE COST
70 CODE CODE PROGRAMS
80 TEST TEST PROGRAMS
170 ADMDC ADM DATA COMM
SQL1116N A connection to or activation of database “SAMPLE” cannot be made
because of BACKUP PENDING. SQLSTATE=57019

因此,在DPF多节点数据库环境,则执行如下命令:

$db2 BACKUP db sample ON ALL DBPARTITIONNUMS TO /db2home2/db2inst2/dbbackup
Part Result
—- ————————————————————————
0000 DB20000I The BACKUP DATABASE command completed successfully.
0001 DB20000I The BACKUP DATABASE command completed successfully.
0002 DB20000I The BACKUP DATABASE command completed successfully.
0003 DB20000I The BACKUP DATABASE command completed successfully.
0004 DB20000I The BACKUP DATABASE command completed successfully.
0005 DB20000I The BACKUP DATABASE command completed successfully.
0006 DB20000I The BACKUP DATABASE command completed successfully.
0007 DB20000I The BACKUP DATABASE command completed successfully.
0008 DB20000I The BACKUP DATABASE command completed successfully.
Backup successful. The timestamp for this backup image is : 20100409114802

可以使用list backup命令进行查询:

$db2 list backup all for db sample

List History File for sample
Number of matching file entries = 2
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
– — —————— —- — ———— ———— ————–
B D 20100409112609001 F D S0000000.LOG S0000000.LOG
—————————————————————————-
Contains 3 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 IBMDB2SAMPLEREL
—————————————————————————-
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20100409112609
End Time: 20100409112611
Status: A
—————————————————————————-
EID: 3 Location: /db2home2/db2inst2/dbbackup
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
– — —————— —- — ———— ———— ————–
B D 20100409114802001 F D S0000030.LOG S0000030.LOG
—————————————————————————-
Contains 3 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 IBMDB2SAMPLEREL
—————————————————————————-
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20100409114802
End Time: 20100409114812
Status: A
—————————————————————————-
EID: 34 Location: /db2home2/db2inst2/dbbackup

这时,查看备份目录的文件:

$ls -l
total 958568
-rw——- 1 db2inst2 db2iadm1 102588416 Apr 09 11:26 SAMPLE.0.db2inst2.NODE0000.CATN0000.20100409112609.001
-rw——- 1 db2inst2 db2iadm1 102588416 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0000.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0001.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0002.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0003.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0004.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0005.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0006.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0007.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0008.CATN0000.20100409114802.001
drwxr-x— 3 db2inst2 db2iadm1 256 Apr 09 11:20 db2inst2

4. 做一些insert、update、delete等DML操作

5. 发出恢复命令恢复数据库

恢复数据库操作,数据库进入ROLL-FORWARD PENDING状态

$db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing without prompting”
SQL2540W Restore is successful, however a warning “2539″ was encountered
during Database Restore while processing in No Interrupt mode.

这时如果连接数据库,会报如下错误:

$db2 connect to sample
SQL1117N A connection to or activation of database “SAMPLE” cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019

如果是多分区数据库,则使用如下语句:

db2_all ‘<<+0< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+1< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+2< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+3< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+4< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+5< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+6< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+7< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+8< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘

查询状态,注意默认时间是UTC时间:

$db2 “rollforward db sample query status”

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 DB pending S0000030.LOG – 2010-04-09-03.48.06.000000 UTC
1 not pending – 1970-01-01-00.00.00.000000 UTC
2 not pending – 1970-01-01-00.00.00.000000 UTC
3 not pending – 1970-01-01-00.00.00.000000 UTC
4 not pending – 1970-01-01-00.00.00.000000 UTC
5 not pending – 1970-01-01-00.00.00.000000 UTC
6 not pending – 1970-01-01-00.00.00.000000 UTC
7 not pending – 1970-01-01-00.00.00.000000 UTC
8 not pending – 1970-01-01-00.00.00.000000 UTC

$db2 “rollforward db sample query status”

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 DB pending S0000030.LOG – 2010-04-09-03.48.06.000000 UTC
1 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC
2 DB pending S0000000.LOG – 2010-04-09-03.48.10.000000 UTC
3 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC
4 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC
5 DB pending S0000000.LOG – 2010-04-09-03.48.10.000000 UTC
6 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC
7 DB pending S0000000.LOG – 2010-04-09-03.48.10.000000 UTC
8 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC

$db2 “rollforward db sample query status using local time”

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 DB pending S0000030.LOG – 2010-04-09-11.48.06.000000 Local
1 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local
2 DB pending S0000000.LOG – 2010-04-09-11.48.10.000000 Local
3 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local
4 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local
5 DB pending S0000000.LOG – 2010-04-09-11.48.10.000000 Local
6 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local
7 DB pending S0000000.LOG – 2010-04-09-11.48.10.000000 Local

8 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local

6. 执行前滚操作

执行以下命令:

db2 rollforward db sample to end of logs
db2 rollforward db sample complete

以下是操作日志:

$db2 rollforward db sample to end of logs

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 DB working S0000383.LOG S0000030.LOG-S0000382.LOG 2010-04-09-14.15.03.000000 Local
1 DB working S0000004.LOG S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
2 DB working S0000004.LOG S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
3 DB working S0000004.LOG S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
4 DB working S0000001.LOG S0000000.LOG-S0000001.LOG 2010-04-09-13.51.53.000000 Local
5 DB working S0000005.LOG S0000000.LOG-S0000005.LOG 2010-04-09-13.51.53.000000 Local
6 DB working S0000002.LOG S0000000.LOG-S0000002.LOG 2010-04-09-13.51.53.000000 Local
7 DB working S0000002.LOG S0000000.LOG-S0000002.LOG 2010-04-09-13.51.53.000000 Local
8 DB working S0000004.LOG S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local

DB20000I The ROLLFORWARD command completed successfully.

这个时候还没完,还必须发出一个complete命令,否则连接数据库还是报错。

$db2 rollforward db sample complete

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 not pending S0000030.LOG-S0000383.LOG 2010-04-09-14.15.03.000000 Local
1 not pending S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
2 not pending S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
3 not pending S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
4 not pending S0000000.LOG-S0000001.LOG 2010-04-09-13.51.53.000000 Local
5 not pending S0000000.LOG-S0000005.LOG 2010-04-09-13.51.53.000000 Local
6 not pending S0000000.LOG-S0000002.LOG 2010-04-09-13.51.53.000000 Local
7 not pending S0000000.LOG-S0000002.LOG 2010-04-09-13.51.53.000000 Local
8 not pending S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local

DB20000I The ROLLFORWARD command completed successfully.

7. 完成。数据库恢复正常。


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