Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    2

    Unanswered: Please advice on "SQL0964C The transaction log for the database is full."

    Hi All this is my first post here Nice to talk to you guys.

    Please give some advice on following Error Message on DB2 DPP version8.1

    "SQL3306N An SQL error "-964" occurred while inserting a row into the table.

    SQL0964C The transaction log for the database is full. SQLSTATE=57011"

    The situation is

    - I have 2 Table and .IXF Exported file with 100M rows (total size approzimate 15GB)
    - Table1 is non partition (DPP) the IXF is imported to the table completely within 2hr
    - Table2 is pastitioned on This TS Structure

    <<CREATE REGULAR TABLESPACE GTEST_DATA2_TS
    IN PG03
    PAGESIZE 4096
    MANAGED BY DATABASE
    USING( FILE '/entis01/ENTISDATA/gtest_data2_ts_f001.dbf' 1310720 )
    ON NODE (0)
    USING( FILE '/entis02/ENTISDATA/gtest_data2_ts_f002.dbf' 1310720 )
    ON NODE (1)
    USING( FILE '/entis03/ENTISDATA/gtest_data2_ts_f003.dbf' 1310720 )
    ON NODE (2)
    USING( FILE '/entis04/ENTISDATA/gtest_data2_ts_f004.dbf' 1310720 )
    ON NODE (3)
    EXTENTSIZE 64
    PREFETCHSIZE 64
    BUFFERPOOL ENTIS_L_BP1
    OVERHEAD 12.67
    TRANSFERRATE 0.18
    DROPPED TABLE RECOVERY OFF
    ;
    >>

    with Same Structure as Table1 and datafile of each partition is enought (5GB).
    But I get "SQL0964C The transaction log for the database is full. SQLSTATE=57011"" within 2 mins after submit import command to Table2

    The log file of Database for your information
    - log primary is 30
    - log secondary is 100
    - logfilesize 4K 256000
    Total size for primary log is 30GB (4096*30*256000)
    Size of Table1 After import is 15GB
    And I test run the command alone in the Database that mean all resource is spend by me only..
    Please advice what occur why log full and How it full in 2min?

    After I look into log path only one file is updated with import command submited time stamp.. only one log file touched but feedback with full message?? Why That?

    <<
    $ ls -ltr
    total 61440488
    -rw------- 1 db2inst1 db2iadm1 512 Aug 30 20:00 SQLLPATH.TAG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000029.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000028.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000027.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000026.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000025.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000024.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000023.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000022.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000021.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000020.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000019.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000018.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000017.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000016.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000015.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000014.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000013.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000012.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000011.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000010.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000009.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000008.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000007.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000006.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000005.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000004.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000003.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000002.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 09:37 S0000001.LOG
    -rw------- 1 db2inst1 db2iadm1 1048584192 Aug 31 11:21 S0000000.LOG
    >>

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    the error states you have too small log files. Increase log files, target that all of data will fit into primary log files. Secondary logs for performance reason are for urgency only.

    Also check why the logs get filled. Maybe more frequent commit is required.

    Hope this helps,
    Grofaty

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    When using the import command, use the COMMITCOUNT option. I would recommend a value of 1000. See the Command Reference manual for details.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Aug 2007
    Posts
    2
    Hi,
    I just confuse why in non partition table 100Million rows is inserted fine,
    But Error in only few thousand of recorded load in DPP Table.

    And How Can I set commit record for import command?

Posting Permissions

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