sysmaster:syspaghdr

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/infocenter/idshelp/v10/topic/com.ibm.perf.doc/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/infocenter/idshelp/v10/topic/com.ibm.perf.doc/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. :o) -- Bye now, Obnoxio "Jesus you fucking people are hopeless." -- Double Enema

"Sehr vielen dank. :o) " Thanks! That was really helpful for the rest of us. ;-p