Work File Database Temp files

william giannelli

Work File Database Temp files

For the Work File database, should secondary extents be specified ?

I was failing on extents due to a filled storage group which I resolved by adding volumes.

But should I have defined with 0 secondary extents?

dataset appears to be an indexspace:

XXXXX.DSNDBC.WORKDB.TIX12768.I0001.A001

thanks

Bill

Ruediger Kurtz

AW: Work File Database Temp files
(in response to william giannelli)
Bill,

I’m not sure I can give you an „one solution fits all“ answer, but we haven’t given any work-TS any secondary.
We usually define them

PRIQTY <a_figure_you_prefer>
SECQTY 0

because we do not want our work-tablespaces to be allocated all over the place. Leave them in one chunk, if possible, define more of them, if necessary, that’s what we do / did.

Hth

Regards

Ruediger




Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: william giannelli [mailto:[login to unmask email]
Gesendet: Donnerstag, 12. Oktober 2017 15:27
An: [login to unmask email]
Betreff: [DB2-L] - Work File Database Temp files


For the Work File database, should secondary extents be specified ?

I was failing on extents due to a filled storage group which I resolved by adding volumes.

But should I have defined with 0 secondary extents?

dataset appears to be an indexspace:

XXXXX.DSNDBC.WORKDB.TIX12768.I0001.A001

thanks

Bill

-----End Original Message-----

Barbara Koenen

Work File Database Temp files
(in response to Ruediger Kurtz)
We have allocated workfiles with 0 secondary, to keep a poorly performing process from being able to allocate extents.

If, after a review of the process affected, we truly need more workspace, we will allocate addition workfiles with 0 secondary.

Again, it probably depends on what you want to control and monitor.


From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Thursday, October 12, 2017 8:53 AM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - AW: Work File Database Temp files

Bill,

I’m not sure I can give you an „one solution fits all“ answer, but we haven’t given any work-TS any secondary.
We usually define them

PRIQTY <a_figure_you_prefer>
SECQTY 0

because we do not want our work-tablespaces to be allocated all over the place. Leave them in one chunk, if possible, define more of them, if necessary, that’s what we do / did.

Hth

Regards

Ruediger




Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de http://www.huk.de

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: william giannelli [mailto:[login to unmask email]
Gesendet: Donnerstag, 12. Oktober 2017 15:27
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - Work File Database Temp files


For the Work File database, should secondary extents be specified ?

I was failing on extents due to a filled storage group which I resolved by adding volumes.

But should I have defined with 0 secondary extents?

dataset appears to be an indexspace:

XXXXX.DSNDBC.WORKDB.TIX12768.I0001.A001

thanks

Bill

-----End Original Message-----

-----End Original Message-----

Philip Sevetson

Work File Database Temp files
(in response to william giannelli)
**please note my email address change**
William,

Define the _primary_ of a Temp tablespace to be as large as you expect to need them according to the various uses (I believe that temp TS are still being used as a swap pool for internal sorts, so figure space for that as well). Someone’s going to dump a query on you, someday, which will use all of that space, and blow up. Too bad, but inevitable -- you can’t give a query infinite space for a work file.

If you define a nonzero secondary quantity, then you’ll start off fine and using less DASD, but the failure mode is that:

1) Someone dumps that huge materialization on it one day, then suddenly in the middle of the query, z/OS is allocating and formatting extents right and left

2) If you hit max extents it’ll _still_ blow up, and at the same time and size which it would have if you’d allocated that space in the primary.

3) You’ll have all of those secondary extents allocated, your temp DASD will be scattered over whatever pool you’re using for it

4) You can’t get the space back without stopping the temp tablespace and delete/defining the dataset

Finally: Given that you have to have x cylinders of secondary space left available for expansion, doesn’t it make sense to just allocate x cylinders as part of the primary quantity? Instead of “reserving” the space and having problems in the middle of a query?


Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: william giannelli [mailto:[login to unmask email]
Sent: Thursday, October 12, 2017 9:27 AM
To: [login to unmask email]
Subject: [DB2-L] - Work File Database Temp files


For the Work File database, should secondary extents be specified ?

I was failing on extents due to a filled storage group which I resolved by adding volumes.

But should I have defined with 0 secondary extents?

dataset appears to be an indexspace:

XXXXX.DSNDBC.WORKDB.TIX12768.I0001.A001

thanks

Bill

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Andy Smith

RE: Work File Database Temp files
(in response to william giannelli)

Bill, we have a mixture, some with zero SECQTY and some with non-zero SECQTY, because we want to separate work file sort and DGTT work (have a read about ZPARM WFDBSEP).

Barbara Koenen

Work File Database Temp files
(in response to Andy Smith)
Yes, this is the only reason we found to allocate a workfile with non-zero secondary.

From: Andy Smith [mailto:[login to unmask email]
Sent: Thursday, October 12, 2017 9:44 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Work File Database Temp files


Bill, we have a mixture, some with zero SECQTY and some with non-zero SECQTY, because we want to separate work file sort and DGTT work (have a read about ZPARM WFDBSEP).

-----End Original Message-----

Phil Grainger

Work File Database Temp files
(in response to Barbara Koenen)
As Barbara says “it depends”

These days, you can create some WITH secondary allocations and some WITHOUT – and dictate to Db2 how you want them to be used (scrollable cursors, temporary tables, sort work space etc)

In some cases secondary allocations make sense, in others they may mask problems your’d rather know about

________________________________

Phil Grainger

Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Barbara Koenen [mailto:[login to unmask email]
Sent: 12 October 2017 15:21
To: [login to unmask email]
Subject: [DB2-L] - RE: Work File Database Temp files

We have allocated workfiles with 0 secondary, to keep a poorly performing process from being able to allocate extents.

If, after a review of the process affected, we truly need more workspace, we will allocate addition workfiles with 0 secondary.

Again, it probably depends on what you want to control and monitor.


From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Thursday, October 12, 2017 8:53 AM
To: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Subject: [DB2-L] - AW: Work File Database Temp files

Bill,

I’m not sure I can give you an „one solution fits all“ answer, but we haven’t given any work-TS any secondary.
We usually define them

PRIQTY <a_figure_you_prefer>
SECQTY 0

because we do not want our work-tablespaces to be allocated all over the place. Leave them in one chunk, if possible, define more of them, if necessary, that’s what we do / did.

Hth

Regards

Ruediger




Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.huk.de&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=6GVTmY5QJyn_Sm0Nr3QNwFlL3GGQ6w_sf4zrSL8QsM8&s=qKC0Nlxwj2bQfEDND37-7K6FYokK_8G5taoZxOIKLNI&e=

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: william giannelli [mailto:[login to unmask email]
Gesendet: Donnerstag, 12. Oktober 2017 15:27
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - Work File Database Temp files


For the Work File database, should secondary extents be specified ?

I was failing on extents due to a filled storage group which I resolved by adding volumes.

But should I have defined with 0 secondary extents?

dataset appears to be an indexspace:

XXXXX.DSNDBC.WORKDB.TIX12768.I0001.A001

thanks

Bill

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (5.9k)

Gabriel Pelly

RE: Work File Database Temp files
(in response to Phil Grainger)

For one particular client, we have a mixture of both 0 and -1 for SECQTY, defined for both 4K and 32K workfiles TS.

Yes, we do have the occasional user requested large queries that almost fill the DASD pool- that we can't prevent. To combat this we have a weekly REXX that drops and recreates oversized workfile TS (no more 2am calls :)

Graham Clarke

RE: Work File Database Temp files
(in response to Gabriel Pelly)

We also use a mixture of 0 & -1.
In addition we use MAXTEMPS to restrict how much space an individual task can allocate which helps keep the bad boys in check,

Javier Estrada Benavides

RE: Work File Database Temp files
(in response to Gabriel Pelly)

Hey, how's it going?

  I also do that exact same thing, keep a set with SECQTY 0 and another with SECQTY -1. I believe the important question here is, how often do you redefine those VSAMs? More often than not, that's the real problem that hits you.

 

Regards,

Javier Estrada Benavides, Mexico

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Database Administrator - DB2 11 DBA for z/OS