data model
look up the Informix dbchema utility; especially the -t option;
e.g. dbschema -d databasename -t tablename
will display all info for a table including primary and foreign keys
hi all let me start by saying a D.B.A. I'm not. I have a database with over 100 tables and some table have over 130 columns. I do not know which version of online we are running. I do have root access on our AIX v4.2.1 H50's. I am trying to build a data model from the info I do have. The table names and column content type. The foreign keys are what I don't have. Is there a command I can include in a korn shell script that will tell me which columns are foreign keys and to were they point. Also a command to tell me the version would be nice. Obviously I did not build this database I adopted it Thanks kirt
I've been where you are.
It sounds like the primary and foreign keys are not explicitly defined. First
things first. Get the schema by:
dbschema -d yourdb -t all -ss > schema.outExamine schema.out to determine if primary and foreign keys are explicitly
define,
they look like:
primary key (emp_id)
foreign key (emp_id) references emp
If keys not defined, then look at indexes. Unique indexes are primary key
candidates. If a table has only one unique index defined, it is the primary key
of that table. If it has two defined, then you've got figure out which one is
the foreign key. Then look for non-unique indexes that are used to make joins
more efficient - these join columns are probably foreign keys - but you have to
figure out what primary key they relate to - hopefully the names of the columns
will help.
I forgot to mention comments and documentation. If you have any, they may be
helpful.
Then there is the source code, if it is available and if you can understand it.
By looking at the SQL statements you can more or less pin down what the columns
relate to what columns. For example if you see
select x from emp, dept
where emp.id = dept.dept_nothen you can assume that emp.id and dept.dept_no are the same thing, and one is
the primary key and the other a foreign key.
Furthermore, there is is (expensive) software, namely ERwin, that will analyze
the schema for you and put everything together. However, when primary and
foreign keys are not explicitly defined and naming of columns is inconsistent,
ERwin does not get it right.
Maybe someone else will respond to this post and offer a shell script or program
that they've written that takes tedium out of this analysis.
Good luck!!!!!!!!!!!!
kirt wrote:
> hi all
> let me start by saying a D.B.A. I'm not. I have a database with over 100
> tables and some table have over 130 columns. I do not know which version of
> online we are running. I do have root access on our AIX v4.2.1 H50's.
> I am trying to build a data model from the info I do have. The table names
> and column content type. The foreign keys are what I don't have. Is there a
> command I can include in a korn shell script that will tell me which columns
> are foreign keys and to were they point.
> Also a command to tell me the version would be nice.
>
> Obviously I did not build this database I adopted it
>
> Thanks
> kirt