Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: Uregent Plz - About BLOB,CLOB datatype ?

    Dear Team,

    I am using BLOB and CLOB datatypes. I need to know about their usage and syntax. I have the following doubts.

    1. When 'Not logged' should be added ?
    2. If I use BLOB(100000m) - it gives error - why ?
    3. Is there any range for MB, to use ?
    4. Give some create table examples, with CLOB and BLOB with M,GBs and Not Logged Syntax

    Regards,
    Sam

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    BLOB

    Hi Sam

    1. The 'not logged initially' switch enables the
    table for future updates, deletes and inserts
    without logging - which is useful for very
    large operations. To use it you have to run:
    'alter table <myschema>.<mytable>
    activate not logged initially' inside a UOW
    every time you want to use it.

    2. Are you sure that you want to create a 100 GB
    BLOB column?? (per record!!)

    3. Please refer to the online documentation.

    4. 'create table xyz.abc ( var1 char (3), var2 blob (1m) )
    in "<mytablespace>" not logged initially'

    PS: My brilliant student help insists, that you can use
    the command: 'alter table <mytable> active not
    logged initially' on all tables in DB2 8.1.4 regardless
    of their creation syntax.

    Hope this helps

    BOW
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513

    Re: BLOB

    Originally posted by Tank
    Hi Sam

    1. The 'not logged initially' switch enables the
    table for future updates, deletes and inserts
    without logging - which is useful for very
    large operations. To use it you have to run:
    'alter table <myschema>.<mytable>
    activate not logged initially' inside a UOW
    every time you want to use it.

    2. Are you sure that you want to create a 100 GB
    BLOB column?? (per record!!)

    3. Please refer to the online documentation.

    4. 'create table xyz.abc ( var1 char (3), var2 blob (1m) )
    in "<mytablespace>" not logged initially'

    PS: My brilliant student help insists, that you can use
    the command: 'alter table <mytable> active not
    logged initially' on all tables in DB2 8.1.4 regardless
    of their creation syntax.

    Hope this helps

    BOW
    I think he meant the NOT LOGGED option for LOBs:
    create table testlob (a clob(100M) not logged)

    It means is that the LOB column will not be logged in the transaction logs. This may be acceptable to your application, for instance in the case where you have an external source where you can load the LOBs from in the event of a failure. It basically means any rollforward recovery (ie. after a crash, or after a RESTORE) will leave these columns with '0000' values instead of the actual data.

    The performance impact of logging large LOBs may make logging prohibitive... and you need space in your log files for them, which might also suck up disk space. But if you don't want them lost when the database crashes or requires restore, you may not have a choice.

    More info is likely in the SQL Ref.
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Perhaps

    Yes he might.

    But i still think taht the table creation
    switch is more flexible, then you can decide
    from time to time wether or not to log operations.
    The onloy drawback is that it works on the entire table.

    BOW
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513

    Re: Perhaps

    Originally posted by Tank
    Yes he might.

    But i still think taht the table creation
    switch is more flexible, then you can decide
    from time to time wether or not to log operations.
    The onloy drawback is that it works on the entire table.

    BOW

    There are pros/cons to both approaches, but for most cases I find the table create switch is too dangerous. If you hit an error within a UOW while NOT LOGGED INITIALLY is enabled, the table is dead, all you can do is drop it or restore... the LOB option doesn't have such a penalty.
    --
    Jonathan Petruk
    DB2 Database Consultant

  6. #6
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Re: Perhaps

    I see your point, thanks for the info.

    Sincerely Yours
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

Posting Permissions

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