DELETE SQL & data pages

A customer use IDS 10,he will delete some record for coming insert operation on the same table ,because of the disk utilization probme,customer hasn't enouth disk. He want to know how many data pages could be used for the coming insert operation after he run some delete SQL in the same table . thanks for your time.

Hi, I use the next query: SELECT DBINFO('dbhostname') AS hostname,DBSERVERNAME AS ids_name,DBINFO('dbspace', tn.partnum) AS dbspace, tn.dbsname AS database, tn.tabname AS tabname, tn.owner AS owner, ph.rowsize AS rowsize, ph.nrows AS nrows, ph.nextns AS nextns, FORMAT_UNITS(ph.nptotal, 'P') AS total_size, FORMAT_UNITS(ph.npused, 'P') AS used_size, FORMAT_UNITS(ph.nptotal-ph.npused + NVL(free,0), 'P') AS free_size,CEIL((((ph.nptotal-ph.npused) + NVL(free,0))/ph.nptotal)*100) AS free_perc FROM sysptnhdr ph, systabnames tn LEFT OUTER JOIN (SELECT pb_partnum AS partnum, COUNT(*) AS free FROM sysptnbit WHERE pb_bitmap = 0 GROUP BY 1 ) AS pb ON pb.partnum = tn.partnum WHERE tn.partnum = ph.partnum AND tn.dbsname = '<DATABASE>' AND tn.tabname = '<TABLENAME>; Keen regards On Wed, 7 Oct 2015 at 14:56 CHUAN LU <luchuan@cn.ibm.com> wrote: > A customer use IDS 10,he will delete some record for coming insert > operation > on the same table ,because of the disk utilization probme,customer hasn't > enouth disk. > He want to know how many data pages could be used for the coming insert > operation after he run some delete SQL in the same table . > thanks for your time. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3b870e5f6b80521848201

If the deleted and inserted rows are all fixed length (so no VARCHAR, LVARCHAR, BYTE, TEXT, BLOB, or CLOB columns) and in the same table, then the new rows will just use the freed space vacated by the deleted rows until that is exhausted. If the rows are variable length, the engine will try to fit new data into the vacated slots, but it may not be able to do that always and may allocate additional pages. How many is unpredictable without a detailed scan of the table's contents and knowledge of the data being deleted and inserted. If he is deleting from one table in the hopes of freeing up space so he can insert into a different table, then in v10.00 he will have to unload thepurged table's remaining data, drop and recreate the table with a smaller set of extents, then reload to data in order to be able to reuse the deleted row space in another table. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Oct 7, 2015 at 9:56 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > A customer use IDS 10,he will delete some record for coming insert > operation > on the same table ,because of the disk utilization probme,customer hasn't > enouth disk. > He want to know how many data pages could be used for the coming insert > operation after he run some delete SQL in the same table . > thanks for your time. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0149c0c608398f0521848305

I've only saw now that you are using IDS 10. On Wed, 7 Oct 2015 at 15:25 Ricardo Henriques < ricardoaireshenriques@gmail.com> wrote: > Hi, > I use the next query: > SELECT > DBINFO('dbhostname') AS hostname,> DBSERVERNAME AS ids_name,> DBINFO('dbspace', tn.partnum) AS dbspace, > tn.dbsname AS database, > tn.tabname AS tabname, > tn.owner AS owner, > ph.rowsize AS rowsize, > ph.nrows AS nrows, > ph.nextns AS nextns, > FORMAT_UNITS(ph.nptotal, 'P') AS total_size, > FORMAT_UNITS(ph.npused, 'P') AS used_size, > FORMAT_UNITS(ph.nptotal-ph.npused + NVL(free,0), 'P') AS free_size,> CEIL((((ph.nptotal-ph.npused) + NVL(free,0))/ph.nptotal)*100) AS free_perc > FROM > sysptnhdr ph, > systabnames tn LEFT OUTER JOIN > (SELECT pb_partnum AS partnum, COUNT(*) AS free > FROM sysptnbit > WHERE pb_bitmap = 0 > GROUP BY 1 > ) AS pb ON pb.partnum = tn.partnum > WHERE > tn.partnum = ph.partnum > AND tn.dbsname = '<DATABASE>' > AND tn.tabname = '<TABLENAME>; > > Keen regards > > On Wed, 7 Oct 2015 at 14:56 CHUAN LU <luchuan@cn.ibm.com> wrote: > > > A customer use IDS 10,he will delete some record for coming insert > > operation > > on the same table ,because of the disk utilization probme,customer hasn't > > enouth disk. > > He want to know how many data pages could be used for the coming insert > > operation after he run some delete SQL in the same table . > > thanks for your time. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11c3b870e5f6b80521848201 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01227d940ae5fc0521848d4c

This is a vague question, but if I understand it correctly, you would like to know how many pages you would recuperate after a delete of x rows in order to insert y new rows. First of all this is complicated to calculate without having more details since the table could have one or more indexes of different sizes and also might contains varchars and may be blobs. Also we need to know the size of the page (4K for AIX for example and 2K for the HP, SOLARIS and LINUX). A 2K page has 2020 bytes usable. A 4K page has 4068 bytes usable. *Let us assume that yout page is a 2 K page and assume that your table does not not any varchars not special columns such as blobs. Let us also assume that you do not have indexes. I am also assuming that your row fits tottaly in the page and does notgo beyong 2K bytes.* A page is 2048 bytes and has 2020 bytes usuable to store rows. A row takes the rowsize + 4 bytes on the page. So if your row is 130 bytes, you need to save 134 bytes for the row. Your page will allow 15 rows to be stored in that page. For the rest, you can do the math? If you delete N rows and insert N+x rows, you will need the space for the extra x rows since the system is able to reuse the space freed after the delete operation. However, if you delete more rows than you will be inserting, the space freed in the pages will still be part of the space of that table aunles you you reorganize the table. Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Tél: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 07/10/15 15:56, CHUAN LU a écrit : > A customer use IDS 10,he will delete some record for coming insert operation > on the same table ,because of the disk utilization probme,customer hasn't > enouth disk. > He want to know how many data pages could be used for the coming insert > operation after he run some delete SQL in the same table . > thanks for your time. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

I'm not sure if I'm missing the point, but as long as the rows are fixed length, then you should be able to insert as many records as you delete. So if you delete 1 million records, you should be able to insert another 1 million records and use (roughly) the same amount of space. Then all you care about is how many MORE records you plan to insert than you deleted. Of course, if you have variable length records, the record size exceeds a page, or if the table has blobs, etc in it, then it gets complicated. Still, if you want to get an idea of how many pages would be freed up by a deleted, then the best thing would be to get a COUNT of the number of records that will be performed by the delete, and then calculate the number of pages that those rows would consume. First calculate the number of records that can fit on a page. For this I am going to assume that the size of the row is less than 1 page. Run the following query against the sysmaster database and supply the name of the database and table: select trunc((i.ti_pagesize-28)/(i.ti_rowsize+4)) rows_page from systabnames n, systabinfo i where n.partnum = i.ti_partnum and dbsname = "<database_name>" and tabname = "<table_name>" If this value exceeds 255, then use 255 for the calculation. (Remember that if the table contains varchars, blobs or other data types, then this won't work for you) Now that you know how many records fit on a page, the number of pages freed by your delete will be: number_of_rows_to_delete/rows_per_page You only asked about data pages...if you have problems with indexes, then that's another story... Regards, Mike Walker Advanced DataTools Corporation -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of CHUAN LU Sent: Wednesday, October 07, 2015 7:56 AM To: ids@iiug.org Subject: DELETE SQL & data pages [35833] A customer use IDS 10,he will delete some record for coming insert operation on the same table ,because of the disk utilization probme,customer hasn't enouth disk. He want to know how many data pages could be used for the coming insert operation after he run some delete SQL in the same table . thanks for your time. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Don't forget that a lot depends on the distribution of the rows and the physical distribution of the rows being deleted. On Wednesday, October 7, 2015 10:33 AM, Mike Walker <mike@advancedatatools.com> wrote: I'm not sure if I'm missing the point, but as long as the rows are fixed length, then you should be able to insert as many records as you delete. So if you delete 1 million records, you should be able to insert another 1 million records and use (roughly) the same amount of space. Then all you care about is how many MORE records you plan to insert than you deleted. Of course, if you have variable length records, the record size exceeds a page, or if the table has blobs, etc in it, then it gets complicated. Still, if you want to get an idea of how many pages would be freed up by a deleted, then the best thing would be to get a COUNT of the number of records that will be performed by the delete, and then calculate the number of pages that those rows would consume. First calculate the number of records that can fit on a page. For this I am going to assume that the size of the row is less than 1 page. Run the following query against the sysmaster database and supply the name of the database and table: select trunc((i.ti_pagesize-28)/(i.ti_rowsize+4)) rows_page from systabnames n, systabinfo i where n.partnum = i.ti_partnum and dbsname = "<database_name>" and tabname = "<table_name>" If this value exceeds 255, then use 255 for the calculation. (Remember that if the table contains varchars, blobs or other data types, then this won't work for you) Now that you know how many records fit on a page, the number of pages freed by your delete will be: number_of_rows_to_delete/rows_per_page You only asked about data pages...if you have problems with indexes, then that's another story... Regards, Mike Walker Advanced DataTools Corporation -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of CHUAN LU Sent: Wednesday, October 07, 2015 7:56 AM To: ids@iiug.org Subject: DELETE SQL & data pages [35833] A customer use IDS 10,he will delete some record for coming insert operation on the same table ,because of the disk utilization probme,customer hasn't enouth disk. He want to know how many data pages could be used for the coming insert operation after he run some delete SQL in the same table . thanks for your time. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.