Identifying the first occurence of a DB2 Querry 1

[8101]

Dear ALL,

We want DB2 to stop searching the table once the first occurence of the predicate is satisfied.(i.e. Once the condition is met, DB2 should stop further processing the querry) Is this possible in DB2?

- We are accessing a DB2 table with millions of records, so optimized querry is what we all looking at.
- We are not looking for a cursor option.

Thanks & Regards,
Vinod.

 

[gregsimpson]

Vinod,

It's not entirely clear from your post whether you you are doing an existence check or do indeed want the first row matching your predicated search returned.

If you wish to actually have only the first row returned to your program then DB2 does have the ability to do this. Unfortunately you were after an example which avoided a CURSOR and this example does use a CURSOR.

Something along the lines of

SELECT .....
FROM EMPLOYEE
WHERE LOCATION = 'ENGLAND'
FETCH FIRST 1 ROW ONLY

will do what you require. However it is essential that if you do use this extra predicate you explain your query to see how it is geeting to the result. It is also probably making sure you have the latest fix packs as a couple of APAR's ware associated with this predicate when the optomizer incorrectly calculated the access path. ALthough it will always appear to you that it is only returning 1 row if a bad access path is chosen you may still have the overhead of the full table being read and sorts etc being performed. I have had great performance results with FETCH FIRST xx ROWS ONLY. Especially when my enquiry has been able to use the Clustering Index.

If it is an existence check your after then something like the following will work very well for you and obviously won't need a CURSOR

SELECT 1 INTO :hvsmallint
FROM SYSIBM.SYSDUMMY1
WHERE EXISTS
(SELECT 1 FROM EMPTABLE WHERE EMPLOYEE = :HVEMPLOYEE)

The following link explains how this works and why it works so well.

http://www.db2mag.com/db_area/archives/2001/q4/programmers.shtml

Hope 1 of these is of use to you.


Cheers
Greg

 

[gregsimpson]

Vinod,

I have just read the article again, maybe I should have read it 5 minutes ago before I replied to you. If it's the existence check your after it looks like

SELECT 1
FROM SYSIBM.SYSDUMMY1 A
WHERE EXISTS (
SELECT 1 FROM BIGTABLE B
WHERE COLUMN_A = 1
AND B.IBMREQD = A.IBMREQD)

is even more efficient nowadays.

Cheers
Greg

 

[8101]

Thanks Greg.

The information was very useful. The performance of the job has improved. However still it's taking few hours.

Let me explain what am specifically looking for....

We have 2 data bases containing around 7 million records.
We are suppossed to process around 25 thousand of records in a flat file against these tables. Each record in the file hits the 7 million records for existence check in different places of the loigc which is required

I am not sure whether DB2 will come out of search once it hits the first occurrence. (we have used the correlated sub query suggested in your mail).

Some times the job takes few minutes and some times it take few hours. We could not understand the behaviour of the execution time (i.e., CPU Time)

Our tables don't have any indexes. We have planned to request the DBA to create the indexes for us.

Is any other performance tuning we can do to increase the efficiency of the JOB? like re-org....

Thank you very much.

Vinodh

 

[gregsimpson]

Vinodh,

it is imperative that you have indexes created to support the queries you are running against. As you don't have indexes DB2 simply starts at the beginning of the table and carries on searching until it satisfies your existence check. Doing what we have specified above ensures that once the existence check is satisfied, it doesn't search for another row.

Once you have indexes set up to support your queries you batch job will run a lot faster. If the index set up happens to contain everything in your existence check, then even better, as the optomizer will choose an index only access, which has many performance benefits.

As regards processing the flat file. It may well be of benefit when you have the indexes created to have the flat file pre sorted into the same order. The fact that DB2 isn't able to make use of an index in this processing and effectively performs full table scans is the reason for your greatly fluctuating CPU time.

Cheers
Greg