Topic
  • 1 reply
  • Latest Post - ‏2013-08-29T16:43:15Z by nivanov1
dabramov
dabramov
1 Post

Pinned topic Not Logged Initially Tablespace Corruption Possible?

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

Hi,

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,

-Dan
 

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
    nivanov1
    231 Posts

    Re: Not Logged Initially Tablespace Corruption Possible?

    ‏2013-08-29T16:43:15Z  

    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.