Computed columns in DB2

[Henning N?rg?rd]

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

[Jan M. Nelken]

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

[Serge Rielau]

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

[Henning N?rg?rd]

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

[Serge Rielau]

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
%