Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Avoid Logging

  1. #1
    Join Date
    Sep 2015
    Posts
    17

    Unanswered: Avoid Logging

    Hello,

    We have scenarios where we insert multiple rows (more than a hundred thousand). Since these tables are related to reporting and are like work tables, we don't want it to be logged. I suppose we can use 'NOT LOGGED INITIALLY' but I read the problem with this is that during the COMMIT, it moves all the dirty pages to disk and hence the COMMIT may take longer. Also, I read that probably using a load from cursor doesn't do logging. Is that correct?

    Can you please guide the best way by which logging can be avoided?

    Thanks & Regards,
    Dhiraj

  2. #2
    Join Date
    Apr 2012
    Posts
    1,006
    Provided Answers: 16
    Post your DB2-server version+fixpack and operating-system detail for the DB2-server when asking for help.
    Check if the LOAD command (not the import) can be used for these work tables (if you are loading from files or pipes)
    You can also use LOAD if you are loading from a cursor (capabilities depend on your DB2 version+platform).
    The load is unlogged (but you need to be certain about the recoverability aspects)

    Avoid 'not logged initially' (because on any failure you have to drop and recreate the table)

    If they are true work tables you can consider making them DGTT or CGTT and making them unlogged (again, dependin on your version+platform).

  3. #3
    Join Date
    Sep 2015
    Posts
    17
    Okay, Thanks for the information. Below are the required details. They are work tables but we would want to keep the data in the table after the process is run so that if there is any issue in the output report, we can check the data in the table for trouble-shooting. In that case, I suppose DGTT or CGTT may not work. And we don't bother about recovery of these tables because these are related to reports that can be run anytime. So, please suggest accordingly.

    FUNCTION: DB2 UDB, RAS/PD component, pdLogInternal, probe:120
    System: AIX
    CPU: total:72 online:56 Cores per socket:8 Threading degree per core:4 SIMD:Y
    DB2 v10.5.0.6 - Fix Pack "7".

    Please let me know if any further details are required.

    Thanks & Regards,
    Dhiraj

  4. #4
    Join Date
    Apr 2012
    Posts
    1,006
    Provided Answers: 16
    Is there some reason you don't want to use LOAD (from file/pipe/cursor) in this case?

  5. #5
    Join Date
    Sep 2015
    Posts
    17
    Nope, there is no specific reason. I was not sure if logging doesn't happen with LOAD and if there is any downside of using the LOAD command. That is the reason wanted to check the best option. But as you mentioned 'NOT LOGGED INITIALLY' may not be good option and that LOAD doesn't do logging, so would go with LOAD from Cursor option.

    Thanks again for clarifying it.

    Thanks & Regards,
    Dhiraj

  6. #6
    Join Date
    Apr 2012
    Posts
    1,006
    Provided Answers: 16
    Sounds like you need to study the docs

  7. #7
    Join Date
    Sep 2015
    Posts
    17
    Can you please provide few links? I have tried to search a lot but I couldn't get detailed information.

    Thanks & Regards,
    Dhiraj

  8. #8
    Join Date
    Apr 2012
    Posts
    1,006
    Provided Answers: 16

  9. #9
    Join Date
    Sep 2015
    Posts
    17
    Thanks for providing the link. There seems to be some, am not able to open the link (need to activate Java script). I will check the information.

    Wanted to also check if there is a way logging can be disabled while deleting the records. Can you please guide on this too?

    Thanks & Regards,
    Dhiraj

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    load replace with empty file?
    Dave

  11. #11
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Regards,
    Mark.

  12. #12
    Join Date
    Sep 2015
    Posts
    17
    Thanks for prompt responses. I believe the above options to delete would delete the whole data of teh table. What if we need to delete only selected records. Say in a table of 5 Million records, need to delete only a million records need to be deleted (based on value of a column). How can I do that without logging?


    Thanks & Regards,
    Dhiraj

  13. #13
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you can't. why all of this concern about logging? Are you having issues or just think you should try to avoid it?
    Dave

  14. #14
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    You can use table partitioning or MDC.
    To delete a subset of rows you can detach the corresponding partition if you can choose the partitioning key appropriately.
    Deletes from MDC tables can produce significantly less logging than usually.
    Regards,
    Mark.

  15. #15
    Join Date
    Oct 2011
    Posts
    27
    I hit a road block while using load cursor with transaction log full. Its 60mil rows (2 CLOB ) and I had 50 logs with 4 mb each. Load cursor does logging however minimally. I tried with Insert / replace. nonrecoverable/copy no.

Posting Permissions

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