SEO

#1 (**permalink**) 02-26-2008, 02:42 PM

I have a table that contains the following columns BIRTH_DATE and AGE.
The table contains other columns too. The AGE column is populated as
zero when the table is loaded. Is there a way DB2 can derive the age
based on the birth date and current date and put the value in the AGE
column whenever someone tries to access the column AGE?

#2 (**permalink**) 02-26-2008, 02:42 PM

"Sam Moore" <sudhin@aol.com> wrote in message
news:a551c85c.0309051102.28f9afd7@posting.google.c om...
> I have a table that contains the following columns BIRTH_DATE and AGE.
> The table contains other columns too. The AGE column is populated as
> zero when the table is loaded. Is there a way DB2 can derive the age
> based on the birth date and current date and put the value in the AGE
> column whenever someone tries to access the column AGE?

Create a view for table that uses a calculated age "year(curentdate -
birth_date)" instead of the real age column. You can call the column "age"
in the view.

Note: You might want the age "cast" into a different datatype and length.

#3 (**permalink**) 02-26-2008, 02:42 PM

> "Sam Moore" <sudhin@aol.com> wrote in message
> news:a551c85c.0309051102.28f9afd7@posting.google.c om...
> > I have a table that contains the following columns BIRTH_DATE and AGE.
> > The table contains other columns too. The AGE column is populated as
> > zero when the table is loaded. Is there a way DB2 can derive the age
> > based on the birth date and current date and put the value in the AGE
> > column whenever someone tries to access the column AGE?
>
> Create a view for table that uses a calculated age "year(curentdate -
> birth_date)" instead of the real age column. You can call the column "age"
> in the view.
>
> Note: You might want the age "cast" into a different datatype and length.
>
Correction: cuurentdate should have been "current date" with the space
added.

#4 (**permalink**) 02-26-2008, 02:44 PM

"Mark A" <ma@switchboard.net> wrote in message news:<4o56b.122$3i.20985@news.uswest.net>...
> > "Sam Moore" <sudhin@aol.com> wrote in message
> > news:a551c85c.0309051102.28f9afd7@posting.google.c om...
> > > I have a table that contains the following columns BIRTH_DATE and AGE.
> > > The table contains other columns too. The AGE column is populated as
> > > zero when the table is loaded. Is there a way DB2 can derive the age
> > > based on the birth date and current date and put the value in the AGE
> > > column whenever someone tries to access the column AGE?
> >
> > Create a view for table that uses a calculated age "year(curentdate -
> > birth_date)" instead of the real age column. You can call the column "age"
> > in the view.
> >
> > Note: You might want the age "cast" into a different datatype and length.
> >
> Correction: cuurentdate should have been "current date" with the space
> added.

Thanks for the suggestion. The only concern that I have with this
approach is how efficient will it be when we do a group by on the age
field. We have many queries group by on certain age range.

#5 (**permalink**) 02-26-2008, 02:44 PM

"Sam Moore" <sudhin@aol.com> wrote in message
news:a551c85c.0309090715.181e7f9a@posting.google.c om...
> "Mark A" <ma@switchboard.net> wrote in message
news:<4o56b.122$3i.20985@news.uswest.net>...
> > > "Sam Moore" <sudhin@aol.com> wrote in message
> > > news:a551c85c.0309051102.28f9afd7@posting.google.c om...
> > > > I have a table that contains the following columns BIRTH_DATE and
AGE.
> > > > The table contains other columns too. The AGE column is populated as
> > > > zero when the table is loaded. Is there a way DB2 can derive the age
> > > > based on the birth date and current date and put the value in the
AGE
> > > > column whenever someone tries to access the column AGE?
> > >
> > > Create a view for table that uses a calculated age "year(curentdate -
> > > birth_date)" instead of the real age column. You can call the column
"age"
> > > in the view.
> > >
> > > Note: You might want the age "cast" into a different datatype and
length.
> > >
> > Correction: cuurentdate should have been "current date" with the space
> > added.
>
>
> Thanks for the suggestion. The only concern that I have with this
> approach is how efficient will it be when we do a group by on the age
> field. We have many queries group by on certain age range.

Accessing the table via a view adds almost nothing to run time of the query.
But as you mentioned, since this particular view has a calculated column, it
does add some overhead each time you execute it. But I think you should try
it and see if there is a big difference.

The only other solution would be to run a batch SQL statement to update the
age each day or at some other interval (depending on how accurate you want
the age to be).

The update SQL statement would be quite simple such as:

Update table
set age = year(current date - birth_date)

Which solution is cheaper depends on a number of factors such as whether you
have a chargeback system (typical on mainframes), how often you run the
queries, etc.

#6 (**permalink**) 02-26-2008, 02:46 PM

"Mark A" <ma@switchboard.net> wrote in message news:<eKm7b.176$M87.41260@news.uswest.net>...
> "Sam Moore" <sudhin@aol.com> wrote in message
> news:a551c85c.0309090715.181e7f9a@posting.google.c om...
> > "Mark A" <ma@switchboard.net> wrote in message
> news:<4o56b.122$3i.20985@news.uswest.net>...
> > > > "Sam Moore" <sudhin@aol.com> wrote in message
> > > > news:a551c85c.0309051102.28f9afd7@posting.google.c om...
> > > > > I have a table that contains the following columns BIRTH_DATE and
> AGE.
> > > > > The table contains other columns too. The AGE column is populated as
> > > > > zero when the table is loaded. Is there a way DB2 can derive the age
> > > > > based on the birth date and current date and put the value in the
> AGE
> > > > > column whenever someone tries to access the column AGE?
> > > >
> > > > Create a view for table that uses a calculated age "year(curentdate -
> > > > birth_date)" instead of the real age column. You can call the column
> "age"
> > > > in the view.
> > > >
> > > > Note: You might want the age "cast" into a different datatype and
> length.
> > > >
> > > Correction: cuurentdate should have been "current date" with the space
> > > added.
> >
> >
> > Thanks for the suggestion. The only concern that I have with this
> > approach is how efficient will it be when we do a group by on the age
> > field. We have many queries group by on certain age range.
>
> Accessing the table via a view adds almost nothing to run time of the query.
> But as you mentioned, since this particular view has a calculated column, it
> does add some overhead each time you execute it. But I think you should try
> it and see if there is a big difference.
>
> The only other solution would be to run a batch SQL statement to update the
> age each day or at some other interval (depending on how accurate you want
> the age to be).
>
> The update SQL statement would be quite simple such as:
>
> Update table
> set age = year(current date - birth_date)
>
> Which solution is cheaper depends on a number of factors such as whether you
> have a chargeback system (typical on mainframes), how often you run the
> queries, etc.

The table has 46 million rows and each row is about 200 bytes. I was
using the following SQL to calculate the age. birth_dt is the column
from the table.

Case
when month(birth_dt) < month(current date) then
year(current date) - year(birth_dt)
when month(birth_dt) > month(current date) then
( year(current date) - year(birth_dt)) -1
when month(birth_dt) = month(current date) and
day(birth_dt) > day(current date) then
( year(current date) - year(birth_dt)) -1
when month(birth_dt) = month(current date) and
day(birth_dt) <= day(current date) then
( year(current date) - year(birth_dt))
end as age

Is there a better way to get the correct age. I am doing this on a IBM
P660 Unix Box running UDB 7.1

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Generated value in a column