db2常用命令

[b]连接数据库： [/b]

connect to [数据库名] user [操作用户名] using [密码]

[b]创建缓冲池(8K)：[/b]

create bufferpool ibmdefault8k IMMEDIATE SIZE 5000 PAGESIZE 8 K ;

创建缓冲池(16K)(OA_DIVERTASKRECORD)：
create bufferpool ibmdefault16k IMMEDIATE SIZE 5000 PAGESIZE 16 K ;

创建缓冲池(32K)(OA_TASK)：
create bufferpool ibmdefault32k IMMEDIATE SIZE 5000 PAGESIZE 32 K ;

[b]创建表空间：[/b]

CREATE TABLESPACE exoatbs IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8K MANAGED BY SYSTEM USING ('/home/exoa2/exoacontainer') EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT8K OVERHEAD 24.10 TRANSFERRATE 0.90 DROPPED TABLE RECOVERY OFF;

CREATE TABLESPACE exoatbs16k IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 16K MANAGED BY SYSTEM USING ('/home/exoa2/exoacontainer16k' ) EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT16K OVERHEAD 24.1 TRANSFERRATE 0.90 DROPPED TABLE RECOVERY OFF;

CREATE TABLESPACE exoatbs32k IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32K MANAGED BY SYSTEM USING ('/home/exoa2/exoacontainer32k' ) EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT32K OVERHEAD 24.1 TRANSFERRATE 0.90 DROPPED TABLE RECOVERY OFF;

GRANT USE OF TABLESPACE exoatbs TO PUBLIC;
GRANT USE OF TABLESPACE exoatbs16k TO PUBLIC;
GRANT USE OF TABLESPACE exoatbs32k TO PUBLIC;

[b]创建系统表空间：[/b]

CREATE TEMPORARY TABLESPACE exoasystmp IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 8K MANAGED BY SYSTEM USING ('/home/exoa2/exoasystmp' ) EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT8K OVERHEAD 24.10 TRANSFERRATE 0.90 DROPPED TABLE RECOVERY OFF;

CREATE TEMPORARY TABLESPACE exoasystmp16k IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 16K MANAGED BY SYSTEM USING ('/home/exoa2/exoasystmp16k' ) EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT16K OVERHEAD 24.10 TRANSFERRATE 0.90 DROPPED TABLE RECOVERY OFF;

CREATE TEMPORARY TABLESPACE exoasystmp32k IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 32K MANAGED BY SYSTEM USING ('/home/exoa2/exoasystmp32k') EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT32K OVERHEAD 24.10 TRANSFERRATE 0.90 DROPPED TABLE RECOVERY OFF;

[b]1. 启动实例(db2inst1):[/b]
db2start

[b]2. 停止实例(db2inst1):[/b]

db2stop

[b]3. 列出所有实例(db2inst1)[/b]

db2ilist

[b]5.列出当前实例:[/b]

db2 get instance

[b]4. 察看示例配置文件:[/b]

db2 get dbm cfg|more

[b]5. 更新数据库管理器参数信息:[/b]

db2 update dbm cfg using para_name para_value

[b]6. 创建数据库:[/b]

db2 create db test

[b]7. 察看数据库配置参数信息[/b]

db2 get db cfg for test|more

[b]8. 更新数据库参数配置信息[/b]

db2 update db cfg for test using para_name para_value

[b]10.删除数据库:[/b]

db2 drop db test

[b]11.连接数据库[/b]

db2 connect to test

[b]12.列出所有表空间的详细信息。[/b]

db2 list tablespaces show detail

13.查询数据:

db2 select * from tb1

14.删除数据:

db2 delete from tb1 where id=1

15.创建索引:

db2 create index idx1 on tb1(id);

16.创建视图:

db2 create view view1 as select id from tb1

17.查询视图:

db2 select * from view1

[b]18.节点编目[/b]

db2 catalog tcp node node_name remote server_ip server server_port

[b]19.察看端口号[/b]

db2 get dbm cfg|grep SVCENAME

[b]20.测试节点的附接[/b]

db2 attach to node_name

[b]21.察看本地节点[/b]

db2 list node direcotry

[b]22.节点反编目[/b]

db2 uncatalog node node_name

[b]23.数据库编目[/b]

db2 catalog db db_name as db_alias at node node_name

[b]24.察看数据库的编目[/b]

db2 list db directory

[b]25.连接数据库[/b]

db2 connect to db_alias user user_name using user_password

[b]26.数据库反编目[/b]

db2 uncatalog db db_alias

[b]27.导出数据[/b]

db2 export to myfile of ixf messages msg select * from tb1

[b]28.导入数据[/b]

db2 import from myfile of ixf messages msg replace into tb1

[b]29.导出数据库的所有表数据[/b]

db2move test export

[b]30.生成数据库的定义[/b]

db2look -d db_alias -a -e -m -l -x -f -o db2look.sql


[b]32.生成定义[/b]

db2 -tvf db2look.sql

[b]33.导入数据库所有的数据[/b]

db2move db_alias import

[b]34.重组检查[/b]

db2 reorgchk

[b]35.重组表tb1[/b]

db2 reorg table tb1

[b]36.更新统计信息[/b]

db2 runstats on table tb1

[b]37.备份数据库test[/b]

db2 backup db test

3[b]8.恢复数据库test[/b]

db2 restore db test

[b]39.列出容器的信息[/b]

db2 list tablespace containers for tbs_id show detail

[b]40.创建表:[/b]

db2 ceate table tb1(id integer not null,name char(10))

[b]41.列出所有表[/b]

db2 list tables

[b]42.插入数据[/b]:

db2 insert into tb1 values(1,’sam’);

db2 insert into tb2 values(2,’smitty’);


[b]43 建立别名[/b]

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;

[b]44. 建立表[/b]

create table zjt_tables as

(select * from tables) definition only;

create table zjt_views as

(select * from views) definition only;

[b]45. 插入记录[/b]

insert into zjt_tables select * from tables;

insert into zjt_views select * from views;

[b]46. 建立视图[/b]

create view V_zjt_tables as select tabschema,tabname from zjt_tables;

[b]47. 建立触发器[/b]

CREATE TRIGGER zjt_tables_del

AFTER DELETE ON zjt_tables

REFERENCING OLD AS O

FOR EACH ROW MODE DB2SQL

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

[b]48. 建立唯一性索引[/b]

CREATE UNIQUE INDEX I_ztables_tabname

[size=3]ON zjt_tables(tabname);

[b]49. 查看表[/b]

select tabname from tables

where tabname='ZJT_TABLES';

[b]50. 查看列[/b]

select SUBSTR(COLNAME,1,20) as 列名,TYPENAME as 类型,LENGTH as 长度

from columns

where tabname='ZJT_TABLES';

[b]51. 查看表结构[/b]

db2 describe table user1.department

db2 describe select * from user.tables

[b]52. 查看表的索引[/b]

db2 describe indexes for table user1.department

[b]53. 查看视图[/b]

select viewname from views

where viewname='V_ZJT_TABLES';

[b]54. 查看索引[/b]

select indname from indexes

where indname='I_ZTABLES_TABNAME';

[b]55. 查看存贮过程[/b]

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

FROM SYSCAT.PROCEDURES;

[b]56. 类型转换(cast)[/b]

ip datatype:varchar

select cast(ip as integer)+50 from log_comm_failed

[b]57. 重新连接[/b]

connect reset

[b]58. 中断数据库连接[/b]

disconnect db2_gcb

[b]59. view application[/b]

LIST APPLICATION;

[b]60. kill application[/b]

FORCE APPLICATION(0);

db2 force applications all (强迫所有应用程序从数据库断开)

[b]61. lock table[/b]

lock table test in exclusive mode

[b]62. lock table 共享[/b]

lock table test in share mode

[b]63. 显示当前用户所有表[/b]

list tables

[b]64. 列出所有的系统表[/b]

list tables for system

[b]65. 显示当前活动数据库[/b]

list active databases

[b]66. 查看命令选项[/b]

list command options

[b]67. 系统数据库目录[/b]

LIST DATABASE DIRECTORY

[b]68. 表空间[/b]

list tablespaces

[b]69. 表空间容器[/b]

LIST TABLESPACE CONTAINERS FOR

Example: LIST TABLESPACE CONTAINERS FOR 1

[b]70. 显示用户数据库的存取权限[/b]

GET AUTHORIZATIONS


[b]71. 表或视图特权[/b]

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

grant all on tables to user WITH GRANT OPTION

[b]72. 程序包特权[/b]

GRANT EXECUTE

ON PACKAGE PACKAGE-name

TO PUBLIC

[b]73. 模式特权[/b]

GRANT CREATEIN ON SCHEMA SCHEMA-name TO USER

[b]74. 数据库特权[/b]

grant connect,createtab,dbadm on database to user

[b]75. 索引特权[/b]

grant control on index index-name to user

[b]76. 信息帮助 (? XXXnnnnn )[/b]

例:? SQL30081

[b]77. SQL 帮助(说明 SQL 语句的语法)[/b]

help statement

例如，help SELECT

[b]80. SQLSTATE 帮助(说明 SQL 的状态和类别代码)[/b]

? sqlstate 或 ? class-code

[b]81. 更改与"管理服务器"相关的口令[/b]

db2admin setid username password

[b]82. 创建 SAMPLE 数据库[/b]

db2sampl

db2sampl F:(指定安装盘)

[b]83. 使用操作系统命令[/b]

! dir

[b]84. 转换数据类型 (cast)[/b]

SELECT EMPNO, CAST(RESUME AS VARCHAR(370))

FROM EMP_RESUME

WHERE RESUME_FORMAT = 'ascii'

[b]85. UDF[/b]

要运行 DB2 Java 存储过程或 UDF，还需要更新服务器上的 DB2 数据库管理程序配置，以包括在该机器上安装 JDK 的路径

db2 update dbm cfg using JDK11_PATH d:sqllibjavajdk

TERMINATE

update dbm cfg using SPM_NAME sample

[b]86. 检查 DB2 数据库管理程序配置[/b]

db2 get dbm cfg

[b]87. 检索具有特权的所有授权名[/b]

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'DATABASE' FROM SYSCAT.DBAUTH

UNION

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'TABLE ' FROM SYSCAT.TABAUTH

UNION

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'PACKAGE ' FROM SYSCAT.PACKAGEAUTH

UNION

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'INDEX ' FROM SYSCAT.INDEXAUTH

UNION

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'COLUMN ' FROM SYSCAT.COLAUTH

UNION

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SCHEMA ' FROM SYSCAT.SCHEMAAUTH

UNION

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SERVER ' FROM SYSCAT.PASSTHRUAUTH

ORDER BY GRANTEE, GRANTEETYPE, 3

create table yhdab

(id varchar(10),

password varchar(10),

ywlx varchar(10),

kh varchar(10));

create table ywlbb

(ywlbbh varchar(8),

ywmc varchar(60))

[b]88. 修改表结构[/b]

alter table yhdab ALTER kh SET DATA TYPE varchar(13);

alter table yhdab ALTER ID SET DATA TYPE varchar(13);

alter table lst_bsi alter bsi_money set data type int;

insert into yhdab values

('20000300001','123456','user01','20000300001'),

('20000300002','123456','user02','20000300002');


[b]备份数据库：[/b]
CONNECT TO EXOA;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;
BACKUP DATABASE EXOA TO "/home/exoa2/db2bak/" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
CONNECT TO EXOA;
UNQUIESCE DATABASE;
CONNECT RESET;


[b]88.不能通过GRANT授权的权限有哪种?[/b]

SYSAM

SYSCTRL

SYSMAINT

要更该述权限必须修改数据库管理器配置参数

[b]89.表的类型有哪些?[/b]

永久表(基表)

临时表(说明表)

临时表(派生表)

[b]90.如何知道一个用户有多少表?[/b]

SELECT*FROMSYSIBM.SYSTABLESWHERECREATOR='USER'

[b]91.如何知道用户下的函数?[/b]

select* from IWH.USERFUNCTION

select*from sysibm.SYSFUNCTIONS

[b]92.如何知道用户下的VIEW数?[/b]

select*from sysibm.sysviewsWHERECREATOR='USER'

[b]93.如何知道当前DB2的版本?[/b]

select*from sysibm.sysvERSIONS

[b]94.如何知道用户下的TRIGGER数?[/b]

select*from sysibm.SYSTRIGGERSWHERESCHEMA='USER'

[b]95.如何知道TABLESPACE的状况?[/b]

select* from sysibm.SYSTABLESPACES

[b]96.如何知道SEQUENCE的状况?[/b]

select*from sysibm.SYSSEQUENCES

[b]97.如何知道SCHEMA的状况?[/b]

select*fromsysibm.SYSSCHEMATA

[b]98.如何知道INDEX的状况?[/b]

select*fromsysibm.SYSINDEXES

[b]99.如何知道表的字段的状况?[/b]

select*fromsysibm.SYSCOLUMNSWHERETBNAME='AAAA'

[b]100.如何知道DB2的数据类型?[/b]

select*fromsysibm.SYSDATATYPES

[b]101.如何知道BUFFERPOOLS状况?[/b]

select*fromsysibm.SYSBUFFERPOOLS

[b]102.DB2表的字段的修改限制?[/b]

只能修改VARCHAR2类型的并且只能增加不能减少.

[b]103.如何查看表的结构?[/b]

DESCRIBLETABLETABLE_NAME

OR

DESCRIBLESELECT*FROMSCHEMA.TABLE_NAME