Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2002
    Posts
    34

    Unanswered: db2 database log parameters

    I am trying to run a sql delete statement, to basically truncate a table.
    When I try to delete 100K records I get the error "transaction log filled."

    I understand setting the db log parameters is specific to each databases needs, but can someone recommend the following parameters for a table that will hold 500K records and need to be deleted and updated on a regular basis.

    logfilsiz (currently-8000)
    logprimary (currently-25)
    logsecond (currently-100)
    mincommit (currently-1)
    softmax (currently-80)
    logretain (currently-recovery)
    locktimeout(currently-300)
    dlchktime(currently-10000)

    In addition, I am aware that these parameters have impact on the db performance settings. If possible can you point in the direction of a shotgun resource(book, article, white paper, etc) that can get me ramped up quickly.

    Thanks for any and all help,

    Brian Kalberer
    Last edited by briankalberer; 04-03-02 at 10:04.

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,
    Go for the following values of the parameters:

    logfilsiz (currently-5000)
    logprimary (currently-6)
    logsecond (currently-10)
    mincommit (currently-1)
    softmax (currently-80)
    others which u asked for (default)

    You can go for this book: DB2 UDB7.1 Performance Tunig Guide
    http://www.redbooks.ibm.com/redbooks/SG246012.html

    Prashant G Dahalkar
    Prashant

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: db2 database log parameters

    If you want to delete all the records, use IMPORT command giving a blank file as input ...

    Cheers

    Sathyaram

    Originally posted by briankalberer
    I am trying to run a sql delete statement, to basically truncate a table.
    When I try to delete 100K records I get the error "transaction log filled."

    I understand setting the db log parameters is specific to each databases needs, but can someone recommend the following parameters for a table that will hold 500K records and need to be deleted and updated on a regular basis.

    logfilsiz (currently-8000)
    logprimary (currently-25)
    logsecond (currently-100)
    mincommit (currently-1)
    softmax (currently-80)
    logretain (currently-recovery)
    locktimeout(currently-300)
    dlchktime(currently-10000)

    In addition, I am aware that these parameters have impact on the db performance settings. If possible can you point in the direction of a shotgun resource(book, article, white paper, etc) that can get me ramped up quickly.

    Thanks for any and all help,

    Brian Kalberer

  4. #4
    Join Date
    Oct 2001
    Posts
    68
    When I worked with Oracle it had(has) a neat TRUNCATE TABLE statement that cleared all data but did not affect the logs. Now this may be dangerous in a high-availability environment but made batch processing and cleanup very efficient and fast. The IMPORT idea is a good one, but difficult to implement inside an application program.

    WP

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Just wondering wether the admin API for import sqluimpr will help in your environment ....

    Cheers

    Sathyaram

    Originally posted by WPSullivan
    When I worked with Oracle it had(has) a neat TRUNCATE TABLE statement that cleared all data but did not affect the logs. Now this may be dangerous in a high-availability environment but made batch processing and cleanup very efficient and fast. The IMPORT idea is a good one, but difficult to implement inside an application program.

    WP

  6. #6
    Join Date
    Mar 2002
    Posts
    34
    Thank you all for your info. I have used the import cmd and found that it works the best.

    when logged on the server via telnet, I use the following cmd lowercase.

    IMPORT FROM /DEV/NULL OF DEL REPLACE INTO TABLENAME

    Thanks,

    BK

  7. #7
    Join Date
    Oct 2001
    Posts
    68
    Thanks Brian,

    That's a pretty clever tip.

    WP

  8. #8
    Join Date
    Oct 2001
    Posts
    68
    For those of you on NT/2000 this works:

    db2 "import from nul: of dep replace into SCHEMA.TABLENAME"

    Replace SCHEMA.TABLENAME with your descriptor.

    WP

  9. #9
    Join Date
    Mar 2002
    Posts
    34

    sql proc passing variables between cursors

    WPSullivan,

    would you happen to have an example of a sql stored proc that uses 2 cursors that passes a host variable from the outer cusor to the inner cursor?

    Is the PREPARE/EXEC sql stmt the only way to achieve this? or is there a simpler way?

    Thanks,

    BK

Posting Permissions

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