Much appreciate - Peter for the detailed mail
Regards,
Anguraj Rathinasamy
> On Feb 8, 2019, at 5:33 AM, Peter Vanroose
<[login to unmask email]> wrote:
>
> Anguraj Rathinasamy,
>
> You were asking for details from last week John Campbell
webinar.
> The URL is
https://www.worldofdb2.com/events/jc-greatest-hits-war-stories-and-best-practices-2019-part-2
> Beware that John Campbell is talking about Db2 12; version 11
could be a (slightly) different story ...
>
> Here is a copy/paste from one of his slides (slides 14+15 of
part 2) where John Campbell states that PBG is "over-used":
>
> Overuse of UTS PBG tablespace and MAXPARTS
>
> • Primary driver for the developing UTS PBG tablespace
was the removal of the 64GB limit for classic segmented tablespace
and avoid the disruptive migration to classic partitioned
tablespace
> • Some considerations
> – All indexes are going to be NPIs
> – Limited partition independence for utilities (REORG,
LOAD)
> – Partitioning not used for query parallelism
> – Degraded insert performance (free space search) as the
number of partitions grow
> – If REORG a partition list/range, it may encounter
undetected deadlock between applications and REORG during the
SWITCH phase (i.e. drain and claim in different order)
> – REORG PART will fail for a full UTS PBG partition if
FREEPAGE or PCTFREE are non-zero
> – Setting system parameter REORG_DROP_PBG_PARTS = ENABLE
could lead to operational issues if the number of PARTs are pruned
back
> • No point-in-time recovery prior to the REORG that
prunes partitions
> • Cannot use DSN1COPY to move data between Db2
systems
> • Should not be using UTS PBG as the design default for
all tables (with large number of partitions)
>
> • General recommendations for use of UTS PBG
tablespace
> – Only use UTS PBG tablespace as the alternative and
replacement for classic segmented tablespace
> – A table greater than 60GB in size should be created as
a UTS PBR tablespace
> – Good reasons to limit number of partitions - should
have as few partitions as possible - ideally only 1
> – DSSIZE and SEGSIZE should be consistent with the
target size of the object e.g.
> • Small size object: DSSIZE = 2GB and SEGSIZE = 4
> • Medium size object: DSSIZE = 4GB and SEGSIZE = 32
> • Large size object: DSSIZE = 64GB and SEGSIZE = 64
> – REORG at the table space level unless do not have
sufficient DASD space for sort
> – Setting system parameter REORG_DROP_PBG_PARTS =
DISABLE?
> • If required to prune back the number of partitions
> – Use online system parameter to temporarily enable for
controlled use
> • Better still, in Db2 12, use the DROP_PART YES option
of REORG
>
>
>
> -- Peter Vanroose
> ABIS Training & Consulting,
> Leuven, Belgium.
>
https://www.abis.be/
>
>
> Site Links: View post online View mailing list online Start
new thread via email Unsubscribe from this mailing list Manage your
subscription
>
> This email has been sent to: [login to unmask email]
> ESAi has well-regarded tools for Fast Cloning, Buffer Pool
Tuning, Log Analysis, TDM & more.
> BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the
job done faster & easier than ever.
>
http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service
at:
>
http://www.idug.org/p/cm/ld/fid=2
>