MVSFORUMS.com :: View topic - EASYTRIEVE and DB2 in INTERPRET MODE

through

[^/[Print]\]

MVSFORUMS.com -> Application Programming

#1: EASYTRIEVE and DB2 in INTERPRET MODE Author: rrv,

Posted: Wed Sep 12, 2007 5:24 am
—
Hi

Our shop uses CA-EASYTRIEVE PLUS-6.4 extensively in PROD environment for reporting and other utility jobs, limited to Q/VSAM file sets. Recently we installed the PAN/SQL Interface add-on to enable reporting based on DB2.

I'm able to perfectly execute an EASYTRIEVE+SQL program as long as it is compiled and link-edited, using IKJEFT1B. However, when I try to execute the same program under EZTPA00 (see the code below), I receive the following message at the first SQL statement:

*******B204 SQL - INVALID OR MISSING STATIC COMMAND PROGRAM NAME

followed by this at subsequent SQL statements:

*******B256 SQL PROCESSING BYPASSED DUE TO PREVIOUS ERROR

Code:

//EZTRUN JOB XXX,'RAM',MSGCLASS=X,CLASS=A,REGION=0M
//EZTRUN EXEC PGM=EZTPA00
//STEPLIB DD DISP=SHR,DSN=CA.EASYTREV.TST1.V6R4.CAILIB
// DD DISP=SHR,DSN=CA.PANSQL.TST1.V2R4C.CAILIB
//SYSPRINT DD SYSOUT=*
//SYSABEND DD SYSOUT=*
//SYSTERM DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//EZTVFM DD UNIT=SYSDA,SPACE=(CYL,(25,25))
//EZTMAC DD DISP=SHR,DSN=CA.COMMON.TST1.CAIMAC
//SYSIN DD *
W-BANK W 2 P 0
W-BRANCH W 2 P 0
W-ACCOUNT W 7 P 0
*
SQL DECLARE CURSOR1 CURSOR FOR +
SELECT BANK_NUMBER, BRANCH_NUMBER, ACCOUNT_NUMBER +
FROM AIDABPTS.IDA_ACCT_MSTR_IDX +
WHERE PART_ID = 10
*
JOB INPUT NULL
*
SQL OPEN CURSOR1
DISPLAY 'CURSOR OPEN SQL CODE : ' SQLCODE
SQL FETCH CURSOR1 +
INTO :W-BANK +
,:W-BRANCH +
,:W-ACCOUNT
IF SQLCODE EQ 0
DISPLAY 'FETCH SUCCESSFUL '
ELSE
DISPLAY 'FETCH SQL CODE : ' SQLCODE
END-IF
SQL CLOSE CURSOR1
/*
//*

Can someone please let me know what I'm missing in this JCL for the program to execute without errors?

Thanks and Regards
Ram

#2: Author: vivek1983,

Posted: Wed Sep 12, 2007 5:36 am
—
rrv,

I think you are not giving the db2 subsystem.

I have not worked on EZT yet
I found some of the EZT programs using the following at the start of the program:

PARM LINK (PGMNAME(R)) DB2SSID('DB2SUBSYSTEM')

Vivek G

#3: Author: rrv,

Posted: Wed Sep 12, 2007 5:47 am
—
Vivek

True, even I have seen the PARM LINK statement at the beginning of few EASYTRIEVE programs. However, my understanding is that PARM LINK terminates execution after the completion of syntax check and compile operations. It does not EXECUTE the program in interpret mode or otherwise.

Pls correct me if this is NOT the case.

Ram

#4: Author: kolusu, Location: San Jose

Posted: Wed Sep 12, 2007 8:00 am
—
rrv,

You are trying to run a dynamic sql easytrieve program. You need DQSPSCV module to do that. The PAN$SQL DD file is processed only when executing statically. The PAN$SQL file can also be used to indicate that you want to execute under the TSO terminal Monitor Program in background mode. If TSO execution is specified, then the plan name and subsystem ID parameters are ignored.

So you need to concatenate the easytrieve DB2 loadlib dataset to steplib.

Hope this helps...

Cheers

Kolusu

#5: Author: rrv,

Posted: Wed Sep 12, 2007 9:45 am
—
Kolusu

Thanks for the response. The second library in the STEPLIB concatenation does contain DQSPSCV module, as seen from the screen print below.

Code:

//STEPLIB DD DISP=SHR,DSN=CA.EASYTREV.TST1.V6R4.CAILIB
// DD DISP=SHR,DSN=CA.PANSQL.TST1.V2R4C.CAILIB

Code:

Menu Functions Confirm Utilities Help

BROWSE CA.PANSQL.TST1.V2R4C.CAILIB Row 00001 of 00008
Command ===> Scroll ===> CSR
Name Prompt Alias-of Size TTR AC AM RM
_________ DQSPSCC 00012378 003C0F 00 ANY 24
_________ DQSPSCF 0000E798 003E10 00 ANY 24
_________ DQSPSCG 000126A8 004009 00 ANY 24
_________ DQSPSCI 000118E0 004208 00 ANY 24
_________ DQSPSCR 00006F78 002709 00 ANY 24
_________ DQSPSCS 00012E60 004406 00 ANY 24
_________ DQSPSCT 00008A58 002910 00 ANY 24
_________ DQSPSCV 00000008 00310D 00 ANY 24

With exactly the same JCL component, I changed the SYSIN to look like the below.

Code:

//SYSIN DD *
PARM LINK(IDAEZT11) SSID('TSG2') PLAN(IDAEZT1C) BIND(ANY)
*
SQL INCLUDE LOCATION W FROM AIDABPTS.IDA_ACCT_MSTR_IDX
SQL DECLARE MSTCUR CURSOR +
FOR SELECT BANK_NUMBER +
,BRANCH_NUMBER +
,ACCOUNT_NUMBER +
FROM AIDABPTS.IDA_ACCT_MSTR_IDX +
WHERE PART_ID = +010
*
JOB INPUT NULL
*
SQL OPEN MSTCUR
DISPLAY 'OPEN CURSOR SQL CODE : ' SQLCODE
SQL FETCH MSTCUR +
INTO :BANK_NUMBER +
,:BRANCH_NUMBER +
,:ACCOUNT_NUMBER
IF SQLCODE EQ 0
DISPLAY 'FETCH CURSOR SUCCESSFUL ' BANK_NUMBER ' ' BRANCH_NUMBER +
' ' ACCOUNT_NUMBER
ELSE
DISPLAY 'FETCH CURSOR SQL CODE : ' SQLCODE
END-IF
SQL CLOSE MSTCUR
DISPLAY 'CLOSE CURSOR SQL CODE : ' SQLCODE
STOP
/*
//*

This is now giving SQLCODE -302 at the first SQL statement - DECLARE CURSOR.

Code:

*B204 SQL - SQL ERROR, SQLCODE IS -302
*B204 SQL - DSNT408I SQLCODE = -302, ERROR: THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER 001 IS
*B204 SQL - INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE
*B204 SQL - DSNT418I SQLSTATE = 22001 SQLSTATE RETURN CODE

Appreciate any help towards resolving this error.

Thanks and Regards
Ram

#6: Author: kolusu, Location: San Jose

Posted: Wed Sep 12, 2007 10:01 am
—

Quote:

*B204 SQL - SQL ERROR, SQLCODE IS -302
*B204 SQL - DSNT408I SQLCODE = -302, ERROR: THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER 001 IS

rrv,

The error is self explanatory. How did you define your Host variables ? Do they match the DB2 column definitions?

Kolusu

#7: Author: rrv,

Posted: Wed Sep 12, 2007 10:46 am
—
Kolusu

I've used the following statement to include the DCLGEN equivalent for EASYTRIEVE.

SQL INCLUDE LOCATION W FROM AIDABPTS.IDA_ACCT_MSTR_IDX

This enables one to use COLUMN names as host variables without explicitly defining additional fields.

What I'm not sure is that the same program works perfectly OK when I execute under TSO background using IKJEFT1B after compile & link-edit. Then why does it fail with -302 in INTERPRET mode?

#8: Author: singhm,

Posted: Thu Sep 27, 2007 9:01 am
—
Hi,

I am also getting the same error but in my case I am using DB2 v8, in my query I am using UNION ALL, and it is throwing -302 error. But the same query when I am running without UNION ALL, i.e. breaking the same query to two different queries, the job is running perfectly fine... Shocked

...Any ideas?

Thanks..

#9: Author: CICS Guy,

Posted: Thu Sep 27, 2007 10:07 am
—
-302 THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE
What is the "position-number" pointing to?

#10: Author: rrv,

Posted: Tue Oct 09, 2007 1:08 am
—
The position number always refers to the very first column being selected. In my example, it is BANK_NUMBER.

The execution environment is CA-PAN/SQL add on working against DB2 V8 Data Sharing Group.

MVSFORUMS.com -> Application Programming

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1