Hi,

I am using ALTER TABLE NOT LOGGED INITIALLY with EMPTY table option to truncate the tables instead of DELETE for intermediate tables.

e.g. Target table - -- Intermediate Tables ----- Source Tables

As per requirement developers has written the pgm to commit at the end of pgm if everything is successful otherwise rollback all the tables[target and intermediate].

Due to Alter table ....NOT LOGGED ..... [truncate] looks like entire table gets locked and after rollback, these tables becomes inaccessible. The only option is to drop and re-create the table.

The alternative is to use delete instead of truncate but deleteing from big tables would slower the process and we do not want to use delete... Is there any way or alternative to use truncate and take care even if there is rollback???

We are using DB2 UDB v8.2 for linux.

Would appreciate if any help of suggestions on this.

Thanks
Diwakar

import from a empty file

If you use a ALTER TABLE NLI to truncate the table then commit immediately to avoid 'table not accessible' problem ..

If you want to rollback the 'truncate' command, I'm afraid , it is not possible. ..The only reason truncate is fast is because it does not log .. Without logging, rollback is not possible ...


Cheers
Sathyaram

Rahul,

Yes import with empty can be done.. how to I do that within SQLs. I can do it from db2 promt.

Sathya: We are not rollbacking the intermediate table for which we have alter table nli....It seems it is not commiting immeditatley. I am using ALTER TABLE NLI empty table to truncate the table.

Thanks
Diwakar

well in our application we create batch file from front end(Java) with appropriate statements and then call that batch file

you havnt mentioned ur db2 level
if u are on db2 9 , u can use sysproc.admin_cmd() procedure to import.

If you are not using autocommit, then issue a commit statement .. Remember, any changes done previously in the same UOW also gets committed ...
If using import, remember, IMPORT also commits automatically and cannot be controlled

Sathyaram

IMPORT is a DB2 command and not a SQL statement. Therefore, you cannot execute IMPORT through JDBC, which only allows SQL statements. To overcome this, you can wrap the invocation of the db2Import() API into a stored procedure:

http://www.ibm.com/developerworks/db...ein/index.html

The ADMIN_CMD procedure also allows that kind of functionality:

http://publib.boulder.ibm.com/infoce...c/r0012547.htm