DB2入门操作之一

发表于 2012 年 3 月 28 日 由 惜分飞

1.DB2启动关闭

--关闭db2
[db2inst1@xifenfei ~]$ db2stop
03/28/2012 09:23:39     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

--开启db2
[db2inst1@xifenfei ~]$ db2start
03/28/2012 09:23:55     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

2.查看DB2数据库

[db2inst1@xifenfei ~]$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = TOOLSDB
 Database name                        = TOOLSDB
 Local database directory             = /home/db2inst1
 Database release level               = d.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

3.连接DB2数据库

[db2inst1@xifenfei ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.4

You can issue database manager commands and SQL statements from the command 
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to TOOLSDB

   Database Connection Information

 Database server        = DB2/LINUX 9.7.4
 SQL authorization ID   = DB2INST1
 Local database alias   = TOOLSDB

4.查看数据库中包含包

db2 => list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------

  0 record(s) selected.

db2 => create table t_xff (id int,name varchar(100))
DB20000I  The SQL command completed successfully.
db2 => list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
T_XFF                           DB2INST1        T     2012-03-28-09.29.54.572395

  1 record(s) selected.

5.常见DML操作

db2 =>  insert into t_xff values(1,'xifenfei')
DB20000I  The SQL command completed successfully.
db2 => insert into t_xff values(2,'www.xifenfei')
DB20000I  The SQL command completed successfully.
db2 => select * from t_xff 

ID          NAME                
----------- ---------------------------------------
          1 xifenfei           
          2 www.xifenfei       

  2 record(s) selected.

db2 => delete from t_xff where id=1
DB20000I  The SQL command completed successfully.
db2 => select * from t_xff

ID          NAME                                        
----------- -----------------------------------------
          2 www.xifenfei                             

  1 record(s) selected.

db2 => quit
DB20000I  The QUIT command completed successfully.
发表在 DB2 | 评论关闭

Linux中安装DB2截图欣赏

发表于 2012 年 3 月 28 日 由 惜分飞
db1

此多媒体陈列厅包含 18 张图像。

DB在linux下面使用图形化界面安装非常方便，绝对不会像oracle那样的恶心 … 继续阅读 →

更多多媒体陈列厅 | 评论关闭

关于SMON_SCN_TIME若干问题说明

发表于 2012 年 3 月 28 日 由 惜分飞

1.SMON_SCN_TIME表基础知识
0)作用
由smon收集scn和time映射关系，用于flashback/查询scn和time对应关系等操作

1)保留条数
官方文档给出说明instance number N * 12 times per hour * 24 hours * 5 days = 1440N rows，因为每次的时间间隔不是非常准确的5分钟，所以在具体的条数在实际生产环境中有一定的出入

2)采集和删除
smon进程没5分钟采集一次插入到SMON_SCN_TIME表中，同时将删除历史数据(超过5天前数据)，采用下面语句
delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0)，如果有时候index出了问题，导致该语句执行很慢

3)当查询scn对应time，如果scn超过SMON_SCN_TIME表范围，将提示错误；或者查询time对应的scn，如果超过范围也同样报错。

2.当SMON_SCN_TIME表出现问题时，truncate操作语句

--找出CLUSTER
Select dbms_metadata.get_ddl('TABLE','SMON_SCN_TIME','SYS') FROM DUAL ;

9i
truncate CLUSTER "SYS"."SMON_SCN_TO_TIME";

10g
truncate  CLUSTER "SYS"."SMON_SCN_TO_TIME";

11g
truncate CLUSTER "SYS"."SMON_SCN_TO_TIME_AUX";

3.停止/开启smon进程收集scn信息

stop
alter system set events '12500 trace name context forever, level 10';

start
alter system set events '12500 trace name context off';

4.index异常处理

--找出index
Select index_name,index_type,owner from dba_indexes where table_name='SMON_SCN_TIME';
--对应index创建语句
Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') FROM DUAL;

--重建index
drop index smon_scn_time_scn_idx;
drop index smon_scn_time_tim_idx;
create unique index smon_scn_time_scn_idx on smon_scn_time(scn);
create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp);
analyze table smon_scn_time validate structure cascade online;

--rebuild index
alter index sys.smon_scn_time_scn_idx rebuild online;
alter index sys.smon_scn_time_tim_idx rebuild online;
analyze table smon_scn_time validate structure cascade online;

补充知识点scn计算方法SCN=(SCN_WRP * 4294967296) + SCN_BAS

发表在 Oracle | 标签为 | 一条评论