CODEWITHSUNDEEP
netezza
user3726851
user3726851

Reputation: 1

Purge records from netezza tables

Delete record older than 20 days from Netezza tables. Procedure will take table_name and timestamp as an input. Does any one know procedure for this?

Upvotes: 0

Views: 1282

Answers (3)

J_Arthur
J_Arthur

Reputation: 75

Couple options based on the final goal:

  • You can flag with a logical delete(e.g., ACTV_ROW = 0)

    UPDATE SET ACTV_ROW = 0 WHERE ROW_DATE < (CURRENT_TIMESTAMP - 20)

  • You could DELETE based on a WHERE clause, this would logically delete within the netezza system though, records are not completely gone until the table is groomed

useful link on deletes

Upvotes: 0

Varun Bajaj
Varun Bajaj

Reputation: 1043

A procedure can be written but it should be noted that Netezza is a DW solution and works well with large data, Your system performance can be hit painfully by delete method instead One option i can suggest is create another temp table with the data you need to retain and drop older data table after processing rename temp table with old table name if you need same table name due to some reason.

Steps for my suggestion -

step 1 => Create table <temp_tbl_name> as select <fields> from <tbl_name> where <timestamp_field> > 20 days
step 2 => drop table <tbl_name>
step 3 => alter <temp_tbl_name> to <tbl_name>

Race condition -

For above solution before following the steps you need to acquire lock on the table

You can write following steps in shell/Java/ or DB procedure as your wish and comfort level.

Hope this will help.

Upvotes: 1

user3740191
user3740191

Reputation: 1

Groom table is the command for it

Upvotes: 0

Related Questions