Computation using SQL

Priya Shankar

Computation using SQL
Hi Team,
We have DB2 V8.1 running on Z/os. We plan to
use a dummy table with one dummy row to do some
computations using SQL as follows

a=2
b=3
Select ((a+b)/2) as TOT from dsn1.dummytab

The idea is to execute a formula dynamically using
SQL. This formula cannot be executed in a COBOL / host
language program as they are stored in DB2 tables as
such and has to be picked up dynamically at run time
by the host language program. Also the formula changes
everyday.

My question is
1. Will this cause extensive IO to the dummy table or
will DB2 intelligently understand that it need not
perform IOs to the table.

2. We plan to execute the above SQL for about 3
million times in a single run for various formulas .
Do you see any performance issues here ? Also in some
cases the calculation is expected to return 3 decimal
places as results.

Thanks in advance for all your help.

Regards
Siva



____________________________________________________________________________________
Need a quick answer? Get one in minutes from people who know.
Ask your question on www.Answers.yahoo.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Glen Sanderson

Re: Computation using SQL
(in response to Priya Shankar)
The SET statement is the most cost efficient as the program does not
need to go into a table to do the calculation.

However, I wonder why you just do not do the calculation while you get
the value(s) from the tables?

For decimal places, use the DECIMAL command to force DB2 to use 3 (or
more) decimal places.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Priya Shankar
Sent: December 4, 2006 11:19
To: [login to unmask email]
Subject: [DB2-L] Computation using SQL

Hi Team,
We have DB2 V8.1 running on Z/os. We plan to
use a dummy table with one dummy row to do some
computations using SQL as follows

a=2
b=3
Select ((a+b)/2) as TOT from dsn1.dummytab

The idea is to execute a formula dynamically using
SQL. This formula cannot be executed in a COBOL / host
language program as they are stored in DB2 tables as
such and has to be picked up dynamically at run time
by the host language program. Also the formula changes
everyday.

My question is
1. Will this cause extensive IO to the dummy table or
will DB2 intelligently understand that it need not
perform IOs to the table.

2. We plan to execute the above SQL for about 3
million times in a single run for various formulas .
Do you see any performance issues here ? Also in some
cases the calculation is expected to return 3 decimal
places as results.

Thanks in advance for all your help.

Regards
Siva



________________________________________________________________________
____________
Need a quick answer? Get one in minutes from people who know.
Ask your question on www.Answers.yahoo.com

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


"MMS <safeway.com>" made the following annotations.
------------------------------------------------------------------------------
Warning:
All e-mail sent to this address will be received by the Safeway corporate e-mail system, and is subject to archival and review by someone other than the recipient. This e-mail may contain information proprietary to Safeway and is intended only for the use of the intended recipient(s). If the reader of this message is not the intended recipient(s), you are notified that you have received this message in error and that any review, dissemination, distribution or copying of this message is strictly prohibited. If you have received this message in error, please notify the sender immediately.

======

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John McKown

Re: Computation using SQL
(in response to Glen Sanderson)
> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email] On Behalf Of Priya Shankar
> Sent: Monday, December 04, 2006 11:19 AM
> To: [login to unmask email]
> Subject: [DB2-L] Computation using SQL
>
>
> Hi Team,
> We have DB2 V8.1 running on Z/os. We plan to
> use a dummy table with one dummy row to do some
> computations using SQL as follows
>
> a=2
> b=3
> Select ((a+b)/2) as TOT from dsn1.dummytab
>
> The idea is to execute a formula dynamically using
> SQL. This formula cannot be executed in a COBOL / host
> language program as they are stored in DB2 tables as
> such and has to be picked up dynamically at run time
> by the host language program. Also the formula changes
> everyday.
>
> My question is
> 1. Will this cause extensive IO to the dummy table or
> will DB2 intelligently understand that it need not
> perform IOs to the table.
>
> 2. We plan to execute the above SQL for about 3
> million times in a single run for various formulas .
> Do you see any performance issues here ? Also in some
> cases the calculation is expected to return 3 decimal
> places as results.
>
> Thanks in advance for all your help.
>
> Regards
> Siva

Is this dynamic SQL? I'm confused because you say that the formula is
stored in the database and can vary. Does that mean that one day the
formula:

(a+b)/2

and tomorrow the formula might be

(a+b+c)/d

???

Do you do a SELECT to retrieve the textual formula? I did the following
SELECT in a another RDMS product (I don't have DB/2) and it worked. It
does not require a table to be defined at all:

SELECT (A+B)/2 FROM (
SELECT 2 AS A, 3 AS B) AS DUMMY;

I think you could also do:

SELECT (A+B)/2 FROM (
SELECT :HOST-A AS A, :HOST-B AS B) AS DUMMY;

Sorry if I'm really off-track, but I am very confused!

--
John McKown
Senior Systems Programmer
HealthMarkets
Keeping the Promise of Affordable Coverage
Administrative Services Group
Information Technology

This message (including any attachments) contains confidential
information intended for a specific individual and purpose, and its
content is protected by law. If you are not the intended recipient, you
should delete this message and are hereby notified that any disclosure,
copying, or distribution of this transmission, or taking any action
based on it, is strictly prohibited.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John McKown

Re: Computation using SQL
(in response to John McKown)
> -----Original Message-----
> From: Priya Shankar [mailto:[login to unmask email]
> Sent: Monday, December 04, 2006 12:15 PM
> To: McKown, John
> Subject: Re: [DB2-L] Computation using SQL
>
>
> Hi John,
> Many thanks for your time and seasonal
> greetings. Yes the formula can vary daily. In your
> example yes day one it can be a+b/2 and second day it
> could be a+b-c etc. An yes the formulas are stored in
> a column of a parameter table.
>
> Thanks Again & Cheers
> Siva

Thanks for the off-list reply. But it does lead me to some futher
questions.

I assume the "dummy" table contains columns which are the "variables" in
the formula, in addition to the actual formula itself. Will the possible
variables vary in the formula, or will they always be part of a "fixed
set". That is, the variable will always be, for instance, named "a"
through "j". A given formula might not reference all of the possible
variables, but it would not reference any other variable other than "a"
through "j".

If the names of the variables themselves vary outside of a given range,
how would the program know what they are and how to assign values to
them?

If the names of the variables are always in a given range, then I assume
that the program will "set" the all the possible formula variables to
some value before doing the calculation SELECT. True? That is, the
program will not know which variables need to be set to perform the
calculation, so it will set all of them ("a" through "j", in my
example).

I may need to "bow out" at this point due to my ignorance of DB/2 and
any "nifty" way to do things with it. Also, I'm really far from your
original question about DB/2 being smart enough to not actually do any
I/O to the table. I just found your question very interesting and I had
never considered doing anything like it.

--
John McKown
Senior Systems Programmer
HealthMarkets
Keeping the Promise of Affordable Coverage
Administrative Services Group
Information Technology

This message (including any attachments) contains confidential
information intended for a specific individual and purpose, and its
content is protected by law. If you are not the intended recipient, you
should delete this message and are hereby notified that any disclosure,
copying, or distribution of this transmission, or taking any action
based on it, is strictly prohibited.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm