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

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.

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.

I see your point, thanks for the info.

Sincerely Yours