DB2執行SQL語句如何才不會報錯呢?這是很多人都提到過的問題,下面就就教您DB2執行SQL語句並導出表記錄的實現方法,供您參考。
很多人在console寫DB2 SQL語句的時候習慣性的按enter換行,結果語句就直接執行報錯.這是因為DB2默認的分隔符是空格,
如果要換行寫語句,就必須在每行語句結尾加\,\表示換行.
還有一個寫法就是連接DB2後,輸入命令db2 -t, 這樣就不需每行語句結尾都加\.只需按enter換行,語句寫完後輸入分號(;) 提交語句運行.
推薦使用下面的方式在DB2執行SQL語句並導出表記錄.
- JXBI_js1:/db2home/mart796>touch crw.txt
- JXBI_js1:/db2home/mart796>db2 CONNECT TO jxmart USER ***** USING ******
- Database Connection Information
- Database server = DB2/AIX64 8.2.5
- SQL authorization ID = MART796
- Local database alias = JXMART
- ###db -t 命令指示語句允許換行,以分號;作結束符###
- JXBI_js1:/db2home/mart796>db2 -t
- (c) Copyright IBM Corporation 1993,2002
- Command Line Processor for DB2 SDK 8.2.5
- You can issue database manager commands and SQL statements from the command
- prompt. For example:
- db2 => connect to sample
- db2 => bind sample.bnd
- For general help, type: ?.
- For command help, type: ? command, where command can be
- the first few keywords of a database manager command. For example:
- ? CATALOG DATABASE for help on the CATALOG DATABASE command
- ? CATALOG for help on all of the CATALOG commands.
- To exit db2 interactive mode, type QUIT at the command prompt. Outside
- interactive mode, all commands must be prefixed with 'db2'.
- To list the current command option settings, type LIST COMMAND OPTIONS.
- For more detailed help, refer to the Online Reference Manual.
- db2 => EXPORT TO "/db2home/mart796/crw.txt" OF DEL MODIFIED BY coldel0x09
- db2 (cont.) => SELECT RN_USER_ID,
- db2 (cont.) => HIS_USER_ID,
- db2 (cont.) => RN_USER_NUMBER,
- db2 (cont.) => HIS_USER_NUMBER,
- db2 (cont.) => RN_BASE_COUNTY_CODE,
- db2 (cont.) => RN_BASE_PLAN_ID,
- db2 (cont.) => RN_CREATE_DATE
- db2 (cont.) => FROM JA.MARTD_DMRN_USER_MS
- db2 (cont.) => WHERE RN_DATE = '2008-07-01'
- db2 (cont.) => ;
- SQL3104N The Export utility is beginning to export data to file
- "/db2home/mart796/crw.txt".
- SQL3105N The Export utility has finished exporting "554848" rows.
- Number of rows exported: 554848
- db2 => quit;
- DB20000I The QUIT command completed successfully.
到此,總共導出584848條記錄.接下來的便是把記錄get 下來,導入到本地的ORACLE數據庫.比較簡單.
DB2 CREATE SERVER語句的用法
常用的DB2管理命令
深入探討DB2表連接原理
幾種常用的DB2循環用法
DB2創建數據庫的實現