MVSFORUMS.com

[chikdu]

Hi,

I have a few questions
QN1:
Is use of functions in DB2 recommended?

For example: there are 2 queries:
QUERY 1:
SELECT A.DATA1
FROM A
WHERE START_DT_TM > :WS-TIMESTAMP

QUERY2:
SELECT A.DATA1
FROM A
WHERE DATE(START_DT_TM) > :WS-TIME

Here START_DT_TM is of data type timestamp.
The input date to the application program is in date format
ie: mm/dd/ccyy which is stored in WS-TIME.
Say the date is 05/06/2005
ie: WS-TIME value will be 05/06/2005
Then WS_TIMESTAMP value will be 2005-05-06-00.00.00.000000

Please let me know which is a better way to code.

QN 2:
I have a query:
If none of the columns mentioned in the join are unique indexes or a part of it which will take more execution time?

QUERY 1:
SELECT A.DATA1, B.DATA2
FROM A,B
WHERE A.ID1= :WS-DATA1
AND B.ID = A.DATA1

QUERY 2:
SELECT A.DATA1
FROM A
WHERE A.ID1= :WS-DATA1

SELECT B.DATA2
FROM A,B
WHERE B.ID = A.DATA1

QN3:
Is there any constraints in the number of joins that can be used? I am asking this because in our application we are only supposed to use a maximum of 4 joins in a single query as recommended by the DBA.

Please let me know.

Thanks.

[kolusu]

Chikdu,

Did you try running explain for the sql statements in your program?

Check this topic ( the last post by me) discusses about EXPLAIN

http://www.mvsforums.com/helpboards/viewtopic.php?t=215

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[videlord]

For QN1:

Use function will cause CPU overhead of couse.

For QN2:
Query 1 is one single query, Query 2 are 2 queries.
do you really need to compare the elpase/cpu time?

For QN3:
Not sure about the limitation of JOINs table. But 4 tables join is permited of course.
There is a limitation of maxmum number of tables in a FROM clause, can't exceed 225 in DB2 V8 for z/OS