Enable Programmatic Access to Remote DB2 Data Using DRDA

I’m trying to set up a stored procedure on our iSeries that can change the server connection to whatever the calling program needs before it runs its query. Your article, Using DRDA to Execute SQL Statements on Another iSeries, seems to address the issue of running SQL statements on a remote iSeries.

I am new to DB2 and although I can set up stored procedures within System i Navigator with some success, the connection statement only produces errors on compile. Your example runs the commands interactively. Is there a syntax convention or another statement to accomplish this task in a stored procedure?

–Ben

Hi Ben:

Thanks for the inquiry. This question reminded me that I never finished my series on DRDA (Distributed Relational Database Architecture) tips! The first two are found here:

• Using DRDA to Execute SQL Statements on Another iSeries–This tip covers the basics of how to get DRDA up and running and how to use it in an interactive SQL session.
• Execute SQL Statements on DB2 UDB for Windows from the iSeries–This tip discusses how to query an instance of DB2 for Windows from the IBM i using DRDA.

The tip I meant to write was on how to use DRDA in a programmatic setting. So here we go.

First, to answer your question, when connecting to another IBM i server using DRDA (with the CONNECT statement) in embedded SQL or the SQL procedural language, there are a few important aspects you need to understand. I’ll demonstrate the technique using the SQL procedural language (which is easily adaptable to embedded SQL).

1. Pass the user name and password as host variables.

Here is sample CONNECT statement with supporting variables:

Declare @User VarChar(128) Not Null Default 'myuser';
Declare @Password VarChar(128) Not Null Default 'mypassword';
Connect to REMOTESYS user @User using @Password;

2. When using DRDA you may only access one system at a time.

For example, once you issue a CONNECT statement to system REMOTESYS all subsequent SQL statements will be submitted to the REMOTESYS server. Use the SET CONNECTION statement to switch servers when maintaining multiple connections.

This connection switching is one of DRDA’s biggest drawbacks. It means when coding you have to store in host variables all the information you want to preserve when switching between connections. For example, if you want to get the CURRENT_TIMESTAMP register of a remote server and compare it to the CURRENT_TIMESTAMP of the local server you need to:

• CONNECT to the remote system
• Read the CURRENT_TIMESTAMP register into a host variable
• Set the connection back to the local system
• Read the CURRENT_TIMESTAMP register into a second host variable
• DISCONNECT from the remote system

To do something more complicated like copy rows from a remote system’s table into a local table, there are more steps:

• CONNECT to the remote system
• Open a cursor
• Fetch a row against the remote table into host variables
• Set the connection to the local system
• Issue an Insert/Values statement against a local system table using host variables
• Set the connection to the remote system
• Repeat the third step of fetching a row against the remote table into host variables until there are no more rows
• Close the cursor
• Clean up

Depending on the application, an alternative method would be to do a block fetch from the remote system and a block insert on the local system.

3. Create and distribute an SQL package.

Once your SQL routine is created (a stored procedure or table function, for example) you must create and deploy an SQL package to all remote servers that the routine accesses. Use the Create SQL Package (CRTSQLPKG) command to create the package. CRTSQLPKG will create an object with the same name as the routine with an object type of *SQLPKG. Assuming an SQL stored procedure named TEST1 exists, here is a sample CRTSQLPKG command:

CRTSQLPKG PGM(I3/TEST1)

When CRTSQLPKG completes, there will be an object of type *SQLPKG called TEST1 in schema i3.

When the package object is created you must distribute the package to the remote system(s) into the same schema name (i.e., library) that the local routine resides in using a third-party tool or by putting it in a save file and using a binary FTP transfer.

For embedded SQL developers, there is a relational database parameter on the compile commands (CRTSQLRPI, CRTSQLCBLI) that can be used to automatically build a package on a remote system. If there are multiple destination systems, then the compile command will have to be run multiple times (once for each remote system) or the package will need to be distributed manually as described above.

If the SQL package is not placed on the remote server, you’ll get the following error when you attempt to execute a DRDA enabled SQL routine:

SQL State: 51002
Vendor Code: -805
Message: [SQL0805] SQL package TEST1 in I3 not found at DRDA Server.
Cause . . . . . : A remote request was attempted to RMTAS400 for SQL 
package TEST1 in I3. The SQL package was not found. If you are using 
Interactive SQL or Query Manager, an attempt to create a package on the
remote system failed (see common cause below) and the package 
requested does not exist.
Recovery . . . : The most common cause of this problem in an
Interactive SQL session to a non-iSeries server is that the connection
is not updateable. In that case the package cannot be automatically
created. To ensure the connection is updateable, do a RELEASE ALL
command followed by a COMMIT before connecting to the relational
database. In other cases, the SQL package can be created by using
the CRTSQLPKG command. Also, precompiling an SQL program 
with the RDB parameter specified will create an SQL package on
the system. Create or restore the SQL package.

There is one other potential error that can occur. Assuming your SQL routine is running properly against remote systems, if you make a change to your SQL routine or embedded SQL program you do need to re-create the packages and distribute them. If you forget to update your package on a remote system, you’ll get the following error:

SQL State: 51003
Vendor Code: -818
Message: [SQL0818] Consistency tokens do not match.
Cause . . . . . : Package TEST1 in I3 on application server RMTAS400
cannot be run with the current application program because either the
application program has been recompiled without rebuilding the
package or the package has been restored from a back level version.
Recovery . . . : Rebuild the package by using the CRTSQLPKG command
or by using a CRTSQLxxx command specifying the correct relational
database. Otherwise, restore the package from a version which matches
the application program being run.

A Simple Example

For good measure, here is a sample stored procedure that reads the CURRENT_SERVER register from a remote server and the local server and returns both values in a single row result set:

Create Procedure i3/Test1
Language SQL
Result Sets 1
Set Option Commit=*None, UsrPrf=*Owner
Begin
 Declare @User VarChar(128) Not Null Default 'USERID';
 Declare @Password VarChar(128) Not Null Default 'PWD';

 Declare @Server1 VarChar(128);
 Declare @Server2 VarChar(128);

 Declare Result1 Cursor For
 Select @Server1||' & '||@Server2 As ServerList
 From SysIBM/SysDummy1;

 Connect to RMTAS400 user @User using @Password;

 Set Connection RMTAS400;
 Set @Server1=Current_Server;
  Set Connection LCLAS400;
 Set @Server2=Current_Server;

 Open Result1;
 Disconnect RMTAS400;
End;

Because of the single simultaneous database server limitation, I often prefer using Java for this kind of work because Java can access multiple systems simultaneously. Also, Java doesn’t suffer from the SQL package distribution hassle that DRDA has. Nevertheless, DRDA can still prove itself useful in many situations such as interactive querying and for developers who are comfortable with SQL but not Java.

Hey, Mike:

Thank you very much for getting back to me. This worked. Unfortunately, I did not foresee the SQL package issue. That tosses a wrench in the works. I am a report writer and trying to get a simple solution that would allow me to write one procedure to run off multiple servers and libraries that make up our development testing and production data. Now it takes a lot of duplication.

–Ben

Ben:

Usually what I do in your situation is duplicate the stored procedure among systems and then just change the connection string in the report writer (hopefully you can do that in your case.) Either way though, you’ll need to duplicate something; an *SQLPKG or the stored procedure itself.

If you know Java, a possible alternative would be to write an external stored procedure in Java and then pass a system name as a parameter. For example:

Call spGetDataJava ('LIVE_AS400',parm1,parm2)

Then your Java program would be responsible for connecting to the appropriate system at run time with the benefit that you’ll have only one stored procedure that can connect to any i, iSeries, or AS/400.

–Mike

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Send your questions or comments for Michael to Ted Holt via the IT Jungle Contact page.

RELATED STORIES AND REFERENCES

Using DRDA to Execute SQL Statements on Another iSeries

Using DRDA to Execute SQL Statements on Another iSeries

System i: Database Distributed database programming

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot