help please

KATHY JONES

help please
I am fairly new to DBA work and the senior DBA is off today. I have a developer who is trying to run SPFUI for the first time (new contractor) and he is getting the following error:

PLAN DSNESPRR NOT AUTHORIZED FOR SUBSYSTEM TSN AND AUTH ID NBB163.

We are DB2 V4.1 on S/390. He does have authorization for the tables he is trying to query. Any help would be appreciated.

Kathy Jones
Central Information Services
Clark County School District
Senior Database Analyst
702-799-5040 x366
[login to unmask email]



Don Lorenz

Re: help please
(in response to KATHY JONES)
Kathy,

Have the contractor change his default Spufi settings to CS (from
RR)......that will probably do it.....

Regards,

-----Original Message-----
From: KATHY JONES [mailto:[login to unmask email]
Sent: Thursday, January 04, 2001 12:02 PM
To: [login to unmask email]
Subject: help please


I am fairly new to DBA work and the senior DBA is off today. I have a
developer who is trying to run SPFUI for the first time (new contractor) and
he is getting the following error:

PLAN DSNESPRR NOT AUTHORIZED FOR SUBSYSTEM TSN AND AUTH ID NBB163.

We are DB2 V4.1 on S/390. He does have authorization for the tables he is
trying to query. Any help would be appreciated.

Kathy Jones
Central Information Services
Clark County School District
Senior Database Analyst
702-799-5040 x366
[login to unmask email]



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]

------------------------------------------------------------------------------
This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you.

=====



Jeff Frazier

Re: help please
(in response to Don Lorenz)
Kathy, many shops to not use plan dsnesprr due to it being the plan for
repeatable read access. go into spufi and change option 5 to yes hit enter
and change isolation level to cs and see if that works for you.




KATHY JONES <[login to unmask email]>@RYCI.COM> on 01/04/2001 01:02:07
PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:

Subject: help please


I am fairly new to DBA work and the senior DBA is off today. I have a
developer who is trying to run SPFUI for the first time (new contractor)
and he is getting the following error:

PLAN DSNESPRR NOT AUTHORIZED FOR SUBSYSTEM TSN AND AUTH ID NBB163.

We are DB2 V4.1 on S/390. He does have authorization for the tables he is
trying to query. Any help would be appreciated.

Kathy Jones
Central Information Services
Clark County School District
Senior Database Analyst
702-799-5040 x366
[login to unmask email]








Smike Toppins

Re: help please
(in response to truman.g.brown@VERIZON.COM)
Most likely, the developer needs to change his defaults to use "CS" instead
of "RR"; also the DB2 subsystem needs to be changed to the correct
subsystem. Most places I've been dis-allow most people from running "RR"
SPUFI queries.


SMike Toppins
Great-West Life
[login to unmask email]
(303) 737-5094


> ----------
> From: KATHY JONES[SMTP:[login to unmask email]
> Reply To: DB2 Data Base Discussion List
> Sent: Thursday, January 04, 2001 11:02 AM
> To: [login to unmask email]
> Subject: help please
>
> I am fairly new to DBA work and the senior DBA is off today. I have a
> developer who is trying to run SPFUI for the first time (new contractor)
> and he is getting the following error:
>
> PLAN DSNESPRR NOT AUTHORIZED FOR SUBSYSTEM TSN AND AUTH ID NBB163.
>
> We are DB2 V4.1 on S/390. He does have authorization for the tables he is
> trying to query. Any help would be appreciated.
>
> Kathy Jones
> Central Information Services
> Clark County School District
> Senior Database Analyst
> 702-799-5040 x366
> [login to unmask email]
>
>
>
> http://www.ryci.com/db2-l. The owners of the list can be reached at
> [login to unmask email]
>



[login to unmask email]

Re: help please
(in response to Jeff Frazier)
It appears that Plan DSNESPRR has not be granted execute on DB2 subsystem
TSN for this user ID NBB163.

It order for him to run this you will need to grant execute for this
plan(DSNESPRR) on that DB2(TSN) to user id (NBB163)

John Lendman
Florida Power and LIght



"KATHY JONES"
<[login to unmask email] To: [login to unmask email]
.CCSD.NET> cc:
Sent by: "DB2 Data Subject: help please
Base Discussion
List"
<[login to unmask email]>


01/04/01 01:02 PM
Please respond to
"DB2 Data Base
Discussion List"






I am fairly new to DBA work and the senior DBA is off today. I have a
developer who is trying to run SPFUI for the first time (new contractor)
and he is getting the following error:

PLAN DSNESPRR NOT AUTHORIZED FOR SUBSYSTEM TSN AND AUTH ID NBB163.

We are DB2 V4.1 on S/390. He does have authorization for the tables he is
trying to query. Any help would be appreciated.

Kathy Jones
Central Information Services
Clark County School District
Senior Database Analyst
702-799-5040 x366
[login to unmask email]








Frank D Marchese

Re: help please
(in response to John_Lendman@FPL.COM)
Some shops do not grant the use of that form of the SPUFI plan (repeatable
read) but do grant the use of cursor stability (DSNESPCS). So either
DSNESPRR will never be made available (in which case you go to the SPUFI
defaults panels and change Isolation level to CS) and/or get someone with
GRANT EXECUTE authority on the plan to grant execute to this poor , needy
user.
Maybe you have that auth as DBA.
HTH

-----Original Message-----
From: KATHY JONES [mailto:[login to unmask email]
Sent: Thursday, January 04, 2001 1:02 PM
To: [login to unmask email]
Subject: help please


I am fairly new to DBA work and the senior DBA is off today. I have a
developer who is trying to run SPFUI for the first time (new contractor) and
he is getting the following error:

PLAN DSNESPRR NOT AUTHORIZED FOR SUBSYSTEM TSN AND AUTH ID NBB163.

We are DB2 V4.1 on S/390. He does have authorization for the tables he is
trying to query. Any help would be appreciated.

Kathy Jones
Central Information Services
Clark County School District
Senior Database Analyst
702-799-5040 x366
[login to unmask email]



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]



[login to unmask email]

Re: help please
(in response to Frank D Marchese)
Kathy,

He probably needs to go into the defaults panel and set the subsystem name,
etc.



James Wu

Re: help please
(in response to Smike Toppins)
I guess you have multiple DB2 subsystems under the same MVS LPAR. You can
change the SSID under option D.

James Wu:-)

(847)646-5548
[login to unmask email]



-----Original Message-----
From: KATHY JONES [mailto:[login to unmask email]
Sent: Thursday, January 04, 2001 12:02 PM
To: [login to unmask email]
Subject: help please


I am fairly new to DBA work and the senior DBA is off today. I have a
developer who is trying to run SPFUI for the first time (new contractor) and
he is getting the following error:

PLAN DSNESPRR NOT AUTHORIZED FOR SUBSYSTEM TSN AND AUTH ID NBB163.

We are DB2 V4.1 on S/390. He does have authorization for the tables he is
trying to query. Any help would be appreciated.

Kathy Jones
Central Information Services
Clark County School District
Senior Database Analyst
702-799-5040 x366
[login to unmask email]



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]



Ken Koleto

Re: help please
(in response to James Wu)
Kathy,
Have the developer go into SPUFI Defaults and change it to 'CS' from 'RR".
Many shops secure the repeatable read (DNSEPRR) version from use because of
the potential for tying up resources. The top of the screen should look like
this:

CURRENT SPUFI DEFAULTS SSID: DB2D

===>



Enter the following to control your SPUFI session:

1 ISOLATION LEVEL ===> CS (RR=Repeatable Read, CS=Cursor
Stability)

HTH,
Ken

> -----Original Message-----
> From: KATHY JONES [SMTP:[login to unmask email]
> Sent: Thursday, January 04, 2001 1:02 PM
> To: [login to unmask email]
> Subject: help please
>
> I am fairly new to DBA work and the senior DBA is off today. I have a
> developer who is trying to run SPFUI for the first time (new contractor)
> and he is getting the following error:
>
> PLAN DSNESPRR NOT AUTHORIZED FOR SUBSYSTEM TSN AND AUTH ID NBB163.
>
> We are DB2 V4.1 on S/390. He does have authorization for the tables he is
> trying to query. Any help would be appreciated.
>
> Kathy Jones
> Central Information Services
> Clark County School District
> Senior Database Analyst
> 702-799-5040 x366
> [login to unmask email]
>
>
>
> http://www.ryci.com/db2-l. The owners of the list can be reached at
> [login to unmask email]

Amit Sinha

Re: help please
(in response to Ken Koleto)
GRANT EXECUTE ON PLAN DSNESPRR TO NBB163

HTH

Amit

Tel:414-931-4815




-----Original Message-----
From: KATHY JONES [mailto:[login to unmask email]
Sent: Thursday, January 04, 2001 12:02 PM
To: [login to unmask email]
Subject: help please


I am fairly new to DBA work and the senior DBA is off today. I have a
developer who is trying to run SPFUI for the first time (new contractor) and
he is getting the following error:

PLAN DSNESPRR NOT AUTHORIZED FOR SUBSYSTEM TSN AND AUTH ID NBB163.

We are DB2 V4.1 on S/390. He does have authorization for the tables he is
trying to query. Any help would be appreciated.

Kathy Jones
Central Information Services
Clark County School District
Senior Database Analyst
702-799-5040 x366
[login to unmask email]



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]



KATHY JONES

Re: help please
(in response to Amit Sinha)
thanks to all that helped - he had RR instead of CS and when he changed it to CS it worked!! thanks again

Kathy Jones
Central Information Services
Clark County School District
Senior Database Analyst
702-799-5040 x366
[login to unmask email]



Gerrit Groenendaal

Re: help please
(in response to KATHY JONES)
HI,

In our shop we bind the plan dsnesprr standard with CS, so no problem at all.

Gerrit

KATHY JONES wrote:

> thanks to all that helped - he had RR instead of CS and when he changed it to CS it worked!! thanks again
>
> Kathy Jones
> Central Information Services
> Clark County School District
> Senior Database Analyst
> 702-799-5040 x366
> [login to unmask email]
>
>



elibs

Re: help please
(in response to Gerrit Groenendaal)
Hello,
Since i am new to DD2,could you please kindly enough tell me how to bind dsnesprr?
Thank you in advance
Larry

----- Original Message -----
From: Gerrit Groenendaal <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Saturday, January 06, 2001 3:14 AM
Subject: Re: help please


> HI,
>
> In our shop we bind the plan dsnesprr standard with CS, so no problem at all.
>
> Gerrit
>
> KATHY JONES wrote:
>
> > thanks to all that helped - he had RR instead of CS and when he changed it to CS it worked!! thanks again
> >
> > Kathy Jones
> > Central Information Services
> > Clark County School District
> > Senior Database Analyst
> > 702-799-5040 x366
> > [login to unmask email]
> >
> >
>
>
>

Gerrit Groenendaal

Re: help please
(in response to elibs)
Larry,

Isolation is the parameter it must be CS and not RR, adjust the other parameters to your current settings.

xxxx is the name of the db2 subsystem.
We are on version 6.

Gerrit


//STEP1 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD DUMMY
//SYSTSIN DD *
DSN SYSTEM=(XXXX) TEST(0) RETRY(0)
rebind plan(dsnesprr) owner(sysadm) +
pklist( +
*.dsnesprr.dsnesm68) +
validate(run) isolation(cs) flag(i) dynamicrules(run) +
acquire(use) release(commit) explain(no) +
currentdata(yes) +
qualifier(sysadm) +
cachesize(256) +
degree(1) +
sqlrules(db2) +
disconnect(expliciet)


elibs wrote:

> Hello,
> Since i am new to DD2,could you please kindly enough tell me how to bind dsnesprr?
> Thank you in advance
> Larry
>
> ----- Original Message -----
> From: Gerrit Groenendaal <[login to unmask email]>
> Newsgroups: bit.listserv.db2-l
> To: <[login to unmask email]>
> Sent: Saturday, January 06, 2001 3:14 AM
> Subject: Re: help please
>
> > HI,
> >
> > In our shop we bind the plan dsnesprr standard with CS, so no problem at all.
> >
> > Gerrit
> >
> > KATHY JONES wrote:
> >
> > > thanks to all that helped - he had RR instead of CS and when he changed it to CS it worked!! thanks again
> > >
> > > Kathy Jones
> > > Central Information Services
> > > Clark County School District
> > > Senior Database Analyst
> > > 702-799-5040 x366
> > > [login to unmask email]
> > >
> > >
> >
> >
> >
> N‡!jx Ê‹«²æìr¸©¶*'¢›b¢{(®Ú jw —*.®Ë›±Êâ¦Ø¨žø¬ Ûax0vü¼ n– y«a¶Úÿÿü0Ãúòr/Ü¢oÝooåý8^£ Þ®Ê ¶ ¥ Ë\jvޭ朅çZ´0vü¿Ñ I?Ñ`"ÿ ãÿ

Ruediger Kurtz

Re: help please
(in response to Gerrit Groenendaal)
Hi Larry,

try .....


BIND PLAN(DSNESPRR) OWNER(DBPRSYA1)
PKLIST(
DSNESPRR.DSNESM68
DEHUKDBWA.DSNESPRR.DSNESM68
DEHUKDBTS.DSNESPRR.DSNESM68)
ACTION(REPLACE) RETAIN
VALIDATE(RUN) ISOLATION(CS) FLAG(I) DYNAMICRULES(RUN)
ACQUIRE(USE) RELEASE(COMMIT) EXPLAIN(NO)
CURRENTDATA(YES)
QUALIFIER(DBPRSYA1)
CACHESIZE(1024)
DEGREE(1)
SQLRULES(DB2)
DISCONNECT(EXPLICIT)

Regards Ruediger


elibs schrieb:

> Hello,
> Since i am new to DD2,could you please kindly enough tell me how to bind dsnesprr?
> Thank you in advance
> Larry
>
> ----- Original Message -----
> From: Gerrit Groenendaal <[login to unmask email]>
> Newsgroups: bit.listserv.db2-l
> To: <[login to unmask email]>
> Sent: Saturday, January 06, 2001 3:14 AM
> Subject: Re: help please
>
> > HI,
> >
> > In our shop we bind the plan dsnesprr standard with CS, so no problem at all.
> >
> > Gerrit
> >
> > KATHY JONES wrote:
> >
> > > thanks to all that helped - he had RR instead of CS and when he changed it to CS it worked!! thanks again
> > >
> > > Kathy Jones
> > > Central Information Services
> > > Clark County School District
> > > Senior Database Analyst
> > > 702-799-5040 x366
> > > [login to unmask email]
> > >
> > >
> >
> >
> >
> M===

--
HUK-COBURG
Versicherungen - Bausparen
Abteilung Informatik
Bahnhofsplatz
96450 Coburg
Tel. 09561/96-3914
Fax. 09561/96-3678
mailto:[login to unmask email]



Roger Miller

Re: help please
(in response to Ruediger Kurtz)
One small change - from CURRENTDATA(YES) to CURRENTDATA(NO) can make a big
difference in the performance. One situation I saw reduced the cpu time by
more than 20%. I am a big fan of CURRENTDATA(NO), since you can have almost
the same number of locks and cpu time as ISOLATION(UR), but avoid the
inconsistencies of changes in progress. With just this dynamic SQL, it's
nearly impossible to depend upon CURRENTDATA(YES).

Roger Miller, DB2 for OS/390