Re: Detecting Fragmentation Scheme in a script
> I am running IDS 7.30.UC6 on HP UX 10.20. I currently have a database
> that is fragmented by expression using year, month. We plan on keeping
> a rolling 24 months of history and detaching the oldest fragment and re
> attaching it with the new year, month when the time arrives. So the
> script has to be able to tell me what year, month data is in what
> fragment so I can detach it. My question is does anyone know how I can
> write a script (Perl has been recommended to me, but I am a DBA not a
> programmer) to detect what fragment should be detached?
>
> Thanks in advance
I had to write a C program to handle this, but I was fragmenting on a weekly basis
and wanted to use rdefmtdate()/rfmtdate(). Your monthly scheme can probably be
handled easily enough in a script, especially since 24 months means you only have to
add two to the year. The first step is to get the list of all fragments. Run the
following through dbaccess and redirect the output to a file:
select evalpos, dbspace, exprtext
from sysfragments
where fragtype = "T"
and tabid = (select tabid
from systables
where tabname = "your_table_name")
order by evalpos;
How you proceed from here depends on whether your fragmentation date ranges are in
ascending or descending order. Given that this is a history table, I'm assuming
that you rarely insert or update data from prior months. In that case, you should
have your fragmentation in descending order, with the newest fragment first. If so,
then you will need to find the dbspace at evalpos 0 (dbspace[0]) and the one at
evalpos 23 (dbspace[23]). Create statements like:
ALTER FRAGMENT DETACH dbspace[23] orphan_table; DROP orphan_table;
ALTER FRAGMENT ADD new_frag_expr IN dbspace[23] BEFORE dbspace[0];
If your fragmentation is in ascending order, then you only have to find the
dbspace[0], detach it, drop the orphan, and add it with no BEFORE clause.
The actual manipulation of the dbaccess output file is left as an exercise for you
in the tool of your choice (awk, perl, sed/grep, etc.).
Mark Collins
mcollins@us.dhl.com
People who have no clear idea what they mean by information or why
they should want so much of it are nonetheless prepared to believe
that we live in an Information Age, which makes every computer
around us what the relics of the True Cross were in the Age of
Faith: emblems of salvation.