Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Best way to offload data from a huge table?

    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!!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2user24 View Post
    Is there an 'ideal' way
    What are your criteria of "ideal"?

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    Ideal meaning what is the best way to accomplish this.. basically the fastest method that is easy if there is such a way..thanks!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The fastest would probably be Optim High Performance Unload for extraction, then a partition detach if your data are partitioned by ID.

  5. #5
    Join Date
    Nov 2007
    Posts
    265
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2user24 View Post
    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?

  7. #7
    Join Date
    Nov 2007
    Posts
    265
    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

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by n_i
    Like I said, just detach the partition with the ID that you want to lose, then drop the detached table.
    That was wrong, disregard.
    Last edited by n_i; 01-17-12 at 22:08.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by db2user24 View Post
    I don't see the NOT LOGGED option in the schema for the table
    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.

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by n_i View Post
    Like I said, just detach the partition with the ID that you want to lose, then drop the detached table.
    How do you detach a partition from this table?

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl View Post
    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.

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by n_i View Post
    I must have been thinking of something else.
    Beer, I guess

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl View Post
    Beer, I guess
    I am predictable, aren't I?

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by n_i View Post
    I am predictable, aren't I?
    By now, I know what you like - beer and dogs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •