Singleton Select efficiency does not appear to be a problem as the existence-checking singleton SELECT is averaging only 0.17 milliseconds (ms) of in-DB2 CPU time and only 0.25 ms of in-DB2 elapsed time. Are there any disadvantages associated with using a singleton SELECT for a one-or-more-rows existence check? No big ones, that
I can see. Yes, it requires extra program logic to check the SQL return
code, but the additional logic is pretty simple. Given that the -811s are
captured by DB2(I suppose through trace records), they may add a little
bit to processing overhead, but probably not much. Performance-wise, it
could be that the singleton SELECT is a little bit more efficient than
OPEN/FETCH/CLOSE would be.
I'm more of a database person than an application developer, but if I had
been faced with the existence check requirement I might have opted for a
WHERE EXISTS subquery, such as this one:
SELECT 'X'
INTO :WORK-FIELDS.WS-EXISTS
FROM ANY_SMALL_TABLE
WHERE EXISTS
(SELECT * FROM UDAT_USR_DATA_ACCS
WHERE UID = :DCLUDAT-USR-DATA-ACCS.UDA-UID);
This approach works well because the statement executes without getting a negative SQL return code regardless of whether the number of rows for a user is zero, one, or more than one. Is it more efficient than the singleton SELECT now in use? Hard to say without actually testing the
two statements. Given that the statement in use now is quite efficient, there may not be much of a difference performance-wise. In that case, use of a statement such as the one above in place of the one in use would be
based on a desire to perform the existence check without generating a lot of -811s, and I'm not sure that satisfying this desire would justify changing thecode of a program that appears to be working fine as is.