Steve,
"sysdummy" may not be on everyone's machine. At least two of ours don't
have it but they all have QSYS2/QSQPTABL.
Given that you are supporting some really old OS versions and that SQL
performance pretty much stunk prior to V5R1 (especially for joins), I
think I'd steer clear of using SQL to check if a record exists in this
case.
SQL is usually slower than the traditional I/O methods when selecting
small numbers of records (the big exception I've seen is when working
with remote data -- SQL is almost always faster than DDM files). The gap
is getting smaller all the time and you'll have to run some benchmarks
to see if it makes a difference for you. There should be quite a few
entries in the archives about this topic (SQL performance benchmarks).
Matt
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[
mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Walden H. Leverich
Sent: Wednesday, May 02, 2007 3:11 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Record seach options - millions of records
Note that if you are using EXISTS, the fields in the select clause
don't matter.
True. However, DB2/400 doesn't allow a select w/out the from clause, so
you can't just check for exists(), you need to select from somewhere
where exists()... So now you've introduced another table.
Ideally you should be able to do:
Select 1 where exists(select * from xxx where ...)
There are many times you're just checking for existence. However, we
need to do a:
Select 1 from sysdummy where exists(selct * from xxx where...)
And now that's just getting ugly.
-Walden
midrange.com
