Donna, as long as you need to REORG the entire table space anyway, I'd recommend going to relative page numbering. Once that's done, if you subsequently need to make an individual partition's DSSIZE larger, you just alter that partition's DSSIZE and it's an IMMEDIATE change - the larger DSSIZE is immediately in effect for the partition, with no need to REORG the partition in question.
Robert
------------------------------
Robert Catterall
------------------------------
Original Message:
Sent: Tue September 26, 2023 10:21 AM
From: Donna Stinson
Subject: Reorg 1.1B row table shrlevel reference
Hi Steve - yes the tablespace is type R in systablespace .... UTS PBR. However if I alter the DSSIZE at the tablespace level - the tablespace is put in advisory reorg pending - and I still need to reorg to materialize that new DSSIZE. But this may avoid the pagenum relative alter - I'm attempting to alter as little as possible while still enlarging the partition size.
------------------------------
Donna Stinson
Original Message:
Sent: Tue September 26, 2023 09:15 AM
From: Steve Johnson
Subject: Reorg 1.1B row table shrlevel reference
Hi Martin,
Good catch thank you. However with UTS PBR she could alter the DSSIZE at the tablespace level (it would apply to all partitions) and avoid a reorg. That might give some immediate relief.
Thanks again,
Steve
------------------------------
Steve Johnson
Original Message:
Sent: Tue September 26, 2023 08:53 AM
From: Martin Ålund
Subject: Reorg 1.1B row table shrlevel reference
Hi Steve
You are not entirely right. To change the DSSIZE for a single partition the tablespace has to be defined with PAGENUM RELATIVE. It is not enough that it is UTS PBR. On the other hand if it has PAGENUM RELATIVE you can increase the DSSIZE very smoothly with an ALTER. That saved me lots of work last night.
Regards Martin
------------------------------
Martin Ålund
Original Message:
Sent: Tue September 26, 2023 08:35 AM
From: Steve Johnson
Subject: Reorg 1.1B row table shrlevel reference
Hi Donna,
If the tablespace is UTS Partition by Range (PBR), you can alter the DSSIZE of an individual partition and do not have to reorg. Since you are not doing that I assume your TS is "old-school partitioned". If that is the case, I highly recommend converting it to UTS PBR at the same time so you can avoid another reorg of the entire TS later to do that conversion.
We have reorged a few very large tablespaces using SHRLEVEL CHANGE and here are a couple things to keep in mind:
- How many indexes do you have on the table? Ensure you have enough sort space to rebuild them during the reorg.
- If you don't have enough sort space, consider dropping non-unique indexes before the reorg, and recreating and rebuilding them with SHRLEVEL CHANGE after the reorg, and of course rebinding impacted packages.
Good luck!
------------------------------
Steve Johnson
Original Message:
Sent: Mon September 25, 2023 03:58 PM
From: Donna Stinson
Subject: Reorg 1.1B row table shrlevel reference
Hello IBM Community ! I have a 1.1B row table with 21 partitions. Each partition is 8 gig and some are dangerously close to hitting that 8 gig limit.
Last month one partition hit the limit and I altered the limit keys to fix that issue, however it's only a matter of days/weeks before this happens again.
I want to alter the dssize at the partition level to 16 gig, however firstly I have to alter to turn on pagenum relative at the tablespace level.
This will force me to reorg the entire tablespace. If I reorg shrlevel none – and it fails – this will put me in a recovery scenario – I would like to avoid that if at all possible.
Therefore after the pagenum relative alter – I'd like to reorg shrlevel reference – after I ensure the SMS Pool has enough dasd available for the shadow table.
Just wondering if anyone sees any issues with this approach, and have you ever reorged a 1.1B row table shrlevel reference?
It's more the reorg that is giving me pause. �� Thanks for any advice !
______________________________________________
Donna Stinson
Sr. DBA
Payment & Registration I&IT Solutions Branch
Health Services Cluster
613-539-1722