IBM DB2 User Manual

Performing positioned UPDATE and DELETE operations in an
SQLJ application
As in DB2 applications in other languages, performing positioned UPDATEs and
DELETEs with SQLJ is an extension of retrieving rows from a result table.
The basic steps are:
1. Declare the iterator.
The iterator can be positioned or named. For positioned UPDATE or DELETE
operations, the iterator must be declared as updatable. To do this, the
declaration must include the following clauses:
implements sqlj.runtime.ForUpdate
This clause causes the generated iterator class to include methods for
using updatable iterators. This clause is required for programs with
positioned UPDATE or DELETE operations.
with (updateColumns=″column-list″)
This clause specifies a comma-separated list of the columns of the result
table that the iterator will update. This clause is optional.
You need to declare the iterator as public, so you need to follow the rules for
declaring and using public iterators in the same file or different files.
If you declare the iterator in a file by itself, any SQLJ source file that has
addressability to the iterator and imports the generated class can retrieve data
and execute positioned UPDATE or DELETE statements using the iterator.
The authorization ID under which a positioned UPDATE or DELETE statement
executes depends on whether the statement executes statically or dynamically.
If the statement executes statically, the authorization ID is the owner of the plan
or package that includes the statement. If the statement executes dynamically
the authorization ID is determined by the DYNAMICRULES behavior that is in
effect. For the IBM Data Server Driver for JDBC and SQLJ, the behavior is
always DYNAMICRULES BIND.
2. Disable autocommit mode for the connection.
If autocommit mode is enabled, a COMMIT operation occurs every time the
positioned UPDATE statement executes, which causes the iterator to be
destroyed unless the iterator has the with (holdability=true) attribute.
Therefore, you need to turn autocommit off to prevent COMMIT operations
until you have finished using the iterator. If you want a COMMIT to occur
after every update operation, an alternative way to keep the iterator from being
destroyed after each COMMIT operation is to declare the iterator with
(holdability=true).
3. Create an instance of the iterator class.
This is the same step as for a non-updatable iterator.
4. Assign the result table of a SELECT to an instance of the iterator.
This is the same step as for a non-updatable iterator. The SELECT statement
must not include a FOR UPDATE clause.
5. Retrieve and update rows.
For a positioned iterator, do this by performing the following actions in a loop:
a. Execute a FETCH statement in an executable clause to obtain the current
row.
b. Test whether the iterator is pointing to a row of the result table by invoking
the PositionedIterator.endFetch method.
114 Application Programming Guide and Reference for Java
™

IBM DB2 Specifications

General

Developer	IBM
Initial Release	1983
License	Proprietary
Written in	C, C++
Category	Database Management System (RDBMS)
Operating System	Linux, Windows
Programming Languages	C, C++
Data Models	Relational
Editions	Community, Standard, Advanced
Key Features	high availability, scalability

Summary

JDBC application programming

How JDBC applications connect to a data source

Explains how JDBC applications establish a connection to a data source.

Connecting to a data source using the DriverManager interface with the IBM Data Server Driver for JDBC and SQLJ

Details on connecting to a data source using the DriverManager interface.

Connecting to a data source using the DataSource interface

Details on connecting to a data source using the DataSource interface for portability.

How to determine which type of IBM Data Server Driver for JDBC and SQLJ connectivity to use

Guidance on selecting the appropriate IBM Data Server Driver connectivity type.

JDBC interfaces for executing SQL

Explains the JDBC interfaces used for executing SQL statements.

Creating and modifying database objects using the Statement.executeUpdate method

Updating data in tables using the PreparedStatement.executeUpdate method

Data retrieval in JDBC applications

Explains how to retrieve data from tables using ResultSet objects.

Retrieving data from tables using the Statement.executeQuery method

Retrieving data from tables using the PreparedStatement.executeQuery method

SQLJ application programming

Connecting to a data source using SQLJ

Explains how to connect to a data source in an SQLJ program.

SQLJ connection technique 1: JDBC DriverManager interface

Details SQLJ connection using the JDBC DriverManager interface.

SQLJ connection technique 2: JDBC DriverManager interface

Details SQLJ connection using the JDBC DriverManager interface.

SQLJ connection technique 3: JDBC DataSource interface

Details SQLJ connection using the JDBC DataSource interface.

SQLJ connection technique 4: JDBC DataSource interface

Details SQLJ connection using the JDBC DataSource interface.

SQL statement execution in SQLJ applications

Describes executing SQL statements within SQLJ.

Creating and modifying DB2 objects in an SQLJ application

Performing positioned UPDATE and DELETE operations in an SQLJ application

Details performing positioned UPDATE and DELETE operations in SQLJ.

Data retrieval in SQLJ applications

Explains how to retrieve result sets using SQLJ iterators.

Using a named iterator in an SQLJ application

Using a positioned iterator in an SQLJ application

Using scrollable iterators in an SQLJ application

Controlling the execution of SQL statements in SQLJ

Describes how to control SQL statement execution in SQLJ.

Handling SQL errors and warnings in SQLJ applications

Details handling SQL errors and warnings in SQLJ.

Handling SQL errors in an SQLJ application

Explains how to handle SQL errors in SQLJ applications.

Java stored procedures and user-defined functions

Setting up the environment for Java routines

Details on setting up the WLM and Java environments for Java routines.

Setting up the WLM application environment for Java routines

Describes setting up WLM application environments for Java routines.

WLM address space startup procedure for Java routines

Provides an example of a WLM address space startup procedure for Java routines.

Run-time environment for Java routines

Explains the run-time environment setup for Java routines.

Definition of a Java routine to DB2

Explains how to define a Java routine to DB2.

Writing a Java stored procedure to return result sets

Explains how to write Java stored procedures that return multiple result sets.

Preparing and running JDBC and SQLJ programs

Program preparation for JDBC programs

Details the preparation process for JDBC programs.

Program preparation for SQLJ programs

Outlines the preparation process for SQLJ programs.

Binding SQLJ applications to access multiple database servers

Explains how to bind SQLJ applications to multiple DB2 servers.

Preparing Java routines with no SQLJ clauses to run from a JAR file

Steps to prepare Java routines from JAR files.

Preparing Java routines with SQLJ clauses to run from a JAR file

Recommended method for preparing SQLJ routines from JAR files.

Running JDBC and SQLJ programs

Explains how to run JDBC and SQLJ programs.

JDBC and SQLJ reference information

Data types that map to database data types in Java applications

Summarizes mappings of Java data types to database data types.

Data types for updating table columns

Data types for retrieval from table columns

Data types for calling stored procedures and user-defined functions

Data types in Java stored procedures and user-defined functions

Properties for the IBM Data Server Driver for JDBC and SQLJ

Defines how connection properties define the connection to a data source.

Common IBM Data Server Driver for JDBC and SQLJ properties for all supported database products

Lists properties common to all supported database products.

Common IBM Data Server Driver for JDBC and SQLJ properties for DB2 servers

Lists properties specific to DB2 servers.

currentQueryOptimization

Specifies a value that controls the class of query optimization.

Common IBM Data Server Driver for JDBC and SQLJ properties for DB2 for z/OS and IDS

Lists properties specific to DB2 for z/OS and IDS database servers.

Common IBM Data Server Driver for JDBC and SQLJ properties for DB2 Database for Linux, UNIX, and Windows

Lists properties specific to DB2 Database for Linux, UNIX, and Windows servers.

Installing the IBM Data Server Driver for JDBC and SQLJ

Installing the IBM Data Server Driver for JDBC and SQLJ as part of a DB2 installation

Details on installing the driver as part of DB2 installation.

Jobs for loading the IBM Data Server Driver for JDBC and SQLJ libraries

Describes jobs for loading the driver libraries.

Environment variables for the IBM Data Server Driver for JDBC and SQLJ

Lists required environment variables for the driver.

Customization of IBM Data Server Driver for JDBC and SQLJ configuration properties

Explains how to customize driver configuration properties.

Enabling the DB2-supplied stored procedures and defining the tables used by the IBM Data Server Driver for JDBC and SQLJ

Creating the WLM address space startup procedure for the IBM Data Server Driver for JDBC and SQLJ stored procedures

DB2Binder utility

Explains the DB2Binder utility for binding DB2 packages.

DB2Binder option descriptions

Migrating from the JDBC/SQLJ Driver for OS/390 and z/OS to the IBM Data Server Driver for JDBC and SQLJ

Prerequisites for migrating to the IBM Data Server Driver for JDBC and SQLJ

To migrate to the IBM Data Server Driver for JDBC and SQLJ, follow these steps

Provides steps for migrating to the IBM Data Server Driver.

Conversion of JDBC/SQLJ Driver for OS/390 and z/OS properties to IBM Data Server Driver for JDBC and SQLJ properties

DB2T4XAIndoubtUtil for distributed transactions with DB2 UDB for OS/390 and z/OS Version 7 servers

Verify the installation of the IBM Data Server Driver for JDBC and SQLJ

Explains how to verify the driver installation.

Security under the IBM Data Server Driver for JDBC and SQLJ

You can determine the security mechanism that is in effect for a connection by calling the DB2Connection.getDB2SecurityMechanism method

User ID and password security under the IBM Data Server Driver for JDBC and SQLJ

Details user ID and password security for JDBC connections.

User ID-only security under the IBM Data Server Driver for JDBC and SQLJ

Explains user ID-only security for JDBC connections.

Encrypted password, user ID, or user ID and password security under the IBM Data Server Driver for JDBC and SQLJ

Covers encrypted security for passwords and user IDs.

Kerberos security under the IBM Data Server Driver for JDBC and SQLJ

Details Kerberos security support for JDBC and SQLJ.

IBM Data Server Driver for JDBC and SQLJ trusted context support

IBM Data Server Driver for JDBC and SQLJ support for SSL

Configuring connections under the IBM Data Server Driver for JDBC and SQLJ to use SSL

Details how to configure connections for SSL usage.

Configuring the Java Runtime Environment to use SSL

Explains how to configure the Java Runtime Environment for SSL.

Security for preparing SQLJ applications with the IBM Data Server Driver for JDBC and SQLJ

JDBC connection concentrator and workload balancing

Java applications that use IBM Data Server Driver for JDBC and SQLJ type 4 connectivity to access DB2 for z/OS or IBM Informix Dynamic Server can take advantage of the connection concentrator and workload balancing functions.

Example of enabling the IBM Data Server Driver for JDBC and SQLJ connection concentrator for DB2 for z/OS servers

Provides an example of enabling connection concentrator for DB2 for z/OS.

Techniques for monitoring IBM Data Server Driver for JDBC and SQLJ connection concentrator and workload balancing

Problem diagnosis with the IBM Data Server Driver for JDBC and SQLJ

Collecting JDBC trace data

Explains procedures for starting JDBC trace collection.

Collecting SQLJ trace data during customization or bind

Example of using configuration properties to start a JDBC trace

Example of a trace program under the IBM Data Server Driver for JDBC and SQLJ

System monitoring for the IBM Data Server Driver for JDBC and SQLJ

To assist you in monitoring the performance of your applications with the IBM Data Server Driver for JDBC and SQLJ, the driver provides two methods to collect information for a connection.

To collect system monitoring data using the DB2SystemMonitor interface: Perform these basic steps:

Outlines basic steps for collecting system monitoring data.

Example of using DB2SystemMonitor methods to collect system monitoring data

Glossary