Question about use of SYSDUMMY1

Kathleen S. Jones

Question about use of SYSDUMMY1
I sent my database analyst to an SQL Performance tuning class by IBM.
This is a question based from her based on her experience tuning some
queries.

We recently tuned some SQL that was using a noncorrelated subquery to test
existence by turning it into the correlated subquery below:

SELECT 1
INTO :WS-ROW-COUNT
FROM SYSIBM . SYSDUMMY1 D
WHERE EXISTS (
SELECT 1
FROM SSASIDB1 . AATD_DAILY_ATTEND A
WHERE A . PERMNUM = :DCLAATD-DAILY-ATTEND.PERMNUM
AND D . IBMREQD = D . IBMREQD )

The process is quite a bit faster now, but when we do an access path
analysis using File-AID for DB2, it says a tablespace scan will be
performed on the tablespace containing SYSIBM.SYSDUMMY1, which is
DSNDB06.SYSSTR, which contains 60 pages. That tablespace contains 4
tables: SYSDUMMY1, SYSSTRINGS, SYSCHECKS, and SYSCHECKDEP, and a look at
our poster of the system catalog shows this as the standard configuration.

Our question: Is the scan of the 60 pages the way this operation is
supposed to work? Should we have an index for SYSDUMMY1 or be doing
anything else to avoid the tablespace scan?

Kathy Jones
Central Information Services
Clark County School District
O/S390 DB2 DBA
NT DB2 DBA
702-799-5040 x366
[login to unmask email]



teldb2kals

Re: Question about use of SYSDUMMY1
(in response to Kathleen S. Jones)
Hi Kathleen,

The tablespace scan for a segmented tablespace (as in this case) will
attempt to read only the pages of the required table, in this case
SYSIBM.SYSDUMMY1, which you will find occupies only 1 page.

Regards,
Kals.

On Mon, 6 Jan 2003 16:30:26 -0800, Kathleen S. Jones
<[login to unmask email]> wrote:

>I sent my database analyst to an SQL Performance tuning class by IBM.
>This is a question based from her based on her experience tuning some
>queries.
>
>We recently tuned some SQL that was using a noncorrelated subquery to test
>existence by turning it into the correlated subquery below:
>
>SELECT 1
>INTO :WS-ROW-COUNT
>FROM SYSIBM . SYSDUMMY1 D
>WHERE EXISTS (
>SELECT 1
>FROM SSASIDB1 . AATD_DAILY_ATTEND A
>WHERE A . PERMNUM = :DCLAATD-DAILY-ATTEND.PERMNUM
>AND D . IBMREQD = D . IBMREQD )
>
>The process is quite a bit faster now, but when we do an access path
>analysis using File-AID for DB2, it says a tablespace scan will be
>performed on the tablespace containing SYSIBM.SYSDUMMY1, which is
>DSNDB06.SYSSTR, which contains 60 pages. That tablespace contains 4
>tables: SYSDUMMY1, SYSSTRINGS, SYSCHECKS, and SYSCHECKDEP, and a look at
>our poster of the system catalog shows this as the standard configuration.
>
>Our question: Is the scan of the 60 pages the way this operation is
>supposed to work? Should we have an index for SYSDUMMY1 or be doing
>anything else to avoid the tablespace scan?
>
>Kathy Jones
>Central Information Services
>Clark County School District
>O/S390 DB2 DBA
>NT DB2 DBA
>702-799-5040 x366
>[login to unmask email]
>



Jim Ruddy

Re: Question about use of SYSDUMMY1
(in response to teldb2kals)
The cardinality of SYSDUMMY1 is 1 and is in a segmented tablespace. Although
the explain shows a tablespace scan only the table is scanned. Adding an
index would at best only waste space and at worse would could slow down the
query if you were somehow able to force usage of the index.

Jim Ruddy
DB2 for z/OS Development



Kathleen S. Jones

Re: Question about use of SYSDUMMY1
(in response to Jim Ruddy)
Thanks for the quick replies -I will pass this on to my analyst in the
morning.
DB2 Data Base Discussion List <[login to unmask email]> writes:
>The cardinality of SYSDUMMY1 is 1 and is in a segmented tablespace.
>Although
>the explain shows a tablespace scan only the table is scanned. Adding an
>index would at best only waste space and at worse would could slow down
>the
>query if you were somehow able to force usage of the index.
>
>Jim Ruddy
>DB2 for z/OS Development
>
>
>
>the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
>can