IDS Forum

RE: index compression on Version 12.10

Posted By: david@smooth1.co.uk
Date: Friday, 23 March 2018, at 3:01 p.m.

In Response To: RE: index compression on Version 12.10 (Alexandre Marini)

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/ids_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/ids_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/ids_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/ids_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.
>

Messages In This Thread

• index compression on Version 12.10
  CHUAN LU -- Thursday, 22 March 2018, at 11:03 p.m.
  • RE: index compression on Version 12.10
    Alexandre Marini -- Friday, 23 March 2018, at 2:11 p.m.
    • RE: index compression on Version 12.10
      david@smooth1.co.uk -- Friday, 23 March 2018, at 2:24 p.m.
    • RE: index compression on Version 12.10
      Alexandre Marini -- Friday, 23 March 2018, at 2:28 p.m.
      • RE: index compression on Version 12.10
        david@smooth1.co.uk -- Friday, 23 March 2018, at 3:01 p.m.
  • RE: index compression on Version 12.10
    Alexandre Marini -- Friday, 23 March 2018, at 2:22 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.