Hidden Columns in Netezza

Want to view deleted data? See which rows were added in a transaction? These extra fields will allow you to do so. Especially clever users can use these to undo transactions after they are committed or scrutinize distributions and zone maps.

createxid – The ID of the transaction that created the row.

select createxid, * from MY_TABLE;

deletexid – The ID of the transaction that deleted the row. If you use this alone you will see ‘0’ for all rows (because they haven’t been deleted yet!), but by including the ‘show deleted records’ setting with your query you can see deleted rows that haven’t been groomed:

set show_deleted_records=true;

select deletexid, * from MY_TABLE;

datasliceid – The dataslice on which the row resides.

select datasliceid, * from MY_TABLE;

_extentid – The extent on which the row resides. _extentid is not unique across the machine, only within each dataslice.

select distinct datasliceid, _extentid from MY_TABLE;

_pageid – The ID of the page on which the row resides. _pageid is not unique across the machine, only within each dataslice.

select _pageid, * from MY_TABLE;

rowid – A unique identifier for the row. If a row is deleted and re-inserted it will get a new rowid.

select rowid, * from MY_TABLE;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s