Results 1 to 9 of 9

Thread: db2 load errors

  1. #1
    Join Date
    Nov 2007
    Posts
    72

    Unanswered: db2 load errors

    Hi,
    DB2 Level - IBM DB2 UDB V9 ESE
    OS LEVEL - IBM AIX 5,3
    ETL TOOL- IBM DATASTAGE
    Architecture - IBM Balanced Configuration Unit (BCU) 2 databcu's and 1 adminbcu

    I am trying to load data into db2 Base table from stage table via db2 load but whenever i abruptly interrupt the job following issues come :-

    1. table goes in backup pending .
    2 .datastage diagnostic file says db2 failed to get table partition information error.SQL0002N .
    3.SQL0290N tablespace access not allowed.

    Regards,

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You're "loading" a table from another table?

    You sure?

    What the command?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2007
    Posts
    72
    its not a command it'a datastage job being executed ..

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Maybe a load from cursor?

    Load does not go to the log (at least not for the data being loaded). Thus, if the load is terminated, the table cannot be recovered and DB2 doesn't know what to do. So you have to restore the table somehow. (You are sure the state is "Backup Pending"?)

    In any case, it would be helpful if you show us the exact statements and which error/success messages you get respectively.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    should be recovery pending

    I hate interfaces

    Are you sure you're going from table to table and file to table?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Don't know how Datastage is configured in this particular case, but typically there is a Datastage agent application on the database server that invokes the DB2 LOAD API directly, so there is no LOAD command issued.

    From the available information my guess would be that the load in fact completes, which brings the tablespace into the backup pending mode. An obvious solution then should be to back the tablespace up, no? If the load were terminated it would leave the tablespace in the load in progress mode.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Nov 2007
    Posts
    72
    Hi,


    Answers to your doubts:-

    1.yes it is from table (stage database) to table of (base database ) .stage database was loaded with files.
    2.since its the datastage API which invokes an agent for load, there is not much manual intervention from dba side ...? is there some parameters in db2 or datastage which can prevent this error.
    3.the tablespace state is backup pending .
    4. sometimes it shows failed to get table partition information error.which i have searched on the internet didnt got any substantial reply.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I can't make much sense of the failure to get the table partition - some more details are needed for that.

    But as n_i said, you have to take a backup after a load. Since a load is not logged, DB2 will not be able to recover the table (with archival logging) unless you did a LOAD COPY or you take a backup of the database or tablespace. Without those actions, DB2 cannot recover the table to the current state by restoring an old backup image and applying any logs on top of that. The loaded data is simply missing.

    Overall, this makes sense to me now: you start the load, then interrupt data stage. DB2 still completes the load process, which (rightfully) puts the tablespace into backup pending state. But the corresponding backup step of data stage is not executed because you interrupted it before. Hence, you have to take manual actions to fix your system state.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    If you are doing a full refresh and keep a data file long anough for a regular back up take place run load with NONRECOVERABLE. Once it is done your table is still fully functional.

    The only down side for this is if you experience a crash, after recovery you would have to drop/recreate this table. But if you have data and table ddl who cares. Well, I guess it also depends on your arrangement.
    --
    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
  •