隨筆-25  評論-6  文章-0  trackbacks-0
          ?
          ?
          查看文章
          ?
          DB2/SQL命令大全
          2006-12-25 13:21

          連接數據庫:

          ??connect?to?[數據庫名]?user?[操作用戶名]?using?[密碼]?

          創建緩沖池(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;

          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;

          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','綜合統計')?

          備份數據庫:
          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;

          以下是小弟在使用db2move中的一些經驗,希望對大家有所幫助。?

          ?db2???connect???to??YOURDB???
          連接數據庫?

          ?db2look?-d??YOURDB??-a?-e?-x?-o?creatab.sql?
          導出建庫表的SQL?

          ?db2move???YOURDB??export?
          用db2move將數據備份出來?

          ?vi???creatab.sql?
          如要導入的數據庫名與原數據庫不同,要修改creatab.sql中CONNECT?項?
          如相同則不用更改?

          ?db2move??NEWDB??load?
          將數據導入新庫中?

          在導入中可能因為種種原因發生中斷,會使數據庫暫掛?
          db2????list?tablespaces???show???detail?
          如:?
          ??????詳細說明:?
          ?????裝入暫掛?
          ?總頁數??????????????????????????=?1652?
          ?可用頁數????????????????????????=?1652?
          ?已用頁數?????????????????????????=?1652?
          ?空閑頁數?????????????????????????=?不適用?
          ?高水位標記(頁)?????????????????=?不適用?
          ?頁大小(字節)???????????????????=?4096?
          ?盤區大小(頁)???????????????????=?32?
          ?預讀取大小(頁)?????????????????=?32?
          ?容器數???????????????????????????=?1?
          ?狀態更改表空間標識????????????????????=?2?
          ?狀態更改對象標識??????????????????????=?59?

          ?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表?

          數據庫會恢復正常,可再用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

          ?
          ?

          ? ? ?
          ?
          posted on 2006-12-28 16:57 MyJavaWorld 閱讀(2776) 評論(0)  編輯  收藏

          只有注冊用戶登錄后才能發表評論。


          網站導航:
          博客園   IT新聞   Chat2DB   C++博客   博問  
           
          主站蜘蛛池模板: 沭阳县| 水富县| 南汇区| 阳原县| 尼木县| 田林县| 孝昌县| 广灵县| 乐安县| 柞水县| 永春县| 黎平县| 潜山县| 聊城市| 上林县| 宝坻区| 德清县| 武宁县| 顺昌县| 蒲江县| 新津县| 弥渡县| 泸州市| 庆安县| 瓦房店市| 怀仁县| 拜泉县| 洛川县| 拜城县| 新丰县| 闻喜县| 金阳县| 天台县| 小金县| 江阴市| 确山县| 都兰县| 城市| 鄱阳县| 博兴县| 清远市|