Adam,

Best bet would be to contact service, I only know a bit about the
underlying way these were written, but the performance should be 'close'
to that of the api (they use the api from within a fenced udf, and
return the data in table format). So something must be failing/looping
in the underlying udf code.

Adam wrote:
> Howdy,
>
> I'm trying to use the new SQL based snapshot functions to get the
> tablespace containers (SNAPSHOT_CONTAINER). On systems with
> approximately 1400 tablespaces, the query runs for about 40 minutes
> before falling over (see below). If I perform the same function using
> API's, it takes less than a minute and works great.
>
> Any ideas why the SQL function is performing so poorly? I'd like to stay
> in Java, if possible. I'm running on Solaris with FP2, but have seen
> this same behavior with AIX.
>
> Thanks for any help,
>
>
>
> Adam
>
>
>
> Here's the error when the SQL finally fails:
>
> $ db2 "select * from table(snapshot_container('LARGE', -1)) as b
> ob"
>
> SNAPSHOT_TIMESTAMP TABLESPACE_ID TABLESPACE_NAME
>
> CONTAINER_ID CONTAINER_NAME
>
>
> CONTAINER_TYPE
> TOTAL_PAGE
> S USABLE_PAGES ACCESSIBLE STRIPE_SET
> -------------------------- --------------------
> --------------------------------
> --------------------------------------------------------------------------------
>
> ---------------- --------------------
> ------------------------------------------
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
>
> ------------------------------------------------------ --------------
> ----------
> ---------- -------------------- -------------------- --------------------
> SQL0443N Routine "SYSPROC.SNAPSHOT_CONTAINER" (specific name
> "SNAPSHOT_CONTAINER") has returned an error SQLSTATE with diagnostic text
> "DBA7612". SQLSTATE=38553
>