Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    36

    Unanswered: Transation log issue

    Hi,
    I'm using Db2V8.1 on AIX.

    I've a shell script that contains delete operations on 5 tables.
    each table has around 1 million records on average to delete.
    I applied commit statement on each db2 delete statement.

    After executing 4 statements, there is an error "Transacton log for the database is full".
    I've seen the path by using command "db2 get db cfg" and found the below path for transaction log
    "/u02/db2inst1/NODE0000/SQL00001/SQLOGDIR/"

    I asked DBA either to increase the log filesize or increase number of logs or clean up old log files.
    He said there is no error in the file db2diag.log.

    Am I looking in the correct path? How to fix this issue?
    Is there any option to have auto commit after x number of records deleted?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You have two options.

    1) beat you DBA over the head and tell him to increase the size of number of logs or both.

    2) change your script to break up the statements into smaller data chunks. This will not use as much log space, and also perform faster.

    I personally would go with number 2.

    Andy

  3. #3
    Join Date
    Nov 2007
    Posts
    36
    Thanks for information.

    Below is the Logfile configuration.

    Log file size (4KB) (LOGFILSIZ) = 10000
    Number of primary log files (LOGPRIMARY) = 10
    Number of secondary log files (LOGSECOND) = 50
    Changed path to log files (NEWLOGPATH) =
    Path to log files =/u02/db2inst1/NODE0000/SQL00001/SQLOGDIR/

    I went in to the location and below are the logs

    -rw------- 1 db2inst1 db2iadm1 512 Jul 23 15:42 SQLLPATH.TAG
    -rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 03:41 S0133167.LOG
    -rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:03 S0133170.LOG
    -rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:03 S0133169.LOG
    -rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:03 S0133168.LOG
    -rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:03 S0133171.LOG
    -rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:05 S0133172.LOG
    -rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:07 S0133173.LOG
    -rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:10 S0133174.LOG
    -rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 08:18 S0133175.LOG
    -rw------- 1 db2inst1 db2iadm1 38760448 Aug 28 08:18 S0133166.LOG


    It shows that only 10 log files are there. There are no secondary logs are created. DBA tells that only 10% of the space is used. But we are still getting transaction log full error.

    How to know whether all the space is used when there is only 10 log files are created?

    We will be splitting the data to delete but how to rectify the space is used 100%?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look at the monitor elements sec_log_used_top, tot_log_used_top, sec_logs_allocated, uow_log_space_used. This might give you more info.

    Also look here for log related config parameters:

    http://publib.boulder.ibm.com/infoce...n/r0006082.htm

    Andy

Posting Permissions

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