Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!

Aurora Emanuela Dellanno

Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!

Hi all,

 

it's all very well for the manuals to say that "Db2 12 uses more 32KB work files than previous Db2 releases" - but how many is MORE?

 

In one of our production systems, the only one we have brought to Db2 12 FL100 so far, we are finding all our storage being used up, we currently have 20 x 32k work datasets for one workfile, and can't find any good reason for it.

 

Unless it is connected to an error with storage, which incidentally makes our batch COPY jobs abend with 04E - not deleting/cleaning up after itself (oh yeah, we have just received a note that our APAR is closed and a PTF from IBM)?

 

Does anyone have any experience with this, or pointers? How do we clean up after the mess? Should we open a PMR?

 

Thanks.

 

Aurora

 

PS stay safe and healthy

NB black lives matter

Roy Boxwell

Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Aurora Emanuela Dellanno)
Make sure they do not all get secondary allocations and find the sql that generates cartesian joins...
I find 8 - 10 4K and 20 or so 32k all PBGs with maxpartitions 1


Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

On 8 Jun 2020, at 16:21, Aurora Emanuela Dellanno <[login to unmask email]> wrote:



Hi all,



it's all very well for the manuals to say that "Db2 12 uses more 32KB work files than previous Db2 releases" - but how many is MORE?



In one of our production systems, the only one we have brought to Db2 12 FL100 so far, we are finding all our storage being used up, we currently have 20 x 32k work datasets for one workfile, and can't find any good reason for it.



Unless it is connected to an error with storage, which incidentally makes our batch COPY jobs abend with 04E - not deleting/cleaning up after itself (oh yeah, we have just received a note that our APAR is closed and a PTF from IBM)?



Does anyone have any experience with this, or pointers? How do we clean up after the mess? Should we open a PMR?



Thanks.



Aurora



PS stay safe and healthy

NB black lives matter

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

Aurora Emanuela Dellanno

RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Roy Boxwell)

excellent point, Roy - would you actually say to drop and recreate our work DB 32k-TBSs with SECQTY 0? we have a maintenance window coming up shortly, so would be able to do it then, right now we have the first TBS of our work DB with about 20 extents, the nasty so-and-so !@§#%&!!! *insert angry face here*

 

We were thinking in terms of leaving S32KSPAC as 0 so as not to suddenly run out of work space, but at the same time limit the growth of the individual TBSs - while speaking to our developers sternly (holding a big stick in a threatening manner)...

 

Can anyone out there give me an idea of how many 32k work TBSs they're spacing out on, generally? on our DB2 11 systems we seem to have 2 at most, however we are in the happy situation of having a new/changed app in our environment at the same time as having gone to Db2 12. Lucky us.

 

This is our production environment, as I think I mentioned, and we have 4 work 32k TBSs, the first one with a ton of DS/extents, the second with 4 DS/extents, and the other two with one DS each.

 

Also, what oh what can we do aside from an offline REORG to resize our goshdarn work DB?

 

Thanks.

 

Aurora

 

PS stay safe and healthy

NB black lives matter

Roy Boxwell

Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Aurora Emanuela Dellanno)
Yep – Drop and recreate as REORG will not work on them… From Db2 Docu:



You can run REORG TABLESPACE on the table spaces in the Db2 catalog database (DSNDB06) and on

some table spaces in the directory database (DSNDB01). You cannot run REORG TABLESPACE on any

table space in the DSNDB07 database.



But I have always just done a TS drop, COMMIT and TS Create in a round-robin-style using batch SPUFI – Just submit one job with the same name for each TS and let them run in sequence…

Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Aurora Emanuela Dellanno <[login to unmask email]>
Sent: Tuesday, June 9, 2020 11:32 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!



excellent point, Roy - would you actually say to drop and recreate our work DB 32k-TBSs with SECQTY 0? we have a maintenance window coming up shortly, so would be able to do it then, right now we have the first TBS of our work DB with about 20 extents, the nasty so-and-so !@§#%&!!! *insert angry face here*



We were thinking in terms of leaving S32KSPAC as 0 so as not to suddenly run out of work space, but at the same time limit the growth of the individual TBSs - while speaking to our developers sternly (holding a big stick in a threatening manner)...



Can anyone out there give me an idea of how many 32k work TBSs they're spacing out on, generally? on our DB2 11 systems we seem to have 2 at most, however we are in the happy situation of having a new/changed app in our environment at the same time as having gone to Db2 12. Lucky us.



This is our production environment, as I think I mentioned, and we have 4 work 32k TBSs, the first one with a ton of DS/extents, the second with 4 DS/extents, and the other two with one DS each.



Also, what oh what can we do aside from an offline REORG to resize our goshdarn work DB?



Thanks.



Aurora



PS stay safe and healthy

NB black lives matter



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

Attachments

  • smime.p7s (5.1k)

Aurora Emanuela Dellanno

RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Roy Boxwell)

Hey Roy,

 

we were thinking of using ALTER SECQTY and REORG in our next batch window, since we do not use DSNDB07 as our work DB - which in fact brings us to the interesting point of can we reorganise a TBS despite it being in the work DB, if we don't use the DB2 default of DSNDB07 - we're testing this today and will find out if the historical reason for NOT using the DB2 default actually was based on this...

 

Answers on a postcard please, to PO BOX PERF911.....

 

Thanks.

 

Aurora

 

PS stay safe and healthy

NB black lives matter

Aurora Emanuela Dellanno

RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Aurora Emanuela Dellanno)

(quick answer: you can't even if it's not called DSNDB07.TBS....: UTILITY NOT ALLOWED AGAINST A WORKFILE)

Roy Boxwell

Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Aurora Emanuela Dellanno)
Nah:



DSNU050I 161 13:26:24.00 DSNUGUTC - REORG TABLESPACE WRKSB10.DSN4K00 SHRLEVEL CHANGE AUX NO MAPPINGTABLE

MAP.SB1RO00 FASTSWITCH YES FORCE NONE MAXRO 20 DRAIN ALL TIMEOUT TERM DRAIN_WAIT 3 PARALLEL 2 RETRY 3 RETRY_DELAY

60 DRAIN_ALLPARTS YES PREFORMAT SORTDEVT SYSALLDA SORTNUM 3 COPYDDN(TCOPY)

DSNU065I -SB10 161 13:26:24.00 DSNUGMAP - UTILITY NOT ALLOWED AGAINST A WORKFILE



In my little DS sand box the WRKSB10 is one members work db… Looks like a little docu update is required!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Aurora Emanuela Dellanno <[login to unmask email]>
Sent: Tuesday, June 9, 2020 1:07 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!



Hey Roy,



we were thinking of using ALTER SECQTY and REORG in our next batch window, since we do not use DSNDB07 as our work DB - which in fact brings us to the interesting point of can we reorganise a TBS despite it being in the work DB, if we don't use the DB2 default of DSNDB07 - we're testing this today and will find out if the historical reason for NOT using the DB2 default actually was based on this...



Answers on a postcard please, to PO BOX PERF911.....



Thanks.



Aurora



PS stay safe and healthy

NB black lives matter



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

Attachments

  • smime.p7s (5.1k)

Roy Boxwell

Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Aurora Emanuela Dellanno)
Too fast for me!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Aurora Emanuela Dellanno <[login to unmask email]>
Sent: Tuesday, June 9, 2020 1:23 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!



(quick answer: you can't even if it's not called DSNDB07.TBS....: UTILITY NOT ALLOWED AGAINST A WORKFILE)



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

Attachments

  • smime.p7s (5.1k)

Gabriel Pelly

RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Aurora Emanuela Dellanno)

I started having this type of issue with DB2 V10, where the DB2 workfiles gobbled up all available workfile dasd space and did not release it. It was caused by a few transactions that could not be changed.

My bandaid, was to drop the excessively sized worfile(s) and re-create them. As they are workfiles, they can be dropped without fear of losing data- assuming they are not 'in use' at the time of drop.

I eventually wrote a REXX to perform the process in batch, run weekly. Let me know if you are interested in it.

BTW: At DB2 V10 (I dont believe it has changed), you should have some workfiles defined that have 0 secondary space, and some with secondary space for each of the 4K and 32K workfiles groups.

Cheers

Gabriel

Aurora Emanuela Dellanno

RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Gabriel Pelly)

Hi Gabriel,

 

yes please, if you could share the REXX code I'd really appreciate it - it will save me some effort!

 

Thanks.

 

Aurora

 

PS stay safe

NB black lives matter

James Campbell

Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Aurora Emanuela Dellanno)
We want them to be able to have multiple datasets - but not too many.

MAXPARTITIONS to the rescue.

James Campbell


On 9 Jun 2020 at 4:07, Aurora Emanuela Dellanno wrote:

> Hey Roy,
>  
> we were thinking of using ALTER SECQTY and REORG in our next batch window, since we do not use DSNDB07 as our work DB - which in fact brings us to the interesting point of can we reorganise a TBS despite it being in the work DB, if we don't use the DB2 default of DSNDB07 - we're testing this today and will find out if the historical reason for NOT using the DB2 default actually was based on this...
>  
> Answers on a postcard please, to PO BOX PERF911.....
>  
> Thanks.
>  
> Aurora
>  
> PS stay safe and healthy
> NB black lives matter
>


--
This email has been checked for viruses by AVG.
https://www.avg.com

Aurora Emanuela Dellanno

RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to James Campbell)

Hi James,

 

I am more puzzled now because of your suggestion. Why do we want them to have multiple datasets/MAXPARTITIONS, considering that they are workfiles and that we will never be able to REORG them?

 

At this point, doesn't it make more sense really to have them as extents, since we would be able to delete the datasets not in use after the foul deed (creating them due to some huge sort)?

 

Of course, I understand not wanting to run out of sort work area, but I need to be able to reclaim my disk storage somehow - we have almost finalised that we will actually have SECQTY 0 but leave the S32KSPAC=0 (see earlier post), so that if we can at least see when new TBSs are created from the Db2 point of view without having to monitor our dataset usage instead - and maybe even use limited Omegamon object analysis to see the runaway SQL if and when it happens.

 

Thanks.

 

Aurora

 

PS stay safe and healthy

NB black lives matter

Roy Boxwell

Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Aurora Emanuela Dellanno)
I would never allow more than MAXPARTITIONS 1 for mine… Why would you want multiple parts?

Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Aurora Emanuela Dellanno <[login to unmask email]>
Sent: Wednesday, June 10, 2020 2:18 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!



Hi James,



I am more puzzled now because of your suggestion. Why do we want them to have multiple datasets/MAXPARTITIONS, considering that they are workfiles and that we will never be able to REORG them?



At this point, doesn't it make more sense really to have them as extents, since we would be able to delete the datasets not in use after the foul deed (creating them due to some huge sort)?



Of course, I understand not wanting to run out of sort work area, but I need to be able to reclaim my disk storage somehow - we have almost finalised that we will actually have SECQTY 0 but leave the S32KSPAC=0 (see earlier post), so that if we can at least see when new TBSs are created from the Db2 point of view without having to monitor our dataset usage instead - and maybe even use limited Omegamon object analysis to see the runaway SQL if and when it happens.



Thanks.



Aurora



PS stay safe and healthy

NB black lives matter



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

Attachments

  • smime.p7s (5.1k)

James Campbell

Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Aurora Emanuela Dellanno)
There are two different uses of the work file data base. Well, since DB2 9 anyway.

Old time workfiles. Sort, etc. If a workfile fills up, the data spills into another workfile.

Declared Temporary tables. Can live in only one tablespace.

Specifying WFDBSEP=YES allows us to have a set of tablespaces with SECQTY=0 which
are used as workfiles, and another set with a SECQTY=-1 and MAXPARTITIONS to limit the
maximum size that those DGTTs can grow to. This puts an upper limit to the space that can
be used, so we don't need to reclaim anything.

And, yes, we get some temporary tables which are larger than 2GB. I don't like them, but the
Golden Rule applies. (Those with the gold make the rules.)

James Campbell



On 10 Jun 2020 at 5:17, Aurora Emanuela Dellanno wrote:

> Hi James,
>  
> I am more puzzled now because of your suggestion. Why do we want them to have multiple datasets/MAXPARTITIONS, considering that they are workfiles and that we will never be able to REORG them?
>  
> At this point, doesn't it make more sense really to have them as extents, since we would be able to delete the datasets not in use after the foul deed (creating them due to some huge sort)?
>  
> Of course, I understand not wanting to run out of sort work area, but I need to be able to reclaim my disk storage somehow - we have almost finalised that we will actually have SECQTY 0 but leave the S32KSPAC=0 (see earlier post), so that if we can at least see when new TBSs are created from the Db2 point of view without having to monitor our dataset usage instead - and maybe even use limited Omegamon object analysis to see the runaway SQL if and when it happens.
>  
> Thanks.
>  
> Aurora
>  
> PS stay safe and healthy
> NB black lives matter
>


--
This email has been checked for viruses by AVG.
https://www.avg.com

Pete Woodman

RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to James Campbell)

There is also the ZPARM MAXTEMPS which can be used to limit the amount of storage in the work files a single thread can use.

Gabriel Pelly

RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Aurora Emanuela Dellanno)

HI Aurora,

Thanks for your interest in my REXX, which I've attached :)

The REXX takes two parms:

1-Subsystem id- either group name or SSID.

2-Applychanges- default to N (just looking). I suggest testing as N, till you are comfortable with the rexx before apply the changes with  Y. Start with a test/DBA subsystem.

 

You will need to make changes to it - for your environments:

1-Do a find on DBIA and you will see a list of valid DB2s, that you will need to alter for your site.

2-I've also written it for a datasharing environment, so you may need to alter that at line 63.

3-It gets the current details for each workfile, so it assembles the dsn/tablespace name at line 77,  111, 162, 182, 195, 216 and 318. Change to your naming standards.

4- space_allowance=1000 on line 40, give a tollerance for the amount of tracks over the 'ideal' size that workfiles would have to be before its considered for action. Alter as you see fit.

5-you will probabaly also want to change the version number :).

 

The REXX can be run online through TSO (assuming its in your allocation) eg:

  TSO WRKSPCFX DBIA N

or through JCL. I've also attached a sample JCL.

 

Let me know of any questions.

 

Cheers

Gabriel

Attachments

  • DBA.TSO.CLIST(WRKSPCFX).TXT (13.7k)
  • DB2.V10.SDSNSAMP(WRKSPCFX).TXT (1.1k)

Roy Boxwell

Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
(in response to Gabriel Pelly)
Hi!

Very nice REXX but it needs a few changes to work in Germany and a couple of minor SQL changes to the SQL when run on Db2 12 as the columns that were only in SYSTABLEPART have now been copied up into SYSTABLESPACE.



In ISPF do a C ALL | ! and then C ALL \ ^ that takes care of code page problems. Then the two SQLs just need to get correlations:



SQLSTMT= " SELECT TSP.PQTY, TSP.SQTY, TSP.BPOOL, TS.SEGSIZE " ,

"FROM SYSIBM.SYSTABLEPART AS TSP , SYSIBM.SYSTABLESPACE AS TS ",



And



SQLSTMT= " SELECT TSP.PQTY, TSP.SQTY, TSP.BPOOL, TS.SEGSIZE " ,

"FROM SYSIBM.SYSTABLEPART AS TSP , SYSIBM.SYSTABLESPACE AS TS ",



And then it works like a charm in Düsseldorf!



I also took the liberty of handling non-datasharing by removing the use of msid concatenated with WRK all the way through to be WorkDB and added this at the start:



/* Assume datasharing but assume non-datasharing DB name*/

DataSharing='Y'

WorkDB='DSNDB07'

If ssid = 'DC10',

! ssid = 'QB1A' then DataSharing='N'

If DataSharing='Y' then WorkDB='WRK'!!ssid



Here in my sandbox the DC10 and the QB1A are non-datasharing and all other subsystem are. My standard is WRKssid for the work databases but then I use WorkDB throughout and so you only have one place to change now.







Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Gabriel Pelly <[login to unmask email]>
Sent: Friday, June 12, 2020 1:24 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!



HI Aurora,

Thanks for your interest in my REXX, which I've attached :)

The REXX takes two parms:

1-Subsystem id- either group name or SSID.

2-Applychanges- default to N (just looking). I suggest testing as N, till you are comfortable with the rexx before apply the changes with Y. Start with a test/DBA subsystem.



You will need to make changes to it - for your environments:

1-Do a find on DBIA and you will see a list of valid DB2s, that you will need to alter for your site.

2-I've also written it for a datasharing environment, so you may need to alter that at line 63.

3-It gets the current details for each workfile, so it assembles the dsn/tablespace name at line 77, 111, 162, 182, 195, 216 and 318. Change to your naming standards.

4- space_allowance=1000 on line 40, give a tollerance for the amount of tracks over the 'ideal' size that workfiles would have to be before its considered for action. Alter as you see fit.

5-you will probabaly also want to change the version number :).



The REXX can be run online through TSO (assuming its in your allocation) eg:

TSO WRKSPCFX DBIA N

or through JCL. I've also attached a sample JCL.



Let me know of any questions.



Cheers

Gabriel



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

Attachments

  • smime.p7s (5.1k)