in search of Informix metadata
Jesse
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
Obnoxio The Clown
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.
--
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.
Jack Parker
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
Hi all,
Thanks,
Jesse
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Jesse
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
On Sep 25, 4:08 am, "Jack Parker" <jack.park...@verizon.net> wrote:
> A decent starting point for you would have beenhttp://www.ibm.com/developerworks/db2/zones/informix/library/techarti...
> dbdiff2 at thewww.iiug.orgsoftware 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-boun...@iiug.org
>
> [mailto:informix-list-boun...@iiug.org]On Behalf Of Jesse
> Sent: Tuesday, September 25, 2007 1:34 AM
> To: informix-l...@iiug.org
> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list
Jesse
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...
Obnoxio The Clown
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
Jesse
On Sep 25, 11:35 pm, "Obnoxio The Clown" <obno...@serendipita.com>
wrote:
Art S. Kagel
wrote:
> Jesse said:
>
> > 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...
>
> 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
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
Jesse
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
John Carlson
> 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
>
Glad to have you aboard . . .
JWC