Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Unanswered: db2 import/Load in HADR enviornment

    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

  2. #2
    Join Date
    Oct 2007
    Posts
    246
    user nonrecoverable options in load command which will not put ur tablespaces in backup pending mode

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    However, keep in mind that LOAD ... NONRECOVERABLE on the primary will mark as invalid the corresponding table in the standby database.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111
    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?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  7. #7
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111
    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.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    if you are trying out andy's suggestion, then no problem

    otherwise, use commitcount option in import
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111
    Quote Originally Posted by sathyaram_s View Post
    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,

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by dgunas View Post
    if the transaction log get full, what is the action should be taken please?

    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.

  11. #11
    Join Date
    Oct 2007
    Posts
    246
    Quote Originally Posted by db2girl View Post
    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

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    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.

  13. #13
    Join Date
    Nov 2009
    Posts
    21
    Provided Answers: 1

    transaction log full scenarios

    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

  14. #14
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111
    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.

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Load may need to spill to tempspace's bufferpool and then disk during index key sorting if sortheap is not enough

Posting Permissions

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