Hi,

We have a READINGS table that currently has around 828,027,973 records. The table has these fields --

ID INTEGER
RDATE TIMESTAMP
RVALUE DOUBLE


Is there an 'ideal' way to export data for a certain ID and then delete the records from the READINGS table? As an example, one of the ID's has 935122 records and we would like to put the data for this ID in a file and then remove it from the table itself. Any tips would be appreciated..thanks!!

What are your criteria of "ideal"?

Ideal meaning what is the best way to accomplish this.. basically the fastest method that is easy if there is such a way..thanks!

The fastest would probably be Optim High Performance Unload for extraction, then a partition detach if your data are partitioned by ID.

Thanks, will Optim High Performance Unload put a copy of the data in a file or actually move the data to a file? If I were to use the export command to put the data in a file, what would be the fastest way to delete records without filling up the logs, etc.

That would certainly depend on how much log space you have, whether the table was created with the NOT LOGGED option, and your "etc" as well.

Why do you think you need the "fastest" way?

I don't necessarily need the 'fastest' way but just don't want it to take forever, that's all I don't see the NOT LOGGED option in the schema for the table..here is the schema :

CREATE TABLE READINGS (
"ID" INTEGER NOT NULL ,
"RDATE" TIMESTAMP NOT NULL ,
"RVAL" DOUBLE NOT NULL )
DATA CAPTURE CHANGES
PARTITIONING KEY ("ID") USING HASHING
IN "R_TBLSP" ;

-- DDL Statements for primary key on Table "READINGS"

ALTER TABLE READINGS
ADD PRIMARY KEY
("ID", "RDATE");


ALTER TABLE READINGS
ADD CONSTRAINT "SQL0502120241xxxxx" FOREIGN KEY
("ID")
REFERENCES TABLE2
("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;


here are the config paramaters of the db that gives some information on the primary / secondary log files --

Catalog cache size (4KB) (CATALOGCACHE_SZ) = 4096
Log buffer size (4KB) (LOGBUFSZ) = 256
Log file size (4KB) (LOGFILSIZ) = 8000
Number of primary log files (LOGPRIMARY) = 10
Number of secondary log files (LOGSECOND) = 5

That was wrong, disregard.

If you want to avoid 2 table scans or so, you could use "SELECT ... FROM OLD TABLE ( DELETE ... )". But that will do the regular logging for DML operations.

You can alter the table to active NOT LOGGED INITIALLY and execute DELETE in the same UOW. But be careful with NLI, you can loose access to the table if you encounter some error during this operation.

How do you detach a partition from this table?

Uhm.. I don't know why I said that... I must have been thinking of something else.

Beer, I guess

I am predictable, aren't I?

By now, I know what you like - beer and dogs