Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007
    Posts
    33

    Unanswered: Error for ALTER Table with not logged initially empty table

    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

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    import from a empty file
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jan 2007
    Posts
    33
    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

  5. #5
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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.
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you are not using autocommit, then issue a commit statement .. Remember, any changes done previously in the same UOW also gets committed ...
    It seems it is not commiting immeditatley. I am using ALTER TABLE NLI empty table to truncate the table.
    If using import, remember, IMPORT also commits automatically and cannot be controlled

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by diwakar123
    Yes import with empty can be done.. how to I do that within SQLs. I can do it from db2 promt.
    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
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •