Do you really need CLOBs? If you strings are always shorter than 32K, just stick with VARCHAR.
Why do you believe it has a huge impact on performance? What did you compare?
Regarding your UPDATE statement - the syntax is not standard SQL and it doesn't make much sense either.
It reads: change a value in column "f1_count" and set the new value to some strange expression that is not a valid subselect or something else. Furthermore, if it were a subselect, there is no correlation defined between Tbl1 for the UPDATE and Tbl1 in the subselect. I understand what the statement tries to do - but it is just not logical. What you need instead is this:
update Tbl1 set f1_count = tmp.freq from
Tbl1 as t1
(select f1, count (*) as freq from Tbl1 group by f1) tmp
However, you should think carefully if you want to use such a design. Your table is not properly normalized and each insert/update/delete affecting the values in column "f1" requires you to run the statement again. Using a separate table for the counts, establishing a referential constraint from "tbl1" to this new table and then applying triggers that update the count automatically seems to be a better way.
UPDATE tbl1 AS o
SET f1_count = ( SELECT COUNT(*)
FROM tbl1 AS i
WHERE o.f1 = i.f1 )
Alternatively, you could try something like this:
UPDATE ( SELECT f1_count,
( SELECT COUNT(f1)
FROM tbl1 AS i
WHERE i.f1 = o.f1 ) AS count
FROM tbl1 AS o ) AS t
SET f1_count = count
IBM DB2 Analytics Accelerator
IBM Germany Research & Development