Execute SQL Statements on DB2 UDB for Windows from the iSeries

June 22, 2005 Michael Sansoterra

With the necessity of tight integration between platforms pervading many IT shops, I wondered what interoperability exists between the DB2 Universal Database (UDB) on the iSeries and DB2 UDB for Windows. After a little investigation, I was pleased to find that DB2 SQL on the iSeries can be used to execute SQL statements on the Windows DB2 platform as easy as it can on a different iSeries using IBM’s Distributed Relational Database Architecture (DRDA).

For a simple intro to DRDA using two iSeries systems or partitions, see Using DRDA to Execute SQL Statements on Another iSeries. For in-depth info DRDA, see the Distributed Database Programming guide in the IBM Information Center. In particular, Appendix B of this guide contains useful information on configuring cross platform database access.

Only a handful of SQL statements on the iSeries are necessary to query a remote server as shown here:

/* Connect to DB2 UDB for Windows Database Server. */
Connect To DB2Data USER ISERIES Using 'password'

/* Query data on the remote server */
Select *
  From DB2Data.DBOwner.DataTable

/* Call a stored procedure on the remote server */
Call DB2Data.DBOwner.UpdatePrice '2431R',214.50

For the record, my testing was performed on an iSeries running V5R2 Group PTF #9 with DB2 UDB for Windows version 8.1.2.169.

Configuring the iSeries

For the iSeries database manager to connect to another UDB server, a relational database (RDB) directory entry must be made for the remote server using the Add Relational Database Directory Entry (ADDRDBDIRE) command. In the case of UDB for Windows, the RDB directory entry must be made for each specific database to be accessed on the remote server. For instance, if you need to access five distinct databases from a single DB2 UDB for Windows server, you’ll need to create five RDB directory entries.

Here’s a sample entry made for a database called NRTHWIND on the DB2 UDB Windows server.

ADDRDBDIRE RDB(NRTHWIND)                    
           RMTLOCNAME('DB2WIN.COMPANY.COM' *IP)    
           TEXT('NRTHWIND Database on DB2WIN') 
           PORT(50000) 
           RMTAUTMTH(*ENCRYPTED *ALWLOWER)
           DEV(*LOC) 
           LCLLOCNAME(*LOC) 
           RMTNETID(*LOC) 
           MODE(*NETATR)
           TNSPGM(*DRDA)

The parameters to note in this command are:

RDB – Relational database entry name. This is both the name the iSeries will use to connect to the UDB server and it also must be the name of a specific database on the remote server.

RMTLOCNAME – enter the name or IP address of the remote DB2 UDB for Windows server. Specify *IP as the connection type.

PORT – the default port for DB2 UDB for Windows is 50000.

Configuring the DB2 UDB for Windows Server

To allow the iSeries to access its data, the following must be configured on the Windows DB2 server:

• A user name and password to access the database and database objects. The user name must be 10 characters or less. Longer names are supported in V5R3 (i5/OS).
• The server instance must be configured to allow remote clients to connect via TCP/IP. You can verify the port name by going into the DB2 control center, right clicking on the instance of DB2 you want to use and choosing Setup Communications. From here, select TCP/IP then click the properties button.

Querying Windows DB2 from the iSeries

A few attributes for the iSeries SQL session need to be set before proceeding. I’ll demonstrate using the STRSQL utility (but the same settings must be changed when using iSeries Navigator, embedded SQL and other iSeries SQL utilities.)

In STRSQL, press F13 and change the naming convention to *SQL, and the date format and time format parameters to *ISO. The *SYS naming convention may not be used when connecting to a Windows DB2 server. Also, if your job’s CCSID defaults to 65535, change the job’s CCSID to 37 (US EBCDIC) or whatever your local CCSID setting may be by using the CHGJOB CCSID(x) command.

We’re now ready to query DB2 for Windows from the iSeries. Connect to the remote database using the CONNECT statement:

Connect To Nrthwind User MIKE Using 'password'

When the statement completes successfully, you will have connected to your DB2 for Windows server’s Nrthwind database under the user name of MIKE. Don’t forget, when using the *SQL naming convention, the syntax to reference objects is: owner name.object name instead of the traditional iSeries library name/object name. For unqualified object names, the current user name is assumed to be the owner. In the sample connection demonstrated above, this query:

Select *
  From Orders

will actually translate to:

Select *
  From Mike.Orders

As already noted, it is assumed these objects are in the NRTHWIND database.

One other thing should be mentioned: the default transaction isolation level on DB2 UDB for Windows is cursor stability (CS) so you may want to code your statements to operate without an isolation level as in this example:

Update Nrthwind.Mike.DataTable
   Set DataValue=DataValue*2
 Where DataValue<5
  With NC

If you don’t do this, your statements will run within a transaction and you’ll need to issue a COMMIT or ROLLBACK when your statements have completed. Incidentally, this hints at a powerful feature of DRDA–the ability to create a transaction that spans SQL statements executing on multiple DB2 servers.

That’s all there is to it. When using DRDA don’t forget about the following SQL statements:

• Use the SET CONNECTION statement to change the active connection.
• Use DISCONNECT to drop a connection to the remote server.

Creating integrated applications is relatively easy thanks to tools such as DRDA. While in my opinion DRDA’s biggest limitation is that a single SQL statement can’t reference objects on multiple servers (i.e. sometimes called a heterogeneous query or distributed query in the Microsoft world) it is still a useful tool for performing ad-hoc queries and other maintenance tasks on remote DB2 servers. Further, by doing a little embedded SQL or Call Level Interface (CLI) programming, applications can easily query and manipulate data on several DB2 servers.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.

RELATED STORY

Using DRDA to Execute SQL Statements on Another iSeries