db2记录

cat /etc/passwd | grep db2
groupadd db2fgrp3
groupadd db2grp3
useradd -d /home/db2inst3 -g db2grp3 -m -s /bin/bash  db2inst3
useradd -d /home/db2fenc3 -g db2fgrp3 -m -s /bin/bash  db2fenc3
cd /opt/ibm/db2/v10.1/instance/
./db2icrt -p 60000 -a server -u db2fenc3 db2inst3


db2 uncatalog db  ksdbs
db2 uncatalog node db2node 
db2 terminate
db2 catalog tcpip node test remote 10.10.12.187 server 60000
db2 catalog db ksdbs at node test
db2 terminate


db2set -all  --list all  have reseted  
db2 connect to ksdbs user user_name using password
db2 update dbm cfg svcename db2inst3
db2 create db ksdbs using codeset gbk territory cn
db2 connect to ksdbs


su - db2inst3
db2set db2comm=tcpip
db2set db2autostart=on
db2set db2codepage=1386
db2set db2country=86
vim /etc/services
db2inst3   60000/tcp   #db2 port
db2 update dbm cfg using svcename 60000
db2stop
db2strart
db2 create db ksdbs using codeset gbk territory cn
db2 connect to sample
db2 grant connect on database to user test 
db2 connect reset
db2  -tvf crt_db.sql


drop tablespace ac_cur_index_tbs;
create tablespace ac_cur_index_tbs managed by database
using( file '/home/db2inst3/index' 262144) 
extentsize 16
prefetchsize 128;


create bufferpool aclongdata size 5000 pagesize 16384;


create tablespace ac_long_data_tbs
pagesize 16384
managed by database
using( file '/home/db2inst3/acldata' 16384)
extentsize 32 
prefetchsize 256
bufferpool aclongdata;


create system temporary tablespace tempspace2 
pagesize 16384 managed by system
using ('/home/db2inst3/tempcont','/home/db2inst3/tempspace')
extentsize 32
prefetchsize 256
bufferpool aclongdata;


db2 list tablespaces;
db2 list tablespaces show detail;
db2 list tablespace containers for 2;


alter tablespace ac_long_data_tbs add(DEVICE '/dev/rath9' 10000, DEVICE '/dev/rath10' 10000);
alter tablespace ac_long_data_tbs extend(all,10000);
alter tablespace ac_long_data_tbs reduce(file '/home/db2inst3/acldata' 16384);
alter tablespace ac_long_data_tbs resize(DEVICE '/dev/rath9' 8000, DEVICE '/dev/rath10' 8000);
alter tablespace ac_long_data_tbs drop(file '/home/db2inst3/acldata',DEVICE '/dev/rath9');


rename tablespace ts1 to ts2;


drop sequence ac.seq_serial_no;
create sequence ac.seq_serial_no as integer increment by 1 maxvalue 1000000000 minvalue 1 cycle cache 2000;


create table xinzhuang_pic(picno int, pic clob(lg)) in data_sapce index in index_space long in lob_space;
drop tablespace data_space, index_space, lob_space;




create table ac.cust_agent_rel
(
    custid                    bigint         not null with default 0,
    fundaccountid             bigint         not null with default 0,
    begindatetime             timestamp      not null with default current timestamp,
    constraint p_key_1 primary key(custid, fundaccountid, agentid)
)
in ac_cur_data_tbs
index in ac_cur_index_tbs;


comment on table ac.cust_agent_rel                                    is 'cust agent rel';
comment on column ac.cust_agent_rel.custid                            is 'custid';
comment on column ac.cust_agent_rel.fundaccountid                     is 'fundaccountid';
comment on column ac.cust_agent_rel.begindatetime                     is 'begindatetime';


drop index ac.i_cfa_ss;
create unique index ac.i_cfa_ss on ac.cust_fund_account(
    showfundaccountid,
    systemtype
);


drop view soc.update_log;
create view soc.update_log as
select serial_no,table_name,key_column,change_type,change_time,note from ac.soc_update_log;
comment on table soc.update_log is 'view with memory update data';




alter table ac.his_cust_trader_accoun add primary key (pbuno);
alter table tablename drop primary key;
alter table ac.cust_info_change alter column operatesource set data type varchar(254);
alter table ac.cust_info_change alter column operatesource set default 'valuse';
alter table ac.cust_trader_account add column openstatus char(1) not null with default '';
comment on column ac.cust_trader_account.openstatus is 'this is a test';
db2 describe indexes for table ac.his_cust_trader_account show detail;
db2ilist -- this command is list all database instance;


db2 list applications
db2 force application all
db2stop force 
db2start
db2 backup db ksdbs
db2 backup db ksdbs online to “/backup/offline” with 4 buffers buffer 1024 parallelism 4 compress without prompting
db2 backup db ksdbs with 4 buffers buffer 1024 parallelism 4 compress without prompting 


db2 drop database ksdbs
db2 restore db ksdbs taken at 20130909163636
db2 restore db ksdbs from /db2home/db2inst1 taken at 20130909163636 
db2 rollforward db ksdbs to end of logs and stop;