dsndb07 problem

Mike Ewanowski

unread,

Apr 4, 1997, 3:00:00 AM4/4/97

to

Any help is appreciated on the following problem. Using version 4, we are
getting this message back in the MVS log from a QMF customer query:

DSNP007I - DSNPXTN0 - EXTEND FAILED FOR 251
DB2P.DSNDBD.DSNDB07.DSNTMP04.I0001.A001.
RC=00D70027
CONNECTION-ID=DB2CALL, CORRELATION-ID=ISEBB,
LUW-ID=*

The strange thing is that there are 5 separate work tablespaces in DSNDB07.
All are identically defined with allocations each of cylinder(175,0). Why
would the extend fail on only the 4th one? All are available as RW status
when I do a display database in SPUFI. The customer admits that this query
returns a large working set of data but that it has run sucessfully before.
TIA for any suggestions.

P.S. I plan to delete and redefine the 5 tablespaces tomorrow when no
customers are online.
But I'd still like an answer if possible.

+--------------------------------+
| \\\|/// |
| \ ~ ~ / |
| (\ @ @ /) |
+--------o000--(_)--000o---------+

Greg Palgrave

unread,

Apr 6, 1997, 4:00:00 AM4/6/97

to

Mike,

It's not really a problem. The extend failure is because there is no
secondary space allocation, so when the temp dataset is full, you get
this message.

What it is indicating about the QMF query is that it is using a lot
of sort space, or is doing a complex join etc and DB2 uses the DSNDB07
datasets as a work area. If it is using one, and it fills up, it simply
swaps to another one. It doesn't necessarily start at number 1 either.

You may want to look at the customers QMF query. It is most likely to
need some rewriting to make it a little more efficient... :)

Cheers,
Greg Palgrave

Mike Ewanowski <is...@EMORY.EDU> wrote in article
<MailDrop1.2d3...@mike.cc.emory.edu>...

Ru...@aol.com

unread,

Apr 6, 1997, 4:00:00 AM4/6/97

to

Hi Mike,

1. DB2 will use all of your (5) defined sort tablespaces.

2. When all space is exhausted, the message below will occur indicating the
last used sort tablespace.

3. sort tablespaces are concurrently used by other users. That's why the
user's query could be ran before. He could retry during low (sort-)activity
time.

So long,
Ruediger Wuepper

================
In einer eMail vom 04.04.1997 21:56:57, schreibt is...@emory.edu (Mike
Ewanowski):

Carter, N Allison (nacarte)

unread,

Apr 7, 1997, 3:00:00 AM4/7/97

to

Or RUNSTATS may need to be run (and the plan rebound).

Allison Carter
Pacific Bell DBA

----------
From: DB2 Data Base Discussion List[SMTP:DB...@AMERICAN.EDU]
Sent: Saturday, April 5, 1997 10:42PM
To: DB2-L
Subject: Re: dsndb07 problem

Mike,

It's not really a problem. The extend failure is because there is no
secondary space allocation, so when the temp dataset is full, you get
this message.

What it is indicating about the QMF query is that it is using a lot
of sort space, or is doing a complex join etc and DB2 uses the DSNDB07
datasets as a work area. If it is using one, and it fills up, it simply
swaps to another one. It doesn't necessarily start at number 1 either.

You may want to look at the customers QMF query. It is most likely to
need some rewriting to make it a little more efficient... :)

Cheers,
Greg Palgrave

Mike Ewanowski <is...@EMORY.EDU> wrote in article
<MailDrop1.2d3...@mike.cc.emory.edu>...

> Any help is appreciated on the following problem. Using version 4, we
are
> getting this message back in the MVS log from a QMF customer query:
>
> DSNP007I - DSNPXTN0 - EXTEND FAILED FOR 251
> DB2P.DSNDBD.DSNDB07.DSNTMP04.I0001.A001.
> RC=00D70027
> CONNECTION-ID=DB2CALL, CORRELATION-ID=ISEBB,
> LUW-ID=*
>
> The strange thing is that there are 5 separate work tablespaces in
DSNDB07.
> All are identically defined with allocations each of cylinder(175,0).
Why
> would the extend fail on only the 4th one? All are available as RW
status
> when I do a display database in SPUFI. The customer admits that this
query
> returns a large working set of data but that it has run sucessfully
before.
> TIA for any suggestions.
>