sysmaster question
System info: HP-UX B.11.11 IDS 9.40.HC3
I been asked by management to collect data on table size and map growth
of the databases by table. The tools management will be using are the
Cognos tool suite (Impromptu, PowerPlay, etc).
So I created a table (gu_db_tables) that I fill with a snap shot of
each table (name, which database, extent numbers, estimated kb size,
etc) once a week. Everything was working fine when we where on IDS
7.31 as the indexes where part of the table, however when we upgraded
to 9.4 the indexes became their own table and now my data I collect is
not what my management wants.
They have asked one of two things to happen so they can us the data
again.
1) Figure out a way to combine the table information and the index
tables information into one record
in gu_db_tables or
2) Only capture the data tables' information and disregard the
index tables if you can't do the first.
An example of what I am talking about we have a table called id_rec
that has the following indexes that are now tables id_fullname,
id_key1, id_phone, id_prsp_no, id_ss_no, id_zip, so that means 7
records need to be combined into 1 for what management wants (option
1), or how I know to disregard 6 records as they are indexes (option
2).
Below is the sql statement that I start with to gather the information
on the 3 databases we are interested in. The sql statements after this
one just use the first and next extent size to estimate kb used. Is
there some where in sysmaster I can query to find the relationship
between id_rec and the 6 index tables or some way to know whether a
table is a table or an index? Or is there a better way to gather the
data management wants?
select e.dbsname, e.tabname, count(*) num_extents, today add_date
from sysmaster:sysextents e, sysmaster:systabnames t
where e.dbsname in ("cars", "cars_audit", "uni83") and
e.dbsname=t.dbsname and e.tabname=t.tabname andt.owner="informix" and
e.tabname > "9999999999"
group by 1,2
order by 1,2
into temp dba_a with no log;
I have created two tables to collect the data, one is the table
information (gu_db_tables) and the other extent information
(gu_db_extents). The extent table holds the weekly snap shot data and
gu_db_table holds current information
gu_db_table layout:
table_no serial no
partnum integer yes
db_name char(32) yes
table_name char(32) yes
schema_dir char(128) yes
schema_name char(32) yes
audited char(1) yes
num_extents integer yes
first_extent integer yes
next_extent integer yes
num_rows integer yes
total_storage integer yes
note char(256) yes
gu_db_extents layout:
table_no integer yes
add_date date yes
num_extents integer yes
first_extent integer yes
next_extent integer yes
total_storage integer yes
John David Adamski
Network Specialist/DBA
Graceland Univeristy
"... Their's not to make reply,
Their's not to reason why,
Their's but to do and die ..." from The "Charge of the Light
Brigade" by Tennyson
sysindexes table in the target database use the tabid field to match it to a table.
jda wrote:
> System info: HP-UX B.11.11 IDS 9.40.HC3
>
> I been asked by management to collect data on table size and map growth
> of the databases by table. The tools management will be using are the
> Cognos tool suite (Impromptu, PowerPlay, etc).
>
> So I created a table (gu_db_tables) that I fill with a snap shot of
> each table (name, which database, extent numbers, estimated kb size,
> etc) once a week. Everything was working fine when we where on IDS
> 7.31 as the indexes where part of the table, however when we upgraded
> to 9.4 the indexes became their own table and now my data I collect is
> not what my management wants.
<SNIP>
> Below is the sql statement that I start with to gather the information
> on the 3 databases we are interested in. The sql statements after this
> one just use the first and next extent size to estimate kb used. Is
> there some where in sysmaster I can query to find the relationship
> between id_rec and the 6 index tables or some way to know whether a
> table is a table or an index? Or is there a better way to gather the
> data management wants?
You have to join to sysptnhdr via the lockid column. That will collate
all of the indexes and fragments that belong to the same table. Then you
can use additional aggregation, also use sysptnext instead of the view
sysextents. So, here's one version:
select t.dbsname, t.tabname, count(*) num_extents, today add_date
from sysmaster:sysptnext pe, sysptnhdr ph, sysmaster:systabnames t
where t.dbsname in ("cars", "cars_audit", "uni83") and
pe.pe_partnum = ph.partnum and ph.lockid = t.partnum and
t.owner="informix" and
t.tabname > "9999999999"
group by 1,2
order by 1,2
into temp dba_a with no log;
FYI, you can use a SUM() of the npused column of sysptnhdr (time pagesize)
to get the exact amount of space taken up by the table and its components.
You can also compare npused to npdata to differentiate the space used for
data and that used for indexes.
Art S. Kagel
<SNIP>