Netezza supports internal datatypes that are used by system to perform internal tasks on the table records. Netezza internal datatypes includes: rowid, transaction ID (createxid, deletexid), and dataslice.
Shows Netezza internal Datatypes:
| Internal Datatype | Column Name |
| rowid | rowid |
| transaction ID | Createxid
deletexid |
| dataslice | datasliceid |
Netezza Internal DataTypes: Rowid
Identifies a specific and unique record in the database. These row numbers are not necessarily sequential within a table. Usually, the initial rowid value is 100,000. The Netezza host assigns a range of sequential rowids to each SPU in the system. When you insert records, the system assigns them unique rowids.
When the initial allotment of rowids is out of range, the system start assignment with another range, which is the reason rowids may not be sequential to records inserted in the tables.
Read:
Netezza Internal DataTypes: Transaction ID
Transaction ID includes two ID’s: createxid and deletexid. Createxid identifies the transaction ID that created the record and deletexid identifies the transaction ID that deleted the record. The deletexid would be 0 for newly inserted records. Whenever you issue update statement, system updates the deletexid on the original row with the current transaction ID, and inserts a record with the updated value and preserves the rowid. You need to perform GROOM TABLE or DATABASE to remove those logically deleted records.
A xid is an 8-byte integer value of which 48 bits are significant. At installation, the initial xid value is 1024. The size of the xid allows for over 100 trillion transaction IDs. System can update the deletexid for at most 38 times.
Netezza Internal DataTypes: Datasliceid:
Identifies that portion of the database table stored on each disk. At installation, the system is divided into a logical number of data slices.
Read:
- Importance of Right Distribution Key in Netezza
- Cluster Based Tables (CBT) in Netezza
- Netezza Recover Deleted Rows
When the system creates a record, it assigns it to a logical data slice and then to a physical disk based on its distribution key. Although the system dynamically generates datasliceid values, it does not store them with each individual record. You can use the datasliceid keyword in a query to identify which data slice the records are coming from.
For example:
SELECT DATASLICEID, name FROM your_table;