About Database OTDs

The Database OTD Wizard generates OTDs by connecting to external data sources and creating corresponding Object Type Definitions. The OTD Wizard can create OTDs based on any combination of Tables and Stored Procedures or Prepared SQL Statements. Field nodes are added to the OTD based on the Tables in the external data source. Java method and parameter nodes are added to provide the appropriate JDBC functionality. For more information about Java methods, refer to your JDBC developer’s reference guide.

Prepared Statements

A Prepared Statement OTD represents a SQL statement that has been compiled. Fields in the OTD correspond to the input values that users need to provide. Prepared statements can be used to perform insert, update, delete and query operations. A prepared statement uses a question mark (?) as a place holder for input. For example: insert into EMP_TAB (Age, Name, Dept No) values (?, ?, ?).

To execute a prepared statement, set the input parameters and call executeUpdate() and specify the input values if any.

When using a Prepared Statement, the ResultsAvailable() method will always return true. Although this method is available, you should not use it with a while loop. Doing so would result in an infinite loop at runtime and will stop all of the system’s CPU. If it is used, it should only be used with an if statement. You can process a ResultSet by looping through the next() method.

Generating ResultSet Nodes of a Stored Procedure

The OTD Wizard provides three different ways to generate the ResultSet nodes of a Stored Procedure. They are the By Executing, Manually, and With Assistance modes.

By Executing Mode	The By Executing mode executes the specified Stored Procedure with default values to generate the ResultSets. Depending on the business logic of the Stored Procedure, zero or more ResultSets can be returned from the execution. In the case that there are multiple ResultSets and the By Executing mode does not return all ResultSets, one should use the other modes to generate the ResultSet nodes. If you modify the ResultSet generated by the By Executing mode, you need to make sure the indexes match the Stored Procedure. This assures your ResultSet indexes are preserved.
With Assistance Mode	The With Assistance mode allows users to specify a query and execute it to generate the ResultSet node. To facilitate this operation, the wizard tries to retrieve the content of the specified Stored Procedure and display it. However, content retrieval is not supported by all types of Stored Procedures. We can roughly classify Stored Procedures into two types: SQL and external. SQL Stored Procedures are created using CREATE PROCEDURE SQL statements while external Stored Procedures are created using host languages such as Java. Since external Stored Procedures do not store their execution plans in the database, content retrieval is impossible. When using this mode, highlight the execute statement up to and including the table name(s) before executing the query.
Manually Mode	The Manually mode is the most flexible way to generate the result set nodes. It allows users to specify the node name, original column name and data type manually. One drawback of this method is that users need to know the original column names and data types. This is not always possible. For example, the column name of 3C in this query. SELECT A, B, 3C FROM table T is generated by the database. In this case, "With Assistance" mode is a better choice.

DB2 Database Configuration Information

The following topics contain information you should know when creating a DB2 OTD.

DB2 Database Connectivity Information

For DB2 OTDs, fill in the following fields for the database connection information:

Host Name: The server where DB2 resides.
Port: The port number of DB2.
Location (AS/400 and z/OS specific): The name of the DB2 subsystem. To find the location of the DB2 subsystem, use the Database Query Tool to issue the following query: select current server from sysibm.sysdummy1.
Database (Windows/UNIX specific): The name of the database instance.
Collection (AS/400 and z/OS specific): The name that identifies a group of packages. For more information on Packages, refer to DB2 and Binding Packages.
User Name: The user name that the adapter uses to connect to the database.
Password: The password used to access the database.
An Optional Parameters field (only for AS/400 and z/OS) will display in the OTD Wizard if the DB2_ConnectionInfo.txt file is present in the JavaCAPS_Home\.netbeans\usrdir\modules\ext\db2adapter directory. This field allows additional connection parameters for the DB2 OTD wizard. Please contact Oracle Corporation. for more information on Optional Parameters.

Note - This parameter is not specific to an OTD. It’s only specific to the session where Java CAPS IDE runs. When you edit the OTD, the current value that shows up is the value that was entered previously in the Wizard. It may or may not be the same value you used for creating the original OTD.

Parameter examples include:
- The showSelectableTables=false parameter can be set to false (default is true) to see additional tables listed under a user, such as the behavior in a previous Adapter which uses an older version of the driver.
- The AlternateID=user1 parameter allows you to set the object owner to be user1 otherwise the user in the User Name field will be used.
- Multiple parameters can be used and must be separated by a semi-colon (;).

DB2 and Binding Packages

This Adapter uses a DataDirect driver (previously known as Merant) to execute SQL calls in DB2. The DataDirect driver requires packages to be created in the DB2 System. Packages do not contain specific SQL statements like static SQL packages but rather dynamic sections, used like cursors to help facilitate the driver’s executing of dynamic SQL queries and returning results.

Creating packages on the server, also known as binding packages, needs only be done once. The first user of the OTD Wizard must have bind permission to create the packages. Without bind authority the user receives an error message when the driver attempts to bind the packages and they will be unable to issue any SQL call. Packages are created automatically, under the Collection ID, when the user fills in the Wizard entries. If the Collection ID is left as blank, it will generate the packages under NULLID.

The driver creates SQL packages on the database including: DDJC330A, DDJC330B, DDJC330C, and so on. When connecting, the driver queries a system table to determine whether the default packages exist on the system. If none exist, the driver creates them.

Note - SQL applications that execute dynamic SQL against DB2 need to have packages bound on the server. In the case of some IBM native tools this may not be obvious because the packages are already installed on the database by default.

DB2 Connect Database Connection Information

The connection information to specify for a DB2 Connect database varies depending on the type of connection you use, Type 2 or Type 4.

For the Type 2 Connection (shown above):

Database: The name of the database instance.
User Name: The user name that the Adapter uses to connect to the database.
Password: The password used to access the database.

For the Type 4 Connection (shown above):

Host Name: The server where DB2 Connect resides.
Port: The port number of DB2 Connect.
Database: The name of the database instance that the DB2 is running on Window/UNIX. The name of the locator for the DB2 running in z/OS or AS/400.
User Name: The user name that the adapter uses to connect to the database.
Password: The password used to access the database.

JDBC Database Connection Information

The required database connection fields for a JDBC OTD include the following:

Driver Jar Files: The location of the driver JAR file.
Driver Java Class Name: The name of the Driver Manager Class.
URL Connection String: The URL connection string for the driver.
User Name: A valid JDBC database username.
Password: The password for the user name noted above.

Informix Database Connection Information

The required database connection fields for an Informix OTD include the following:

Host name: The name of the host to which you are connecting.
Port ID: The host port number (1526 is the default).
Informix Server: The name of the Informix server.
Database name: The name of the database to which you are connecting.
User name: A valid Informix database username.
Password: A password for the user name noted above.

Oracle Database Connection Information

The required database connection fields for an Oracle OTD include the following:

Host Name: The server where Oracle resides.
Port ID: The port number of Oracle.
SID: The name of the Oracle instance (equivalent to the database name).
User Name: The user name that the Adapter uses to connect to the database.
Password: The password used to access the database.

SQL Server Database Connectivity Information

The required database connection fields for a SQL Server OTD include the following:

Host Name: The database service host name.
Port ID: The database service connection port ID/number.
Database name– the name of the SQL Server database.
User Name: A valid SQL Server database username.
Password: The password for the user name noted above.

Sybase Database Connectivity Information

The required database connection fields for a Sybase OTD include the following:

Host Name: The database service host name.
Port ID: The database service connection port ID/number.
Database Name: The name of the Sybase database.
User Name: A valid Sybase database username.
Password: The password for the user name noted above.

VSAM Database Connectivity Information

The required database connection fields for a VSAM OTD include the following:

Host Name: The database service host name.
Port ID: The database service connection port ID/number.
Database Name: The name of the VSAM database.
User Name: A valid VSAM database username.
Password: The password for the user name noted above.

Skip Navigation Links
Exit Print View
	Developing OTDs for Oracle Java CAPS Database Adapters Java CAPS Documentation