? |
查看文章
|
? |
連接數據庫: ??connect?to?[數據庫名]?user?[操作用戶名]?using?[密碼]? 創建緩沖池(8K): ??create?bufferpool?ibmdefault8k?IMMEDIATE??SIZE?5000?PAGESIZE?8?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; GRANT?USE?OF?TABLESPACE?exoatbs?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; 1.?啟動實例(db2inst1): db2start 2.?停止實例(db2inst1): db2stop 3.?列出所有實例(db2inst1) db2ilist 5.列出當前實例: db2?get?instance 4.?察看示例配置文件: db2?get?dbm?cfg|more 5.?更新數據庫管理器參數信息: db2?update?dbm?cfg?using?para_name?para_value 6.?創建數據庫: db2?create?db?test 7.?察看數據庫配置參數信息 db2?get?db?cfg?for?test|more 8.?更新數據庫參數配置信息 db2?update?db?cfg?for?test?using?para_name?para_value 10.刪除數據庫: db2?drop?db?test 11.連接數據庫 db2?connect?to?test 12.列出所有表空間的詳細信息。 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 18.節點編目 db2?catalog?tcp?node?node_name?remote?server_ip?server?server_port 19.察看端口號 db2?get?dbm?cfg|grep?SVCENAME 20.測試節點的附接 db2?attach?to?node_name 21.察看本地節點 db2?list?node?direcotry 22.節點反編目 db2?uncatalog?node?node_name 23.數據庫編目 db2?catalog?db?db_name?as?db_alias?at?node?node_name 24.察看數據庫的編目 db2?list?db?directory 25.連接數據庫 db2?connect?to?db_alias?user?user_name?using?user_password 26.數據庫反編目 db2?uncatalog?db?db_alias 27.導出數據 db2?export?to?myfile?of?ixf?messages?msg?select?*?from?tb1 28.導入數據 db2?import?from?myfile?of?ixf?messages?msg?replace?into?tb1 29.導出數據庫的所有表數據 db2move?test?export 30.生成數據庫的定義 db2look?-d?db_alias?-a?-e?-m?-l?-x?-f?-o?db2look.sql 31.創建數據庫 db2?create?db?test1 32.生成定義 db2?-tvf?db2look.sql 33.導入數據庫所有的數據 db2move?db_alias?import 34.重組檢查 db2?reorgchk 35.重組表tb1 db2?reorg?table?tb1 36.更新統計信息 db2?runstats?on?table?tb1 37.備份數據庫test db2?backup?db?test 38.恢復數據庫test db2?restore?db?test 399\.列出容器的信息 db2?list?tablespace?containers?for?tbs_id?show?detail 40.創建表: db2?ceate?table?tb1(id?integer?not?null,name?char(10)) 41.列出所有表 db2?list?tables 42.插入數據: db2?insert?into?tb1?values(1,’sam’); db2?insert?into?tb2?values(2,’smitty’); .?建立數據庫DB2_GCB? CREATE?DATABASE?DB2_GCB?ON?G:?ALIAS?DB2_GCB? USING?CODESET?GBK?TERRITORY?CN?COLLATE?USING?SYSTEM?DFT_EXTENT_SZ?32? 2.?連接數據庫? connect?to?sample1?user?db2admin?using?8301206? 3.?建立別名? 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;? 4.?建立表? create?table?zjt_tables?as? (select?*?from?tables)?definition?only;? create?table?zjt_views?as? (select?*?from?views)?definition?only;? 5.?插入記錄? insert?into?zjt_tables?select?*?from?tables;? insert?into?zjt_views?select?*?from?views;? 6.?建立視圖? create?view?V_zjt_tables?as?select?tabschema,tabname?from?zjt_tables;? 7.?建立觸發器? 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))? 8.?建立唯一性索引? CREATE?UNIQUE?INDEX?I_ztables_tabname? [size=3]ON?zjt_tables(tabname);? 9.?查看表? select?tabname?from?tables? where?tabname='ZJT_TABLES';? 10.?查看列? select?SUBSTR(COLNAME,1,20)?as?列名,TYPENAME?as?類型,LENGTH?as?長度? from?columns? where?tabname='ZJT_TABLES';? 11.?查看表結構? db2?describe?table?user1.department? db2?describe?select?*?from?user.tables? 12.?查看表的索引? db2?describe?indexes?for?table?user1.department? 13.?查看視圖? select?viewname?from?views? where?viewname='V_ZJT_TABLES';? 14.?查看索引? select?indname?from?indexes? where?indname='I_ZTABLES_TABNAME';? 15.?查看存貯過程? SELECT?SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15)? FROM?SYSCAT.PROCEDURES;? 16.?類型轉換(cast)? ip?datatype:varchar? select?cast(ip?as?integer)+50?from?log_comm_failed? 17.?重新連接? connect?reset? 18.?中斷數據庫連接? disconnect?db2_gcb? 19.?view?application? LIST?APPLICATION;? 20.?kill?application? FORCE?APPLICATION(0);? db2?force?applications?all?(強迫所有應用程序從數據庫斷開)? 21.?lock?table lock?table?test?in?exclusive?mode? 22.?共享? lock?table?test?in?share?mode? 23.?顯示當前用戶所有表? list?tables? 24.?列出所有的系統表? list?tables?for?system? 25.?顯示當前活動數據庫? list?active?databases? 26.?查看命令選項? list?command?options? 27.?系統數據庫目錄? LIST?DATABASE?DIRECTORY? 28.?表空間? list?tablespaces? 29.?表空間容器? LIST?TABLESPACE?CONTAINERS?FOR? Example:?LIST?TABLESPACE?CONTAINERS?FOR?1? 30.?顯示用戶數據庫的存取權限? GET?AUTHORIZATIONS? 31.?啟動實例? DB2START? 32.?停止實例? db2stop? 33.?表或視圖特權? grant?select,delete,insert,update?on?tables?to?user? grant?all?on?tables?to?user?WITH?GRANT?OPTION? 34.?程序包特權? GRANT?EXECUTE? ON?PACKAGE?PACKAGE-name? TO?PUBLIC? 35.?模式特權? GRANT?CREATEIN?ON?SCHEMA?SCHEMA-name?TO?USER? 36.?數據庫特權? grant?connect,createtab,dbadm?on?database?to?user? 37.?索引特權? grant?control?on?index?index-name?to?user? 38.?信息幫助?(??XXXnnnnn?)? 例:??SQL30081? 39.?SQL?幫助(說明?SQL?語句的語法)? help?statement? 例如,help?SELECT? 40.?SQLSTATE?幫助(說明?SQL?的狀態和類別代碼)? ??sqlstate?或???class-code? 41.?更改與"管理服務器"相關的口令? db2admin?setid?username?password? 42.?創建?SAMPLE?數據庫? db2sampl? db2sampl?F:(指定安裝盤)? 43.?使用操作系統命令? !?dir? 44.?轉換數據類型?(cast)? SELECT?EMPNO,?CAST(RESUME?AS?VARCHAR(370))? FROM?EMP_RESUME? WHERE?RESUME_FORMAT?=?'ascii'? 45.?UDF 要運行?DB2?Java?存儲過程或?UDF,還需要更新服務器上的?DB2?數據庫管理程序配置,以包括在該機器上安裝?JDK?的路徑? db2?update?dbm?cfg?using?JDK11_PATH?d:sqllibjavajdk? TERMINATE? update?dbm?cfg?using?SPM_NAME?sample? 46.?檢查?DB2?數據庫管理程序配置? db2?get?dbm?cfg? 47.?檢索具有特權的所有授權名? 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))? 48.?修改表結構? 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');? 49.?業務類型說明? insert?into?ywlbb?values? ('user01','業務申請'),? ('user02','業務撤消'),? ('user03','費用查詢'),? ('user04','費用自繳'),? ('user05','費用預存'),? ('user06','密碼修改'),? ('user07','發票打印'),? ('gl01','改用戶基本信息'),? ('gl02','更改支付信息'),? ('gl03','日統計功能'),? ('gl04','沖帳功能'),? ('gl05','對帳功能'),? ('gl06','計費功能'),? ('gl07','綜合統計')? 備份數據庫: 以下是小弟在使用db2move中的一些經驗,希望對大家有所幫助。? ?db2???connect???to??YOURDB??? ?db2look?-d??YOURDB??-a?-e?-x?-o?creatab.sql? ?db2move???YOURDB??export? ?vi???creatab.sql? ?db2move??NEWDB??load? 在導入中可能因為種種原因發生中斷,會使數據庫暫掛? ?db2?select?tabname,tableid?from?syscat.tables?where?tableid=59? 表名知道后到db2move.lst(在db2move??YOURDB??export的目錄中)中找到相應的.ixf文件? 數據庫會恢復正常,可再用db2?list?tablespaces?show?detail查看 30.不能通過GRANT授權的權限有哪種? SYSAM SYSCTRL SYSMAINT 要更該述權限必須修改數據庫管理器配置參數 31.表的類型有哪些? 永久表(基表) 臨時表(說明表) 臨時表(派生表) 32.如何知道一個用戶有多少表? SELECT*FROMSYSIBM.SYSTABLESWHERECREATOR='USER' 33.如何知道用戶下的函數? select*fromIWH.USERFUNCTION select*fromsysibm.SYSFUNCTIONS 34.如何知道用戶下的VIEW數? select*fromsysibm.sysviewsWHERECREATOR='USER' 35.如何知道當前DB2的版本? select*fromsysibm.sysvERSIONS 36.如何知道用戶下的TRIGGER數? select*fromsysibm.SYSTRIGGERSWHERESCHEMA='USER' 37.如何知道TABLESPACE的狀況? select*fromsysibm.SYSTABLESPACES 38.如何知道SEQUENCE的狀況? select*fromsysibm.SYSSEQUENCES 39.如何知道SCHEMA的狀況? select*fromsysibm.SYSSCHEMATA 40.如何知道INDEX的狀況? select*fromsysibm.SYSINDEXES 41.如何知道表的字段的狀況? select*fromsysibm.SYSCOLUMNSWHERETBNAME='AAAA' 42.如何知道DB2的數據類型? select*fromsysibm.SYSDATATYPES 43.如何知道BUFFERPOOLS狀況? select*fromsysibm.SYSBUFFERPOOLS 44.DB2表的字段的修改限制? 只能修改VARCHAR2類型的并且只能增加不能減少. 45.如何查看表的結構? DESCRIBLETABLETABLE_NAME OR DESCRIBLESELECT*FROMSCHEMA.TABLE_NAME |
? | ? | ? |