Q and A

Asked and Answered

I work in a small operation where we've had DB2 for about 18 years — and we've never changed any of our options. We don't have a full-time DBA. I'm the backup DBA, but no one does much with tuning our system. The DBA is a systems analyst (as am I). We use one buffer pool, but I've been looking into changing this.

We've been experiencing some problems where Nomad programs (this is a report writing tool) and a CICS online program are butting heads. They only go up against each other on one table. We're not clear on why this is happening, but feel we need to do some tuning. The other DBA and I are primarily doing application work — so it's really hard to stay current on what to do with our DB2 system. Do you have any recommendations on how to approach this subject? I've been wondering about bringing in an expert for a week or two to analyze what we're doing and whether there is something we should be doing differently. We have no programs bound with CURRENTDATA(NO) option.

Robert Catterall responds:

Having a DB2 expert in for a week or two could be a good investment. Not only would you likely get a good list of actionable recommendations, you'd also probably learn a lot just by looking over the DB2 expert's shoulder.

When two DB2-accessing programs "butt heads," it's usually due to lock contention. Presumably, the Nomad programs are accessing the table in question in read-only mode, while the CICS program is changing data in the table (via INSERT, UPDATE, or DELETE). It would help me to know which program failsıthe Nomad or the CICS program? Or, does it vary?

CURRENTDATA(NO) might or might not help. I'd check the isolation level of the programs in question. They should both probably be bound with ISOLATION(CS), also known as cursor stability. If the Nomad programs are bound with ISOLATION(RR), or repeatable read, that could be a source of lock contention. Check both plan and package bind options.

Also, make sure that the CICS program is not issuing the SQL statement LOCK TABLE. It might help to check this out on the Nomad side, too.

See a complete archive of reader/author Q&As .