If you use the not logged initially "feature" a bit will be set for the entire life of the table. An added "feature", a foreign key constraint cannot be defined on a table that references a parent with the NOT LOGGED INITIALLY attribute.
If you still want to use not logged initially, use db2 +c option at the alter, to ensure no logging. If you have an error in any part of script that issues this command EVERYTHING rolls back. The only way to turn the bit off is to get IBM to write custom code. They may relax this in the future. -----Original Message----- From: Eldon B Tucker [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 27, 2001 1:14 PM To: [EMAIL PROTECTED] Subject: DB2EUG: resetting a table after getting SQL1477N Say I have a table that I want to completely repopulate. It may hold 1/2 to 1 million rows. I'd like to directly populate it in the database using the SQL "select into", but don't want a logged transaction. I can create the table with "not logged initially", then when I'm ready to populate the table, at the start of a transaction, issue the command alter table MINE.MYTABLE activate not logged initially ; I can then do the SQL INSERT INTO, and it will put unlogged rows in the table. (And then I do a COMMIT, of course.) Should something go wrong in the insert statement, the table is left in a bad state, and I'll get a SQL1477N error in trying to access it. The documentation that I've seen mentions that the table must be dropped and recreated at this point. My question is: Is there a way to restore the table to an normal state again, truncated, without having to drop and recreate it? I'm wondering about this because I see situations in certain batch programs where use of the "not logged initially" would make sense, except that the batch programs are not allowed to drop and create tables in our production environment. If there were a command that I could use, I could run it just before the alter table activate not logged initially and SQL insert. This would insure the table was always accessible regardless of the status from a previous job. -- Eldon Tucker ===== To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod ===== To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
