export to tempfile of del select * from TABLENAME where not清理條件；

load from tempfile of del modified by delprioritychar replace into TABLENAME nonrecoverable；

調整數據庫參數。%db2 connect to wisg

%db2 UPDATE DB CFG FOR wisg USING LOGBUFSZ??????? 512日志緩沖區大小(4KB)%db2 UPDATE DB CFG FOR wisg USING LOCKLIST??????? 512鎖定列表的最大存儲量(4KB)%db2 UPDATE DB CFG FOR wisg USING APP_CTL_HEAP_SZ 512最大應用程序控制堆大小(4KB)%db2 UPDATE DB CFG FOR wisg USING SORTHEAP??????? 25000排序列表堆(4KB)%db2 UPDATE DB CFG FOR wisg USING MAXLOCKS??????? 60每個應用程序的鎖定百分比列表%db2 UPDATE DB CFG FOR wisg USING NUM_IOCLEANERS? 4異步頁清除程序的數目%db2 UPDATE DB CFG FOR wisg USING NUM_IOSERVERS?? 6? I/O服務器的數目%db2 UPDATE DB CFG FOR wisg USING logfilsiz?????? 160000日志文件大小(4KB)%db2 UPDATE DB CFG FOR wisg USING LOGPRIMARY????? 3主日志文件的數目%db2 UPDATE DB CFG FOR wisg USING LOCKTIMEOUT????? 60鎖的超時時間%db2 UPDATE DBM CFG USING HEALTH_MON off關閉健康監控中心%db2 UPDATE DBM CFG USING START_STOP_TIME 3啟動停止超時時間%db2 terminate

79. db2創建用戶1.在系統中創建一個用戶2.用db2inst1鏈接數據:db2 connect to das

3.給新用戶賦權限:db2 grant connect on database to user XXX

80.啟動數據庫監聽db2set DB2COMM=tcpip

db2stop force

db2start

db2優化：以db2inst1用戶登錄，調整實例參數，并測試啟動是否成功。#su - db2inst1

%db2start

%db2 UPDATE DBM CFG USING SHEAPTHRES 125000共享排序的排序堆域值(4KB)%db2set DB2_PARALLEL_IO=*所有表空間將使用每個容器主軸數等于6的缺省值。預取大小比啟用并行I/O時大6倍。所有表空間均會啟用并行I/O。預取請求分解成多個較小請求，每個請求等于預取大小除以擴展數據塊大小后的值(或等于容器數乘以主軸數)。%db2set DB2_SKIPINSERTED=on

%db2set DB2_EVALUNCOMMITTED=on為了提高并發性，DB2(R)現在允許在某些情況下對CS或RS隔離掃描延遲行鎖定，直到知道一條記錄滿足查詢的謂詞為止。缺省情況下，當在表掃描或索引掃描期間執行行鎖定時，DB2會先鎖定已掃描的每一行然后再確定該行是否符合查詢要求。為了提高掃描的并發性，可以延遲行鎖定，直到確定某行符合查詢要求為止。要利用此功能，應啟用DB2_EVALUNCOMMITTED注冊表變量。%db2set DB2_SKIPDELETED=on如果啟用了DB2_SKIPDELETED注冊表變量，則DB2在類型2索引掃描中將跳過已刪除的鍵。%db2stop

77.數據庫調優db2untag -f /dev/raw/raw1

db2untag -f /dev/raw/raw2

db2untag -f /dev/raw/raw3

db2untag -f /dev/raw/raw4

db2 update db cfg using newlogpath /dev/raw/raw4

db2 update db cfg using logfilsiz 20480

db2 update db cfg using logprimary 15

db2 update db cfg using logsecond 5

db2 update db cfg using PCKCACHESZ 51200堆中沒有足夠的存儲器可用來處理語句:db2 update db cfg using APP_CTL_HEAP_SZ 1000

statement is too long or too complex:db2 update db cfg using STMTHEAP 4096

db2 alter bufferpool ibmdefaultbp size 102400

78.創建裸設備#fdisk –l

#fdisk /dev/sdb其中/dev/sdb為陣列的一個盤輸入“n”創建新分區輸入“p”創建主分區。輸入“1”，指定分區號為1選擇默認值1，按鍵盤上的“Enter”鍵選擇默認值51200，按鍵盤上的“Enter”鍵輸入“p”查看分區信息輸入“t”改變分區ID輸入“1”指定分區號為1。輸入“8e”指定ID為8e，8e為LVM類型分區輸入“p”查看分區信息輸入“w”，上述設置生效并退出創建物理卷#pvcreate -ff /dev/sdb1創建邏輯卷組#vgcreate -s 64M -v db2dg /dev/sdb1創建邏輯卷#lvcreate -l 160 –n? db2vol01 db2dg

#lvcreate -L 5120M -n db2vol02 db2dg

#lvcreate -L 5120M -n db2vol03 db2dg查看創建的卷組和邏輯卷。#vgdisplay –v創建文件系統。#mkfs -t ext3 /dev/db2dg/db2vol01掛載文件系統。#mkdir /db2vol01

#mount /dev/db2dg/db2vol01 /db2vol01

#chown –R db2inst1:db2grp1 /db2vol01掛載文件系統。#mkdir /db2vol01

#mount /dev/db2dg/db2vol01 /db2vol01

#chown –R db2inst1:db2grp1 /db2vol01關于目錄/home/db2inst1，具體的操作如下：#cd /home/db2inst1

#tar cvf /home/db2.tar .*

#cd /opt/IBM/db2/V8.1/instance

#./db2idrop db2inst1

#mount /dev/db2dg/db2vol10 /home/db2inst1

#cd /home/db2inst1

#tar xvf /home/db2.tar

#chown –R db2inst1:db2grp1 /home/db2inst1把邏輯卷鏈接到裸設備上。#raw /dev/raw/raw1 /dev/db2dg/db2vol02

#raw /dev/raw/raw2 /dev/db2dg/db2vol03

#raw /dev/raw/raw3 /dev/db2dg/db2vol04

#raw /dev/raw/raw4 /dev/db2dg/db2vol05改變裸設備的屬組。#chown -R db2inst1:db2grp1 /dev/raw/raw1

#chown -R db2inst1:db2grp1 /dev/raw/raw2

#chown -R db2inst1:db2grp1 /dev/raw/raw3

#chown -R db2inst1:db2grp1 /dev/raw/raw4

73.檢索具有特權的所有授權名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

74.修改表結構alter table yhdab ALTER kh SET DATA TYPE varchar(13);

alter table lst_bsi alter bsi_money set data type int;

75.備份數據庫：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;

76.查看是哪張表掛起:db2 select tabname,tableid from syscat.tables where tableid=59表名知道后到db2move.lst(在db2move? YOURDB? export的目錄中)中找到相應的.ixf文件db2 load from tab11.ixf of ixf terminate into db2admin.xxxxxxxxx

tab11.ixf對應的是xxxxxxxxx表

51. view application：LIST APPLICATION;

52. kill application：FORCE APPLICATION(0);

db2 force applications all (強迫所有應用程序從數據庫斷開)

53. lock table：lock table test in exclusive mode

54.共享：lock table test in share mode

55.列出所有的系統表：list tables for system

56.顯示當前活動數據庫：list active databases

57.查看命令選項：list command options

58.表空間：list tablespaces

59.表空間容器：LIST TABLESPACE CONTAINERS FOR；Example: LIST TABLESPACE CONTAINERS FOR 1

60.顯示用戶數據庫的存取權限：GET AUTHORIZATIONS

61.表或視圖特權:grant select,delete,insert,update on tables to user

grant all on tables to user WITH GRANT OPTION

62.程序包特權:GRANT EXECUTE ON PACKAGE PACKAGE-name TO PUBLIC

63.模式特權:GRANT CREATEIN ON SCHEMA SCHEMA-name TO USER

64.數據庫特權:grant connect,createtab,dbadm on database to user

65.索引特權:grant control on index index-name to user

66.信息幫助(? XXXnnnnn):例:? SQL30081

67. SQL幫助(說明SQL語句的語法):help statement

68. SQLSTATE幫助(說明SQL的狀態和類別代碼):? sqlstate或? class-code

69.更改與"管理服務器"相關的口令:db2admin setid username password

70.使用操作系統命令:! dir

71.轉換數據類型(cast)

SELECT EMPNO, CAST(RESUME AS VARCHAR(370)) FROM EMP_RESUME WHERE RESUME_FORMAT = 'ascii'

72. UDF:要運行DB2 Java存儲過程或UDF，還需要更新服務器上的DB2數據庫管理程序配置，以包括在該機器上安裝JDK的路徑db2 update dbm cfg using JDK11_PATH d:sqllibjavajdk

TERMINATE

update dbm cfg using SPM_NAME sample

31.重組檢查：db2 reorgchk

32.重組表tb1：db2 reorg table tb1

33.更新統計信息：db2 runstats on table tb1

34.備份數據庫test：db2 backup db test

35.恢復數據庫test：db2 restore db test

36.列出容器的信息：db2 list tablespace containers for tbs_id show detail

37.列出所有表：db2 list tables

38.建立別名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;

39.建立觸發器CREATE TRIGGER zjt_tables_del

AFTER DELETE ON zjt_tables

REFERENCING OLD AS O

FOR EACH ROW MODE DB2SQL

40.建立唯一性索引：CREATE UNIQUE INDEX I_ztables_tabname ON zjt_tables(tabname);

41.查看表：select tabname from tables where tabname='ZJT_TABLES';

42.查看列：select SUBSTR(COLNAME,1,20) as列名,TYPENAME as類型,LENGTH as長度from columns where tabname='ZJT_TABLES';

43.查看表結構：db2 describe table user1.department；db2 describe select * from user.tables

44.查看表的索引：db2 describe indexes for table user1.department

45.查看視圖：select viewname from views where viewname='V_ZJT_TABLES';

46.查看索引：select indname from indexes where indname='I_ZTABLES_TABNAME';

47.查看存貯過程：SELECT SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15) FROM SYSCAT.PROCEDURES;

48.類型轉換(cast)

ip datatype:varchar

select cast(ip as integer)+50 from log_comm_failed

49.重新連接：connect reset

50.中斷數據庫連接：disconnect db2_gcb

1.啟動實例(db2inst1):db2start

2.停止實例(db2inst1):db2stop

3.列出所有實例(db2inst1)：db2ilist

4.列出當前實例:db2 get instance

5.察看示例配置文件:db2 get dbm cfg|more

6.更新數據庫管理器參數信息:db2 update dbm cfg using para_name para_value

7.察看數據庫配置參數信息：db2 get db cfg for test|more

8.更新數據庫參數配置信息：db2 update db cfg for test using para_name para_value

9.刪除數據庫:db2 drop db test

10.連接數據庫：db2 connect to test

11.列出所有表空間的詳細信息：db2 list tablespaces show detail

12.創建索引:db2 create index idx1 on tb1(id);

13.創建視圖:db2 create view view1 as select id from tb1

14.查詢視圖:db2 select * from view1

15.節點編目：db2 catalog tcp node node_name remote server_ip server server_port

16.察看端口號：db2 get dbm cfg|grep SVCENAME

17.測試節點的附接：db2 attach to node_name

18.察看本地節點：db2 list node direcotry

19.節點反編目：db2 uncatalog node node_name

20.數據庫編目：db2 catalog db db_name as db_alias at node node_name

21.察看數據庫的編目：db2 list db directory

22.連接數據庫：db2 connect to db_alias user user_name using user_password

23.數據庫反編目：db2 uncatalog db db_alias

24.導出數據：db2 export to myfile of ixf messages msg select * from tb1

25.導入數據：db2 import from myfile of ixf messages msg replace into tb1

26.導出數據庫的所有表數據：db2move test export

27.生成數據庫的定義：db2look -d db_alias -a -e -m -l -x -f -o db2look.sql

28.生成定義：db2 -tvf db2look.sql

29.加載腳本: db2 –td@ -vf filename，其中@為命令行的分隔符30.導入數據庫所有的數據：db2move db_alias import

創建數據庫在文件上創建數據庫：CREATE DATABASE das USING CODESET 'UTF-8' TERRITORY 'CN' CATALOG TABLESPACE MANAGED BY DATABASE USING (file 'd:\db2data\dascat.dat' 102400) EXTENTSIZE 16 PREFETCHSIZE 32 USER TABLESPACE MANAGED BY DATABASE USING (file 'd:\db2data\dasusr.dat' 102400) EXTENTSIZE 256 PREFETCHSIZE 64 TEMPORARY TABLESPACE MANAGED BY DATABASE USING (file 'd:\db2data\dastmp.dat' 102400) EXTENTSIZE 256 WITH "DAS DB"在裸設備上創建數據庫CREATE DATABASE das212 CATALOG TABLESPACE MANAGED BY DATABASE USING (device '/dev/db2dg/lv_dat01' 10240000) EXTENTSIZE 16 PREFETCHSIZE 32 USER TABLESPACE MANAGED BY DATABASE USING (device '/dev/db2dg/lv_dat02' 10240000) EXTENTSIZE 256 PREFETCHSIZE 64 TEMPORARY TABLESPACE MANAGED BY DATABASE USING (device '/dev/db2dg/lv_dat03' 10240000) EXTENTSIZE 256 WITH "DAS DB"

GRANT USE OF TABLESPACE exoatbs TO PUBLIC;

GRANT USE OF TABLESPACE exoatbs16k TO PUBLIC;

GRANT USE OF TABLESPACE exoatbs32k TO PUBLIC;創建系統表空間：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;

創建緩沖池(8K)：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 ;創建表空間：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;

說明：在不相關的數據表export數據時，可以采取并發的形式，以提高效率；TABLENAME指待清理table的名稱；modified by delprioritychar防止數據庫記錄中存在換行符,導致數據無法裝入的情況;

replace into對現數據庫中的內容進行替換，即將現行的數據記錄清理，替換為數據文件內容；nonrecoverable無日志方式裝入；connect to [數據庫名] user [操作用戶名] using [密碼]

分享到：

2010-06-20 14:44

瀏覽 1524

分類:數據庫

評論

總結

以上是生活随笔為你收集整理的db2mysql下载_db2SQL命令的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。