MVSFORUMS.com

[nadh]

Hi

I have to write a query which produces a result set with a range of values.
eg: suppose i have 1,2,3,4,5 with sql i need to get 2,3,4.

My exact problem is as below

Table definition is as follows

Name Datatype Null Option Primary key
PRM_NO DECIMAL(2) NOT NULL PK
PRM_KEY CHAR(16) NOT NULL PK
CO_CD CHAR(2) NOT NULL --
PRM_VAL VARCHAR(100) NOT NULL --

Index details

Name Clustered Unique Bufferpool Columns
IA02613A Yes Yes BP0 PRM_NO, PRM_KEY

cursor i have written is
EXEC SQL
DECLARE TA02613A-CURP1 CURSOR FOR
SELECT PRM_NO,
PRM_KEY,
CO_CD,
PRM_VAL
FROM DB2.TA02613A
WHERE
CONCAT(SUBSTR(CHAR(PRM_NO),2,2),PRM_KEY)> :VA1-PAR-SEQ
AND
CONCAT(SUBSTR(CHAR(PRM_NO),2,2),PRM_KEY)< :VA2-PAR-SEQ
END-EXEC.

problem here is, it is working fine, but As you use the Concat word, DB2 cannot use the index. so client questioned it.

As this is written for online application i have to modify it to improve the performance. One more hurdle here is VA1-PAR-SEQ and VA2-PAR-SEQ fields are char(18) fileds. so i concatinated and written.

Pls. help me out to write a query which uses the index.

Thanks in advance
nadh

[kolusu]

Nadh,

why do you have to concatenate ? can't you just define it indivudually ?

[nadh]

Thanks Kolusu,

But it did not worked out. I will get the values into the VA1-PAR-SEQ and VA2-PAR-SEQ from previous screen which is char(18) from IMS. I have to retrieve the data from DB2.TA02613A in which the variable is splitted into two columns as you specified, but PRM_NO AS DECIMAL(2) NOT NULL PK AND PRM_KEY CHAR(16) NOT NULL PK BOTH COMBINEDLY UNIQUE i.e. index built as prm_no+prm_key.

Index details

Name Clustered Unique Bufferpool Columns
IA02613A Yes Yes BP0 PRM_NO, PRM_KEY

so in application prm_no will be changing in va1 and va2. it can be same also.
with the query you specified i'm not getting the desired result which i got with the query i specified using with concatination. But client is saying that it will use tablescans so it will degrade the performance. So he is asking to rewrite it without concatinating.
i tried with the below one
SELECT PRM_NO, PRM_KEY, CO_CD, PRM_VAL
FROM DB2.TA02613A WHERE PRM_NO BETWEEN 02 AND 02
AND
pRM_KEY BETWEEN '101 01'AND '201 01'
i'm getting the result but the one which is satisfying the '02101 01'. But i should not get the one.
Pls. help me out.

Thanks in advance
nadh

[beginner]

Nadh,

Try this:

EXEC SQL
DECLARE TA02613A-CURP1 CURSOR FOR
SELECT PRM_NO
,PRM_KEY
,CO_CD
,PRM_VAL
FROM (SELECT PRM_NO, PRM_KEY, CO_CD, PRM_VAL
FROM DB2.TA02613A
WHERE PRM_NO BETWEEN :VA1-PAR-SEQ
AND :VA2-PAR-SEQ
)
AS T1
WHERE T1.PRM_KEY BETWEEN :VA1-PAR-KEY AND :VA2-PAR-KEY)
END-EXEC

[nadh]

Hi,

Here BOTH THE VARIABLES VA1-PAR-SEQ AND VA2-PAR-KEY ARE CHARACTER.
WHEREAS PRM_NO IS DECIMAL AND PRM_KEY IS CHAR(16) SO THE SQL IS GIVING ERROR.

PLS. HELP ME OUT

THANKS IN ADVANCE
NADH

[beginner]

Nadh,

What's the constraint in defining VA1-PAR-SEQ as numeric?

[nadh]

hi,

VA1-PAR-SEQ AND VA2-PAR-SEQ IS NOT NUMERIC IT IS CHAR(18).BUT PRM_NO IS DECIMAL AND PRM_KEY IS CHAR(16). INDEX IS BUIL ON PRM_NO+PRM_KEY.

i HAVE WRITTEN THE QUERY

SELECT PRM_NO
,PRM_KEY
,CO_CD
,PRM_VAL
FROM DB2.TA02613A
WHERE PRM_NO = 02.
AND (PRM_KEY > '101 01'
AND PRM_KEY < '201 01');

IT IS GIVING THE DATA WHAT I WANT. BUT IT IS INCLUDING THE
02101 01. EXCEPT THAT EVERY THING I'M GETTING IS PERFECT WHY THAT ROW IS INCLUDED I'M NOT GETTING

NADH

[kolusu]

Nadh,

Please do not post in uppercase. Posting in upper case is like shouting. The reason for picking the 201 01 key might there is a space between 201 and 01 in your select clause. May be the table is having a low-value instead of a space.

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