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 > 0
I 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" |
+------------------------------------------+