Re: row level lock

[Database Administrator]

I read a bit on the *object* and it seems you should use some kind of
updaterow (or something) to apply the update.
http://java.sun.com/j2se/1.4.2/docs/api/

I wonder if it makes a difference using datasources instead of the
DriverManager.

For the following code, i get
table S
convert to table SIX
then row X with table SIX
(this ugly quick and dirty java 'program' doesn't use the *object* methods
though)
Play with it.

ok a real dirty one...

//
// javac xixi.java
// assumed called from a db2clp by java -cp .;%classpath%;i:\java\jdk xixi
// this program may use items found in the file util.java /samples directory
//

import java.lang.*;
import java.sql.*;
import java.math.BigDecimal;
import java.io.*;

class xixi
{

public static void main(String argv[])
{

String alias= new String("DBOX");
String server="localhost";
int portNumber = 50000;
String userId="DBOXA";
String password="DBOXA";
Connection con = null;

String url = "jdbc:db2://" + server + ":" + portNumber + "/" + alias ;
System.out.println( " Connect to '" + alias + "' " +"(" + url +")" );

try
{
Driver driver = null;

driver=(Driver)Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();

System.out.println();
System.out.println("BEGIN UOW");

// setup
con = DriverManager.getConnection( url, userId, password );
con.setAutoCommit(false);
con.setTransactionIsolation(Connection.TRANSACTION _SERIALIZABLE);
con.setHoldability(ResultSet.HOLD_CURSORS_OVER_COM MIT);

// get some info
DatabaseMetaData dbmd = con.getMetaData();
System.out.println("dbmd.getDriverName() " + dbmd.getDriverName());
System.out.println( "getMajorVersion() " + driver.getMajorVersion() );
System.out.println( "getMinorVersion() " + driver.getMinorVersion() );
System.out.println( "con.isReadOnly() " + con.isReadOnly() );

// the test we're interested in...
updateThisPlease(con);

// disconnect
System.out.println();
System.out.println(" Disconnect from '" + alias + "' database.");

con.commit();
con.close();
}
catch (Exception e)
{
JdbcException jdbcExc = new JdbcException(e);
jdbcExc.handle();
}

SnapIt();

} // main


// --------------------------------
// updateThisPlease(Connection con)
// --------------------------------
//
static void updateThisPlease(Connection con)
{
try
{
String itmnum = null;
int test=0;
int id = 0;
String curName = null;

System.out.println();
System.out.println(
"----------------------------------------------------------\n");

// display the final content of the 'DDAI.NOINDEX_TABLE' table
NOINDEXTABLETbContentDisplay(con);

System.out.println();
System.out.println(
" Invoke the statements:\n" +
" Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV E,
ResultSet.CONCUR_UPDATABLE);\n" +
" ResultSet rs = stmt.executeQuery(select id, brnum, ITMNUM, DESC
from DDAI.NOINDEX_TABLE where id = 166 for update with rr );" +
"\n" );
System.out.print( "(1): " );
SnapIt();

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV E,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("select id, brnum, ITMNUM, DESC from
DDAI.NOINDEX_TABLE where id = 166 for update with rr" );

System.out.print( "(2): " );
SnapIt();

System.out.println(
" Invoke the statements:\n" +
" curName = rs.getCursorName();\n" +
" Statement stmt1 = con.createStatement();" +
"\n" );
curName = rs.getCursorName();
Statement stmt1 = con.createStatement();
while (rs.next())
{
System.out.print( "(3): " );
SnapIt();

stmt1.executeUpdate("UPDATE DDAI.NOINDEX_TABLE SET itmnum = 4 WHERE
CURRENT OF " + curName);

System.out.print( "(4): " );
SnapIt();

}
stmt1.close();
rs.close();
stmt.close();

// display the final content of the 'DDAI.NOINDEX_TABLE' table
NOINDEXTABLETbContentDisplay(con);

// rollback the transaction
System.out.println();
System.out.println(" Rollback the transaction...");
con.rollback();
System.out.println(" Rollback Done.");
System.out.print( "(5): " );
SnapIt();

}
catch (Exception e)
{
JdbcException jdbcExc = new JdbcException(e, con);
jdbcExc.handle();
}
} // updateThisPlease




// helping function: Display the content of the 'DDAI.NOINDEX_TABLE' table
static void NOINDEXTABLETbContentDisplay(Connection con)
{
try
{
Integer brnum = new Integer(0);
Integer itmnum = new Integer(0);
Integer id = new Integer(0);
String desc = null;

System.out.println();
System.out.println(
" SELECT * FROM DDAI.NOINDEX_TABLE WHERE brnum >= 150\n" +
" brnum itmnum id desc \n" +
" --- -------- ---- ----- ----- -------- --------");

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT BRNUM, ITMNUM, DESC, ID FROM DDAI.NOINDEX_TABLE WHERE brnum
>= 150");

while (rs.next())
{
brnum = Integer.valueOf(rs.getString(1));
itmnum = Integer.valueOf(rs.getString(2));
desc = rs.getString(3);
id = Integer.valueOf(rs.getString(4));

System.out.print(" "+Data.format(brnum, 20) +
" " + Data.format(itmnum, 20) +
" " + Data.format(id, 20));
if (desc != null)
{
System.out.print(" " + Data.format(desc, 20));
}
else
{
System.out.print(" -");
}
System.out.println();
}
rs.close();
stmt.close();
}
catch (Exception e)
{
JdbcException jdbcExc = new JdbcException(e, con);
jdbcExc.handle();
}
} // NOINDEXTABLETbContentDisplay


// helping function: do the snapshot
static void SnapIt()
{
try {
int test=0;

System.out.print( "Press any key to continue... " );
test = System.in.read();
test = System.in.read();
System.out.print( "\n" );
String line;
Process p = Runtime.getRuntime().exec(("db2cmd /c /w /i db2 get
snapshot for locks on dbox"));

BufferedReader input = new BufferedReader
(new InputStreamReader(p.getInputStream()));
while ((line = input.readLine()) != null) {
System.out.println(line);
}
input.close();
}
catch (Exception err) {
err.printStackTrace();
}

} // SnapIt




} // xixi