Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2014
    Posts
    2

    Unanswered: NOT LOGGED INITIALLY is slower than with LOGGING on

    I have a procedure in a package that calls this code:

    IF UPPER(LOGFLAG)='Y' THEN
    EXECUTE IMMEDIATE 'ALTER TABLE tname ACTIVATE NOT LOGGED INITIALLY';
    END IF;
    stmnt := 'INSERT INTO tname ( col, col1, col2, col3) SELECT cola, colb, colc ';
    stmnt := stmnt || ' from ori_tname a, tmp2 b where date_col > ''2013-02-02'' '
    EXECUTE IMMEDIATE stmnt;


    The issue I'm seeing is that when NOT LOGGED is activated it is slower than when it is not. The difference is significant. With NOT LOGGED on, moving 2000 rows to the new table takes over 4minutes. When the insert is being logged is takes 50 sconds. Why is this happening?

    v9.7.0.5 <-- db2 version is on Redhat 2.6.18-308.1.1.el5 (Redhat 5.8)

  2. #2
    Join Date
    Aug 2014
    Posts
    2

    Question NOT LOGGED INITIALLY is slower than with LOGGING on

    I have a package that copies rows from one table to another. When I alter the table to be NOT LOGGED initially, the copy is significantly SLOWER then when the table is logged. For instance, coping 200 rows with logging on takes ~58seconds, with logging off is takes over 4 minutes. What is going on?

    I call the package like this:

    db2 +c -v "begin partition.migrate('2013-02-01-00.00.00','2013-02-01-00.00.00','Y'); end;"

    The relavent part of the code:
    IF UPPER(LOGFLAG)='Y' THEN
    EXECUTE IMMEDIATE 'ALTER TABLE tname ACTIVATE NOT LOGGED INITIALLY';
    END IF;
    stmnt := 'INSERT INTO tname ( col, col1, col2, col3) SELECT cola, colb, colc ';
    stmnt := stmnt || ' from ori_tname a, tmp2 b where date_col > ''2013-02-02'' '
    EXECUTE IMMEDIATE stmnt;












    "DB2 v9.7.0.5",
    Red Hat Enterprise Linux Server release 5.8

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    which part of it is taking that long?
    the alter table? waiting on locks? or the insert?

    You should be able to put some breaks in to your code to be able to pinpoint exactly what is taking you longer. Once you know what, then you can investigate why.
    Dave

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
  •