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
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 ;
// Disconnect from the remote...
exec sql disconnect REMOTEDB2 ;
chk_SqlStt ( 'E9' : sqlstt );
// Do ( something ) with various SQLSTTs...
p chk_SqlStt b
d errNbr 2 const
d errStt 5 const
if ( errNbr = 'E1' and errStt = '08002' ); // Already exists...
// Ignore this case...
elseIf ( errStt <> *zero );
dsply ( errNbr + ' ' + errStt );
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.