SYSDBASE contention with dynamic SQL

Ken George

SYSDBASE contention with dynamic SQL
Hi,

We have some new Java programs which are executing dynamic SQL calls via db2-connect to our development subsystem. As development of the project has moved forward, we are seeing more and more 00C9008E abends with the Java issued dyamic SQL listed as the loser in the log display. The function that is listed as the holder of the resource is plan PPAMT99F which is CA-Platinum Plan Analyzer which we use as a back end to our compile process to provide explains for mainframe COBOL based programs.

We have been running the explains with our compile process for years and at the same time, we have many dynamic SQL requests coming in from SPUFI, Online CA-Platinum functions, etc. We haven't had any problems in the past until we started performing the dynamic SQL from Java via DB2-Connect.

If CA-Platinum were using SYSDBASE, we would expect that it would be taking share locks, so why would the dynamic SQL be requesting update or some other non-compatible lock request?

If anyone has any experiences with increased contention on SYSDBASE or an answer to our situation, we would very much appreciate it!

Thanks....
Ken

Ken George
Edward Jones
IS Data Services / DB2 DBA
100 Progress Parkway
(314)-515-8683
Hours: 6:30 - 3:00 (M-F)
[login to unmask email]

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Myron Miller

Re: SYSDBASE contention with dynamic SQL
(in response to Ken George)
I can't answer for sure. But I think a bind takes an exclusive lock on
SYSDATABASE for the database that the plan needs. I know that I cannot bind
plans that have active sql on the tables in the plans.

I suspect as your volume grows, you'll find more and more contention against
SYSDATABASE and find it harder to bind and make database changes during prime
time.

You can know for sure what locks are being taken if you look at the resource
timeouts for the plan PPAMT99F.

I don't know if its possible, but you might just consider changing from the CA
plan PPAMT99F to bind to a straight bind step at the end of the compile
process. I don't know how long your CA-bind step runs, but if it runs for more
than seconds, its too long.

Myron
--- [login to unmask email] wrote:
> Hi,
>
> We have some new Java programs which are executing dynamic SQL calls via
> db2-connect to our development subsystem. As development of the project has
> moved forward, we are seeing more and more 00C9008E abends with the Java
> issued dyamic SQL listed as the loser in the log display. The function that
> is listed as the holder of the resource is plan PPAMT99F which is CA-Platinum
> Plan Analyzer which we use as a back end to our compile process to provide
> explains for mainframe COBOL based programs.
>
> We have been running the explains with our compile process for years and at
> the same time, we have many dynamic SQL requests coming in from SPUFI, Online
> CA-Platinum functions, etc. We haven't had any problems in the past until we
> started performing the dynamic SQL from Java via DB2-Connect.
>
> If CA-Platinum were using SYSDBASE, we would expect that it would be taking
> share locks, so why would the dynamic SQL be requesting update or some other
> non-compatible lock request?
>
> If anyone has any experiences with increased contention on SYSDBASE or an
> answer to our situation, we would very much appreciate it!
>
> Thanks....
> Ken
>
> Ken George
> Edward Jones
> IS Data Services / DB2 DBA
> 100 Progress Parkway
> (314)-515-8683
> Hours: 6:30 - 3:00 (M-F)
> [login to unmask email]
>
>
---------------------------------------------------------------------------------
> 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". If you will be out of the office, send the SET
> DB2-L NO MAIL command to [login to unmask email] 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


__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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