Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

To identify matched records from two tables

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vvgoud

New User


Joined: 16 Aug 2007
Posts: 27
Location: Hyderabad

PostPosted: Thu Sep 18, 2008 7:47 pm    Post subject: To identify matched records from two tables
Reply with quote

Hi,

Could someone help me on this problem.

We have 2 tables keyword table and Description table, keyword table contain keywords and Description table contain Description of some text)

I need to identify matched records of Description table, which are matching the keywords of KEYWORD table.

For example Keyword table contain 1000 keywords and Description table contain 500 records so searching possibility is

1000 * 500 = 50000 times my query should execute, so processing time will take more if do this through cobol program.


My idea is combining all keywords at a time and searching in the description field is any possibility? if we do it how to do this?

So I am not familiar DB2 Cobol and DB2 procedures need any idea can we take care about this from database side other than cobol program.

see example of my two tables two rows

Keyword table
----------
Engine
break
Light
-----

Description table
-------------
Engine Starter is not working
Front and back breaks damaged
head lights are broken

Please give some ideas
Back to top
View user's profile Send private message

Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Thu Sep 18, 2008 10:24 pm    Post subject:
Reply with quote

Hi

First of all, please let us know what your trying to achieve with this. Whether you want only to check the existence or write the results in to some file or etc..

vvgoud wrote:

For example Keyword table contain 1000 keywords and Description table contain 500 records so searching possibility is

1000 * 500 = 50000 times my query should execute, so processing time will take more if do this through cobol program.


If you have 1000 records in Keyword table and 500 from Description table, then how come it will be 50000 times.. Your requirement is to get the matching Description records rite. In that case the maximum no is 2000 (1000 from Keyword and for each record, we need to look up in Description table which is 1000 maximum assuming each keyword would have only one description in the Description table)

And also, we cannot simply say the COBOL program would be taking more time always.. Sometimes the Cobol program involves simple SELECT might be faster than the DB2 Join. Again it depends on table properties (like Index, no. of records etc)

But if we have one to one relationship between Keyword table and Description table then we can join both table which might be taking less time if the join columns are properly indexed.

SOLUTION 1

SELECT A.KEYWORD_ID, B.DECRIPTION_TEXT
INTO :WS-KEYWORD-ID,:WS-DESCRIPTION-TEXT
FROM KEYWORD A
,DESCRIPTION B
WHERE A.KEYWORD_ID = B.KEYWORD_ID

SOLUTION 2

Please use a Cursor to select from Keyword table and for each record you can have singleton select from Description table until the End of Table

PERFORM UNTIL EOF-RECORD

SELECT KEYWORD_ID
INTO :WS-KEYWORD-ID
FROM KEYWORD

EVALUATE SQLCODE

WHEN 0
SELECT DESCRIPTION_TEXT
INTO :WS-DESCRIPTION-TEXT
FROM DESCRIPTION
WHERE KEYWORD_ID = :WS-KEYWORD-ID
WHEN 100
SET EOF-RECORD
WHEN OTHER
ERROR-PARA

If the tables are properly indexed, there will not be much difference in performance. But solution 2 involves extra coding compared to solution 1

Suggestions are welcome,
Hope this helps.
Back to top
View user's profile Send private message
vvgoud

New User


Joined: 16 Aug 2007
Posts: 27
Location: Hyderabad

PostPosted: Thu Sep 18, 2008 11:44 pm    Post subject: Reply to: Need Best solution
Reply with quote

Suresh I am very happy to see your response

I have some comments on your solution.

SOLUTION 1

SELECT A.KEYWORD_ID, B.DECRIPTION_TEXT
INTO :WS-KEYWORD-ID,:WS-DESCRIPTION-TEXT
FROM KEYWORD A
,DESCRIPTION B
WHERE A.KEYWORD_ID = B.KEYWORD_ID

we dont have any reference for keyword table and Description table so your A.KEYWORD_ID = B.KEYWORD_ID condition retrieve the 0 rows.


I will give example of two tables and output

This is not not one to one relation ship many to many relation ship

One keyword can find in many descriptions and one description can have many keywords.



Keyworkds Table.
-----------------------
Keyword( CHAR 32) below are the values of 5 rows.

START
TOWED
ALARM
OIL
ENGINE


Description Table contain 5 rows
-----------------------------
SID(9) Description(CHAR 100)

1 POWER/CAR STARTED THE PARKED IT AND CAR WAS TOWED
2 INSTALL SILENCER ALARM SYSTEM STARTED
3 OIL AND FILTER CHANGE SPECIAL. $29.95,
4 WARR PER-DELIVERY INSPECTION ~|~WPDI
5 POWER STEERING FLUSH, FLUSH COMPLETE SYSTEM TOWED


I have to read the first keyword from keyword table 'START' and search in the all 5 rows of description table so i will find using 'START' keyword in row1 and Row2 so my output of first keyword is 2 SIDs.

and I will read second record of keyword 'TOWED' and search in the 5 rows of description table and find 1st row and 5th row.

I will read the keyword 'ALARM' from keyword table find the second row means SID 2.

for keyword 'OIL' we can find the third row means SID 3
and for 'ENGINE' no rows.


my OUTPUT is

SID
-------
1
2
1
5
2
3

Please let me know your ideas

Thanks for your help.
Venkat.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Sep 19, 2008 12:05 am    Post subject:
Reply with quote

Hello,

What should happen if a keyword is embedded in another word? For example, what if some description mentons "Aluminum foil" or "They toiled to get the spare tire stowed"? Do "foil" and "toiled" match "oil"? What about "stowed"? What if TOW and TOWED are both in the keyword table? And these are only a tiny few. . .

How will the list of SIDs be used once found?
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Fri Sep 19, 2008 12:08 am    Post subject:
Reply with quote

Hi

You can use Solution 2 for this

Declare a Working Storage variable size as same as Description Text

01 WS-DESCRIPTION-KEY.
05 WS-FILLER PIC X(01) VALUE '%'.
05 WS-DESC-TEXT PIC X(99).


PERFORM UNTIL EOF-RECORD

SELECT KEYWORD_ID
INTO :WS-KEYWORD-ID
FROM KEYWORD

EVALUATE SQLCODE

WHEN 0

MOVE WS-KEYWORD-ID TO WS-DESC-TEXT

INSPECT WS-DESCRIPTION-TEXT REPLACING ALL SPACES BY '%'

SELECT SID
INTO :WS-SID
FROM DESCRIPTION
WHERE DESCRIPTION_TEXT LIKE :WS-DESCRIPTION-KEY

WHEN 100

SET EOF-RECORD TO TRUE

WHEN OTHER
ERROR-PARA

Since we getting Multiple results here, we need to use CURSOR in both the cases.

Hope this helps,
Back to top
View user's profile Send private message
vvgoud

New User


Joined: 16 Aug 2007
Posts: 27
Location: Hyderabad

PostPosted: Fri Sep 19, 2008 4:50 am    Post subject: Reply to: To identify matched records from two tables
Reply with quote

Thanks suresh i will try with this

Thanks for your help

Venkat
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Sep 19, 2008 5:56 am    Post subject:
Reply with quote

Hello,

To repeat:
Quote:
How will the list of SIDs be used once found?
Also, do the answers need to be precise?

Depending on the answers to those 2 questions (and maybe more), the question of one keyword being embedded may or may not matter.

I do believe you need to better understand (or better qualify) your requirement and how you will use the "output" before starting to throw code at it.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    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 SORTJOIN - Copy Matched and Unmatched... Steve Ironmonger DFSORT/ICETOOL 5 Tue Jan 17, 2017 4:26 pm
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us