Db2 11 for z/OS PCTFREE_UPD_CALC

Daniel Luksetich

Db2 11 for z/OS PCTFREE_UPD_CALC
Hello,

I am seeing PCTFREE_UPD_CALC values in SYSIBM.SYSTABLEPART as high as 97%!
This seems unreasonable. Anyone have a thought on this?

Cheers,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

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

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Application Developer - DB2 11 for z/OS

IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and
Windows



Bill Gallagher

Db2 11 for z/OS PCTFREE_UPD_CALC
(in response to Daniel Luksetich)
Sounds like an outlier.

Anything unusual about the table defined in that TS? Very low cardinality, lots of VARCHAR columns, etc?

Does SYTABLESPACESTATS show high numbers for the TS for REORGNEARINDREF and REORGFARINDREF with respect to TOTALROWS?

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

[cid:[login to unmask email]

From: Daniel L Luksetich <[login to unmask email]>
Sent: Wednesday, December 12, 2018 2:31 PM
To: [login to unmask email]
Subject: [DB2-L] - Db2 11 for z/OS PCTFREE_UPD_CALC

Hello,
I am seeing PCTFREE_UPD_CALC values in SYSIBM.SYSTABLEPART as high as 97%! This seems unreasonable. Anyone have a thought on this?
Cheers,
Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Past-Chairman
IDUG DB2-L Administrator
IBM Certified Database Adminstrator - DB2 11 DBA for z/OS
IBM Certified System Administrator - DB2 11 for z/OS
IBM Certified Application Developer - DB2 11 for z/OS
IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and Windows


-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201
Attachments

  • image001.png (5.6k)

Joe Geller

RE: Db2 11 for z/OS PCTFREE_UPD_CALC
(in response to Bill Gallagher)

Dan,

I just looked up PCTFREE FOR UPDATE in the V11 Technical Overview.  You can specify a value of the CREATE Tablespace, use a default from a Zparm or specify autonomic calculation (by using -1 as the value).  For autonomics, RTS is keeping track of the growth of rows due to updates and Db2 will adjust the value based on that.

I can picture a scenario where you load/insert a large % of the rows with an empty very long Varchar (or more than one varchar), then subsequently Update all of the rows and fill in that column with a lengthy value.  It is quite possible that 97% of the space is now used by those updates, so it would actually be good to leave that much freespace on the Inserts/Load.

Joe

In Reply to Bill Gallagher:

Sounds like an outlier.

Anything unusual about the table defined in that TS? Very low cardinality, lots of VARCHAR columns, etc?

Does SYTABLESPACESTATS show high numbers for the TS for REORGNEARINDREF and REORGFARINDREF with respect to TOTALROWS?

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

[cid:[login to unmask email]

From: Daniel L Luksetich <[login to unmask email]>
Sent: Wednesday, December 12, 2018 2:31 PM
To: [login to unmask email]
Subject: [DB2-L] - Db2 11 for z/OS PCTFREE_UPD_CALC

Hello,
I am seeing PCTFREE_UPD_CALC values in SYSIBM.SYSTABLEPART as high as 97%! This seems unreasonable. Anyone have a thought on this?
Cheers,
Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Past-Chairman
IDUG DB2-L Administrator
IBM Certified Database Adminstrator - DB2 11 DBA for z/OS
IBM Certified System Administrator - DB2 11 for z/OS
IBM Certified Application Developer - DB2 11 for z/OS
IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and Windows


-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Walter Jani&#223;en

AW: Db2 11 for z/OS PCTFREE_UPD_CALC
(in response to Joe Geller)
Hi

The problem is that this space can’t be used for inserts, even if the insert would fail,this space is not used.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Joe Geller <[login to unmask email]>
Gesendet: Mittwoch, 12. Dezember 2018 22:47
An: [login to unmask email]
Betreff: [DB2-L] - RE: Db2 11 for z/OS PCTFREE_UPD_CALC


Dan,

I just looked up PCTFREE FOR UPDATE in the V11 Technical Overview. You can specify a value of the CREATE Tablespace, use a default from a Zparm or specify autonomic calculation (by using -1 as the value). For autonomics, RTS is keeping track of the growth of rows due to updates and Db2 will adjust the value based on that.

I can picture a scenario where you load/insert a large % of the rows with an empty very long Varchar (or more than one varchar), then subsequently Update all of the rows and fill in that column with a lengthy value. It is quite possible that 97% of the space is now used by those updates, so it would actually be good to leave that much freespace on the Inserts/Load.

Joe

In Reply to Bill Gallagher:
Sounds like an outlier.

Anything unusual about the table defined in that TS? Very low cardinality, lots of VARCHAR columns, etc?

Does SYTABLESPACESTATS show high numbers for the TS for REORGNEARINDREF and REORGFARINDREF with respect to TOTALROWS?

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

[cid:[login to unmask email]

From: Daniel L Luksetich
Sent: Wednesday, December 12, 2018 2:31 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 11 for z/OS PCTFREE_UPD_CALC

Hello,
I am seeing PCTFREE_UPD_CALC values in SYSIBM.SYSTABLEPART as high as 97%! This seems unreasonable. Anyone have a thought on this?
Cheers,
Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Past-Chairman
IDUG DB2-L Administrator
IBM Certified Database Adminstrator - DB2 11 DBA for z/OS
IBM Certified System Administrator - DB2 11 for z/OS
IBM Certified Application Developer - DB2 11 for z/OS
IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and Windows


-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

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

  • image001.png (2.6k)

Daniel Luksetich

AW: Db2 11 for z/OS PCTFREE_UPD_CALC
(in response to Walter Janißen)
I’ve starting to use PCTFREE FOR UPDATE with great success, and recommend everyone review their settings to see if they can take advantage of this. However, how is 97% PCTFREE FOR UPDATE useful for anything, especially when you have billions of row to deal with? What would happen if you set PCTFREE FOR UPDATE to -1? Will Db2 in that case use 97%?



Anyway, I’m going to use the number as a guideline. If it’s big I will set PCTFREE FOR UPDATE to something reasonable (10% or so), if small leave it at zero. Always monitor.



Cheers,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Walter Janißen <[login to unmask email]>
Sent: Thursday, December 13, 2018 3:21 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: Db2 11 for z/OS PCTFREE_UPD_CALC



Hi



The problem is that this space can’t be used for inserts, even if the insert would fail,this space is not used.



Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
<mailto:[login to unmask email]> [login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996



Von: Joe Geller <[login to unmask email] <mailto:[login to unmask email]> >
Gesendet: Mittwoch, 12. Dezember 2018 22:47
An: [login to unmask email] <mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: Db2 11 for z/OS PCTFREE_UPD_CALC



Dan,

I just looked up PCTFREE FOR UPDATE in the V11 Technical Overview. You can specify a value of the CREATE Tablespace, use a default from a Zparm or specify autonomic calculation (by using -1 as the value). For autonomics, RTS is keeping track of the growth of rows due to updates and Db2 will adjust the value based on that.

I can picture a scenario where you load/insert a large % of the rows with an empty very long Varchar (or more than one varchar), then subsequently Update all of the rows and fill in that column with a lengthy value. It is quite possible that 97% of the space is now used by those updates, so it would actually be good to leave that much freespace on the Inserts/Load.

Joe

In Reply to Bill Gallagher:

Sounds like an outlier.

Anything unusual about the table defined in that TS? Very low cardinality, lots of VARCHAR columns, etc?

Does SYTABLESPACESTATS show high numbers for the TS for REORGNEARINDREF and REORGFARINDREF with respect to TOTALROWS?

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

[cid:[login to unmask email]

From: Daniel L Luksetich
Sent: Wednesday, December 12, 2018 2:31 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 11 for z/OS PCTFREE_UPD_CALC

Hello,
I am seeing PCTFREE_UPD_CALC values in SYSIBM.SYSTABLEPART as high as 97%! This seems unreasonable. Anyone have a thought on this?
Cheers,
Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Past-Chairman
IDUG DB2-L Administrator
IBM Certified Database Adminstrator - DB2 11 DBA for z/OS
IBM Certified System Administrator - DB2 11 for z/OS
IBM Certified Application Developer - DB2 11 for z/OS
IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and Windows


-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201



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



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

Bruce Williamson

RE: Db2 11 for z/OS PCTFREE_UPD_CALC
(in response to Walter Janißen)

I use -1 and trust RTS to have the most relevant info and then monitor for issues. I've never personally seen 97% so can't comment on that, most of what I see is around 2% up to 10% but I think Joe's comments regarding large VARCHARs makes sense.

With regard to inserts Walter, you can always use PCTFREE and FREEPAGE to ensure inserts can find space

Cheers
Bruce

 

P.S. Want to make a difference to DB2 but don't know how? Join the RFE Community?

P.P.S. While you're at it why not join the "All DB2 for z/OS" group and vote on community proposals

Michael Hannan

RE: Db2 11 for z/OS PCTFREE_UPD_CALC
(in response to Bruce Williamson)



In Reply to Bruce Williamson:

With regard to inserts Walter, you can always use PCTFREE and FREEPAGE to ensure inserts can find space.  

I believe you misunderstood Walter's point, since an Insert would only fail, if the distributed freespace (excluding PCTFREE FOR UPDATE) is used up, and extend was not possible. Should be a rare event indeed. Usually it performs very poorly in preference to failing during the space search methods. PCTFREE and FREEPAGE only preserve space at Reorg time and not forever. Inserts can perform extremely badly when on extended space search, where Insert is attempted in some pages, due to granularity of spacemap not quite clear enough, but actual freespace is not quite big enough for possibly unusually large records. FREEPAGE could be useful for these cases (if not all used up), but still causes rows to be unclustered. Insert on a page attempt can also be failed if conditional page lock not obtainable immediately, and DB2 moves on to try elsewhere.

I have seen very poor Insert performance with large average data Getpages per Insert. Clustering scheme may not be helping, if Inserts all go to much the same place. Reorg to put back some reasonable freespace percentages did help a lot (for a while at least or reorg regularly). Append, Member Cluster, and Algorithm 2 could also help a lot, to avoid fruitless space searches and lock contention.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Bruce Williamson

RE: Db2 11 for z/OS PCTFREE_UPD_CALC
(in response to Michael Hannan)

Not at all, I was merely providing a practical workaround for the potential INSERT problem. Is there an alternative MC00 notwithstanding?

Cheers
Bruce

P.S. Want to make a difference to DB2 but don't know how? Join the RFE Community?

P.P.S. While you're at it why not join the "All DB2 for z/OS" group and vote on community proposals