この節では、高度なスクロール技法に関する以下のトピックについて説明します。
アプリケーションがデータベースからデータを検索するとき、 FETCH ステートメントを使うとデータを下方へスクロールすることができます。しかし、データベース・マネージャーの組み込み SQL ステートメントにはデータを上方へスクロールする機能 (上方 FETCH 機能に相当) がありません。一方、DB2 CLI と Java では読み取り専用のスクロール可能カーソルによる上方 FETCH 機能をサポートしています。スクロール可能カーソルに関する詳細については、 コール・レベル・インターフェースの手引きおよび解説書 および Java アプリケーションおよびアプレットの作成を参照してください。組み込み SQL アプリケーションの場合、すでに検索されたデータをスクロールするには以下の技法を使うことができます。
これらのオプションについて、以下の節で詳しく説明します。
アプリケーションは、取り出されたデータを仮想記憶域に保管することができます。そのデータが仮想記憶域に入りきらない場合、アプリケーションはそのデータを一時ファイルに書き込むことができます。この方法の利点は、データベース内のデータがトランザクションによる一時的な変更を受けた場合でさえも、ユーザーは、取り出されたデータとまったく同じものを、上方スクロールによって常に見ることができるという点です。
反復可能読み取りの分離レベルを使用すると、カーソルをクローズしたりオープンすることにより、トランザクションから検索したデータをもう一度検索することができます。検索結果のデータは、他のアプリケーションにより更新されることはありません。データの更新方法は、分離レベルおよびロックにより左右されます。
この技法は、データをもう一度見ようとする順序により異なります。
データを先頭から再び検索するには、活動カーソルをクローズしてそれを再オープンするだけで済みます。このアクションによってカーソルは結果表の先頭に置かれます。ただし、アプリケーションがその表に対してロックを保持していない限り、他のユーザーがその表に変更を加える可能性があるので、以前に結果表の最初の行であったものが、最初の行でなくなるということもあり得ます。
結果表の中ほどから 2 度目のデータ検索を行うには、 2 度目の SELECT ステートメントを実行し、そのステートメント上で 2 つ目のカーソルを宣言してください。たとえば、最初の SELECT ステートメントが次のものであるとします。
SELECT * FROM DEPARTMENT WHERE LOCATION = 'CALIFORNIA' ORDER BY DEPTNO
今度は DEPTNO = 'M95' から始まる行に戻って、その場所から順番に行を取り出すとします。この場合は、次のようにコーディングしてください。
SELECT * FROM DEPARTMENT WHERE LOCATION = 'CALIFORNIA' AND DEPTNO >= 'M95' ORDER BY DEPTNO
このステートメントによって、カーソルは希望する場所に置かれます。
2 番目の結果表の行は、最初の結果表と同じ順序で表示されるとは限りません。データベース・マネージャーは、SELECT ステートメントが ORDER BY 機能を使用していない場合、行の順序を重要視しません。そのため、同じ DEPTNO 値を持つ行がいくつかある場合には、 2 番目の SELECT ステートメントが最初のものとは違う順序で行を検索する場合があります。保証されているのは、ORDER BY DEPTNO 文節での要求に従って、それらすべてが部門番号の順に並べられるということだけです。
同じ SQL ステートメントを同じホスト変数を指定して 2 度実行したとしても、順序付けが異なる場合があります。たとえば、2 度目の実行がなされるまでの間にカタログの統計が更新されたり、索引が作成されるか除去される場合もあります。その後で SELECT ステートメントをもう一度実行することも考えられます。
最初の SELECT が持っていなかった述部を 2 番目の SELECT が持っている場合、配列が変更することがあります。それはデータベース・マネージャーが新しい述部に対して索引を使用するということがあり得るからです。たとえば、この例で、データベース・マネージャーが最初のステートメントに対しては LOCATION 上の索引を選び、 2 番目のものに対しては DEPTNO 上の索引を選ぶ場合があります。行は索引キーの順に従って取り出されるため、 2 番目の順序は最初の順序と同じとは限りません。
この場合も、2 つの同様な SELECT ステートメントを実行したときに、統計が変更されず、索引の作成も除去も行われなかったとしても、行の順序が異なる場合があります。例では、LOCATION の異なる値が多数ある場合、データベース・マネージャーは両方のステートメント用に LOCATION 上で 1 つの索引を選択することができます。しかし、2 番目のステートメントの DEPTNO の値を次のように変えると、データベース・マネージャーは DEPTNO 上の索引を選ぶことがあります。
SELECT * FROM DEPARTMENT WHERE LOCATION = 'CALIFORNIA' AND DEPTNO >= 'Z98' ORDER BY DEPTNO
SQL ステートメントの形式とこのステートメントの値との間にはわずかな関係しかないため、順序が ORDER BY 文節で固有のものとして定められているのでない限り、 2 つの異なった SQL ステートメントが同じ順序で行を戻してくるとは考えないでください。
行の昇順が省略時の設定です。 DEPTNO のおのおのの値に対する行が 1 つしかない場合、次のステートメントは行を固有の昇順に指定します。
SELECT * FROM DEPARTMENT WHERE LOCATION = 'CALIFORNIA' ORDER BY DEPTNO
同じ行を逆順に検索するには、次のステートメントのように順序を降順として指定してください。
SELECT * FROM DEPARTMENT WHERE LOCATION = 'CALIFORNIA' ORDER BY DEPTNO DESC
2 番目のステートメント上のカーソルは、最初のステートメント上のカーソルからの順番とはまったく逆の順番に行を検索します。検索の順序は、最初のステートメントが固有の順序を指定している場合にのみ保証されます。
行を逆順で検索する場合、DEPTNO 列に、 1 つは昇順で、もう 1 つは降順の 2 つの索引を持つと便利です。
データベース・マネージャーは表内に保管されているデータを配列することは行いません。そのため、表の末尾は定義されていません。しかし、SQL ステートメントの結果としては順序が定義されます。
SELECT * FROM DEPARTMENT ORDER BY DEPTNO DESC
この例の場合、次のステートメントは DEPTNO の値が最も高い行にカーソルを位置付けします。
SELECT * FROM DEPARTMENT WHERE DEPTNO = (SELECT MAX(DEPTNO) FROM DEPARTMENT)
ただし、同じ値を持つ行がいくつかある場合には、カーソルはそれらのうちの最初の行に置かれることに注意してください。
上方にスクロールして以前に検索されたデータを更新するには、 すでに検索済みデータのスクロールおよび 検索されたデータの更新で説明する技法を組み合わせて使用することができます。以下の 2 つの技法のいずれかを行うことができます。
UPDAT プログラムは動的 SQL を使用して、SAMPLE データベース内の STAFF 表にアクセスし、すべてのマネージャーを行員に変更します。それから、直前の作業単位をロールバックして、その変更を元に戻します。このサンプルは、以下のプログラミング言語で入手可能です。
Java アプリケーションは、 SQLException オブジェクトに対して定義された方式によって SQLCODE および SQLSTATE にアクセスするので、同等の "include SQLCA" ステートメントは必要ありません。
REXX アプリケーションには 1 つの SQLCA 構造のオカレンスがあります。これは SQLCA という名前で、アプリケーションが使用するために事前定義されているものです。アプリケーションの定義がなくてもこれを参照することができます。
Java および REXX アプリケーションは、ホスト変数を宣言する必要はありません。ただし (REXX の場合)、LOB ファイル参照変数およびロケーターは別です。ホスト変数のデータ・タイプおよびサイズは、変数の参照の実行時に決定されます。
CHECKERR マクロ / 関数は、プログラム外部にあるエラー検査ユーティリティーです。エラー検査ユーティリティーの所在は、ご使用のプログラミング言語により異なります。
このエラー検査ユーティリティーのソース・コードについては、 プログラム例での GET ERROR MESSAGE の使用を参照してください。
#include <stdio.h> #include <string.h> #include <stdlib.h> #include <sqlenv.h> #include "utilemb.h" EXEC SQL INCLUDE SQLCA; (1) int main(int argc, char *argv[]) { EXEC SQL BEGIN DECLARE SECTION; (2) char statement[256]; char userid[9]; char passwd[19]; char jobUpdate[6]; EXEC SQL END DECLARE SECTION; printf( "\nSample C program: UPDAT \n"); if (argc == 1) { EXEC SQL CONNECT TO sample; EMB_SQL_CHECK("CONNECT TO SAMPLE"); } else if (argc == 3) { strcpy (userid, argv[1]); strcpy (passwd, argv[2]); EXEC SQL CONNECT TO sample USER :userid USING :passwd; (3) EMB_SQL_CHECK("CONNECT TO SAMPLE"); } else { printf ("\nUSAGE: updat [userid passwd]\n\n"); return 1; } /* endif */ strcpy (jobUpdate, "Clerk"); EXEC SQL UPDATE staff SET job = :jobUpdate WHERE job = 'Mgr'; (4) EMB_SQL_CHECK("UPDATE STAFF"); printf ("All 'Mgr' have been demoted to 'Clerk'!\n" ); strcpy (jobUpdate, "Sales"); EXEC SQL DELETE FROM staff WHERE job = :jobUpdate; (5) EMB_SQL_CHECK("DELETE FROM STAFF"); printf ("All 'Sales' people have been deleted!\n"); EXEC SQL INSERT INTO staff VALUES (999, 'Testing', 99, :jobUpdate, 0, 0, 0); (6) EMB_SQL_CHECK("INSERT INTO STAFF"); printf ("New data has been inserted\n"); EXEC SQL ROLLBACK; (7) EMB_SQL_CHECK("ROLLBACK"); printf( "On second thought -- changes rolled back.\n" ); EXEC SQL CONNECT RESET; EMB_SQL_CHECK("CONNECT RESET"); return 0; } /* end of program : UPDAT.SQC */
import java.sql.*; import sqlj.runtime.*; import sqlj.runtime.ref.*; class Updat { 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 ("\n Java Updat Sample"); String url = "jdbc:db2:sample"; // URL is jdbc:db2:dbname Connection con = null; // Set the connection (3) 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 Updat [username password]\n"); } // Set the default context DefaultContext ctx = new DefaultContext(con); DefaultContext.setDefaultContext(ctx); // Enable transactions con.setAutoCommit(false); // UPDATE/DELETE/INSERT try { String jobUpdate = null; jobUpdate="Clerk"; #sql {UPDATE staff SET job = :jobUpdate WHERE job = 'Mgr'}; (4) System.out.println("\nAll 'Mgr' have been demoted to 'Clerk'!"); jobUpdate="Sales"; #sql {DELETE FROM staff WHERE job = :jobUpdate}; System.out.println("All 'Sales' people have been deleted!"); (5) #sql {INSERT INTO staff VALUES (999, 'Testing', 99, :jobUpdate, 0, 0, 0)}; (6) System.out.println("New data has been inserted"); } catch( Exception e ) { throw e; } finally { // Rollback the transaction System.out.println("\nRollback the transaction..."); #sql { ROLLBACK }; (7) System.out.println("Rollback done."); } } catch (Exception e) { System.out.println (e); } } }
Identification Division. Program-ID. "updat". Data Division. Working-Storage Section. copy "sql.cbl". copy "sqlenv.cbl". copy "sqlca.cbl". (1) EXEC SQL BEGIN DECLARE SECTION END-EXEC. (2) 01 statement pic x(80). 01 userid pic x(8). 01 passwd. 49 passwd-length pic s9(4) comp-5 value 0. 49 passwd-name pic x(18). 01 job-update pic x(5). EXEC SQL END DECLARE SECTION END-EXEC. * Local variables 77 errloc pic x(80). 77 error-rc pic s9(9) comp-5. 77 state-rc pic s9(9) comp-5. * Variables for the GET ERROR MESSAGE API * Use application specific bound instead of BUFFER-SZ 77 buffer-size pic s9(4) comp-5 value 1024. 77 line-width pic s9(4) comp-5 value 80. 77 error-buffer pic x(1024). 77 state-buffer pic x(1024). Procedure Division. Main Section. display "Sample COBOL program: UPDAT". display "Enter your user id (default none): " with no advancing. accept userid. if userid = spaces EXEC SQL CONNECT TO sample END-EXEC else display "Enter your password : " with no advancing accept passwd-name. * Passwords in a CONNECT statement must be entered in a VARCHAR format * with the length of the input string. inspect passwd-name tallying passwd-length for characters before initial " ". EXEC SQL CONNECT TO sample USER :userid USING :passwd (3) END-EXEC. move "CONNECT TO" to errloc. call "checkerr" using SQLCA errloc. move "Clerk" to job-update. EXEC SQL UPDATE staff SET job=:job-update (4) WHERE job='Mgr' END-EXEC. move "UPDATE STAFF" to errloc. call "checkerr" using SQLCA errloc. display "All 'Mgr' have been demoted to 'Clerk'!". move "Sales" to job-update. EXEC SQL DELETE FROM staff WHERE job=:job-update END-EXEC. (5) move "DELETE FROM STAFF" to errloc. call "checkerr" using SQLCA errloc. display "All 'Sales' people have been deleted!". EXEC SQL INSERT INTO staff VALUES (999, 'Testing', 99, (6) :job-update, 0, 0, 0) END-EXEC. move "INSERT INTO STAFF" to errloc. call "checkerr" using SQLCA errloc. display "New data has been inserted". EXEC SQL ROLLBACK END-EXEC. (7) move "ROLLBACK" to errloc. call "checkerr" using SQLCA errloc. DISPLAY "On second thought -- changes rolled back." EXEC SQL CONNECT RESET END-EXEC. move "CONNECT RESET" to errloc. call "checkerr" using SQLCA errloc. End-Prog. stop run.
注: | REXX プログラムは静的 SQL を使用できません。このプログラムは動的 SQL で記述されています。 |
/* REXX program UPDAT.CMD */ parse version rexxType . parse source platform . if platform == 'AIX/6000' & rexxType == 'REXXSAA' then do rcy = SysAddFuncPkg("db2rexx") end else do if RxFuncQuery('SQLDBS') <> 0 then rcy = RxFuncAdd( 'SQLDBS', 'db2ar', 'SQLDBS' ) if RxFuncQuery('SQLEXEC') <> 0 then rcy = RxFuncAdd( 'SQLEXEC', 'db2ar', 'SQLEXEC' ) end /* pull in command line arguments */ parse arg userid passwd . /* check to see if the proper number of arguments have been passed in */ PARSE ARG dbname userid password . if ((dbname = "" ) | , (userid <> "" & password = "") , ) then do SAY "USAGE: updat.cmd <dbname> [<userid> <password>]" exit -1 end /* connect to database */ SAY SAY 'Connect to' dbname IF password= "" THEN CALL SQLEXEC 'CONNECT TO' dbname ELSE CALL SQLEXEC 'CONNECT TO' dbname 'USER' userid 'USING' password CALL CHECKERR 'Connect to ' SAY "Connected" say 'Sample REXX program: UPDAT.CMD' jobupdate = "'Clerk'" st = "UPDATE staff SET job =" jobupdate "WHERE job = 'Mgr'" call SQLEXEC 'EXECUTE IMMEDIATE :st' (4) call CHECKERR 'UPDATE' say "All 'Mgr' have been demoted to 'Clerk'!" jobupdate = "'Sales'" st = "DELETE FROM staff WHERE job =" jobupdate call SQLEXEC 'EXECUTE IMMEDIATE :st' (5) call CHECKERR 'DELETE' say "All 'Sales' people have been deleted!" st = "INSERT INTO staff VALUES (999, 'Testing', 99," jobupdate ", 0, 0, 0)" call SQLEXEC 'EXECUTE IMMEDIATE :st' (6) call CHECKERR 'INSERT' say 'New data has been inserted' call SQLEXEC 'ROLLBACK' (7) call CHECKERR 'ROLLBACK' say 'On second thought...changes rolled back.' call SQLEXEC 'CONNECT RESET' call CHECKERR 'CONNECT RESET' CHECKERR: arg errloc if ( SQLCA.SQLCODE = 0 ) then return 0 else do say '--- error report ---' say 'ERROR occurred :' errloc say 'SQLCODE :' SQLCA.SQLCODE /******************************\ * GET ERROR MESSAGE API called * \******************************/ call SQLDBS 'GET MESSAGE INTO :errmsg LINEWIDTH 80' say errmsg say '--- end error report ---' if (SQLCA.SQLCODE < 0 ) then exit else do say 'WARNING - CONTINUING PROGRAM WITH ERRORS' return 0 end end return 0