SQL Connect to remote system, run retrieved data on local system problem

Tags:
RPGLE
SQL commands
I need some help with the syntax required to build the SQL commands (embedded into an RPGLE program) that will do the following:
  1. Connect to a remote system (using a userID & password passed into the program as parameters).
  2. Dynamically build ALTER commands based on data SELECTed from a table on the remote system. In other words, each row retrieved will be an ALTER command.
  3. Run each dynamically-created ALTER command (there may be multiple rows) on the local system.
As this is something I haven't done before with SQL, I'm not sure how the connection to the remote system is ended before running the retrieved commands on the local system. And because each row is a command, I'm not sure how to format the loop to execute each retrieved ALTER statement. Any & all help is very much appreciated! Thanks so much! Mike Chance, Catamaran
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

I don’t have something that does what you want, but I do have something that has all of the parts that you need. And you should be able to modify it to fit your requirements. With just a bit of preparation, you should be able to compile this and run it to see what it does:

     h  dftactgrp( *NO )
     h   actgrp( 'RMTCONN' )

     d QCUSTCDT      e ds                  occurs( 10 )

     d sqlStmt         s            512
     d errDsp          s             10

     d DB2USER         s             10a   inz( 'someuser  ' )
     d DB2PWD          s             10a   inz( 'somepwd   ' )

     d rmtST           s              2a   inz( 'NY' )

     d i               s             10i 0
     d gotRows         s             10i 0

     d chk_SqlStt      pr
     d  errNbr                        2    const
     d  errStt                        5    const

      /free
       exec SQL  SET OPTION
                     Naming    = *Sys,
                     Commit    = *None,
                     UsrPrf    = *User,
                     DynUsrPrf = *User,
                     Datfmt    = *iso,
                     CloSqlCsr = *EndMod ;

       dsply  'Begin' ;

       // Create a *LOCAL connection...
       // Reasonably fails w/SQLSTT='08002'...
       exec sql connect to LOCALDB2 ;
       chk_SqlStt ( 'E1' : sqlstt );

       // Create a remote connection...
       exec sql connect to REMOTEDB2   USER :DB2USER USING :DB2PWD ;
       chk_SqlStt ( 'E2' : sqlstt );


       // Point to the remote connection...
       exec sql set connection REMOTEDB2 ;
       chk_SqlStt ( 'E3' : sqlstt );


       // Open a cursor through the remote connection...

       sqlStmt = 'SELECT * FROM QIWS/QCUSTCDT WHERE STATE = ?' ;

       exec SQL  DECLARE C1 CURSOR FOR stmt ;
       exec SQL  PREPARE stmt FROM :sqlStmt ;
       chk_SqlStt ( 'E4' : sqlstt );


       exec SQL  OPEN C1 USING :rmtST ;
       chk_SqlStt ( 'E5' : sqlstt );


       // FETCH however many rows from the remote connection...

       exec SQL  FETCH C1 FOR 10 ROWS
                     INTO :QCUSTCDT ;
       chk_SqlStt ( 'E6' : sqlstt );

       gotRows = sqler3 ;


       // Close our cursor...
       exec SQL  CLOSE C1 ;
       chk_SqlStt ( 'E7' : sqlstt );


       // Point to the *LOCAL connection...
       exec sql set connection LOCALDB2 ;
       chk_SqlStt ( 'E8' : sqlstt );


       // Process rows we retrieved...
       //    Display library of the access path...

       for i = 1 to gotRows ;
          %occur( QCUSTCDT ) = i ;
          dsply LSTNAM ;
       endFor ;


       // Disconnect from the remote...
       exec sql disconnect REMOTEDB2 ;
       chk_SqlStt ( 'E9' : sqlstt );


       *inlr=*on ;
       return ;
      /end-Free

       // ----------------------------------------
       // Do ( something ) with various SQLSTTs...
       // ----------------------------------------

     p chk_SqlStt      b
     d                 pi
     d  errNbr                        2    const
     d  errStt                        5    const

      /free
       if ( errNbr = 'E1' and errStt = '08002' ); // Already exists...
          // Ignore this case...
       elseIf ( errStt <> *zero );
          dsply  ( errNbr + ' ' + errStt );
       endIf ;

       return ;
      /end-Free
     p chk_SqlStt      e

You’ll need to change the LOCALDB2 and REMOTEDB2 names to match the database names in your relational database directory (use WRKRDBDIRE). Also, change the DB2USER and DB2PWD variable values to be a valid user/password combination on the REMOTEDB2 database. (You can pass the password in as a parm or retrieve it from some external object if you don’t want to hard-code it. Be sure it doesn’t stay hard-coded in the source whenever you aren’t testing. That can maybe save you from being fired.)

To compile the program, you’ll need a file named QCUSTCDT in the compile’s library list. You might use CRTDUPOBJ to create a duplicate from the QIWS library into your personal library. Almost every site has that library with that file. (If yours doesn’t, then use any file that you can have on both systems.)

The SELECT statement qualifies to QIWS. If you create a duplicate on REMOTEDB2, you can get away from the qualification.

There might be minor bugs because I had to change a few things from my actual source. E.g., I don’t have systems nor databases named LOCALDB2 and REMOTEDB2. Other small changes were similar. It should be easy enough to fix any bugs from those changes.

Once it compiles, you will then need to run:

CRTSQLPKG PGM( mylib/RMTCONN ) RDB( REMOTEDB2 )

The name of the *SQLPKG here is RMTCONN. It should be the same name as the test program that you compile. When your program runs, the remote system will take the program name and try to find a matching *SQLPKG. Whenever you change the database processing in your program, you’ll need to run the CRTSQLPKG command again. The *SQLPKG needs to match what the program is going to do.

So, in short, set the database names, set user/password, get the QCUSTCDT file in place, compile the program, create the *SQLPKG, and then test the program.

The program sets up local and remote connections and then switches between them as needed. (Only one can actually be active at once.) A set of rows is FETCHed from the remote database. The example FETCHes 10 ROWS, but you might want to make the DS allow for 100 or 200 or more. Change the FETCH statement to match the size of your DS. (You can make it larger than the number of rows that will be returned.) The connection is finally switched back to the local database, and some retrieved values are displayed.

In order to create the cursor, a SELECT statement is put into a variable and passed to a SQL PREPARE statement. You will probably do something similar in the loop at the end. At first, you might simply use DSPLY to display each of the ALTER statements. Once your program works that far, you’ll probably change the loop to PREPARE each of your ALTER statements and EXECUTE them.

Use SQL PREPARE and SQL EXECUTE to get the ALTER run on your local system. Or if you can’t make those work, output them to a local file. You might create a second process that runs them as a SQL script.

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    Unless there is only a very small number of rows, e.g., 10 or fewer, you would not want to process them one row at a time. The overhead of (connect_remote, retrieve, disconnect_remote, connect_local, process ALTER, disconnect_local, repeat) would be too great. Better would be to (connect_remote, fetch all [or many] rows at once, disconnect, etc.).

    How many rows will you receive? A close guess is good enough.
    35,000 pointsBadges:
    report
  • mikechance81263
    Thanks so much for your help. I'm thinking it's less than 10. So, after I run the remote select and disconnect, I'll then be able to fetch the records and execute the rows as commands on the local system, right?
    25 pointsBadges:
    report
  • mikechance81263
    RealRaven, it has taken me WAY too long to get back to you and thank you SO very much for your help with this. All the elements you addressed in your example program I was able to incorporate into my program and it is working great. I really appreciate you taking the time and effort to get back to me.
    25 pointsBadges:
    report
  • TheRealRaven
    Pleased it helps. Some clarifying details could be added, but it sounds like you worked your way through.
    35,000 pointsBadges:
    report
  • kimcodingagain
    I used this process to connect to a remote iSeries db, successfully pulled records into variables, but am unable to call a local program passing these variables.  I'm getting a -204 error - not finding the local program?  Tried doing an insert into a local file and had the same result.  Any ideas you could shoot my way?
    20 pointsBadges:
    report
  • pdraebel
    Still connected to the remote? Close the connection.
    7,545 pointsBadges:
    report
  • kimcodingagain

    That was it - works all the way through now! 

    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: