Dear All,

We are using Db2 v4.1 on OS/390. Currently we are doing Data Archival on few production tables. As part of it, we did runstats and re-bind which changed the accesspath and caused performance degradation to one of the program. I would like to know that, can we skip the rebind after reload & runstats utilities on the table, as we are not changing the source code. In this case whether the Db2 will take old accesspath or will it automatically generates a new accesspath.
Please help me.
Vaddadi

Yes, you can skip the rebind and the old accesspath will be used. But you may want to investigate why the rebind degraded performance in the first place. Look at the EXPLAIN for the plan (check ACCESSTYPE), and see if it is doing a tablespace or index scan. You may need to tweak or add an index. It'd be good if you have the EXPLAIN for the plan before you did the rebind to compare, but the current information may give you an indication of why the program is running slow. We find that we -fix- many performance problems by doing a reorg, runstats and a rebind.

I would consider doing a reorg, then a runstats and rebind. This might help if you deleted a lot of rows in the archive process.

Hi crisscross,

The SORTC_JOIN and ACCESSDEGREE parametes have changed after archival. The Declare Cursor for the application program has started taking more DSNDB07 space for holding the resultant cursor table and failed with the error 'unable to extend 4K'. I had increased the Sort Database size and ran the job.

To prevent for the further occurence of this kind of problems after archival, we are planning not to re-bind as out motive is get the space in production rather than the performance improvement.

As stated earlier, will the existing accesspath get change by any chance after doing the archival and running the runstats without doing the re-bind.

Any help
vaddadi

No, it's the rebind that pulls in the information gathered during the runstats which the optimizer uses to set access paths. Since you're not going to rebind anyway, you may even skip the runstats step, though dynamic SQL (like SPUFI) does use the most current runstats.

You did not say how those two EXPLAIN parameters changed. But I checked the Admin Guide and it says if both SORTN_JOIN and SORTC_JOIN and 'Y', you are doing two sorts. ACCESS_DEGREE is the number of parallel tasks or operations activated by a query. You running out of sort space might be because you are running these sorts in parallel. You'll have to do some more analysis of your EXPLAIN. The Administration Guide can help.

By the way, have you done a REORG of the tables involed in this query? I agree with Marcus, that a REORG, RUNSTATS and REBIND should be done, in that order after many rows are deleted.

The SORTC_JOIN was 'N' before archival and it has changed to 'Y' after archival. SORTN_JOIN has value of 'N' before and after archival, its not changed.

The ACCESS_DEGREE has changed from 6 to 3.

I have loaded with Platinum with RECLUSTER YES and SPACE-DEFN YES.(which has got the same functionality of RE-org) and then ran the runstats followed by Re-bind.

Vaddadi