1

I have a database DAT1 running on DB2 version 10.5. I want to enable logging for all CLOB and BLOB columns of my table TAB1. The table was initially created with those columns as NOT LOGGED but now I need to change it to LOGGED.

I know there is the procedure ALTOBJ

that should be able to change this attribute. However I learned form the documentation that this procedure do the following:

The procedure backs up the data of the table being altered, then drops the original table and creates a new version using the DDL statement; the final step loads the stored data back into the new table.

I need to modify many tables and the size of each table normally reaches about 10GB or even more. This means that this operation may require a very large amount of free disk space plus the time needed to create the new table and copy back the data.

I'm wondering if this operation can be achieved also with a simple ALTER TABLE or another command which doesn't involve creating or copying new data.

1

You cannot alter the LOGGED attribute of a LOB column. However, you can alter the table by adding a new LOGGED LOB column, update the new column from the existing column, then drop the original column. It may require less wasted disk space but is unlikely to be faster than using ALTOBJ.

  • Do I need to manually recreate all indexes, constraints etc ... after calling ALTOBJ ? – Bemipefe May 26 '16 at 14:43
  • I believe ALTOBJ will re-create all dependent objects, but it's always a good idea to test first. – mustaccio May 26 '16 at 15:27
  • The drawback is that you have to parse the DDL generated and extract the CREATE TABLE statement which is not easy from SQL. So maybe creating a new LOGGED column is better but you may hit the row length limit which is 32K for DB2 LUW – Bemipefe Jun 16 '16 at 10:00
  • LOB columns are not subject to the row length (page size) limit, because they are stored separately -- unless inlined, of course. – mustaccio Jun 16 '16 at 11:58
  • Yes the problem is that you have to specify an inline length >= 312 and once the column is created you can't decrease the value. You are allowed to only increase the value. – Bemipefe Jun 16 '16 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.