DSNE106E PLAN DSNESPCS NOT AUTHORIZED FOR SUBSYSTEM

Hello My Blog readers....

Here is another classic problem and it's solution for you...

What to do when many people are getting the error message like "DSNE106E PLAN DSNESPCS NOT AUTHORIZED FOR SUBSYSTEM DSNA AND AUTH ID TSK10"

Here you go..

Spufi uses 2 plans for execution of SQL statements on demand.
One is DSNESPRR and other one is DSNESPCS.
DSNESPRR plan uses Repeatable read where as DSNESPCS uses Cursor stability isolation level.

DSNESPRR plan is the default for Spufi.

RR isolation is highest in restriction level and provides least concurrency. This may a disaster in production environment if someone accesses the table thru DSNESPRR in spufi and went for a break without coming out from the output panel. This may lock the Table.

Solution:
1) Never allow people to use RR isolation level in Spufi. (this is the Best Practice)
2) Rebind DSNESPRR with an isolation level of CS, thereby eliminating the chance that people will use spufi with RR
3) FREE DSNESPRR plan so that nobody will access this plan.


Note: To Resolve the authorization problem mentioned at the begining of the blog.

Always grant EXECUTE on DSNESPCS to PUBLIC.




Cheers..
Prakash C. Singh
IBM Certified DB2 DBA