The following topics on advanced scrolling techniques are discussed in this section:
When an application retrieves data from the database, the FETCH statement allows it to scroll forward through the data, however, the database manager has no embedded SQL statement that allows it scroll backwards through the data, (equivalent to a backward FETCH). DB2 CLI and Java, however, do support a backward FETCH through read-only scrollable cursors. Refer to the CLI Guide and Reference and see Creating Java Applications and Applets for more information on scrollable cursors. For embedded SQL applications, you can use the following techniques to scroll through data that has been retrieved:
These options are discussed in more detail in:
An application can save fetched data in virtual storage. If the data does not fit in virtual storage, the application can write the data to a temporary file. One effect of this approach is that a user, scrolling backward, always sees exactly the same data that was fetched, even if the data in the database was changed in the interim by a transaction.
Using an isolation level of repeatable read, the data you retrieve from a transaction can be retrieved again by closing and opening a cursor. Other applications are prevented from updating the data in your result set. Isolation levels and locking can affect how users update data.
This technique depends on the order in which you want to see the data again:
To retrieve the data again from the beginning, merely close the active cursor and reopen it. This action positions the cursor at the beginning of the result table. But, unless the application holds locks on the table, others may have changed it, so what had been the first row of the result table may no longer be.
To retrieve data a second time from somewhere in the middle of the result table, execute a second SELECT statement and declare a second cursor on the statement. For example, suppose the first SELECT statement was:
SELECT * FROM DEPARTMENT
WHERE LOCATION = 'CALIFORNIA'
ORDER BY DEPTNO
Now, suppose that you want to return to the rows that start with DEPTNO = 'M95' and fetch sequentially from that point. Code the following:
SELECT * FROM DEPARTMENT
WHERE LOCATION = 'CALIFORNIA'
AND DEPTNO >= 'M95'
ORDER BY DEPTNO
This statement positions the cursor where you want it.
The rows of the second result table may not be displayed in the same order as in the first. The database manager does not consider the order of rows as significant unless the SELECT statement uses ORDER BY. Thus, if there are several rows with the same DEPTNO value, the second SELECT statement may retrieve them in a different order from the first. The only guarantee is that they will all be in order by department number, as demanded by the clause ORDER BY DEPTNO.
The difference in ordering could occur even if you were to execute the same SQL statement, with the same host variables, a second time. For example, the statistics in the catalog could be updated between executions, or indexes could be created or dropped. You could then execute the SELECT statement again.
The ordering is more likely to change if the second SELECT has a predicate that the first did not have; the database manager could choose to use an index on the new predicate. For example, it could choose an index on LOCATION for the first statement in our example and an index on DEPTNO for the second. Because rows are fetched in order by the index key, the second order need not be the same as the first.
Again, executing two similar SELECT statements can produce a different ordering of rows, even if no statistics change and no indexes are created or dropped. In the example, if there are many different values of LOCATION, the database manager could choose an index on LOCATION for both statements. Yet changing the value of DEPTNO in the second statement to the following, could cause the database manager to choose an index on DEPTNO:
SELECT * FROM DEPARTMENT
WHERE LOCATION = 'CALIFORNIA'
AND DEPTNO >= 'Z98'
ORDER BY DEPTNO
Because of the subtle relationships between the form of an SQL statement and the values in this statement, never assume that two different SQL statements will return rows in the same order unless the order is uniquely determined by an ORDER BY clause.
Ascending ordering of rows is the default. If there is only one row for each value of DEPTNO, then the following statement specifies a unique ascending ordering of rows:
SELECT * FROM DEPARTMENT
WHERE LOCATION = 'CALIFORNIA'
ORDER BY DEPTNO
To retrieve the same rows in reverse order, specify that the order is descending, as in the following statement:
SELECT * FROM DEPARTMENT
WHERE LOCATION = 'CALIFORNIA'
ORDER BY DEPTNO DESC
A cursor on the second statement retrieves rows in exactly the opposite order from a cursor on the first statement. Order of retrieval is guaranteed only if the first statement specifies a unique ordering sequence.
For retrieving rows in reverse order, it can be useful to have two indexes on the DEPTNO column, one in ascending order and the other in descending order.
The database manager does not guarantee an order to data stored in a table; therefore, the end of a table is not defined. However, order is defined on the result of an SQL statement:
SELECT * FROM DEPARTMENT
ORDER BY DEPTNO DESC
For this example, the following statement positions the cursor at the row with the highest DEPTNO value:
SELECT * FROM DEPARTMENT
WHERE DEPTNO =
(SELECT MAX(DEPTNO) FROM DEPARTMENT)
Note, however, that if several rows have the same value, the cursor is positioned on the first of them.
To scroll backward and update data that was retrieved previously, you can use a combination of the techniques discussed in Scrolling Through Data that has Already Been Retrieved and Updating Retrieved Data. You can do one of two things:
The UPDAT program uses dynamic SQL to access the STAFF table in the SAMPLE database and changes all managers to clerks. Then the program reverses the changes by rolling back the unit of work. The sample is available in the following programming languages:
Java applications access SQLCODE and SQLSTATE through the methods defined for the SQLException object, and therefore do not need an equivalent "include SQLCA" statement.
REXX applications have one occurrence of an SQLCA structure, named SQLCA, predefined for application use. It can be referenced without application definition.
Java and REXX applications do not need to declare host variables, except (for REXX) in the case of LOB file reference variables and locators. Host variable data types and sizes are determined at run time when the variables are referenced.
The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:
See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.
#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.
| Note: | REXX programs cannot contain static SQL. This program is written with dynamic 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