First of all we are using IBM DB2 7.1 on a MVS Mainframe.
I am an application developer with a group of DBA's supporting us. They do code reviews and normally tell us we are doing a 4 table (or 6 table) join , exceeds their cost threshhold, and need to break it down to improve performance (most of these joins run in less than 1 CPU minute). They also tell us efficient code will make the system perform better. I have beem writing DB2 code for ove 10 years, know all about perforance, looking at DB2 plan tables and such and have always read and understand, it is better to get all the data you can at one time, instead of seperate reads. I also tell them that the way to improve performance is to do runstats (in produciton this is fine, it is required before they reorg, in development regions --no) and to do rebinds of the produciton plans. They say no. I am looking for soft copies of documentation that specifically address performace issues on a IBM MVS mainframe DB2 that address table joins and other performance issues (like rebinding and rebinding frequencies) so I can quit knocking my head against a wall. I would ask a second question about when and if other companies rebind development regions, but this is not my primary question for today. Thanks in advance.

There are lots of different opinions on these subjects, so I don't think you will find a definitive answer. Just because advice is published, does not make it correct. I see lots of published advice that I personally think is incorrect. Even if you get advice from an IBM person who works at the DB2 development lab, remember that almost every one of them is a product developer (who writes very low level code) and almost none of them are DBA’s, nor have they ever been DBA's.

The issue of how many joins to do at one time depends somewhat on what platform your client resides on (assuming DB2 resides on z/OS). If your client resides on z/OS (CICS, TSO Batch, etc) then making two calls to DB2 each with 4 joins may be equal or better in performance than one call to DB2 with 8 joins. That is because calling DB2 from another mainframe process is just a call to another address space on the same machine. Another factor is that sometimes (but not always) an 8-way join is more expensive than two 4-way joins. Actual testing can determine where this might occur.

However, if your client is remote (not on the DB2 z/OS) server, then you need to factor in the extra communication cost of the client talking to DB2 more than one time. So in this case, you would want to minimize the number of calls to DB2, or write a stored procedure to do the same.

Having too many joins is also an issue of the database design, and not just the design of applicaiton program in how it accesses DB2. I see lots of cases of over-normalized database designs that are the root cause of the problem.

Regarding statistics and rebinds: My personal opinion is that once a database has grown to its normal size, and is not likely to significantly increase in size, and all the static plans are bound and optimized, then there is no need to keep doing rebinds after every reorg. Presumably, the plan would be the same after each reorg, runstats, and rebind (unless the data has significantly changed). Since mainframe users (unlike open system users) are usually charged by the CPU second for every job they run (because the mainframe is shared by many users) then there is some real incentive to not run unnecessary utility jobs.

Data I should have added was: It is a vendor database/system. We support all backend operations and custom code to new batch processes. Additionally, we add indexes as appropriate, but can not modify structure or tables themselves. We have implemented a vendor upgrade that included new tables and new indexes. Most vendor code was moved into production when upgrade was completed without time to size accordingly. Additionally, I am not asking about rebinding on a frequent basis, but occasionally like, semi-annual and such.

Certainly, you would want to rebind a plan or package any time you make changes (for example adding indexes) that might improve the performance of the SQL. I don't think that hard rules are necessary in these cases, just use common sense.

If you drop an index (or other object) that is used in a plan or package, DB2 will do an automatic rebind the first time the plan or package is invoked by a program that executes. But in these cases it usually it is a good idea to rebind explicitly during the change window to ensure that there are no bind problems.