RE: Finding dbspace from system catalog tables
Actually the iiug version of dbdiff2 does not do fragments. You need the
new dbdiff2 2.0 version which is still in beta and has been there for quite
some time now with little activity - my fault. Sigh, maybe I'll get to it
in time for the conference.
cheers
j.
"Richard Thomas" <richard_thomas@yes.optus.com.au> on 06/20/98 01:42:09 AM
To: informix-list@iiug.org
cc: (bcc: Jack Parker/Boston50/Epsilon)
Subject: RE: Finding dbspace from system catalog tables
Curt van den Heuvel wrote:
> On 10 Jun 1998 17:22:55 GMT, "James Richardson"
> <jamesr@aethos.co.uk.nospam> wrote:
>
> >
> > I am currently attempting to write a dbschema-alike program, which
analyses the information contained in the sys* tables, which
> will compare differences between two different database schemas, and ,
later, will generate the SQL to convert one database to be
> > the same as another.
> >
> > Things are going fairly well so far, however I have found no
information about how to determine which dbspace a table is in from
> > the system catalog.
> >
> > If anybody could tell me where the information is, that would be great.
> >
> > TIA
> >
> > James Richardson
>
> > PS: The next step is determining how to reconstruct fragmentation
information, so if you have any tips......
>
> For a non-fragmented table, convert the partnum field of the systables
> entry to an 8-digit hex number (with leading zeros). The first three
> digits will be the dbspace number as defined in the
> sysmaster:sysdbspaces table.
>
> For a fragmanted table, the partnum is 0 in systables, and the dbspace
> information is contained in the sysfragments table.
>
> -Curt
Sounds like serious "Wheel R & D" to me - check out dbdiff2 by Jack Parker
in the IIUG archives, it does exactly what you are proposing.
But in any case, the way to identify the DBspace a table is in via SQL is:
SELECT tabname, DBINFO('dbspace', partnum) AS dbspace
FROM systables
WHERE partnum > 0I am presuming you have a version 7+ instance. If not, you will have to go
with the Hex conversion discussed above.
The fragmentation information is in sysfragments - I'm not sure if dbdiff2
handles that or not.
cheers
RET
+------------------------------------------+
| Richard Thomas |
| DBA - Marketing Information Systems |
| Optus IT |
| email: richard_thomas@yes.optus.com.au |
| Ph: +61 2 9342 7188 |
| "My opinions are my opinions" |
+------------------------------------------+