Re: Anomaly: How are pages allocated?

On Tue, 16 Mar 2004 20:00:29 -0500, Chris Bullivant wrote: How did you create the table in the first place? If you just edited the schema of the t_invoices_ar table did you adjust the EXTENT SIZE and/or NEXT SIZE parameters? Very likely you did something akin to: dbschema -d hutrep_eom -t 't_invoices_ar' -ss | sed 's/t_invoices_ar/t_invoices_ar_temp' |dbaccess hutrep_eom - If you look at the dbschema for the tables you should not that either the EXTENT SIZE (initial extent sizing) is very large or the initial extent size is 16 and the NEXT SIZE is very large. Art S. Kagel > I'm using On-Line 7.31.UC6. Am trying to reclaim unused space from a DB and > to do this I'm trying to understand the Sysmaster DB. > > I'm going to unload a very big table (t_invoices_ar ~ 17M rows used, 50M > deleted by me), drop it, rebuild it, and then reload it. To test my scripts > I created a table, t_invoices_ar_temp, and then ran the scripts on that > using a 200 row subset of the big table. Everything looks good. > > About an hour later, doing some investigating, I used this script: > select tabname,ti_nptotal,ti_npused,ti_npdata,ti_nrows,ti_rowsize from > systabnames, systabinfo > where partnum = ti_partnum > and dbsname = 'hutrep_eom' > and tabname like 't\\_%';> > And got this output: > tabname ti_nptotal ti_npused ti_npdata ti_nrows ti_rowsize > > t_invoices_ar 3623517 3623517 550193 17606143 59 > t_invoices_ar_dia 8 1 0 0 31 > t_invoices_ar_vio 8 1 0 0 72 > t_oone_calltype 8 5 4 62 120 > t_invoices_ar_temp 136563 17 7 200 59 > t_invoices_tmp_dia 8 1 0 0 31 > t_invoices_tmp_vio 8 1 0 0 72 > t_equipshist 176 170 85 2018 78 t_invoices > 145149 102731 80130 2724388 55 t_phone_rev > 13960 13949 7036 379929 33 > > I understand this to mean that t_invoices_ar_temp has been allocated 136,563 > pages but only 17 are in use!! There has been no activity > > Either I totally misunderstand this output or there was a been a very > worrying, massive overallocation of pages to this table or there is third, > very mysterious explanation. > > Can anyone help me to understand what is happening here? > > Thanks, > Chris Bullivant

"Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2004.03.17.08.18.35.715384.1445@bloomberg.net>... > On Tue, 16 Mar 2004 20:00:29 -0500, Chris Bullivant wrote: > > How did you create the table in the first place? If you just edited the > schema of the t_invoices_ar table did you adjust the EXTENT SIZE and/or NEXT > SIZE parameters? Very likely you did something akin to: > > dbschema -d hutrep_eom -t 't_invoices_ar' -ss | sed > 's/t_invoices_ar/t_invoices_ar_temp' |dbaccess hutrep_eom -> > If you look at the dbschema for the tables you should not that either the > EXTENT SIZE (initial extent sizing) is very large or the initial extent size > is 16 and the NEXT SIZE is very large. > > Art S. Kagel > Yes you're right - the Extent Size is 748800 - obvious now that you've pointed it out. Thanks for your help, Chris Bullivant