Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
I need to write sql to work with a cobol group level

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Joe Gennero

New User


Joined: 13 Feb 2009
Posts: 1
Location: 48092

PostPosted: Fri Feb 13, 2009 1:58 am    Post subject: I need to write sql to work with a cobol group level
Reply with quote

I need to write a SQL statement that will give me a results set that has rows that is >= to a phone number and <= to another phone number. The only problem my database defines each part of the phone number separately. So my phone number looks like this in DB2:

AT_NPA_NO CHAR(03)
AT_NXX_NO CHAR(03)
AT_STATION_NO CHAR(04)

I have an index made up of these three columns but the sql I’m using to get this results set isn’t picking it up. I don’t have a separate column that has all 10 digit of the phone number in one column.

In my working storage section I’m storing 2 phone numbers (a low and a high number) as follows:
01 ws-phone-low.
05 ws-nxx-low pic x(03)
05 ws-npa-low pic x(03)
05 ws-station-low pic x(04)

Right now my SQL look like the following
SELECT AT_NPA_NO
,AT_NXX_NO
,AT_STATION_ID
FROM PHONET00
WHERE (AT_NPA_NO BETWEEN :WS-NPA-LOW AND :WS-NPA-HIGH)
AND (AT_NXX_NO BETWEEN '000' AND '999')
AND (AT_STATION_NO BETWEEN '0000' AND '9999')
AND (AT_NPA_NO||AT_NXX_NO||AT_STATION_NO
BETWEEN :WS-PHONE-LOW
AND :WS-PHONE-HIGH)
ORDER BY AT_NPA_NO DESC
,AT_NXX_NO DESC
,AT_BLC_ID DESC

This is working but it is using a ton of CPU. It's a large database and using the incorrect key is causing the excessive CPU time.

I know I've seen a formula where the where clause would be split up into multiple parts. I'm just not sure how I would do that. Any idea. We are thinking of adding another column which has the 10 digit phone number but I'm sure there is a way to do it.

Has anyone run into something like this where you have to search on 3 column but need to think of 3 columns as 1 for the select?
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Fri Feb 13, 2009 2:08 am    Post subject:
Reply with quote

Why:
AND (AT_NPA_NO||AT_NXX_NO||AT_STATION_NO
BETWEEN :WS-PHONE-LOW
AND :WS-PHONE-HIGH)
?
Isn't the fourth condition already taken care of by the previous three?
Back to top
View user's profile Send private message
ykishor
Currently Banned

New User


Joined: 11 Aug 2007
Posts: 24
Location: my pc

PostPosted: Sun Feb 15, 2009 7:58 pm    Post subject: how abt this...
Reply with quote

I guess I have understood the requirements clearly: u r using DB2 and COBOL. I guess this will be much faster.

Use a cursor to fetch rows from the table and pass it on to a COBOL variable. Like
AT_NPA_NO to :WS-FIRST
AT_NXX_NO CHAR to :WS-SECOND
AT_STATION_NO CHAR :WS-THIRD

Declare WS-FIRST,WS-SECOND,WS-THIRD under a level 1 variable, so the leve1 variable will have the concatenated form of the phone no.

Now as you fetch the rows from the table pass it to the level 1 variable and compare it using ur condition. write the O/P accordingly.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Assembler Macro Copybook converted to... Ziquilix PL/I & Assembler 0 Tue Sep 26, 2017 3:07 am
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
This topic is locked: you cannot edit posts or make replies. Extract all "IF" Statements... Adarsh Damodaran CLIST & REXX 1 Wed Sep 06, 2017 9:28 am
This topic is locked: you cannot edit posts or make replies. Limit access to certain RACF group cvnlynn CLIST & REXX 5 Wed Aug 23, 2017 2:28 am
No new posts Match two files using multiple keys a... santoshks1987 SYNCSORT 11 Fri Aug 18, 2017 10:50 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us