DB2相关笔记 Ι (常用知识点)

DB2相关笔记 Ι （常用知识点）

1、--db2里面建索引：

CREATE INDEX ALMUSR.IDX_BILL_NO ON ALMUSR.ACC_LOAN

( BILL_NO ASC )

ALLOW REVERSE SCANS

COMPRESS NO;

COMMIT;

--建立唯一性索引

CREATE UNIQUE INDEX I_ztables_tabname ON zjt_tables(tabname);

2、--指定表空间建表

create table INCT_00 (

ACCT_NO CHARACTER(16) not null

,REC_NO CHARACTER(9) not null

,TRAN_TYPE CHARACTER(2)

)

IN USERSPACE8K; --表空间USERSPACE8K

3、表结构导出

db2look

模板：db2look -d database_name [,],注：选项的顺序可以任意

-e:提取数据库对象的DDL语句。

-a：提取用户创建的所有对象的DDL语句。如果这选项与 -e 选项一起指定，则是对数据库中所有的对象都进行处理。

-z:指定schema 模式名的对象。

-t:指定table 一个或多个表对象，表名用空格字符分开。

-l：为用户定义的表空间、缓冲池生成DDL语句

-o：将输出结果写入到一个文件中。

eg：

db2look -d $DB2NAME -t $SCHNAME.$TABNAME -e -o $TABNAME.sql

db2look -d FYMADB -e -t PA_IMPORT_CONF -o FYMA_PA_IMPORT_CONF_DDL.sql

db2look -d FYMADB -e -t FDM_PA_GL -o DB2_20160302.sql

db2look -d FYMADB -t FYMA.FDM_PA_GL -e -o DB2_20160302.sql

db2look -d ALMDB -e -a -l -o db2look_20120924.sql

db2look -d RDMDB -e -a -o db2look_20120101.sql

3、批量数据导入

注意：导入全量表时使用replace,导入增量表时使用insert

db2 connect to FYMADB user fyma using fyma; --连接数据库

(db2 connect reset --重新连接

db2 disconnect db2_gcb --中断数据库连接 )

db2 load from ADM_FTP_TPCOA_RATE.del of del replace into FYMA.ADM_FTP_TPCOA_RATE;

db2 load from ADM_FUND_PACK_DATA.del of del replace into FYMA.ADM_FUND_PACK_DATA;……

4、导出模板

db2 connect to odsdb user prbk using prbk

--导出

db2 export to F_CD_DBC_ODMSA_20121106.dat of del modified by DATESISO coldel0x03 NOCHARDEL "select * from ALMDB.F_CD_DBC_ODMSA" > F_CD_DBC_ODMSA_20121106.log;

--或者

db2 export to F_CD_DBC_ODMSA_20121106.dat of del MODIFIED BY nochardel coldel0x03 datesiso "select * from ALMDB.F_CD_DBC_ODMSA" > F_CD_DBC_ODMSA_20121106.log;

5、数据库配置方面的命令

(1). 检查 DB2 数据库管理程序配置 db2 get dbm cfg

(2). 察看数据库配置参数信息 db2 get db cfg for CRMSDB|more

(3). 更新数据库参数配置信息 db2 update db cfg for CRMSDB using para_name para_value

(4). 查看实例 db2ilist

(5). 数据库节点目录 db2 list node directory

(6). 系统数据库目录 db2 list db directory

(7). 查看命令选项 db2 list command options

(8). 启动实例 停止实例 DB2START db2stop force

6、查看表空间的命令

查看表空间 db2 list tablesapces show detail

查看表空间容器 LIST TABLESPACE CONTAINERS FOR 2

--查看表sql语句

方法一：查看表空间是否已满

select substr(TABLESPACE_NAME,1,20) as TBSPC_NAME

,bigint(TOTAL_PAGES * PAGE_SIZE)/1024/1024/1024 as "TOTAL(GB)"

,used_pages*PAGE_SIZE/1024/1024/1024 as "USED(GB)"

, free_pages*PAGE_SIZE/1024/1024/1024 as "FREE(GB)"

from table(snapshot_tbs_cfg('CRMSDB', -2)) as snapshot_tbs_cfg

方法二 ：通过load的第三方工具查看

/home/db2icrms/sqllib/db2dump/db2diag.log

--显示当前用户所有表 db2 list tables

--列出所有的系统表 db2 list tables for system

--------------------------------------------------------

---进程相关命令---

--------------------------------------------------------

7、删除约束（删除表ALMUSR.PUB_CASH_LOAN的约束）

ALTER TABLE ALMUSR.PUB_CASH_LOAN DROP PRIMARY KEY;

删除表ALMUSR.PUB_CASH_LOAN的主键约束PK_M_LOAN

ALTER TABLE ALMUSR.PUB_CASH_LOAN DROP CONSTRAINT PK_M_LOAN;

COMMIT;

8、快速清空表M_LOAN

alter table M_LOAN activate not logged initially with empty table;

9、列出相关进程号、杀进程

db2 list application

db2 force applications (进程号)--kill单个进程 如：db2 force applications (23459)

db2 force application all 所有进程

10、查询表的状态：

db2 load query table tablename;

11、给表或视图赋特权

grant select,delete,insert,update on tables to user

grant all on tables to user WITH GRANT OPTION

12、服务器后台调用存储过程

db2 connect to DMCRMS user crmsapp using crmsapp

db2 "call CRMSAPP.UP_AMT_ADD_NUM_TEST(?)"

如：db2 "call PROC_FDM_CAPITAL_AC_GATHER_D.db2(20160131,?,?,?,?)" --20160131传入参数，？表示输出参数

db2 connect reset

13、在后台运行SQL脚本命令

db2 connect to FYMADB user fyma using fyma --连接数据库

db2 -tvf LRUSR_B_AD_CURVE_ADD_INFO_DATA.sql | tee -a data.log --建表并输出log

db2 -td@ -f PROC_PARTITION_MGR.sql | tee -a proc.log ----在后台编译存储过程或函数并输出log

14、查询schema下表空间下的表

SELECT TABSCHEMA,TABNAME,TBSPACE,INDEX_TBSPACE

FROM SYSCAT.TABLES

WHERE

(TBSPACE in ('USERSPACE1','ODS_TBS_DATA','ALM_TBS_IDX')

OR INDEX_TBSPACE IN ('ALM_TBS_IDX'))

AND TABSCHEMA = 'FYMA'

ORDER BY TBSPACE,INDEX_TBSPACE;

15、查询表涉及的存储过程

select PROCNAME from SYSCAT.PROCEDURES

where SPECIFICNAME in(select dname from sysibm.sysdependencies

where bname in ( select PKGNAME from syscat.packagedep a where a.BSCHEMA='FYMA' and a.BNAME='PDM_PA_SUBJ')) ;

16、修改字段类型

alter table FYMA.FDM_PA_CHECK_RESULT alter column CHECK_RESULT set data type VARCHAR(100);

删除表中字段

alter table [表名] drop column [字段名];

添加表中字段(可无限添加)

alter table [表名] add column [字段名];

为表添加主键约束(该列必须为not null)

alter table [表名] add primary key [字段名1，字段名2等等];

查看表结构命令(如同oracle中的desc tablename)

describe table [tablename];

建表中字段添加注释

COMMENT ON column almusr.MA_JOB_EVENT_CFG.JOB_ID（schema.table.column） is '作业ID';

commit;

更新字段

UPDATE ALMUSR.ADD_MANAGE_PROPERTY SET GL_ACCOUNT_ID='13210101'

WHERE SEQ_NO>15;COMMIT;

17、后台编译和运行存储过程

编译：CM PROC_FDM_PA_CHECK_RESULT_M.db2

运行：db2 "call PROC_FDM_PA_CHECK_RESULT_M('20151031',?,?,?,?)"

注意：PROC_FDM_PA_CHECK_RESULT_M不带db2

18、exist和in的区别

A表大表，B表是小表

子查询是小表时用in效率高

子查询是大表时用exist效率高

例如：表A（小表），表B（大表）1：select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引； select * from A where exists(select cc from B where cc=https://www.360docs.net/doc/f93505658.html,) 效率高，用到了B表上cc列的索引。

19、建SESSION临时表命令

declare global temporary table FDM_PA_AC_TMP_33 like FDM_PA_AC_TMP

not logged with replace on commit preserve rows;

20、快速建表、试图

方法一：create table tmp_2016 like fyma.fdm_pa_check_result;

方法二：create table tmp_20160302 as (select * from fyma.fdm_pa_check_result) definition only;

create table zjt_views as (select * from views) definition only; --视图

21、查询某个表中的前100记录

SELECT * FROM 【tablename】 FETCH FIRST 100 ROWS ONLY ;

22、测试表sysibm.dual

Select * from sysibm.dual;

23、时间处理

SELECT

timestampdiff (256, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔年",

timestampdiff (128, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔季度",

timestampdiff (64, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔月",

timestampdiff (32, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔周",

timestampdiff (16, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔日",

timestampdiff (8, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔时",

timestampdiff (4, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔分",

timestampdiff (2, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔秒"

FROM SYSIBM.SYSDUMMY1;

24、db2中表挂起后解挂

DB2 REORG TABLE [表名];

25、时间算法

values(LAST_DAY(sysdate - 1 months)); -- 上月最后一天

values(LAST_DAY(sysdate - 2 months) + 1); -- 上月第一天

values(LAST_DAY(sysdate)); -- 当月最后一天

values(LAST_DAY(sysdate - 1 months) + 1); -- 当月第一天

26、查看表中数据条数：

select 'select ''' || t.TABLE_NAME ||''',count(*) from ALMUSR.' || t.TABLE_NAME ||' UNION ALL'

from sysibm.tables t

where t.TABLE_SCHEMA='ALMUSR'

and t.TABLE_NAME NOT LIKE 'M_%'

AND t.TABLE_NAME NOT LIKE 'LIQ_RPT%'

AND t.TABLE_NAME NOT LIKE 'PUB_CASH%'

AND t.TABLE_NAME NOT LIKE 'PDM_%'

AND t.TABLE_NAME NOT LIKE '%TEST%'

AND t.TABLE_NAME NOT LIKE '%_TMP';

27、建自动扩展表空间

db2 CREATE LARGE TABLESPACE TBS_ODS_CUR_32K PAGESIZE 32768 managed by automatic storage

AUTORESIZE YES

INITIALSIZE 5G

EXTENTSIZE 64

PREFETCHSIZE 16

BUFFERPOOL BP_RDM_32K

NO FILE SYSTEM CACHING

28、连接数据库

connect to 【dbname】 user 【username】using 【password】;

29、建立数据库DB2_GCB

CREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCB

USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM DFT_EXTENT_SZ 32

30、为表和视图建别名

CREATE ALIAS DB2ADMIN.TABLES FOR SYSSTAT.TABLES;

CREATE ALIAS DB2ADMIN.VIEWS FOR SYSCAT.VIEWS

CREATE ALIAS DB2ADMIN.COLUMNS FOR SYSCAT.COLUMNS;

CREATE ALIAS GUEST.COLUMNS FOR SYSCAT.COLUMNS;

31、向表和视图中快速 插入记录

insert into zjt_tables select * from tables;

insert into zjt_views select * from views;

32、建立触发器

CREATE TRIGGER zjt_tables_del

AFTER DELETE ON zjt_tables

REFERENCING OLD AS O

FOR EACH ROW MODE DB2SQL

Insert into zjt_tables1 s(substr(o.tabschema,1,8),substr(o.tabname,1,10))

33、后台查看列

db2 "select SUBSTR(COLNAME,1,20) as orgname,TYPENAME as char,LENGTH as 20 from columns where tabname='tbl_orgdef'";

--查看表结构

db2 describe table uesr.tables;

--查看表的索引

db2 describe indexes for table user.tables;

34、查看存储过程

SELECT SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15)

FROM SYSCAT.PROCEDURES;

35、字段类型转换cast

ip datatype:varchar

select cast(ip as integer)+50 from tablename；

36、锁等待问题解决方案

select * from sysibmadm.sanplock;

方法1、select agent_id,tabname,lock_mode from table(snap_get_lock('dbname')) as aa; dbname--数据库名

方法2、37、编目节点