Results 1 to 10 of 10

Thread: Load command

  1. #1
    Join Date
    Apr 2010
    Posts
    9

    Unanswered: Load command

    When I try to import some data over Load command I get a message : SQL0964C The transaction log for the database is full. SQLSTATE=57011. Is there any way to solve this?
    I know one way, to increase log size and number of primary and secundary logs but is there any other way because for me isn't possible to increase logs on that mashine. (no enough space)
    Thank's in advance,
    ppkkkcc

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use a nonrecoverable load.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you are using the IMPORT command (I can't tell from your question), use the COMMITCOUNT option keyword (something like 5000 should probably be OK). See the Command Reference manual for more info on this option.
    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
    Apr 2010
    Posts
    9
    My command is:
    db2 load from test.ixf of ixf messages test.log insert into shema1.test

    If I want to use a nonrecoverable load I just need to add nonrecoverable on the end, is that right?
    exsample:
    db2 load from test.ixf of ixf messages test.log insert into shema1.test nonrecoverable

    And what is a bad side, if there is a bad side, of nonrecoverable load?
    And in LOAD command can I specified commincount option?

    tnx

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You cannot specify commitcount with a load.

    The bad side of a nonrecoverable load is that if there is some "major" problem during the load, the table will be marked as corrupted and you will have to drop it. Having rows rejected during the load is not normally a "major" problem.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    The load utility does minimal logging by default (for copy yes/no and nonrecoverable options). The delete phase, if it's invoked, can generate a lot of log records since each delete operation is logged. So, check if there was a delete phase done by the load. if not, then I suspect some other application was holding the active log space and load was just a victim.

  7. #7
    Join Date
    Apr 2010
    Posts
    9
    Quote Originally Posted by Marcus_A View Post
    You cannot specify commitcount with a load.

    The bad side of a nonrecoverable load is that if there is some "major" problem during the load, the table will be marked as corrupted and you will have to drop it. Having rows rejected during the load is not normally a "major" problem.
    If that is only difference that OK for me, because the table is empty and if there is some "major" problem during the load I can drop it and create again.

    Quote Originally Posted by db2girl;
    The load utility does minimal logging by default (for copy yes/no and nonrecoverable options). The delete phase, if it's invoked, can generate a lot of log records since each delete operation is logged. So, check if there was a delete phase done by the load. if not, then I suspect some other application was holding the active log space and load was just a victim.
    I get a message from first post in delete phase. So, I guess that nonrecoverable load doesn't include delete phase?

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Nonrecoverable does not log anything (well, almost nothing), including a delete phase if applicable to a particular load.

    One thing I forgot to mention about a nonrecoverable load is that you can not rollforward a database through a nonrecoverable load without loosing the table that was loaded as nonrecoverable. That means that if you restore a database backup and roll forward the logs active during the time period that a nonrecoverable load takes place (or a crash recovery occurs and logs are rolled forward during that timeframe) then the table will be marked as bad and you will have to drop it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    As far as I know, the delete phase is always logged (even in case of a nonrecoverable load).

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Bella is right on the delete phase. What you also want to consider is even if your load is good but you do not take a backup right after and you crash. Your restore wont have this data and you still would need to drop the table because your load was not logged. And keep in mind that DELETE phase is slow.

    Nonreverable is a great but only if you can afford to take a hit by having to rebuild and repopulate that table.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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