DB2 - Plan Error

Jerry Ragland

DB2 - Plan Error
Hi all,

I am new to CICS-DB2 programming. I get the following error when I try to
give grant to a plan.

The JCL and the error message is pasted below -

####JCL#####

//SYSTSIN DD *
DSN SYSTEM(DSN1)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP71) PARMS('/ALIGN(LHS) MIXED') -
LIB('DSN710.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
GRANT BIND,EXECUTE ON PLAN DB2PLN01 TO PUBLIC ;

####ERROR####

PAGE 1
***INPUT STATEMENT:
GRANT BIND,EXECUTE ON PLAN DB2PLN01 TO PUBLIC ;
SQLERROR ON GRANT COMMAND, EXECUTE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -204, ERROR: DB2PLN01 IS AN UNDEFINED NAME
DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXA07 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -180 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION

It seems the plan name is unavailable. What should be done to make it available.

It will be more helpfull if someone explains me what the above JCL tries to do.

Thanks in advance.

-Jerry.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Isaac Yassin

Re: DB2 - Plan Error
(in response to Jerry Ragland)
Hi Jerry,

You need to bind it first.


Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Jerry Ragland
Sent: Monday, December 26, 2005 4:30 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 - Plan Error

Hi all,

I am new to CICS-DB2 programming. I get the following error when I try to give grant to a plan.

The JCL and the error message is pasted below -

####JCL#####

//SYSTSIN DD *
DSN SYSTEM(DSN1)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP71) PARMS('/ALIGN(LHS) MIXED') -
LIB('DSN710.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
GRANT BIND,EXECUTE ON PLAN DB2PLN01 TO PUBLIC ;

####ERROR####

PAGE 1
***INPUT STATEMENT:
GRANT BIND,EXECUTE ON PLAN DB2PLN01 TO PUBLIC ;
SQLERROR ON GRANT COMMAND, EXECUTE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -204, ERROR: DB2PLN01 IS AN UNDEFINED NAME
DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXA07 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -180 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION

It seems the plan name is unavailable. What should be done to make it available.

It will be more helpfull if someone explains me what the above JCL tries to do.

Thanks in advance.

-Jerry.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be
reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Manas (Cognizant) Biswal

Re: DB2 - Plan Error
(in response to Isaac Yassin)

DSNTEP2 is a batch SQL processor. It just runs whatever SQL is present in the SYSIN card. Check that your plan exists on the SYSIBM.SYSPLAN table.

Thanks,
Manas

"It is not the strongest of the species that survives, nor the most intelligent; it is the one that is most adaptable to change". - Charles Darwin


________________________________

From: DB2 Data Base Discussion List on behalf of Jerry Ragland
Sent: Mon 12/26/2005 9:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 - Plan Error



Hi all,

I am new to CICS-DB2 programming. I get the following error when I try to
give grant to a plan.

The JCL and the error message is pasted below -

####JCL#####

//SYSTSIN DD *
DSN SYSTEM(DSN1)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP71) PARMS('/ALIGN(LHS) MIXED') -
LIB('DSN710.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
GRANT BIND,EXECUTE ON PLAN DB2PLN01 TO PUBLIC ;

####ERROR####

PAGE 1
***INPUT STATEMENT:
GRANT BIND,EXECUTE ON PLAN DB2PLN01 TO PUBLIC ;
SQLERROR ON GRANT COMMAND, EXECUTE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -204, ERROR: DB2PLN01 IS AN UNDEFINED NAME
DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXA07 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -180 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION

It seems the plan name is unavailable. What should be done to make it available.

It will be more helpfull if someone explains me what the above JCL tries to do.

Thanks in advance.

-Jerry.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm




This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly
prohibited and may be unlawful.

Visit us at http://www.cognizant.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark McCormack

DB2 - Plan Error
(in response to Manas (Cognizant) Biswal)
< //SYSTSIN DD *
< DSN SYSTEM(DSN1)
< RUN PROGRAM(DSNTEP2) PLAN(DSNTEP71) PARMS('/ALIGN(LHS) MIXED') -
< LIB('DSN710.RUNLIB.LOAD')
< //SYSPRINT DD SYSOUT=*
< //SYSUDUMP DD SYSOUT=*
< //SYSIN DD *
< GRANT BIND,EXECUTE ON PLAN DB2PLN01 TO PUBLIC ;
<
< It will be more helpfull if someone explains me what the above JCL tries
to do.

Jerry,

This is a portion of JCL to get to DB2 via TSO connect.
DSNTEP2 is a program which can execute dynamic SQL stmts.
1. The EXEC stmt specifies pgm=IKJEFT01, IKJEFT1A, or IKJEFT1B, three entry
points in the same batch TSO program.
2. That program gets its input from SYSTSIN and writes to SYSTSPRT. In
this case:
a. connect to DB2 subsystem DSN1 via the DSN command processor
b. then execute program DSNTEP2 using plan DSNTEP71
(the default plan name for DSNTEP2 under DB2v7)
DSNTEP2 gets its input from SYSIN and writes to SYSPRINT
c. find that program in library DSN710.RUNLIB.LOAD
d. if the SQL is a SELECT stmt, align the results
to the left of the 133 byte SYSPRINT line.

I hpoe this helps.
Mark

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Troy Coleman

Re: DB2 - Plan Error
(in response to Mark McCormack)
Line 1: The SYSTSIN DD is the input for TSO commands. You can issue these
same command online but since you are running them in batch the IKJEFT1B TSO
processor will process the commands.
Line 2: DSN command - connect to DB2 in your case subsystem is DSN1
Line 3: RUN command - Run program DSNTEP2 using PLAN DSNTEP71 out of library
DSN710.RUNLIB.LOAD passing some format parms.
This plan is usually installed using hlq.SDSNSAMP:
DSN710.NEW.SDSNSAMP(DSNTEJ1L)
Line 4: Load library you install DSNTEP2 in.
Line 5, 6 - output
Line 7: SYSIN DD - The DDL you want to execute in batch. In this case the
GRANT BIND, and EXECUTE on plan DB2PLN01 to user PUBLIC. In order for this
GRANT to work you must first create the plan by using the BIND command. See
the DSNTEJ1L JCL for an example of the BIND along with the grant.

1 < //SYSTSIN DD *
2 < DSN SYSTEM(DSN1)
3 < RUN PROGRAM(DSNTEP2) PLAN(DSNTEP71) PARMS('/ALIGN(LHS) MIXED') -
4 < LIB('DSN710.RUNLIB.LOAD')
5 < //SYSPRINT DD SYSOUT=*
6 < //SYSUDUMP DD SYSOUT=*
7 < //SYSIN DD *
8 < GRANT BIND,EXECUTE ON PLAN DB2PLN01 TO PUBLIC ; < < It will be more
helpfull if someone explains me what the above JCL tries to do.



Troy Coleman, Support Engineer
IBM Certified Solutions Expert

SoftBase Systems, Inc.
847-776-0618
828-670-9900 ext. 334
[login to unmask email]

Compliance Challenged with Test Data Privacy? White Papers and More at
http://www.softbase.com/

The information contained in this message may be CONFIDENTIAL and is for the
intended addressee only. Any unauthorized use, dissemination of the
information, or copying of this message is prohibited. If you are not the
intended addressee, please notify the sender immediately and delete this
message.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Mark McCormack
Sent: Tuesday, December 27, 2005 11:23 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 - Plan Error

< //SYSTSIN DD *
< DSN SYSTEM(DSN1)
< RUN PROGRAM(DSNTEP2) PLAN(DSNTEP71) PARMS('/ALIGN(LHS) MIXED') -
< LIB('DSN710.RUNLIB.LOAD')
< //SYSPRINT DD SYSOUT=*
< //SYSUDUMP DD SYSOUT=*
< //SYSIN DD *
< GRANT BIND,EXECUTE ON PLAN DB2PLN01 TO PUBLIC ; < < It will be more
helpfull if someone explains me what the above JCL tries to do.

Jerry,

This is a portion of JCL to get to DB2 via TSO connect.
DSNTEP2 is a program which can execute dynamic SQL stmts.
1. The EXEC stmt specifies pgm=IKJEFT01, IKJEFT1A, or IKJEFT1B, three entry
points in the same batch TSO program.
2. That program gets its input from SYSTSIN and writes to SYSTSPRT. In this
case:
a. connect to DB2 subsystem DSN1 via the DSN command processor
b. then execute program DSNTEP2 using plan DSNTEP71
(the default plan name for DSNTEP2 under DB2v7)
DSNTEP2 gets its input from SYSIN and writes to SYSPRINT
c. find that program in library DSN710.RUNLIB.LOAD
d. if the SQL is a SELECT stmt, align the results
to the left of the 133 byte SYSPRINT line.

I hpoe this helps.
Mark

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm