Detached Primary Key/Unique Index on 10
Hi, I have a large table on an informix 10 machine. Now it runs out of extent on the primary key index fragment. It is a default primary key that is created with the table. How can I detached the primary key? Thank you.
If the key was give an explicit name, then you can drop it and its index
along with it using that name. If not then you can find the constraint
name in sysconstraints:
select constrname
from systables st, sysconstraints sc
where st.tabid = sc.tabid
and tabname = 'mytable'
and constrtype = 'P';
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Fri, May 10, 2013 at 6:49 AM, MOHAMMAD IRFAN <irfan199@yahoo.com> wrote:
> Hi, I have a large table on an informix 10 machine. Now it runs out of
> extent
> on the primary key index fragment. It is a default primary key that is
> created
> with the table. How can I detached the primary key?
>
> Thank you.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c37a845c257c04dc5b91be
I mean, how to re-create the primary key? Since I still need the PK to enforce unique records. The table itself is fragmented by round robin strategy. The PK is consist of 3 columns. I've tried to create unique index first but it failed. It give me fragmentation strategy error. I've also tried to create individual index for those 3 columns according to ids 10 documentation: http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqls.doc/sqls 218.htm But when I create uniqe index, it still fail. Though I haven't try to just create the primary key. Is there any solution? Thanks.
Yes. Create the unique index with no fragmentation or IN clause and it will follow the table's fragmentation which it must do. Then you should be able to create the primary key using that index. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, May 10, 2013 at 11:40 AM, MOHAMMAD IRFAN <irfan199@yahoo.com> wrote: > I mean, how to re-create the primary key? Since I still need the PK to > enforce > unique records. The table itself is fragmented by round robin strategy. > The PK > is consist of 3 columns. I've tried to create unique index first but it > failed. It give me fragmentation strategy error. I've also tried to create > individual index for those 3 columns according to ids 10 documentation: > > > http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqls.doc/sqls 218.htm > > But when I create uniqe index, it still fail. Though I haven't try to just > create the primary key. Is there any solution? > > Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0160a3b8dc1f5b04dc5f591a
You can drop the contraint PK and the unique index that was created with it will be dropped automatically unless the unique index was created on its own prior to creating the primary key. If the unique index remains after dropping the primary key, you need to drop the index by itself; indexes created on their own have explicite names that have been given by the creator of the index (look into the table sysindices or the viw sysindexes), while indexes created implicitely when your create a primary key have names such as " 112_45" with a blank space in the first position - these indexes cannot be dropped directly. Be careful, you might have foreign constraints that refer to the primary key. In this case, you need to drop the foreign key constraint(s) first. One other thing that you might run into when you recreate your new indexes, if your dbspaces are fragmented (a lot of holes in the chunk) , your newly recreated indexes might reuse the same free spaces and you will run into the same problem of too many extents. Either, create the new index using a different dbspace or reorganize you current dbspace. Moving up to later versions (11.50, 11.70 or event 12.1) will help in order to use some of the new functionnalities such as repack, shrink, reorg, extent sizes for indexes. Hope that this helps. Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Président UGIF - User Group Informix France IIUG - Board of Directors Tél: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 10/05/13 12:49, MOHAMMAD IRFAN a écrit : > Hi, I have a large table on an informix 10 machine. Now it runs out of extent > on the primary key index fragment. It is a default primary key that is created > with the table. How can I detached the primary key? > > Thank you. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >