views:

347

answers:

1

I have a REXX job that needs to read from both Teradata (using BTEQ) and DB2. At present, I can get it to either read from Teradata or DB2, but not both. When I try to read from both, the Teradata one (which runs first) works fine but the DB2 read gives an error of RC(1) upon attempting to open the cursor.

Code to read from Teradata (by and large copied from http://www.teradataforum.com/teradata/20040928_131203.htm):

ADDRESS TSO "DELETE BLAH.TEMP"
"ALLOC FI(SYSPRINT) DA(BLAH.TEMP) NEW CATALOG SP(10 10) TR RELEASE",
   "UNIT(SYSDA) RECFM(F B A) LRECL(133) BLKSIZE(0) REUSE"
"ATTRIB FBATTR LRECL(220)"
"ALLOC F(SYSIN) UNIT(VIO) TRACKS SPACE(10,10) USING(FBATTR)"
/* Set up BTEQ script */
QUEUE ".RUN FILE=LOGON"
QUEUE "SELECT COLUMN1 FROM TABLE1;"
/* Run BTEQ script */
"EXECIO * DISKW SYSIN (FINIS"
"CALL 'SYS3.TDP.APPLOAD(BTQMAIN)'"; bteq_rc = rc
"FREE FI(SYSPRINT SYSIN)"
/* Read and parse BTEQ output */
"EXECIO * DISKR SYSPRINT (STEM BTEQOUT. FINIS"
DO I = 1 to BTEQOUT.0
   ...
END

Code to read from DB2:

ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN rcDB2 = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT " subsys

sqlQuery = "SELECT COLUMN2 FROM TABLE2;"

ADDRESS DSNREXX "EXECSQL DECLARE C001 CURSOR FOR S001"
IF SQLCODE <> 0 THEN DO
   SAY 'DECLARE C001 SQLCODE = ' SQLCODE
   EXIT 12
END
ADDRESS DSNREXX "EXECSQL PREPARE S001 FROM :sqlQuery"
IF SQLCODE <> 0 THEN DO
   SAY 'PREPARE S001 SQLCODE = ' SQLCODE SQLERROR
      EXIT 12
END
ADDRESS DSNREXX "EXECSQL OPEN C001"
IF SQLCODE <> 0 THEN DO
   SAY 'OPEN C001 SQLCODE = ' SQLCODE
   EXIT 12
END

ADDRESS DSNREXX "EXECSQL FETCH C001 INTO :col2"
IF SQLCODE <> 0 THEN DO
   SAY 'FETCH C001 SQLCODE = ' SQLCODE
   EXIT 12
END

I suspect that this has something to do with my use of SYSPRINT and SYSIN. Anyone know how I can get this to work?

Thanks.

Edit

The question as stated was actually wrong. Apologies for not correcting this earlier.

What I had really done was have this:

ADDRESS TSO "SUBCOM DSNREXX" 
IF RC THEN rcDB2 = RXSUBCOM('ADD','DSNREXX','DSNREXX') 
ADDRESS DSNREXX "CONNECT " subsys

...followed by a small read from DB2, then followed by the code to read from Teradata, followed by more code to read from DB2. When this was changed to reading from Teradata first before having anything to do with DB2 at all, it worked.

+1  A: 

I don't think this has anything to do with SYSPRINT or SYSIN.

I think you are getting TSO RC = 1, not SQLCODE = 1 (because there is no SQLCODE of 1.

1 is a warning, -1 is an error. You can look this up in the DB2 Application Programming and SQL Guide

Turn on TRACE R and run it.

There are variables (shown below) that display info about the error/warning.

22 *-* ADDRESS DSNREXX "EXECSQL OPEN C1"                                  
   >>>   "EXECSQL OPEN C1"                                                
   +++ RC(1) +++                                                          
23 *-* IF SQLCODE <> 0                                                    
28 *-* SAY 'SQLSTATE='sqlstate', SQLERRMC='sqlerrmc', SQLERRP='sqlerrp

SQLSTATE=00000, SQLERRMC=, SQLERRP=DSN
29 - SAY 'SQLERRD='sqlerrd.1', 'sqlerrd.2', 'sqlerrd.3', 'sqlerrd.4',', sqlerrd.5', 'sqlerrd.6
SQLERRD=0, 0, 0, -1, 0, 0
32 - SAY 'SQLWARN='sqlwarn.0', 'sqlwarn.1', 'sqlwarn.2', 'sqlwarn.3',', sqlwarn.4', 'sqlwarn.5', 'sqlwarn.6', 'sqlwarn.7',', sqlwarn.8', 'sqlwarn.9', 'sqlwarn.10
SQLWARN= , N, , , , 2, , , , ,

For example, it could be that when both are used together, there is not enough memory.

Bob Stark
Thanks for your answer. I actually had a fix for this (see edit for explanation) a while ago, but forgot to post it. However, I still have next to no idea why the old implementation didn't work. I'll mark this as the correct answer if you can help clarify.
lins314159
I think you have to dump those SQLWARN.1 SQLWARN.2 etc. fields if you get a warning, figure out which one is filled in, and work that error by digging through the manual. It probably has something to do with the specific query, and when you reworked the program, the query was slightly different, you used a different cursor, or something.
Bob Stark