Comments (5)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 lelle12 commented Permalink

Hi Serge. In my experience generated columns are mostly used for performance improvements, but at the same time they bloats the data model with unnecessary information. The ability to hide them removes this obstacle, very nice!

 
My other objection against generated columns compared to functional indexes is that they are rather cumbersome to add. Take a simple schema as example:
 
create table emp
( emp_no int not null primary key
, name varchar(20) not null ) ;
 
create table emp_room
( room_no int not null
, emp_no int not null
references emp
, primary key (room_no, emp_no) ) ;
 
adding a generated column in emp, say:
 
alter table emp add column ucase_name generated always as ( ucase(name) )
 
means
 
1. dropping the foreign key (or emp_room will be put in check pending)
2. set integrity for emp off
3. alter table emp add column ucase_name generated always as ( ucase(name) )
4. set integrity for emp immediate force generated
5. reorg table emp (better safe than sorry)
6. restore foreign key
 
Even though it is easy to automate via scripts, it still requires more work than adding a functional index. Are there any plans to ease up this process?
 
Cheers
/Lennart

2 SergeRielau commented Permalink

Lennart,

 
At this point I'd rather implement index on expression than further improve generated columns.
The reason why we chose the SET INTEGRITY route was because adding a the column inevitably requires an update of every row. For an ALTER TABLE that was un-precedented.
In the days of limited log space there even was a utility to update the generated column incrementally.
We could probably remove the SET INTEGRITY requirement.
But, again, I rather add the index on expression. After all DB2 zOS has it for many years now.
 
Cheers
Serge
 

3 Frederik Engelen commented Permalink

Hello Serge,

 
I had success in the past using this type of generated columns, but now I cannot seem to get the optimizer to use the generated column in the background. The function I'm using is:
 
CREATE OR REPLACE FUNCTION SEARCH_STRING (IN INSTR VARCHAR(16336))
RETURNS VARCHAR(16336)
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN cast(strip(COLLATION_KEY_BIT(INSTR, 'CLDR181_AS_S1'), TRAILING, X'00') as VARCHAR(16336));
 
When applying an equals predicate on the original column - SEARCH_STRING(SRC_COLUMN) = ? -, the function is not expanded in the optimized statement. It shows:
 
"WHERE (VARCHAR($INTERNAL_FUNC$(),16336) = ?) "
 
Is this the reason why he doesn't use the generated column automatically? Do you have any suggestions to make it work?
 
Thanks in advance,
 
Frederik Engelen

4 SergeRielau commented Permalink

Frederik,

 
This is news to me and worthy of a PMR.
 
Cheers
Serge

5 ParulJ commented Permalink

Hi Serge,

 
Can you please give some attention on this one regarding generated columns ?
https://www.ibm.com/developerworks/forums/thread.jspa?threadID=469487&tstart=0
 
Thanks,
Parul