New Embedded SQL to pull data from 1 DB to another is failing

Tags:
DB2
SQL
We just started using a TEST DB2 machine for the first time, for the past 20 years we've simply had test libraries on the Production DB2 box. Whenever I needed test data before, I just ran a CL I had created to copy *REPLACE the selected files. Our DB2 administrator is refusing to allow DDM to pull data from Production down to test. We are being told to just request a restore of the files we need as needed. I was shown a way to interactively pull one file by using Interactive SQL and F13/option 1 to set output to be a file . I am attempting to write program to do something like this. I chose to use free-form to practice because I haven't been using it. My code is getting an SQL error at the FETCH into an external Data structure dsACMS. SQL STATE 428GX SQLCODE -000020430 SQLERRMC = `aDSACMS
I looked up the SQL state and see:
Start of change428GXEnd of change Start of changeA global variable cannot be set or referenced in this context.End of change Start of change-20430End of change
I confirmed that the Record format of the (DEV)/JDPACMS on the test box and (production)/JDPACMS are identical.
I am getting an odd compile warning level 10 saying object dsACMS in *LIBL not found, but I say the external name is JDPACMS...
Can someone explain why I'm getting this error? Code below:
********************************************************************************************** * UTRRMTCPY - SQLRPGLE (FREE) - Copy 1 file from Production to Test Box * given variables * Mary K. Mathias 12/10/2015 ********************************************************************************************** * Notes: to be called from Test Box **********************************************************************************************
 H dftactgrp(*no) DatEdit(*YMD) DatFmt(*ISO)
 H debug option(*srcstmt: *nodebugio)
 FJDPACMS O E DISK
 * Variables
 D SQLstmt S 100A
 D dsACMS E DS extname(JDPACMS) qualified
 D dsJDPACMS E DS extname(JDPACMS)
 * *Entry Prototype
 D Main PR extpgm('UTRRMTCPY')
 D 10A
 D 10A
 D 10A
 D 10A
 D 10A
 D 10A
 * *Entry Procedure Interface
 D Main PI
 D inRDBA 10A
 D inUser 10A
 D inPwd 10A
 D inFile 10A
 D inFromLib 10A
 D inToLib 10A
 Exec SQL SET OPTION Naming = *Sys, Commit = *None, UsrPrf = *User, DynUsrPrf = *User, Datfmt = *iso, CloSqlCsr = *EndMod;
 Exec SQL CONNECT TO :inRDBA USER :inUser USING :inPwd;
 SQLstmt = 'Select * From ' + %trim(inFromLib) + '/' + %trim(inFile) ;
 Exec SQL Prepare DynCsrC1 from :SQLstmt;
 Exec SQL Declare CsrC1 Cursor for DynCsrC1;
 Exec SQL Open CsrC1;
 If sqlcod = *zero;
 EndIf;
 DoW %subst(sqlState:1:2) = '00';
 If SQLERRMC = *blanks;
 EndIf;
 If (inFile = 'JDPACMS');
 // Get Remote File
 Exec SQL Fetch Next From CsrC1 into dsACMS;
 // Write output
 dsJDPACMS = dsACMS;
 Write ACMSR;
 EndIf;
 EndDo;
 Exec SQL Close CsrC1;
 Exec SQL CONNECT RESET;
 *InLR = *On;

The SQLCOD and SQLERRMC lines are there only to be able to monitor expression... initially I tried an external ds without qualified, and when that didn't work, change to 2 external ds with 1 qualified...
Thanks, Mary


Software/Hardware used:
V7R1 DB2 on both machines
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question:  

 
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.

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: