Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    34

    Unanswered: DB2 Unload - Desctructive Read

    Is there any option on the UNLOAD utitlity where I can specify to delete the rows from the table which I am unloading like a desctructive read in an MQ.

    The delete statement which I am using to delete the rows is taking longer time because of the volume of data(3 million deletes), so I was looking for any option in the unload utility itself. Our process unloads the data from the table with the key and then feed to a module whcich reads the key field from the file and delete the data and load with the new set of data. We do a commit point every 500 records and the process takes around 3 to 4 hours to delete 3 million records. Trying to speed up the process.

    Any constructive input is highly appreciated..

    Thanks,
    Gopu

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    There may be a TRUNCATE statement for your version+platform for db2

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    load replace with a dummy file.
    Dave

  4. #4
    Join Date
    Jul 2003
    Posts
    34
    I cannot do the LOAD replace with dummy file because it will delete all the records. My table has over 100 million records and I need to delete around 3 million records based on some keys every day.

    What will be the best way to acheive this without using too much resource.

  5. #5
    Join Date
    Jul 2003
    Posts
    34
    TRUNCATE has a different function here in DB2, not like the same as in oracle,

    TRUNCATE or TRUNC
    The TRUNCATE function returns the first argument, truncated as specified. Truncation is to the number of places to the right or left of the decimal point this is specified by the second argument.

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Doh! The TRUNCATE statement is what you were told. But you looked at a function. Different things. Use the Knowledge Centre properly.

  7. #7
    Join Date
    Jul 2003
    Posts
    34
    @db2mor, sorry I quoted the wrong one. I copy pasted the truncate function from the knowledge center.

    I cannot use the TRUNCATE statement because it deletes all the data from the table, I want to delete only few records(3 million) from a table which has 100 million records.

    Any other solution other than physical delete using commit frequency.

  8. #8
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    New facts emerging....better to follow full disclosure (supply ALL relevant facts ) when asking for help.
    For a table with 100-million rows, consider using range-partitioning. Simply detach (and later drop) the unwanted partitions when data is no longer needed.
    If the table is already range-partitioned, but the partitioning columns don't match the deletion criteria, then you are stuck with the DELETE statement.
    I notice you failed to specify basic facts: your db2 version+fixpack, operating-system-platform etc, which also influence possible solutions.
    Depending on the criticality of the data and ease of recovery of the table, 'not logged initially' is possible but not recommended for production.

Tags for this Thread

Posting Permissions

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