Im looking for something similar to a SETLL. When I OPEN the cursor, will the SQLCA give me some specific answer? Or do I have to actually FETCH the cursor?
Thanks,
Phil
Software/Hardware used:
OS V5R4M0
I need to update file A...
But not with SQL?
That is, a SQL UPDATE statement could restrict itself only to rows in A that had a match in B. But it seems that that isn't exactly what you're after. You want to "select" the number of matching rows in B, and, if not zero, then you want to move on to some later code that will eventually result in an update to A after some preparation is done over the values.
If that's reasonably accurate, then a potential solution could be SELECT COUNT(*) FROM FILEB WHERE {fileb=filea}. In there, COUNT(*) would be selected into a host variable that could be tested for zero.
Of course, that's different from SETLL since multiple possible I/Os could take place and you are trying to avoid that... assuming I'm understanding what you want.
Is the matching column indexed?
Tom
I’ve used Tom’s approach and it works well. The following method also works for checking that a record exists without performing the “count”.
[CODE
]D RecFound s 1a inz(‘N’)
/free
reset RecFound;
Exec SQL set :RecFound = (select ‘Y’ from sysibm/sysdummy1
where exists (select zip1 from MyZips where zip1 = 15205));</pre>
Tom -
Slap-me-up-side-of-head! SMACK!
> SELECT COUNT(*)
Why didn’t I think of that.
Thanks
Phil
I was wondering about indexing because I was hoping DB2 would be smart enough to check index-entry counts for an indexed column. IMO, that ought to make any physical I/O be a minimum. Just the page(s) with appropriate index tree elements should ever be hit.
Maybe a SET :var statement:
Tom
I’ve used Tom’s approach and it works well. The following method also works for checking that a record exists without performing the “count”.
D RecFound s 1a inz('N') /free reset RecFound; Exec SQL set :RecFound = (select 'Y' from sysibm/sysdummy1 where exists (select zip1 from MyZips where zip1 = 15205));