Informix

You can rename them even in older engine versions.

DELIMIDENT is your friend here. ;)

export DELIMIDENT=1
dbaccess mydb << EOF
RENAME INDEX " 123_456" TO ix__mytable__col1__col2;
ALTER FRAGMENT ON INDEX ix__mytable__col1__col2 INIT IN my8kdbsp;
EOF

-- creating table, default database dbspace is 'datadbs' (pagesize:2kb)
> CREATE TABLE my_8k_tbl
> (
> pk_col BIGSERIAL not null,
> fk_col INTEGER not null,
> ix1_col INTEGER not null,
> ix2_col datetime year to minute,
> PRIMARY KEY (pk_col) CONSTRAINT pk_col_constr
> )
> FRAGMENT BY RANGE(pk_col) INTERVAL(12345) STORE IN (DBS8K1, DBS8K2)
> PARTITION p0 VALUES < 12345 IN DBS8K1
> ;

Table created.

-- Checking pagesize of the table
> select b.name dbsname, b.pagesize, a.tabname
> from sysmaster:systabnames a, sysmaster:sysdbspaces b
> where sysmaster:partdbsnum(a.partnum) = b.dbsnum and a.tabname='my_8k_tbl';



dbsname   dbs8k1
pagesize  8192
tabname   my_8k_tbl

1 row(s) retrieved.


-- Checking pagesize of the index created by the primary key
> select b.name dbsname, b.pagesize, a.tabname
> from sysmaster:systabnames a, sysmaster:sysdbspaces b
> where sysmaster:partdbsnum(a.partnum) = b.dbsnum
> and a.tabname in (select idxname from sysconstraints where constrname='pk_col_constr');



dbsname   datadbs
pagesize  2048
tabname    116_24

1 row(s) retrieved.
​

Hi Gary,

I'd normally start with what the manual says. However the Informix manual doesn't seem to have much to say on about where any implicit indices go. I can only find:

"You can specify a dbspace for the table that is different from the storage location for the database, or fragment the table among dbspaces, or among named fragments in one or more dbspaces.
"If you specify no IN clause nor fragmentation scheme, the new table is stored in the same dbspace where the current database is stored."

Since it's not explicitly documented what the behaviour should be for related indices it is difficult to raise it as a bug. (Unless you can find a description elsewhere in the manual that I have missed.)

Personally I avoid this inline syntax and have gone as far to write scripts to manipulate schema files to remove it for primary keys and foreign keys for the reason you have found. I tend to think this syntax is there for compatibility; it certainly reduces your level of control and on large busy systems this kind of thing starts to become very important.

Lurking in the background is the problem that a unique index of any kind, i.e. primary key or unique constraint, cannot follow the table schema unless the lead key is the same as the range column. While your case seems to have a sensible solution, if you were to add "UNIQUE (fk_col)" to your CREATE TABLE statement this index would have to be detached. Where does the index get placed then? It is not clear to me that it should go in any of the dbspaces listed for the intervals or partition "p0". So it goes in the default dbspace.

I support the idea behind an RFE but I think it would need to cover the two cases being discussed:

1. A unique index or PK which has a lead key the same as the range column.
2. A unique index or PK which does not.