Indexes via DBI driver in Perl (DBD::Informix)

Indexes via DBI driver in Perl (DBD::Informix)

Post by Jonathan Leffle » Sun, 31 Dec 1899 09:00:00




>  how can I get the indexes of some or all tables in a database using
>  DBI driver? I'm using the Informix-DBD, and there's no hint about this
>  topic.

...mainly because it isn't something that DBI supports directly.

Your best bet is probably to go to the IIUG software archives
(hmm, how many times have I said this today?  http://www.iiug.org)
and get hold of SQLCMD.  In the code for the INFO statements
(sqlinfo.ec), you will find the statements I use to generate the
information loosely equivalent to "INFO INDEXES FOR sometable",
which is not a built-in command in SQL so you cannot run it
directly from DBD::Informix.  Note that the statements assume
you know the table number (systables.tabid) entry for the table.
To deal with lists of tables, you will have to revise the code
slightly.

--
Yours,

Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
     "I don't suffer from insanity; I enjoy every minute of it!"

 
 
 

1. Indexes via DBI driver in Perl

Just perform this SQL:

SELECT idxname
FROM sysindexes si, systables st
WHERE si.tabid - st.tabid
  AND st.tabname = "mytable";

If you also want the column definitions that is more difficult and ugly but
looks something very much like this:

SELECT idxname, c1.colname, c2.colname, c3.colname, ......
FROM sysindexes si,
        systables st,
        syscolumns c1,
        OUTER syscolumns c2,
...
        OUTER syscolumns c16
WHERE si.tabid - st.tabid
  AND st.tabname = "mytable"
  AND c1.tabid = st.tabid AND c1.colno = ABS(si.part1)
  AND c2.tabid = st.tabid AND c2.colno = ABS(si.part2)
  AND c3.tabid = st.tabid AND c3.colno = ABS(si.part3)
...
  AND c16.tabid - st.tabid AND c16.colno = ABS(si.part16)
;

Art S. Kagel

2. Permission to VIEW bot not the TABLE

3. Perl DBI-0.93 DBD-Informix-0.58 on SCO

4. Help on changing control:

5. perl DBD, perl DBI programming

6. info from one file to another

7. Perl & DBI/DBD::Pg confusion with finish

8. Indexes with Progress

9. Where are Perl DBI and DBD for PostgreSQL??

10. Perl, DBD, DBI, HPUX 9.05

11. PERL , DBI , DBD and ORACLE

12. Perl 5.003, DBI, DBD and Oracle problem