467,881 Members | 1,202 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,881 developers. It's quick & easy.

Computed columns in DB2

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
  • viewed: 10991
Share:
4 Replies
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
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
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
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.

Similar topics

1 post views Thread by Paulo Andre Ortega Ribeiro | last post: by
3 posts views Thread by Raymond Du | last post: by
reply views Thread by am72de | last post: by
7 posts views Thread by Aamir Mahmood | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.