MVSFORUMS.com

binduvnair · Beginner Joined: 07 Jan 2003 Posts: 16 Topics: 8

Hi ,

When i tried to execute a drop view and create view statement through JCL it throwed the following error?
DSN
RUN PROGRAM(xxxxx) PLAN(xxxxxx)
DSN ENDED DUE TO ERROR+
.SYSTEM ABEND CODE 0C4 REASON CODE 00000010 PSW 070C10008C51696A ASIDS: HOME
Drop view returned SQL CODE =0
But as I did not give a condition code check in the subsequent steps it went through those. Interestingly the next step was to unload records from the table using the previous view.

If we are not using the view and instead give the select statement directly it is working fine without this 0C4 error.

I am curious to know why this is happening.. Could you please help me out?

kolusu · Posted: Fri Dec 03, 2004 9:14 am Post subject:

binduvnair,

can you post the complete error messages and your JCL

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

binduvnair · Beginner Joined: 07 Jan 2003 Posts: 16 Topics: 8

Hi Kolusu,
This is what it is..
//STP0010 EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=<PLI Library of my shop>,DISP=SHR
// DD DSN=<DB2 run lib>,Disp=shr
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD*
DSN SYSTEM(DB2P)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP71)
//SYSIN DD *
DROP VIEW schema.viewname;
CREATE VIEW schema.viewname AS
SELECT *
FROM schema.tb1
WHERE
col1 =
( SELECT col2 FROM schema.tbl2);

The error given was
.READY
. DSN SYSTEM(DB2P)
.DSN
. RUN PROGRAM(DSNTEP2) PLAN(DSNTEP71)
.DSN ENDED DUE TO ERROR+
.SYSTEM ABEND CODE 0C4 REASON CODE 00000010 PSW 070C10008C51696A ASIDS: HOME
.***INPUT STATEMENT:
. DROP VIEW schema.viewname;
. RESULT OF SQL STATEMENT:
. DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
.
CREATE VIEW schema.viewname AS
SELECT *
FROM schema.tb1
WHERE
col1 =
( SELECT col2 FROM schema.tbl2
Thanks and Regards,
Bindu

kolusu · Posted: Fri Dec 03, 2004 10:06 am Post subject:

Bindu,

I just noticed that your plan name is wrong in the your SYSTSIN cards. You are running the program DSNTEP2 and the plan name is also the same, but you have the plan name as DSNTEP71. So change your control cards to the following

Bithead · Posted: Fri Dec 03, 2004 1:19 pm Post subject:

The difference in the plan may be version specific.

You may need to run the DROP in one step and the CREATE in another as there is no commit point.

binduvnair · Beginner Joined: 07 Jan 2003 Posts: 16 Topics: 8

Hi,
Thanks for the replies. The funniest part i have in this is that this have been running in production fine for the last few months. Only this month it abended. If it was the problem with the plan, it would have abended in the earlier runs also. That's what i am confused about .. Sad

(