Hey Jan
Mine was a question - a partially formed idea at best, however:
If you do not use bound packages, this should work, the db2 optimiser will step around that index while it is invalid, at most there could be some queuing on the index until it can be dropped, and them some overhead while db2 establishes a new path using the new index - my experience maxes this as a one time hit of up to a second (or so) on only the first execution of the dynamiic SQL
Now I cannot be quoted on this, but my interpretation of the manual is that, optionally, even for bound programs, the optimiser will recalculate the path.
I really do not know as we have stepped into the land of only dynamic SQL, and I offer only a vague recollection of 'automatic rebind' in the online manual .... somewhere.
I encourage anyone who has this requirement to test and see before they implement any hare-brained ideas I proffer.
Greg
------------------------------
Greg BRUCE
------------------------------
Original Message:
Sent: Mon September 11, 2023 02:37 PM
From: Jan Nelken
Subject: Online index reorganization in Db2 pureScale®
Hi Greg!
How you propose to deal with packages referring to now inaccessible index when implementing your idea of "flipping" indexes?
------------------------------
Jan Nelken
Original Message:
Sent: Thu September 07, 2023 12:34 PM
From: Greg BRUCE
Subject: Online index reorganization in Db2 pureScale®
It is possible/beneficial to create an additional index, with say one extra (small, low cardinality, potentially useful) field, then reorg ( or drop, rebuild) the old index then do this alternately whenever an online reorg is required ?
------------------------------
Greg BRUCE
Original Message:
Sent: Fri July 07, 2023 08:14 PM
From: Youssef Sbai Idrissi
Subject: Online index reorganization in Db2 pureScale®
According to the IBM documentation, online index reorganization is not currently supported in Db2 pureScale environments. However, IBM is working on adding this feature in a future release. There is no specific ETA for when this feature will be available, but it is expected to be released in the next few months.
In the meantime, if your customer needs to reorganize indexes in a Db2 pureScale environment, they can do so offline. To do this, they can use the REORG INDEXES
command with the OFFLINE
option. This will rebuild the indexes without affecting the availability of the database.
------------------------------
Youssef Sbai Idrissi
Software Engineer
Original Message:
Sent: Fri July 07, 2023 07:55 PM
From: Roberto Stradella
Subject: Online index reorganization in Db2 pureScale®
Hello,
Can anyone tell me when the online index reorg will be made available in Db2 pureScale ? I have a Customer using Db2 pureScale that would use the online index reorg as it used in Db2 z/OS. The only information I have found is in the online documentation (last updated 18 April 2023) reporting "Restriction: Online index reorganization is not supported in Db2 pureScale® environments and will fail with SQL1419N".
Thanks in advance. Ciao. Roberto
------------------------------
Roberto Stradella
------------------------------