• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    OCEAN User Group

    OCEAN TechCon25 Online

    It’s an Exciting Time for IBM i !

    July 16 & 17, 2025 – ONLINE

    Two virtual days of learning, presented by an outstanding group of IBM’ers and IBM Champions, featuring leading-edge topics.

    FREE for OCEAN members!

    Register NOW!

    Annual (12-month) Individual OCEAN Memberships are $80 and a Corporate Membership is $250. A Corporate Membership would allow your entire company to have full access to the OCEAN website & video library and to attend OCEAN events at member rates. Act now because rates are increasing on August 1, 2025.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    CocoBase Object-to-Relational Mapping Tool Gets DB2 Support Companies Want Good Enough IT, Not ‘Best of Breed’

    Leave a Reply Cancel reply

Volume 4, Number 29 -- September 1, 2004
THIS ISSUE
SPONSORED BY:

Advanced Systems Concepts
COMMON
Guild Companies

Table of Contents

  • Reusing HTML and Form Filling with RPG CGI
  • Using DRDA to Execute SQL Statements on Another iSeries
  • Admin Alert: Finding More Elusive OS/400 Commands

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle