Creating attached index in 9.3 using SQL query

Hi, How do I create an attached index using SQL query without setting DEFAULT_ATTACH to 1? Actually, I am trying to generate schema (dbschema) of a database which was created with DEFAULT_ATTACH set to 1. When I run dbschema on it, it outputs with "IN <dbspace" clause that mean detached index whereas I created attached index (bug #163284). I heard of somthing called "IN TABLE" clause which is not supported. Manual says: DEFAULT_ATTACH The DEFAULT_ATTACH environment variable supports the legacy behavior of Version 7.x of Dynamic Server,which required that only nonfragmented Btree indexes on nonfragmented tables can be attached. If DEFAULT_ATTACH is set to 1, then all other indexes, including R-trees and UDT indexes, must be detached. (An attached index is one that is created without specifying a storage clause.) If DEFAULT_ATTACH is not set, then the CREATE INDEX statement creates detached indexes by default. This release of Dynamic Server can support attached indexes that were created by Version 7.x of Dynamic Server. Important: DEFAULT_ATTACH might not continue to be supported in future releases of Informix database servers. It is not recommended that you develop new database applications that depend on this deprecated feature. Thanks in advance, Ravi

I've never heard of this. Generally if you hear of somthing that is not supported, then that's a clear indication that it is also not tested. I don't know of too may sites that are willing to do stuff that we do not document and thus are not testing. So it's probably a good idea to stay clear of anything that is not documented unless told specifically by tech support to use it. M.P. Ravindra Aute/Lenexa/IBM@I To: ids@iiug.org BMUS cc: Sent by: Subject: Creating attached index in 9.3 using SQL query [1713] forum.subscriber@ iiug.org 08/15/2003 01:53 PM Hi, How do I create an attached index using SQL query without setting DEFAULT_ATTACH to 1? Actually, I am trying to generate schema (dbschema) of a database which was created with DEFAULT_ATTACH set to 1. When I run dbschema on it, it outputs with "IN <dbspace" clause that mean detached index whereas I created attached index (bug #163284). I heard of somthing called "IN TABLE" clause which is not supported. Manual says: DEFAULT_ATTACH The DEFAULT_ATTACH environment variable supports the legacy behavior of Version 7.x of Dynamic Server,which required that only nonfragmented Btree indexes on nonfragmented tables can be attached. If DEFAULT_ATTACH is set to 1, then all other indexes, including R-trees and UDT indexes, must be detached. (An attached index is one that is created without specifying a storage clause.) If DEFAULT_ATTACH is not set, then the CREATE INDEX statement creates detached indexes by default. This release of Dynamic Server can support attached indexes that were created by Version 7.x of Dynamic Server. Important: DEFAULT_ATTACH might not continue to be supported in future releases of Informix database servers. It is not recommended that you develop new database applications that depend on this deprecated feature. Thanks in advance, Ravi

The only SQL way to accomplish this is "create index i1_t1 on t1(c1) in table" and I tried that in 9.30 and that works ...otherwise the DEFAULT_ATTACH needs to be set for the session where you are creating the indexes in order to accomplish what you want. Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi Ravindra Aute/Lenexa/IBM@I To: ids@iiug.org BMUS cc: Sent by: Subject: Creating attached index in 9.3 using SQL query [1713] forum.subscriber@ iiug.org 08/15/2003 11:53 AM Hi, How do I create an attached index using SQL query without setting DEFAULT_ATTACH to 1? Actually, I am trying to generate schema (dbschema) of a database which was created with DEFAULT_ATTACH set to 1. When I run dbschema on it, it outputs with "IN <dbspace" clause that mean detached index whereas I created attached index (bug #163284). I heard of somthing called "IN TABLE" clause which is not supported. Manual says: DEFAULT_ATTACH The DEFAULT_ATTACH environment variable supports the legacy behavior of Version 7.x of Dynamic Server,which required that only nonfragmented Btree indexes on nonfragmented tables can be attached. If DEFAULT_ATTACH is set to 1, then all other indexes, including R-trees and UDT indexes, must be detached. (An attached index is one that is created without specifying a storage clause.) If DEFAULT_ATTACH is not set, then the CREATE INDEX statement creates detached indexes by default. This release of Dynamic Server can support attached indexes that were created by Version 7.x of Dynamic Server. Important: DEFAULT_ATTACH might not continue to be supported in future releases of Informix database servers. It is not recommended that you develop new database applications that depend on this deprecated feature. Thanks in advance, Ravi

Rajib, Where is this documented? I will stress again. We only test that which we document. M.Pruet Rajib Sarkar/Phoenix/IB To: ids@iiug.org M@IBMUS cc: Sent by: Subject: Re: Creating attached index in 9.3 using SQL query [1715] forum.subscriber@ iiug.org 08/15/2003 04:08 PM The only SQL way to accomplish this is "create index i1_t1 on t1(c1) in table" and I tried that in 9.30 and that works ...otherwise the DEFAULT_ATTACH needs to be set for the session where you are creating the indexes in order to accomplish what you want. Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi Ravindra Aute/Lenexa/IBM@I To: ids@iiug.org BMUS cc: Sent by: Subject: Creating attached index in 9.3 using SQL query [1713] forum.subscriber@ iiug.org 08/15/2003 11:53 AM Hi, How do I create an attached index using SQL query without setting DEFAULT_ATTACH to 1? Actually, I am trying to generate schema (dbschema) of a database which was created with DEFAULT_ATTACH set to 1. When I run dbschema on it, it outputs with "IN <dbspace" clause that mean detached index whereas I created attached index (bug #163284). I heard of somthing called "IN TABLE" clause which is not supported. Manual says: DEFAULT_ATTACH The DEFAULT_ATTACH environment variable supports the legacy behavior of Version 7.x of Dynamic Server,which required that only nonfragmented Btree indexes on nonfragmented tables can be attached. If DEFAULT_ATTACH is set to 1, then all other indexes, including R-trees and UDT indexes, must be detached. (An attached index is one that is created without specifying a storage clause.) If DEFAULT_ATTACH is not set, then the CREATE INDEX statement creates detached indexes by default. This release of Dynamic Server can support attached indexes that were created by Version 7.x of Dynamic Server. Important: DEFAULT_ATTACH might not continue to be supported in future releases of Informix database servers. It is not recommended that you develop new database applications that depend on this deprecated feature. Thanks in advance, Ravi