Stored procedures have been used in many types of environments to reduce network traffic, improve performance, and access features that may only be available to the server. Currently, DB2 supports stored procedures written in C, C++, Java, COBOL, FORTRAN, and REXX, which has left the Smalltalk programmer with no option but to move their business logic from Smalltalk to one of the supported languages. This causes not only migration but also maintenance problems in a development shop. You can write stored procedures directly in Smalltalk.

This section covers how to implement and deploy your Smalltalk database code as a stored procedure in DB2.

To implement stored procedures in Smalltalk, you need to complete four basic steps. Further details for each step are given later.

To create and use stored procedures written in Smalltalk, ensure that you have the following in your image:

Also, you need the Server Runtime feature on your DB2 Server machine in order to run your Smalltalk Stored Procedure on your UDB Server machine.

The best way to describe how to create a stored procedure in Smalltalk is to demonstrate it. The steps in this section use the INPSRV2 example because most DB2 users are familiar with it.

To create the C wrapper DLL, you need the following software components:

In this example, each of the software components are provided in text format.

makeinp

The makeinp file is a make file for compiling the DLL using IBM VisualAge C++. For the call-in function, you need to link with abtvxt40.lib. For DB2, you need to link with db2api.lib. This make file is specific to IBM VisualAge C++. If you use a different compiler, make any needed changes. The source for makeinp is as follows:

inpsrv2.def

The inpsrv.def file is the definition file needed for the inpsrv2.dll. This file must give an entry point for your DLL. By convention, DB2 looks for a entry point that matches the name of the DLL; however, you can define multiple entry points in the one DLL. Multiple entry points within a wrapper DLL are useful for multiple stored procedures that have only one wrapper program.

The contents of the definition file are as follows:

inpsrv2.c

The inpsrv2.c program is a wrapper that calls the real Smalltalk stored procedure. It sets up the environment to be called into by defining the Smalltalk image name, the class that has implemented the logic and the entry point method. Each of these call-in parameters must contain a trailing blank. Note that only class methods are supported as entry points. Once the environment is set up, the image starts. Then, the entry point method is run and control is given to the Smalltalk stored procedure.

After the Stored Procedure is run, the Smalltalk image can be stopped. Since the starting, running, and stopping of the image are initiated separately, you can set up a stored procedure to leave the image loaded if you expect to call the procedure again. Ideally, you might synch up the SQL_DISCONNECT_PROC DB2 return code with the vastop call to stop only the image when the server program is released from DB2's main memory. In this example, only one parameter is passed into the Smalltalk image: a pointer to the SQLDA containing the parameters. Although this is labeled input_sqlda, it contains both input and output. In this example there is only input. For those who want to use the SQLCA area for reporting errors you must also pass this pointer to the Smalltalk image.

The code for the sample call-in program follows.

The Smalltalk stored procedure is quite simple. To create one, you complete two steps:

To access the data, use the VA Smalltalk server call-in support. The class method arguments in AbtCallinSupport return an instance of CallinArea. After you get the instance, you can use the callinData method to access the data. The client and server will need to have identical definitions for the type and size of the data being passed in.

In the sample code shown below, it is the address of an AbtMVSSqlda or an AbtWsSqlda, in other words the pointer to the input SQLDA area. Once an instance of AbtMVSSqlda or AbtWsSqlda is available, you can use methods to parse any variable information. The inpsrv2.c code assumes that the first variable will be the table name and that all other parameters will be entries into that table. However, to create more complicated examples, you should be familiar with the SQLDA structure. Refer to IBM DB2 UDB SQL Reference for a complete explanation of the structure. To parse the SQLDA, use the class named AbtMVSSqlda or AbtWsSqlda.

The sample code shown below shows the two key things you have to do when coding a Smalltalk stored procedure.

Example: Making the null connection and executing an SQL statement

To complete this example, you use two public class methods. run calls the createInsertQuerySpec: method.

After you handle the null connection and parse the passed parameters, you can add any additional business logic desired.

Example 2: Using Database visual parts

This example illustrates how to use visual parts provided by the VA: Database, DB2 CLI feature to define a Smalltalk stored procedure for a server application. This example also illustrates how to output data.

When using the visual parts to define Smalltalk stored procedures, keep in mind the following:

Defining a single-row query in the development image:   Begin by creating an application and naming it TstOutSProc. Add a non-visual part named CalcYear to the application. This application will have a single-row query. The Database Guide details how to define a query and connection specification. For your convenience, here are the steps you can follow:

In the Composition Editor that opens, do the following:

When the settings open you will need to define a connection specification. Press Connection Spec and then New to create one. In the New database connection specification dialog that opens, do the following:

Now that you have a connection specification, continue defining the single-row query:

Your Composition Editor should now look like this:

Next, change your application's prerequisites so that AbtViewApplication is no longer a prerequisite. Version the classes, and then version and release the application.

Adding function and packaging in an XD image:   Now that you have defined a query, create an XD image for the target server environment. Include Database -DB2 CLI and any other installed feature that your application might need. See the Server Guide.

Import your application into the XD image, create a new edition of the application, and then add the following methods to the class CalcYear:

After you add the methods, add AbtCallinSupport as a prerequisite and version your application.

For packaging, follow the steps given in Server Guide, with the following suggestions:

Next, you must register the stored procedure. You can register the stored procedure by executing a CREATE PROCEDURE statement. For example, the following example would register the inpsrv2 stored procedure:

For information on wiring the stored procedure part, refer to the Database Guide.

You should consider following when writing your stored procedures in Smalltalk: