Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2003
    Posts
    74

    Arrow Unanswered: The transaction log for the database is full

    I have user the following SQL statement in my program.
    insert ino a select * from b, The table b has 6 million records and more over 60 fields. After I execute the SQL statement. The DB2 returns the SQLCODE -964.
    I have changed the table a and b to NOT LOGGED INITIALLY, but the result is still error!

  2. #2
    Join Date
    Jan 2003
    Posts
    74
    db2 get db cfg for dbname

    ...
    Log retain for recovery enabled (LOGRETAIN) = OFF
    ...
    User exit for logging enabled (USEREXIT) = OFF
    ...

  3. #3
    Join Date
    Sep 2002
    Posts
    456

    Re: The transaction log for the database is full

    Depending on what the parameters of LOGRETAIN or USEREXIT are set to, you need to sought a different path:
    >> If any of the parameter is set to ON then make sure your system as enough space to write the logs
    >> If none of the parameter is set to ON i.e. circular logging is enabled then make sure the value of LOGPRIMARY and LOGSECOND parameter is set to high. Also the parameter LOGFILESZ should be looked after.

    You can calculate the log space and then allocate appropriate log files.


    Paul

    Originally posted by zhouhaiming
    I have user the following SQL statement in my program.
    insert ino a select * from b, The table b has 6 million records and more over 60 fields. After I execute the SQL statement. The DB2 returns the SQLCODE -964.
    I have changed the table a and b to NOT LOGGED INITIALLY, but the result is still error!

  4. #4
    Join Date
    Jan 2003
    Posts
    74

    Arrow

    Thank you very much! I will try it.

  5. #5
    Join Date
    Jan 2003
    Posts
    74
    What you have said is tried-and-true. But it's too slow. Does anyone konw more faster way? Please tell me!

  6. #6
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    Zhouhaiming,

    just start a "search" in this forum for "log full". This problem was handled in many threads.
    Rodney Krick

  7. #7
    Join Date
    Jan 2003
    Posts
    74
    I have searched by the keyword "+log +full" and I have used all the method. But DB2 still returns the -964 SQLCODE. Please expert tell me how to resolve this problem.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    6 million records, 60 fields is big to fit into a single transaction ...

    HAve you thought about using EXPORT and IMPORT/LOAD ?

    Cheers

    Sathyaram

    Originally posted by zhouhaiming
    I have searched by the keyword "+log +full" and I have used all the method. But DB2 still returns the -964 SQLCODE. Please expert tell me how to resolve this problem.

  9. #9
    Join Date
    Jan 2003
    Posts
    74
    sathyaram_sŁŹ Do you know how to let DB2 not to log. Because I have finished to compile all the programs on my PC environment. It's a developing environment. But when I migrate my programs to the true environment(AIX4.3.3 and DB2 v7.2) the problem is raised. My programs are the ETL(Extract, Transform, Load) code. All the programs transact much data.

  10. #10
    Join Date
    Mar 2003
    Posts
    343
    First, create the target table with the exact structure of the source table, but minus all indexes (including PK) and with the not logged initially option and the partitioning key defined. I have a script for this which I can copy in tomorrow. What you have to do then is
    a] in your session, turn not logged initially on
    b] run the select ... insert into ....with autocommit off.
    c] Run commit as a separate statement(this will turn not logged initially off)
    d] Create indexes for the target table.(index builds are logged)
    e] If you want to rename the target table to the name of the source table, then they must be in the same schema

    In order to do this, you will have to build the sql statements as variables in the script and then run them all as one statement with the +c option,

    ie db2cmd1=alter table schema.tab1 activate not logged initially
    db2cmd2=insert into schema.tab1 select * from schema.tab2

    db2 +c -tf "${db2cmd1}; ${db2cmd2}"

    RISKS : If there's something wrong with the sql statement, you risk the target table being inaccessible. It will not come up in status in syscat.tables.

    If you have a partitioned database, this works very fast since it works on all nodes in parallel. We had to migrate a 2 TB partitioned database last year and using this strategy we managed to get the migration doen in 14 hours. 6 million rows should be closer to an hour in a partitioned environment. It might take longer in a non-partitioned environment but I don't think it will be that much longer. If the data is available in lod files, autoloads will definitely be faster. However exports will take quite a bit of time unless you parallelize the export.

    I will copy in the script later.

  11. #11
    Join Date
    Jan 2003
    Posts
    74
    Thank you very much. I have resolved this problem by your method.

    1)First, I create my table with the keyword of NOT LOGGED INITIALLY. This table I have created will be done DML(insert, delete or update) operation. The following is my code:create table ods.t_base_data_1
    (CDWBH char(6),
    ISBNY integer,
    ...
    NDSDF5 decimal(11,2),
    NYHDF decimal(11,2),
    IYCBH smallint,
    sdate char(7),
    etldate date
    )
    in userspace1
    not logged initially;

    2)Edit the odd_ods.db2 file in DB2 instance user's HOME path. The content of the file odd_ods.db2 is
    update command options using c off;
    connect to dl user db2inst1 using db2inst1;

    alter table ods.t_base_data_1 activate not logged initially;
    call append_by_date('odd.t_base_data_1', 'ods.t_base_data_1', 'ISBNY', '0001-01', ?, ?);
    commit;

    3)invoke the following command
    db2 -tf odd_ods.db2

    Comment:the odd_ods.db2 is in the current path.

    4)There are 6929924 records in the odd.t_base_data_1. It takes 3 hours to do this work. Our machine is IBM,7025-F80 with 512M memory and 2 CPU.

    5)Appendix, the SQL Stored Procedure append_by_date is
    create procedure append_by_date(in source varchar(128),
    in target varchar(128),
    in s_sdate varchar(128),
    in t_sdatevar char(7),
    out error_code int,
    out error_label varchar(50))
    language sql
    begin
    declare SQLCODE int default 0;
    declare at_end int default 0;
    declare stmt varchar(2000);
    declare col_name varchar(128);
    declare all_col varchar(1000);

    declare c1 cursor for
    select colname
    from syscat.columns
    where tabschema = ucase(substr(source, 1, posstr(source, '.') - 1))
    and tabname = ucase(substr(source, posstr(source, '.') + 1, length(source)))
    order by colno;

    declare EXIT HANDLER FOR SQLEXCEPTION
    set error_code = SQLCODE;
    declare CONTINUE HANDLER FOR NOT FOUND
    set at_end = 1;

    set error_code = 0;

    set stmt = 'delete from '||target||' '||
    'where sdate = '''||t_sdatevar||'''';
    set error_label = 'The position of the error raised is 0001.';
    prepare ps from stmt;
    set error_label = 'The position of the error raised is 0002.';
    execute ps;

    set at_end=0;
    set all_col = '';
    open c1;
    fetch c1 into col_name;
    while at_end = 0 do
    set all_col = all_col||col_name||',';
    fetch c1 into col_name;
    end while;
    close c1;

    if t_sdatevar != '0001-01' then
    set stmt = 'insert into '||target||' '||
    'select '||all_col||''''||t_sdatevar||''', '''||char(current date, iso)||''' '||
    'from '||source||' '||
    'where '||s_sdate||' = '||substr(t_sdatevar, 1, 4)||substr(t_sdatevar, 6, 2);
    set error_label = 'The position of the error raised is 0003.';
    prepare ps from stmt;
    set error_label = 'The position of the error raised is 0004.';
    execute ps;
    else
    set stmt = 'insert into '||target||' '||
    'select '||all_col||''''||t_sdatevar||''', '''||char(current date, iso)||''' '||
    'from '||source;
    set error_label = 'The position of the error raised is 0005.';
    prepare ps from stmt;
    set error_label = 'The position of the error raised is 0006.';
    execute ps;

    set stmt = 'update '||target||' '||
    'set sdate = substr(char('||s_sdate||'), 1, 4)||''-''||'||'substr(char('||s_sdate||'), 5, 2)';
    set error_label = 'The position of the error raised is 0007.';
    prepare ps from stmt;
    set error_label = 'The position of the error raised is 0008.';
    execute ps;
    end if;

    set error_label = '';
    end @

Posting Permissions

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