index compression on Version 12.10
User asked whether CREATE INDEX for compressed indexes locks the entire table. Standard CREATE INDEX applies exclusive locks. However, the ONLINE keyword allows index creation with only brief catalog locks during operation, available in Enterprise/Advanced editions. This allows compressed indexes to be created without blocking business operations.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
If i want to compress the detached index, Do the create index command lock the whole user table ? it will affect the business. thanks.
Sorry, I thougth you was asking about index creation. Manual says nothing about it. But I think the engine will only require a share intent lock, not an exclusive one during the whole operation. Some friend could confirm that, please? Thanks a lot. Best regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Informix on Cloud - Database Administrator - 2017 IBM dashDB Managed Service for Analytics and Transactions - 2017 DB2 Advanced DBA - v10.5 for LUW IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero Informix independent consultant ________________________________ De: Alexandre Marini <alexandre_marini@hotmail.com> Enviado: sexta-feira, 23 de março de 2018 15:06 Para: CHUAN LU; ids@iiug.org Assunto: RE: index compression on Version 12.10 [40929] Hi, Chuan. Yes, from the manual page: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0401.htm "When you issue the CREATE INDEX statement, the table is locked in exclusive mode. If another process is using the table, CREATE INDEX returns an error. (For an exception, however, see The ONLINE keyword of CREATE INDEX<https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc /ids_sqs_0441.htm?view=kc#ids_sqs_0441>.)" That's why online index creation is an advanced feature, only available at EE/AE editions. HTH Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Informix on Cloud - Database Administrator - 2017 IBM dashDB Managed Service for Analytics and Transactions - 2017 DB2 Advanced DBA - v10.5 for LUW IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero Informix independent consultant ________________________________ De: ids-bounces@iiug.org <ids-bounces@iiug.org> em nome de CHUAN LU <luchuan@cn.ibm.com> Enviado: sexta-feira, 23 de março de 2018 00:03 Para: ids@iiug.org Assunto: index compression on Version 12.10 [40929] If i want to compress the detached index, Do the create index command lock the whole user table ? it will affect the business. thanks. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi, Chuan. Yes, from the manual page: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0401.htm "When you issue the CREATE INDEX statement, the table is locked in exclusive mode. If another process is using the table, CREATE INDEX returns an error. (For an exception, however, see The ONLINE keyword of CREATE INDEX<https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc /ids_sqs_0441.htm?view=kc#ids_sqs_0441>.)" That's why online index creation is an advanced feature, only available at EE/AE editions. HTH Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Informix on Cloud - Database Administrator - 2017 IBM dashDB Managed Service for Analytics and Transactions - 2017 DB2 Advanced DBA - v10.5 for LUW IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero Informix independent consultant ________________________________ De: ids-bounces@iiug.org <ids-bounces@iiug.org> em nome de CHUAN LU <luchuan@cn.ibm.com> Enviado: sexta-feira, 23 de março de 2018 00:03 Para: ids@iiug.org Assunto: index compression on Version 12.10 [40929] If i want to compress the detached index, Do the create index command lock the whole user table ? it will affect the business. thanks. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0401.htm An index can be created ONLINE and COMPRESSED. https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0441.htm#ids_sqs_0441 "the database server briefly locks the table while updating the system catalog with information about the new index." Regards, David. Regareds, David. > On 23 March 2018 at 18:11 Alexandre Marini <alexandre_marini@hotmail.com> wrote: > > > Sorry, I thougth you was asking about index creation. > Manual says nothing about it. > But I think the engine will only require a share intent lock, not an exclusive > one during the whole operation. > > Some friend could confirm that, please? > Thanks a lot. > > Best regards. > > Alexandre Marini > IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 > IBM Informix on Cloud - Database Administrator - 2017 > IBM dashDB Managed Service for Analytics and Transactions - 2017 > DB2 Advanced DBA - v10.5 for LUW > IBM Information Management Informix Technical Professional > IBM Certified Developer - Informix Genero > Informix independent consultant > ________________________________ > De: Alexandre Marini <alexandre_marini@hotmail.com> > Enviado: sexta-feira, 23 de março de 2018 15:06 > Para: CHUAN LU; ids@iiug.org > Assunto: RE: index compression on Version 12.10 [40929] > > Hi, Chuan. > Yes, from the manual page: > > https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0401.htm > > "When you issue the CREATE INDEX statement, the table is locked in exclusive > mode. If another process is using the table, CREATE INDEX returns an error. > (For an exception, however, see The ONLINE keyword of CREATE > INDEX<https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc /ids_sqs_0441.htm?view=kc#ids_sqs_0441>.)" > > That's why online index creation is an advanced feature, only available at > EE/AE editions. > > HTH > Regards. > > Alexandre Marini > IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 > IBM Informix on Cloud - Database Administrator - 2017 > IBM dashDB Managed Service for Analytics and Transactions - 2017 > DB2 Advanced DBA - v10.5 for LUW > IBM Information Management Informix Technical Professional > IBM Certified Developer - Informix Genero > Informix independent consultant > ________________________________ > De: ids-bounces@iiug.org <ids-bounces@iiug.org> em nome de CHUAN LU > <luchuan@cn.ibm.com> > Enviado: sexta-feira, 23 de março de 2018 00:03 > Para: ids@iiug.org > Assunto: index compression on Version 12.10 [40929] > > If i want to compress the detached index, Do the create index command lock the > whole user table ? it will affect the business. > thanks. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Thanks David, so only on advanced and enterprise editions. Best regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Informix on Cloud - Database Administrator - 2017 IBM dashDB Managed Service for Analytics and Transactions - 2017 DB2 Advanced DBA - v10.5 for LUW IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero Informix independent consultant ________________________________ De: david@smooth1.co.uk <david@smooth1.co.uk> Enviado: sexta-feira, 23 de março de 2018 15:24 Para: ids@iiug.org; Alexandre Marini Assunto: RE: index compression on Version 12.10 [40935] https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0401.htm An index can be created ONLINE and COMPRESSED. https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0441.htm#ids_sqs_0441 "the database server briefly locks the table while updating the system catalog with information about the new index." Regards, David. Regareds, David. > On 23 March 2018 at 18:11 Alexandre Marini <alexandre_marini@hotmail.com> wrote: > > > Sorry, I thougth you was asking about index creation. > Manual says nothing about it. > But I think the engine will only require a share intent lock, not an exclusive > one during the whole operation. > > Some friend could confirm that, please? > Thanks a lot. > > Best regards. > > Alexandre Marini > IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 > IBM Informix on Cloud - Database Administrator - 2017 > IBM dashDB Managed Service for Analytics and Transactions - 2017 > DB2 Advanced DBA - v10.5 for LUW > IBM Information Management Informix Technical Professional > IBM Certified Developer - Informix Genero > Informix independent consultant > ________________________________ > De: Alexandre Marini <alexandre_marini@hotmail.com> > Enviado: sexta-feira, 23 de março de 2018 15:06 > Para: CHUAN LU; ids@iiug.org > Assunto: RE: index compression on Version 12.10 [40929] > > Hi, Chuan. > Yes, from the manual page: > > https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0401.htm > > "When you issue the CREATE INDEX statement, the table is locked in exclusive > mode. If another process is using the table, CREATE INDEX returns an error. > (For an exception, however, see The ONLINE keyword of CREATE > INDEX<https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc /ids_sqs_0441.htm?view=kc#ids_sqs_0441>.)" > > That's why online index creation is an advanced feature, only available at > EE/AE editions. > > HTH > Regards. > > Alexandre Marini > IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 > IBM Informix on Cloud - Database Administrator - 2017 > IBM dashDB Managed Service for Analytics and Transactions - 2017 > DB2 Advanced DBA - v10.5 for LUW > IBM Information Management Informix Technical Professional > IBM Certified Developer - Informix Genero > Informix independent consultant > ________________________________ > De: ids-bounces@iiug.org <ids-bounces@iiug.org> em nome de CHUAN LU > <luchuan@cn.ibm.com> > Enviado: sexta-feira, 23 de março de 2018 00:03 > Para: ids@iiug.org > Assunto: index compression on Version 12.10 [40929] > > If i want to compress the detached index, Do the create index command lock the > whole user table ? it will affect the business. > thanks. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Yes, if the user is using compression in production then this must be Enterprise or Advanced Enterprise - http://www.iiug.org/en/2017/07/29/compare-informix/ NOTE Also https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.gsg.doc/id s_gsg_181.htm "If concurrent transactions apply changes to the table faster than online index creation can apply those same changes to the index, the server now automatically places a transient shared lock on the table from time to time to reduce the amount of concurrent insert, update, or delete activity in the table to allow the index build to catch up with new changes. Because it is a shared lock, read activity in the table is not affected. Consequently, online index creation no longer results in long transactions or running out of space to store records of the ongoing changes that need to be applied to the index." It is not clear if this also applies to 12.10. Regards, David. > On 23 March 2018 at 18:28 Alexandre Marini <alexandre_marini@hotmail.com> wrote: > > > Thanks David, so only on advanced and enterprise editions. > > Best regards. > > Alexandre Marini > IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 > IBM Informix on Cloud - Database Administrator - 2017 > IBM dashDB Managed Service for Analytics and Transactions - 2017 > DB2 Advanced DBA - v10.5 for LUW > IBM Information Management Informix Technical Professional > IBM Certified Developer - Informix Genero > Informix independent consultant > > ________________________________ > De: david@smooth1.co.uk <david@smooth1.co.uk> > Enviado: sexta-feira, 23 de março de 2018 15:24 > Para: ids@iiug.org; Alexandre Marini > Assunto: RE: index compression on Version 12.10 [40935] > > > https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0401.htm > > An index can be created ONLINE and COMPRESSED. > > > https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0441.htm#ids_sqs_0441 > > "the database server briefly locks the table while updating the system catalog > with information about the new index." > > Regards, > David. > > Regareds, > David. > > > On 23 March 2018 at 18:11 Alexandre Marini <alexandre_marini@hotmail.com> > wrote: > > > > > > Sorry, I thougth you was asking about index creation. > > Manual says nothing about it. > > But I think the engine will only require a share intent lock, not an > exclusive > > one during the whole operation. > > > > Some friend could confirm that, please? > > Thanks a lot. > > > > Best regards. > > > > Alexandre Marini > > IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 > > IBM Informix on Cloud - Database Administrator - 2017 > > IBM dashDB Managed Service for Analytics and Transactions - 2017 > > DB2 Advanced DBA - v10.5 for LUW > > IBM Information Management Informix Technical Professional > > IBM Certified Developer - Informix Genero > > Informix independent consultant > > ________________________________ > > De: Alexandre Marini <alexandre_marini@hotmail.com> > > Enviado: sexta-feira, 23 de março de 2018 15:06 > > Para: CHUAN LU; ids@iiug.org > > Assunto: RE: index compression on Version 12.10 [40929] > > > > Hi, Chuan. > > Yes, from the manual page: > > > > > https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0401.htm > > > > "When you issue the CREATE INDEX statement, the table is locked in exclusive > > mode. If another process is using the table, CREATE INDEX returns an error. > > (For an exception, however, see The ONLINE keyword of CREATE > > > INDEX<https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc /ids_sqs_0441.htm?view=kc#ids_sqs_0441>.)" > > > > That's why online index creation is an advanced feature, only available at > > EE/AE editions. > > > > HTH > > Regards. > > > > Alexandre Marini > > IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 > > IBM Informix on Cloud - Database Administrator - 2017 > > IBM dashDB Managed Service for Analytics and Transactions - 2017 > > DB2 Advanced DBA - v10.5 for LUW > > IBM Information Management Informix Technical Professional > > IBM Certified Developer - Informix Genero > > Informix independent consultant > > ________________________________ > > De: ids-bounces@iiug.org <ids-bounces@iiug.org> em nome de CHUAN LU > > <luchuan@cn.ibm.com> > > Enviado: sexta-feira, 23 de março de 2018 00:03 > > Para: ids@iiug.org > > Assunto: index compression on Version 12.10 [40929] > > > > If i want to compress the detached index, Do the create index command lock > the > > whole user table ? it will affect the business. > > thanks. > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >