1. DL manuals
  2. IBM
  3. Other
  4. DB2
  5. Manual

IBM DB2 Manual - Contents

Summary of DB2

  • Page 1

    Db2 version 9.1 for z/os application programming guide and reference for java ™ sc18-9842-03.

  • Page 3

    Db2 version 9.1 for z/os application programming guide and reference for java ™ sc18-9842-03.

  • Page 4

    Note before using this information and the product it supports, be sure to read the general information under “notices” at the end of this information. Fourth edition (december 2008) this edition applies to db2 version 9.1 for z/os (db2 v9.1 for z/os), product number 5635-db2, and to any subsequent ...

  • Page 5: Contents

    Contents about this information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Ix who should read this information . . . . . . . . . . . . . . . . . . . . . . . . . . . . Ix db2 utilities suite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Ix terminology and citations....

  • Page 6

    Configuration of db2 for z/os servers for client reroute . . . . . . . . . . . . . . . . . . . 85 enabling ibm data server driver for jdbc and sqlj client reroute for connections to db2 servers . . . . . 85 enabling ibm data server driver for jdbc and sqlj client reroute for connections to ids serve...

  • Page 7

    Chapter 6. Preparing and running jdbc and sqlj programs. . . . . . . . . . . . 183 program preparation for jdbc programs . . . . . . . . . . . . . . . . . . . . . . . . . 183 program preparation for sqlj programs . . . . . . . . . . . . . . . . . . . . . . . . . 183 binding sqlj applications to acce...

  • Page 8

    Db2pooledconnection class . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345 db2poolmonitor class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348 db2preparedstatement interface . . . . . . . . . . . . . . . . . . . . . . . . . . . 351 db2resultsetmetadata interface . . . . ....

  • Page 9

    Security for preparing sqlj applications with the ibm data server driver for jdbc and sqlj . . . . . . . 462 chapter 11. Jdbc and sqlj connection pooling support . . . . . . . . . . . . . 465 chapter 12. Ibm data server driver for jdbc and sqlj type 4 connectivity jdbc and sqlj distributed transacti...

  • Page 10

    Viii application programming guide and reference for java ™.

  • Page 11: About This Information

    About this information this information describes db2 ® for z/os ® support for java ™ . This support lets you access relational databases from java application programs. This information assumes that your db2 subsystem is running in version 9.1 new-function mode. Generally, new functions that are de...

  • Page 12

    Db2 represents either the db2 licensed program or a particular db2 subsystem. Omegamon ® refers to any of the following products: v ibm tivoli ® omegamon xe for db2 performance expert on z/os v ibm tivoli omegamon xe for db2 performance monitor on z/os v ibm db2 performance expert for multiplatforms...

  • Page 13

    The following web site: http://publib.Boulder.Ibm.Com/infocenter/dzichelp ibm and accessibility see the ibm accessibility center at http://www.Ibm.Com/able for more information about the commitment that ibm has to accessibility. How to send your comments your feedback helps ibm to provide quality in...

  • Page 14

    Optional_item required_item v if you can choose from two or more items, they appear vertically, in a stack. If you must choose one of the items, one item of the stack appears on the main path. Required_item required_choice1 required_choice2 if choosing one of the items is optional, the entire stack ...

  • Page 15

    V with the exception of xpath keywords, keywords appear in uppercase (for example, from ). Keywords must be spelled exactly as shown. Xpath keywords are defined as lowercase names, and must be spelled exactly as shown. Variables appear in all lowercase letters (for example, column-name ). They repre...

  • Page 16

    Xiv application programming guide and reference for java ™.

  • Page 17

    Chapter 1. Java application development for db2 the db2 database system provides driver support for client applications and applets that are written in java using jdbc, and for embedded sql for java (sqlj). Jdbc is an application programming interface (api) that java applications use to access relat...

  • Page 18

    2 application programming guide and reference for java ™.

  • Page 19

    Chapter 2. Supported drivers for jdbc and sqlj the db2 product includes support for two types of jdbc driver architecture. According to the jdbc specification, there are four types of jdbc driver architectures: type 1 drivers that implement the jdbc api as a mapping to another data access api, such ...

  • Page 20

    V all of the methods that are described in the jdbc 3.0 specifications. See ″ driver support for jdbc apis ″ . V some methods that are described in the jdbc 4.0 specifications, if you install ibm data server driver for jdbc and sqlj version 4.0. V sqlj application programming interfaces, as defined ...

  • Page 21

    The ibm data server driver for jdbc and sqlj is upward compatible with the next version of a db2 database if the applications under which the driver runs use no new features. For example, ibm data server driver for jdbc and sqlj type 4 connectivity from the ibm data server driver for jdbc and sqlj v...

  • Page 22

    Table 1. Versions of db2 database for linux, unix, and windows and ibm data server driver for jdbc and sqlj (continued) db2 database for linux, unix, and windows version and fix pack level ibm data server driver for jdbc and sqlj version version 8.1 fix pack 3 1.3.70 version 8.1 fix pack 4a 1.5.54 v...

  • Page 23

    Chapter 3. Jdbc application programming writing a jdbc application has much in common with writing an sql application in any other language. In general, you need to do the following things: v access the java packages that contain jdbc methods. V declare variables for sending data to or retrieving da...

  • Page 24

    Try { // load the driver class.Forname("com.Ibm.Db2.Jcc.Db2driver"); 3a system.Out.Println("**** loaded the jdbc driver"); // create the connection using the ibm data server driver for jdbc and sqlj con = drivermanager.Getconnection (url); 3b // commit changes manually con.Setautocommit(false); syst...

  • Page 25

    Notes to figure 1 on page 7: note description 1 this statement imports the java.Sql package, which contains the jdbc core api. For information on other java packages that you might need to access, see ″ java packages for jdbc support ″ . 2 string variable empno performs the function of a host variab...

  • Page 26

    The following figure shows how a java application connects to a data source using ibm data server driver for jdbc and sqlj type 4 connectivity. Java application drivermanager or datasource local database or db2 subsystem jdbc driver* database server *java byte code executed under jvm, and native cod...

  • Page 27

    Related concepts “example of a simple jdbc application” on page 7 related tasks “connecting to a data source using sqlj” on page 103 “disconnecting from data sources in jdbc applications” on page 99 connecting to a data source using the drivermanager interface with the ibm data server driver for jdb...

  • Page 28

    V use the form of the getconnection method that specifies info , after setting the user and password properties in a java.Util.Properties object. Example: establishing a connection and setting the user id and password in a url: string url = "jdbc:db2://myhost:5021/mydb:" + "user=dbadm;password=dbadm...

  • Page 29

    Ibm data server driver for jdbc and sqlj type 4 connectivity url syntax jdbc:db2: // server jdbc:db2j:net: jdbc:ids: : port / database : property = value ; ibm data server driver for jdbc and sqlj type 4 connectivity url option descriptions the parts of the url have the following meanings: jdbc:db2:...

  • Page 30

    V if the connection is to an ibm cloudscape server, the database is the fully-qualified name of the file that contains the database. This name must be enclosed in double quotation marks ( ″ ). For example: "c:/databases/testdb" property=value ; a property and its value for the jdbc connection. You c...

  • Page 31

    Jdbc:db2os390sqlj: are for compatibility of programs that were written for older drivers, and apply to ibm data server driver for jdbc and sqlj type 2 connectivity only. Jdbc:default:connection indicates that the url is for a connection to the local subsystem through a db2 thread that is controlled ...

  • Page 32

    The best way to use a datasource object is for your system administrator to create and manage it separately, using websphere application server or some other tool. The program that creates and manages a datasource object also uses the java naming and directory interface (jndi) to assign a logical na...

  • Page 33

    Import java.Sql.*; // jdbc base import javax.Sql.*; // addtional methods for jdbc import com.Ibm.Db2.Jcc.*; // ibm data server driver for jdbc and sqlj 1 // interfaces db2simpledatasource dbds=new db2simpledatasource(); 2 dbds.Setdatabasename("dbloc1"); 3 // assign the location name dbds.Setdescript...

  • Page 34

    For the drivermanager interface, you specify the type of connectivity through the url in the drivermanager.Getconnection method. For the datasource interface, you specify the type of connectivity through the drivertype property. The following table summarizes the differences between type 2 connectiv...

  • Page 35

    Jdbc connection objects when you connect to a data source by either connection method, you create a connection object, which represents the connection to the data source. You use this connection object to do the following things: v create statement , preparedstatement , and callablestatement objects...

  • Page 36

    V com.Ibm.Db2.Jcc.Db2xadatasource , which supports connection pooling and distributed transactions. The connection pooling is provided by websphere application server or another application server. You can use this implementation only with ibm data server driver for jdbc and sqlj type 4 connectivity...

  • Page 37

    Related reference “properties for the ibm data server driver for jdbc and sqlj” on page 201 “db2simpledatasource class” on page 352 “db2xadatasource class” on page 366 java packages for jdbc support before you can invoke jdbc methods, you need to be able to access all or parts of various java packag...

  • Page 38

    3. If the method returns a resultset : a. In a loop, position the cursor using the next method, and retrieve data from each column of the current row of the resultset object using getxxx methods. B. Invoke the close method to close the resultset object. Example: the following code demonstrates how t...

  • Page 39

    Databasemetadata.Getdatabaseproductname returns a string that identifies the database manager and the operating system. The string has one of the following formats: database-productdatabase-product / operating-system the following table shows examples of values that are returned by databasemetadata....

  • Page 40

    Jdbc interfaces for executing sql you execute sql statements in a traditional sql program to update data in tables, retrieve data from the tables, or call stored procedures. To perform the same functions in a jdbc program, you invoke methods. Those methods are defined in the following interfaces: v ...

  • Page 41

    V execute insert, update, delete, and merge statements that do not contain parameter markers. V with the ibm data server driver for jdbc and sqlj, execute the call statement to call stored procedures that have no parameters and that return no result sets. To execute these sql statements, you need to...

  • Page 42

    Db2 for z/os does not support dynamic execution of the call statement. For calls to stored procedures that are on db2 for z/os data sources, the parameters can be parameter markers or literals, but not expressions. The following types of literals are supported: v integer v double v decimal v charact...

  • Page 43

    You can also use the preparedstatement.Executeupdate method for statements that have no parameter markers. The steps for executing a preparedstatement object with no parameter markers are similar to executing a preparedstatement object with parameter markers, except you skip step 2 on page 26. The f...

  • Page 44

    – a positive number, if a positive number of rows are affected by the operation, and the operation is not a mass delete on a segmented table space. – 0, if no rows are affected by the operation. – -1, if the operation is a mass delete on a segmented table space. V for an sql call statement, a value ...

  • Page 45

    3. Check for errors. If no errors occurred: a. Get the number of rows that were affect by each sql statement from the array that the executebatch invocation returns. This number does not include rows that were affected by triggers or by referential integrity enforcement. B. If autocommit is disabled...

  • Page 46

    Con.Commit(); 4b } catch(batchupdateexception b) { 5 // process batchupdateexception } in the following code fragment, a batched statement returns automatically generated keys. Try { ... Preparedstatement pstmt = con.Preparestatement( 1 "insert into dept (deptno, deptname, admrdept) " + "values (?,?...

  • Page 47

    Learning about parameters in a preparedstatement using parametermetadata methods the ibm data server driver for jdbc and sqlj includes support for the parametermetadata interface. The parametermetadata interface contains methods that retrieve information about the parameter markers in a preparedstat...

  • Page 48

    Data retrieval in jdbc applications in jdbc applications, you retrieve data using resultset objects. A resultset represents the result set of a query. Retrieving data from tables using the statement.Executequery method to retrieve data from a table using a select statement with no parameter markers,...

  • Page 49

    “jdbc interfaces for executing sql” on page 24 related tasks “learning about a resultset using resultsetmetadata methods” on page 35 “specifying updatability, scrollability, and holdability for resultsets in jdbc applications” on page 38 “retrieving multiple result sets from a stored procedure in a ...

  • Page 50

    Related concepts “jdbc interfaces for executing sql” on page 24 related tasks “retrieving multiple result sets from a stored procedure in a jdbc application” on page 46 related reference “driver support for jdbc apis” on page 252 making batch queries in jdbc applications the ibm data server driver f...

  • Page 51

    Java.Sql.Connection con = java.Sql.Drivermanager.Getconnection(url, properties); java.Sql.Statement s = con.Createstatement(); // clean up from previous executions try { s.Executeupdate ("drop table testqbatch"); } catch (exception e) { } // create and populate a test table s.Executeupdate ("create ...

  • Page 52

    V the number of columns in a resultset v the qualifier for the underlying table of the resultset v information about a column, such as the data type, length, precision, scale, and nullability v whether a column is read-only after you invoke the executequery method to generate a resultset for a query...

  • Page 53

    Characteristics of a jdbc resultset under the ibm data server driver for jdbc and sqlj the ibm data server driver for jdbc and sqlj provides support for scrollable, updatable, and holdable cursors. In addition to moving forward, one row at a time, through a resultset , you might want to do the follo...

  • Page 54

    If a jdbc resultset is static, the size of the result table and the order of the rows in the result table do not change after the cursor is opened. This means that if you insert rows into the underlying table, the result table for a static resultset does not change. If you delete a row of a result t...

  • Page 55

    Table 6. Valid combinations of resultsettype and resultsetconcurrency for scrollable resultsets resultsettype value resultsetconcurrency value type_forward_only concur_read_only type_forward_only concur_updatable type_scroll_insensitive concur_read_only type_scroll_sensitive 1 concur_read_only type_...

  • Page 56

    Table 7. Resultset methods for positioning a scrollable cursor (continued) method positions the cursor notes: 1. This method does not apply to connections to ibm informix dynamic server. 2. If the cursor is before the first row of the resultset , this method positions the cursor on the first row. 3....

  • Page 57

    Related concepts “characteristics of a jdbc resultset under the ibm data server driver for jdbc and sqlj” on page 37 related tasks “retrieving data from tables using the statement.Executequery method” on page 32 multi-row sql operations with the ibm data server driver for jdbc and sqlj: ibm data ser...

  • Page 58

    If the data source does not have support for multi-row fetch, the ibm data server driver for jdbc and sqlj performs the multi-row fetch operations. When you retrieve data in your applications, the ibm data server driver for jdbc and sqlj determines whether to use multi-row fetch, depending on severa...

  • Page 59

    Testing whether the current row of a resultset is a delete or update hole in a jdbc application: if a resultset has the type_scroll_sensitive attribute, and the underlying cursor is sensitive static, you need to test for delete or update holes before you attempt to retrieve rows of the resultset . A...

  • Page 60

    1. Perform the following steps for each row that you want to insert. A. Call the resultset.Movetoinsertrow method to create the row that you want to insert. The row is created in a buffer outside the resultset . If an insert buffer already exists, all old values are cleared from the buffer. B. Call ...

  • Page 61

    2. If inserts can be visible to the resultset , call the databasemetadata.Insertsaredetected method to determine whether the given type of resultset can detect inserts. 3. If the resultset can detect inserts, call the resultset.Rowinserted method to determine whether the current row is was inserted....

  • Page 62

    Callablestatement.Execute invoke this method if the stored procedure returns multiple result sets, or an unknown number of result sets. Restriction: ibm informix dynamic server (ids) data sources do not support multiple result sets. 5. If the stored procedure returns multiple result sets, retrieve t...

  • Page 63

    “learning about a resultset using resultsetmetadata methods” on page 35 “retrieving data from tables using the preparedstatement.Executequery method” on page 33 “retrieving data from tables using the statement.Executequery method” on page 32 “calling stored procedures in jdbc applications” on page 4...

  • Page 64

    } rs.Close(); // close the result set cstmt.Close(); // close the statement retrieving an unknown number of result sets from a stored procedure in a jdbc application: retrieving an unknown number of result sets from a stored procedure is a more complicated procedure than retrieving a known number of...

  • Page 65

    V to keep the current resultset open when you check for the next resultset , specify a value of statement.Keep_current_result for current . V to close the current resultset when you check for the next resultset , specify a value of statement.Close_current_result for current . V to close all resultse...

  • Page 66

    With progressive streaming, the data source dynamically determines the most efficient mode in which to return lob or xml data, based on the size of the lobs or xml objects. For ibm data server driver for jdbc and sqlj version 3.50 and later, progressive streaming behavior is the default for lob retr...

  • Page 67

    If you disable progressive streaming, the way in which the ibm data server driver for jdbc and sqlj handles lobs depends on the value of the fullymaterializelobdata property. Use of progressive streaming is the preferred method of lob or xml data retrieval. Lob locators with the ibm data server driv...

  • Page 68

    For clob columns: – getasciistream – getcharacterstream – getstring v call the following resultset methods to update a blob or clob column in an updatable resultset : for blob columns: – updatebinarystream – updateblob for clob columns: – updateasciistream – updatecharacterstream – updateclob if you...

  • Page 69

    Resultset.Updateblob(int columnindex , inputstream x ) resultset.Updateblob(string columnlabel , inputstream x ) resultset.Updatebinarystream(int columnindex , inputstream x ) resultset.Updatebinarystream(string columnlabel , inputstream x ) preparedstatement.Setblob(int columnindex , inputstream x ...

  • Page 70

    V use the callablestatement.Registeroutparameter call to specify that an output parameter is of type blob. Then you can retrieve the parameter value into any variable that has a data type that is compatible with a blob data type. For example, the following code lets you retrieve a blob value into a ...

  • Page 71

    Variable that has a data type that is compatible with a clob data type. For example, the following code lets you retrieve a clob value into a string variable: cstmt.Registeroutparameter(parmindex, java.Sql.Types.Clob); cstmt.Execute(); string chardata = cstmt.Getstring(parmindex); v use the callable...

  • Page 72

    If you are using jdbc 3.0, for setobject , use the ibm data server driver for jdbc and sqlj-only type com.Ibm.Db2.Jcc.Types.Rowid or an instance of the com.Ibm.Db2.Jcc.Db2rowid class as the target type for the parameter. If you are using jdbc 4.0, for setobject , use the type java.Sql.Types.Rowid or...

  • Page 73

    In a jdbc program, you can create a distinct type using the executeupdate method to execute the create distinct type statement. You can also use executeupdate to create a table that includes a column of that type. When you retrieve data from a column of that type, or update a column of that type, yo...

  • Page 74

    Connection.Releasesavepoint(savepoint savepoint ) releases the specified savepoint, and all subsequently established savepoints. Connection.Rollback(savepoint savepoint ) rolls back work to the specified savepoint. Databasemetadata.Supportssavepoints() indicates whether a data source supports savepo...

  • Page 75

    Method call. The statement that is executed must be an insert statement or an insert within select statement. Otherwise, the jdbc driver ignores the parameter that sets the flag. Restriction: you cannot prepare an sql statement for retrieval of automatically generated keys and use the preparedstatem...

  • Page 76

    Import java.Sql.*; import java.Math.*; import com.Ibm.Db2.Jcc.*; connection con; statement stmt; resultset rs; java.Math.Bigdecimal idcolvar; ... Stmt = con.Createstatement(); // create a statement object stmt.Executeupdate( "create table emp_phone (empno char(6), phoneno char(4), " + "identcol inte...

  • Page 77

    Providing extended client information to the data source with ibm driver-only methods a set of ibm data server driver for jdbc and sqlj-only methods provide extra information about the client to the server. This information can be used for accounting, workload management, or debugging. Extended clie...

  • Page 78

    Providing extended client information to the data source with client info properties the ibm data server driver for jdbc and sqlj version 4.0 supports jdbc 4.0 client info properties, which you can use to provide extra information about the client to the server. This information can be used for acco...

  • Page 79

    Client info properties support by the ibm data server driver for jdbc and sqlj jdbc 4.0 includes client info properties, which contain information about a connection to a data source. The databasemetadata.Getclientinfoproperties method returns a list of client info properties that the ibm data serve...

  • Page 80

    The following table lists the client info property values that the ibm data server driver for jdbc and sqlj returns for db2 for z/os table 12. Client info property values for db2 for z/os name max_len default_value for type 4 connectivity default_value for type 2 connectivity description application...

  • Page 81

    Xml data in jdbc applications in jdbc applications, you can store data in xml columns and retrieve data from xml columns. In database tables, the xml built-in data type is used to store xml data in a column as a structured set of nodes in a tree format. In applications, xml data is in the serialized...

  • Page 82

    As internally encoded data. Xml data that is sent to the data source as character data is treated as externally encoded data. External encoding for java applications is always unicode encoding. Externally encoded data can have internal encoding. That is, the data might be sent to the data source as ...

  • Page 83

    Example: the following example demonstrates inserting data from a file into an xml column. The data is inserted as binary data, so the database server honors the internal encoding. Public void insertbinstream() { preparedstatement insertstmt = null; string sqls = null; int cid = 0; resultset rs=null...

  • Page 84

    V use the resultset.Getobject method to retrieve the data, and then cast it to the db2xml type and assign it to a db2xml object. Then use a db2xml.Getdb2xxx or db2xml.Getdb2xmlxxx method to retrieve the data into a compatible output data type. Db2xml.Getdb2xmlxxx methods add xml declarations with en...

  • Page 85

    If the application executes the xmlserialize function on the data that is to be returned, after execution of the function, the data has the data type that is specified in the xmlserialize function, not the xml data type. Therefore, the driver handles the data as the specified type and ignores any in...

  • Page 86

    String coltype = meta.Getcolumntype(1); system.Out.Println("fetchtostring: column type = " + coltype); while (rs.Next()) { stringdoc = rs.Getstring(1); system.Out.Println("document contents:"); system.Out.Println(stringdoc); } catch (sqlexception sqle) { system.Out.Println("fetchtostring: sql except...

  • Page 87

    “db2xml interface” on page 368 java support for xml schema registration and removal the ibm data server driver for jdbc and sqlj provides methods that let you write java application programs to register and remove xml schemas and their components. The jdbc methods are: db2connection.Registerdb2xmlsc...

  • Page 88

    Try { xmlschemadocumentslengths[0] = (int) fi.Getchannel().Size(); system.Out.Println(xmlschemadocumentslengths[0]); } catch (ioexception e1) { e1.Printstacktrace(); } xmlschemadocumentsproperties[0] = null; xmlschemadocumentspropertieslengths[0] = 0; xmlschemaproperties = null; xmlschemapropertiesl...

  • Page 89

    Db2connection ds = (db2connection) con; // invoke updatedb2xmlschema ds.Updatedb2xmlschema( xmlschemanamequalifiertarget, xmlschemanametarget, xmlschemanamequalifiersource, xmlschemanamesource, dropsourceschema); } transaction control in jdbc applications in jdbc applications, as in other types of s...

  • Page 90

    Committing or rolling back jdbc transactions in jdbc, to commit or roll back transactions explicitly, use the commit or rollback methods. For example: connection con; ... Con.Commit(); if autocommit mode is on, the database manager performs a commit operation after every sql statement completes. To ...

  • Page 91

    Table 19. Default autocommit modes for ids data sources (continued) type of data source default autocommit mode for local transactions default autocommit mode for global transactions non-ansi-compliant database with logging true false exceptions and warnings under the ibm data server driver for jdbc...

  • Page 92

    V the sqlstate getthrowable returns a java.Lang.Throwable object that caused the sqlexception , or null, if no such object exists. Printtrace prints diagnostic information. Sqlexception subclasses if you are using jdbc 4.0 or later, you can obtain more specific information than an sqlexception provi...

  • Page 93

    Sqlexception objects are chained in the same order as the corresponding statements were added to the batch. To help you match sqlexception objects to statements in the batch, the error description field for each sqlexception object begins with this string: error for batch element # n : n is the numb...

  • Page 94

    2. Optional: during a connection to a db2 for z/os or ibm informix dynamic server (ids) data source, set the retrievemessagesfromserverongetmessage property to true if you want full message text from an sqlexception.Getmessage call. 3. Optional: during a ibm data server driver for jdbc and sqlj type...

  • Page 95

    9) invoke the db2sqlca.Getsqlwarn method to retrieve the sqlwarn values in an array. 10) invoke the db2sqlca.Getsqlstate method to retrieve the sqlstate value. 11) invoke the db2sqlca.Getmessage method to retrieve error message text from the data source. E. Invoke the sqlexception.Getnextexception m...

  • Page 96

    // individual sqlerrmc tokens string sqlerrp = sqlca.Getsqlerrp(); 5d7 // get the sqlerrp int[] sqlerrd = sqlca.Getsqlerrd(); 5d8 // get sqlerrd fields char[] sqlwarn = sqlca.Getsqlwarn(); 5d9 // get sqlwarn fields string sqlstate = sqlca.Getsqlstate(); 5d10 // get sqlstate string errmessage = sqlca...

  • Page 97

    Handling an sqlwarning under the ibm data server driver for jdbc and sqlj unlike sql errors, sql warnings do not cause jdbc methods to throw exceptions. Instead, the connection , statement , preparedstatement , callablestatement , and resultset classes contain getwarnings methods, which you need to ...

  • Page 98

    Related concepts “example of a simple jdbc application” on page 7 related tasks “handling an sqlexception under the ibm data server driver for jdbc and sqlj” on page 77 retrieving information from a batchupdateexception when an error occurs during execution of a statement in a batch, processing cont...

  • Page 99

    Each resultset contains: v if the resultset is not null, it contains the automatically generated keys for an execution of the batched sql statement. V if the resultset is null, an error occurred during retrieval of the automatically generated keys for the sql statement. 3. Use sqlexception methods g...

  • Page 100

    Ibm data server driver for jdbc and sqlj client reroute support the db2 automatic client reroute feature allows client applications to recover from a loss of communication with the server so that they can continue to work with minimal interruption. Jdbc and sqlj client applications can take advantag...

  • Page 101

    Configuration of db2 for z/os servers for client reroute if sysplex routing is disabled on a db2 for z/os server, and a data sharing group is set up for member-specific access, you can use ibm data server driver for jdbc and sqlj client reroute support. If sysplex routing is enabled for a db2 data s...

  • Page 102

    A. Specify the server name and port number of the primary server that you want to use in the connection url. B. Set the clientreroutealternateservername and clientreroutealternateportnumber properties to the server name and port number of the alternate server that you want to use. V if your applicat...

  • Page 103

    The following code configures jndi for client reroute. It creates an instance of db2clientrerouteserverlist , binds that instance to the jndi registry, and assigns the jndi name of the db2clientrerouteserverlist object to the clientrerouteserverlistjndiname property. // create a starting context for...

  • Page 104

    V if your installation is using more than one connection manager for client reroute, and your application is using the datasource interface for connections, use one of the following techniques: – set the server names and port numbers in datasource properties: a. Set the servername and portnumber pro...

  • Page 105

    The following code sets up datasource properties in an application so that the application connects to cm2.Sf.Ibm.Com as the main connection manager, and cm1.Sf.Ibm.Com as the alternative connection manager. That is, if cm2.Sf.Ibm.Com is down during the initial connection, the driver should connect ...

  • Page 106

    Getxxx and setxxx methods are defined for each property. When a datasource is configured to use jndi for storing client reroute alternate information, the standard server and port properties of the datasource are not used for a getconnection request. Instead, the primary server address is obtained f...

  • Page 107

    V for the first connection to ibm informix dynamic server: a. The application specifies a server and port for the initial connection. Those values identify a connection manager. B. The ibm data server driver for jdbc and sqlj uses the information from the connection manager to obtain information abo...

  • Page 108

    C. If reconnection to the primary server fails, the ibm data server driver for jdbc and sqlj attempts to connect to the alternate servers. If this is not the first connection, the latest alternate server list is used to find the next alternate server. Reconnection to the primary server is called fai...

  • Page 109

    Examples example: client reroute to a db2 server when maxretriesforclientreroute and retryintervalforclientreroute are not set: suppose that the following properties are set for a connection to a database: property value enableclientaffinitieslist db2basedatasource.No (2) servername host1 portnumber...

  • Page 110

    Host2a:port2a is stored as the new alternative server. If another communication failure is detected on this same connection, or on another connection that is created from the same datasource , the driver tries to connect to host1a:port1a as the new primary server. If that connection fails, the drive...

  • Page 111

    1. The ibm data server driver for jdbc and sqlj tries to connect to the connection manager that is identified by host1:port1. 2. The connection to host1:port1 fails, so the driver tries to connect to the connection manager that is identified by host2:port2. 3. The connection to host2:port2 succeeds....

  • Page 112

    In an ibm informix dynamic server environment, primary and standby servers correspond to members of a high-availability cluster that is controlled by a connection manager. If multiple connection managers exist, the ibm data server driver for jdbc and sqlj can use them to determine primary and altern...

  • Page 113

    1. The driver tries to connect to host1:port1. 2. The connection to host1:port1 fails. 3. The driver waits two seconds. 4. The driver tries to connect to host1:port1. 5. The connection to host1:port1 fails. 6. The driver waits two seconds. 7. The driver tries to connect to host1:port1. 8. The connec...

  • Page 114

    Existing connections to that member terminate. However, when new type 2 connections to the data sharing group are requested, db2 for z/os connects the application to an alternative member of the data sharing group that is active on the same lpar. Related reference “ ibm data server driver for jdbc a...

  • Page 115

    Disconnecting from data sources in jdbc applications when you have finished with a connection to a data source, it is essential that you close the connection to the data source. Doing this releases the connection object’s database and jdbc resources immediately. To close the connection to the data s...

  • Page 116

    100 application programming guide and reference for java ™.

  • Page 117

    Chapter 4. Sqlj application programming writing a sqlj application has much in common with writing an sql application in any other language. In general, you need to do the following things: v import the java packages that contain sqlj and jdbc methods. V declare variables for sending data to or retr...

  • Page 118

    Ezsqljnameiter iter; int count=0; #sql [ctx] iter = {select lastname from employee}; 4b // create result table of the select while (iter.Next()) { 4c system.Out.Println(iter.Lastname()); // retrieve rows from result table count++; } system.Out.Println("retrieved " + count + " rows of data"); } catch...

  • Page 119

    Notes to figure 27 on page 101: note description 1 these statements import the java.Sql package, which contains the jdbc core api, and the sqlj.Runtime package, which contains the sqlj api. For information on other packages or classes that you might need to access, see ″ java packages for sqlj suppo...

  • Page 120

    Related tasks “connecting to a data source using the datasource interface” on page 15 “performing positioned update and delete operations in an sqlj application” on page 114 “making batch updates in sqlj applications” on page 119 “committing or rolling back sqlj transactions” on page 153 “closing th...

  • Page 121

    If the data source is a db2 for z/os system, and you do not specify these parameters, db2 uses the external security environment, such as the racf security environment, that was previously established for the user. For a cics connection, you cannot specify a user id or password. Info specifies an ob...

  • Page 122

    Doing this creates a jdbc connection object for the connection to the data source. You can use any of the forms of getconnection that are specified in ″ connect to a data source using the drivermanager interface with the ibm data server driver for jdbc and sqlj ″ . The meanings of the url , user , a...

  • Page 123

    Sqlj connection technique 3: jdbc datasource interface sqlj connection technique 3 uses the jdbc datasource as the underlying means for creating the connection. To use sqlj connection technique 3, follow these steps: 1. Execute an sqlj connection declaration clause . Doing this generates a connectio...

  • Page 124

    The following code uses connection technique 3 to create a connection to a location with logical name jdbc/sampledb . This example assumes that the system administrator created and deployed a datasource object that is available through jndi lookup. The numbers to the right of selected statements cor...

  • Page 125

    If the data source is a db2 for z/os system, and you do not specify these parameters, db2 uses the external security environment, such as the racf security environment, that was previously established for the user. For a cics connection, you cannot specify a user id or password. The following code u...

  • Page 126

    You use the default connection by specifying your sql statements without a connection context object. When you use this technique, you do not need to load a jdbc driver unless you explicitly use jdbc interfaces in your program. The default connection context can be: v the connection context that is ...

  • Page 127

    Host variables and host expressions are case sensitive. A complex expression is an array element or java expression that evaluates to a single value. A complex expression in an sqlj clause must be surrounded by parentheses. The following examples demonstrate how to use host expressions. Example: dec...

  • Page 128

    V the string __sjt_ is a reserved prefix for variable names that are generated by sqlj. Do not begin the following types of names with __sjt_: – host expression names – java variable names that are declared in blocks that include executable sql statements – names of parameters for methods that conta...

  • Page 129

    Connection-context specification in an executable clause, if you do not explicitly specify a connection context, the executable clause uses the default connection context. Related concepts “example of a simple sqlj application” on page 101 “comments in an sqlj application” on page 112 “data retrieva...

  • Page 130

    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 positione...

  • Page 131

    C. If the iterator is pointing to a row of the result table, execute an sql update... Where current of :iterator-object statement in an executable clause to update the columns in the current row. Execute an sql delete... Where current of :iterator-object statement in an executable clause to delete t...

  • Page 132

    The following code shows how to declare a named iterator and use it for positioned updates. The numbers to the right of selected statements correspond to the previously described steps. First, in one file, declare named iterator updbyname , specifying that you want to use the iterator to update colu...

  • Page 133

    Then, in another file, use updbyname for a positioned update, as shown in the following code fragment: related concepts “sql statement execution in sqlj applications” on page 112 “iterators as passed variables for positioned update or delete operations in an sqlj application” on page 118 “data retri...

  • Page 134

    “using a named iterator in an sqlj application” on page 124 “using a positioned iterator in an sqlj application” on page 126 related reference “sqlj implements-clause” on page 282 “sqlj with-clause” on page 283 “sqlj.Runtime.Forupdate interface” on page 298 iterators as passed variables for position...

  • Page 135

    You can avoid a bind time error for a program like the one in figure 36 on page 118 by specifying the bind option sqlerror(continue). However, this technique has the drawback that it causes the db2 database manager to build a package, regardless of the sql errors that are in the program. A better te...

  • Page 136

    V different statements v statements with different numbers of input parameters or host expressions v statements with different data types for input parameters or host expressions v statements with no input parameters or host expressions when an error occurs during execution of a statement in a batch...

  • Page 137

    Other integer this value is the number of rows that were updated by the statement. This value is returned if the statement was executed rather than added to a batch. 5. Execute the batch explicitly or implicitly. V invoke the executioncontext.Executebatch method to execute the batch explicitly. Exec...

  • Page 138

    Do this by invoking the executioncontext.Setbatching(false) method. When you disable batching, you can still execute the batch implicitly or explicitly, but no more statements are added to the batch. Disabling batching is useful when a batch already exists, and you want to execute a batch compatible...

  • Page 139

    “sqlj.Runtime.Sqlnullexception class” on page 314 data retrieval in sqlj applications sqlj applications use a result set iterator to retrieve result sets. Like a cursor, a result set iterator can be non-scrollable or scrollable. Just as in db2 applications in other languages, if you want to retrieve...

  • Page 140

    Using a named iterator in an sqlj application use a named iterator to refer to each of the columns in a result table by name. The steps in using a named iterator are: 1. Declare the iterator. You declare any result set iterator using an iterator declaration clause . This causes an iterator class to ...

  • Page 141

    When you declare an iterator in this way, you can instantiate it only within an instance of the nesting class. However, you can declare the iterator and other classes in the file as public. You cannot cast a jdbc resultset to an iterator if the iterator is declared as an inner class. This restrictio...

  • Page 142

    “performing positioned update and delete operations in an sqlj application” on page 114 “using a positioned iterator in an sqlj application” “using scrollable iterators in an sqlj application” on page 130 related reference “sqlj iterator-declaration-clause” on page 285 “sqlj.Runtime.Namediterator in...

  • Page 143

    V as a nested static class within another class using this alternative lets you combine the iterator declaration with other class declarations in the same source file, declare the iterator and other classes as public, and make the iterator class visible from other code modules or packages. However, ...

  • Page 144

    Related concepts “sql statement execution in sqlj applications” on page 112 “data retrieval in sqlj applications” on page 123 related tasks “using a named iterator in an sqlj application” on page 124 “performing positioned update and delete operations in an sqlj application” on page 114 “using scrol...

  • Page 145

    Without the capability for multiple, concurrently open iterators for a single sql statement, if you want to select employee and salary values for a specific employee number, you need to define a different sql statement for each employee number, as shown in figure 41. Figure 42 demonstrates how you c...

  • Page 146

    The following example shows an application with two concurrently open instances of an iterator. As with any other iterator, you need to remember to close this iterator after the last time you use it to prevent excessive storage consumption. Related concepts “data retrieval in sqlj applications” on p...

  • Page 147

    Same application process are immediately visible. Rows that are inserted or deleted with insert and delete statements that are executed by other application processes are visible after the changes are committed. Important: db2 database for linux, unix, and windows servers do not support dynamic scro...

  • Page 148

    A. Position the cursor using one of the methods listed in the following table. Table 20. Sqlj.Runtime.Scrollable methods for positioning a scrollable cursor method positions the cursor first 1 on the first row of the result table last 1 on the last row of the result table previous 1,2 on the previou...

  • Page 149

    Table 21. Fetch clauses for positioning a scrollable cursor (continued) method positions the cursor relative( n ) 1,4 if n >0, on the row that is n rows after the current row. If n n rows before the current row. If n =0, on the current row. After 1,5 after the last row in the result table before 1,5...

  • Page 150

    “sqlj.Runtime.Scrollable interface” on page 302 calling stored procedures in an sqlj application to call a stored procedure, you use an executable clause that contains an sql call statement. You can execute the call statement with host identifier parameters. You can execute the call statement with l...

  • Page 151

    2. Associate the execution context with the call statement for the stored procedure. Do not use this execution context for any other purpose until you have retrieved and processed the last result set. 3. For each result set: a. Use the executioncontext method getnextresultset to retrieve the result ...

  • Page 152

    Related concepts “sql statement execution in sqlj applications” on page 112 related tasks “calling stored procedures in an sqlj application” on page 134 “writing a java stored procedure to return result sets” on page 178 lobs in sqlj applications with the ibm data server driver for jdbc and sqlj wit...

  • Page 153

    Java data types for retrieving or updating lob column data in sqlj applications when the deferprepares property is set to true, and the ibm data server driver for jdbc and sqlj processes an uncustomized sqlj statement that includes host expressions, the driver might need to do extra processing to de...

  • Page 154

    Input parameters for clob columns for input parameters for clob columns, you can use one of the following techniques: v use a java.Sql.Clob input variable, which is an exact match for a clob column: #sql call storproc(:in clobdata)}; before you can use a java.Sql.Clob input variable, you need to cre...

  • Page 155

    This technique should be used only if you know that the length of the retrieved data is less than or equal to 32kb. Otherwise, the data is truncated. Output parameters for dbclob columns dbclob output or input/output parameters for stored procedures are not supported. Related concepts “lobs in sqlj ...

  • Page 156

    V if the iterator is a positioned iterator, the number of columns in the result set must match the number of columns in the iterator. In addition, the data type of each column in the result set must match the data type of the corresponding column in the iterator. V if the iterator is a named iterato...

  • Page 157

    Table. Notes to figure 47: note description 1 this sqlj clause executes the select statement, constructs an iterator object that contains the result table for the select statement, and assigns the iterator object to variable iter. 2 the getresultset() method converts iterator iter to resultset rs. 3...

  • Page 158

    Related concepts “sql statement execution in sqlj applications” on page 112 related tasks “connecting to a data source using sqlj” on page 103 related reference “sqlj with-clause” on page 283 “sqlj assignment-clause” on page 291 “sqlj.Runtime.Connectioncontext interface” on page 293 ″ describe for s...

  • Page 159

    Rowids in sqlj with the ibm data server driver for jdbc and sqlj db2 for z/os and db2 for i5/os support the rowid data type for a column in a table. A rowid is a value that uniquely identifies a row in a table. Although ibm informix dynamic server (ids) also supports rowids, those rowids have the in...

  • Page 160

    Related reference “data types that map to database data types in java applications” on page 193 “db2rowid interface” on page 352 distinct types in sqlj applications in an sqlj program, you can create a distinct type using the create distinct type statement in an executable clause. You can also use c...

  • Page 161

    Savepoints in sqlj applications under the ibm data server driver for jdbc and sqlj, you can include any form of the sql savepoint statement in your sqlj program. An sql savepoint represents the state of data and schemas at a particular point in time within a unit of work. Sql statements exist to set...

  • Page 162

    In applications, xml data is in the serialized string format. In sqlj applications, you can: v store an entire xml document in an xml column using insert or update statements. V retrieve an entire xml document from an xml column using single-row select statements or iterators. V retrieve a sequence ...

  • Page 163

    V if the data source is db2 database for linux, unix, and windows, the data source generates an error if the external and internal encoding are incompatible, unless the external and internal encoding are unicode. If the external and internal encoding are unicode, the data source ignores the internal...

  • Page 164

    Java.Sql.Resultset rs = s.Executequery ("select * from custacc"); rs.Next(); java.Sql.Sqlxml xmlobject = (java.Sql.Sqlxml)rs.Getobject(2); #sql [ctx] {insert into custacc values (6, :xmlobject)}; after you retrieve the data it is still in utf-8 encoding, so when you insert the data into another xml ...

  • Page 165

    Table 22. Sqlxml and db2xml methods, data types, and added encoding specifications (continued) method output data type type of xml internal encoding declaration added sqlxml.Getcharacterstream reader none sqlxml.Getsource source none sqlxml.Getstring string none db2xml.Getdb2asciistream inputstream ...

  • Page 166

    The fetch statement retrieves the data into the sqlxml object in utf-8 encoding. The sqlxml.Getbinarystream stores the data in a binary stream. Example: suppose that you retrieve a document from an xml column into a com.Ibm.Db2.Jcc.Db2xml host expression, but you need the data in a byte string with ...

  • Page 167

    Annotations java annotations are a means for adding metadata to java programs that can also affect the way that those programs are treated by tools and libraries. Annotations are declared with annotation type declarations, which are similar to interface declarations. Java annotations can appear in t...

  • Page 168

    Public class testenum2 { public enum color { red,orange,yellow,green,blue,indigo,violet} color color = null; switch (color) { case red: system.Out.Println("red is at one end of the spectrum."); #sql[ctx] { insert into mytable values (:color) }; break; case violet: system.Out.Println("violet is on th...

  • Page 169

    Example: pass an arbitrary number of parameters of type object , to a method that inserts each parameter value into table tab. Public void runthis(object... Objects) throws sqlexception { for (object obj : objects) { #sql { insert into tab (varcharcol) values(:obj) }; } } transaction control in sqlj...

  • Page 170

    Related concepts “sql statement execution in sqlj applications” on page 112 “savepoints in sqlj applications” on page 145 related tasks “committing or rolling back sqlj transactions” on page 153 “connecting to a data source using sqlj” on page 103 handling sql errors and warnings in sqlj application...

  • Page 171

    Related reference “error codes issued by the ibm data server driver for jdbc and sqlj” on page 382 “sqlstates issued by the ibm data server driver for jdbc and sqlj” on page 388 handling sql warnings in an sqlj application other than a +100 sql error code on a select into statement, db2 warnings do ...

  • Page 172

    Closing the connection to a data source in an sqlj application when you have finished with a connection to a data source, you need to close the connection to the data source. Doing so releases the connection context object’s db2 and sqlj resources immediately. To close the connection to the data sou...

  • Page 173

    Chapter 5. Java stored procedures and user-defined functions like stored procedures and user-defined functions in any other language, java stored procedures and user-defined functions are programs that can contain sql statements. You invoke java stored procedures from a client program that is writte...

  • Page 174

    2. Create the workload manager for z/os (wlm) application environment for running the routines. 3. Set up the run-time environment for java routines, which includes setting environment variables. Setting up the wlm application environment for java routines to set up wlm application environments for ...

  • Page 175

    3 specifies a data set into which db2 puts information that you can use to debug your stored procedure. The information that db2 collects is for assistance in debugging setup problems, and should be used only under the direction of ibm software support. You should comment out this dd statement durin...

  • Page 176

    Procedure name this name must match the name of the jcl startup procedure for the stored procedure address spaces that are associated with this application environment. Start parameters if the db2 subsystem in which the stored procedure runs is not in a sysplex, the db2ssn value must match the name ...

  • Page 177

    Msgfile( , , , , ) ddname recfm lrecl blksize noenq enq the maximum length of the language environment run-time options string in a javaenv data set for java stored procedures is 245 bytes. If you exceed the maximum length, db2 truncates the contents but does not issue a message. If you enter the co...

  • Page 178

    The default is /usr/lpp/db2910_base. Jcc_home the value of jcc_home is the highest-level directory in the set of directories that contain the jdbc driver. For example: jcc_home=/usr/lpp/db2910_base jcc_home must be set. Java_home this environment variable indicates to db2 that the wlm environment is...

  • Page 179

    The default is gmt. Work_dir modify work_dir to change the default destination for stdout and stderr output. _cee_envfile specifies a z/os unix system services data set that contains some or all of the settings for environment variables. Use the _cee_envfile parameter if the length of environment va...

  • Page 180

    “preparing java routines with no sqlj clauses and no jar file” on page 188 “preparing java routines with sqlj clauses and no jar file” on page 190 “installing the z/os application connectivity to db2 for z/os feature” on page 435 “upgrading the ibm data server driver for jdbc and sqlj to a new versi...

  • Page 181

    Statement. For java routines, the argument of external name is a string that is enclosed in single quotation marks. The external name clause for a java routine has the following syntax: external name ’ class-name . Method-name ’ (1) (2) ( method-signature ) jar-name : package-name . Notes: 1 for com...

  • Page 182

    Has one input parameter of type integer and returns one result set. The java method for the stored procedure receives one parameter of type java.Lang.Integer, but the default java data type for an sql type of integer is int, so the external name clause requires a signature clause. The external name ...

  • Page 183

    The value of the security parameter determines the authorization id that must have authority to access z/os unix system services. The values of security and the ids that must have access to z/os unix system services are: db2 the user id that is defined for the stored procedure address space in the r...

  • Page 184

    Wlm environment wlmijav dynamic result sets 1 program type sub parameter style java; example: defining a java user-defined function: suppose that you have written and prepared a user-defined function that has these characteristics: fully-qualified function name myschema.S2sal input parameter integer...

  • Page 185

    Sqlj.Db2_install_jar installs a jar file into the local db2 catalog or a remote db2 catalog. Sqlj.Replace_jar replaces an existing jar file in the local db2 catalog. Sqlj.Db2_replace_jar replaces an existing jar file in the local db2 catalog or a remote db2 catalog. Sqlj.Remove_jar deletes a jar fil...

  • Page 186

    Sqlj.Install_jar parameters url a varchar(1024) input parameter that identifies the z/os unix system services full path name for the jar file that is to be installed in the db2 catalog. The format is file:// path-name or file:/ path-name . Jar-name a varchar(257) input parameter that contains the db...

  • Page 187

    Jar-name a varchar(257) input parameter that contains the db2 name of the jar, in the form schema.Jar-id or jar-id . This is the name that you use when you refer to the jar in sql statements. If you omit schema , db2 uses the sql authorization id that is in the current schema special register. The o...

  • Page 188

    To replace a jar file at a remote location, you need to execute a connect statement to connect to that location before you call sqlj.Db2_replace_jar. Sqlj.Db2_replace_jar authorization privilege set: if the call statement is embedded in an application program, the privilege set is the privileges tha...

  • Page 189

    The plan or package. If the statement is dynamically prepared, the privilege set is the privileges that are held by the authorization ids of the process. For calling sqlj.Remove_jar, the privilege set must include at least one of the following items: v the execute privilege on sqlj.Remove_jar v owne...

  • Page 190

    V ownership of the jar v the alterin privilege on the schema of the jar the authorization id that matches the schema name implicitly has the alterin privilege on the schema. V sysadm or sysctrl authority for referring to jar jar2 in the java path, the privilege set must include at least one of the f...

  • Page 191

    Path a varchar(2048) input parameter that specifies the class resolution path that the jvm uses when jar-name references a class that is not contained in jar-name , found in the classpath, or system-supplied. During execution of the java routine, when db2 encounters an unresolved class reference, db...

  • Page 192

    Differences between java routines and stand-alone java programs there are a few basic difference between java routines and stand-alone java programs. Those differences are: v in a java routine, a jdbc connection or an sqlj connection context can use the connection to the data source that processes t...

  • Page 193

    – set connection v routine parameters have different mappings to host language data types than the mappings of routine parameters to host language parameters for other languages. V the technique for returning result sets from java stored procedures is different from the technique for returning resul...

  • Page 194

    In certain cases, you need to declare variables as static and non-final. In those cases, you can use the following technique to make your routines work correctly with static variables. To determine whether the values of static data in a routine have persisted across routine invocations, define a sta...

  • Page 195

    Notes to figure 53: 1 this sqlj clause declares the iterator named namesal, which is used to retrieve the rows that will be returned to the stored procedure caller in a result set. 2 the declaration for the stored procedure method contains declarations for a single passed parameter, followed by the ...

  • Page 196

    Test your routine as a stand-alone program before you invoke your java routines from sql applications, it is a good idea to run the routines as stand-alone programs, which are easier to debug. A java program that runs as a routine requires only a db2 package. However, before you can run the program ...

  • Page 197

    Stderr and stdout output is written to the directory that is specified by the work_dir parameter in the javaenv data set, if that directory exists. If no work_dir parameter is specified, output goes to the default directory, /tmp/java, if that directory exists. Related concepts “run-time environment...

  • Page 198

    182 application programming guide and reference for java ™.

  • Page 199

    Chapter 6. Preparing and running jdbc and sqlj programs db2 for z/os java programs run in the z/os unix system services environment. The following topics contain information about preparing and running java programs. Program preparation for jdbc programs preparing a java program that contains only j...

  • Page 200

    The basic steps in sqlj program preparation are: 1. Run the sqlj command from the z/os unix system services command line to translate and compile the source code. The sqlj command generates a java source program, optionally compiles the java source program, and produces zero or more serialized profi...

  • Page 201

    Use the db2 bind command to do that. Related tasks “binding sqlj applications to access multiple database servers” “preparing java routines with sqlj clauses to run from a jar file” on page 188 “preparing java routines with sqlj clauses and no jar file” on page 190 related reference “sqlj - sqlj tra...

  • Page 202

    An existing sqlj application was customized and bound using the following db2sqljcustomize invocation: db2sqljcustomize -url jdbc:db2://system1.Svl.Ibm.Com:8000/zos1 -user user01 -password mypass -rootpkgname wrksqlj -qualifier wrk1 -collection mycol1 -bindoptions "currentdata no qualifier wrk1 " -s...

  • Page 203

    Related tasks “program preparation for sqlj programs” on page 183 related reference “db2sqljbind - sqlj profile binder” on page 407 program preparation for java routines the program preparation process for java routines varies, depending on whether the routines contain sqlj clauses. The following to...

  • Page 204

    “definition of a jar file for a java routine to db2” on page 168 related tasks “creating jar files for java routines” on page 191 preparing java routines with no sqlj clauses and no jar file the program preparation process for java routines that contain no sqlj clauses and do not run from a jar file...

  • Page 205

    2. Run the db2sqljcustomize command to produce serialized profiles that are customized for db2 for z/os and db2 packages. 3. Run the jar command to package the class files that contain the methods for your routine, and the profiles that you generated in step 2 into a jar file. See ″ creating jar fil...

  • Page 206

    S1sal.Jar 4. Call the install_jar stored procedure, which is on db2 for z/os, to define the jar file to db2. You need to execute the call statement from a static sql program or from an odbc or jdbc program. The call statement looks similar to this: call sqlj.Install_jar('file:/u/db2res3/s1sal.Jar','...

  • Page 207

    The steps in the process are: 1. Run the sqlj command to translate the source code to produce generated java source code and serialized profiles, and to compile the java program to produce java bytecodes. 2. Run the db2sqljcustomize command to produce serialized profiles that are customized for db2 ...

  • Page 208

    F specifies that the argument immediately after the options list is the name of the jar file to be created. For example, to create a jar file named acmejos.Jar from add_customer.Class, which is in package acmejos, execute this jar command: jar -cvf acmejos.Jar acmejos/add_customer.Class to create a ...

  • Page 209

    Chapter 7. Jdbc and sqlj reference information the ibm implementations of jdbc and sqlj provide a number of application programming interfaces, properties, and commands for developing jdbc and sqlj applications. Data types that map to database data types in java applications to write efficient jdbc ...

  • Page 210

    Table 25. Mappings of java data types to database server data types for updating database tables (continued) java data type database data type java.Sql.Blob blob java.Sql.Blob xml 10 java.Sql.Clob clob java.Sql.Clob dbclob 9 java.Sql.Clob xml 10 java.Sql.Date date java.Sql.Time time java.Sql.Timesta...

  • Page 211

    Table 26. Mappings of database server data types to java data types for retrieving data from database server tables sql data type recommended java data type or java object type other supported java data types smallint short byte, int, long, float, double, java.Math.Bigdecimal, boolean, java.Lang.Str...

  • Page 212

    Table 26. Mappings of database server data types to java data types for retrieving data from database server tables (continued) sql data type recommended java data type or java object type other supported java data types dbclob( m ) no exact equivalent. Use java.Sql.Clob. Rowid java.Sql.Rowid byte[]...

  • Page 213

    Table 27. Mappings of java, jdbc, and sql data types for calling stored procedures and user-defined functions (continued) java data type jdbc data type sql data type 1 java.Math.Bigdecimal decimal decimal java.Math.Bigdecimal java.Types.Other decfloat n 3 java.Math.Bigdecimal com.Ibm.Db2.Jcc.Db2type...

  • Page 214

    Table 27. Mappings of java, jdbc, and sql data types for calling stored procedures and user-defined functions (continued) java data type jdbc data type sql data type 1 notes: 1. A db2 for z/os stored procedure or user-defined function parameter cannot have the xml data type. 2. A stored procedure or...

  • Page 215

    Table 28. Mappings of sql data types in a create procedure or create function statement to data types in the corresponding java stored procedure or user-defined function program (continued) sql data type in create procedure or create function 1 data type in java stored procedure or user-defined func...

  • Page 216

    Date, time, and timestamp values that can cause problems in jdbc and sqlj applications you might receive unexpected results in jdbc and sqlj applications if you use date, time, and timestamp values that do not correspond to real dates and times. In particular, using the hour ’24’ to represent midnig...

  • Page 217

    Table 29. Examples of updating date, time, or timestamp sql values with java date, time, or timestamp values that do not represent real dates or times (continued) string input value target type in database value sent to table column, or exception 25:00:00 time 01:00:00 24:00:00 time 00:00:00 2008-15...

  • Page 218

    V in a java.Util.Properties value in the info parameter of a drivermanager.Getconnection call. V in a java.Lang.String value in the url parameter of a drivermanager.Getconnection call. Some properties with an int data type have predefined constant field values. You must resolve constant field values...

  • Page 219

    ″ signon function for rrsaf ″ (db2 application programming and sql guide) related reference “jdbc differences between the current ibm data server driver for jdbc and sqlj and earlier db2 jdbc drivers” on page 370 “ibm data server driver for jdbc and sqlj extensions to jdbc” on page 316 “db2basedatas...

  • Page 220

    If more than one server name is specified, delimit the server names with commas (,) or spaces. The number of values that is specified for clientreroutealternateservername must match the number of values that is specified for clientreroutealternateportnumber. Clientreroutealternateservername applies ...

  • Page 221

    V if the connection is to a db2 for z/os server, the databasename value is the db2 location name that is defined during installation. All characters in this value must be uppercase characters. You can determine the location name by executing the following sql statement on the server: select current ...

  • Page 222

    Deferprepares is supported for ibm data server driver for jdbc and sqlj type 2 connectivity to db2 database for linux, unix, and windows, and for ibm data server driver for jdbc and sqlj type 4 connectivity. Possible values are: true statement preparation on the data source does not occur until the ...

  • Page 223

    Also suppose that clientreroutealternateportnumber contains the following string: port1,port2,port3 when client affinities are enabled, the retry order is: 1. Host1:port1 2. Host2:port2 3. Host3:port3 db2basedatasource.No (2) the ibm data server driver for jdbc and sqlj does not enable client affini...

  • Page 224

    Db2basedatasource.Not_set (0) the ibm data server driver for jdbc and sqlj does not use seamless failover. This is the default. Fetchsize specifies the number of result set rows that is the default fetch size for resultset objects that are generated from statement objects. The data type of this prop...

  • Page 225

    This property has no effect on stored procedure parameters or on lobs that are fetched using scrollable cursors. Lob stored procedure parameters are always fully materialized. Lobs that are fetched using scrollable cursors use lob locators if progressive streaming is not in effect. Logintimeout the ...

  • Page 226

    With progressive streaming, also known as dynamic data format, the data source dynamically determines the most efficient mode in which to return lob or xml data, based on the size of the lobs or xml objects. The value of the streambuffersize parameter determines whether the data is materialized when...

  • Page 227

    Clear_text_password_security (3) user id and password user_only_security (4) user id only encrypted_password_security (7) user id, encrypted password encrypted_user_and_password_security (9) encrypted user id and password kerberos_security (11) kerberos. This value does not apply to connections to i...

  • Page 228

    Sslconnection specifies whether the ibm data server driver for jdbc and sqlj uses an ssl socket to connect to the data source. If sslconnection is set to true , the connection uses an ssl socket. If sslconnection is set to false , the connection uses a plain socket. This property is applicable only ...

  • Page 229

    For example: v a timestamp value of 2009-07-19-10.12.00.000000 is truncated to 2009-07-19-10.12.00.0 after retrieval. V a timestamp value of 2009-12-01-11.30.00.100000 is truncated to 2009-12-01-11.30.00.1 after retrieval. Timestamp_zero_padding (2) trailing zeroes are not truncated in the result of...

  • Page 230

    V com.Ibm.Db2.Jcc.Db2basedatasource.Trace_driver_configuration (x’10’) v com.Ibm.Db2.Jcc.Db2basedatasource.Trace_connects (x’20’) v com.Ibm.Db2.Jcc.Db2basedatasource.Trace_drda_flows (x’40’) v com.Ibm.Db2.Jcc.Db2basedatasource.Trace_result_set_meta_data (x’80’) v com.Ibm.Db2.Jcc.Db2basedatasource.Tr...

  • Page 231

    0 or negative integer disables internal statement caching for the pooledconnection . 0 is the default. Maxstatements controls the internal statement cache that is associated with a pooledconnection only when the pooledconnection object is created. Maxstatements has no effect on caching in an already...

  • Page 232

    On a db2 for z/os server. Database administrators can use this property to correlate work on a db2 for z/os server to client applications. The data type of this property is string. The maximum length is 12 bytes. If this value is null , the ibm data server driver for jdbc and sqlj supplies a value o...

  • Page 233

    Multiple instances of the ibm data server driver for jdbc and sqlj can be installed at a database server by running the db2binder utility multiple times. The db2binder utility includes a -collection option that lets the installer specify the collection id for each ibm data server driver for jdbc and...

  • Page 234

    Constant integer value format com.Ibm.Db2.Jcc.Db2basedatasource.Jis 4 yyyy-mm-dd the default is com.Ibm.Db2.Jcc.Db2basedatasource.Iso . Decimalroundingmode specifies the rounding mode for decimal floating-point values on db2 for z/os version 9 or later, or db2 database for linux, unix, and windows d...

  • Page 235

    Enablerowsetsupport specifies whether the ibm data server driver for jdbc and sqlj uses multiple-row fetch for forward-only cursors or scrollable cursors, if the data source supports multiple-row fetch. The data type of this property is int. When enablerowsetsupport is set, its value overrides the u...

  • Page 236

    If the value of fullymaterializeinputstreams is true , the jdbc driver fully materialized the streams before sending them to the server. Gsscredential for a data source that uses kerberos security, specifies a delegated credential that is passed from another principal. The data type of this property...

  • Page 237

    Db2basedatasource.Not_set (0) use the resultsetholdability setting for catalog queries. This is the default value. Valid values are hold_cursors_over_commit (1) or close_cursors_at_commit (2). These values are the same as the resultset.Hold_cursors_over_commit and resultset.Close_cursors_at_commit c...

  • Page 238

    Possible values of timeformat are: constant integer value format com.Ibm.Db2.Jcc.Db2basedatasource.Iso 1 hh:mm:ss com.Ibm.Db2.Jcc.Db2basedatasource.Usa 2 hh:mm am or hh:mm pm com.Ibm.Db2.Jcc.Db2basedatasource.Eur 3 hh.Mm.Ss com.Ibm.Db2.Jcc.Db2basedatasource.Jis 4 hh:mm:ss the default is com.Ibm.Db2....

  • Page 239

    Usejdbc4columnnameandlabelsemantics is set to com.Ibm.Db2.Jcc.Db2basedatasource.Yes. Com.Ibm.Db2.Jcc.Db2basedatasource.No (2) the ibm data server driver for jdbc and sqlj uses the following rules to determine the values that resultsetmetadata.Getcolumnname , resultsetmetadata.Getcolumnlabel , and re...

  • Page 240

    Positive integer enables the internal statement cache for a pooledconnection , and specifies the number of statements that the ibm data server driver for jdbc and sqlj keeps open in the cache. 0 or negative integer disables internal statement caching for the pooledconnection . 0 is the default. Maxs...

  • Page 241

    For jdbc and sqlj uses transport objects and a global transport objects pool to support the connection concentrator and sysplex workload balancing. There is one transport object for each physical connection to the data source. The data type of this property is int. The maxtransportobjects value is i...

  • Page 242

    Currentlocktimeout specifies whether db2 database for linux, unix, and windows servers wait for a lock when the lock cannot be obtained immediately. The data type of this property is int. Possible values are: integer wait for integer seconds . Integer is between -1 and 32767, inclusive. Lock_timeout...

  • Page 243

    Currentexplainmode specifies the value for the current explain mode special register. The current explain mode special register enables and disables the explain facility. The data type of this property is string. The maximum length is 254 bytes. This property applies only to connections to data sour...

  • Page 244

    Optimizationprofile applies to db2 database for linux, unix, and windows servers only. Optimizationprofiletoflush specifies the name of an optimization profile that is to be removed from the optimization profile cache. The data type of this property is string. The default is null. Plugin the name of...

  • Page 245

    Accountinginterval specifies whether db2 accounting records are produced at commit points or on termination of the physical connection to the data source. The data type of this property is string. If the value of accountinginterval is ″ commit ″ , and there are no open, held cursors, db2 writes an a...

  • Page 246

    For client accounting purposes. Unlike the jdbc connection user name, this value can change during a connection. For a db2 for z/os server, the maximum length is 16 bytes. This property applies only to ibm data server driver for jdbc and sqlj type 2 connectivity on db2 for z/os. Clientworkstation sp...

  • Page 247

    Specify this property if you do not bind plans for your sqlj programs or for the jdbc driver. If you specify this property, do not specify planname. Recommendation: use pklist instead of planname. The format of the package list is: , collection-id . * pklist overrides the value of the db2.Jcc.Pklist...

  • Page 248

    Character input data to the ccsid of the db2 for z/os database server, or sends the data in utf-8 encoding for conversion by the database server. Sendcharinputsutf8 applies to ibm data server driver for jdbc and sqlj type 2 connectivity to db2 for z/os database servers only. The data type of this pr...

  • Page 249

    Ssid specifies the name of the local db2 for z/os subsystem to which a connection is established using ibm data server driver for jdbc and sqlj type 2 connectivity on db2 for z/os. The data type of this property is string. The ssid property overrides the db2.Jcc.Ssid configuration property. Ssid can...

  • Page 250

    Ids database to return an sqlwarning to the application if an sql statement does not use ansi-standard syntax. The data type of this property is boolean. Possible values are: false or 0 do not send a value to the ids database that instructs the database to return an sqlwarning to the application if ...

  • Page 251

    False the application cannot contain delimited sql identifiers. Double quotation marks ( ″ ) or single quotation marks (’) delimit literal strings. This is the default. True the application can contain delimited sql identifiers. Delimited sql identifiers must be enclosed in double quotation marks ( ...

  • Page 252

    If this property is not set, no value is sent to the server. The value for the informixstacksize environment variable is used. Nodefdac specifies whether the database server prevents default table privileges (select, insert, update, and delete) from being granted to public when a new table is create...

  • Page 253

    ″ off ″ or ″ 0 ″ parallel processing is disabled. If this property is not set, no value is sent to the server. The value for the pdqpriority environment variable is used. Psort_dbtemp specifies the full path name of a directory in which the database server writes temporary files that are used for a ...

  • Page 254

    Dumppoolstatisticsonschedule specifies how often, in seconds, global transport pool statistics are written to the file that is specified by dumppoolstatisticsonschedulefile. The global transport object pool is used for the connection concentrator and sysplex workload balancing. The default is -1. -1...

  • Page 255

    Ibm data server driver for jdbc and sqlj configuration properties the ibm data server driver for jdbc and sqlj configuration properties have driver-wide scope. The following table summarizes the configuration properties and corresponding connection or datasource properties, if they exist. Table 33. ...

  • Page 256

    Table 33. Summary of configuration properties and corresponding connection and datasource properties (continued) configuration property name connection or datasource property name: com.Ibm.Db2.Jcc.Db2basedatasource. ... Notes db2.Jcc.Progressivestreaming progressivestreaming 1, 4, 5, 6 db2.Jcc.Overr...

  • Page 257

    Db2.Jcc.Allowsqljduplicatestaticqueries specifies whether multiple open iterators on a single select statement in an sqlj application are allowed under ibm data server driver for jdbc and sqlj type 2 connectivity. To enable this support, set db2.Jcc.Allowsqljduplicatestaticqueries to yes or true. Db...

  • Page 258

    Db2.Jcc.Decimalroundingmode or db2.Jcc.Override.Decimalroundingmode specifies the rounding mode for decimal or decimal floating-point values on db2 for z/os or db2 database for linux, unix, and windows database servers, and for decimal values on all other data sources that support the decimal data t...

  • Page 259

    Db2.Jcc.Defaultsqlstate if db2.Jcc.Defaultsqlstate is specified with no value, the ibm data server driver for jdbc and sqlj returns ’fffff’. Db2.Jcc.Defaultsqlstate= xxxxx xxxxx is the value that the ibm data server driver for jdbc and sqlj returns when the sqlstate value is null. If xxxxx is longer...

  • Page 260

    The file that is specified by db2.Jcc.Dumppoolstatisticsonschedulefile. The global transport object pool is used for the connection concentrator and sysplex workload balancing. The default is -1. -1 means that global transport pool statistics are not written. Db2.Jcc.Dumppoolstatisticsonschedulefile...

  • Page 261

    Db2.Jcc.Maxtransportobjectwaittime specifies the maximum amount of time in seconds that an application waits for a transport object if the db2.Jcc.Maxtransportobjects value has been reached. Transport objects are used for the connection concentrator and sysplex workload balancing. When an applicatio...

  • Page 262

    The following example specifies a package list for a ibm data server driver for jdbc and sqlj instance whose packages are in collection jdbccid. Sqlj applications that are prepared under this driver instance are bound into collections sqljcid1, sqljcid2, or sqljcid3. Db2.Jcc.Pklist=jdbccid.*,sqljcid...

  • Page 263

    To force a rollback operation and disables further operations on jdbc connections that are in a unit of work during processing of jvm shutdown hooks. Any other value the ibm data server driver for jdbc and sqlj takes no action with respect to rollback processing during processing of jvm shutdown hoo...

  • Page 264

    Driver does the conversion, and the target ccsid is a single-byte ccsid, the driver needs to allocate only the maximum number of characters in the input data. For example, any of the following settings for db2.Jcc.Sendcharinputsutf8 causes the ibm data server driver for jdbc and sqlj to convert inpu...

  • Page 265

    You should set the trace properties under the direction of ibm software support. Db2.Jcc.Tracelevel or db2.Jcc.Override.Tracelevel specifies what to trace. The db2.Jcc.Override.Tracelevel property overrides the tracelevel property for a connection or datasource object. You specify one or more trace ...

  • Page 266

    Db2.Jcc.Tracefile or db2.Jcc.Override.Tracefile enables the ibm data server driver for jdbc and sqlj trace for java driver code, and specifies the name on which the trace file names are based.The db2.Jcc.Tracefile property does not apply to ibm data server driver for jdbc and sqlj type 2 connectivit...

  • Page 267

    Set before the driver is loaded and initialized. Changes to db2.Jcc.Tracepollinginterval after the driver is loaded and initialized have no effect. Db2.Jcc.T2zostracefile enables the ibm data server driver for jdbc and sqlj trace for c/c++ native driver code for ibm data server driver for jdbc and s...

  • Page 268

    1 wrap the trace 0 do not wrap the trace the default is 1. This parameter is optional. For example: db2sqlj_trace_wrap=0 you should set db2.Jcc.T2zostracewrap only under the direction of ibm software support. Db2.Jcc.Useccsid420shapedconverter specifies whether arabic character data that is in ebcdi...

  • Page 269

    Table 35. Support for array methods jdbc method ibm data server driver for jdbc and sqlj1 support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support free 2 yes no no getarray yes no yes getbasetype yes no yes getbasetypename yes no yes getresultset yes no yes notes: 1...

  • Page 270

    Table 38. Support for callablestatement methods jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support methods inherited from java.Sql.Statement yes yes yes methods inherited from java.Sql.Preparedstatement yes ...

  • Page 271

    Table 38. Support for callablestatement methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support setshort yes 7 no yes setstring yes 7 no yes settime yes 7 no yes settimestamp yes 7 no yes setu...

  • Page 272

    Table 39. Support for clob methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support notes: 1. This is a jdbc 4.0 method. 2. Supported forms of this method include the following jdbc 4.0 form: g...

  • Page 273

    Table 40. Support for connection methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support settypemap no no yes notes: 1. This is a jdbc 4.0 method. 2. The db2 jdbc type 2 driver for linux, unix...

  • Page 274

    Table 44. Support for databasemetadata methods jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support allproceduresarecallable yes yes yes alltablesareselectable yes 1 yes yes 1 datadefinitioncausestransactionco...

  • Page 275

    Table 44. Support for databasemetadata methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support getmaxcharliterallength yes yes yes getmaxcolumnnamelength yes yes yes getmaxcolumnsingroupby yes...

  • Page 276

    Table 44. Support for databasemetadata methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support gettableprivileges yes yes yes gettables yes yes 10 yes 10 gettabletypes yes yes yes gettimedatef...

  • Page 277

    Table 44. Support for databasemetadata methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support supportscatalogsindatamanipulation yes 1 yes yes 1 supportscatalogsinindexdefinitions yes yes yes...

  • Page 278

    Table 44. Support for databasemetadata methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support supportspositioneddelete yes yes yes supportspositionedupdate yes yes yes supportsresultsetconcur...

  • Page 279

    Table 44. Support for databasemetadata methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support notes: 1. Db2 data sources return false for this method. Ids data sources return true . 2. This m...

  • Page 280

    Table 46. Support for datatruncation methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support getdatasize yes yes yes getindex yes yes yes getparameter yes yes yes getread yes yes yes gettransf...

  • Page 281

    Table 49. Support for parametermetadata methods jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support getparameterclassname no no no getparametercount yes no no getparametermode yes no no getparametertype yes n...

  • Page 282

    Table 51. Support for preparedstatement methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support setblob yes 4 yes yes setboolean yes yes yes setbyte yes yes yes setbytes yes yes yes setcharact...

  • Page 283

    Table 51. Support for preparedstatement methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support notes: 1. If the value of the length parameter is -1, all of the data from the inputstream or re...

  • Page 284

    Table 53. Support for resultset methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support getasciistream yes yes yes getbigdecimal yes yes yes getbinarystream yes 1 yes yes getblob yes yes yes g...

  • Page 285

    Table 53. Support for resultset methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support movetocurrentrow yes no no movetoinsertrow yes no no next yes yes yes previous yes yes yes refreshrow ye...

  • Page 286

    Table 53. Support for resultset methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support notes: 1. Getbinarystream is not supported for clob columns. 2. Getmetadata pads the schema name, if the...

  • Page 287

    Table 54. Support for resultsetmetadata methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support getprecision yes yes yes getscale yes yes yes getschemaname yes yes yes gettablename yes 1 yes y...

  • Page 288

    Table 57. Support for sqldata methods jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support getsqltypename no no no readsql no no no writesql no no no table 58. Support for sqldataexception methods 1 jdbc metho...

  • Page 289

    Table 61. Support for sqlinput methods jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support readarray no no no readasciistream no no no readbigdecimal no no no readbinarystream no no no readblob no no no readb...

  • Page 290

    Table 63. Support for sqlinvalidauthorizationspecexception methods 1 jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support methods inherited from java.Lang.Exception yes no no methods inherited from java.Lang.T...

  • Page 291

    Table 66. Support for sqloutput methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support writeboolean no no no writebyte no no no writebytes no no no writecharacterstream no no no writeclob no ...

  • Page 292

    Table 68. Support for sqlsyntaxerrorexception methods 1 (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support note: 1. This is a jdbc 4.0 class. Table 69. Support for sqltimeoutexception methods 1 j...

  • Page 293

    Table 72. Support for sqltransientrollbackexception methods 1 jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support methods inherited from java.Lang.Exception yes no no methods inherited from java.Lang.Throwabl...

  • Page 294

    Table 74. Support for statement methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support getgeneratedkeys yes no no getmaxfieldsize yes yes yes getmaxrows yes yes yes getmoreresults yes yes 3 y...

  • Page 295

    Table 74. Support for statement methods (continued) jdbc method ibm data server driver for jdbc and sqlj support db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support notes: 1. For the ibm data server driver for jdbc and sqlj, statement.Cancel() is supported only in the ...

  • Page 296

    Table 77. Support for javax.Sql.Xaconnection methods jdbc method ibm data server driver for jdbc and sqlj support 1 db2 jdbc type 2 driver for linux, unix and windows support ids jdbc driver support methods inherited from javax.Sql.Pooledconnection yes yes yes getxaresource yes yes yes notes: 1. The...

  • Page 297

    “lobs in jdbc applications with the ibm data server driver for jdbc and sqlj” on page 49 related tasks “learning about a data source using databasemetadata methods” on page 21 “creating and modifying database objects using the statement.Executeupdate method” on page 24 “updating data in tables using...

  • Page 299

    Positioned update and delete operations in an sqlj application ″ for information on performing a positioned update or positioned delete operation in sqlj. You need to implement sqlj.Runtime.Scrollable when you declare a scrollable iterator. See ″ use scrollable iterators in an sqlj application ″ for...

  • Page 300

    V when the application executes positioned update and delete statements with the iterator, those changes are visible to the iterator. V when the application executes insert, update, and delete statements within the application but outside the iterator, those changes are visible to the iterator. The ...

  • Page 301

    Sqlj connection-declaration-clause the connection declaration clause declares a connection to a data source in an sqlj application program. Syntax java-modifiers context java-class-name implements-clause with-clause description java-modifiers specifies modifiers that are valid for java class declara...

  • Page 302

    Syntax java-modifiers iterator java-class-name implements-clause with-clause ( positioned-iterator-column-declarations ) named-iterator-column-declarations positioned-iterator-column declarations: , java-data-type named-iterator-column-declarations: , java-data-type java-id description java-modifier...

  • Page 303

    Checking during serialized profile customization to succeed, the data types of the columns in the iterator must be compatible with the data types of the columns in the result table. See ″ java, jdbc, and sql data types ″ for a list of compatible data types. Usage notes v an iterator declaration clau...

  • Page 304

    Sqlj context-clause a context clause specifies a connection context, an execution context, or both. You use a connection context to connect to a data source. You use an execution context to monitor and modify sql statement execution. Syntax [ connection-context ] execution-context connection-context...

  • Page 305

    Table 80. Valid sql statements in an sqlj statement clause alter database alter function alter index alter procedure alter stogroup alter table alter tablespace call comment on commit create alias create database create distinct type create function create global temporary table create index create ...

  • Page 306

    Table 80. Valid sql statements in an sqlj statement clause (continued) set current precision set current refresh age set current routine version set current rules set current sqlid set path truncate update usage notes v sqlj supports both positioned and searched delete and update operations. V for a...

  • Page 307

    Related reference “sqlj statement-clause” on page 288 sqlj assignment-clause the assignment clause assigns the result of an sql operation to a java variable. Syntax java-id = { fullselect } order-by-clause optimize-for-clause isolation-clause queryno-clause fetch-first-clause iterator-conversion-cla...

  • Page 308

    Cast host-expression description host-expression identifies the jdbc resultset that is to be converted to an sqlj iterator. Usage notes v if the iterator to which the jdbc resultset is to be converted is a positioned iterator, the number of columns in the resultset must match the number of columns i...

  • Page 309

    Table 82. Summary of sqlj.Runtime classes class name purpose asciistream a class for handling an input stream whose bytes should be interpreted as ascii. Binarystream a class for handling an input stream whose bytes should be interpreted as binary. Characterstream a class for handling an input strea...

  • Page 310

    V closes any open connectedprofile objects v closes the underlying jdbc connection object close() is equivalent to close(close_connection). Close(boolean) format: public abstract void close (boolean close-connection ) throws sqlexception performs the following functions: v releases all resources tha...

  • Page 311

    Constructors the following constructors are defined in a concrete implementation of the connectioncontext interface that results from translation of the statement #sql context ctx; : ctx(string, boolean) format: public ctx(string url , boolean autocommit ) throws sqlexception parameters: url the rep...

  • Page 312

    Ctx(connection) format: public ctx(java.Sql.Connection jdbc-connection-object ) throws sqlexception parameters: jdbc-connection-object a previously created jdbc connection object. If the constructor call throws an sqlexception, the jdbc connection object remains open. Ctx(connectioncontext) format: ...

  • Page 313

    Ctx(connectioncontext) format: public ctx(sqlj.Runtime.Connectioncontext sqlj-connection-context-object ) throws sqlexception parameters: sqlj-connection-context-object a previously created sqlj connectioncontext object. Methods the following additional methods are generated in a concrete implementa...

  • Page 314

    Sqlj.Runtime.Forupdate interface sqlj implements the sqlj.Runtime.Forupdate interface in sqlj programs that contain an iterator declaration clause with implements sqlj.Runtime.Forupdate . An sqlj program that does positioned update or delete operations (update...Where current of or delete...Where cu...

  • Page 315

    “using a named iterator in an sqlj application” on page 124 related reference “sqlj.Runtime.Resultsetiterator interface” sqlj.Runtime.Positionediterator interface the sqlj.Runtime.Positionediterator interface is implemented when an sqlj application executes an iterator declaration clause for a posit...

  • Page 316

    It indicates that the iterator fetches rows in a result table in the forward direction, from first to last. Fetch_reverse format: public static final int fetch_reverse a constant that can be used by the following methods: v set by sqlj.Runtime.Scrollable.Setfetchdirection and sqlj.Runtime.Executionc...

  • Page 317

    Getfetchsize format: synchronized public int getfetchsize() throws sqlexception returns the number of rows that should be fetched by sqlj when more rows are needed. The returned value is the value that was set by the setfetchsize method, or 0 if no value was set by setfetchsize. Getresultset format:...

  • Page 318

    Parameters: number-of-rows the expected number of rows that sqlj should fetch for the iterator that is associated with the given execution context. If number-of-rows is less than 0 or greater than the maximum number of rows that can be fetched, an sqlexception is thrown. Related reference “sqlj.Runt...

  • Page 319

    Getfetchdirection() format: public abstract int getfetchdirection() throws sqlexception returns the fetch direction of the iterator. Possible values are: sqlj.Runtime.Resultsetiterator.Fetch_forward rows are processed in a forward direction, from first to last. Sqlj.Runtime.Resultsetiterator.Fetch_r...

  • Page 320

    Public abstract boolean relative(int n ) throws sqlexception if n >0, positions the iterator on the row that is n rows after the current row. If n n rows before the current row. If n =0, positions the iterator on the current row. The cursor must be on a valid row of the result table before you can u...

  • Page 321

    Creates an ascii java.Io.Inputstream object with a specified length. Parameters: input-stream the inputstream object that sqlj interprets as an asciistream object. Length the length of the inputstream object that sqlj interprets as an asciistream object. Related reference “sqlj.Runtime.Binarystream ...

  • Page 322

    Sqlj.Runtime.Characterstream class the sqlj.Runtime.Characterstream class is for an input stream of character data with a specified length. The sqlj.Runtime.Characterstream class is derived from the java.Io.Reader class, and extends the java.Io.Filterreader class. Sqlj interprets the bytes in an sql...

  • Page 323

    Parameters: length the number of characters that are read from the reader object. Related reference “sqlj.Runtime.Asciistream class” on page 304 “sqlj.Runtime.Binarystream class” on page 305 “sqlj.Runtime.Streamwrapper class” on page 314 “sqlj.Runtime.Unicodestream class” on page 316 sqlj.Runtime.Ex...

  • Page 324

    Public static final int query_count a constant that can be passed to the setbatchlimit method. It indicates that the previous execution produced a result set. Unlimited_batch format: public static final int unlimited_batch a constant that can be returned from the getupdatecount method. It indicates ...

  • Page 325

    Executequery format: public rtresultset executequery ( ) throws sqlexception this method is used by code that is generated by the sqlj translator. It is not intended for direct use by application programs. Executeupdate format: public int executeupdate() throws sqlexception this method is used by co...

  • Page 326

    Synchronized public int getfetchsize() throws sqlexception returns the number of rows that should be fetched by sqlj when more rows are needed. This value applies only to iterator objects that were generated from the given execution context. The returned value is the value that was set by the setfet...

  • Page 327

    Current indicates what sqlj does with the currently open result set before it advances to the next result set: java.Sql.Statement.Close_current_result specifies that the current resultset object is closed when the next resultset object is returned. Java.Sql.Statement.Keep_current_result specifies th...

  • Page 328

    Isbatching format: public synchronized boolean isbatching() returns true if batching is enabled for the execution context. Returns false if batching is disabled. Registerstatement format: public rtstatement registerstatement(connectioncontext connctx, object profilekey, int stmtndx) throws sqlexcept...

  • Page 329

    Executioncontext.Auto_batch indicates that implicit execution occurs when the number of statements in the batch reaches a number that is set by sqlj. Positive integer the number of statements that are added to the batch before sqlj executes the batch implicitly. The batch might be executed before th...

  • Page 330

    Parameters: max-bytes the maximum number of bytes that sqlj should return from a binary, varbinary, char, varchar, graphic, or vargraphic column. A value of 0 means that the number of bytes is unlimited. 0 is the default. Setmaxrows format: public synchronized void setmaxrows(int max-rows ) specifie...

  • Page 331

    The sqlj.Runtime.Asciistream , sqlj.Runtime.Binarystream , and sqlj.Runtime.Unicodestream classes extend sqlj.Runtime.Streamwrapper . Sqlj.Runtime.Streamwrapper supports methods for specifying the length of sqlj.Runtime.Asciistream , sqlj.Runtime.Binarystream , and sqlj.Runtime.Unicodestream objects...

  • Page 332

    “sqlj.Runtime.Asciistream class” on page 304 “sqlj.Runtime.Binarystream class” on page 305 “sqlj.Runtime.Characterstream class” on page 306 “sqlj.Runtime.Unicodestream class” sqlj.Runtime.Unicodestream class the sqlj.Runtime.Unicodestream class is for an input stream of unicode data with a specified...

  • Page 333

    Javax.Sql.Datasource ds = new com.Ibm.Db2.Jcc.Db2simpledatasource(); ((com.Ibm.Db2.Jcc.Db2basedatasource) ds).Setservername("sysmvs1.Stl.Ibm.Com"); table 83 summarizes the ibm data server driver for jdbc and sqlj-only interfaces. Table 83. Summary of ibm data server driver for jdbc and sqlj-only int...

  • Page 334

    Table 84. Summary of ibm data server driver for jdbc and sqlj-only classes provided by the ibm data server driver for jdbc and sqlj (continued) class name applicable data sources purpose db2connectionpooldatasource 1, 2, 3 a factory for pooledconnection objects. Db2exceptionformatter 1, 2, 3 contain...

  • Page 335

    Db2basedatasource class the com.Ibm.Db2.Jcc.Db2basedatasource class is the abstract data source parent class for all ibm data server driver for jdbc and sqlj-specific implementations of javax.Sql.Datasource , javax.Sql.Connectionpooldatasource , and javax.Sql.Xadatasource . Db2basedatasource impleme...

  • Page 336

    Table 85. Db2basedatasource properties and their data types (continued) property name applicable data sources data type com.Ibm.Db2.Jcc.Db2basedatasource.Clientdebuginfo ( ibm data server driver for jdbc and sqlj type 4 connectivity) 1 on page 324, 2 on page 324 string com.Ibm.Db2.Jcc.Db2basedatasou...

  • Page 337

    Table 85. Db2basedatasource properties and their data types (continued) property name applicable data sources data type com.Ibm.Db2.Jcc.Db2basedatasource.Defaultisolationlevel 1 on page 324, 2 on page 324, 3 on page 324 int com.Ibm.Db2.Jcc.Db2basedatasource.Deferprepares 1 on page 324, 2 on page 324...

  • Page 338

    Table 85. Db2basedatasource properties and their data types (continued) property name applicable data sources data type com.Ibm.Db2.Jcc.Db2basedatasource.Maxretriesforclientreroute 1 on page 324, 2 on page 324, 3 on page 324 int com.Ibm.Db2.Jcc.Db2basedatasource.Maxrowsetsize ( ibm data server drive...

  • Page 339

    Table 85. Db2basedatasource properties and their data types (continued) property name applicable data sources data type com.Ibm.Db2.Jcc.Db2basedatasource.Securitymechanism 1 on page 324, 2 on page 324, 3 on page 324 int com.Ibm.Db2.Jcc.Db2basedatasource.Sendcharinputsutf8 1 on page 324 int com.Ibm.D...

  • Page 340

    Table 85. Db2basedatasource properties and their data types (continued) property name applicable data sources data type com.Ibm.Db2.Jcc.Db2basedatasource.Xanetworkoptimization 1, 2, 3 boolean com.Ibm.Db2.Jcc.Db2basedatasource.Dbansiwarn 3 boolean com.Ibm.Db2.Jcc.Db2basedatasource.Dbdate 3 string com...

  • Page 341

    Db2clientrerouteserverlist methods getalternateportnumber format: public int[] getalternateportnumber() retrieves the port numbers that are associated with the alternate servers. Getalternateservername format: public string[] getalternateservername() retrieves an array that contains the names of the...

  • Page 342

    Db2connection interface the com.Ibm.Db2.Jcc.Db2connection interface extends the java.Sql.Connection interface. Db2connection implements the java.Sql.Wrapper interface. Db2connection methods the following methods are defined only for the ibm data server driver for jdbc and sqlj. Alternatewasusedoncon...

  • Page 343

    Removes a previously registered xml schema from the data source. Parameter descriptions: sqlidschema the sql schema name for the xml schema. Sqlidschema is a string value with a maximum length of 128 bytes. The value of sqlidschema must be the string ’sysxsr’ or null. If the value of sqlidschema is ...

  • Page 344

    Returns current client workstation name for the current client. Important: getdb2clientworkstation is deprecated in the jdbc 4.0 implementation of the ibm data server driver for jdbc and sqlj. Use java.Sql.Connection.Getclientinfo instead. Getdb2correlator format: string getdb2correlator() throws ja...

  • Page 345

    Public int getdb2securitymechanism() throws java.Sql.Sqlexception returns the security mechanism that is in effect for the connection: 3 clear text password security 4 user id-only security 7 encrypted password security 9 encrypted user id and password security 11 kerberos security 12 encrypted user...

  • Page 346

    This method does not apply to connections to ibm informix dynamic server data sources. Isdb2alive format: public boolean db2connection.Isdb2alive() throws java.Sql.Sqlexception returns true if the socket for a connection to the data source is still active. Important: isdb2alive is deprecated in the ...

  • Page 347

    Properties new properties for the connection. These properties override any properties that are already defined on the db2connection instance. Registerdb2xmlschema formats: public void registerdb2xmlschema(string[] sqlidschema , string[] sqlidname , string[] xmlschemalocations , inputstream[] xmlsch...

  • Page 348

    Xmlschemadocuments value is a string or inputstream value with a maximum length of 30mb. The values must not be null. Xmlschemadocumentslengths the lengths of the xml schema documents in the xmlschemadocuments parameter, if the first form of registerdb2xmlschema is used. Each xmlschemadocumentslengt...

  • Page 349

    The sql alterin privilege is required for updating the target xml schema. The sql dropin privilege is required for dropping the source xml schema. Parameter descriptions: targetsqlidschema the sql schema name for a registered xml schema that is to be updated. Targetsqlidschema is a string value with...

  • Page 350

    Jarfile the contents of the jar file that is to be replaced on the server. Jarfilelength the length of the jar file that is to be replace on the server. Jarid the name of the jar in the database, in the form schema.Jar-id or jar-id . This is the name that you use when you refer to the jar in sql sta...

  • Page 351

    That established the trusted physical connection. If the cookies match, the connection becomes available for immediate use, with different properties, by the new user . User the client id that the database system uses to establish the database authorization id. If the user was not authenticated by t...

  • Page 352

    Password the password for the authorization id that is used to establish the connection. Gsscredential if the data source uses kerberos security, specifies a delegated credential that is passed from another principal. Properties properties for the reused connection. These properties override any pro...

  • Page 353

    Length is 22 bytes. A java empty string ( ″″ ) is valid for this parameter value, but a java null value is not valid. Important: setdb2clientaccountinginformation is deprecated in the jdbc 4.0 implementation of the ibm data server driver for jdbc and sqlj. Use java.Sql.Connection.Setclientinfo inste...

  • Page 354

    P pid client process id t tid client thread id (optional) c id data connection generated id l lvl debug library diagnostic trace level for example: m9.72.133.89:8355,i9.72.133.89,p4552,t123,c1,l0 see the description of set client debuginfo for a detailed description of this string. Mgrinfo a string ...

  • Page 355

    Setdb2clientuser sets the value in the client userid special register. Parameter description: user the user id for the current client.The maximum length depends on the server. For a db2 database for linux, unix, and windows server, the maximum length is 255 bytes. For a db2 for z/os server, the maxi...

  • Page 356

    Specifies the collection id for the connection. When you set this value, you also set the collection id of the ibm data server driver for jdbc and sqlj instance that is used for the connection. The setdb2currentpackageset method applies only to connections to db2 database systems. Parameter descript...

  • Page 357

    Db2connectionpooldatasource class db2connectionpooldatasource is a factory for pooledconnection objects. An object that implements this interface is registered with a naming service that is based on the java naming and directory interface (jndi). The com.Ibm.Db2.Jcc.Db2connectionpooldatasource class...

  • Page 358

    Password the password for the authorization id that is used to establish the connection. Gsscredential if the data source uses kerberos security, specifies a delegated credential that is passed from another principal. Properties properties for the connection. Getdb2trustedpooledconnection formats: p...

  • Page 359

    Related concepts chapter 11, “jdbc and sqlj connection pooling support,” on page 465 related reference “properties for the ibm data server driver for jdbc and sqlj” on page 201 “db2pooledconnection class” on page 345 db2databasemetadata interface the com.Ibm.Db2.Jcc.Db2databasemetadata extends the j...

  • Page 360

    Db2diagnosable interface the com.Ibm.Db2.Jcc.Db2diagnosable interface provides a mechanism for getting db2 diagnostics from an sqlexception . Db2diagnosable methods the following methods are defined only for the ibm data server driver for jdbc and sqlj. Getsqlca format: public db2sqlca getsqlca(); r...

  • Page 362

    V ibm data server driver for jdbc and sqlj type 2 connectivity to db2 for z/os version 9.1 or later db2pooledconnection methods the following methods are defined only for the ibm data server driver for jdbc and sqlj. Getconnection (untrusted or trusted reuse without reauthentication) format: public ...

  • Page 363

    User the client identity that is used by the data source to establish the authorization id for the database server. If the user was not authenticated by the application server, the application server must pass a user identity that represents an unauthenticated user. Password the password for user . ...

  • Page 364

    Properties properties for the reused connection. These properties override any properties that are already defined on the db2pooledconnection instance. Getdb2connection (untrusted or trusted reuse without reauthentication) formats: public java.Sql.Connection getdb2connection( java.Util.Properties pr...

  • Page 365

    Public abstract int createdobjectcount() retrieves the number of objects that the ibm data server driver for jdbc and sqlj created since the pool was created. Getmonitorversion format: public int getmonitorversion() retrieves the version of the db2poolmonitor class that is shipped with the ibm data ...

  • Page 366

    Maximum capacity. A blocked request might be successful if an object is returned to the pool before the db2.Jcc.Maxtransportobjectwaittime is exceeded and an exception is thrown. Numberofrequestsblockeddatasourcemax format: public abstract int numberofrequestsblockeddatasourcemax() retrieves the num...

  • Page 367

    Retrieves the total time in milliseconds for requests that were blocked by the pool. This time can be much larger than the elapsed execution time of the application if the application uses multiple threads. Db2preparedstatement interface the com.Ibm.Db2.Jcc.Db2preparedstatement interface extends the...

  • Page 368

    Before a com.Ibm.Db2.Jcc.Db2resultsetmetadata method can be used, a java.Sql.Resultsetmetadata object that is returned from a java.Sql.Resultset.Getmetadata call needs to be cast to com.Ibm.Db2.Jcc.Db2resultsetmetadata . Db2resultsetmetadata methods: the following methods are defined only for the ib...

  • Page 369

    A db2basedatasource object does not support connection pooling or distributed transactions. It contains all of the properties and methods that the db2basedatasource class contains. In addition, db2simpledatasource contains the following ibm data server driver for jdbc and sqlj-only properties. Db2si...

  • Page 370

    Getsqlerrmc format: public abstract string getsqlerrmc() returns a string that contains the sqlca sqlerrmc values, delimited with spaces. Getsqlerrmctokens format: public abstract string[] getsqlerrmctokens() returns an array, each element of which contains an sqlca sqlerrmc token. Getsqlerrp format...

  • Page 371

    Setdb2clientprogramid format: public abstract void setdb2clientprogramid(string program-id ) throws java.Sql.Sqlexception sets a user-defined program identifier for the connection, on db2 for z/os servers. That program identifier is an 80-byte string that is used to identify the caller. The db2 for ...

  • Page 372

    Public long getidsserial8() throws java.Sql.Sqlexception retrieves an automatically generated key from a serial8 column after the automatically generated key was inserted by a previously executed insert statement. The following conditions must be true for getidsserial8 to execute successfully: v the...

  • Page 373

    Enable format: public void enable(boolean on ) throws java.Sql.Sqlexception enables the system monitor that is associated with a connection. This method cannot be called during monitoring. All times are reset when enable is invoked. Getapplicationtimemillis format: public long getapplicationtimemill...

  • Page 374

    Getnetworkiotimemicros format: public long getnetworkiotimemicros() throws java.Sql.Sqlexception returns the sum of elapsed network i/o times that were collected while system monitoring was enabled. The time is in microseconds. Elapsed network i/o time includes the time to write and read drda data f...

  • Page 375

    Interval end when the database server is ready to issue the tcp/ip command to return the reply message to the client. For ibm data server driver for jdbc and sqlj type 2 connectivity to db2 for z/os: the database server elapsed time interval is the difference, in microseconds, between these points i...

  • Page 376

    Db2tracemanager methods gettracemanager format: static public db2tracemanager gettracemanager() throws java.Sql.Sqlexception gets an instance of the global log writer. Setlogwriter formats: public abstract void setlogwriter(string tracedirectory , string basetracefilename , int tracelevel ) throws j...

  • Page 377

    When the form of setlogwriter with the tracefilename parameter is used, only one log file is written. Tracefilename can include a directory path. Logwriter specifies a character output stream to which all global log records are written. This value overrides the logwriter property on a datasource or ...

  • Page 378

    Public abstract void unsetlogwriter() throws java.Sql.Sqlexception disables the global log writer override for future connections. Suspendtrace format: public void suspendtrace() throws java.Sql.Sqlexception suspends all global, connection -level, or datasource -level traces for current and future c...

  • Page 379

    Public abstract boolean gettracefileappend() throws java.Sql.Sqlexception returns true if the global trace records are appended to the trace file. Otherwise, gettracefileappend returns false . Related reference “properties for the ibm data server driver for jdbc and sqlj” on page 201 db2tracemanager...

  • Page 380

    V com.Ibm.Db2.Jcc.Db2basedatasource.Trace_meta_calls (x’2000’) v com.Ibm.Db2.Jcc.Db2basedatasource.Trace_datasource_calls (x’4000’) v com.Ibm.Db2.Jcc.Db2basedatasource.Trace_large_object_calls (x’8000’) v com.Ibm.Db2.Jcc.Db2basedatasource.Trace_t2zos (x’10000’) v com.Ibm.Db2.Jcc.Db2basedatasource.Tr...

  • Page 381

    Tracelevel specifies what to trace. You can specify one or more of the following traces with the tracelevel parameter: v com.Ibm.Db2.Jcc.Db2basedatasource.Trace_none (x’00’) v com.Ibm.Db2.Jcc.Db2basedatasource.Trace_connection_calls (x’01’) v com.Ibm.Db2.Jcc.Db2basedatasource.Trace_statement_calls (...

  • Page 382

    Public void gettracelevel() throws java.Sql.Sqlexception returns the trace level for the trace records that are generated by the trace controller, if it is set. Otherwise, gettracelevel returns -1 ( trace_all ). Unsetlogwriter format: public abstract void unsetlogwriter() throws java.Sql.Sqlexceptio...

  • Page 383

    An application server using a system authorization id uses this method to establish a trusted connection. Trusted connections are supported for: v ibm data server driver for jdbc and sqlj type 4 connectivity to db2 database for linux, unix, and windows version 9.5 or later, and db2 for z/os version ...

  • Page 384

    Properties properties for the connection. Getdb2xaconnection formats: public db2xaconnection getdb2xaconnection(string user , string password , java.Util.Properties properties ) throws java.Sql.Sqlexception public db2xaconnection getdb2xaconnection( org.Ietf.Jgss.Gsscredential gsscredential , java.U...

  • Page 385

    Public java.Io.Inputstream getdb2asciistream() throws sqlexceptionn retrieves data from a db2xml object, and converts the data to us-ascii encoding. Getdb2binarystream format: public java.Io.Inputstream getdb2binarystream() throws sqlexception retrieves data from a db2xml object as a binary stream. ...

  • Page 386

    Getdb2xmlbytes format: public byte[] getdb2xmlbytes(string targetencoding ) throws sqlexceptionn retrieves data from a db2xml object as a byte array, converts the data to targetencoding , and imbeds an xml declaration with an encoding specification for targetencoding in the returned data. Parameter:...

  • Page 387

    Supported methods for a list of methods that the ibm data server driver for jdbc and sqlj supports, see ″ driver support for jdbc apis ″ . Use of progressive streaming by the jdbc drivers for ibm data server driver for jdbc and sqlj, version 3.50 and later, use of progressive streaming is the defaul...

  • Page 388

    Resultsetmetadata values for ibm data server driver for jdbc and sqlj version 4.0 and later for the ibm data server driver for jdbc and sqlj version 4.0 and later, the default behavior of resultsetmetadata.Getcolumnname and resultsetmetadata.Getcolumnlabel differs from the default behavior for earli...

  • Page 389

    The jdbc/sqlj driver for z/os support only non-scrollable and non-updatable resultset s. Difference in url syntax the syntax of the url parameter in the drivermanager.Getconnection method is different for each driver. See the following topics for more information: v ″ connect to a data source using ...

  • Page 390

    Support for read-only connections with the ibm data server driver for jdbc and sqlj, you can make a connection read-only through the readonly property for a connection or datasource object. The jdbc/sqlj driver for z/os does not support read-only connections. Results returned from resultset.Getstrin...

  • Page 391

    Default mappings for preparedstatement.Setxxxstream with the ibm data server driver for jdbc and sqlj, when you use the preparedstatement.Setbinarystream , preparedstatement.Setcharacterstream , or preparedstatement.Setunicodestream method, and no information about the data type of the target column...

  • Page 392

    Result of using getboolean to retrieve a value from a char column with the ibm data server driver for jdbc and sqlj, when you execute resultset.Getboolean or callablestatement.Getboolean to retrieve a boolean value from a char column, and the column contains the value ″ false ″ or ″ 0 ″ , the value ...

  • Page 393

    Progressive streaming is supported in the ibm data server driver for jdbc and sqlj version 3.1 and later, but for ibm data server driver for jdbc and sqlj version 3.2 and later, progressive streaming behavior is the default for lob and xml retrieval, for connections to db2 for z/os version 9.1 and l...

  • Page 394

    Drivers, you can set the usejdbc4columnnameandlabelsemantics connection and datasource property to db2basedatasource.No (2). Batch updates with automatically generated keys have different results in different driver versions with the ibm data server driver for jdbc and sqlj version 3.52 or later, pr...

  • Page 395

    Server driver for jdbc and sqlj permitted the update or delete operations, but unexpected updates or deletes might occur. Starting with version 3.7 and 3.51 of the ibm data server driver for jdbc and sqlj, the enablerowsetsupport property enables or disables multi-row fetch from db2 for z/os tables ...

  • Page 396

    Table 87. Resultsetmetadata.Getcolumnname and resultsetmetadata.Getcolumnname before and after ibm data server driver for jdbc and sqlj version 4.0 for a query with an as clause (continued) target data source behavior before ibm data server driver for jdbc and sqlj version 4.0 behavior for ibm data ...

  • Page 397

    The following table lists the resultsetmetadata.Getcolumnname and resultsetmetadata.Getcolumnname values that are returned for the query. Table 89. Resultsetmetadata.Getcolumnname and resultsetmetadata.Getcolumnname before and after ibm data server driver for jdbc and sqlj version 4.0 for a table co...

  • Page 398

    Production of dbrms during sqlj program preparation the sqlj program preparation process for the ibm data server driver for jdbc and sqlj does not produce dbrms. Therefore, with the ibm data server driver for jdbc and sqlj, you can produce db2 packages only by using the ibm data server driver for jd...

  • Page 399

    Table 90. Error codes issued by the ibm data server driver for jdbc and sqlj error code message text and explanation sqlstate +4204 errors were encountered and tolerated as specified by the return data until clause. Explanation: tolerated errors include federated connection, authentication, and auth...

  • Page 400

    Table 90. Error codes issued by the ibm data server driver for jdbc and sqlj (continued) error code message text and explanation sqlstate +4461 text-from-getmessage explanation: the specified value is invalid of out of range. User response: call sqlexception.Getmessage to retrieve specific informati...

  • Page 401

    Table 90. Error codes issued by the ibm data server driver for jdbc and sqlj (continued) error code message text and explanation sqlstate -4203 error executing function . Server returned rc . : an error occurred on an xa connection during execution of an sql statement. For network optimization, the ...

  • Page 402

    Table 90. Error codes issued by the ibm data server driver for jdbc and sqlj (continued) error code message text and explanation sqlstate -4225 text-from-getmessage explanation: an error occurred when data was sent to a server or received from a server. User response: call sqlexception.Getmessage to...

  • Page 403

    Table 90. Error codes issued by the ibm data server driver for jdbc and sqlj (continued) error code message text and explanation sqlstate -4470 text-from-getmessage explanation: the requested operation cannot be performed because the target resource is closed. User response: call sqlexception.Getmes...

  • Page 404

    Table 90. Error codes issued by the ibm data server driver for jdbc and sqlj (continued) error code message text and explanation sqlstate -4497 the application must issue a rollback. The unit of work has already been rolled back in the db2 server, but other resource managers involved in the unit of ...

  • Page 405

    Table 91. Sqlstates returned by the ibm data server driver for jdbc and sqlj sqlstate class sqlstate description 01 xxx warning 02 xxx no data 02501 the cursor position is not valid for a fetch of the current row. 02506 tolerable error 08 xxx connection exception 08003 a connection does not exist 08...

  • Page 406

    Table 91. Sqlstates returned by the ibm data server driver for jdbc and sqlj (continued) sqlstate class sqlstate description 42815 the data type, length, scale, value, or ccsid is invalid 42820 a numeric constant is too long, or it has a value that is not within the range of its data type 42968 the ...

  • Page 407

    And version, and information about its environment, such as information about the java runtime environment, operating system, path information, and license restrictions. -help specifies that the db2jcc utility describes each of the options that it supports. If any other options are specified with -h...

  • Page 408

    [jcc] path separator (":" on unix) = : [jcc] user's account name = myusrid [jcc] user's home directory = /home2/myusrid [jcc] user's current working directory = /home2/myusrid [jcc] dumping all system properties: { java.Assistive=on, java.Runtime.Name=java (tm) 2 runtime environment, standard editio...

  • Page 409

    Generated by the translator and .Class files that are generated by the compiler. The default is the directory that contains the sqlj source files. The translator uses the directory structure of the sqlj source files when it puts the generated files in directories. For example, suppose that you want ...

  • Page 410

    Yes generate smap files. An smap file name is sqlj-source-file- name .Java.Smap. The sqlj translator places the smap file in the same directory as the generated java source file. -encoding= encoding-name specifies the encoding of the source file. Examples are jis or euc. If this option is not specif...

  • Page 411

    Begins with a dash (-). Do not include spaces between -j and the jvm option. If you need to specify multiple jvm options, precede each compiler option with -j. For example: -j-xmx128m -j-xmine2m sqlj-source-file-name specifies a list of sqlj source files to be translated. This is a required paramete...

  • Page 412

    Db2sqljcustomize -help -url jdbc:db2:// server / database : port : property = value ; -datasource jndi-name -user user-id -password password -automaticbind yes -automaticbind no -pkgversion auto -pkgversion version-id -bindoptions ″ options-string ″ -storebindoptions -collection collection-name -onl...

  • Page 413

    -zosdescprocparms -zosprocedurepath procedure-path -gendbrm -dbrmdir directory-name serialized-profile-name file-name .Grp options-string: db2-for-z/os-options db2-database-for-linux-unix-and-windows-options db2 for z/os options: action(replace) (1) replver( version-id ) action(add) dbprotocol(drda)...

  • Page 414

    Federated no federated yes funcpath schema-name insert def insert buf isolation cs isolation rr isolation rs isolation ur owner authorization-id qualifier qualifier-name queryopt optimization-level sqlerror nopackage sqlerror continue sqlwarn yes sqlwarn no staticreadonly no staticreadonly yes valid...

  • Page 415

    Select current server from sysibm.Sysdummy1; if the connection is to a db2 database for linux, unix, and windows server, database is the database name that is defined during installation. If the connection is to an ibm cloudscape server, the database is the fully-qualified name of the file that cont...

  • Page 416

    -bindoptions options-string specifies a list of options, separated by spaces. These options have the same function as db2 precompile and bind options with the same names. If you are preparing your program to run on a db2 for z/os system, specify db2 for z/os options. If you are preparing your progra...

  • Page 417

    If -longpkgname is not specified, package-name-stem must be an alphanumeric string of seven or fewer bytes. If -longpkgname is specified, package-name-stem must be an alphanumeric string of 127 or fewer bytes. -singlepkgname package-name specifies that the customizer creates one package, with the na...

  • Page 418

    Table 93. Pkgisolation values and associated isolation levels (continued) pkgnumber value isolation level for package 4 repeatable read (rr) example: suppose that a profile name is thisismyprog_sjprofile111.Ser. The db2sqljcustomize option -longpkgname is not specified. Therefore, bytes-from-program...

  • Page 419

    Information can have the biggest impact on storage usage for character inout parameters, lob out parameters, and decimal out parameters. When -zosdescprocparms is specified, the db2 database server uses the specified or default value of -zosprocedurepath to resolve unqualified names of stored proced...

  • Page 420

    -dbrmdir directory-name when -gendbrm is specified, -dbrmdir specifies the local directory into which db2sqljcustomize puts the generated dbrm files. The default is the current directory. -dbrmdir applies to programs that are to be run on db2 for z/os database servers only. -tracefile file-name enab...

  • Page 421

    Usage notes online checking is always recommended: it is highly recommended that you use online checking when you customize your serialized profiles. Online checking determines information about the data types and lengths of db2 host variables, and is especially important for the following items: v ...

  • Page 422

    Specifying the collection option in the -bindoptions parameter. To execute a package that is in a collection other than the collection that is specified in the serialized profile, include a set current packageset statement in the program. Using the version parameter: use the version parameter to bin...

  • Page 423

    “sqlj - sqlj translator” on page 392 “db2sqljprint - sqlj profile printer” on page 412 “db2sqljbind - sqlj profile binder” ″ bind and rebind options ″ (db2 command reference) db2sqljbind - sqlj profile binder db2sqljbind binds db2 packages for a serialized profile that was previously customized with...

  • Page 424

    -tracelevel trace_sqlj -tracefile file-name , -tracelevel trace_none trace_connection_calls trace_statement_calls trace_result_set_calls trace_driver_configuration trace_connects trace_drda_flows trace_result_set_meta_data trace_parameter_meta_data trace_diagnostics trace_sqlj trace_xa_calls trace_t...

  • Page 425

    Release(commit) release(deallocate) sqlerror(nopackage) sqlerror(continue) validate(run) validate(bind) notes: 1 these options can be specified in any order. Db2 database for linux, unix, and windows options (1) blocking unambig blocking all blocking no dec 15 dec 31 degree 1 degree any explain no e...

  • Page 426

    Select current server from sysibm.Sysdummy1; if the connection is to a db2 database for linux, unix, and windows server, database is the database name that is defined during installation. If the connection is to an ibm cloudscape server, the database is the fully-qualified name of the file that cont...

  • Page 427

    -dbrmdir applies to programs that are to be run on db2 for z/os database servers only. -tracefile file-name enables tracing and identifies the output file for trace information. This option should be specified only under the direction of ibm software support. -tracelevel if -tracefile is specified, ...

  • Page 428

    This if the cursor (iterator) for a positioned update or delete statement is in the same package as the positioned update or delete statement. V fully qualify db2 table names in positioned update and positioned delete statements. Related tasks “program preparation for sqlj programs” on page 183 “bin...

  • Page 429: Sqlj

    Chapter 8. Installing the ibm data server driver for jdbc and sqlj if you plan to run jdbc or sqlj applications, after you install db2 for z/os or migrate to the current version of db2 for z/os, you need to install the current version of the ibm data server driver for jdbc and sqlj. Installing the i...

  • Page 430

    6. In z/os unix system services, run the db2binder utility to bind the packages for the ibm data server driver for jdbc and sqlj. 7. If you plan to use ibm data server driver for jdbc and sqlj type 4 connectivity to implement distributed transactions against db2 udb for os/390 and z/os version 7 ser...

  • Page 431

    Environment variables for the ibm data server driver for jdbc and sqlj you need to set environment variables so that the operating system can locate the ibm data server driver for jdbc and sqlj. The environment variables that you must set are: steplib modify steplib to include the sdsnexit, sdsnload...

  • Page 432

    Include sqlj.Zip in the classpath if you plan to prepare sqlj applications that include only jdbc 3.0 and earlier functions . Include sqlj4.Zip in the classpath if you plan to prepare sqlj applications that include jdbc 4.0 and later functions, as well as jdbc 3.0 and earlier functions. Important: i...

  • Page 433

    You can set configuration properties in the following ways: v set the configuration properties as java system properties. Those settings override any other settings. For stand-alone java applications, you can set the configuration properties as java system properties by specifying -d property = valu...

  • Page 434

    Example: putting db2jccconfiguration.Properties in a jar file: suppose that your configuration properties are in a file that is in ebcdic code page 1047. To put the properties file into a jar file, follow these steps: 1. Rename db2jccconfiguration.Properties to another name, such as ebcdicversion.Pr...

  • Page 435

    V sqludts v sqlcamessage the tables that you need to create are: v sysibm.Sysdummyu v sysibm.Sysdummya v sysibm.Sysdummye those tables ensure that character conversion does not occur when unicode data is stored in dbclob or clob columns. Follow these steps to install the stored procedures and create...

  • Page 436

    File utilities notes options help ------------------------------------------------------------------------ definition menu wlm appl command ===> ___________________________________________________________ definition data set . : none definition name . . . . Wlmenv description . . . . . . Environment...

  • Page 437

    The applenv value must match the value that you specify in the wlm address space startup procedure and on the create procedure statements for the stored procedures. Limit on starting server address spaces for a subsystem instance specify 1 (no limit). Related tasks “creating the wlm address space st...

  • Page 438

    -reopt none -reopt always -reopt once -reopt auto -blocking all -blocking unambig -blocking no -optprofile profile-name -owner authorization-id -sqlid authorization-id -generic -package package-name -version version-id -bindoptions ″ options-string ″ -verbose -help db2binder option descriptions -url...

  • Page 439

    V for user packages, -action drop indicates that the specified package should be dropped. -action drop applies only if the target database server is db2 for z/os. -size controls the number of statement , preparedstatement , or callablestatement objects that can be open concurrently, or the number of...

  • Page 440

    Until the prepared statement is invalidated, or until the statement is removed from the dynamic statement cache and needs to be prepared again. Auto specifies that access paths are automatically determined by the database server. -blocking specifies the type of row blocking for cursors. All for curs...

  • Page 441

    -version specifies the version id of the package that is to be rebound. If -version is specified, -action rebind, -package, and -generic must also be specified. -bindoptions specifies a string that is delimited with quotation marks. The contents of that string are one or more parameter and value pai...

  • Page 442

    This option corresponds to the dynamicrules(invokerun) bind option. 3 when the package is run as or runs under a stored procedure or user-defined function package, the database server processes dynamic sql statements using invoke behavior. Otherwise, the database server processes dynamic sql stateme...

  • Page 443

    -verbose specifies that the db2binder utility displays detailed information about the bind process. Specifies that the db2binder utility describes each of the options that it supports. If any other options are specified with -help, they are ignored. Db2binder return codes when the target operating s...

  • Page 444

    Table 95. Db2binder return codes when the target operating system is windows (continued) return code meaning -401 the binder class was not found. -402 connection to the database server failed. -403 databasemetadata retrieval for the database server failed. -501 no more packages are available in the ...

  • Page 445

    Db2t4xaindoubtutil syntax java com.Ibm.Db2.Jcc.Db2t4xaindoubtutil -url jdbc:db2: // server / database : port -user user-id -password password -owner owner-id -help -delete -priqty integer -secqty integer -bindonly -showsql -jdbccollection nullid -jdbccollection collection-id db2t4xaindoubtutil param...

  • Page 446

    V the authorization id of the owner of the t4xain01, t4xain02, t4xain03, and t4xain04 packages. Sql statements in those packages are executed using the authority of owner-id . -help specifies that the db2t4xaindoubtutil utility describes each of the options that it supports. If any other options are...

  • Page 447

    Csynclog varchar(150) for bit data) in indbtts; create unique index indbtidx on sysibm.Indoubt(indbtxid, uowid); db2t4xaindoubtutil example run the db2t4xaindoubtutil to allow a db2 for os/390 and z/os version 7 subsystem that has ip address mvs1, port number 446, and db2 location name sjcec1 to par...

  • Page 448

    Database is the db2 location name that is defined during installation. All characters in this value must be uppercase characters. You can determine the location name by executing the following sql statement on the server: select current server from sysibm.Sysdummy1; -user specifes the user id under ...

  • Page 449

    Com.Ibm.Db2.Jcc.Db2version.Getversion()); // connect to the local db2 for z/os server system.Out.Println("establishing connection to local server"); connection conn = drivermanager.Getconnection("jdbc:db2:"); system.Out.Println(" successful connect"); conn.Setautocommit(false); // use databasemeta...

  • Page 450

    System.Out.Println("sqlexception: " + e); e.Printstacktrace(); } } } related tasks “installing the z/os application connectivity to db2 for z/os feature” on page 435 chapter 9, “migrating from the jdbc/sqlj driver for os/390 and z/os to the ibm data server driver for jdbc and sqlj,” on page 439 “upg...

  • Page 451

    “customization of ibm data server driver for jdbc and sqlj configuration properties” on page 416 “verify the installation of the ibm data server driver for jdbc and sqlj” on page 432 “run-time environment for java routines” on page 160 related tasks “enabling the db2-supplied stored procedures and d...

  • Page 452

    1. Allocate and load the z/os application connectivity to db2 for z/os libraries. 2. On all db2 for z/os servers to which you plan to connect, set subsystem parameter descstat to yes. Descstat corresponds to installation field describe for static on panel dsntipf. This step is necessary for sqlj sup...

  • Page 453

    ″ describe for static ″ (db2 installation guide) jobs for loading the z/os application connectivity to db2 for z/os libraries to allocate the hfs or zfs directory structure and use smp/e to load the z/os application connectivity to db2 for z/os libraries, you need to run a set of jobs. Those jobs ar...

  • Page 454

    Debugging functions. If z/os application connectivity to db2 for z/os is installed in /usr/lpp/jcct4v3 , modify path as follows: export path=/usr/lpp/jcct4v3/bin:$path classpath z/os application connectivity to db2 for z/os contains the following class files: db2jcc.Jar contains all jdbc classes and...

  • Page 455

    Chapter 9. Migrating from the jdbc/sqlj driver for os/390 and z/os to the ibm data server driver for jdbc and sqlj you need to migrate to the ibm data server driver for jdbc and sqlj before you migrate to db2 version 9.1 for z/os. Prerequisites for migrating to the ibm data server driver for jdbc an...

  • Page 456

    6. On db2 for z/os, enable the db2-supplied stored procedures and define the tables that are used by the ibm data server driver for jdbc and sqlj. 7. In z/os unix system services, run the db2binder utility to bind the packages for the ibm data server driver for jdbc and sqlj. 8. Optional: in db2 for...

  • Page 457

    C. Check the routine definitions in the db2 catalog. If the value of collid in sysibm.Sysroutines is dsnjdbc, you need to take one of the following actions: v bind the ibm data server driver for jdbc and sqlj packages into a collection with the dsnjdbc collection id. V redefine the routines with a c...

  • Page 458

    And z/os does not always fully describe host variables and parameters. Because the db2sqljupgrade utility is an offline utility, it cannot check host variable or parameter information against the corresponding table column or routine definition information. Therefore, you might experience problems d...

  • Page 459

    When you use the planname property, you bind plans for your sqlj programs and the jdbc driver. When you use the pklist property, you bind packages for your sqlj programs and the jdbc driver. This means that if you move from using sqlj and jdbc plans to packages, you need to grant execution privilege...

  • Page 460

    Table 96. Jdbc/sqlj driver for os/390 and z/os properties and their ibm data server driver for jdbc and sqlj equivalents (continued) jdbc/sqlj driver for os/390 and z/os property ibm data server driver for jdbc and sqlj equivalent notes db2.Sp.Lob.Output.Parm.Size db2.Jcc.Loboutputsize property valu...

  • Page 461

    Table 96. Jdbc/sqlj driver for os/390 and z/os properties and their ibm data server driver for jdbc and sqlj equivalents (continued) jdbc/sqlj driver for os/390 and z/os property ibm data server driver for jdbc and sqlj equivalent notes db2.Connpool.Connect.Create.Timeout none not needed for the ibm...

  • Page 462

    If you do not plan to continue to use a plan for your sqlj applications, specify the name of the package list for those applications in the db2.Jcc.Pklist configuration property. If you plan to continue to use a plan for your sqlj applications, specify that plan name in the db2.Jcc.Planname property...

  • Page 463: Jdbc And Sqlj

    Chapter 10. Security under the ibm data server driver for jdbc and sqlj when you use the ibm data server driver for jdbc and sqlj, you choose a security mechanism by specifying a value for the securitymechanism property. You can set this property in one of the following ways: v if you use the driver...

  • Page 464

    The following table lists the security mechanisms that the ibm data server driver for jdbc and sqlj supports, and the value that you need to specify for the securitymechanism property to specify each security mechanism. The default security mechanism is clear_text_password_security. If the server do...

  • Page 465

    Import java.Sql.*; // jdbc base ... String url = "jdbc:db2://mvs1.Sj.Ibm.Com:5021/san_jose:user=dbadm;password=dbadm;"; // set url for the data source connection con = drivermanager.Getconnection(url); // create connection alternatively, you can set the user id and password by setting the user and p...

  • Page 466

    Com.Ibm.Db2.Jcc.Db2basedatasource.Clear_text_password_security); // set security mechanism to // user id and password ibm data server driver for jdbc and sqlj type 2 connectivity with no user id or password: for ibm data server driver for jdbc and sqlj type 2 connectivity, if you use user id and pas...

  • Page 467

    Com.Ibm.Db2.Jcc.Db2simpledatasource db2ds = new com.Ibm.Db2.Jcc.Db2simpledatasource(); // create db2simpledatasource object db2ds.Setdrivertype(4); // set the driver type db2ds.Setdatabasename("san_jose"); // set the location db2ds.Setservername("mvs1.Sj.Ibm.Com"); // set the server name db2ds.Setpo...

  • Page 468

    Encrypted_user_and_data_security or encrypted_user_password_and_data_security securitymechanism value. Encrypted_user_and_data_security is valid for connections to db2 for z/os servers only, and only for des encryption (encryptionalgorithm value of 1). Db2 for z/os or db2 database for linux, unix, a...

  • Page 469

    Ds.Setportnumber(5021); // set the port number ds.Setuser("db2adm"); // set the user id ds.Setpassword("db2adm"); // set the password ds.Setsecuritymechanism( com.Ibm.Db2.Jcc.Db2basedatasource.Encrypted_password_security); // set security mechanism to // user id and encrypted password related tasks ...

  • Page 470

    ... 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("kerberosserverprincipal", "sample/srvlsj.Ibm.Com@srvlsj.Sj.Ibm.Com"...

  • Page 471

    Properties.Put("kerberosserverprincipal", “sample/srvlsj.Ibm.Com@srvlsj.Sj.Ibm.Com"); // set the kerberos server properties.Put("securitymechanism", new string("" + com.Ibm.Db2.Jcc.Db2basedatasource.Kerberos_security + "")); // set security mechanism to // kerberos string url = "jdbc:db2://mvs1.Sj.I...

  • Page 472

    // set the delegated credential properties.Put("securitymechanism", new string("" + com.Ibm.Db2.Jcc.Db2basedatasource.Kerberos_security + "")); // set security mechanism to // kerberos string url = "jdbc:db2://mvs1.Sj.Ibm.Com:5021/san_jose"; // set url for the data source connection con = drivermana...

  • Page 473

    A trusted context is an object that the database administrator defines that contains a system authorization id and a set of trust attributes. Currently, for db2 database servers, a database connection is the only type of context that is supported. The trust attributes identify a set of characteristi...

  • Page 474

    String user = "user"; string password = "password"; // call getdb2trustedpooledconnection to get the trusted connection // instance and the cookie for the connection object[] objects = datasource.Getdb2trustedpooledconnection( user,password, properties); example: reuse an existing trusted connection...

  • Page 475

    “creating and deploying datasource objects” on page 19 related reference “properties for the ibm data server driver for jdbc and sqlj” on page 201 ″ configuring the db2 server for ssl ″ (db2 administration guide) configuring connections under the ibm data server driver for jdbc and sqlj to use ssl t...

  • Page 476

    For example, suppose that the server certificate is stored in a file named jcc.Cacert. Issue the following keytool utility statement to read the certificate from file jcc.Cacert, and store it in a truststore named cacerts. Keytool -import -file jcc.Cacert -keystore cacerts 2. Configure the java runt...

  • Page 477

    # set the java security providers security.Provider.1=com.Ibm.Jsse2.Ibmjsseprovider2 security.Provider.2=com.Ibm.Crypto.Fips.Provider.Ibmjcefips security.Provider.3=com.Ibm.Crypto.Provider.Ibmjce security.Provider.4=com.Ibm.Security.Jgss.Ibmjgssprovider security.Provider.5=com.Ibm.Security.Cert.Ibmc...

  • Page 478

    To do that, set the following java system properties: javax.Net.Ssl.Truststore specifies the name of the truststore that you specified with the -keystore parameter in the keytool utility in step 1 on page 459. If the ibm data server driver for jdbc and sqlj property db2basedatasource.Ssltruststorelo...

  • Page 479

    6. A user with authority to bind packages on the bind-and-run system runs the db2sqljbind command on the customized serialized profiles that were copied from the customization-only system. V use a stored procedure to do customization: write a java stored procedure that customizes serialized profiles...

  • Page 480

    464 application programming guide and reference for java ™.

  • Page 481

    Chapter 11. Jdbc and sqlj connection pooling support connection pooling is part of jdbc datasource support, and is supported by the ibm data server driver for jdbc and sqlj. The ibm data server driver for jdbc and sqlj provides a factory of pooled connections that are used by websphere application s...

  • Page 482

    “db2connectionpooldatasource class” on page 341 “db2pooledconnection class” on page 345 466 application programming guide and reference for java ™.

  • Page 483

    Chapter 12. Ibm data server driver for jdbc and sqlj type 4 connectivity jdbc and sqlj distributed transaction support the ibm data server driver for jdbc and sqlj in the z/os environment supports distributed transaction management when you use ibm data server driver for jdbc and sqlj type 4 connect...

  • Page 484

    Sysibm.Indoubt table and the t4xaindbtpkg package. You should never modify the sysibm.Indoubt table manually. In jdbc or sqlj applications, distributed transactions are supported for connections that are established using the datasource interface. A connection is normally established by the applicat...

  • Page 485

    Transaction. Figure 58 illustrates a program that uses jta methods to execute a distributed transaction. This program acts as the transaction manager and a transactional application. Two connections to two different data sources do sql work under a single distributed transaction. Class xasample { ja...

  • Page 486

    // the xadatasource contains the user id and password. // get the xaconnection object from each xadatasource xaconn1 = xads1.Getxaconnection(); xaconn2 = xads2.Getxaconnection(); // get the java.Sql.Connection object from each xaconnection conn1 = xaconn1.Getconnection(); conn2 = xaconn2.Getconnecti...

  • Page 487

    // so there is nothing more to do. } } } catch (javax.Transaction.Xa.Xaexception xae) { // distributed transaction failed, so roll it back. // report xaexception on prepare/commit. System.Out.Println("distributed transaction prepare/commit failed. " + "rolling it back."); system.Out.Println("xaexcep...

  • Page 488

    System.Out.Println(" naming exception: " + nme.Getmessage()); } } } recommendation: for better performance, complete a distributed transaction before you start another distributed or local transaction. Related reference “db2xadatasource class” on page 366 472 application programming guide and refere...

  • Page 489

    Chapter 13. Jdbc and sqlj global transaction support jdbc and sqlj global transaction support lets enterprise java beans (ejb) and java servlets that run under websphere application server access db2 for z/os relational data within global transactions. Websphere application server provides the envir...

  • Page 490

    When you run a multi-threaded client under websphere, a transaction can span multiple threads. This situation might occur in a java servlet. An application that runs in this environment needs to perform some sql on each connection object before the application passes the object to another thread. Th...

  • Page 491: Balancing

    Chapter 14. 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. The ibm da...

  • Page 492

    You can monitor the global transport objects pool in either of the following ways: v using traces that you start using ibm data server driver for jdbc and sqlj configuration properties v using an application programming interface related reference “properties for the ibm data server driver for jdbc ...

  • Page 493

    V db2.Jcc.Mintransportobjects v db2.Jcc.Maxtransportobjects v db2.Jcc.Maxtransportobjectwaittime v db2.Jcc.Dumppool v db2.Jcc.Dumppoolstatisticsonschedule v db2.Jcc.Dumppoolstatisticsonschedulefile start with settings similar to these: db2.Jcc.Mintransportobjects=0 db2.Jcc.Maxtransportobjects=1500 d...

  • Page 494

    – ibm informix connect or the ibm informix client software development kit (client sdk), version 3.50 or later – a primary server and one or more secondary servers of any type (hdr secondary, sd secondary, or rs secondary) client requirements: v ibm data server driver for jdbc and sqlj version 3.52 ...

  • Page 495

    Source: table 101. Example of data source property settings for ibm data server driver for jdbc and sqlj connection concentrator and workload balancing for ibm informix dynamic server property setting enablesysplexwlb true maxtransportobjects 100 4. Restart websphere application server. Techniques f...

  • Page 496

    Hwroc the number of objects that were reused from the pool. Coc the number of objects that the ibm data server driver for jdbc and sqlj created since the pool was created. Aooc the number of objects that exceeded the idle time that was specified by db2.Jcc.Maxtransportobjectidletime and were deleted...

  • Page 497

    “example of enabling the ibm data server driver for jdbc and sqlj connection concentrator for db2 for z/os servers” on page 476 chapter 14. Jdbc connection concentrator and workload balancing 481.

  • Page 498

    482 application programming guide and reference for java ™.

  • Page 499: Driver For Jdbc And Sqlj

    Chapter 15. Problem diagnosis with the ibm data server driver for jdbc and sqlj to obtain data for diagnosing sqlj or jdbc problems with the ibm data server driver for jdbc and sqlj, collect trace data and run utilities that format the trace data. You should run the trace and diagnostic utilities on...

  • Page 500

    The default trace level is trace_all . See ″ properties for the ibm data server driver for jdbc and sqlj ″ for information on how to specify more than one type of tracing. 2. Invoke the drivermanager.Setlogwriter method to specify the trace destination and turn the trace on. After a connection is es...

  • Page 501

    Formatting information about an sqlj customized serialized profile the db2sqljprint utility formats information about each sqlj clause in a serialized profile that is customized for the ibm data server driver for jdbc and sqlj. Run the db2sqljprint utility on the customized serialized profile for th...

  • Page 502

    Example of a trace program under the ibm data server driver for jdbc and sqlj you might want to write a single class that includes methods for tracing under the drivermanager interface, as well as the datasource interface. The following example shows such a class. The example uses ibm data server dr...

  • Page 503

    } catch(java.Sql.Sqlexception e) { com.Ibm.Db2.Jcc.Db2exceptionformatter.Printtrace(e, printwriter, "[traceexample]"); } finally { cleanup(c, printwriter); printwriter.Flush(); } } // if the code ran successfully, the connection should // already be closed. Check whether the connection is closed. //...

  • Page 504

    Printwriter.Println("[traceexample] deadlocks could " + "occur if the connection is not closed."); com.Ibm.Db2.Jcc.Db2exceptionformatter.Printtrace(e, printwriter, "[traceexample]"); } } catch(java.Lang.Throwable e) { printwriter.Println("[traceexample] unable to " + "force the connection to close")...

  • Page 506

    490 application programming guide and reference for java ™.

  • Page 507

    Chapter 16. Tracing ibm data server driver for jdbc and sqlj c/c++ native driver code to debug applications that use ibm data server driver for jdbc and sqlj type 2 connectivity on db2 for z/os, you might need to trace the c/c++ native driver code. To collect, format, and print the trace data for th...

  • Page 508

    Db2jcctrace parameters format specifies that the output trace file contains formatted trace data. Abbreviation: fmt flow specifies that the output trace file contains control flow information. Abbreviation: flw information specifies that the output trace file contains information about the trace, su...

  • Page 509: For Jdbc And Sqlj

    Chapter 17. 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. That information is: core driver...

  • Page 510

    To collect system monitoring information using the trace method: start a jdbc trace, using configuration properties or connection or datasource properties. Include trace_system_monitor when you set the tracelevel property. For example: string url = "jdbc:db2://sysmvs1.Stl.Ibm.Com:5021/san_jose" + ":...

  • Page 512

    496 application programming guide and reference for java ™.

  • Page 513

    Information resources for db2 for z/os and related products many information resources are available to help you use db2 for z/os and many related products. A large amount of technical information about ibm products is now available online in information centers or on library web sites. Disclaimer: ...

  • Page 514

    Table 102. Db2 version 9.1 for z/os book titles (continued) title publication number available in information center available in pdf available in bookmanager ® format available in printed book db2 version 9.1 for z/os installation guide gc18-9846 x x x x db2 version 9.1 for z/os introduction to db2...

  • Page 515

    Table 103. Related product information resource locations (continued) related product information resources cics transaction server for z/os information center: http://publib.Boulder.Ibm.Com/infocenter/cicsts/v3r1/index.Jsp cobol information center: http://publib.Boulder.Ibm.Com/infocenter/pdthelp/v...

  • Page 516

    Table 103. Related product information resource locations (continued) related product information resources pl/i information center: http://publib.Boulder.Ibm.Com/infocenter/pdthelp/v1r1/index.Jsp this product is now called enterprise pl/i for z/os. System z ® http://publib.Boulder.Ibm.Com/infocente...

  • Page 517

    Table 103. Related product information resource locations (continued) related product information resources z/os library center site: http://www.Ibm.Com/servers/eserver/zseries/zos/bkserv/ this resource includes information about the following z/os elements and components: v character data represent...

  • Page 518

    – a collection of glossaries of ibm terms in multiple languages is available on the ibm terminology web site at: http://www.Ibm.Com/software/ globalization/terminology/index.Jsp v national language support information; available at the ibm publications center at: http://www.Elink.Ibmlink.Ibm.Com/pub...

  • Page 519

    How to obtain db2 information you can access the official information about the db2 product in a number of ways. V “db2 on the web” v “db2 product information” v “db2 education” on page 504 v “how to order the db2 library” on page 504 db2 on the web stay current with the latest information about db2...

  • Page 520

    The books for db2 for z/os are also available on the following cd-rom and dvd collection kits, which contain online books for many ibm products: v ibm z/os software products collection , sk3t-4270, in english v ibm z/os software products dvd collection , sk3t–4271, in english pdf format many of the ...

  • Page 521

    Be prepared to give your customer number, the product number, and either the feature codes or order numbers that you want. How to obtain db2 information 505.

  • Page 522

    506 application programming guide and reference for java ™.

  • Page 523

    How to use the db2 library titles of books in the library begin with db2 version 9.1 for z/os. However, references from one book in the library to another are shortened and do not include the product name, version, and release. Instead, they point directly to the section that holds the information. ...

  • Page 524

    V how to write distributed applications across operating systems v how to write applications that use open database connectivity (odbc) to access db2 servers v how to write applications that use jdbc and sqlj with the java programming language to access db2 servers v how to write applications to sto...

  • Page 525

    V db2 command reference , which explains how to run commands if you will be using data sharing, you need db2 data sharing: planning and administration , which describes how to plan for and implement data sharing. Additional information about system and database administration can be found in db2 mes...

  • Page 526

    510 application programming guide and reference for java ™.

  • Page 527: Notices

    Notices this information was developed for products and services offered in the u.S.A. Ibm may not offer the products, services, or features discussed in this document in other countries. Consult your local ibm representative for information on the products and services currently available in your a...

  • Page 528

    Ibm may use or distribute any of the information you supply in any way it believes appropriate without incurring any obligation to you. Licensees of this program who wish to have information about it for the purpose of enabling: (i) the exchange of information between independently created programs ...

  • Page 529

    General-use programming interface and associated guidance information is identified where it occurs by the following markings: general-use programming interface and associated guidance information... Trademarks ibm, the ibm logo, and ibm.Com ® are trademarks or registered marks of international busi...

  • Page 530

    514 application programming guide and reference for java ™.

  • Page 531: Glossary

    Glossary abend see abnormal end of task. Abend reason code a 4-byte hexadecimal code that uniquely identifies a problem with db2. Abnormal end of task (abend) termination of a task, job, or subsystem because of an error condition that recovery facilities cannot resolve during execution. Access metho...

  • Page 532

    Application plan to process sql statements that it encounters during statement execution. Application process the unit to which resources and locks are allocated. An application process involves the execution of one or more programs. Application programming interface (api) a functional interface tha...

  • Page 533

    Basic row format a row format in which values for columns are stored in the row in the order in which the columns are defined by the create table statement. Contrast with reordered row format. Basic sequential access method (bsam) an access method for storing or retrieving data blocks in a continuou...

  • Page 534

    Call attachment facility (caf) a db2 attachment facility for application programs that run in tso or z/os batch. The caf is an alternative to the dsn command processor and provides greater control over the execution environment. Contrast with recoverable resource manager services attachment facility...

  • Page 535

    Checkpoint a point at which db2 records status information on the db2 log; the recovery process uses this information if db2 abnormally terminates. Child lock for explicit hierarchical locking, a lock that is held on either a table, page, row, or a large object (lob). Each child lock has a parent lo...

  • Page 536

    Points. Within a code page, each code point has only one specific meaning. In ebcdic, for example, the character a is assigned code point x’c1’, and character b is assigned code point x’c2’. Code point in cdra, a unique bit pattern that represents a character in a code page. Code unit the fundamenta...

  • Page 537

    Cannot use any new functions of the new version. Contrast with compatibility mode*, enabling-new-function mode, enabling-new-function mode*, and new-function mode. Composite key an ordered set of key columns or expressions of the same table. Compression dictionary the dictionary that controls the pr...

  • Page 538

    Copy target a named set of sms storage groups that are to be used as containers for copy pool volume copies. A copy target is an sms construct that lets you define which storage groups are to be used as containers for volumes that are copied by using flashcopy functions. Copy version a point-in-time...

  • Page 539

    Cursor sensitivity the degree to which database updates are visible to the subsequent fetch statements in a cursor. Cursor stability (cs) the isolation level that provides maximum concurrency without the ability to read uncommitted data. With cursor stability, a unit of work holds locks only on its ...

  • Page 540

    Subsystems that directly access and change the same data while maintaining data integrity. Data sharing member a db2 subsystem that is assigned by xcf services to a data sharing group. Data source a local or remote relational or non-relational data manager that is capable of supporting data access v...

  • Page 541

    Are embedded within an application and are prepared during the execution of the application. Deferred write the process of asynchronously writing changed data pages to disk. Degree of parallelism the number of concurrently executed operations that are initiated to process a query. Delete hole the lo...

  • Page 542

    Distributed data facility (ddf) a set of db2 components through which db2 communicates with another relational database management system. Distributed relational database architecture (drda) a connection protocol for distributed relational database processing that is used by ibm relational database ...

  • Page 543

    Ebcdic extended binary coded decimal interchange code. An encoding scheme that is used to represent character data in the z/os, vm, vse, and iseries environments. Contrast with ascii and unicode. Embedded sql sql statements that are coded within an application program. See static sql. Enabling-new-f...

  • Page 544

    Exception table a table that holds rows that violate referential constraints or check constraints that the check data utility finds. Exclusive lock a lock that prevents concurrently executing application processes from reading or changing data. Contrast with share lock. Executable statement an sql s...

  • Page 545

    Space. Contrast with external procedure and native sql procedure. Failed member state a state of a member of a data sharing group in which the member’s task, address space, or z/os system terminates before the state changes from active to quiesced. Fallback the process of returning to a previous rel...

  • Page 546

    A number of both that are combined by set operators. Fullselect specifies a result table. If a set operator is not used, the result of the fullselect is the result of the specified subselect or fullselect. Fully escaped mapping a mapping from an sql identifier to an xml name when the sql identifier ...

  • Page 547

    “old” (for primary) and “new” (for secondary) structures. Group buffer pool (gbp) a coupling facility cache structure that is used by a data sharing group to cache data and to ensure that the data is consistent for all members. Group buffer pool recovery pending (grecp) the state that exists after t...

  • Page 548

    Be invoked as a job or jobstep, from a tso terminal, or from within a user’s application program. Idcams listcat a facility for obtaining information that is contained in the access method services catalog. Identity column a column that provides a way for db2 to automatically generate a numeric valu...

  • Page 549

    Inflight a status of a unit of recovery. If db2 fails before its unit of recovery completes phase 1 of the commit process, it merely backs out the updates of its unit of recovery at restart. These units of recovery are termed inflight . Inheritance the passing downstream of class resources or attrib...

  • Page 550

    Invalid package a package that depends on an object (other than a user-defined function) that is dropped. Such a package is implicitly rebound on invocation. Contrast with inoperative package. Ip address a value that uniquely identifies a tcp/ip host. Irlm see internal resource lock manager. Isolati...

  • Page 551

    Linear data set (lds) a vsam data set that contains data but no control information. A linear data set can be accessed as a byte-addressable string in virtual storage. Linkage editor a computer program for creating load modules from one or more object modules or load modules by resolving cross refer...

  • Page 552

    Lock promotion the process of changing the size or mode of a db2 lock to a higher, more restrictive level. Lock size the amount of data that is controlled by a db2 lock on table data; the value can be a row, a page, a lob, a partition, a table, or a table space. Lock structure a coupling facility da...

  • Page 553

    Luw see logical unit of work. Luwid see logical unit of work identifier. Mapping table a table that the reorg utility uses to map the associations of the rids of data records in the original copy and in the shadow copy. This table is created by the user. Mass delete the deletion of all rows of a tab...

  • Page 554

    Network identifier (nid) the network id that is assigned by ims or cics, or if the connection type is rrsaf, the rrs unit of recovery id (urid). New-function mode (nfm) the normal mode of operation that exists after successful completion of a version-to-version migration. At this stage, all new func...

  • Page 555

    Does not require the use of an sql preprocessor. In addition, odbc provides an architecture that lets users add modules called database drivers , which link the application to their choice of database management systems at run time. This means that applications no longer need to be directly linked t...

  • Page 556

    Parallelism coordinator in sysplex query parallelism, the db2 subsystem from which the parallel query originates. Parallel sysplex a set of z/os systems that communicate and cooperate with each other through certain multisystem hardware components and software services to process customer workloads....

  • Page 557

    Are the partitioning columns of the table. The index can be partitioned or nonpartitioned. Partner logical unit an access point in the sna network that is connected to the local db2 subsystem by way of a vtam conversation. Path see sql path. Pds see partitioned data set. Physical consistency the sta...

  • Page 558

    Table. A table cannot be defined as a parent unless it has a unique key or primary key. Principal an entity that can communicate securely with another entity. In kerberos, principals are represented as entries in the kerberos registry database and include users, servers, computers, and others. Princ...

  • Page 559

    Processes. See also cursor stabilityrepeatable read, and uncommitted read. Rebind the creation of a new application plan for an application program that has been bound previously. If, for example, you have added an index for a table that your application accesses, you must rebind the application in ...

  • Page 560

    Table is a descendent of the one before it, and the first table is a descendent of the last table. Referential integrity the state of a database in which all values of all foreign keys are valid. Maintaining referential integrity requires the enforcement of referential constraints on all operations ...

  • Page 561

    A commit point. See also cursor stability, read stability, and uncommitted read. Repeating group a situation in which an entity includes multiple attributes that are inherently the same. The presence of a repeating group violates the requirement of first normal form. In an entity that satisfies the ...

  • Page 562

    Was holding at the time of a subsystem failure. The lock is retained in the coupling facility lock structure across a db2 for z/os failure. Rid see record identifier. Rid pool see record identifier pool. Right outer join the result of a join operation that includes the matched rows of both tables th...

  • Page 563

    A collection of, and a way of qualifying, database objects such as tables, views, routines, indexes or triggers that define a database. A database schema provides a logical classification of database objects. Scrollability the ability to use a cursor to fetch in either a forward or backward directio...

  • Page 564

    Server the target of a request from a remote requester. In the db2 environment, the server function is provided by the distributed data facility, which is used to access db2 data from remote applications. Service class an eight-character identifier that is used by the z/os workload manager to associ...

  • Page 565

    (for example, max or avg). Contrast with built-in function, external function, and sql function. Source program a set of host language statements and sql statements that is processed by an sql precompiler. Source table a table that can be a base table, a view, a table expression, or a user-defined t...

  • Page 566

    References to user-defined functions, distinct types, and stored procedures. In dynamic sql, the sql path is found in the current path special register. In static sql, it is defined in the path bind option. Sql procedure a user-written program that can be invoked with the sql call statement. An sql ...

  • Page 567

    Stored procedure a user-written application program that can be invoked through the use of the sql call statement. Stored procedures are sometimes called procedures. String see binary string, character string, or graphic string. Strong typing a process that guarantees that only user-defined function...

  • Page 568

    Sysplex query parallelism parallel execution of a single query that is accomplished by using multiple tasks on more than one db2 subsystem. See also query cp parallelism. System administrator the person at a computer installation who designs, controls, and manages the use of the computer system. Sys...

  • Page 569

    Or sorting intermediate results from queries that contain a large number of rows. The two types of temporary table, which are created by different sql statements, are the created temporary table and the declared temporary table. Contrast with result table. See also created temporary table and declar...

  • Page 570

    Trigger body the set of sql statements that is executed when a trigger is activated and its triggered action condition evaluates to true. A trigger body is also called triggered sql statements. Trigger cascading the process that occurs when the triggered action of a trigger causes the activation of ...

  • Page 571

    Cics or ims environments can run under the tso attachment facility. Typed parameter marker a parameter marker that is specified along with its target data type. It has the general form: cast(? As data-type) type 2 indexes indexes that are created on a release of db2 after version 7 or that are speci...

  • Page 572

    User-defined data type (udt) see distinct type. User-defined function (udf) a function that is defined to db2 by using the create function statement and that can be referenced thereafter in sql statements. A user-defined function can be an external function, a sourced function, or an sql function. C...

  • Page 573

    Wlm application environment a z/os workload manager attribute that is associated with one or more stored procedures. The wlm application environment determines the address space in which a given db2 stored procedure runs. Wlm enclave a construct that can span multiple dispatchable units (service req...

  • Page 574

    A base table. This table stores the xml data, and the column in the base table points to it. Xml table space a table space that is implicitly created when an xml column is added to a base table. The table space stores the xml table. If the base table is partitioned, one partitioned table space exist...

  • Page 575: Index

    Index numerics 24 as hour value jdbc and sqlj applications 200 a accessibility keyboard x shortcut keys x accessing packages jdbc 21 sqlj 110 allow debug mode clause of create procedure 164 application programming jdbc 7 sqlj 101 assignment clause sqlj 291 auto-generated key retrieving in jdbc appli...

  • Page 576

    Data source connecting to using jdbc 9 connecting using drivermanager 11 connecting using jdbc datasource 15 data type mappings java, jdbc, and database 193 databasemetadata retrieving data source information, jdbc 21 databases compatibility ibm data server driver for jdbc and sqlj 4 datasource inte...

  • Page 577

    F failover support ibm data server driver for jdbc and sqlj type 2 connectivity on db2 for z/os 97 final call clause create function statement 164 g general-use programming information, described 512 getcause method 75 getdatabaseproductname identify data source type 23 getdatabaseproductversion ide...

  • Page 578

    Java database connectivity (jdbc) (continued) executeupdate methods running against db2 for z/os server 27 isolation levels overview 73 resultsets delete holes 43 inserting row 43, 44 transactions autocommit modes 74 java routine 158 environment variable settings 160 java routine with no sqlj progra...

  • Page 579

    N named iterator passed as variable 118 result set iterator 124 no sql clause of create function statement 164 clause of create procedure statement 164 o online 503 online books 503 online checking for better optimization 395 needed during customization 395 restriction 395 p parameter style clause o...

  • Page 580

    Sdk java version 1.5 150 secure socket layer ibm data server driver for jdbc and sqlj 458 security ibm data server driver for jdbc and sqlj 447 sqlj program preparation 462 security clause of create function 164 clause of create procedure 164 security, encrypted security-sensitive data ibm data serv...

  • Page 581

    Ssl support configuring java runtime environment 459 sslconnection property ibm data server driver for jdbc and sqlj 459 statement reference, sqlj 281 statement.Executequery retrieving data from db2 tables 32 stored procedure access to z/os unix system services 164 calling, sqlj 134 java 157 keeping...

  • Page 582

    566 application programming guide and reference for java ™.

  • Page 584

    Program number: 5635-db2 printed in usa sc18-9842-03.

  • Page 585

    Spine information: db2 version 9.1 for z/os application programming guide and reference for java ™.