Using DRDA to Execute SQL Statements on Another iSeries

September 1, 2004 Michael Sansoterra

For iSeries shops using SQL with multiple machines or logical partitioning, IBM’s Distributed Relational Database Architecture (DRDA) may help with SQL administration tasks. For example, it is common to develop an SQL routine on a test box and then duplicate the routine on a production box, which requires a cut and paste of SQL text or some other mechanism to propagate the routine. Or, sometimes it may be handy to know what’s in a file on the live system while working on the test system, without having to go through the pain of starting a new 5250 session or using iSeries Navigator.

DRDA helps with these scenarios by allowing a local iSeries to execute SQL statements on a remote server (this includes DB2 databases running on other platforms, but for simplicity we’ll just consider the iSeries server). DRDA allows you to, for instance, issue a CREATE FUNCTION statement or view the results of a SELECT statement on a live box, all from the confines of your favorite SQL utility, such as STRSQL, on your test box.

SETTING UP DRDA

The setup is easy. For simplicity, I’ll assume that both iSeries boxes (or logical partitions) have TCP/IP enabled and that there are no firewalls in between. To illustrate, we’ll designate the two boxes as LIVE (remote) and TEST (local), and configure the test box to execute SQL statements on the live box.

LIVE (REMOTE) BOX CONFIGURATION

On the remote system, DRDA uses the DDM (Distributed Data Management) server to receive and process remote SQL requests. You can verify that the DDM server is running by testing whether job QRWTSRVR is active on the system (WRKJOB QRWTSRVR). To start this server, use the following command:

STRTCPSVR SERVER(*DDM)

TEST (LOCAL) BOX CONFIGURATION

On the local system, you need to add an entry for the live (remote) iSeries in the relational database directory entry. This can be done by executing the Work with RDB Directory Entry (WRKRDBDIRE) command. Use option 1 to add an entry for your remote system. There are three required settings.

Relational database is the name that references the remote iSeries in your SQL statement.

Remote location is the host name or address of the remote iSeries in your environment.

Type specifies the connection type. Select *IP.

Alternatively, the entry can be made by executing the Add RDB Directory Entry (ADDDRBDIRE) command:

ADDRDBDIRE RDB(LIVE)                    
           RMTLOCNAME(LIVE.YOURCOMPANY.COM *IP)
           TEXT(PRODUCTION)

USING DRDA

On the test box, start the interactive SQL utility using the STRSQL command. By default you will be connected to the database on the local server. Use the CONNECT statement to change the current connection to the remote server. CONNECT requires the name of the RDB entry for the remote server and a user name and password:

/* connect to remote iSeries with user profile MIKE */
CONNECT TO LIVE USER MIKE USING 'password'

In this example, LIVE is the name of the remote system that was added to the RDB directory.

Once connected successfully, all subsequent SQL statements will be executed against the remote server until the connection is changed. (Note that you can’t mix objects from different servers.) For safety, you can even use a three-part name (server/schema/object) on certain SQL statements that will only allow the statement to run when connected to the specified server:

/* This will only run when 
   connected to the TEST box */
Update Test/DataLib/Orders
   Set ShippedFlag='Y'
 Where ShipDate=Current_Date

To switch back and forth between the test and live boxes in the SQL session, use the SET CONNECTION statement:

SET CONNECTION TEST

SET CONNECTION LIVE

If you ever lose track of what system you’re on, simply type CONNECT by itself:

CONNECT   /* Shows Current Connection */

Or query the Current_Server register:

Select Current_Server
  From SysIBM/SysDummy1

When finished with the remote connection, issue the DISCONNECT statement:

DISCONNECT LIVE

Currently, I’m at a site with a test and live box where only the test box has the SQL Development Kit installed (STRSQL). Also, I’m not allowed to use iSeries Navigator unless I’m on site, which is rare. This makes using SQL on their live box difficult. DRDA has come in handy by allowing me, from their test box, to do quick ad-hoc queries of the live data and to easily issue CREATE FUNCTION and CREATE PROCEDURE statements on the live box after I’ve finished testing them.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. E-mail: msansoterra@itjungle.com