Hello,
DB2 Ver 8.2 on Windows 2003
HADR enviornment.

I want to import nearly 1GB size of data (ASC file contain only one column (24 char)) from the ASC file to the temporary table and I dont need to replicate this data to the STANDBY system.
What is the best option to use for this work?

I know that the import will write to the transaction logs.
However the LOAD will not write to the transaction logs but it will put the tablespace to backup pending mode if I use the COPY NO option.

Please give me some suggestions.

Thanks, D

user nonrecoverable options in load command which will not put ur tablespaces in backup pending mode

However, keep in mind that LOAD ... NONRECOVERABLE on the primary will mark as invalid the corresponding table in the standby database.

Is there any way to halt the HADR then copy the data into the temporary table using IMPORT on PRIMARY and generate my reports then drop the temporary table.
Once I have finish my work RESUME the HADR?

You could do that but the secondary database will attempt to catch up once you resume it, using whatever log records are available. In other words, it won't make any difference to you.

Why not try doing it this way:

1) turn off auto-commit (start a unit of work)
2) create the table with NOT LOGGED INITIALLY
3) Import the data
4) commit

I am not sure if this would work, but it should in theory. None of the inserts into the table will be logged, so they should not go to the HADR server.

You might want to test will a small data size first.

Andy

Thanks for all your ideas and I will try all this on our test db.

However, the text file is only 1GB and the new temp table will have only one column ( varchar(20)). So if I use the import utility how many transaction logs will be created? will this fill up the transaction logs?
The current setup - primary logs 120 and secondary 60 and the logsize 10MB

Thanks.

if you are trying out andy's suggestion, then no problem

otherwise, use commitcount option in import

Hi, this may be a not very good question. However, I wish to clear this.

During the LOAD or any other situation, if the transaction log get full, what is the action should be taken please?

do i need to stop and start the db2? or increase the number of logs ? or log size?

Please clear me as I did not face this issue yet.

Thanks,

You need to find the application that is holding the oldest transaction (db2 get snapshot for database on <db name> | grep -i oldest) and commit/rollback. db2diag.log will contain similar info.

but if you find the agent which is using the max logs, you only have the options to kill it for rollback, you cannot commit it ??
regds
Paul

You can commit if you can "access" the appl that is holding the active log space. For example: if a user forgot to close some GUI window, then they can go back to close it and therefore commit/rollback. If I'm executing something from CLP with autocommit turned off, I can also commit/rollback whatever I'm doing. In most cases, we'd force the application handle holding the oldest transaction / identified in the db2diag.log. Active log space can also be held by dirty pages and some db cfg parameters can be tuned to prevent this from happening.

As far as I understand your question there are 2 corrections. First thing when you a load operation it does not do any kind of logging. So you dont have to worry about logs getting full during load. Only during import of data logging occurs. Second one is When you encounter a log full situation we need to identify the application which is using the maximum logspace. You can issue commit statements if possible or force it off. The other process of allocating number of secondary logs and increasing the logfilesz paramaters comes later.

1. LOGFILSIZ Parameter is not configurable online. When this parameter is updated you may need to deactivate and activate the database.

2. logsecond is configurable online and You can do it on the fly.

Hope this helps a little bit for your question. Correct me if I am wrong.


Thanks

Surgeon

Thanks a lot Sergon. I can now understatnd the process.

When we do the NONRECOVERABLE load, is there are any possibilities to fill up the tempspace?

Thanks.

Load may need to spill to tempspace's bufferpool and then disk during index key sorting if sortheap is not enough