in search of Informix metadata

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jesse - 25 Sep 2007 06:34 GMT

Hi all,

I have a few questions about Informix that I haven't been able to
answer in web searches. I am using 9.5 but I'm interested in the
answers for earlier versions as well.

1. is there a standard way to store metadata about objects with
informix? In SQL Server there is the notion of extended properties,
and other DBMS use other methods, such as the ALL_TAB_COMMENTS in
Oracle, etc. Is there something similar with Informix?

2. are dependencies between procs and functions, and the tables/views
they target stored by the server? There is the sysdepend table, but
it seems only to track dependencies of views on tables.

3. Is there a way, using a tool or via SQL to generate the DDL for all
database objects in an existing db? I am aware of sysprocbody,
systrigbody, and sysviews for procs, triggers, and views. But what
about tables, indexes, etc? Any way to generate the DDL for them?

4. Is there a system catalog table that stores info about proc/
function parameters? I'm hoping for something akin to
INFORMATION_SCHEMA.PARAMETERS.

All of this is in regard to an app I have built (SqlSpec) that will
document a schema for any database on all major DBMS. I am currently
in the process of adding Informix support for it. You can check it
out here if you are interested: http://www.elsasoft.org.

Thanks,
Jesse

Reply to this Message

Obnoxio The Clown - 25 Sep 2007 07:37 GMT

Jesse said:

> Hi all,
>
> I have a few questions about Informix that I haven't been able to
> answer in web searches. I am using 9.5 but I'm interested in the
> answers for earlier versions as well.

9.4, surely? :o)

> 1. is there a standard way to store metadata about objects with
> informix? In SQL Server there is the notion of extended properties,
> and other DBMS use other methods, such as the ALL_TAB_COMMENTS in
> Oracle, etc. Is there something similar with Informix?

Not that I know of.

> 2. are dependencies between procs and functions, and the tables/views
> they target stored by the server? There is the sysdepend table, but
> it seems only to track dependencies of views on tables.

Not that I know of, but I could easily be wrong.

> 3. Is there a way, using a tool or via SQL to generate the DDL for all
> database objects in an existing db? I am aware of sysprocbody,
> systrigbody, and sysviews for procs, triggers, and views. But what
> about tables, indexes, etc? Any way to generate the DDL for them?

systables, sysindexes? Look at the dbschema command.

> 4. Is there a system catalog table that stores info about proc/
> function parameters? I'm hoping for something akin to
> INFORMATION_SCHEMA.PARAMETERS.

Pass.

> All of this is in regard to an app I have built (SqlSpec) that will
> document a schema for any database on all major DBMS. I am currently
> in the process of adding Informix support for it. You can check it
> out here if you are interested: http://www.elsasoft.org.

Signature

Bye now,
Obnoxio

"I'm astonished anyone pays real money for this crap."
-- Cosmo

"Cluster in my trousers"
-- Guy Bowerman

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

Reply to this Message

Jack Parker - 25 Sep 2007 12:08 GMT

A decent starting point for you would have been
http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/030
5parker/0305parker.html
Sounds like you are a bit past that. the SQL Reference Manual chapter 1
goes through each of the catalogues and details the contents. Although the
one linked to in the article is a bit old. Not sure where to find the
current copy, I can send you a copy if you like.

Also check out the dbschema command (OS level):

USAGE:

dbschema [-q] [-t tabname] [-s user] [-p user] [-r rolename] [-f
procname]
[-hd tabname] -d dbname [-w passwd] [-seq sequence] [-l [num]]
[-u [ia] udtname [all]] [-it [Type]] [-ss [-si]] [filename]

-q Suppress the db version from header

-t table name or "all" for all tables

-s synonyms created by user name
or "all" for all users

-p permissions granted to user name
or "all" for all users

-r create and grant of the role
or "all" for all roles :Not a valid option for SE

-f SPL routine name
or "all" for all SPL routines

-hd Histograms of the distribution for columns of
of a specified table, a specific table column,
or "all" for all tables.

-d database name

-w database password

-seq generate sequence specific syntax

-u Prints the definitions of user-defined data types

-ui Prints the definitions of user-defined data types,
including type inheritance

-ua Prints the definitions of user-defined data types,
including all functions and casts defined over a type

-u all Directs dbschema to include all the tables
in the display of distributions

-it Type of isolation can be DR, CR, CS or RR

-l set lock mode to wait [number] optional

-ss generate server specific syntax

-si excludes the generation of index storage clauses for
non-fragmented tables

filename is the name
of file that the SQL
script goes in.

Finally, you might also be interested in the sysmaster database, which
mainly contains virtual tables that describe memory structures. That is
documented in the Admin manual I think, although some undocumented tables
can also be useful.

For working code which rolls through all of this check out myschema and
dbdiff2 at the www.iiug.org software repository. Myschema is a 'c' version
of dbschema, dbdiff2 compares two databases via the metadata. Art keeps
myschema current, I haven't looked at dbdiff2 since V7/8 (although I do have
plans to correct that).

As to version differences, Informix has a history of retaining backwards
compatibility. A system catalogue from Version 4 will still look the same
in current versions (10,11), although additional columns may have been
added, new tables may have been added, and in the case of sysindexes, it is
a view to a newer structure - You won't find catalogues for features that
were unsupported (sysfrag* shows up in v7), but the tables you do find will
remain essentially unchanged over releases.

Regards,
Jack Parker

-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of Jesse
Sent: Tuesday, September 25, 2007 1:34 AM
To: informix-list@iiug.org
Subject: in search of Informix metadata

Hi all,

I have a few questions about Informix that I haven't been able to
answer in web searches. I am using 9.5 but I'm interested in the
answers for earlier versions as well.

1. is there a standard way to store metadata about objects with
informix? In SQL Server there is the notion of extended properties,
and other DBMS use other methods, such as the ALL_TAB_COMMENTS in
Oracle, etc. Is there something similar with Informix?

2. are dependencies between procs and functions, and the tables/views
they target stored by the server? There is the sysdepend table, but
it seems only to track dependencies of views on tables.

3. Is there a way, using a tool or via SQL to generate the DDL for all
database objects in an existing db? I am aware of sysprocbody,
systrigbody, and sysviews for procs, triggers, and views. But what
about tables, indexes, etc? Any way to generate the DDL for them?

4. Is there a system catalog table that stores info about proc/
function parameters? I'm hoping for something akin to
INFORMATION_SCHEMA.PARAMETERS.

All of this is in regard to an app I have built (SqlSpec) that will
document a schema for any database on all major DBMS. I am currently
in the process of adding Informix support for it. You can check it
out here if you are interested: http://www.elsasoft.org.

Thanks,
Jesse

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Reply to this Message

Jesse - 26 Sep 2007 06:44 GMT

Jack: thanks for the link to your article, I did read that earlier and
it was useful.

Jack/Obnoxio: the tip to use dbschema.exe to generate scripts for
tables was perfect. It's working well.

I should have a build working against Informix soon! :)

Jesse

> A decent starting point for you would have beenhttp://www.ibm.com/developerworks/db2/zones/informix/library/techarti...
> 5parker/0305parker.html
[quoted text clipped - 127 lines]
> Informix-list mailing list
> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list

Reply to this Message

Jesse - 26 Sep 2007 07:11 GMT

One other question. Is it possible to change context from one
database to another in the same connection?

In SQL Server you can issue a USE statement to change context. Or
alternatively you can use a 3 part naming convention, like this, to
select from a given database regardless of what the current context
is:

select * from dbame.schemaname.tablename

It appears this is not possible with Informix? It seems rather a
waste to have to make another connection to the server just to query
from a different database on the same server...

Reply to this Message

Obnoxio The Clown - 26 Sep 2007 07:35 GMT

Jesse said:

> One other question. Is it possible to change context from one
> database to another in the same connection?
[quoted text clipped - 9 lines]
> waste to have to make another connection to the server just to query
> from a different database on the same server...

In Informix, you have a slightly different set of contexts. It seems to me
that an SQLServer schema = an Informix database, and probably an SQLServer
dbname = an Informix instance.

So, given instances called foo and bar; databases called db1 and db2 in
each instance; and a table called tablea in each of the databases, you can
connect to db1 on foo and refer to:

select * from tablea; -- refers to tablea in db1 on instance foo
select * from db2:tablea; -- refers to tablea in db2 on instance foo
select * from db1:tablea@bar; -- refers to tablea in db1 on instance bar
select * from db2:tablea@bar; -- refers to tablea in db2 on instance bar

Signature

Reply to this Message

Jesse - 26 Sep 2007 07:43 GMT

Excellent, that's exactly the syntax I was missing. Thanks!

On Sep 25, 11:35 pm, "Obnoxio The Clown" <obno...@serendipita.com>
wrote:

> Jesse said:
>
[quoted text clipped - 39 lines]
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.

Reply to this Message

Art S. Kagel - 26 Sep 2007 14:29 GMT

On Sep 26, 2:35 am, "Obnoxio The Clown" <obno...@serendipita.com>
wrote:

> Jesse said:
>
[quoted text clipped - 24 lines]
> select * from db1:tablea@bar; -- refers to tablea in db1 on instance bar
> select * from db2:tablea@bar; -- refers to tablea in db2 on instance bar

Almost. The last two should be:

select * from db1@bar:tablea; -- refers to tablea in database/schema
db1 on instance bar
select * from db2@bar:tablea; -- refers to tablea in database/schema
db2 on instance bar

FYI, if you do not have to join across databases/instances, it CAN be
more efficient in a single application that needs data from multiple
databases to have a separate connection for each database. This is
most true if the 'other' database(s) are in different instances than
the current database. This is because if you are accessing a remote
server through the current connection the connection IDS instance is
handling the communications and it's a multithreaded server that has
lots of work to do. If you open a separate connection in your
application and switch between connections to get data from multiple
servers then your application is handling all of the communications
and it has nothing better to do ;-) Obviously, you should test and
see what works best for your application and environment, for example
if the IDS instance has access to a GB network but the application has
to use a slower interface, then the remote through the IDS server will
tend to be faster for larger queries but may still be slower for
smaller ones.

Art S. Kagel

Reply to this Message

Jesse - 27 Sep 2007 18:17 GMT

Art,

thanks for the tip. In my case I have a single connection to database
"foo" and want to grab some data from sysmaster as well, for the same
instance. So there's no need for another connection I think. I am
able to get what I need with the colon syntax: select * from
sysmaster:sometable

Thanks for your help - I appreciate it. I am a total beginner with
informix. :)

Jesse

> On Sep 26, 2:35 am, "Obnoxio The Clown" <obno...@serendipita.com>
> wrote:
[quoted text clipped - 53 lines]
>
> Art S. Kagel

Reply to this Message

John Carlson - 28 Sep 2007 02:45 GMT

> Art,
>
[quoted text clipped - 8 lines]
>
> Jesse

Glad to have you aboard . . .

JWC

Reply to this Message