Using stored procedures in a DB2 CLI application
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.
An overview of implementing stored procedures
To implement stored procedures in Smalltalk, you need to complete four basic steps. Further details for each step are given later.
1. Create a C wrapper DLL. The C wrapper DLL is a small DLL that DB2 starts when the stored procedure is called. It starts a Smalltalk image. This image is the glue for your stored procedure. The DLL uses the call-in support provided by Server Smalltalk. For the purposes of this information, a C program is used but it could also be written in Cobol.
2. Code the Smalltalk stored procedure. The Smalltalk stored procedure is your server logic. It can be identical to anything you would do in a client Smalltalk application or in a stored procedure written in C. Your code must set up a connection so that DB2 can associate the server connection handle with the underlying connection of the client application. This is done using the Smalltalk methods nullConnect, nullConnectIfError:, nullConnectWithAlias:, or nullConnectWithAlias:ifError:, which are implemented in AbtIbmCliDatabaseConnection. Also, you must package your code using the call-in support provided in Server Workbench. This enables the C Wrapper DLL to start up, run, and stop the Smalltalk image.
3. Register the stored procedure. You register the stored procedure the same way as for any stored procedure that you would write for DB2. As with any stored procedure, registering is not required but is often helpful. The C wrapper DLL is the piece that is registered in DB2CLI.PROCEDURES table (a pseudo-catalog table).
4. Create a client application that uses the stored procedure. You create the client application the same way as for any stored procedure. Nothing unique is needed on the client. This section uses a nonvisual stored procedure part. However, the inpcli2.c program could also work as the client.
The support you need to implement stored procedures
To create and use stored procedures written in Smalltalk, ensure that you have the following in your image:
•Database parts-The stored procedure part is in the VA: Database, DB2 CLI feature.
•IBM ST: Server, Database feature
•IBM ST: Server Workbench, Base feature - The Server Workbench is required for packaging your Smalltalk stored procedure so that it can be started and stopped from the C wrapper DLL.
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.
Implementing Smalltalk stored procedures
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.
For information on INPSRV2 and stored procedures, refer to materials on writing Stored Procedures in the IBM DB2 Universal Database Embedded SQL Programming Guide and to materials on using advanced features in the IBM DB2 Universal Database Call Level Interface Guide and Reference .
For information on the Smalltalk call-in support, see Server Guide.
Creating the C wrapper DLL
To create the C wrapper DLL, you need the following software components:
•A make file for compiling the DLL
•A definition file that gives the entry point for the DLL
•The program source file, a C program in this example, that is itself the wrapper that calls the Smalltalk stored procedure
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:
# Windows compile and link (IBM C/C++ 3.5)
# This was taken from the DB2 makefile so I can model my DLL after inpsrv
CC=icc
LINK=ilink
CFLAGS=-c+ -Ti -Ge- -Gm+ -W1 -DABT_SYSTEM -Ic:\progra~1\vast\callin
LINKFLAGS=/MAP /DEBUG /ST:32000 /PM:VIO
LINKFLAGS1=/ST:64000 /PM:VIO /MAP /DLL
LINKFLAGSDE=/MAP /DEBUG /ST:250000 /PM:VIO
LIBS= $(DB2PATH)\lib\db2api.lib c:\progra~1\vast\callin\abtvxt40.lib \
f:\ibmcpp\LIB\cppwm35.lib f:\ibmcpp\LIB\kernel32.lib \
f:\ibmcpp\SDK\LIB\user32.lib f:\ibmcpp\SDK\LIB\gdi32.lib
all: inpsrv2
clean:
- del inpsrv2.obj inpsrv2.dll inpsrv2.map inpsrv2.exp
inpsrv2.dll : inpsrv2.c;
$(CC) $(CFLAGS) inpsrv2.c
inpsrv2 : inpsrv2.dll;
ilib /GI inpsrv2.def
$(LINK) $(LINKFLAGS1) inpsrv2.obj inpsrv2.exp $(LIBS)
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:
LIBRARY inpsrv2
EXPORTS
_inpsrv2@16
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.
/********************************************************************/
/* Smalltalk Callin Sample Program (C) */
/********************************************************************/
/*
#include #include #include #include // For DB2
#include // For DB2
#include // For DB2
#include #include "abtvxt40.h" // For Smalltalk VM Callin
void errorRtn(void);
/* Declarations for Smalltalk callin function */
VAST_HANDLE vhandle;
VAST_STATUS vstatus;
VAST_RESULT vresult;
SQL_API_RC SQL_API_FN inpsrv2 ( void *reserved1,
struct sqlda *input_sqlda,
struct sqlda *dummy,
struct sqlca *ca)
{
/* Declarations for Smalltalk calling function */
char imageName??(64??) = {0};
char receiver??(64??) = {0};
char selector??(64??) = {0};
DWORD len = 256;
LPSTR Buffer256];
/********************************************************************/
/* Initialize required Smalltalk VM Callin parameters */
/* Note all parms must have a trailing blank */
/********************************************************************/
memcpy(imageName,"CALLIN03 ",9); /* NOTE: extension is not used */
memcpy(receiver,"Inpsrv ",7); /* Class being called */
memcpy(selector,"run ",4); /* Class Method being called */
/********************************************************************/
/* Smalltalk VM Startup */
/********************************************************************/
vastart(imageName,&vhandle,&vstatus);
if (vstatus != 0 || vhandle == 0 ) errorRtn;
/********************************************************************/
/* Smalltalk VM Run Image */
/* This example only sends the input_sqlda. In this example you */
/* will not be able to use the SQLCA to pass errors */
/********************************************************************/
varun(&vhandle,receiver,selector,sizeof(input_sqlda), input_sqlda,
&vstatus,&vresult);
if (vstatus != 0) errorRtn;
/********************************************************************/
/* Smalltalk VM Stop */
/********************************************************************/
vastop(,&vhandle,&vstatus);
if (vstatus != 0) errorRtn;
return( SQLZ_DISCONNECT_PROC );
}
void errorRtn()
{
printf("Error - VSTATUS = %d\n",vstatus);
return( SQLZ_DISCONNECT_PROC );
}
Creating the Smalltalk stored procedure
The Smalltalk stored procedure is quite simple. To create one, you complete two steps:
1. Make a null connection so that you are running under the same connection as the client application. Use the nullConnect, nullConnectIfError:, nullConnectWithAlias:, or nullConnectWithAlias:ifError: instance method in AbtIbmCliDatabaseConnection.
2. Parse the parameters passed in.
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.
run
"This is the entry point for the Callin Stored Procedure"
| rc aConnect sqlda arguments table statement aQuery aDictionary |
"Null Connect: create a new connection and then associate it with the client connection"
aConnect := AbtIbmCliDatabaseManager new newDatabaseConnection.
aConnect nullConnect.
" Create the table by Directly Executing a Statement"
sqlda := AbtWsSqlda address: ((AbtCallinSupport arguments) callerData address) .
table :=(sqlda sqldataAt: 1) abrAsString.
statement := 'Create table ', table, ' (name CHAR(20))'.
aConnect executeCallForSQLStatement: statement ifError: :error | error ].
"Insert the entries into the table using a QuerySpec. "
"This prepares and binds the statement once while executing it multiple times. "
aQuery := self createInsertQuerySpec: table.
aDictionary := Dictionary new.
2 to: (sqlda sqld) do: [:num |
aDictionary at: 'NAME' put: ((sqlda sqldataAt: num) abrAsString).
aConnect executeQuerySpec: aQuery withValues: aDictionary.
].
aConnect commitUnitOfWork.
^aConnect disconnect.
createInsertQuerySpec: table
"This code was borrowed from the non-visual database query spec and is
used to create the host variable shape."
^(AbtQuerySpec new name: 'InsertRow';
statement: 'INSERT INTO ', table, ' (NAME) VALUES (:NAME)';
description: 'Insert a value into the table built. ';
hostVarsShape: (AbtCompoundType new
name: '';
addField: (( AbtDatabaseFixedCharField new name: 'NAME'; nullsOk: true;
procBindType: 1; count: 20)));
outputShape: (nil)
)
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:
•An alias is created for you. Thus, to set up a connection so that DB2 can associate the server connection handle with the underlying connection of the client application, use the methods nullConnectWithAlias: or nullConnectWithAlias:ifError:.
•You must specify output data for the sqlind field of the SQLDA. If you have no output, specify any negative number. If the value for sqlind is a negative number, then no output is returned. If you specify a positive number, then the database will look in the data field for a value. For information on the sqlind parameter, refer to the DB2 and SQL documentation.
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:
1. Drop a Single Row Query part on the free-form surface and change its name to SumUpTotalYears.
2. Open the part's settings and, when prompted to define an access set, select Yes and name the access set.
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:
1. For Alias, specify CalcYearConnectionSpec
2. For Database, select IBM Database 2 - CLI. Only IBM Database 2 - CLI supports Smalltalk stored procedures.
3. Select a data source name.
4. Deselect Prompt for logon information.
5. Select OK.
Now that you have a connection specification, continue defining the single-row query:
1. Pop-up the part's menu, and select Query > Create.
2. Select 'sum(staff.years) from staff'
3. Specify a name and a description for the query.
4. From the Tables/view list, select Staff.
5. Pop-up the menu for the computed column list box and select Create.
6. In the Computed Column Details dialog, select STAFF.YEARS, Unary operators from the menu bar, Sum(x), and then Apply.
7. In the Computed Columns list, select STAFF.YEARS and then Apply.
8. From the part's pop-up menu, select Tear-off Attributes. Tear off the result row from the SingleRowQuery.
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:
Class method doCalc:
doCalc
"The entry point for the callin function of the Stored Procedure.
This must be a Class Method for the callin support to access it."
CalcYear new doCalc.
Instance method doCalc:
doCalc
"This is the entry point for the application"
| sqlvar aConnect total |
"Get the callin SQLDA area."
sqlvar := AbtFixedSqlda sqldaClass
address: (AbtCallinSupport arguments callerData address).
"Create a null connection based on the caller of the stored procedure"
AbtIbmCliDatabaseManager active newDatabaseConnection
nullConnectWithAlias: 'CalcYearConnectionSpec'.
"Execute desired queries"
(self subpartNamed: 'sumUpTotalYears') executeQueryAsTransaction.
"Place the results back into the input\output SQLDA"
(sqlvar sqldataAt: 1) int32At: 0 put:
((self subpartNamed: 'resultRow of sumUpTotalYears')
abtAtAttribute: #'1').
(sqlvar sqlindAt: 1)
int32At: 0
put: (((self subpartNamed: 'resultRow of sumUpTotalYears')
abtAtAttribute: #'1') isNil
ifTrue: -1]
ifFalse: 0]).
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:
1. On the Create New Instructions tab, select the XD Single Image with Callin instructions.
2. On the Applications and ICs tab, move the application to the Selected Applications and ICs list.
Registering the stored procedure
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:
CREATE PROCEDURE inpsrv2 (IN table char(20), IN entry1 char(20),
IN entry2 char(20), IN entry3 char(20))
EXTERNAL NAME 'inpsrv3!inpsrv3' LANGUAGE c PARAMETER STYLE db2dari
Wiring the stored procedure part
For information on wiring the stored procedure part, refer to the Database Guide.
Important considerations
You should consider following when writing your stored procedures in Smalltalk:
•Do not try to connect to the database in your stored procedure. Your stored procedure should rely on the client's database connection.
•Using the database nonvisual parts can be complicated since you use them in the Composition Editor, whereas the server call-in support is in your XD environment only (that is, no Composition Editor). The best way to handle this is build your stored procedure in your development image. Get it working the way you intend it to and then move over to the XD image and add the support to parse the IOAREA.
•For Windows: The server call-in function is somewhat restrictive in that you can not specify a directory to look for the .icx file. It will always look in the current directory. For Windows, it appears that UDB makes c:\winnt\system32 the current directory when running stored procedures.
•The great thing about stored procedures is their flexibility. The key to using them is making sure your client and server are synchronized. UDB provides a fairly open-ended structure of the SQLDA for passing parameters and SQLCA for passing error messages. The call-in function provides an open structure in the IOAREA. So use this as just a guideline and not as a formula. Also, use the problem set to determine where you should do what. Control can be added in the client, the Smalltalk server, or the C wrapper program. Do what makes the most sense for your requirements.
Last modified date: 05/20/2020