2uzhan.com
Advertisement
Now Place:2uzhan.com » The transaction log for the database is full

The transaction log for the database is full

DB2 @ April 6, 2003   Views:0

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!

--------------Solutions-------------

db2 get db cfg for dbname

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

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

Thank you very much! I will try it.

What you have said is tried-and-true. But it's too slow. Does anyone konw more faster way? Please tell me!

Zhouhaiming,

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

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.

6 million records, 60 fields is big to fit into a single transaction ...

HAve you thought about using EXPORT and IMPORT/LOAD ?

Cheers

Sathyaram

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.

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.

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 @

Tags:
© 2018 2uzhan.com Contact