IBM Data Management Community Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems. Join / Log in
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 !
Payment & Registration I&IT Solutions Branch
Health Services Cluster
We have a few tablespaces that big and we have reorged them with SHRLEVEL REFERENCE and SHRLEVEL CHANGE. You cannot use SHRLEVEL NONE to materialize a pending definition change, for example converting to pagenum relative. I would never recommend anyone to use REORG SHRLEVEL NONE by the way. You can do a partlevel reorg, but you have to reorg all partitions in the same reorg statement. That may make it a little bit easier. Make sure you use a TEMPLATE for the inline image copies with &PART specified.
REORG TABLESPACE abc PARTLEVEL.
Regards Martin Ålund
Db2 Systems Programmer
This spring we did the same with a 33B million row table. It was a huge undertaking. My colleague will present our experiences at the Prague IDUG.
But my tips are
Leo de Jong
could you please let me know the name of your colleague or the name of the presentation?
I would like to attend if possible.
Add SORTDATA NO along with RECLUSTER NO option to the online REORG as that will help with the elapsed time along with less resources used for the job.
Hope the Reorg went ok and if not yet done all the best.
------------------------------Leo de JongOriginal Message:Sent: Mon September 25, 2023 03:58 PMFrom: Donna StinsonSubject: Reorg 1.1B row table shrlevel reference
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:
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.