Will the query (in the attachment) perform “OK” or run like a “dog”?

It will typically execute on a z/OS platform using DB2 v7.

Let’s assume that the two tables contain anywhere between 10 million to 100 million rows! TB1800 is a view. Once the query is actually executed, will the view selection criteria, or the select predicates, be processed first?

(Conceivably there may be “a lot” of these queries each day)

Thanks!

"It depends" on what criteria you apply for "OK" performance.

I cannot see how this query can run without a tablespace scan. Personally, I think that tablespace scans of 10 million+ row tables do not give "OK" performance when the query is run a "lot" of times a day. But YMMV.

This is SQL tuning 101. How would you like the query to be executed? Put yourself in DB2's shoes - if you were DB2 how would you go about executing the query? Run an EXPLAIN on the query. How can you make DB2 do what you want it to? See your local DBA you should be able to point out what s/he needs to do to make it an acceptable performer.

James Campbell

To be honest, I looked at your document, but I don't have time right now to figure out your question.

But I did notice that your tablespaces and indexspaces have 48% free space (PCTFREE 48). This could not possibly be even close to the optimum value. The default is 10% freespace (on each page) and for data warehouse databases it could even be less than that.

Also, for a tablespace with very large tables like you have, you need a much large SEGSIZE.

I realize that this may be a development environment, but these 2 parms should be the same for development and production if you want your Explain to give accurate information about the access path or optimizing your SQL.

If your table really has millions of rows then your primary and secondary space allocations are way too small.
You used the defaults: priqty=48 (this is 48K, size of a 3390 track) and secqty=12 (12K) for all your tablespaces and indexes.
The result will be hundreds of VSAM extents till you run out of extents.
I would suggest you change it to a multiple of 720 (720K is the size of a 3390 cylinder), both the priqty and the secqty. Something like priqty=72000 (100 cylinders, 70MB) and secqty=7200 is probably enough for the tablespace. The indexes need less space, let's say priqty 7200 and secqty 7200.

Thanks everyone for all your replies. I am looking into this further. The values in the ddl are only "starter" values & would be amended at installation time depending on data volumes within different environments. The question posed was more from a generic performance perspective. Thanks again!