|
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.
|
|
•
|
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.
|
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 VA Smalltalk Server Guide.
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:
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 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.
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.
To complete this example, you use two public class methods. run calls the createInsertQuerySpec: method.
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.
|
•
|
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 VA Smalltalk Database Guide details how to define a query and connection specification. For your convenience, here are the steps you can follow:
|
1.
|
|
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.
|
|
2.
|
For Database, select IBM Database 2 - CLI. Only IBM Database 2 - CLI supports Smalltalk stored procedures.
|
|
5.
|
Select OK.
|
|
1.
|
Pop-up the part's menu, and select Query > Create.
|
|
4.
|
|
6.
|
In the Computed Column Details dialog, select STAFF.YEARS, Unary operators from the menu bar, Sum(x), and then Apply.
|
|
7.
|
|
8.
|
From the part's pop-up menu, select Tear-off Attributes. Tear off the result row from the SingleRowQuery.
|
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 VA Smalltalk Server Guide.
After you add the methods, add AbtCallinSupport as a prerequisite and version your application.
For packaging, follow the steps given in VA Smalltalk Server Guide, with the following suggestions:
|
2.
|
On the Applications and ICs tab, move the application to the Selected Applications and ICs list.
|
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 VA Smalltalk Database Guide.
|
•
|
 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. |
