一些DB2 学习中的笔记，笔记凌乱，还会不断增加，有时间的时候整理一下

sqlplus / as sysdba 服务器上用的
sqlplus oracle/oracle@orcl
startup;
shutdown;
lsnrctl start； 打开监听

set -e vi

ps -ef | grep db2;
kill -9
SELECT COUNT(*) FROM COORDINATE

db2admin setid db2admin passw0rd;

db2 "select count(*) from table"

create database databasename using codepage GBK territory CN;

db2look -d poc -a -e -z zj0410 -o poc.sql;
db2 drop table tablename;

DB2主日志过大导致连接数据库失败

db2 update db cfg for using logfilsiz 12345 logprimary 5

修改日志文件大小:update db cfg using logfilsiz 65536; 实际大小为65536（页数）×4k（块大小）；
增加主日志文件数：update db cfg using logprimary 10;最多不超过256；
也可以增加辅助日志文件数：update db cfg using logsecond 100；最大不超过254；辅助日志文件在需要时分配，不再使用时系统收回。
也可以不写日志：
ALTER TABLE A ACTIVATE NOT LOGGED INITIALLY;注意当事务提交后，表A就会恢复到logging状态；即设置一次只能做一个事务。再要no logging的话，还需要设置。

db2 catalog tcpip node mynode remote db2server.mycompany.com server db2icdb
db2 catalog netbios node jeremy remote N01FCBE3 adapter 0
2. 编目数据库。
编目一个或多个属于已编目实例的数据库。示例如下：
db2 catalog database sample as mysamp at node mynode
db2 catalog database baydb as newbaydb at node mynode

db2 rollforward db db to end of logs and complete

DB2 登录数据库时的代码页转换错误 SQL0332N

db2set db2codepage=1386
db2 terminate
db2 connect to sample

db2fmcu -d

db2 CREATE db htdctr USING CODESET GBK TERRITORY cn

create bufferpool BP2 size 25000 pagesize 8 K

#####################Instance######################
db2icrt db1
db2icrt -u db2admin db2inst1
db2idrop -f db1
db2ilist
db2imigr db2
db2iupdt db2

db2set -lr
db2set DB2COMM=TCPIP,APPC
db2set DB2COMM=
db2set -all

set DB2INSTANCE PROD windows
setenv DB2INSTANCE PROD Cshell
export DB2INSTANCE PROD Kshell

db2 get instance

db2 get database manager configuration
db2 get database configuration for database_name

db2 update database manager configuration using parameter new_value
db2 update database configuration for database_name using parameter new_value

db2 update database manager configuration using parameter new_value immediate
db2 update database configuration for database_name using parameter new_value immediate

db2 update database manager configuration using parameter new_value deferred
db2 update database configuration for database_name using parameter new_value deferred

db2 attach to instance_name
db2 get database manager configuration show detail

db2 connect to database_name
db2 get database configuration for database_name show detail

list command options;
update command options using c on;
update command options using c off;

list database directory;

db2 connect to bank;
db2 connect reset;
db2 disconnect bank;
db2 list tables;
db2 list tables for all
db2 list tables for schema bank;

############select###################
select * from staff;
select * from staff fetch first 10 rows only;
select name,salary from staff;
select distinct dept,job from staff;
select name, salary + comm AS pay from staff;
select name, salary from where salary > 20000;
select name, salary from staff where job<> 'Mgr' AND salary > 20000;
select name from staff where name like 'S%'; //find all names that start with the letter S;
select lastname from employee where lastname IN ( select sales_person from sales where sales_date < '01/01/1996';
select e.salary from employee e where e.salary < (select AVG(s.salary) from staff s)";
select name, salary from staff where salary > 20000 order by salary;
select name, salary from staff where salary > 20000 order by salary DESC;
select deptnumb, deptname,id AS manager_id, name AS manager from org,staff where manager=id order by deptnumb

select empno,deptname,projname
from (employee
left outer join porject
on respemp=empno)
left outer join department
on mgrno=impno;

###############delete##############3

delete from
如果纪录很多，可以
import from <空文件> of del replace into

db2 DROP VIEW viewname;
db2 drop table tablename;

################ easy mining proceduar ##############33333

db2 "call IDMMX.FindDeviations('BANK.DEV_CUSTOMERS01','BANK.CUSTOMERS_MASTERDATA')";
select * from bank.dev_customers01 fetch first 10 rows only;

create table bank.customers_masterdata (
CLIENT_ID CHAR(9) not null,
AGE REAL,
GENDER CHAR(2),
MARITAL_STATUS CHAR(12),
PROFESSION CHAR(24),
NBR_YEARS_CLI REAL,
Primary key (client_id)
) not logged initially;

Import from bankdevsample.csv of del modified by CHARDEL "" COLDEL, DECPT.
insert into bank.customers_masterdata;

commit;

db2 list applications show detail
db2 force application all
db2 force application (1)

快照监控

GET MONITOR SWITCHES
UPDATE MONITOR SWITCHES USING LOCKS ON
UPDATE MONITOR SWITCHES USING BUFFERPOOL OFF

RESET MONITOR ALL
RESET MONITOR FOR DATABASE SAMPLE

create table inventory (partno INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 100 INCREMENT BY 1), description CHAR(20) )

insert into inventory VALUES (DEFAULT,'door')
insert into inventory (description) VALUES ('hinge')
insert into inventory VALUES (200,'windor')
insert into inventory (description) VALUES ('frame')

CREATE TABLE TEST (
column 1 definition, column 2 definition, ...
) IN INDEX IN

CREATE UNIQUE INDEX IX ON EMPLOYEE (EMPNO) INCLUDE (LASTNAME,FIRSTNME)

db2mtrk -i -d -p

EXPORT TO myfile.del OF DEL
MODIFIED BY chardel! coldel@ codepage=1208 timestampformat="yyyy.mm.dd hh:mm tt"
MESSAGES msg.out
SELECT * FROM schedule

IMPORT FROM emp.ixf OF IXF
MESSAGES msg.out
CREATE INTO employee IN datatbsp INDEX IN indtbsp

export to emp.del of del
modified by chardel! coldel@ codepage=1208
messages msg.out
select * from employee

IMPORT FROM myfile.ixf OF IXF
COMMITCOUNT 500
MESSAGES msg.out
INSERT INTO newtable

IMPORT FROM myfile.ixf OF IXF
COMMITCOUNT 500 RESTARTCOUNT 30000 ROWCOUNT 100000
MESSAGES msg.out
INSERT INTO newtable

IMPORT FROM myfile.ixf OF IXF
ALLOW WRITE ACCESS
MESSAGES msg.out
INSERT INTO newtable

LOAD FROM emp.ixf OF IXF
MESSAGES msg.out
MODIFIED BY DUMPFILE=c:emp.dmp
TEMPFILES PATH d:tmp
INSERT INTO employee
FOR EXCEPTION empexp

db2batch -d sample -f db2batch.sql

BACKUP DATABASE sample
TO /db2backup/dir1, /db2backup/dir2
WITH 4 BUFFERS
BUFFER 4096
PARALLELISM 2

BACKUP DATABASE sample
ONLINE
TO /dev/rdir1 INCLUDE LOGS

RESTORE DATABASE sample
FROM C:DBBACKUP
LOGTARGET C:DB2NODE0000SQL00001SQLOGDIR

(1) BACKUP DATABASE sample
(2) TABLESPACE ( syscatspace, userspace1, userspace2 )
(3) ONLINE
(4) TO /db2tbsp/backup1, /db2tbsp/backup2

(1) RESTORE DATABASE sample
(2) TABLESPACE ( mytblspace1 )
(3) ONLINE
(4) FROM /db2tbsp/backup1, /db2tbsp/backup2

(1) RESTORE DATABASE sample
(2) FROM C:DBBACKUP
(3) TAKEN AT 20060314131259
(4) WITHOUT ROLLING FORWARD
(5) WITHOUT PROMPTING