To interact with DB2, you can use nonvisual parts. Regardless of the DB2 release level, you can use also methods from the following classes to interact with DB2:

This section illustrates how to use the DB2 access classes. Sample source can be found in the class methods of AbtSampleStaticIbmDatabaseApp in the application with the same name. For the full sample database application, import AbtSampleStaticIbmDatabaseApp as part of the configuration map Server Samples in file abtmvssp.dat. See the Server Guide for more information. These sample methods use the query specs found in SQL package AbtTest (class AbtSqlPackageSample).

The following topics are included in this section:

Database access on workstations is made through a database connection. Workstation databases are accessed in client/server mode. There are several ways to obtain a connection.

You can use nonvisual parts to make a connection. Nonvisual parts use a connection spec to make a connection.

Or, using Smalltalk, you can write a simple method that asks the access set for a specific connection spec and then asks the connection spec for a connection. On workstations, the result is an actual connection to the database. As a result, the information provided by your application is used in the workstation's operating system.

For an example of obtaining a connection, see sample method getConnection in sample application AbtSampleStaticIbmDatabaseApp.

Using CLI for the connection

If you are using CLI, you do not need to perform any additional steps to make the connection.

After your transactional native application completes its database access, disconnect from the database by sending the disconnect method to the active connection.

You can dynamically run any SQL statement, other than SELECT, by passing a string containing the statement to an active connection using method executeSQLStatement:.

The method initializeTableQs follows:

For an example, see sample methods dropTableQs and initializeTableQs in application AbtSampleStaticIbmDatabaseApp.

You can statically run non-SELECT SQL statements by first creating a query spec for the statement and then by running the query spec. The query spec contains all of the information needed by the precompiler and DB2 to run the statement. Every query spec will contain a string that holds the SQL statement. If you want your program to pass data to the SQL statement at run-time, you must tell the query spec the data type and length of each of those data values. The collection of data types and lengths is called a shape. Each non-SELECT query spec has an input shape, even if that shape is empty.

You specify the input shape by providing an input shape string either to the instance of the query spec or through the TrailBlazer browser.

An input shape string contains what is essentially a declaration of each value passed between your application and the database. Each declaration contains the name of the value and a description of the value. The syntax for the description is the same as the syntax used to describe columns in a CREATE TABLE statement.

The sample method fillTableQs in application AbtSampleStaticIbmDatabaseApp shows how a query spec can be used to insert rows into a table. The query spec is named addRowToTable. The table contains two columns, a small integer and an eight-character string. The input shape looks like this:

The insert statement (also held by the query spec) looks like this:

The names INT and CHAR have nothing to do with the actual column names in the table. Instead, they are used to tell DB2 that the first value passed in the INSERT statement is a small integer and the second value is an eight-character string.

When it comes time to insert the rows, the application asks the SQL package for the query spec (using runtimeQuerySpecNamed:forPackageSpecNamed:). For each row to be added, the active connection will be prompted to run the query spec (using executeQuerySpec:). Data is passed to DB2 using a dictionary. The dictionary is keyed by the names of the values (which are the same names used in the input shape and in the query spec's SQL statement). The values in the dictionary are the data values to be passed to the database.

Running SELECT statements is very much like running non-SELECT statements except that the database passes data back to the program. An input shape is used to describe any data being passed to the database (as part of the WHERE clause). Another shape, called the output shape, is used to describe the data passed back to the program.

To query a database, get the query spec from the SQL package. The sample method named readFromTableQs in application AbtSampleStaticIbmDatabaseApp uses the query spec named queryTable to query the rows whose INTVAL value is greater than 0. Because multiple rows can be returned, the application asks the active connection for a result table that logically contains the rows described by the query spec. Rows are returned, one at a time, by iterating over the result table. During this iteration, the iterator is an instance of AbtIbmRow. The fields in the row are named, typed, and ordered according to the output shape.

You can delete a row from a table in one of the following ways:

The following sample code deletes all of the result table's rows by sending that result table the deletePresentRow method in a loop.

See the sample method deleteAllRowsFromTableQs in the application AbtSampleStaticIbmDatabaseApp.

You can update a row in a table in one of the following ways:

The following sample method, updateTableQs in application AbtSampleStaticIbmDatabaseApp, is an example of updating a result table's current row.

To update a result table's current row, make sure that the data (the columns listed in the FOR UPDATE CLAUSE) with which you will update the table is described in the Output Shape of the query spec.

The actual method that updates the table takes as an argument an instance of AbtIbmRow, whose compound type corresponds to the Output Shape of the SELECT query spec. You can instantiate this IbmRow yourself, or you can ask the query spec to instantiate and initialize a row for you. The method updateRowWithValues: takes a dictionary containing names and values to be used in the update and answers an AbtIbmRow. This row can be passed to the result table using method atCurrentRowPutRow:.

The sample application AbtSampleStaticIbmDatabaseApp uses methods outputGlobal and outputGlobal: to manage the global (CICSTTY for CICS or Transcript anywhere) to which messages should be written. In this way, the sample methods can be run in CICS Transaction Server.

For example, to run the readFromTableQs method in CICS, use the following startup code: