|
View previous topic :: View next topic
|
| Author |
Message |
vvgoud
New User
Joined: 16 Aug 2007
Posts: 27
Location: Hyderabad
|
|
|
|
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 |
|
 |
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
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 |
|
|
vvgoud
New User
Joined: 16 Aug 2007
Posts: 27
Location: Hyderabad
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
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 |
|
|
vvgoud
New User
Joined: 16 Aug 2007
Posts: 27
Location: Hyderabad
|
|
|
|
Thanks suresh i will try with this
Thanks for your help
Venkat |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
|
|
