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. With VisualAge Smalltalk Version 5.0, you can now 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.
For information on the Smalltalk call-in support, see VisualAge Smalltalk Server Guide.
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.
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)
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
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 Buffer[256];
/********************************************************************/ /* 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 ); }
The Smalltalk stored procedure is quite simple. To create one, you complete two steps:
To access the data, use the VisualAge 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.
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.
This example illustrates how to use visual parts provided by the VisualAge: 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 VisualAge Smalltalk 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 theVisualAge Smalltalk 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:
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.
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 VisualAge Smalltalk 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:
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
For information on wiring the stored procedure part, refer to the VisualAge Smalltalk Database Guide.
You should consider following when writing your stored procedures in Smalltalk: