Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2005
    Posts
    15

    Unanswered: Transaction Log Full creating new Table

    DB2 8.2 is the backend to our Vignette Imaging System. I am using the Vignette Admin Console to simply create a new Table. When I go to Commit the table through their tool, I get the SQL0964C error.

    My current setup:
    LOGFILSIZ=4096
    LOGPRIMARY=10
    LOGSECOND=100

    I tried updating LOGSECOND to 200, stop/start DB2, but I don't see it changing. Also, LOGRETAIN and USEREXIT are both OFF.

    Is there a better way to clean this up?

    Thanks

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    check if the drive have enough space left
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Oct 2005
    Posts
    15
    Yes, plenty of disk space on the drive. Not sure why the parameter will not increase, although those values seem pretty large considering nothing ever happens on this system. It's Test, and used for mostly inquiry. We increased them when we did a mass load of data from our Production system into this one.

    Now a simple table create bombs. Can the logs be purged or simply turned off?

    Thanks

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Is the tool tyring to load some data in the same unit of work as creating the table? Why don't you create the table directly in DB2. There is no way that a simple table create without data can cause the logs to fill up.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Oct 2005
    Posts
    15
    I'm waiting for the vendor to get me more details about what it is doing. There's no data being loaded, which like you said, should not fill up the logs. I'll also check with them about creating it through DB2. This subsystem may not see, or be able to manage it if not created in the tool. It's very proprietary.

    Until I get more info from them, all I know is when I commit the table, even before adding the fields, just the ones they put in, it fails and i get the SQL0964C error in the error logs.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Do you know if they use CLI or JDBC? If so, you could collect a CLI trace and see which operations are to be executed besides the CREATE TABLE statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Oct 2005
    Posts
    15
    I could use an ODBC trace. There are several table updates going on behind the scenes, but nothing very big, and I cannot change how that process works anyway.

    I guess what I really need to know is just how can I clear out this transaction log, or give it more space? I tried to increase LOGSECOND but it does not seem to take.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The log files are released for reuse when all transactions (units of work) have been commited that are contained on that particular log.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Oct 2005
    Posts
    15
    As far as I can tell all Commits have been completed. I have stopped and started the DB2 database, and still get the transaction log full error when trying to run this process that creates a couple of tables, and updates a couple more.

    Is there no way to purge them?

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No, the logs are needed for crash recovery (in case of system failure). But only uncommited transactions are needed, and DB2 will release the logs for reuse as soon as everyone has commited (or a parituclar log file has no uncommited transactions on it).

    Anytime you stop the intance and restart it, the logs are released (unless crash recovery is in progress).

    You should double check to see if there is sufficient disk space on the path were the logs are located. Also, check the dates on the log files to see when the last one created is.

    BTW, do you have anything set for LOGARCHMETH1 ? If so, then you are not using circular logging.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Oct 2005
    Posts
    15
    That parameter is set to OFF. Should it be on? Seems like based on what I'm reading it should work, but doesn't, and there's not too much I can do besides increase the size of the logs to help it.

    I will check the disk space out too, but last I looked we had plenty available.

    Thanks

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Marcus_A
    But only uncommited transactions are needed, ..
    Just a small correction: During crash recovery, DB2 needs the log records from uncommitted and committed transactions. The log records from uncommitted transactions are needed to rollback those transactions and undo any changes done so far. For committed transactions, DB2 must ensure that any changes are made persistent in the database (which may not yet have happened). Thus, it is necessary to re-apply those log records.

    Back to the original question: Circular or archival logging doesn't make any difference here - assuming that enough disk space (1GB for logfilsiz = 4096 and logprimary+logsecond ~= 250) is available. What do you mean with "plenty" being available (output of "df -m ." in the log directory would help us)?

    Something is occupying all the log space. Maybe some other applications are running concurrently and do not commit/rollback? How did you determine that all transactions have committed?

    Do you do anything else besides using this proprietary tool? What does "db2 list applications" show you? Is there anything interesting in the db2diag output (set the diaglevel to 4 before collecting this information)?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Oct 2005
    Posts
    15
    Space stats:

    $ df -m
    Filesystem MB blocks Free %Used Iused %Iused Mounted on
    /dev/hd4 128.00 89.70 30% 2449 11% /
    /dev/hd2 5376.00 609.73 89% 48317 26% /usr
    /dev/hd9var 128.00 111.79 13% 476 2% /var
    /dev/hd3 384.00 373.27 3% 235 1% /tmp
    /dev/hd1 128.00 35.01 73% 74 1% /home
    /proc - - - - - /proc
    /dev/hd10opt 128.00 69.00 47% 949 6% /opt
    /dev/fslv00 39424.00 31827.48 20% 8961 1% /db2
    /dev/fslv01 2048.00 1118.69 46% 2430 1% /home/runtime
    /dev/fslv02 1024.00 1022.71 1% 6 1% /home/runtime/is/log
    /dev/fslv03 5120.00 5118.90 1% 4 1% /home/runtime/db/dbfiles
    /dev/fslv05 3072.00 3043.89 1% 115 1% /policy
    /dev/fslv06 1024.00 1023.52 1% 4 1% /home/backup
    /dev/fslv04 269312.00 15982.66 95% 2608465 42% /home/runtime/is/files
    $ pwd
    /db2/db2inst1/db2inst1/NODE0000/SQL00002
    $ ls
    SQLBP.1 SQLDBCONF SQLOGDIR SQLSPCS.2 SQLT0002.0 db2rhist.asc
    SQLBP.2 SQLINSLK SQLOGMIR.LFH SQLT0000.0 SQLTMPLK db2rhist.bak
    SQLDBCON SQLOGCTL.LFH SQLSPCS.1 SQLT0001.0 db2event

    $ db2 list applications

    Auth Id Application Appl. Application Id DB # of
    Name Handle Name Agents
    -------- -------------- ---------- ------------------------------ -------- -----
    IDMDB mmc.exe 1175 C0A82546.J607.00F9C7173124 TOWERDB 1
    DB2INST1 db2bp 966 *LOCAL.db2inst1.071227160145 TOWERDB 1
    IDMDB ODBC4DB2 14 2010C9D8.C35020.F1F1F0F8C5F4 TOWERDB 1
    IDMDB ODBC4DB2 15 2006C398.C35020.F0F7F0F2C1F4 TOWERDB 1
    IDMDB ODBC4DB2 12 2010CEA8.C35020.F1F1F0C4C2F4 TOWERDB 1
    IDMDB ODBC4DB2 13 2010C9D8.C35020.F1F1F0F8C5F4 TOWERDB 1
    IDMDB ODBC4DB2 11 2010CEA8.C35020.F1F1F0C4C2F4 TOWERDB 1
    IDMDB ODBC4DB2 10 2010CEA8.C35020.F1F1F0C4C2F4 TOWERDB 1

    Being a Test system, there is not much running except the processes started by the third party application. The db2diag has a lot of good information that I'll look through, including the log full errors.

    I may force off all applications and then try the process again. And on the Commits, does stopping DB2 force all commits to take place?

    Thanks for all the information!

  14. #14
    Join Date
    Oct 2005
    Posts
    15
    Sorry for the ugly output above.....

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by tunatoo
    And on the Commits, does stopping DB2 force all commits to take place?
    db2stop force will not cause any commits, but it will do a rollback on any uncommited transactions.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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