How to know what index is set on the table with command line in informix

informix

I'm using the Informix DB. I want to detect what index the table has using command line. I used 

dbschema -d dbName -t  tableName | grep index

to detect the index. What's my problem is that the dbschema output format is not specified (hard to automatically parse it) so I don't think it is reliable. Is there any better way to automatically detect the index instead of read by human?

Best Answer

I think understand your problem.

The dbaccess break lines at the middle of the statement and the table name goes to line bellow... Something like this (dbschema output) 

create unique index "informix".ix1c_h_dbases_2012_06 on "informix"
    .h_dbases_2012_06 (seq,site_name) using btree  filtering

I consider two solutions 100% functional...

First suggestion

Parse the dbschema with awk to join the lines until found a semicolon.
This will give to you long lines... Then you can work little more with awk/sed to get only the index and table name... not so hard.
But this probably will become a shell script.

$ dbschema -d dba  -t h_dbases_2012_06| awk '{printf $0} /;/{print ""}' | egrep "create.index"
create index "informix".ix10_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,pagwrites) using btree ;
create index "informix".ix11_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,total_sorts) using btree ;
create index "informix".ix12_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,isreads) using btree ;
create index "informix".ix13_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,iswrites) using btree ;
create index "informix".ix14_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,iscommits) using btree ;
create index "informix".ix15_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,net_write_mb) using btree ;
create index "informix".ix16_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,horini) using btree ;
create index "informix".ix1_h_dbases_2012_06 on "informix".h_dbases_2012_06     (seq) using btree ;
create index "informix".ix1b_h_dbases_2012_06 on "informix".h_dbases_2012_06     (seq,hostname) using btree ;
create index "informix".ix2_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,username) using btree ;
create index "informix".ix3_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,cpu_time) using btree ;
create index "informix".ix4_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,programa) using btree ;
create index "informix".ix5_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,tarefa) using btree ;
create index "informix".ix6_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,hostname) using btree ;
create index "informix".ix7_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,maxlog_kb) using btree ;
create index "informix".ix8_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,net_read_mb) using btree ;
create index "informix".ix9_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,pagereads) using btree ;

Second suggestion

Query the database.
For this , they should be a script and your user should have access to database with dbaccess 

$ cat x.sh
dbaccess -e - <<EOF
database $1 ;
select t.tabname[1,20] as table, i.idxname[1,20] as idx
from systables t , sysindices i
where t.tabid = i.tabid
  and t.tabname matches lower("$2*")
  and t.tabid > 99
order by 1,2
;
EOF

$ sh x.sh dba h_dbases_2014_04
database dba ;
Database selected.


select t.tabname[1,20] as table, i.idxname[1,20] as idx
from systables t , sysindices i
where t.tabid = i.tabid
  and t.tabname matches lower("h_dbases_2014_04*")
  and t.tabid > 99
order by 1,2
;

table                idx

h_dbases_2014_04     ix10_h_dbases_2014_0
h_dbases_2014_04     ix11_h_dbases_2014_0
h_dbases_2014_04     ix12_h_dbases_2014_0
h_dbases_2014_04     ix13_h_dbases_2014_0
h_dbases_2014_04     ix14_h_dbases_2014_0
h_dbases_2014_04     ix15_h_dbases_2014_0
h_dbases_2014_04     ix16_h_dbases_2014_0
h_dbases_2014_04     ix1_h_dbases_2014_04
h_dbases_2014_04     ix1b_h_dbases_2014_0
h_dbases_2014_04     ix1c_h_dbases_2014_0
h_dbases_2014_04     ix2_h_dbases_2014_04
h_dbases_2014_04     ix3_h_dbases_2014_04
h_dbases_2014_04     ix4_h_dbases_2014_04
h_dbases_2014_04     ix5_h_dbases_2014_04
h_dbases_2014_04     ix6_h_dbases_2014_04
h_dbases_2014_04     ix7_h_dbases_2014_04
h_dbases_2014_04     ix8_h_dbases_2014_04
h_dbases_2014_04     ix9_h_dbases_2014_04

18 row(s) retrieved.




Database closed.
Related Question