DB2 Version 10.1 for Linux, UNIX, and Windows

Compound SQL in Java™ applications

SQLJ statement clauses in SQLJ applications or SQL statements in JDBC applications can include compound statements.

A compound statement is a BEGIN-END block that includes SQL and procedural statements. All compound statements in JDBC and SQLJ applications are executed dynamically.

The data server must be DB2® for Linux, UNIX, and Windows Version 9.7 or later.

The IBM® Data Server Driver for JDBC and SQLJ passes the compound SQL to the data server without modification.

If your compound SQL contains parameter markers, you need to set the Connection or DataSource property enableNamedParameterMarkers to DB2BaseDataSource.YES (1).

In addition, at the data server, you need to set the DB2_COMPATIBILITY_VECTOR registry variable to enable PL/SQL compilation and execution.

The following example shows JDBC code that executes an inlined compound SQL statement. The compound SQL statement contains parameter markers, so you need to set enableNamedParameterMarkers to DB2BaseDataSource.YES (1).

…
Properties properties = new Properties(); // Create a Properties object
properties.put("user", "db2adm");         // Set user ID for the connection
properties.put("password", "db2adm");     // Set password for the connection
properties.put("enableNamedParameterMarkers", 
  new String("" + 
  com.ibm.db2.jcc.DB2BaseDataSource.YES + "")); 
                                          // Set enableNamedParameterMarkers
String url = "jdbc:db2://luw1.myloc.ibm.com:9896/sample";
                                          // Set URL for the data source
Connection conn1 = DriverManager.getConnection(url, properties); 
                                          // Create the connection
…
                                          // Prepare and execute the compound
                                          // statement in the same way that
                                          // you prepare a single SQL statement
PreparedStatement ps = conn1.prepareStatement(
     "BEGIN atomic " +
       " for row as " +
       "  select pk, c1, discretize (c1) as d from source " +
       " do " +
       "  if row.d is null then " +
       "   insert into except values (row.pk, ?); " +
       "  else " +
       "   insert into target values (row.pk, ?); " +
       "  end if; " +
       " end for; " +
     "end");
   ps.setInt(1, 98);
   ps.setInt(2, 88);
   ps.execute();
…