Currently whenever we create an index on a table we see significant number of lock timeouts for the applications. We are running a pureScale cluster and not able to create Indexes ONLINE.
We use DB2 LUW pureScale as a customer facing environment, every time we create an index there will be an interruption to the services due to the S lock which is incompatible with concurrent writes from other applications. For us this is unacceptable as we don't have any work around.
Our non-customer facing environments use MS SQL server and they support ONLINE Index creation in a couple of modes, ONLINE mode and ONLINE RESTARTABLE mode. We are losing the argument to Microsoft SQL server and even postgreSQL (open source) supports ONLINE Index creation when you specify CONCURRENTLY option. We need this option in DB2 LUW as soon as possible as in this AGILE/Devops age creating indexes and deploying services on the fly is becoming more and more common.
We don't mind if the locking needed to create index is at a couple of points like MS SQL server does, when it initially starts the index and when it completes the index but they are for very short duration.
Our services are getting locked out for the most part of the index creation on DB2 LUW tables especially when they are large. DB2 LUW is getting a bad rap because of this and losing its competitive edge.
|Who would benefit from this IDEA?||DB2 LUW Community and IBM|
How should it work?
Proposed solution: Please allow "ONLINE" option for "CREATE INDEX" statement and preferably with RESTARTABLE option i.e. it should be pausable, stoppable and restartable like INPLACE reorg for e.g. IBM can take a look at MS SQL server create index options and also postgreSQL create index options.
Impact: Not having a completely ONLINE index build for a pureScale environment is not acceptable and our business users are increasingly getting frustrated with this issue.
Work Around: There is no work around other than telling business owners and application users to expect lock time outs during Index creation.
|Priority Justification||No work around exists and causing significant issues to the applications while creating indexes on large tables|
|Customer Name||ULINE INC|
NOTICE TO EU RESIDENTS: per EU Data Protection Policy, if you wish to remove your personal information from the IBM ideas portal, please login to the ideas portal using your previously registered information then change your email to "firstname.lastname@example.org" and first name to "anonymous" and last name to "anonymous". This will ensure that IBM will not send any emails to you about all idea submissions