Db2 12 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces

Aurora Emanuela Dellanno

Db2 12 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces

Hey y'all,

 

can someone please point me to the page in the manuals where it says that 32k tablespaces in the WORK database must be segmented and cannot be UTS PBG?

 

In today's chapter of the WORKDB saga, I dropped/created all tablespaces in our WORKDB (in our sandbox AND in our TEST environment, which hosts some TCM databases, for example) to make them UTS PBG, and then this, that and the other stopped working with abends in object type 100 and 200 - the datasets couldn't get extents.

here is one error from one of our apps:

 

SELECT * FROM T9999.ISS3RESSOURCEN WHERE SYS_NAME = 'SYSCAT' ORDER by RES_ID ;

SQLERROR ON   SELECT   COMMAND, OPEN     FUNCTION $$$                         

 RESULT OF SQL STATEMENT:    $$$                                              

 DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN         

          UNAVAILABLE RESOURCE. REASON 00C90084, TYPE OF RESOURCE 00000100, AND

          RESOURCE NAME DB2-MANAGED SPACE WITHOUT SECONDARY ALLOCATION OR US  

 DSNT418I SQLSTATE   = 57011 SQLSTATE RETURN CODE                              

 DSNT415I SQLERRP    = DSNXRSOR SQL PROCEDURE DETECTING ERROR                 

 DSNT416I SQLERRD    = -115  13172746  0  13231826  -959250432  0 SQL         

          DIAGNOSTIC INFORMATION                                               

 DSNT416I SQLERRD    = X'FFFFFF8D'  X'00C9000A'  X'00000000'  X'00C9E6D2'     

          X'C6D30000'  X'00000000' SQL DIAGNOSTIC INFORMATION                 

 

 

 

 

So I tried dropping the only one now defined with SECQTY -1 and recreating that one only as segmented, still no soap.

 

It only works if ALL the 32k tablespaces are segmented, independently of the SECQTY definition (the 4k don't seem to bother it) - so it must be documented somewhere and I have forgotten it, but I'd like to find out where... because when I look for it I find instead completely conflicting information like in here:

 

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_createtablespace.html

 

TIA, have a good weekend.

 

 

Aurora

 

PS stay safe and healthy

NB black lives matter

 

Michael Hannan

RE: Db2 12 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces
(in response to Aurora Emanuela Dellanno)

Aurora,

Not sure if you were making a rhetorical question. From IBMWeb:
"Table spaces in a work file database: (FL 504)A table space in a work file database, the table space is always a partition-by-growth table space. <

The following restrictions apply to table spaces created in a work file database:

  • They can be created for another member only if both the executing Db2 subsystem and the other member can access the work file data sets. That is required whether the data sets are user-managed or in a Db2 storage group.
  • They cannot use 8 KB or 16 KB page sizes. (The buffer pool in which you define the table space determines the page size. For example, a table space that is defined in a 4 KB buffer pool has 4 KB page sizes.)
  • When you create a table space in a work file database, the following clauses are not allowed:
    • CCSID
    • COMPRESS
    • CLOSE YES
    • DEFINE NO
    • FREEPAGE
    • GBPCACHE
    • LARGE
    • LOCKPART
    • LOCKSIZE
    • LOGGED
    • MAXROWS
    • MEMBER CLUSTER
    • NOT LOGGED
    •  PAGENUM 
    • PCTFREE
    • SEGSIZE (SEGSIZE 16 is always used)
    • TRACKMOD
      "

At V11:

Table spaces in a work file database:If MAXPARTITIONS is specified for a table space in a work file database, the table space is a partition-by-growth table space.If MAXPARTITIONS is not specified for a table space in a work file database, the table space is a segmented (non-UTS) table space.

etc.

 

Michael Hannan,
DB2 Application Performance Specialist

Aurora Emanuela Dellanno

RE: Db2 12 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces
(in response to Michael Hannan)

Hi Michael,

 

Happy Monday.

 

well no, my question was not exactly rhetorical, I am truly puzzled (though the tone was meant to be ironic) - I wanted to have all my work tablespaces as UTS PBG, so I did a drop/create, and some of my tools - for example the IBM DB2 Admin Tool - no longer work, because they incur the unavailable resource error I mentioned in my earlier post.

 

I had already double checked the definition on the IBM website (in fact, what you quoted), and therefore it makes no sense that it should not, but I add to my question: can I not have a mixture of UTS PBG AND segmented tablespaces in one work database?

 

I don't get any Db2 error when I create them, but some of my tools meet a -904 with either:

 

1. all work tablespaces in one work database defined as UTS PBG (Db2-managed); or

2. 3/4 work tablespaces as UTS PBG, one as segmented

 

The only way they work as expected is to define all the tablespaces in the work database as segmented.

 

I have set WFDBSEP=YES, by the way.

 

As for the definition, the manual tells me it should work:

 

"Db2 directs declared global temporary table work only to Db2-managed (STOGROUP) work file table spaces that are defined as partition-by-growth (regardless of the SECQTY setting) or segmented (non-universal) table spaces with a non-zero SECQTY setting. Db2 directs work file work only to Db2-managed table spaces that are defined as segmented (non-universal) with a zero SECQTY value or user-managed table spaces. If no table space with the preferred allocation type is available, Db2 issues an error message, a negative SQLCODE, or both".

 

 I think I ought to open a PMR (Db2? DB2 Admin Tool?) but before doing that, I was asking if and what I have missed.

 

Thanks.

 

Aurora

 

PS stay safe and healthy

NB black lives matter

Chris Tee

Db2 12 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces
(in response to Aurora Emanuela Dellanno)
Aurora

Does your segmented work tablespace have SECQTY 0?

________________________________
From: Aurora Emanuela Dellanno <[login to unmask email]>
Sent: 13 July 2020 11:03
To: [login to unmask email] <[login to unmask email]>
Subject: [DB2-L] - RE: Db2 12 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces


Hi Michael,



Happy Monday.



well no, my question was not exactly rhetorical, I am truly puzzled (though the tone was meant to be ironic) - I wanted to have all my work tablespaces as UTS PBG, so I did a drop/create, and some of my tools - for example the IBM DB2 Admin Tool - no longer work, because they incur the unavailable resource error I mentioned in my earlier post.



I had already double checked the definition on the IBM website (in fact, what you quoted), and therefore it makes no sense that it should not, but I add to my question: can I not have a mixture of UTS PBG AND segmented tablespaces in one work database?



I don't get any Db2 error when I create them, but some of my tools meet a -904 with either:



1. all work tablespaces in one work database defined as UTS PBG (Db2-managed); or

2. 3/4 work tablespaces as UTS PBG, one as segmented



The only way they work as expected is to define all the tablespaces in the work database as segmented.



I have set WFDBSEP=YES, by the way.



As for the definition, the manual tells me it should work:



"Db2 directs declared global temporary table work only to Db2-managed (STOGROUP) work file table spaces that are defined as partition-by-growth (regardless of the SECQTY setting) or segmented (non-universal) table spaces with a non-zero SECQTY setting. Db2 directs work file work only to Db2-managed table spaces that are defined as segmented (non-universal) with a zero SECQTY value or user-managed table spaces. If no table space with the preferred allocation type is available, Db2 issues an error message, a negative SQLCODE, or both".



I think I ought to open a PMR (Db2? DB2 Admin Tool?) but before doing that, I was asking if and what I have missed.



Thanks.



Aurora



PS stay safe and healthy

NB black lives matter

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

Andy Smith

RE: Db2 12 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces
(in response to Aurora Emanuela Dellanno)

Aurora

<<

some of my tools meet a -904 with either:
1. all work tablespaces in one work database defined as UTS PBG (Db2-managed); or
2. 3/4 work tablespaces as UTS PBG, one as segmented
>>

If you are running with WFDBSEP=YES then general spannable sort work requires Classic Segmented workfiles with SECQTY = 0 (or user-managed workfiles).
So I would expect you to encounter -904's in case 1. above, because only UTS PBG were available.
For case 2. perhaps the one segmented was just not enough, hence you still got the 00C90084

I would recommend you have some 4K and 32K Db2-managed Classic Segmented with SECQTY = 0 (for general spannable sort work)
And then some 4k and 32K UTS PBG (for non-spannable DGTT work).
This should be a safe setup for WFDBSEP=YES

Aurora Emanuela Dellanno

RE: Db2 12 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces
(in response to Andy Smith)

hi Chris and Andy,

 

WFDBSEP=YES - so actually that clears up error 1 for me (work file work needs simple segmented/user managed, silly me), but it still does not clean up item 2, since I have 3 "classic segmented" tablespaces defined with SECQTY 0 and a single one with SECQTY = -1, so as far as GDTT are concerned it's the same difference and I still have only one tablespace going into extents, but now everything works with the same type of test (running the DDL command with the DB2 Admin tool, for example)...

 

If the problem was needing more room to span, it would still fail.

 

Thanks.

 

Aurora

Andy Smith

RE: Db2 12 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces
(in response to Aurora Emanuela Dellanno)

Hi Aurora

It's not clear to me if you still have the -904/00C90084 problem...?

Some records which might be of interest for you to understand more about the use of workfiles in your test scenario...

QISTDGTTCTO (column WORKFILE_CUR_DGTT in OMPE) and QISTWFCTO (WORKFILE_CUR_WF)

QISTW4K (WORKFILE_STOR_4K) and QISTW32K (WORKFILE_STOR_32K)

QISTWFP1 (WORKFILE_UNAV_4K) and QISTWFP2 (WORKFILE_UNAV_32K)

I've used the above recently during a similar problem to yours.

Aurora Emanuela Dellanno

RE: Db2 12 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces
(in response to Andy Smith)

Andy,

 

I solved my immediate problem by dropping all the tablespaces in my work DB and creating them as segmented - this works (and it's what I had before I started trying to change to UTS).

 

I am still not satisfied as to why I can't use a mixture of UTS and segmented though.

 

Thanks.

 

Aurora

Andy Smith

RE: Db2 12 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces
(in response to Aurora Emanuela Dellanno)

You should be able to use a mixture, and with WFDBSEP=YES it should be encouraged. 

I think those fields will help you to work out what is going on.

Michael Hannan

RE: Db2 12 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces
(in response to Andy Smith)

The Rules seem to be rather complex, so this makes vital reading:

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/inst/src/tpc/db2z_ipf_wfdbsep.html

One has to suspect some sort of weakness being documented as a feature. Ha ha.

 

Michael Hannan,
DB2 Application Performance Specialist

James Campbell

Db2 12 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces
(in response to Aurora Emanuela Dellanno)
One slight thing to be aware of with workfile UTS PBG - they can only be used for DGTTs.

https://www.worldofdb2.com/events/session-2-dos-and-dont-s-about-continuous-delivery-tec
hnical-road
at 30:15

I presume the reason is related to the fact that UTS can hold only one table - and hence one
DGTT - while most workfile tablespaces have to hold a swarm of mini result tables.

James Campbell


On 10 Jul 2020 at 6:48, Aurora Emanuela Dellanno wrote:

> Hey y'all,
>  
> can someone please point me to the page in the manuals where it says that 32k tablespaces in the WORK database must be segmented and cannot be UTS PBG?
>  
> In today's chapter of the WORKDB saga, I dropped/created all tablespaces in our WORKDB (in our sandbox AND in our TEST environment, which hosts some TCM databases, for example) to make them UTS PBG, and then this, that and the other stopped working with abends in object type 100 and 200 - the datasets couldn't get extents.
> here is one error from one of our apps:
>  
> ...
> SELECT * FROM T9999.ISS3RESSOURCEN WHERE SYS_NAME = 'SYSCAT' ORDER by RES_ID ;
> SQLERROR ON   SELECT   COMMAND, OPEN     FUNCTION $$$                         
>  RESULT OF SQL STATEMENT:    $$$                                              
>  DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN         
>           UNAVAILABLE RESOURCE. REASON 00C90084, TYPE OF RESOURCE 00000100, AND
>           RESOURCE NAME DB2-MANAGED SPACE WITHOUT SECONDARY ALLOCATION OR US  
>  DSNT418I SQLSTATE   = 57011 SQLSTATE RETURN CODE                              
>  DSNT415I SQLERRP    = DSNXRSOR SQL PROCEDURE DETECTING ERROR                 
>  DSNT416I SQLERRD    = -115  13172746  0  13231826  -959250432  0 SQL         
>           DIAGNOSTIC INFORMATION                                               
>  DSNT416I SQLERRD    = X'FFFFFF8D'  X'00C9000A'  X'00000000'  X'00C9E6D2'     
>           X'C6D30000'  X'00000000' SQL DIAGNOSTIC INFORMATION                 
>  
>  
>  
>  
> So I tried dropping the only one now defined with SECQTY -1 and recreating that one only as segmented, still no soap.
>  
> It only works if ALL the 32k tablespaces are segmented, independently of the SECQTY definition (the 4k don't seem to bother it) - so it must be documented somewhere and I have forgotten it, but I'd like to find out where... because when I look for it I find instead completely conflicting information like in here:
>  
> https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_createtablespace.html
>  
> TIA, have a good weekend.
>  
>  
> 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 FL500 code level V12R1M506 - the never ending saga of 32K work tablespaces
(in response to James Campbell)

thanks again Andy, James and Michael (in alpha order).

 

Strange as it seems, I had read all the manuals and documentation, and I still think it ought to have worked as I tried to configure it, though maybe, as Michael says, weaknesses are badly documented as features.

 

Well, in order to check those fields to go with -904 errors, I would have to take some time recreating the situation that was and going through the motions one by one in our sandbox and, frankly, I have no time nor fancy to do that (although I am sure it would be interesting) since we're bringing our last systems to Db2 12 next batch window.

 

We have decided that, since we absolutely need to separate the work files (hence WFDBSEP=YES) and for that we need a segmented TBs, we will face the music once again when IBM fix that, in order to get rid of "classic" segmented tablespaces altogether - we're good like this now.

 

Thanks again.

 

Aurora

 

PS stay safe and healthy
NB black lives matter