integrating rpg and cl programs by using the microsoft ole db provider for db2

Integrating RPG and CL Programs by Using the Microsoft OLE DB Provider for DB2

Host Integration Server Technical Article

Applies To:Host Integration Server 2000

Published: June 2003

AbstractThis document describes how to use DB2 stored procedures to call RPG (Report Program Generator) and CL (Control Language) programs (running on an IBM iSeries or AS/400 system) with the Microsoft® OLE DB Provider for DB2. Samples of RPG and CL server programs are provided, as well as sample Microsoft Visual Basic® 6, Microsoft Visual Basic .NET, and Microsoft Visual C#® .NET client-side code to connect to those programs.

Integrating RPG and CL Programs by Using the Microsoft OLE DB Provider for DB2 1

IntroductionInformation technology (IT) departments running IBM iSeries or AS/400 systems have a critical need for application integration between these systems and computers running Microsoft Windows®.

Microsoft Host Integration Server 2000 provides several ways to perform application integration between these systems, including DB2 database access, printer emulation, data queue access, and shared folders.

In addition, many customers are looking for ways to integrate Windows-based applications with their existing RPG and CL programs on host platforms. You can do this with Host Integration Server 2000 by wrapping an existing RPG or CL application with a DB2 stored procedure, and then using the Microsoft OLE DB Provider for DB2 to call this stored procedure. This solution is described in detail later in this document.

Why Not Use COMTI?When looking at the feature set of Host Integration Server 2000, COMTI (COM Transaction Integrator) immediately stands out as one of the most likely ways of integrating existing RPG and CL programs with a Windows-based application. However, COMTI currently only supports accessing applications on IBM zSeries and OS/390 mainframe systems. It is possible to use COMTI with an iSeries or AS/400 system, but only when running CICS/400 on that system. Only a very small percentage of IT shops that use iSeries or AS/400 systems run CICS/400.

Microsoft announced at this year’s TechEd conference that COMTI will be expanded in the future to support direct calls into RPG and CL programs, but until these features are released, most iSeries or AS/400 shops cannot use COMTI.

DB2 Stored ProceduresYou can integrate RPG and CL applications with Windows-based applications by using DB2 stored procedures. DB2 is IBM’s premier relational database management system (RDBMS), and has been tightly integrated into the iSeries and AS/400 product line for many years. A stored procedure is a common way in an RDBMS, such as DB2, to enable a precompiled set of procedures to be stored in the database and called by users and applications. This provides performance, consistency, data integrity, and security benefits depending on the situation.

By using DB2 on the iSeries and AS/400 platforms, you can define a stored procedure that calls into an external program such as an RPG program or a CL program. This stored procedure can pass parameters into the external program and retrieve return parameters, thus providing an easy way to integrate DB2 into existing applications.

The Windows ConnectionThe Microsoft OLE DB Provider for DB2, which ships as part of Host Integration Server 2000, has the ability to call into DB2 stored procedures. This is where the integration picture begins to take shape. You can leverage the ability of the DB2 stored procedures to call RPG and CL applications and in turn, integrate these applications with Windows-based applications through the OLE DB Provider for DB2.

The following code examples illustrate this solution.

2 Integrating RPG and CL Programs by Using the Microsoft OLE DB Provider for DB2

A Sample CL ProgramThe following CL code is a simple application that takes two input parameter strings and swaps them:

PGM (&STR1 &STR2) /*SWAPSTR*/

DCL VAR(&STR1) TYPE(*CHAR) LEN(10)

DCL VAR(&STR2) TYPE(*CHAR) LEN(10)

DCL VAR(&TMPSTR) TYPE(*CHAR) LEN(10)

CHGVAR &TMPSTR &STR1

CHGVAR &STR1 &STR2

CHGVAR &STR2 &TMPSTR

ENDPGM

After this code is compiled and available for execution, the next step is to define a stored procedure that “wraps” this CL program, calling into it with the appropriate parameters. You can use the following SQL statement to do this:

CREATE PROCEDURE MYLIB/SWAPSTR(INOUT PARAM1 CHAR (10 ), INOUT

PARAM2 CHAR (10 )) LANGUAGE CL NOT DETERMINISTIC NO SQL EXTERNAL

NAME MYLIB/SWAPSTR PARAMETER STYLE GENERAL

Note The syntax used in the preceding statement to qualify the collection name is COLLECTIONNAME/OBJECTNAME. Use this syntax when executing commands from an interactive SQL session (STRSQL) on the iSeries. When using the OLE DB Provider for DB2, use COLLECTIONNAME.OBJECTNAME, using a period rather than a forward slash.

Notice that the CL program takes two 10-character strings as input parameters. It uses a third local variable to swap the values of the two input strings, and then ends. If you examine the stored procedure, you see that it takes two 10-character input/output parameters, which it passes to the MYLIB/SWAPSTR application.

A Sample RPGLE ProgramThe following is RPGLE code for a similar application:

**************************************************************************

* Program SWAPRPG

* Initiated by Stored Procedure or Manual Call

* Function This RPGLE program swaps the values of the parameters passed.

**************************************************************************

H Option(*SrcStmt : *NoDebugIO)

**************************************************************************

* STAND ALONE VARIABLES


* ---------------------

D TempVar S 10A Inz Temp Var to Switch

**************************************************************************

* *ENTRY PARAMETERS

C *Entry Plist

C Parm ValueOne 10 Character Value One

C Parm ValueTwo 10 Character Value Two

**************************************************************************

* MAINLINE

**************************************************************************

C Eval TempVar = ValueTwo

*

C Eval ValueTwo = ValueOne

C Eval ValueOne = TempVar

*

C Eval *InLr = *On

**************************************************************************

This code performs exactly the same function as the sample CL program. It switches the values of the two input parameters. The following is the stored procedure definition used to call this RPGLE application:

CREATE PROCEDURE MYLIB/SWAPRPG(INOUT PARM1 CHAR (10), INOUT

PARM2 CHAR (10)) LANGUAGE RPGLE NOT DETERMINISTIC NO

SQL EXTERNAL NAME MYLIB/SWAPRPG PARAMETER STYLE GENERAL

Sample Client CodeTo complete the picture, the following sample code shows how to call into one of these stored procedures from a client application. The following is a code sample using ActiveX® Data Objects (ADO) and Visual Basic 6:

'setup required connection object

Dim ConnString As String

ConnString = "Provider=DB2OLEDB;Password=SNA;Persist Security Info=True;User

ID=SNA;Initial Catalog=MYHOST;Data Source=MYHOST_IP;Network Transport

Library=TCPIP;Network Address=MYHOST;Package Collection=MYLIB;Default Schema=MYLIB"

Dim myConnection As New ADODB.Connection

myConnection.ConnectionString = ConnString

myConnection.Open


'setup the actual command and command type

Dim myCommand As New ADODB.Command

myCommand.CommandText = "CALL MYLIB.SWAPSTR(?,?)"

myCommand.CommandType = adCmdText

myCommand.ActiveConnection = myConnection

'setup the parameters

Dim myParameter As ADODB.Parameter

Set myParameter = myCommand.CreateParameter("param1", adChar, adParamInputOutput,

10, "String1 ")

Call myCommand.Parameters.Append(myParameter)

Dim myParameter2 As ADODB.Parameter

Set myParameter2 = myCommand.CreateParameter("param2", adChar, adParamInputOutput,

10, "String2 ")

Call myCommand.Parameters.Append(myParameter2)

'execute

myCommand.Execute

myConnection.Close

There are a few key points to understand in this code sample:

The syntax of the SQL statement that calls the DB2 stored procedure is simply the CALL keyword followed by the stored procedure name. Question marks are used as placeholders for each of the parameters that will be passed in by the application.

The CommandType of the ADODB.Command object is set to adCmdText rather than adCmdStoredProc. This is necessary because ADO inserts an EXEC keyword rather than the CALL keyword if adCmdStoredProc is used.

Notice how two parameters are created that match the specification of the DB2 stored procedure, and then these are appended to the ADODB.Command object. It is important that the parameters defined in the sample code match the stored procedure to ensure that data is passed between the two applications correctly.

The following is a sample of Visual Basic .NET code demonstrating the same functionality:

'setup required connection object

Dim ConnString As String

ConnString = "Provider=DB2OLEDB;Password=SNA;Persist Security Info=True;User


Library=TCPIP;Network Address=MYHOST;Package Collection=MYLIB;Default Schema=MYLIB"

Dim myConnection As New OleDb.OleDbConnection(ConnString)


'setup the actual command and command type

Dim myCommand As New OleDb.OleDbCommand("CALL MYLIB.SWAPSTR(?,?)", myConnection)

myCommand.CommandType = CommandType.Text

'setup the parameters

Dim myParameter As New OleDb.OleDbParameter("param1", OleDb.OleDbType.Char, 10)

myParameter.Value = "String1"

myParameter.Direction = ParameterDirection.InputOutput

myCommand.Parameters.Add(myParameter)

Dim myParameter2 As New OleDb.OleDbParameter("param2", OleDb.OleDbType.Char, 10)

myParameter2.Value = "String2"

myParameter2.Direction = ParameterDirection.InputOutput

myCommand.Parameters.Add(myParameter2)

'execute

Try

myConnection.Open()

myCommand.ExecuteNonQuery()

myConnection.Close()

Catch e As Exception

Console.WriteLine(e.Message)

End Try

And finally, the following is a Visual C# version demonstrating the same functionality:

//setup required connection object

string ConnString = "Provider=DB2OLEDB;Password=SNA;Persist Security Info=True;User


Library=TCPIP;Network Address=MYHOST;Package Collection=MYLIB;Default

Schema=MYLIB";

OleDbConnection myConnection = new OleDbConnection(ConnString);

//setup the actual command and command type


OleDbCommand myCommand = new OleDbCommand("CALL MYLIB.SWAPSTR(?,?)", myConnection);

myCommand.CommandType = CommandType.Text;

//setup the parameters

OleDbParameter myParameter = new OleDbParameter("param1",OleDbType.Char,10);

myParameter.Value = "String1";

myParameter.Direction = ParameterDirection.InputOutput;

myCommand.Parameters.Add(myParameter);

OleDbParameter myParameter2 = new OleDbParameter("param2",OleDbType.Char,10);

myParameter2.Value = "String2";

myParameter2.Direction = ParameterDirection.InputOutput;

myCommand.Parameters.Add(myParameter2);

//execute

try

{

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

}

catch (Exception e)

{

Console.WriteLine(e.Message);

}

ConclusionThis document demonstrates not only that you can use Host Integration Server 2000 to integrate existing RPG and a CL application with Windows-based applications, but also that it is a straightforward process.

This integration is not limited only to RPG and CL applications. DB2 stored procedures support several other types of external programs including C, COBOL, FORTRAN, and REXX.


integrating rpg and cl programs by using the microsoft ole db provider for db2

Documents