Hi,

I found this sophisticated statement as a cronjob.

#!/bin/sh
#
#
# Determine the number of still possible Extents per fragment
#
dbaccess sysmaster <<eof
select {+ORDERED,INDEX(a,syspaghdridx)}

trunc(pg_frcnt/8) frext, partaddr( dbsnum, pg_pagenum ) partnum from
sysdbspaces b, syspaghdr a
where a.pg_partnum = partaddr( dbsnum, 1 )
and pg_flags = 2 into temp ggg with no log;
select first 20 dbsname, tabname, frext from systabnames a, ggg b
where a.partnum = b.partnum
order by frext;
eof

Could somebody explain the statement or - better - give me a hint where I
can find some documentation about the specific topic. Found nothing in the
whole IDS 10 docus. They seem to be a bit reserved with the sysmaster
database.

And a question to the same topic: Is there any limitation with the number of
extents per fragment (IDS 9.40.FC4W2)? In my team spooks a value between
200 and 500 extents per fragment.

TIA,
Reinhard

Hi,

jetzt mal auf deutsch. ;-)

Die Query soll scheinbar die noch verfügbaren Extents pro
Tabellen/Fragment/Index ermitteln.

Und ja, es gibt eine maximale Anzahl von möglichen Extents bei den
obigen Objekten - und die Beachtung dieser Grenze ist in der Tat
verdammt wichtig, denn wenn die erreicht ist ... ist Schluß. D.h. dann
ist kein weiteres Wachstum (= Allokation neuer Extents) des Objekts mehr
möglich.
(Es gibt dann zum Beispiel bei INSERT auf einen solchen Table (bzw.
einen Table, bei dem ein Index seine maximale Extentzahl erreicht hat)
nur noch die Fehlermeldung "Could not insert. No more extents").

Das Objekt muss dann komplett neu aufgebaut werden. -> Bei großen
Tabellen/Indexen (gerade da passierts natürlich) laaaaange Downtime.

Die Sache ist mir schon mal bei einem 24x7 System passiert ... Horror!
Die Ursache war eine viel zu geringe NEXT EXTENTS SIZE (ich glaub 32 KB
oder so warens) Einstellung bei einem Table der viele, viele GBytes groß
wird. Irgendwann war dann die maximale Anzahl von Extents (bei 2k page
(UNIXe) size etwa 200, bei 4k page size (WINDOFF) etwa 400) erreicht,
d.h. es waren keine INSERTS mehr möglich.

Schließlich blieb nichts anderes mehr üblich als die Tabelle mit ALTER
FRAGMENT INIT IN ... neu zu erstellen (samt Indexen). Das kann bei
großen Objekte dauern (Stunden unscheduled downtime ...)

Interessanter Link zu dem Thema:
http://publib.boulder.ibm.com/infoce...oc/perf189.htm

Ich hab auch eine eigene Query@Work, die die noch freien Extents für
jedes Objekt ausgibt, bei Interesse ...

Gruss.

Habichtsberg, Reinhard schrieb:
> Hi,
>
> I found this sophisticated statement as a cronjob.
>
> #!/bin/sh
> #
> #
> # Determine the number of still possible Extents per fragment
> #
> dbaccess sysmaster <<eof
> select {+ORDERED,INDEX(a,syspaghdridx)}
>
> trunc(pg_frcnt/8) frext, partaddr( dbsnum, pg_pagenum ) partnum from
> sysdbspaces b, syspaghdr a
> where a.pg_partnum = partaddr( dbsnum, 1 )
> and pg_flags = 2 into temp ggg with no log;
> select first 20 dbsname, tabname, frext from systabnames a, ggg b
> where a.partnum = b.partnum
> order by frext;
> eof
>
> Could somebody explain the statement or - better - give me a hint where I
> can find some documentation about the specific topic. Found nothing in the
> whole IDS 10 docus. They seem to be a bit reserved with the sysmaster
> database.
>
> And a question to the same topic: Is there any limitation with the number of
> extents per fragment (IDS 9.40.FC4W2)? In my team spooks a value between
> 200 and 500 extents per fragment.
>
> TIA,
> Reinhard
>
>
>

Michael Schmid said:
>
> jetzt mal auf deutsch. ;-)

Thanks! That was really helpful for the rest of us.

--
Bye now,
Obnoxio

"Jesus you fucking people are hopeless."
-- Double Enema

Obnoxio The Clown schrieb:
> Michael Schmid said:
>> jetzt mal auf deutsch. ;-)
>
> Thanks! That was really helpful for the rest of us.
>

For the clown and the rest of the world: ;-)

The query seems to report the still avalaible extents for a
table/fragment or index.

And yes, there exists a maximum number of extents for these kinds of
objects - and to consider this limit is damn important, because when you
reach it then ... big problem. This means no further growth (=
allocation of new, additional extents) for this object. For example,
when you try to INSERT in such a table (or into a table, for which an
index has reach its maximum extents) you get the error message "Could
not insert. No more extents").

You have to rebuild the object completly -> With big Table/Indexes (and
there are the ones where you could get this problem) you need looong
downtime for the rebuild.

I saw this happened in an 24x7 system ... big trouble!
The cause was a much too low NEXT EXTENT SIZE (i think it were 32 KN or
so, dawm the developer) for a table, that was to many, many GB in size.
Then the maximum number of extents was final reached (with 2k page size
(UNIX) about 200, with 4k page size (WIN) about 400), and then no more
INSERTS.

In the end, the table (some GB) had to be rebuild with ALTER FRAGMENT
INIT IN ... (also the indexes of this table). And this can run for a
while (in this case hours of unscheduled downtime).

Interesting link about this topic:
http://publib.boulder.ibm.com/infoce...oc/perf189.htm

I have a Query@Work, that reports the free extents for every object, if
you are interested ...

Regards.

Michael Schmid said:
> Obnoxio The Clown schrieb:
>> Michael Schmid said:
>>> jetzt mal auf deutsch. ;-)
>>
>> Thanks! That was really helpful for the rest of us.
>
> For the clown and the rest of the world: ;-)

Sehr vielen dank. )

--
Bye now,
Obnoxio

"Jesus you fucking people are hopeless."
-- Double Enema

"Sehr vielen dank. ) "

Thanks! That was really helpful for the rest of us.


;-p

Hi all,

here is the query for the still available extents:

select {+ ordered, index(a, syspaghdridx) } -- necessary
c.tabname, -- the table or index
c.dbsname, -- the database
b.name, -- the dbspace
trunc(a.pg_frcnt / 8) frext -- the free extents
from sysmaster:sysdbspaces b,
sysmaster:syspaghdr a,
sysmaster:systabnames c
where a.pg_partnum = sysmasterartaddr(b.dbsnum, 1)
and sysmaster:bitval(a.pg_flags, 2) = 1
and a.pg_nslots = 5
and c.partnum = sysmasterartaddr(b.dbsnum, a.pg_pagenum)
order by 3 asc -- show me the problem candidates first

This works in 9.4 and i think it should work in 10, too.

But note: for fragmented table or indexes it shows the table (or index)
multiple times (for each fragment of a table one row), so you have to
consider the dbspace.name column, too.

If you find, that you only have a few (< 30) extents left and the table
or index should grow in the future, then adjust you NEXT EXTENT SIZE as
soon as possible (and consider a scheduled downtime for a rebuild).

Hope this helps avoiding a nasty problem.


Here are two additional links with interesting and detailed information
about this issue:
But, i'm sorry - really! - they are in german: :-(

http://www.ordix.de/onews2/4_2004/si...chitektur.html
http://www.ordix.de/onews2/1_2005/ib...ganisation.php