DSNDB07 on multiple volumes?

Mark Labby

DSNDB07 on multiple volumes?
Can a user define DSNDB07 dataset span multiple volumes?

We had an issue over the weekend where they snuck a new join into
production that required massive amounts of data to be sorted and it filled
all of the 4K datasets. We have ten 4K datasets allocated on specific
volumes where the primary allocation is about 3/4 of the volume. When it
needed a lot more space each of the datasets took 3 additional allocations,
hit 45,000 tracks, then tried to go after an A002 dataset. Since they are
user defined, there was no A002 and the jobs were ABENDing. Our DASD
management people have multiple volumes eligible to be used, but it looks
like it filled the initial volume and would not span to a second one. We
are not seeing why it would not continue allocating to the A001 dataset
since it had only used 4 extents.



Dominic MORTIMER

Re: DSNDB07 on multiple volumes?
(in response to Mark Labby)
Mark,

I think that you would need to do an IDCAMS ALTER VOLUME(*) to allow this
VSAM dataset to span multiple volumes.

Cheers

Dom




-----Original Message-----
From: Mark Labby [mailto:[login to unmask email]
Sent: 09 December 2002 17:13
To: [login to unmask email]
Subject: DSNDB07 on multiple volumes?


Can a user define DSNDB07 dataset span multiple volumes?

We had an issue over the weekend where they snuck a new join into
production that required massive amounts of data to be sorted and it filled
all of the 4K datasets. We have ten 4K datasets allocated on specific
volumes where the primary allocation is about 3/4 of the volume. When it
needed a lot more space each of the datasets took 3 additional allocations,
hit 45,000 tracks, then tried to go after an A002 dataset. Since they are
user defined, there was no A002 and the jobs were ABENDing. Our DASD
management people have multiple volumes eligible to be used, but it looks
like it filled the initial volume and would not span to a second one. We
are not seeing why it would not continue allocating to the A001 dataset
since it had only used 4 extents.







_________________________________________________________
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
SchlumbergerSema.
If you are not the intended recipient, be advised that you have received
this email in error and that any use, dissemination, forwarding, printing,
or copying of this email is strictly prohibited.

If you have received this email in error please notify the
SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.
_________________________________________________________



michael bell

Re: DSNDB07 on multiple volumes?
(in response to Dominic MORTIMER)
User defined datasets have the same limits as stogroup datasets but you have
identified the major problem with them. You have to do the initial
del/define for the A002/A003, etc datasets.

The second problem was the initial define didn't identify multiple volumes
so it wouldn't extend past the first volume. This might prevent it from
getting to 2 GB so it can attempt the A002 dataset. If you had the datasets
defined on 3390-3 volumes you should have been able to get 2GB. For the
initial define it would be VOL(* * * *) to let it go to 4 SMS volumes or
stop/ ALTER ADDVOL(* * * *) to let it use more volumes.

Separate statement - The performance difference between DB2 sort and DFSORT
is big enough I would consider unload/ DFSORT / match merge logic.

Mike Bell
HLS Technologies

----- Original Message -----
From: "Mark Labby" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 09, 2002 11:12 AM
Subject: DSNDB07 on multiple volumes?


> Can a user define DSNDB07 dataset span multiple volumes?
>
> We had an issue over the weekend where they snuck a new join into
> production that required massive amounts of data to be sorted and it
filled
> all of the 4K datasets. We have ten 4K datasets allocated on specific
> volumes where the primary allocation is about 3/4 of the volume. When it
> needed a lot more space each of the datasets took 3 additional
allocations,
> hit 45,000 tracks, then tried to go after an A002 dataset. Since they are
> user defined, there was no A002 and the jobs were ABENDing. Our DASD
> management people have multiple volumes eligible to be used, but it looks
> like it filled the initial volume and would not span to a second one. We
> are not seeing why it would not continue allocating to the A001 dataset
> since it had only used 4 extents.
>
>
>





Phil Grainger

Re: DSNDB07 on multiple volumes?
(in response to michael bell)
Mark,

What you need are more DSNDB07 pagesets not more volumes for the pagesets
you have!

DB2 assumes that all work datasets are on distinct volumes and uses that
assumption to optimise it's use of them. If you can afford the space for
these to span volumes, DB2 would 'prefer' that you actually created distinct
datasets on the new volumes instead

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: Mark Labby [mailto:[login to unmask email]
Sent: 09 December 2002 17:13
To: [login to unmask email]
Subject: [DB2-L] DSNDB07 on multiple volumes?


Can a user define DSNDB07 dataset span multiple volumes?

We had an issue over the weekend where they snuck a new join into
production that required massive amounts of data to be sorted and it filled
all of the 4K datasets. We have ten 4K datasets allocated on specific
volumes where the primary allocation is about 3/4 of the volume. When it
needed a lot more space each of the datasets took 3 additional allocations,
hit 45,000 tracks, then tried to go after an A002 dataset. Since they are
user defined, there was no A002 and the jobs were ABENDing. Our DASD
management people have multiple volumes eligible to be used, but it looks
like it filled the initial volume and would not span to a second one. We
are not seeing why it would not continue allocating to the A001 dataset
since it had only used 4 extents.





Robert J. Milonas

Re: DSNDB07 on multiple volumes?
(in response to Phil Grainger)
I would think that DB2 would use the VSAM candidate volumes for secondaries. We allocate (what we think is a max) primary allocation and if it fills that, we want things to abend (we don't specify secondaries). Instead of filling five volumes, we use to fill 12 or 15 volumes and then it would then blow off anyway. Most of the time it would be someone modifying an existing query resulting in a cartesian product.


Mark Labby <[login to unmask email]> wrote:

>Can a user define DSNDB07 dataset span multiple volumes?
>
>We had an issue over the weekend where they snuck a new join into
>production that required massive amounts of data to be sorted and it filled
>all of the 4K datasets. We have ten 4K datasets allocated on specific
>volumes where the primary allocation is about 3/4 of the volume. When it
>needed a lot more space each of the datasets took 3 additional allocations,
>hit 45,000 tracks, then tried to go after an A002 dataset. Since they are
>user defined, there was no A002 and the jobs were ABENDing. Our DASD
>management people have multiple volumes eligible to be used, but it looks
>like it filled the initial volume and would not span to a second one. We
>are not seeing why it would not continue allocating to the A001 dataset
>since it had only used 4 extents.
>
>
>
>

__________________________________________________________________
The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp

Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/



francisco fred

Re: DSNDB07 on multiple volumes?
(in response to Robert J. Milonas)

Hi Mark
I had this problem some times ago and now i give you my experience , do the following :
1- Use the command STOP DATABASE (DSNDB07) to ensure that no user are accessing the data base.
2- Add space for extension to the DB2 storage group by one of these methods :
A - Use SQL to change the storage group , adding volumes as nessary
B - Use SQL to create more table spaces in database DSNDB07
3- use the command START DATABASE (DSNDB07).
But be careful if you use method B it's cause DB2 uses these data sets in parallel and may be again you job ABENDED if you have heavy data for load , instead , if you use method A , DB2 creates first VSAM data set (A001) and then creates another data sets( A002 ,....)


Mark Labby <[login to unmask email]> wrote:Can a user define DSNDB07 dataset span multiple volumes?

We had an issue over the weekend where they snuck a new join into
production that required massive amounts of data to be sorted and it filled
all of the 4K datasets. We have ten 4K datasets allocated on specific
volumes where the primary allocation is about 3/4 of the volume. When it
needed a lot more space each of the datasets took 3 additional allocations,
hit 45,000 tracks, then tried to go after an A002 dataset. Since they are
user defined, there was no A002 and the jobs were ABENDing. Our DASD
management people have multiple volumes eligible to be used, but it looks
like it filled the initial volume and would not span to a second one. We
are not seeing why it would not continue allocating to the A001 dataset
since it had only used 4 extents.





---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

Max Scarpa

Re: DSNDB07 on multiple volumes?
(in response to francisco fred)
Hi Mark

Take a look at

Understanding the DB2 for z/OS Sort

By Chuck Hoover. There are some hints about DB2 DSNDB07 including DASD tips
other than a good explanation about DB2 sort.

You can downlod it from Compuware web sitez or SHARE proceedings.

Regards



Alexandros Papadopoulos

Re: DSNDB07 on multiple volumes?
(in response to Max Scarpa)
A couple of notes on that:
1) 3/4 of a volume is quite close to the limit of this dataset (2GB,
actually 43695 tracks). It therefore cannot be larger and attempts to use
A002). Even if you have defined (or alter) A001 as multivolume, it cannot
grow anymore.
2) Since the ds is user defined, DB2 cannot allocate A002 automatically. It
has to be predifined.

Now let me explain our problem (unsolved-no PMR created yet, we only spoke
for a while about it with local IBM support-we plan to open a PMR anyway):
We have 4 4K temp datasets. We created A001 as 2GB primary and since we were
getting -904 on A002, we defined also 4 A002 (one for every DSNTMP0x TS),
2GB each. Of course -904 went away,

BUT: From time to time, on very long queries (even on creation of huge
indexes) we get a quite annoying s04E abend:

DSNI014I + DSNITLPE DATA IN USE DURING ABEND
REASON 00C90101
ERQUAL 2005
TYPE 00000302
NAME DSNDB07 .DSNTMP03.X'00080167' <-- this page varies between
different abends.
CONNECTION-ID=DB2CALL
CORRELATION-ID=U903
LUW-ID=*
DSNI014I + DSNITLPE DATA IN USE DURING ABEND
REASON 00C90101
ERQUAL 2005
TYPE 00000302
NAME DSNDB07 .DSNTMP03.X'0007E031' <-- this page is consistent.
CONNECTION-ID=DB2CALL
CORRELATION-ID=U903
LUW-ID=*

Note that we redefine DSNDB07 datasets daily, we are on DB2 V5 on z/OS 1.1,
not very current.
We plan to open a PMR on that-Oh, I've said that already.

Alekos

-----Original Message-----
From: Mark Labby [mailto:[login to unmask email]
Sent: Monday, December 09, 2002 7:13 PM
To: [login to unmask email]
Subject: DSNDB07 on multiple volumes?


Can a user define DSNDB07 dataset span multiple volumes?

We had an issue over the weekend where they snuck a new join into
production that required massive amounts of data to be sorted and it filled
all of the 4K datasets. We have ten 4K datasets allocated on specific
volumes where the primary allocation is about 3/4 of the volume. When it
needed a lot more space each of the datasets took 3 additional allocations,
hit 45,000 tracks, then tried to go after an A002 dataset. Since they are
user defined, there was no A002 and the jobs were ABENDing. Our DASD
management people have multiple volumes eligible to be used, but it looks
like it filled the initial volume and would not span to a second one. We
are not seeing why it would not continue allocating to the A001 dataset
since it had only used 4 extents.








Michael Ebert

Re: DSNDB07 on multiple volumes?
(in response to Alexandros Papadopoulos)
You should rather have more smaller work datasets than few giant (let
alone multi-DS) ones, especially if you are using parallelism. Also,
having secondary extents on a work DS serves no really useful purpose. If
you have the space, allocate it right from the start.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



Can a user define DSNDB07 dataset span multiple volumes?

We had an issue over the weekend where they snuck a new join into
production that required massive amounts of data to be sorted and it
filled
all of the 4K datasets. We have ten 4K datasets allocated on specific
volumes where the primary allocation is about 3/4 of the volume. When it
needed a lot more space each of the datasets took 3 additional
allocations,
hit 45,000 tracks, then tried to go after an A002 dataset. Since they are
user defined, there was no A002 and the jobs were ABENDing. Our DASD
management people have multiple volumes eligible to be used, but it looks
like it filled the initial volume and would not span to a second one. We
are not seeing why it would not continue allocating to the A001 dataset
since it had only used 4 extents.

Michael Ebert

Re: DSNDB07 on multiple volumes?
(in response to Michael Ebert)
1. Huge indexes should be created with DEFER YES followed by REBUILD INDEX.
It's much faster and causes nearly no logging.
2. What is the point of redefining the workfile Datasets daily??
3. Is this maybe a (SAP) system involved in a Homogeneous System
Copy/Cloning (either as source or target)? Possibly the workfile VSAM
datasets are used by two DB2 subsystems.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany

On Tue, 10 Dec 2002 10:49:41 +0200, Alekos Papadopoulos <[login to unmask email]>
wrote:

>Now let me explain our problem (unsolved-no PMR created yet, we only spoke
>for a while about it with local IBM support-we plan to open a PMR anyway):
>We have 4 4K temp datasets. We created A001 as 2GB primary and since we
were
>getting -904 on A002, we defined also 4 A002 (one for every DSNTMP0x TS),
>2GB each. Of course -904 went away,
>
>BUT: From time to time, on very long queries (even on creation of huge
>indexes) we get a quite annoying s04E abend:
>
>DSNI014I + DSNITLPE DATA IN USE DURING ABEND
> REASON 00C90101
> ERQUAL 2005
> TYPE 00000302
> NAME DSNDB07 .DSNTMP03.X'00080167' <-- this page varies between
>different abends.
> CONNECTION-ID=DB2CALL
> CORRELATION-ID=U903
> LUW-ID=*
>
>Note that we redefine DSNDB07 datasets daily, we are on DB2 V5 on z/OS 1.1,
>not very current.
>We plan to open a PMR on that-Oh, I've said that already.
>
>Alekos



Alexandros Papadopoulos

Re: DSNDB07 on multiple volumes?
(in response to Michael Ebert)
Dear Dr. Ebert,
1. DEFER YES is an excellent idea. I never thought about it. Thank you very
much. (What we actually did, since it was an historical table used only
during batch processing, we unloaded, deleted, created the index and
reloaded, but your idea is by far much better).
2. You are also right about the redefinition of datasets. Actually, we are
still lucky enough to have a short window, where we stop our system to take
DR copies. Thre redefinition idea initially came from a previous definition,
who had much smaller priqty, therefore we were able to remove extents. Since
it's really short and well scheduled job however, we prefer to let it as is,
since it give us the freedom to 'resize' DSNDB07 when needed, by actually
changing only this job.
3. Unfortunately, this is not our case. No other subsystem is using these
datasets, nor it's a system involved in homogeneous cloning. Apart from
that, it appears only if the second (A002) dataset is used. A -very cloudy I
have to admit- guess is that probably (???) DB2 uses some 'special locking'
on DSNDB07, based on Dataset Name(???) - I only guess it because DSNDB07 has
some 'special handling' (must be stopped for an ALTER for example) but
guessing may always be nonsence.

Once more thank you very much

Alekos

-----Original Message-----
From: Dr. Michael Ebert [mailto:[login to unmask email]
Sent: Tuesday, December 10, 2002 11:48 AM
To: [login to unmask email]
Subject: Re: DSNDB07 on multiple volumes?


1. Huge indexes should be created with DEFER YES followed by REBUILD INDEX.
It's much faster and causes nearly no logging.
2. What is the point of redefining the workfile Datasets daily??
3. Is this maybe a (SAP) system involved in a Homogeneous System
Copy/Cloning (either as source or target)? Possibly the workfile VSAM
datasets are used by two DB2 subsystems.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany

On Tue, 10 Dec 2002 10:49:41 +0200, Alekos Papadopoulos <[login to unmask email]>
wrote:

>Now let me explain our problem (unsolved-no PMR created yet, we only spoke
>for a while about it with local IBM support-we plan to open a PMR anyway):
>We have 4 4K temp datasets. We created A001 as 2GB primary and since we
were
>getting -904 on A002, we defined also 4 A002 (one for every DSNTMP0x TS),
>2GB each. Of course -904 went away,
>
>BUT: From time to time, on very long queries (even on creation of huge
>indexes) we get a quite annoying s04E abend:
>
>DSNI014I + DSNITLPE DATA IN USE DURING ABEND
> REASON 00C90101
> ERQUAL 2005
> TYPE 00000302
> NAME DSNDB07 .DSNTMP03.X'00080167' <-- this page varies between
>different abends.
> CONNECTION-ID=DB2CALL
> CORRELATION-ID=U903
> LUW-ID=*
>
>Note that we redefine DSNDB07 datasets daily, we are on DB2 V5 on z/OS 1.1,
>not very current.
>We plan to open a PMR on that-Oh, I've said that already.
>
>Alekos








Pantazis Pantazi

Re: DSNDB07 on multiple volumes?
(in response to Alexandros Papadopoulos)
We had the same problem, ERQUAL 2005, on one of our user tablespaces and we
opened a PMR on it with IBM. Luckily
the problem was not in the Production environment.

IBM had supplied us with the following preventive PTFs:
APAR PTF
PQ45732 UQ51953
PQ45330 UQ50952
PQ46465 UQ52263
PQ51174 UQ57841
PQ52432 UQ58078
PQ50073 UQ57145
PQ37219 UQ43422

The above do NOT solve the problem but rather prevent it from happening, if
not already there.

They said that since the problem is not in production that the PTF would be
sufficient. We are on DB2/390 V6.1.

Of course the PTFs have been applied but the problem in preproduction was
solved by redefining the tablespace.

Regards,

Pantazis Pantazi
Database Administrator

Enterprise Systems Support
Information Services Division
Laiki Group, Cyprus
Tel: +357-22812530, Fax: +357-22812583
Email: [login to unmask email]



Alekos
Papadopoulos To: [login to unmask email]
<[login to unmask email]> cc:
Sent by: DB2 Data bcc:
Base Discussion Subject: Re: DSNDB07 on multiple volumes?
List
<[login to unmask email]
ASSOC.COM>
10/12/2002 10:49
Please respond to
DB2 Data Base
Discussion List







A couple of notes on that:
1) 3/4 of a volume is quite close to the limit of this dataset (2GB,
actually 43695 tracks). It therefore cannot be larger and attempts to use
A002). Even if you have defined (or alter) A001 as multivolume, it cannot
grow anymore.
2) Since the ds is user defined, DB2 cannot allocate A002 automatically. It
has to be predifined.

Now let me explain our problem (unsolved-no PMR created yet, we only spoke
for a while about it with local IBM support-we plan to open a PMR anyway):
We have 4 4K temp datasets. We created A001 as 2GB primary and since we
were
getting -904 on A002, we defined also 4 A002 (one for every DSNTMP0x TS),
2GB each. Of course -904 went away,

BUT: From time to time, on very long queries (even on creation of huge
indexes) we get a quite annoying s04E abend:

DSNI014I + DSNITLPE DATA IN USE DURING ABEND
REASON 00C90101
ERQUAL 2005
TYPE 00000302
NAME DSNDB07 .DSNTMP03.X'00080167' <-- this page varies between
different abends.
CONNECTION-ID=DB2CALL
CORRELATION-ID=U903
LUW-ID=*
DSNI014I + DSNITLPE DATA IN USE DURING ABEND
REASON 00C90101
ERQUAL 2005
TYPE 00000302
NAME DSNDB07 .DSNTMP03.X'0007E031' <-- this page is consistent.
CONNECTION-ID=DB2CALL
CORRELATION-ID=U903
LUW-ID=*

Note that we redefine DSNDB07 datasets daily, we are on DB2 V5 on z/OS 1.1,
not very current.
We plan to open a PMR on that-Oh, I've said that already.

Alekos

-----Original Message-----
From: Mark Labby [mailto:[login to unmask email]
Sent: Monday, December 09, 2002 7:13 PM
To: [login to unmask email]
Subject: DSNDB07 on multiple volumes?


Can a user define DSNDB07 dataset span multiple volumes?

We had an issue over the weekend where they snuck a new join into
production that required massive amounts of data to be sorted and it filled
all of the 4K datasets. We have ten 4K datasets allocated on specific
volumes where the primary allocation is about 3/4 of the volume. When it
needed a lot more space each of the datasets took 3 additional allocations,
hit 45,000 tracks, then tried to go after an A002 dataset. Since they are
user defined, there was no A002 and the jobs were ABENDing. Our DASD
management people have multiple volumes eligible to be used, but it looks
like it filled the initial volume and would not span to a second one. We
are not seeing why it would not continue allocating to the A001 dataset
since it had only used 4 extents.



the





the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



********************************************************************************************
Privileged/Confidential information may be contained in this message and
may be subject to legal privilege. Access to this e-mail by anyone other
than the intended recipient is unauthorised. If you are not the intended
recipient (or responsible for delivery of the message to such person), you
may not use, copy, distribute or deliver to anyone this message (or any
part of its contents) or take any action in reliance on it. In such case,
you should destroy this message, and notify us immediately.

If you have received this email in error, please notify us immediately by
e-mail or telephone and delete the e-mail from any computer. If you or your
employer does not consent to internet e-mail messages of this kind, please
notify us immediately.

All reasonable precautions have been taken to ensure no viruses are present
in this e-mail. As we cannot accept responsibility for any loss or damage
arising from the use of this e-mail or attachments we recommend that you
subject these to your virus checking procedures prior to use.

The views, opinions, conclusions and other information expressed in this
electronic mail are not given or endorsed by Laiki Group unless otherwise
indicated by an authorised representative independent of this message.
********************************************************************************************