table sizing problem

greenspun.com : LUSENET : DBAzine : One Thread

Dear Friends, Hi, I am facing the foll. problem. We have an Payroll application and the processing is done every month-end. During this processing there are many rows inserted in various tables. Many a times it happens that one or another table's max extents is reached and this leads to error in processing. Sad part is, the application has no proper error notifying process, so the problem has to be debugged in its depth.

Can you suggest me some SQL queries which can be run before processing to find out the table(s) nearing their max. extents. This would help us a lot.

Thanks in advance

-- mihir sanghani (mihir.sanghani@sisl.co.in), May 30, 2001

Answers

If you are working with DB2 for OS/390 then information on the number of extents per data set is not available in the system catalog, and therefore you can not obtain that information using a SQL query. You can get the information from your storage management software - volume by volumne.

If you run the STOSPACE utility against your DB2 storage groups (and you use STOGROUPs), you can find the SPACE information by table space in the SYSIBM.SYSTABLESPACE system catalog table. If your table space is not partitioned, you might be able to compare the SPACE against the PRIQTY and SECQTY columns in SYSIBM.SYSTABLEPART to determine how much space each table space is using. The SPACE column is recorded as number of kilobytes but the PRIQTY and SECQTY columns are recorded as number of pages (which are 4K for most table spaces). You would need to come up with query that grabs these values, manipulates the data so that it is comparable, and then determines how many extents should exist. For this you will need to keep in mind that DB2 takes an additional extent when the current extent has free space less than half the size of the next extent to be taken.

Good luck, Craig S. Mullins

-- Craig S. Mullins (Craig_Mullins@BMC.com), June 05, 2001.


To findout the remaining number of max extents u could use the following sql statement as DBA:

select e.segment_name "Table Name", e.tablespace_name "Tablespace", ( t.max_extents - count(e.extent_id) ) "Remaining extents" from dba_extents e, dba_tables t where e.segment_name = t.table_name and e.segment_type = 'TABLE' group by e.segment_name, e.tablespace_name, t.max_extents

-- Keshava Murthy D G (dgkmurthy@yahoo.com), June 10, 2002.


Thats fine!! to findout the remaining number of max extents u could use the following sql statement as DBA:

select e.segment_name "Table Name", e.tablespace_name "Tablespace", ( t.max_extents - count(e.extent_id) ) "Remaining extents" from dba_extents e, dba_tables t where e.segment_name = t.table_name and e.segment_type = 'TABLE' group by e.segment_name, e.tablespace_name, t.max_extents;

at the same time one more complex DBA procedure u have incorporate in your day to day administrative activity - that is to measure the growth rate each table object and ensure that growth value of each object is matching with an estimated value of "NEXT EXTENT" parameter of the respective object.

For example: if an object is growing about 50k per month then, u need to size the next extent of that object to atleast 3 to 6 months, then the next extent value is going to be: 50K x 3-6 months = approximatly from 150k to 300k...

Conclusion: if the next extent value of an individual data object is not sized properly and it is configured to some what lesser value, the object might consume all the max extent values in a single day..

-- Keshava Murthy D G (dgkmurthy@yahoo.com), June 10, 2002.


Moderation questions? read the FAQ