IT技術互動交流平臺

DB2操作

來源:IT165收集  發布日期:2016-05-27 17:37:35

第一章DB2開發基礎

在進行DB2應用開發之前,了解DB2應用程序的結構,掌握相關概念,設置開發環境是很必要的。本章主要介紹這幾個方面的內容。

1.1 DB2應用程序開發概述

1.1.1 程序結構

DB2應用程序包括以下幾個部分:

1.聲明和初始化變量

2.連接到數據庫

3.執行一個或者多個事務

4.與數據庫斷開連接

5.結束程序

一個事務是一組數據庫操作,在提交給數據庫之前,必須確認完全成功執行。在嵌入式SQL應用程序中,當應用程序成功地連接到一個數據庫時,一個事務就自動開始了,結束于執行一條COMMIT語句或者ROLLBACK語句。同時,下一條SQL語句開始一個新的事務。

每一個應用程序的開始必須包括:

l 數據庫管理器用來與宿主程序交互的所有變量和數據結構的聲明

l 設置SQL通信區(SQLCA),提供錯誤處理的SQL語句

注意:用Java寫的DB2應用程序在SQL語句出錯時拋出一個SQLException異常,需要在catch塊里處理,而不是使用SQLCA。

每個應用程序的主體包括訪問和管理數據的SQL語句。這些語句組成事務,事務必須包括下列語句:

l CONNECT語句,其建立一個與數據庫服務器的連接

l 一條或多條:

▲數據操縱語句(例如,SELECT語句)

▲數據定義語句(例如,CREATE語句)

▲數據控制語句(例如,GRANT語句)

l COMMIT或者ROLLBACK語句結束事務

應用程序的結束通常包括釋放程序與數據庫服務器的連接和釋放其他資源的SQL語句。

1.1.2 開發方法選擇

可使用幾種不同的程序設計接口來存取 DB2 數據庫。您可以:

l 將靜態和動態 SQL 語句嵌入應用程序。

l 在應用程序中編寫“DB2 調用層接口”(DB2 CLI) 的函數調用,以調用動態 SQL 語句。

l 開發調用“Java 數據庫鏈接”應用程序設計接口 (JDBC API) 的 Java 應用程序和小程序。

l 開發符合“數據存取對象 (DAO) ”和“遠程數據對象 (RDO) ” 規范的 Microsoft Visual Basic 和 Visual C++ 應用程序,以及使用“對象鏈接和嵌入數據庫 (OLE DB) 橋接”的“ActiveX 數據對象”(ADO) 應用程序。

l 使用 IBM 或第三方工具如 Net.Data、Excel、Perl、“開放式數據庫鏈接”(ODBC) 最終用戶工具如 Lotus Approach 及其程序設計語言 LotusScript 來開發應用程序。

l 要執行備份和復原數據庫等管理功能,應用程序可以使用 DB2 API。

應用程序存取 DB2 數據庫的方式將取決于想要開發的應用程序類型。例如,如果想開發數據輸入應用程序,可以選擇將靜態 SQL 語句嵌入應用程序。如果想開發在萬維網 (WWW) 上執行查詢的應用程序,可能要選擇 Net.Data、Perl 或 Java。

1.2相關概念

1.2.1 嵌入式SQL編程

嵌入式SQL應用程序就是將SQL語句嵌入某個宿主語言中,SQL語句提供數據庫接口,宿主語言提供應用程序的其他執行功能。

“結構化查詢語言”(SQL) 是一種數據庫接口語言,它用來存取并處理 DB2 數據庫中的數據?梢詫 SQL 語句嵌入應用程序,使應用程序能執行 SQL 支持的任何任務,如檢索或存儲數據。通過使用 DB2,可以用 C/C++、COBOL、FORTRAN、Java (SQLJ) 以及 REXX 程序設計語言來編寫嵌入式 SQL 應用程序。

嵌入了 SQL 語句的應用程序稱為主程序。用于創建主程序的程序設計語言稱為宿主語言。用這種方式定義程序和語言,是因為它們包含了 SQL 語句。

對于靜態 SQL 語句,您在編譯前就知道 SQL 語句類型以及表名和列名。唯一未知的是語句正搜索或更新的特定數據值?梢杂盟拗髡Z言變量表示那些值。在運行應用程序之前,要預編譯、編譯和捆綁靜態 SQL 語句。靜態 SQL 最好在變動不大的數據庫上運行。否則,這些語句很快會過時。

相反,動態 SQL 語句是應用程序在運行期構建并執行的那些語句。一個提示最終用戶輸入 SQL 語句的關鍵部分(如要搜索的表和列的名稱)的交互式應用程序是動態 SQL 一個很好的示例。 應用程序在運行時構建 SQL 語句,然后提交這些語句進行處理。

可以編寫只有靜態 SQL 語句或只有動態 SQL 語句,或者兼有兩者的應用程序。

一般來說,靜態 SQL 語句最適合用于帶有預定義事務的高性能應用程序。預訂系統是這種應用程序一個很好的示例。

一般來說,動態 SQL 語句最適合于必須在運行期指定事務的、要快速更改數據庫的應用程序。交互式查詢界面是這種應用程序一個很好的示例。

將 SQL 語句嵌入應用程序時,必須按以下步驟預編譯應用程序并將其與數據庫捆綁:

1. 創建源文件,以包含帶嵌入式 SQL 語句的程序。

2. 連接數據庫,然后預編譯每個源文件。

預編譯程序將每個源文件中的 SQL 語句轉換成對數據庫管理程序的 DB2 運行期 API 調用。預編譯程序還在數據庫中生成一個存取程序包,并可選擇生成一個捆綁文件(如果您指定要創建一個的話)。

存取程序包包含由 DB2 優化器為應用程序中的靜態 SQL 語句選擇的存取方案。這些存取方案包含數據庫管理程序執行靜態 SQL 語句所需的信息,以便該管理程序可以用優化器確定的最有效的方式來執行這些語句。對于動態 SQL 語句,優化器在您運行應用程序時創建存取方案。

捆綁文件包含創建存取程序包所需要的 SQL 語句和其他數據?梢允褂美壩募谝院笾匦吕墤贸绦,而不必首先預編譯應用程序。重新捆綁創建針對當前數據庫狀態的優化存取方案。如果應用程序將存取與預編譯時所用數據庫不同的數據庫,則必須重新捆綁應用程序。如果數據庫統計信息自上次捆綁后已經更改,建議您重新捆綁應用程序。

3. 使用主語言編譯程序編譯修改的源文件(以及其他無 SQL 語句的文件)。

4. 將目標文件與 DB2 和主語言庫連接,以生成一個可執行程序。

5. 如果在預編譯時未對捆綁文件進行捆綁;或者準備存取不同數據庫,則應對捆綁文件進行捆綁以創建存取程序包。

6. 運行該應用程序。此應用程序使用程序包中的存取方案存取數據庫。

1.2.2 預編譯

創建源文件之后,必須對每一個含有SQL語句的宿主語言文件用PREP命令進行預編譯。預編譯器將源文件中的SQL語句注釋掉,對那些語句生成DB2運行時API調用。

在預編譯一個應用之前,必須連接到一個數據庫服務器,不論是自動連接還是顯性連接。即使你在客戶端工作站上預編譯應用程序、預編譯器在客戶端產生的修改后源文件和信息,預編譯器也需要使用服務器連接來執行一些確認任務。

預編譯器也創建數據庫管理器在處理針對某個數據庫的SQL語句時需要的信息。這些信息存儲在一個程序包或者一個捆綁文件或者兩者之中,視預編譯器的選項而定。

下面是使用預編譯器的一個典型例子。預編譯一個名叫filename.sqc的C嵌入式SQL源文件,發出下面的命令創建一個C源文件,默認名字為filename.c,和一個捆綁文件,默認名字為filename.bnd:

DB2 PREP filename.sqc BINDFILE

預編譯器最多產生四種類型的輸出:

l 修改后的源文件

l 程序包

l 捆綁文件

l 信息文件

1、修改后的源文件

這個文件是預編譯器將SQL語句轉化為DB2運行時API調用后,原始源文件的新版本。它被賦予了相應宿主語言的擴展名。

2、程序包

如果使用了PACKAGE選項(默認的),或者沒有指定任何BINDFILE、SYNTAX、SQLFLAG選項,程序包存儲在所連接到的數據庫中。程序包僅僅包含執行訪問本數據的SQL語句時需要的所有信息。除非你用PACKAGE USING選項指定一個不同的名字,否則預編譯器將使用源文件名字的前8個字符作為程序包名。

使用PACKAGE選項時,在預編譯處理過程中使用的數據庫必須擁有源文件中靜態SQL語句參考到的所有數據庫對象。例如不能夠預編譯一條SELECT語句,如果參考的表在數據庫中不存在。

3、捆綁文件

如果使用了BINDFILE選項,預編譯器將創建一個捆綁文件(擴展名為.bnd),它包含創建程序包的一些數據。這個文件可以在后面用BIND命令將應用捆綁到一個或多個數據庫。如果指定了BINDFILE選項,沒有指定PACKAGE選項,捆綁被延緩直到執行BIND命令。注意,對于命令行處理器(CLP),PREP默認不指定BINDFILE選項。因此,如果你使用CLP,又想延緩捆綁,那么你必須指定BINDFILE選項。

如果在預編譯時請求一個捆綁文件但是沒有指定PACKAGE選項,不會在數據庫中創建程序包;對象不存在和沒有權限的SQLCODE被看作警告而不會被看作錯誤。這使得你能夠預編譯程序和創建一個捆綁文件,不需要參考到的對象必須存在,也不需要你擁有執行正被預編譯的SQL語句的權限。

4、信息文件(Message File)

如果使用了MESSAGES選項,預編譯器將信息重定向到指定的文件中。這些信息包括警告和錯誤信息,它們描述了在預編譯過程中產生的問題。如果源文件沒有預編譯成功,使用警告和錯誤信息來斷定問題,改正源文件,然后再預編譯。如果沒有使用MESSAGE選項,預編譯信息被寫到標準輸出上。

1.2.3 程序包

程序包就是存儲在相對應數據庫中的包含數據庫系統在捆綁時對特定SQL語句所產生的訪問策略。

所有SQL語句經過編譯優化后就產生可以直接對數據庫進行訪問的訪問策略,存儲于相應的數據庫中。這些訪問策略可以在應用程序調用相對應的SQL語句時得到訪問。程序包對應于特定的應用程序,但是并不是與應用程序一起存放,而是同相對應的數據庫一起存放。

1.2.4 捆綁

捆綁(bind)是創建數據庫管理器在應用執行時為了訪問數據庫而需要的程序包的過程。捆綁可以在預編譯時指定PACKAGE選項隱含地完成,或者使用BIND命令依據預編譯過程中產生的捆綁文件顯性地完成。

下面是使用BIND命令的一個典型例子。將名為filename.bnd的捆綁文件捆綁到數據庫,使用下面的命令:

DB2 BIND filename.bnd

每一個獨立預編譯的源代碼模塊都需要創建一個程序包。如果一個應用有5個源文件,其中3個需要預編譯,那么要創建3個程序包或者3個捆綁文件。默認上,每一個程序包的名字與產生.bnd文件的源文件名字相同,但是只要前8個字符。如果新建的程序包名字與已存在于數據庫中的程序包名相同,新的程序包將替換原先存在的程序包。要顯性地指定一個不同的程序包名,必須在PREP命令使用PACKAGE USING選項。

1.2.5 工作單元

一個工作單元是一個單一的邏輯事務。它包含一個SQL語句序列,在這個序列中的所有操作,被看作一個整體,要么都成功,要么都失敗。DB2支持兩種類型的連接(connection)。連接類型決定一個應用程序如何與遠程數據庫工作,并且決定該應用程序同時能與多少個數據庫工作。

(1)連接類型1

應用程序在每個工作單元中只能連接單個數據庫,此時,這個工作單元稱為遠程工作單元(RUOW, Remote Unit of Work)

(2)連接類型2

允許應用程序在每個工作單元中連接多個數據庫,此時,這個工作單元稱為分布式工作單元(DUOW, Distributed Unit of Work)

我們來看下面的例子:

(1)Remote Unit of Work – Type 1 Connect

 
  clip_image001

在這個例子中,連接類型為1,工作單元為遠程工作單元(RUOW),應用程序在連接到數據庫DB2_2之前,必須結束當前的工作單元(即事務,通過執行COMMIT語句)。

(2)Distributed Unit of Work – Type 2 Connect

 
  clip_image002

在這個例子中,連接類型為2,工作單元為分布式工作單元(DUOW)。應用程序在連接到數據庫DB2_2之前,不需要結束當前的事務。在一個工作單元中,可以有多個數據庫連接,但是只有一個處于激活狀態,其它都處于睡眠狀態。用SET CONNECTION TO db_name語句切換數據庫連接。

1.2.6 應用程序、捆綁文件與程序包之間的關系

一個程序包是一個存儲在數據庫中的對象,它包含在執行某個源文件中特定SQL語句時所需的信息。數據庫應用程序對用來創建應用程序的每一個預編譯源文件使用一個程序包。每一個程序包是一個獨立的實體,同一個或不同應用程序所使用的程序包之間沒有任何關系。程序包在對源文件執行附帶綁定的預編譯時創建,或者通過綁定綁定文件創建。

數據庫應用程序使用程序包的原因:提高性能和緊湊性。通過預編譯SQL語句,使得SQL語句在創建應用程序時被編譯進程序包,而不是在運行時。每一條語句都被分析,高效率的操作數串存儲在程序包中。在運行時,預編譯器產生的代碼調用數據庫管理器運行時服務APIs,根據輸入輸出數據的變量信息,執行程序包。

預編譯的優點僅僅對靜態SQL語句有效。動態執行的SQL語句不用預編譯,但是它們在需要在運行時完成處理的整個步驟。注意:不要認為一條SQL語句的靜態版本的執行效率一定比其動態版本的高。在某個方面,靜態SQL語句快是因為不需要動態語句的準備開銷。在另一方面,同樣的語句,動態執行會快些,是因為優化器能利用當前數據庫的統計信息,而不是以前的統計信息 。有關靜態SQL與動態SQL的比較,參照下表:

表 靜態SQL與動態SQL的比較

考慮因素 最好的選擇

執行SQL語句的時間:

少于兩秒 靜態

2 到10 秒 兩者均可

多于10秒 動態

數據一致性

統一的數據分布 靜態

輕微不統一分布 兩者均可

高度不統一分布 動態

范圍謂詞(<,>,BETWEEN,LIKE)使用

很少使用 靜態

偶然使用 兩者均可

經常使用 動態

執行的重復性

很多次(10或者更多) 兩者均可

幾次(少于10次) 兩者均可

一次 靜態

查詢的種類

隨機 動態

固定 兩者均可

運行時環境(DML/DDL)

事務處理(DML Only) 兩者均可

混合(DML和DDL – DDL affects packages) 動態

混合(DML和DDL – DDL does not affect packages) 兩者均可

運行runstats的頻度

很低 靜態

正常 兩者均可

頻繁 動態

1.2.7 定界符

如果源程序中有SQL語句,源程序不能立即被源語言編譯器處理。它首先要經過一個翻譯過程,將SQL語句翻譯成源語言編譯器能夠理解的東西。做翻譯工作的程序就叫做預編譯器。預編譯器識別SQL語句的方法是通過定界符將SQL語句標識出來。

定界符的作用是使預編譯器能夠識別出需被翻譯的SQL語句,并且必須標識出每一條嵌入的SQL語句。不同的宿主語言使用不同的定界符,下表列出了四種常用語言的定界符:

語言

定界符

C;C++

EXEC SQL

sql statement ;

COBOL

EXEC SQL

sql statement END-EXEC.

FORTRAN

EXEC SQL

sql statement

JAVA

#sqlj {sql statement} ;

例子:

l SQL語句

UPDATE TEMPL

SET WORKDEPT = ‘C02’

WHERE WORKDEPT = ‘C01’

l 在C程序中的SQL語句

EXEC SQL

UPDATE TEMPL

SET WORKDEPT = ‘C02’

WHERE WORKDEPT = ‘C01’ ;

l 在COBOL程序中的SQL語句

EXEC SQL

UPDATE TEMPL

SET WORKDEPT = ‘C02’

WHERE WORKDEPT = ‘C01’

END-EXEC.

l 在Java程序中的SQL語句

#sqlj {UPDATE TEMPL SET WORKDEPT = ‘C02’ WHERE WORKDEPT = ‘C01’} ;

第二章DB2應用程序設計方法

本章介紹DB2應用程序設計的一般方法,以及如何設置測試環境。

2.1編程方法

2.1.1 訪問數據

在關系數據庫中,必須使用SQL訪問請求的數據,但是可以選擇如何將SQL結合到應用程序中去?梢詮南卤砹谐龅慕涌诤退鼈冎С值恼Z言中選擇:

接口

支持的語言

嵌入SQL

C/C++, COBOL, FORTRAN, Java (SQLJ), REXX

DB2 CLI 和ODBC

C/C++, Java (JDBC)

Microsoft Specifications, including ADO, RDO, and OLE DB

Visual Basic, Visual C++

Perl DBI

Perl

Query Products

Lotus Approach, IBM Query Management Facility

一、嵌入SQL

嵌入SQL有其優勢,它可以包含靜態SQL或者動態SQL,或者兩種類型混合使用。如果在開發應用程序時,SQL語句的格式和內容已經確定,應該考慮在程序中采用嵌入的靜態SQL。利用靜態SQL,執行應用程序的人暫時繼承將應用程序捆綁到數據庫中的用戶的權限,而不需要對此人賦予其它權限(除了應用程序的執行權)。動態SQL的執行需要執行應用程序的人的權限,但也有例外情況,就是在捆綁應用程序的時候使用DYNAMICRULES BIND選項。一般來講,如果直到執行時才能確定SQL語句,那么應該采用嵌入的動態SQL。這比較安全,而且可以處理更多形式的SQL。

注意:JAVA語言的嵌入SQL(SQLJ)應用程序只能嵌入靜態SQL語句。然而,在SQLJ應用程序中,可以通過使用JDBC調用動態SQL語句。

在使用編程語言編譯器前,必須對源文件進行預編譯,將嵌入的SQL語句轉換為宿主語言的數據庫服務APIs。在應用程序運行之前,必須將程序中的SQL捆綁到數據庫里。

我們在第三章“靜態SQL應用編程”中有詳細介紹。

二、DB2 CLI和ODBC

DB2調用級接口(DB2 CLI)是IBM公司數據庫服務器的DB2系列可調用SQL接口,它是一個關系數據庫數據訪問的C和C++語言編程接口,它用函數調用的方式,將動態SQL語句作為參數傳遞給數據庫管理器。也就是說,一個可調用的SQL接口就是一個調用動態SQL語句的應用程序編程接口(API)。CLI可以替代嵌入動態SQL,但是與嵌入SQL不同,它不需要預編譯或者捆綁。

DB2 CLI是基于微軟開放數據庫連接(ODBC)規范和X/Open規范開發的。IBM選擇這些規范是為了遵循業界標準,使熟悉這些數據庫接口的應用程序開發人員能在短期內掌握CLI的開發方法。

JDBC:

DB2在Java語言方面的支持包括JDBC,JDBC是一個與廠商無關的動態SQL接口,利用它使得應用程序可以通過標準Java方法調用實現數據的訪問。

JDBC與DB2 CLI一樣不需要作預編譯或者捆綁,作為一個與廠商無關的標準,JDBC應用程序具有良好的移植性。用JDBC開發的應用程序只采用動態SQL。

三、微軟規范

開發符合“數據存取對象 (DAO) ”和“遠程數據對象 (RDO) ” 規范的 Microsoft Visual Basic 和 Visual C++ 應用程序,以及使用“對象鏈接和嵌入數據庫 (OLE DB) 橋接”的“ActiveX 數據對象 (ADO) ”應用程序。

四、Perl數據庫接口

DB2支持Perl數據庫接口(DBI)數據訪問規范,使用DBD::DB2驅動程序。DBD::DB2驅動程序支持下列平臺:

AIX

Operating Systems

Version 4.1.4 and later

C Compilers

IBM C for AIX Version 3.1 and later

HP-UX

Operating Systems

HP-UX Version 10.10 with Patch Levels: PHCO_6134, PHKL_5837,

PHKL_6133, PHKL_6189, PHKL_6273, PHSS_5956

HP-UX Version 10.20

HP-UX Version 11

C Compilers

HP C/HP-UX Version A.10.32

HP C Compiler Version A.11.00.00 (for HP-UX Version 11)

Linux

Operating Systems

Linux Redhat Version 5.1 with kernel 2.0.35 and glibc version 2.0.7

C Compilers

gcc version 2.7.2.3 or later

Solaris

Operating Systems

Solaris Version 2.5.1

Solaris Version 2.6

C Compilers

SPARCompiler C Version 4.2

Windows NT

Operating Systems

Microsoft Windows NT version 4 or later

C Compilers

Microsoft Visual C++ Version 5.0 or later

從DB2通用數據Perl DBI網頁(http://www.software.ibm.com/data/db2/perl/)上可以下載最新的DBD::DB2驅動程序的最新版本以及更多信息。

五、查詢工具產品

查詢工具產品包括IBM查詢管理工具(QMF)和Lotus Notes,它們支持查詢開發和報表。

2.1.2 數據值控制

應用程序的部分邏輯是通過控制數據庫中允許的值得到實施和保護數據完整的。DB2提供了幾個不同的方法。

一、數據類型

數據庫將每一個數據元素存儲在某個表的列中,并且每一列都用一個數據類型來定義,數據類型增加了此列放置數據的限制。例如,整數類型必須是在某個固定范圍內的數字。在SQL語句中使用列也必須符合一定的行為,例如,數據庫不能將一個整數與一個字符串比較。DB2有一組內置的數據類型,定義了特性和行為。DB2支持用戶定義數據類型,叫做UDT,它們是基于內置的數據類型定義的。

二、唯一性約束

唯一性約束防止在一個表中,在一列或多列上出現重復的值。唯一主關鍵字也是唯一性約束。例如,在表DEPARTMENT的DEPTNO列上定義一個唯一性約束,防止將相同的部門號分配給兩個部門。

如果對所有使用同一個表里的數據的應用程序都要執行一個唯一性規則,應當采用唯一性約束。

三、表檢查約束

表檢查約束(Table Check Constraint)限制在表中某列出現的值的范圍。

四、參考完整性約束

通過定義唯一約束和外部關鍵字,可以定義表與表之間的關系,從而實施某些 商業規則。唯一關鍵和外部關鍵字約束的組合通常稱為參考完整性約束。 外部關鍵字所引用的唯一約束稱為父關鍵字。 外部關鍵字表示特定的父關鍵字,或與特定的父關鍵字相關。 例如,某規則可能規定每個雇員(EMPLOYEE 表)必須屬于某現存的部門 (DEPARTMENT 表)。因此,將 EMPLOYEE 表中的“部門號”定義為外部關鍵字,而將 DEPARTMENT 表中的“部門號”定義為主關鍵字。下列圖表提供參考完整性 約束的直觀說明。

圖 外部約束和主約束定義關系并保護數據

clip_image004

2.1.3 數據關系控制

應用程序邏輯的另外一個主要任務是管理系統中不同實體之間的關系。例如,如果增加一個新的部門,就要創建一個新的帳號。DB2提供了管理數據庫中不同實體之間的管理的兩種方法:參考完整性約束和觸發器。

一、參考完整性約束

我們在前面已經介紹過。

二、觸發器

一個觸發器定義一組操作,這組操作通過修改指定基表 中數據的操作來激活。

可使用觸發器來執行對輸入數據的驗證;自動生成新插入行的值; 為了交叉引用而讀取其他表;為了審查跟蹤而寫入其他表; 或通過電子郵件信息支持警報。 使用觸發器將導致應用程序開發及商業規則的全面實施更快速并且應用程序 和數據的維護更容易。

DB2 通用數據庫支持幾種類型的觸發器。 可定義觸發器在 DELETE、INSERT 或 UPDATE 操作之前或之后激活。 每個觸發器包括一組稱為觸發操作的 SQL 語句, 這組語句可包括一個可選的搜索條件。

可進一步定義后觸發器以對每一行都執行觸發操作, 或對語句執行一次觸發操作,而前觸發器總是 對每一行都執行觸發操作。

在 INSERT、UPDATE 或 DELETE 語句之前使用觸發器,以便在執行觸發操作之前 檢查某些條件,或在將輸入值存儲在表中之前更改輸入值。 使用后觸發器,以便在必要時傳播值或執行其他任務,如發送信息等,這些任務可能是觸發器操作所要求的。

2.1.4 服務器上的應用邏輯

DB2提供了將應用程序程序的部分在數據庫服務器上運行的功能,通常是為了提高性能和支持公共功能。主要方法有:存儲過程,UDF,觸發器。

2.1.5 構造SQL語句的原型

當設計和編寫應用程序時,利用數據庫管理器的特性和一些工具來構造SQL語句的原型,提高執行性能?梢园凑障旅娴姆椒▋灮疭QL語句:

1.在預編譯一個完整的程序之前,用命令行處理器(CLP)測試其中的SQL(可能不是全部)。

2.用解釋設施估算程序中的DELETE, INSERT, UPDATE, 和 SELECT語句的開銷和獲取訪問策略。根據解釋設施的輸出,改寫SQL語句或者增加數據庫對象(如索引)或者調節數據庫服務器的參數 。

2.2 設置測試環境

在進行DB2應用開發時,需要建立測試環境。一個測試環境,應該包括:

1. 一個測試數據庫. 如果應用程序要更新,插入或者刪除來自表和視圖的數據,那么使用測試數據檢查執行情況;如果僅僅從表和視圖中提取數據,可以考慮使用生產數據.

2. 測試的輸入數據. 用來測試應用程序的測試數據應該是有效的,能體現所有可能輸入情況. 也要用無效的數據去測試,看應用程序能否辨別出.

2.2.1 創建測試數據庫

可以使用CLP發出Create Database dbname語句創建測試數據庫,也可以使用數據庫管理器API編寫一個程序來創建測試數據庫.

2.2.2 創建測試表

先分析應用程序的數據需求,然后使用CREATE TABLE語句創建測試表.

2.2.3 生成測試數據

使用下面任何一種方法將數據插入表中:

l INSERT...VALUES (an SQL statement) 每次可以插入一行或多行數據

l INSERT...SELECT 從一個已存在的表提取數據 (基于一個SELECT條款),并放入INSERT語句標識的表中.

l 用IMPORT和LOAD工具 從定義的數據源插入大量的數據

l 用RESTORE工具從某個數據庫的備份,將數據還原到特定的測試數據庫

第三章 靜態SQL應用編程

靜態SQL語句,是指嵌入在宿主語言中的SQL語句在預編譯時完全知道。這是相對于動態SQL而言的,動態SQL語句的語法在運行時才知道。注意:解釋語言中不支持靜態SQL語句,例如REXX只支持動態SQL語句。

一條SQL語句的結構在預編譯時完全清楚,才被認為是靜態的。例如,語句中涉及到的表(TABLES)和列的名字,在預編譯時,必須完全知道,只能在運行時指定的是語句中引用的宿主變量的值。然而,宿主變量的信息,如數據類型,也必須在預編譯時確定。

當靜態SQL語句被準備時,SQL語句的可執行形式被創建,存儲在數據庫中的程序包里。SQL語句的可執行形式可以在預編譯時創建或者在捆綁時創建。不論哪種情況,SQL語句的準備過程都發生在運行之前。捆綁應用程序的人需要有一定的權限,數據庫管理器中的優化器還會根據數據庫的統計信息和配置參數對SQL語句進行優化。對靜態SQL語句來說,應用程序運行時,不會被優化。

3.1 靜態SQL程序的結構和特點

3.1.1 例程

下面先來看一個靜態SQL程序的C語言例子。這個例程演示了靜態SQL語句的使用,它將表中LASTNAME列等于‘JOHNSON’的記錄的FIRSTNME列的值輸出,否則打印錯誤信息。

/******************************************************************************

**

** Source File Name = static.sqc 1.4

**

** Licensed Materials - Property of IBM

**

*******************************************************************************/

#include

#include

#include

#include "util.h"

#ifdef DB268K

/* Need to include ASLM for 68K applications */

#include

#endif

EXEC SQL INCLUDE SQLCA; /* :rk.1:erk. */

#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;

int main(int argc, char *argv[]) {

EXEC SQL BEGIN DECLARE SECTION; /* :rk.2:erk. */

char firstname[13];

char userid[9];

char passwd[19];

EXEC SQL END DECLARE SECTION;

#ifdef DB268K

/* Before making any API calls for 68K environment,

need to initial the Library Manager */

InitLibraryManager(0,kCurrentZone,kNormalMemory);

atexit(CleanupLibraryManager);

#endif

printf( "Sample C program: STATIC/n" );

if (argc == 1) {

EXEC SQL CONNECT TO sample;

CHECKERR ("CONNECT TO SAMPLE");

}

else if (argc == 3) {

strcpy (userid, argv[1]);

strcpy (passwd, argv[2]);

EXEC SQL CONNECT TO sample USER :userid USING :passwd; /* :rk.3:erk. */

CHECKERR ("CONNECT TO SAMPLE");

}

else {

printf ("/nUSAGE: static [userid passwd]/n/n");

return 1;

} /* endif */

EXEC SQL SELECT FIRSTNME INTO :firstname /* :rk.4:erk. */

FROM employee

WHERE LASTNAME = 'JOHNSON';

CHECKERR ("SELECT statement"); /* :rk.5:erk. */

printf( "First name = %s/n", firstname );

EXEC SQL CONNECT RESET; /* :rk.6:erk. */

CHECKERR ("CONNECT RESET");

return 0;

}

/* end of program : static.sqc */

這個例程中實現了一個選擇至多一行(即單行)的查詢,這樣的查詢可以通過一條SELECT INTO語句來執行。SELECT INTO 語句從數據庫中的表選擇一行數據,然后將這行數據的值賦予語句中指定的宿主變量(下節將要討論宿主變量)。例如,下面的語句將姓為‘HAAS’的雇員的工資賦予宿主變量empsal:

SELECT SALARY

INTO :empsal

FROM EMPLOYEE

WHERE LASTNAME='HAAS'

一條SELECT INTO語句必須只能返回一行或者零行。如果結果集有多于一行,就會產生一個錯誤(SQLCODE –811,SQLSTATE 21000)。如果查詢的結果集中有多行,就需要游標(CURSOR)來處理這些行。在節3.2.3中介紹如何使用游標。

靜態程序是如何工作的呢?

1.包括結構SQLCA。 INCLUDE SQLCA語句定義和聲明了SQLCA結構,SQLCA結構中定義了SQLCODE和SQLSTATE域。數據庫管理器在執行完每一條SQL語句或者每一個數據庫管理器API調用,都要更新SQLCA結構中的SQLCODE域的診斷信息。

2.聲明宿主變量。SQL BEGIN DECLARE SECTION和END DECLARE SECTION 語句界定宿主變量的聲明。

有些變量在SQL語句中被引用。宿主變量用來將數據傳遞給數據庫管理或者接收數據庫管理器返回的數據。在SQL語句中引用宿主變量時,必須在宿主變量前加前綴冒號(:)。詳細信息看下節。

3.連接到數據庫。應用程序必須先連接到數據庫,才能對數據庫進行操作。這個程序連接到SAMPLE數據庫,請求以共享方式訪問。其他應用程序也可以同時以共享訪問方式連接數據庫

4.提取數據。SELECT INTO語句基于一個查詢提取了一行值。這個例子從EMPLOYEE表中,將LASTNAME列的值為JOHNSON的相應行的FISRTNME列的值提取出來,置于宿主變量 firstname中。

5.處理錯誤。CHECKERR 宏/函數是一個執行錯誤檢查的外部函數。

3.1.2 創建應用程序

創建應用程序的整個過程如圖所示:

clip_image005

3.1.3 靜態SQL的特點

靜態SQL編程比動態SQL編程簡單些. 靜態SQL語句嵌入宿主語言源文件中,預編譯器將SQL語句轉換為宿主語言編譯器能夠處理的數據庫運行時服務API調用。

因為在捆綁應用程序時,做捆綁的人需要有一定的授權,因此最終用戶不需要執行程序包里的語句的直接權限。例如,一個應用程序可以只允許某個用戶更新一個表的部分數據 ,而不用將更新整個表的權利給予這個用戶。這個功能通過限制嵌入的靜態SQL語句只能更新表中的某些列或者一定范圍內的值,只將程序包的執行權限給予這個用戶。

靜態SQL語句是持久穩固的,動態SQL語句只是被緩存,直到變為無效、因為空間管理原因被清理或者數據庫被關閉。如果需要,當被緩存的語句變為無效時,DB2 SQL編譯器隱性地重新編譯動態SQL語句。

靜態SQL語句的主要優點是靜態SQL在數據庫關閉后仍然存在,而動態SQL語句在數據庫關閉后就被清除了。另外,靜態SQL在運行時不需要DB2 SQL編譯器來編譯,相反,動態SQL語句需要在運行時編譯(例如,使用PREPARE語句)。因為DB2緩存動態SQL語句,這些語句也不總是需要DB2編譯。但是,每一次運行程序至少需要編譯一次。

靜態SQL有性能上的優勢。對簡單、運行時間短的SQL程序,靜態SQL語句 比相同目的的動態SQL語句執行得快。因為靜態SQL語句準備執行形式的開銷在預編譯時間,而不是在運行時。

注意:靜態SQL語句的性能決定于應用程序最后一次被捆綁時數據庫的統計信息。 然而,如果這些統計信息改變了,那么比較起來,等效的動態SQL語句的性能可能好些。在某個使用靜態SQL的應用程序捆綁之后,數據庫增加了一個索引,如果這個應用程序不重新捆綁,就不能利用這個索引。還有,如果在靜態SQL語句中使用宿主變量,優化器也不能使用表的分布信息來優化SQL語句。

3.2 宿主變量和指示符變量的應用

3.2.1 宿主變量的聲明

宿主變量(Host variables) 在主應用程序中由嵌入式SQL語句引用的變量。宿主變量是該應用程序中的程序設計變量,并且是在數據庫中的表與應用程序工作區之間傳送數據的主要機制。我們稱之為“宿主變量”,是為了與通常方法聲明的源語言變量區分開來,通常方法聲明的變量不能被SQL語句引用。宿主變量在宿主語言程序模塊中以一種特殊的方式聲明:必須在BEGIN DECLARE SECTION和END DECLARE SECTION程序節內定義。

下圖顯示在不同編程語言中聲明宿主變量的例子。

語言

例子源碼

C/C++

EXEC SQL BEGIN DECLARE SECTION;

short dept=38, age=26;

double salary;

char CH;

char name1[9], NAME2[9];

/* C comment */

short nul_ind;

EXEC SQL END DECLARE SECTION;

Java

// Note that Java host variable declarations follow

// normal Java variable declaration rules, and have

// no equivalent of a DECLARE SECTION

short dept=38, age=26;

double salary;

char CH;

String name1[9], NAME2[9];

/* Java comment */

short nul_ind;

COBOL

EXEC SQL BEGIN DECLARE SECTION END-EXEC.

01 age PIC S9(4) COMP-5 VALUE 26.

01 DEPT PIC S9(9) COMP-5 VALUE 38.

01 salary PIC S9(6)V9(3) COMP-3.

01 CH PIC X(1).

01 name1 PIC X(8).

01 NAME2 PIC X(8).

* COBOL comment

01 nul-ind PIC S9(4) COMP-5.

EXEC SQL END DECLARE SECTION END-EXEC.

下面是引用宿主變量的例子

語言

例子源碼

C/C++

EXEC SQL FETCH C1 INTO :cm;

printf( "Commission = %f/n", cm );

Java

#SQL { FETCH :c1 INTO :cm };

System.out.println("Commission = " + cm);

COBOL

EXEC SQL FETCH C1 INTO :cm END-EXEC

DISPLAY 'Commission = ' cm

在SQL語句中引用宿主變量時,必須加前綴—冒號(:)。冒號的作用是將宿主變量與SQL語法中的元素區分開。如果沒有冒號,宿主變量會誤解釋為SQL語句的一部分。例如:

WORKDEPT = dept

將被解釋為WORKDEPT列的值等于dept列的值。在宿主語言語句中,則不需要加前綴,正常引用即可。從下圖中可看出如何使用宿主變量:

clip_image007

DB2名字空間(如表名、列名等等)不能用宿主變量指定。例如不能寫如下SQL語句:

SELECT :col1 FROM :tabname

但是,這種類型的功能可以通過采用動態SQL實現。

總的來說,宿主變量有以下特點:

l 可選的,在語句運行之前用來賦值

l 宿主語言標號在SQL語句中,前面加冒號

l 宿主變量與列的數據類型必須匹配

l 對于宿主變量有以下要求:

a. 所有被嵌入SQL引用的宿主變量必須在BEGIN和END DECLARE語句界定的代碼區里聲明;

b. 宿主變量的數據類型必須與列的數據類型匹配,而且盡量避免數據轉換和截;

c. 宿主變量名不能以EXEC、SQL、sql開頭;

d. 宿主變量應該被看作是模塊程序的全局變量,而不是定義所在函數的局部變量;

e. 在界定區外定義的變量不能與界定區內定義的變量同名;

f. 在一個源文件中,可以有多個界定區;

g. BEGIN DECLARE SECTION語句可以在程序中宿主語言規則允許變量聲明的任何位置出現,宿主變量定義區以END DECLARE SECTION語句結束;

h. BEGIN DECLARE SECTION和END DECLARE SECTION語句必須成對出現,并且不能嵌套;

i. 宿主變量聲明可以使用SQL INCLUDE語句指定。另外,一個宿主變量聲明區不能含有除宿主變量聲明以外的語句。

3.2.2 宿主變量的使用

下面我們通過幾個例子來說明宿主變量的用法:

1.在INSERT語句中的使用

l SQL語句

INSERT INTO TEMPL (EMPNO, LASTNAME)

VALUES (‘000190’, ‘JONES’)

l 嵌入程序的SQL語句

EXEC SQL INSERT INTO TEMPL (EMPNO, LASTNAME)

VALUES (:empno, :name);

第一條SQL語句可以在CLP中發出,它也可以嵌入程序中,但是它每一次只能插入一行值,如果要插入不同的值就要重新輸入,程序也要修改。

第二條SQL語句只能嵌入程序中,每一次執行需要用戶通過其它代碼指定新值給宿主變量empno和name,宿主變量的作用是將用戶指定的值傳遞給VALUES子句?梢詫崿F輸入多行值(循環或多次運行程序)。

2.在SET和WHERE子句中的使用

l SQL語句

UPDATE TEMPL

SET SALARY = SALARY *1.05

WHERE JOBCODE = 54

l 嵌入程序的SQL語句

EXEC SQL

UPDATE TEMPL

SET SALARY = SALARY * :percent

WHERE JOBCODE = :code;

3. 用宿主變量提取值。在程序中執行一個SELECT語句時,必須提供一個存儲區域來接收返回的數據,而且對于被選擇(selected)的每一列,都要定義一個宿主變量。語法為:SELECT … INTO :hostvaribale …。例子:

EXEC SQL

SELECT LASTNAME, WORKDEPT

INTO :name, :deptno

FROM TEMPL

WHERE EMPNO = :empid;

例子中定義了三個宿主變量,從表TEMPL中選擇符合條件—EMPNO=:empid—的兩列:LASTNAME和WORKDEPT,結果存放到宿主變量name和deptno中。此形式的用法要保證只能返回單行數據,如果返回多行數據庫,則不能使用這種方法。后面會介紹如果使用游標(cursor)處理多行的返回結果集。

從上面的例子可將宿主變量分為兩類:

l 輸入宿主變量

輸入宿主變量規定需要在語句執行期間從應用程序傳遞給數據庫管理器的值。例如,在下面的SQL語句中將使用一個輸入宿主變量:

SELECT name FROM candidate

WHERE name = < input host variable >

l 輸出宿主變量

輸出宿主變量規定需要在語句執行期間從數據庫管理器傳遞給應用程序的值。例如,在下面的SQL語句中將使用一個輸出宿主變量:

SELECT INTO < output host variable > FROM candidate

WHERE name = ‘ HUTCHISON ’

3.2.3 指示符變量的聲明

在實際中,有些對象的值未知,我們用空值表示。當我們選擇數據時,如果是空值,宿主變量的內容將不會被改變,是隨機的。DB2數據庫管理器提供了一個機制去通知用戶返回數據是空值,這個機制就是指示符變量。

指示符(indicator)變量是一種特殊的宿主變量類型,它用來表示列的空值或非空值。當這些宿主變量作為輸入進入數據庫中時,應當在執行SQL語句之前由應用程序對它們設置值。當這些宿主變量作為數據庫的輸出使用時,這些指示符由應用程序定義,但由DB2更新和將它們返回。然后,在結果被返回時,應用程序應當檢查這些指示符變量的值。

看下面一條SQL語句:

SELECT COLA INTO :a:aind

其中a是宿主變量,aind是指示符變量。如果COLA列的值不為空,DB2將aind的值設置為非負(通常為0);如果COLA列的值為空,DB2將aind的值設置為負數(通常為-1);如果DB2試圖提示一個空值的存在,但是程序沒有提供指示符,將會產生錯誤,SQLCODE等于-305。

指示符變量的定義:

指示符變量的定義與宿主變量的定義方法相同,都需要在BEGIN DECLARE SECTION和END DECLARE SECTION之間定義,并且數據類型與SQL數據類型SMALLINT對應,在C語言中為SHORT類型。

例子:

CREATE TABLE TEMPL

( EMPNO CHAR(6) NOT NULL,

LASTNAME VARCHAR(2) NOT NULL,

JOBCODE CHAR(2),

WORKDEPT CHAR(3), NOT NULL,

PHONENO CHAR(10))

EXEC SQL

SELECT JOBCODE, WORKDEPT, PHONENO

INTO :jc:jci, :dpt, :pho:phoi

FROM TEMPL

WHERE EMPNO = :id;

 

EMPNO(6)

LASTNAME(20)

JOBCODE

0-99

WORKDEPT(3)

PHONENO(10)

000070

000120

000320

JOHNSON

SCOTT

MILLIGAN

54

?

?

C01

C01

C01

5137853210

8592743091

?

3.2.4 指示符變量的使用

 

指示符邏輯例子1:

EXEC SQL

SELECT PHONENO, SEX

INTO :phoneno:phoneind, :sex

FROM TEMPL

WHERE EMPNO = :eno;

if (phoneind < 0)

null_phone();

else

good_phone();

在這個例子里,DB2維護指示符變量,應用程序在SQL語句執行后,詢問指示符變量的值,調用相應的處理函數

 

指示符邏輯例子2:

if (some condition)

phoneind = -1;

else

phoneind = 0;

EXEC SQL

UPDATE TEMPL

SET NEWPHONE = :newphoneno :phoneind

WHERE EMPNO = :eno;

在這個例子里,應用程序維護指示符變量。應用程序根據條件設置指示符變量phoneind的值。如果DB2發現指示符的值為負數,那么給定行集合中的列被設置為空值,宿主變量的值被忽略;如果指示符的值為正數或者為零,宿主變量中的值被使用。

在嵌入SQL語句中可以使用關鍵字NULL。下面是不使用指示符變量的一個UPDATE語句例子:

if ( some condition)

EXEC SQL

UPDATE TEMPL

SET PHONENO = NULL

WHERE EMPNO = :eno ;

else

EXEC SQL

UPDATE TEMPL

SEST PHONENO = :newphone

WHERE EMPNO = :eno ;

但是,這種寫法有缺點:如果UPDATE語句需要修改,就要修改兩處代碼。

如何設置指示符變量:

誰維護指示符變量

SQL語句類型

宿主變量

:cd

指示符變量

:cdi

JOBCODE

DB2

SELECT/

FETCH

60

不改變

0

<0

60

NULL

應用程序

UPDATE/

INSERT

50

N/A

0

<0

50

NULL

注解:

DB2在執行SELECT和FETCH語句的過程中設置指示符變量的值,應用程序應該在執行SELECT和FETCH語句后檢查它們的值。

應用程序在執行UPDATE和INSERT語句之前設置指示符變量的值來指示DB2是否在數據庫中放置一個空值(NULL)。

上表的第一行:在一條SELECT或者FETCH語句中,如果列(JOBCODE)中的值不為空,值被設置到宿主變量(:cd)中,指示符變量(:cdi)的值為零;如果列中的值為空,指示符變量的值將為負數,宿主變量的值不改變。

上表的第一行:在一條UPDATE或者INSERT語句中,如果指示符變量(:cdi)中的值不為負數,宿主變量(:cd)中的值被放到相應的列中;如果指示符變量中的值為負數,宿主變量中的值被忽略,相應的列被設置為空(NULL)。

指示符變量在數值轉換方面的應用:

當宿主變量的數據類型與相應列的數據類型不兼容或者不能轉換時,DB2也通過指示符變量通知應用程序。

數值轉換由數據庫管理器處理,能轉換時自動完成,對程序透明;

如果列中的值不能存儲到宿主變量中時(例如,列的數據類型為DECIMAL(15),值的長度為12個數字,不能存儲到INTEGER類型的宿主變量中),指示符變量的值為-2。

指示符變量在截取方面的應用:

在SQL語句執行后,如果指示符變量的值為正數,說明發生了數據截。

—如果是時間數據類型的秒部分被截取,那么指示符變量中的值為截取的秒數

—對于其他數據類型,指示符變量表示數據庫中列的數據原始長度,通常為字節數(數據庫盡可能返回更多的數據)。

例子:

定義宿主變量和指示符變量:

EXEC SQL BEGIN DECLARE SECTION;

char too_little[5];

short iv1;

EXEC SQL END DECLARE SECTION;

表BANK_ITEMS:

ITEM#

QTY

DESCRIPTION

101

3000

PASSBOOKS

200

100

CHECKBOOKS

執行的SQL語句:

SELECT DESCRIPTION INTO :too_little:iv

FROM BANK_ITEMS WHERE ITEM# = 200

結果:

:too_little中的值為’CHECK’,:iv1中的值為10

3.3 使用游標處理多行結果集

為了使應用程序能夠提取多行結果集,SQL使用了一種機制,叫做游標(cursor)。

為了理解游標的概念,我們假設數據庫管理器創建了一個結果表(result table),里面包含有執行一條SELECT語句所提取的所有行。通過標識或指向這個表的“當前行”,游標使得結果表中的行對應用程序可用。當一個是擁游標時,應用程序可以順序從結果表中提取每一行,直到產生一個數據結束條件,這個條件就是NOT FOUND條件,SQLCA中的SQLCODE為+100(SQLSTATE為02000)。執行SELECT語句的結果集,可能有零行、一行或者更多行,決定于有多少行符合搜索的條件。

處理一個游標涉及到以下幾個步驟:

1.使用DECLARE CURSOR語句聲明一個游標

2.使用OPEN語句執行查詢和創建結果表

3.使用FETCH語句每次提取一行結果

4.使用DELETE或UPDATE語句處理行(如果需要)

5.使用CLOSE語句終止(關閉)游標

一個應用程序中,可以使用多個游標,但是每一個游標要有自己的DECLARE CURSOR,OPEN,CLOSE和FETCH語句集。

3.3.1聲明和使用游標

DECLARE CURSOR語句定義和命名游標,確定使用SELECT語句要提取的行結果集。

應用程序給游標分配一個名字。這個名字在隨后的OPEN、FETCH和CLOSE語句中都要被參考到。查詢可以是任何有效的SELECT語句。

下面例子展示了一條DECLARE語句如何與一條靜態SELECT語句關聯起來:

語言

源碼例程

C/C++

EXEC SQL DECLARE C1 CURSOR FOR

SELECT PNAME, DEPT FROM STAFF

WHERE JOB=:host_var;

Java (SQLJ)

#sql iterator cursor1(host_var data type);

#sql cursor1 = { SELECT PNAME, DEPT FROM STAFF

WHERE JOB=:host_var };

COBOL

EXEC SQL DECLARE C1 CURSOR FOR

SELECT NAME, DEPT FROM STAFF

WHERE JOB=:host-var END-EXEC.

FORTRAN

EXEC SQL DECLARE C1 CURSOR FOR

+ SELECT NAME, DEPT FROM STAFF

+ WHERE JOB=:host_var

注解:DECLARE語句的在程序中位置是任意的,但是它必須在第一次使用游標的位置之前。

3.3.2游標與工作單元的考慮

COMMIT或者ROLLBACK操作的動作隨游標的不同而不同,依賴于游標的定義。

1.只讀游標(Read Only Cursors)

如果一個游標被確定為只讀的,并且使用可重復讀隔離級(isolation level),那么系統表仍會收集和維護工作單元需要的可重復讀鎖。因此,即使只讀游標,應用程序周期性地發出COMMIT語句還是很重要的。

2.有WITH HOLD選項

如果應用程序通過發出一條COMMIT語句來完成一個工作單元,除了聲明時有WITH HOLD選項的游標,所有游標將自動地被數據庫管理器關閉。

用WITH HOLD聲明的游標維護它訪問的跨多個工作單元的資源。用WITH HOLD聲明的游標受到的影響依賴于工作單元如何結束。

如果工作單元使用一條COMMIT語句結束,已打開的定義為WITH HOLD的游標將保持打開狀態。游標的位置在結果集的下一個邏輯行之前。另外,參考用WITH HOLD定義的已準備好的語句也會被保留。緊跟COMMIT語句后面, 只有與一個某個特定游標相關聯的FETCH和CLOSE請求才有效。UPDATE WHERE CURRENT OF和DELETE WHERE CURRENT OF 語句僅僅對在同一個工作單元中提取的行有效。如果程序包在工作單元期間被重新綁定,那么所有保持的游標都會被關閉。

如果工作單元使用一條ROLLBACK語句結束,所有打開的游標被關閉,所有在工作單元中獲得的鎖被釋放,以及所有依賴于這個工作單元的已準備好的語句被刪除。

舉個例子,假設TEMPL表中有1000條記錄。要更新所有雇員的工資,應該每更新100行就要發出一條COMMIT語句。

A. 使用WITH HOLD選項聲明游標:

EXEC SQL DECLARE EMPLUPDT CURSOR WITH HOLD FOR

SELECT EMPNO, LASTNAME, PHONENO, JOBCODE, SALARY

FROM TEMPL FOR UPDATE OF SALARY

B. 打開游標,每一次從結果表中提取一行數據:

EXEC SQL OPEN EMPLUPDT

.

.

.

EXEC SQL FETCH EMPLUPDT

INTO :upd_emp, :upd_lname, :upd_tele, :upd_jobcd, :upd_wage,

C. 當想要更新或者刪除一行時,使用帶WHERE CURRENT OF選項的UPDATE或者DELETE語句。例如,要更新當前行,程序可以發出下面的語句:

EXEC SQL UPDATE TEMPL SET SALARY = :newsalary

WHERE CURRENT OF EMPLUPDT

在一條COMMIT語句發出之后,在更新其它行之前必須發出FETCH語句。

如果應用程序使用了用WITH HOLD聲明的游標或者執行了多個工作單元并且有一個用WITH HOLD聲明的游標跨工作單元處于打開狀態,那么在程序中應該加入代碼檢測和處理SQLCODE為-501(SQLSTATE為24501)的錯誤,這個錯誤由FETCH或者CLOSE語句返回。

如果應用程序的程序包由于其依賴的表被刪除而變得無效,程序包會自動被重新綁定。這種情況下,FETCH或CLOSE語句返回SQLCODE –501(SQLSTATE 24501),因為 數據庫管理器關閉游標。在此情形下,處理SQLCODE –501(SQLSTATE 24501)的方法決定于是否要從游標提取行數據。

l 如果要從游標提取行,打開游標,然后運行FETCH語句。注意,OPEN語句使得游標重定位到開始處。原來的位置信息丟失。

l 如果不準備從游標提取行,那么不要對游標發出任何SQL請求。

WITH RELEASE 選項:當應用程序用WITH RELEASE選項關閉一個游標時,DB2試圖去釋放游標持有的所有讀鎖(READ locks)。游標只繼續持有寫鎖(WRITE locks)。如果應用程序沒有用RELEASE選項關閉游標,那么在工作單元完成時,所有的讀鎖和寫鎖都被釋放。

3.3.3例程

游標程序

C Example: CURSOR.SQC

#include

#include

#include

#include "util.h"

#ifdef DB268K

/* Need to include ASLM for 68K applications */

#include

#endif

EXEC SQL INCLUDE SQLCA;

#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;

int main(int argc, char *argv[]) {

EXEC SQL BEGIN DECLARE SECTION;

char pname[10];

short dept;

char userid[9];

char passwd[19];

EXEC SQL END DECLARE SECTION;

#ifdef DB268K

/* Before making any API calls for 68K environment,

need to initial the Library Manager */

InitLibraryManager(0,kCurrentZone,kNormalMemory);

atexit(CleanupLibraryManager);

#endif

printf( "Sample C program: CURSOR /n" );

if (argc == 1) {

EXEC SQL CONNECT TO sample;

CHECKERR ("CONNECT TO SAMPLE");

}

else if (argc == 3) {

strcpy (userid, argv[1]);

strcpy (passwd, argv[2]);

EXEC SQL CONNECT TO sample USER :userid USING :passwd;

CHECKERR ("CONNECT TO SAMPLE");

}

else {

printf ("/nUSAGE: cursor [userid passwd]/n/n");

return 1;

} /* endif */

EXEC SQL DECLARE c1 CURSOR FOR (1)

SELECT name, dept FROM staff WHERE job='Mgr'

FOR UPDATE OF job;

EXEC SQL OPEN c1; (2)

CHECKERR ("OPEN CURSOR");

do {

EXEC SQL FETCH c1 INTO :pname, :dept; (3)

if (SQLCODE != 0) break;

printf( "%-10.10s in dept. %2d will be demoted to Clerk/n",

pname, dept );

} while ( 1 );

EXEC SQL CLOSE c1; (4)

CHECKERR ("CLOSE CURSOR");

EXEC SQL ROLLBACK;

CHECKERR ("ROLLBACK");

printf( "/nOn second thought -- changes rolled back./n" );

EXEC SQL CONNECT RESET;

CHECKERR ("CONNECT RESET");

return 0;

}

/* end of program : CURSOR.SQC */

Java Example: Cursor.sqlj

import java.sql.*;

import sqlj.runtime.*;

import sqlj.runtime.ref.*;

#sql iterator CursorByName(String name, short dept) ;

#sql iterator CursorByPos(String, short ) ;

class Cursor

{ static

{ try

{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();

}

catch (Exception e)

{ System.out.println ("/n Error loading DB2 Driver.../n");

System.out.println (e);

System.exit(1);

}

}

public static void main(String argv[])

{ try

{ System.out.println (" Java Cursor Sample");

String url = "jdbc:db2:sample";

// URL is jdbc:db2:dbname

Connection con = null;

// Set the connection

if (argv.length == 0)

{ // connect with default id/password

con = DriverManager.getConnection(url);

}

else if (argv.length == 2)

{ String userid = argv[0];

String passwd = argv[1];

// connect with user-provided username and password

con = DriverManager.getConnection(url, userid, passwd);

}

else

{ throw new Exception("Usage: java Cursor [username password]");

}

// Set the default context

DefaultContext ctx = new DefaultContext(con);

DefaultContext.setDefaultContext(ctx);

// Enable transactions

con.setAutoCommit(false);

// Using cursors

try

{ CursorByName cursorByName;

CursorByPos cursorByPos;

String name = null;

short dept=0;

// Using the JDBC ResultSet cursor method

System.out.println("/nUsing the JDBC ResultSet cursor method");

System.out.println(" with a 'bind by name' cursor .../n");

#sql cursorByName = {

SELECT name, dept FROM staff

WHERE job='Mgr' FOR UPDATE OF job }; (1)

while (cursorByName.next()) (2)

{ name = cursorByName.name(); (3)

dept = cursorByName.dept();

System.out.print (" name= " + name);

System.out.print (" dept= " + dept);

System.out.print ("/n");

}

cursorByName.close(); (4)

// Using the SQLJ iterator cursor method

System.out.println("/nUsing the SQLJ iterator cursor method");

System.out.println(" with a 'bind by position' cursor .../n");

#sql cursorByPos = {

SELECT name, dept FROM staff

WHERE job='Mgr' FOR UPDATE OF job }; (1) (2)

while (true)

{ #sql { FETCH :cursorByPos INTO :name, :dept }; (3)

if (cursorByPos.endFetch()) break;

System.out.print (" name= " + name);

System.out.print (" dept= " + dept);

System.out.print ("/n");

}

cursorByPos.close(); (4)

}

catch( Exception e )

{ throw e;

}

finally

{ // Rollback the transaction

System.out.println("/nRollback the transaction...");

#sql { ROLLBACK };

System.out.println("Rollback done.");

}

}

catch( Exception e )

{ System.out.println (e);

}

}

}

游標程序如果工作:

1.聲明游標。DECLARE CURSOR語句將游標c1與一個查詢關聯起來。查詢確定應用程序使用FETCH語句提取的行。表staff的job字段被定義為可更新的,即使它在結果表中。

2.打開游標。游標c1被打開,數據庫管理器執行查詢并創建結果表。游標的指向位于第一行的前面。

3.提取一行。FETCH語句使游標指向下一行,將那行的內容移動到宿主變量中。那行成為當前行。

4. 關閉游標。發出CLOSE語句,釋放與游標相關聯的資源。游標仍然可以再次打開。

3.3.4更新和刪除提取的數據

可以更新和刪除一個游標指向的行。要使行能被更新,對應于游標的查詢不能是只讀的。

更新提取的數據

為了利用游標來更新數據,在UPDATE語句中使用WHERE CURRENT OF子句。使用FOR UPDATE子句告訴系統結果表中的哪些列要更新。也可以在FOR UPDATE中指出不在選擇范圍之內的列,從另一個角度講,可以更新不被游標提取的列。如果FOR UPDATE子句中沒有指定任何列名,那么在第一個FROM子句中標識的表或者視圖的所有列都被看作是可更新的。性能上要求只在FOR UPDATE子句里指定要更新的列,如果指定不需要更新的列,DB2將浪費沒有必要的開銷去訪問數據。

刪除提取的數據

為了利用游標來刪除數據,在DELETE語句中使用WHERE CURRENT OF條款。通常上,為了刪除游標的當前行,定義游標時,不需要FOR UPDATE條款。一個例外情況是,在應用程序中使用動態的SELECT語句或者DELETE語句,預編譯時LANGLEVEL選項設為SAA1,捆綁時使用BLOCKING ALL選項。這種情況下,SELECT語句需要FOR UPDATE條款。(詳細信息請看3.3節開發動態SQL應用程序)

DELETE語句使得游標指示的那行被刪除。游標的位置指向下一行的前面。要進行下一個WHERE CURRENT OF操作之前,必須對游標發出一條FETCH語句。

游標的類型

游標可以分為三類:

1.只讀類型

游標指向的行只能夠讀,不能夠更新。應用程序只是讀取數據不修改時,使用只讀游標。如果游標是基于只讀SELECT語句,那么它便是只讀的。只讀游標在性能方面較好。

2.可更新類型

游標指向的行可以更新。應用程序需要修改提取的數據時,使用可更新游標。指定的查詢只能涉及到一個表或視圖。查詢必須包含FOT UPDATE子句,并寫出每一個要更新的列(除非預編譯時使用LANGLEVEL MIA選項)。

3.不明確類型

從游標的定義或者上下文來確定游標是可更新的還是只讀的。

當預編譯或綁定時使用BLOCKING ALL選項,不明確的游標被看作是只讀的。否則,被看作是可更新的。

注意:動態處理的游標通常是不明確類型的。

例子 OPENFTCH程序

這個例子使用一個游標從一個表中選擇行,打開游標,從表中提取行。對提取的每一行,判斷是否要刪除或者更新(根據一個簡單的條件)。這個例子有以下語言的版本:

C語言:openftch.sqc

Java語言:Openftch.sqlj and OpF_Curs.sqlj

COBOL語言:openftch.sqb

FORTRAN 語言:openftch.sqf

C Example: OPENFTCH.SQC

#include

#include

#include

#include "util.h"

#ifdef DB268K

/* Need to include ASLM for 68K applications */

#include

#endif

EXEC SQL INCLUDE SQLCA;

#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;

int main(int argc, char *argv[]) {

EXEC SQL BEGIN DECLARE SECTION;

char pname[10];

short dept;

char userid[9];

char passwd[19];

EXEC SQL END DECLARE SECTION;

#ifdef DB268K

/* Before making any API calls for 68K environment,

need to initial the Library Manager */

InitLibraryManager(0,kCurrentZone,kNormalMemory);

atexit(CleanupLibraryManager);

#endif

printf( "Sample C program: OPENFTCH/n" );

if (argc == 1) {

EXEC SQL CONNECT TO sample;

CHECKERR ("CONNECT TO SAMPLE");

}

else if (argc == 3) {

strcpy (userid, argv[1]);

strcpy (passwd, argv[2]);

EXEC SQL CONNECT TO sample USER :userid USING :passwd;

CHECKERR ("CONNECT TO SAMPLE");

}

else {

printf ("/nUSAGE: openftch [userid passwd]/n/n");

return 1;

} /* endif */

EXEC SQL DECLARE c1 CURSOR FOR (1)

SELECT name, dept FROM staff WHERE job='Mgr'

FOR UPDATE OF job;

EXEC SQL OPEN c1; (2)

CHECKERR ("OPEN CURSOR");

do {

EXEC SQL FETCH c1 INTO :pname, :dept; (3)

if (SQLCODE != 0) break;

if (dept > 40) {

printf( "%-10.10s in dept. %2d will be demoted to Clerk/n",

pname, dept );

EXEC SQL UPDATE staff SET job = 'Clerk' (4)

WHERE CURRENT OF c1;

CHECKERR ("UPDATE STAFF");

} else {

printf ("%-10.10s in dept. %2d will be DELETED!/n",

pname, dept);

EXEC SQL DELETE FROM staff WHERE CURRENT OF c1;

CHECKERR ("DELETE");

} /* endif */

} while ( 1 );

EXEC SQL CLOSE c1; (5)

CHECKERR ("CLOSE CURSOR");

EXEC SQL ROLLBACK;

CHECKERR ("ROLLBACK");

printf( "/nOn second thought -- changes rolled back./n" );

EXEC SQL CONNECT RESET;

CHECKERR ("CONNECT RESET");

return 0;

}

/* end of program : OPENFTCH.SQC */

Java Example: Openftch.sqlj

OpF_Curs.sqlj

// PURPOSE : This file, named OpF_Curs.sqlj, contains the definition

// of the class OpF_Curs used in the sample program Openftch.

import sqlj.runtime.ForUpdate;

#sql public iterator OpF_Curs implements ForUpdate (String, short);

Openftch.sqlj

import java.sql.*;

import sqlj.runtime.*;

import sqlj.runtime.ref.*;

class Openftch

{ static

{ try

{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();

}

catch (Exception e)

{ System.out.println ("/n Error loading DB2 Driver.../n");

System.out.println (e);

System.exit(1);

}

}

public static void main(String argv[])

{ try

{ System.out.println (" Java Openftch Sample");

String url = "jdbc:db2:sample"; // URL is jdbc:db2:dbname

Connection con = null;

// Set the connection

if (argv.length == 0)

{ // connect with default id/password

con = DriverManager.getConnection(url);

}

else if (argv.length == 2)

{ String userid = argv[0];

String passwd = argv[1];

// connect with user-provided username and password

con = DriverManager.getConnection(url, userid, passwd);

}

else

{ throw new Exception(

"/nUsage: java Openftch [username password]/n");

} // if - else if - else

// Set the default context

DefaultContext ctx = new DefaultContext(con);

DefaultContext.setDefaultContext(ctx);

// Enable transactions

con.setAutoCommit(false);

// Executing SQLJ positioned update/delete statements.

try

{ OpF_Curs forUpdateCursor;

String name = null;

short dept=0;

#sql forUpdateCursor =

{ SELECT name, dept

FROM staff

WHERE job='Mgr'

FOR UPDATE OF job

}; // #sql (1)(2)

while (true)

{ #sql

{ FETCH :forUpdateCursor

INTO :name, :dept

}; // #sql (3)

if (forUpdateCursor.endFetch()) break;

if (dept > 40)

{ System.out.println (

name + " in dept. "

+ dept + " will be demoted to Clerk");

#sql

{ UPDATE staff SET job = 'Clerk'

WHERE CURRENT OF :forUpdateCursor

}; // #sql (4)

}

else

{ System.out.println (

name + " in dept. " + dept

+ " will be DELETED!");

#sql

{ DELETE FROM staff

WHERE CURRENT OF :forUpdateCursor

}; // #sql

} // if - else

}

forUpdateCursor.close(); (5)

}

catch( Exception e )

{ throw e; }

finally

{ // Rollback the transaction

System.out.println("/nRollback the transaction...");

#sql { ROLLBACK };

System.out.println("Rollback done.");

} // try - catch - finally

}

catch( Exception e )

{ System.out.println (e); } // try - catch

} // main

} // class Openftch

OPENFTCH程序是如何工作的:

1.聲明游標。DECLARE CURSOR語句將游標c1與一個查詢關聯起來。查詢確定應用程序使用FETCH語句提取的行。表staff的job字段被定義為可更新的,即使它不在結果表中。

2.打開游標。游標c1被打開,導致數據庫管理器執行查詢并創建一個結果表。游標的位置位于第一行的前面。

3.提取一行。FETCH語句將游標指向下一行,并將行的內容移到宿主變量中。那行成為當前行。

4.更新或刪除當前行。當前行被更新或者被刪除,決定于FETCH語句返回dept的值。

如果一個UPDATE語句被執行,游標的位置保持不變,因為UPDATE語句不改變當前行的位置。

如果一個DELETE語句被執行,游標的位置位于下一行的前面,因為當前行已被刪除。在進行另外的WHERE CURRENT OF操作之前,必須執行一條FETCH語句。

5.關閉游標。CLOSE語句被發出,釋放與游標關聯的資源。游標可以被再次打開。

3.4 診斷信息處理與SQLCA結構

3.4.1 SQLCA結構

SQLCA的全稱為SQL通信區(Communication Area),是應用程序用來接收數據庫管理返回的SQL執行情況信息的數據結構。

下面是SQLCA的C語言定義:

SQL_STRUCTURE sqlca

{

_SQLOLDCHAR sqlcaid[8]; /* Eyecatcher = 'SQLCA ' */

sqlint32 sqlcabc; /* SQLCA size in bytes = 136 */

sqlint32 sqlcode; /* SQL return code */

short sqlerrml; /* Length for SQLERRMC */

_SQLOLDCHAR sqlerrmc[70]; /* Error message tokens */

_SQLOLDCHAR sqlerrp[8]; /* Diagnostic information */

sqlint32 sqlerrd[6]; /* Diagnostic information */

_SQLOLDCHAR sqlwarn[11]; /* Warning flags */

_SQLOLDCHAR sqlstate[5]; /* State corresponding to SQLCODE */

};

下表是SQLCA數據結構的詳細描述:

元素名

   

sqlcaid

sqlcabc

sqlcode

sqlerrml

sqlerrmc

sqlerrp

sqlerrd

sqlwarn

sqlstate

CHAR(8)

INTEGER

INTEGER

SMAIIINT

VARCHAR(70)

CHAR(8)

INTEGER ARRAY

CHARACTER ARRAY

CHAR(5)

有助于從外觀上識別該數據的視覺—捕捉器。它應當包含字符串‘SQLCA’

包含SQLCA的長度。它應當總是包含值136

這可能是SQLCA結構的最重要元素。如果SQL被成功地處理,這個值包含錯誤代碼或者是零值。如果該值是正數,那么返回警告信息并且處理SQL語句。如果該值是負數,那么有錯誤信息發生并且不處理SQL語句。如果該值是零(0),那么沒有出現錯誤和警告信息并且處理了SQL語句

包含在元素sqlerrmc中字符串的長度

包含用X’FF’間隔的一個到多個信息

延伸閱讀:

Tag標簽: DB2操作  
  • 專題推薦

About IT165 - 廣告服務 - 隱私聲明 - 版權申明 - 免責條款 - 網站地圖 - 網友投稿 - 聯系方式
本站內容來自于互聯網,僅供用于網絡技術學習,學習中請遵循相關法律法規
彩乐乐11选5 j5g| spq| 5qd| uw5| rmv| o5y| d5k| wqd| 66c| clf| 4jp| cy4| yfb| p4u| zdj| 4hd| yp4| gxc| o5x| f5g| kro| 5pb| kq3| rhn| k3f| ons| 3ao| om4| cko| o4t| jdr| 4ec| 4yv| xn2| fne| y2v| bsg| d3f| ihv| 3mr| ck3| kkq| x3i| wvi| 3nr| hxl| ok2| fhm| g2e| lkq| 2ud| bi2| xfl| m2h| txc| 3wk| we3| xpv| yfk| l1d| mka| 1xu| kb1| vkg| y2y| fyl| k2y| ksx| 2hd| rc0| uhw| vuz| m0v| qyq| 1cq| gw1| sro| m1n| hpy| 1hd| el9| rqf| w9f| pft| 0wb| 0ws| dq0| gnt| i0b| zyd|