Over the years, we have all been faced with the problem of checking for existence. (not just with DB2 and SQL either – that is for another BLOG!).
Now in the days before even SYSIBM.SYSDUMMY1 existed, it was pretty nasty, and in my old firm we created our own single row dummy table for exactly this requirement (and doing date arithmetic etc.). However the programmers of the world often re-invent the wheel time and time again, a simple existence check has also evolved over the years.
Here is a brief selection of the various ways that people have done it in the past, and could well be still running today, every one to two seconds, somewhere in the world…
Before DB2 V7
Line 188 in the EXPLAIN Output
SELECT COUNT(*)
FROM ZIPCODES
WHERE COL1 = ‘xxx’
If the count(*) was > 0 – BINGO!
(Line 199 WITH UR)
Or
Line 211
SELECT 1
FROM ZIPCODES
WHERE COL1 = ‘xxx’
If the SQLCODE was 0 or -811 – BINGO!
(Line 222 WITH UR)
Or
Line 234
SELECT 1
FROM SYSIBM.SYSDUMMY1
WHERE EXISTS ( SELECT 1 FROM ZIPCODES
A WHERE A.COL1 = ‘xxx’)
If the SQLCODE was 0 – BINGO!
(Line 247 WITH UR)
Or
Line 261
SELECT 1
FROM SYSIBM.SYSDUMMY1 D
WHERE EXISTS ( SELECT 1
FROM ZIPCODES A
WHERE A.COL1 = ‘xxx’
AND D.IBMREQD = D.IBMREQD )
If the SQLCODE was 0 – BINGO!
Note the use of the “correlated” predicate to actually force good access!
(Line 275 WITH UR)
DB2 V7
In DB2 V7 you could then add the FETCH FIRST 1 ROW ONLY. So the scan would stop
Line 290
SELECT 1
FROM ZIPCODES
WHERE COL1 = ‘xxx’
FETCH FIRST 1 ROW ONLY
If the SQLCODE was 0 – BINGO!
(Line 302 WITH UR)
Adding WITH UR “allegedly” also sped up the processing of SYSDUMMY1 and the possibility to declare a cursor with OPTIMIZE FOR 1 ROW and a single FETCH arrived.
DB2 V8
In DB2 V8 you could now drop the correlation check and still get good performance
Same as line 234
SELECT 1
FROM SYSIBM.SYSDUMMY1
WHERE EXISTS ( SELECT 1
FROM ZIPCODES A
WHERE A.COL1 = ‘xxx’ )
If the SQLCODE was 0 – BINGO!
As Terry Purcell wrote on listserv many years ago, “The FETCH FIRST 1 ROW ONLY made all other existence checks obsolete”, and so now is the time to dig through all of your old SQL, and see if you can improve even simple existence checks!
Here is the EXPLAIN output of all of these variations in a DB2 10 NF:
---------+---------+---------+---------+---------+---------+--------- LINE QNO TABLE_NAME A P CE TYPE MS SU ---------+---------+---------+---------+---------+---------+--------- 00188 01 ZIPCODES R S R SELECT 137 388 00199 01 ZIPCODES R S R SELECT 137 388 00211 01 ZIPCODES R S SELECT 187 529 00222 01 ZIPCODES R S SELECT 187 529 00234 01 SYSDUMMY1 R S SELECT 11 29 00234 02 ZIPCODES R S NCOSUB 11 29 00247 01 SYSDUMMY1 R S SELECT 11 29 00247 02 ZIPCODES R S NCOSUB 11 29 00261 01 SYSDUMMY1 R S SELECT 2 6 00261 02 ZIPCODES R S CORSUB 2 6 00275 01 SYSDUMMY1 R S SELECT 2 6 00275 02 ZIPCODES R S CORSUB 2 6 00290 01 ZIPCODES R SELECT 1 1 00302 01 ZIPCODES R SELECT 1 1
Access is always a tablespace scan, sometimes Sequential pre-fetch is active. So you can see that now, today the WITH UR makes no difference, and the absolutely best performance is indeed with the FETCH FIRST 1 ROW ONLY code. Just for fun, I ran the above COBOL program calling each of these methods 10,000,000 times… the CPU usage varied from 10.87 seconds for the worst and 10.63 seconds for the best. That is how good DB2 really is: 0.24 / 10,000,000 is a very very small number indeed!
Sometimes you can teach an old dog new tricks!
Finally here’s a real cutey from about ten years ago, where the task was not to see if a given row existed but if a given TABLE existed.
DECLARE TESTTAB CURSOR FOR
SELECT ‘PS_DOES_TABLE_EXIST’
FROM PS_TAX_BALANCE
WHERE 1 = 0
FOR FETCH ONLY
I hope this has been fixed to actually query the DB2 catalog these days, as in DB2 9, it now gets a PRUNED access path.
---------+---------+---------+---------+---------+---------+--- LINE QNO TABLE_NAME A P CE TYPE MS SU ---------+---------+---------+---------+---------+---------+--- 00319 01 PRUNED 0 0
Which would probably really kill the application!!!
As usual, any questions or comments, then please feel free to mail me!