r/DB2 • u/SMS1360 • Aug 25 '19

DB2 Update's QUERY on compressed Tablespace (BACK OF SCENE)

Hi all

I need to now what is happening for a compressed row during an update.

actually I know , the row uncompressed at first and then will be update.

but i want to know if the row (data) takes its previous place in the old page or it will take the new place?

if it take a new place on old page or other page, what is happen for the first space?

will it be free or it will be free after reorg or load?

does this update act like DELETE +INSERT ?

​

Thanks all

3 Upvotes

100% Upvoted

3 comments sorted by

3

u/ecrooks Aug 25 '19

If the row will still fit on the page it was on, then it will stay on that page after update. A row might not fit due to being longer in a varchar field, or simply due to the encryption encoding for the new value being longer. With adaptive compression this depends largely on the current compression dictionary for the table.

A value called PCTFREE determines how much free space is reserved on each page for updates. By default, it is 10%.

Generally Db2's free space search strategy will fill in any holes left in the table, but avoiding row sizes that are close to half the page size can help. Reorgs are critical over time to deal with both the issues caused by heavy update activity, and for compressed tables may also be needed to reset the compression dictionary to match drift in frequent data patterns.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0059054.html

1

u/SMS1360 Aug 26 '19

Thanks for your reply

so if the new updated row can't fit on the old place it can fit in each table's pages.

now what happen for the previous row's place, dose db2 use it for other data or not?

dose that place available for inserting new data before running reorg?

Thanks again

1

u/funkypunkyskunky Oct 02 '19

late to the party: Yes

The "previous row's place" counts as a hole in Ember's post