• 3 replies
  • Latest Post - ‏2016-02-14T23:19:15Z by andrewmcl
1 Post

Pinned topic Not Logged Initially Tablespace Corruption Possible?

‏2013-08-29T01:13:31Z |


We're using NOT LOGGED INITIALLY to create a table which act as a workspace for a an app. Since we're perfectly fine in re-creating the table in case of disaster, but we would like the table to persist in case of an application error, this seems like a good solution to avoid excessive transaction logs when populating the table. 

However, we're getting some push back from our DBA team to the effect that, if there is a failure during the NIL transaction, it could impact (very badly) the entire tablespace. I haven't gotten a more precise explanation, and I can't seem to find anything in the IBM docs or on various forums that corroborate this risk. 

Is there any circumstance where the use of NOT LOGGED INITIALLY can corrupt the entire tablespace? Anyone have any pointers to help me understand the risk, or refute the danger? 

Thanks so much,


My understanding is that the risks associated with using NIL are that, if there's a problem with the transaction that isn't being logged, you'll lose your work, and if it's done on create we may need to drop the table on our DR site. That's fine.  

  • nivanov1
    231 Posts

    Re: Not Logged Initially Tablespace Corruption Possible?


    NOT LOGGED INITIALLY affects only the table(s) for which it is turned on, so the tablespace itself and other tables in it should be safe. However, please note that not only a "disaster" will render the NOT LOGGED INITIALLY table inaccessible -- whatever causes the transaction to roll back, including application errors, will also put it in that state, exactly because it cannot be rolled back due to the lack of log information.

  • Henri2398
    2 Posts

    Re: Not Logged Initially Tablespace Corruption Possible?


    NOT LOGGED INITIALLY does not corrupt the entire Database, as this option is related to table and not tablespaces. If you encounter any error during your activity, your table will be flagged as non recoverable and not tablespace. so don't worry here if you can recreate the table and the data.

    However, if you are not able to recover the table space data of your DB2 database then this can help you - How to resolve Table Space corruption in DB2 database.

  • andrewmcl
    119 Posts

    Re: Not Logged Initially Tablespace Corruption Possible?


    If they're hot and bothered just get it moved to it's own tablespace