By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,702 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

Computed columns in DB2

P: n/a
I'm working as software developer mostely om SQL-server platform.
On SQL-server we are using a lot of "Computed Columns"

Does anyone know if that is possible to do in DB2 too ?

I mean - define a column, based on SQL-query in database.
And how - do i do - would like a practic example if possible.

/Henning
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Henning N?rg?rd wrote:
I'm working as software developer mostely om SQL-server platform.
On SQL-server we are using a lot of "Computed Columns"

Does anyone know if that is possible to do in DB2 too ?

I mean - define a column, based on SQL-query in database.
And how - do i do - would like a practic example if possible.

/Henning


Perhaps GENERATED columns is what you are looking for? Search DB2 UDB
Information Centre - or post example what you are trying to achieve.

Jan M. Nelken
Nov 12 '05 #2

P: n/a
Henning,

DB2 supports "expression generated" columns.
CREATE TABLE T (c1 INT, c2 INT GENERATED ALWAYS AS (c1 * 2));
The difference between this feature and the one from SQL Server is that
the values in DB2 are persistent in the table. They are not computed
upon reference, but on update/insert.
SQL Server 2005 calls this PERSISTED
(shame on MS for not following the standard, DB2 Dev worked hard to make
the two varieties compatible)

To achieve the exact same behaviour in DB2 as in SQL Server 2000 you
would use a view.
I am rather curious though: What to you use computed columns in SQL
server for? I never figured out what this SQL Server 2000 feature is
good for....

Cheers
Serge
Nov 12 '05 #3

P: n/a
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2s*************@uni-berlin.de>...
Henning,

DB2 supports "expression generated" columns.
CREATE TABLE T (c1 INT, c2 INT GENERATED ALWAYS AS (c1 * 2));
The difference between this feature and the one from SQL Server is that
the values in DB2 are persistent in the table. They are not computed
upon reference, but on update/insert.
SQL Server 2005 calls this PERSISTED
(shame on MS for not following the standard, DB2 Dev worked hard to make
the two varieties compatible)

To achieve the exact same behaviour in DB2 as in SQL Server 2000 you
would use a view.
I am rather curious though: What to you use computed columns in SQL
server for? I never figured out what this SQL Server 2000 feature is
good for....

Cheers
Serge

Hei Serge,

Tank you for your answer.
My task is to use some rather complex SQL (used for reservation
system) and use the value from SQL as value in field.

For instance i want to do this (simple eksample):

Create a field called "Numbs"
Define a function CalculateNumbs()
This function would look like this in SQL-server:
CREATE FUNCTION dbo.CalculateNumbs (@TYPE AS char(1))
RETURNS decimal (13,0)
AS
BEGIN
DECLARE @ANTAL AS decimal(13,0)
SELECT count(*) as @ANTAL from tabel where itemtype = @TYPE
IF @@ERROR <> 0
SELECT @ANTAL = -2
RETURN @ANTAL
END

This Function CalculateNumbs is used on field Numbs.
The purpose of this is isolating complex SQL rather than doing it in
program.

It has a lot of other atvantaged too, so that why we use it.

But is it possible to do something like this in DB2 ?
/Henning
Nov 12 '05 #4

P: n/a
Henning N?rg?rd wrote:
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2s*************@uni-berlin.de>...
Henning,

DB2 supports "expression generated" columns.
CREATE TABLE T (c1 INT, c2 INT GENERATED ALWAYS AS (c1 * 2));
The difference between this feature and the one from SQL Server is that
the values in DB2 are persistent in the table. They are not computed
upon reference, but on update/insert.
SQL Server 2005 calls this PERSISTED
(shame on MS for not following the standard, DB2 Dev worked hard to make
the two varieties compatible)

To achieve the exact same behaviour in DB2 as in SQL Server 2000 you
would use a view.
I am rather curious though: What to you use computed columns in SQL
server for? I never figured out what this SQL Server 2000 feature is
good for....

Cheers
Serge


Hei Serge,

Tank you for your answer.
My task is to use some rather complex SQL (used for reservation
system) and use the value from SQL as value in field.

For instance i want to do this (simple eksample):

Create a field called "Numbs"
Define a function CalculateNumbs()
This function would look like this in SQL-server:
CREATE FUNCTION dbo.CalculateNumbs (@TYPE AS char(1))
RETURNS decimal (13,0)
AS
BEGIN
DECLARE @ANTAL AS decimal(13,0)
SELECT count(*) as @ANTAL from tabel where itemtype = @TYPE
IF @@ERROR <> 0
SELECT @ANTAL = -2
RETURN @ANTAL
END

This Function CalculateNumbs is used on field Numbs.
The purpose of this is isolating complex SQL rather than doing it in
program.

It has a lot of other atvantaged too, so that why we use it.

But is it possible to do something like this in DB2 ?
/Henning

Of course, but this is not what is called a computed column....

db2 -td%

CREATE FUNCTION dbo.CalculateNumbs (TYPE AS char(1))
RETURNS DECIMAL(13, 0)
RETURN (SELECT count(*) as @ANTAL from tabel where itemtype = TYPE)
%

DB2 also supports simple logic inside the function using BEGIN END.
For complex logic (including error-handling) do this:

CREATE FUNCTION dbo.CalculateNumbs (TYPE AS char(1))
RETURNS DECIMAL(13, 0)
BEGIN ATOMIC
DECLARE ANTAL DECIMAL(13, 2);
CALL dbo.CalculateNumbsProc(type, antal);
RETURN antal;
END
%
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.