Number of extents and remaining extents

I have a script in stored procedure which calculates, among other things, the number of extents allocated to a table and the remaining number of extents it can take before it errors out "no more extents". In version prior to 9.40, this is how I calculated:- for number of extents:- w_partnum is table partnum. select nextns into w_num_extents, from sysptnhdr,systabnames where systabnames.partnum = sysptnhdr.partnum and sysptnhdr.partnum = w_partnum ; for remaining number of extents select physaddr into w_physaddr from sysptntab where partnum = w_partnum ; select s.pg_frcnt/8 into w_rem_extents from syspaghdr s where s.pg_partnum = 0 and s.pg_pagenum = w_physaddr; Since the table structure of sysptntab has changed in 9.40, how is this information gathered in 9.40. On second thoughts, does it even matter. Is there a limit on number of extents in 9.40. All I am aware of is that the limitations of extents, num of pages per table has been vastly increased in 9.40. I need this to customize the script for 9.40 onwards. thanks.

I'm going to have to think this one through. But it seems that what I'm really hearing is that we need add a "max_possible_extents" field to the sysptnhdr pseudo table. M.P. "rkusenet" <rkusenet@sympatico.ca> wrote in message news:c63rj6$7mk55$1@ID-75254.news.uni-berlin.de... > I have a script in stored procedure which calculates, among other things, > the number of extents allocated to a table and the remaining number of > extents it can take before it errors out "no more extents". > > In version prior to 9.40, this is how I calculated:- > > for number of extents:- > > w_partnum is table partnum. > > select nextns > into w_num_extents, > from sysptnhdr,systabnames > where systabnames.partnum = sysptnhdr.partnum > and sysptnhdr.partnum = w_partnum ;> > for remaining number of extents > select physaddr > into w_physaddr > from sysptntab > where partnum = w_partnum ;> > select s.pg_frcnt/8 > into w_rem_extents > from syspaghdr s > where s.pg_partnum = 0 > and s.pg_pagenum = w_physaddr; > > Since the table structure of sysptntab has changed in 9.40, > how is this information gathered in 9.40. On second thoughts, > does it even matter. Is there a limit on number of extents in > 9.40. All I am aware of is that the limitations of extents, > num of pages per table has been vastly increased in 9.40. > > I need this to customize the script for 9.40 onwards. > > thanks. > > > >

That would make life easier :-)))) Madison Pruet wrote: > > I'm going to have to think this one through. But it seems that what I'm > really hearing is that we need add a > "max_possible_extents" field to the sysptnhdr pseudo table. > > M.P. > > "rkusenet" <rkusenet@sympatico.ca> wrote in message > news:c63rj6$7mk55$1@ID-75254.news.uni-berlin.de... > > I have a script in stored procedure which calculates, among other things, > > the number of extents allocated to a table and the remaining number of > > extents it can take before it errors out "no more extents". > > > > In version prior to 9.40, this is how I calculated:- > > > > for number of extents:- > > > > w_partnum is table partnum. > > > > select nextns > > into w_num_extents, > > from sysptnhdr,systabnames > > where systabnames.partnum = sysptnhdr.partnum > > and sysptnhdr.partnum = w_partnum ;> > > > for remaining number of extents > > select physaddr > > into w_physaddr > > from sysptntab > > where partnum = w_partnum ;> > > > select s.pg_frcnt/8 > > into w_rem_extents > > from syspaghdr s > > where s.pg_partnum = 0 > > and s.pg_pagenum = w_physaddr; > > > > Since the table structure of sysptntab has changed in 9.40, > > how is this information gathered in 9.40. On second thoughts, > > does it even matter. Is there a limit on number of extents in > > 9.40. All I am aware of is that the limitations of extents, > > num of pages per table has been vastly increased in 9.40. > > > > I need this to customize the script for 9.40 onwards. > > > > thanks. > > > > > > > > -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #