asked on

Defining custom type calculated fields for IBM DB2 database tables

I'd like to define a custom field type, MYTYPE, that is calculated according to a formula (e.g. FIELD1+FIELD2). I'd like to be able to add fields of MYTYPE to DB2 database tables.

Oracle refers to this ability as virtual column. More info can be found here:
http://www.dba-oracle.com/oracle11g/oracle_11g_function_based_columns.htm

I am not sure if DB2 views can achieve the same purpose. Views can define a column for the calculated fields, but may not allow associating the calculation with a custom type. Having a custom type is important because it allows keeping the formula definition in a single place and seemlessly propagate the updates.

Kent Olsen

Hi cyber,

You can create your own data types in DB2. Generally, these are structure objects similar to C struct objects.

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/c0005011.htm

The CREATE TYPE statement is used to actually create the type:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/c0005013.htm

Good Luck,
Kent

cyber-33

ASKER

Hi Kdo, I saw the UDTs before, but I don't think they can be calculated according to a formula. In other words, the UDT types seem to be static. Please, correct me if I am wrong.

Kent Olsen

Yes. As far as I know they are static.

You could always create a Materialized View / Materialized Query Table over the existing table. A ton more flexible with the potential to be a lot more efficient, too.

Kent

cyber-33

ASKER

Yeah, the view is an option, but as stated in the question, I don't think views allow formula-based custom types. Do they?

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyber-33

ASKER

Kdo - thank you.

I was afraid that virtual columns are not supported in DB2 and you have confirmed it. The question is resolved.