COALESCE Efficiency?

[RICHINMINN]

On a mainframe z/OS, DB2 v8 environment, I'm using the COALESCE function to retrieve a value from any of four to six different database locations (table/column).

"The COALESCE function returns the first argument that is not null... The arguments are evaluated in the order in which they are specified, and the result of the function is the first argument that is not null." -- from IBM DB2 v6 SQL REFERENCE.

Here's a snippet of the SQL (showing only 2 locations) looking for address information from 2 different database locations:
SELECT COALESCE(ADDS1.ADDR_LN_1_TX,ADDS2.ADDR_LN_1_TX) ,
COALESCE(ADDS1.ADDR_LN_2_TX,ADDS2.ADDR_LN_2_TX) ,
COALESCE(ADDS1.CITY_NM,ADDS2.CITY_NM) ,
COALESCE(ADDS1.ST_CD,ADDS2.ST_CD) ,
COALESCE(ADDS1.PSTL_CD,ADDS2.PSTL_CD)
FROM ODS.BIL_PROV_INFO BIPRIN
LEFT OUTER JOIN ODS.ADDRESS ADDS1
ON ADDS1.ADDR_ID_NO = BIPRIN.BIL_PROV_ADDR_ID
LEFT OUTER JOIN ODS.ADDRESS ADDS1
ON ADDS2.ADDR_ID_NO = BIPRIN.PAYTO_PROV_ADDR_ID
WHERE etc...

My question is:
If I specify 2 (or 4, 6, or more) arguments in a COALESCE function, does DB2
1) retrieve all 2 (or 4, 6 or more) values from the database, then analyze each value in sequence to determine if it is null or not, and use the first one that is not null, or
2) retrieve just the first value from the database, and analyze it to determine if it is null or not, and if it isn't, stop, otherwise retrieve the second value from the database, and analyze it, and so on through the remaining arguments?

It is such a useful function that I can't believe that IBM wouldn't have optimized it to retrieve only one value at a time from the database, and go only as far as it needs to, stopping at the first non-null value.

Does anyone know?

Rich (in Minn.)

 

[blom0344]

To be honest I do not know the exact algorithm used. However, I can imagine fetching step by step is not the way data is processed internally. That would mean a lot of potential fetching (=overhead). In memory computing should be so much faster than additional 'fetching' , that my guess would be on scenario 1.

Hopefully this will develop into an interesting discussion!

Ties Blom