SEO

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

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
>

Thanks for the reply. I did some looking around and there is a fix in
FP3 for a bug which sounds a lot like my problem. I'm downloading the
fixpack, and keeping my fingers crossed.

Thanks again,


Adam


Sean McKeough wrote:
> 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
>>
>