Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2013
    Location
    India
    Posts
    246

    Unanswered: Exixting LOB Compact

    Dear forum,

    I am using Db2 10.1 on RHEL 5

    i have already created a table with lob data field BLOB (10 M)

    Now i want to Alter the table and make that LOB field COMPACT

    Alter table abc alter column mycol....... COMPACT..

    I am not able to find correct syntax for it . Do any body has an idea about it? Alter the table for compacting LOBs is possible or not ?

    regards
    sumit
    ssumit

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Did you try the DB2 doc for 10.1 on ALTER TABLE? BTW, you will take a performance hit on any LOB columns defined as compact.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Yes i have gone through, But i am not able to reach the exact syntax. I believe its possible but i am not able to build correct command syntax for that.

    any idea ?

    regards
    sumit
    ssumit

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    so : publish the command that has been tried....
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    ALTER TABLE mytab alter column mycol set data type BLOB (10M) COMPACT
    ssumit

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    reading the syntax diagram and according my opinion changing this option is not allowed..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Thanks, but in a toad for db2 5.0 GUI tool i am able to alter the COMPAT feature of BLOB, So definitely there will be any DDL for the same.

    See attachment.

    Please suggest if i am missing something.

    regards
    Attached Thumbnails Attached Thumbnails BLOB_COMPACT.bmp  
    Last edited by ssumit; 10-31-13 at 05:57.
    ssumit

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ssumit View Post
    Thanks, but in a toad for db2 5.0 GUI tool i am able to alter the COMPAT feature of BLOB, So definitely there will be any DDL for the same.

    See attachment.

    Please suggest if i am missing something.

    regards
    Is it possible that TOAD drops and recreates the table? Can TOAD show you the DDL syntax it uses?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    I am not able see the DDL as it asks for license on "SHOW DDL" and i am using a free version.

    do you think alteration is possible for BLOB COMPACT ?
    ssumit

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    in datastudio this option is not available for change with alter table
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ssumit View Post
    I am not able see the DDL as it asks for license on "SHOW DDL" and i am using a free version.

    do you think alteration is possible for BLOB COMPACT ?
    You should look at the documentation to see if it is supported.

    You can also try a test table alter with TOAD and see if it actually works, and if it does, whether TOAD has to drop and recreate the table.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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