Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SELECT * FROM DB2 with JOIN to Non-DB2?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] SELECT * FROM DB2 with JOIN to Non-DB2?
 Login/Join
 
Member
posted
I am trying to get a SELECT * FROM DB2 to work where the WHERE clause joins to a non-DB2 file for filtering. I want to use SELECT * so I get all the columns in DB2.

SELECT *
FROM DB2 A NONDB2 B
WHERE A.FIELD1 = B.FIELD1
AND A.FIELD2 = B.FIELD2
AND A.FIELD3 = B.FIELD3
FOR FETCH ONLY;

My goal is to return all the data in DB2 where the KEY matches what is in the HOLD file (NONDB2 file).

I have already tried:
JOIN FIELD1 AND FIELD2 AND FIELD3 IN NONDB2 TO ALL FIELD1 AND FIELD2 AND FIELD3 IN DB2 AS A1

TABLE FILE NONDB2
PRINT *
ON TABLE HOLD...

However this presents 2 issues.
First, the Select * includes both the NONDB2 fields and DB2 fields. I only want the DB2 fields without having to exclude them later.

Second, this requires a FOCUS Master to be updated. The tables are constantly being modified to include new columns and I prefer not to have to update the FOCUS Master every time the DBA makes a change.

Any suggestions?

This message has been edited. Last edited by: <Kathryn Henning>,


MF FOCUS 7.6.5
Excel, TXT, CSV
 
Posts: 5 | Location: Nashville  | Registered: July 27, 2011Report This Post
Virtuoso
posted Hide Post
How many records are in the non-DB2 table? Is it a FOCUS hold file or stored in some other DB?


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Member
posted Hide Post
It can contain just a few records or over 1000. The DB2 contains over 100 Million rows. The non-DB2 is not in any database.


MF FOCUS 7.6.5
Excel, TXT, CSV
 
Posts: 5 | Location: Nashville  | Registered: July 27, 2011Report This Post
Virtuoso
posted Hide Post
You might try something like this. Essentially the key values are written to a DB2 table, from which the main extract selects values for the WHERE clauses. Your FOCUS session will need CREATE/WRITE permission in order to create the KEYLIST table.

TABLE FILE NONDB2
 BY FIELD1 AS 'FIELD1'
 BY FIELD2 AS 'FIELD2'
 BY FIELD3 AS 'FIELD3'
 ON TABLE HOLD AS KEYLIST FORMAT SQLORA
END
-RUN
-*
SET SQLENGINE = SQLORA
SQL
 SELECT *
 FROM DB2 A
 WHERE A.FIELD1 IN (SELECT FIELD1 FROM KEYLIST)
 WHERE A.FIELD2 IN (SELECT FIELD2 FROM KEYLIST)
 WHERE A.FIELD3 IN (SELECT FIELD3 FROM KEYLIST)
 FOR FETCH ONLY ;
END
-RUN


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
I haven't done DB2 in many years so I don't know if this works, but can you try the following to retrieve only the DB2 records?

SELECT A.*
FROM DB2 A NONDB2 B
WHERE A.FIELD1 = B.FIELD1
AND A.FIELD2 = B.FIELD2
AND A.FIELD3 = B.FIELD3
FOR FETCH ONLY;


WebFOCUS 8
 
Posts: 74 | Location: Gahanna, OH | Registered: September 22, 2009Report This Post
Member
posted Hide Post
Unfortunately our site has FOCUS locked down as a read only tool. So creating a temporary table isnt an option.

I also tried the "Select A.*" but it doesnt know how to handle the non-db2 file in the select.


MF FOCUS 7.6.5
Excel, TXT, CSV
 
Posts: 5 | Location: Nashville  | Registered: July 27, 2011Report This Post
Expert
posted Hide Post
If the code you posted in your initial post works, and the only issue is that you retrieve more columns than you want, why not restrict the * ?

In WebFOCUS, specify SEG.COLUMN_NAME where COLUMN-NAME is the name of a column only in the DB2 table - you will retrieve all the columns in the DB2 table.

In SQL, specify TABLE-NAME.* where TABLE-NAME is a specific table - you will retrieve all the columns in the specified table.

In either case you will not retrieve the columns in other tables in the join structure.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Note that the original request...
SELECT *
FROM DB2 A NONDB2 B
WHERE A.FIELD1 = B.FIELD1
 AND A.FIELD2 = B.FIELD2
 AND A.FIELD3 = B.FIELD3
FOR FETCH ONLY;

...and Dan's code...
TABLE FILE NONDB2
 BY FIELD1 AS 'FIELD1'
 BY FIELD2 AS 'FIELD2'
 BY FIELD3 AS 'FIELD3'
 ON TABLE HOLD AS KEYLIST FORMAT SQLORA
END
...
SQL
 SELECT *
 FROM DB2 A
 WHERE A.FIELD1 IN (SELECT FIELD1 FROM KEYLIST)
 WHERE A.FIELD2 IN (SELECT FIELD2 FROM KEYLIST)
 WHERE A.FIELD3 IN (SELECT FIELD3 FROM KEYLIST)
 FOR FETCH ONLY ;
END
-RUN


are not equivalent. The latter may pull spurious rows, where field1 matches field1 of some row in keylist, but the values for field2 and field3 do not match the same keylist row.

So you would need a follow-up MATCH FILE, between the hold file obtained from DB2 and the keylist file, to eliminate the spurious rows.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
I thought I tried Tablename.* so I tried it again and get a FOC0003 error.

JOIN FIELD1 AND FIELD2 AND FIELD3 IN NONDB2 TO ALL FIELD1 AND FIELD2 AND FIELD3 IN DB2 AS A1

TABLE FILE NONDB2
PRINT DB2.*
ON TABLE HOLD...


I tried TABLENAME.* and 'TABLENAME.*' (in quotes) and both get the FOC003 error.


MF FOCUS 7.6.5
Excel, TXT, CSV
 
Posts: 5 | Location: Nashville  | Registered: July 27, 2011Report This Post
Virtuoso
posted Hide Post
Jack is absolutely correct about my previous sample code pulling inappropriate records. The sample code below should fix that error but is irrelevant because the environment won't allow table KEYLIST to be created.

TABLE FILE NONDB2
 BY FIELD1 AS 'FIELD1'
 BY FIELD2 AS 'FIELD2'
 BY FIELD3 AS 'FIELD3'
 ON TABLE HOLD AS KEYLIST FORMAT SQLORA
END
...
SQL
 SELECT A.*
 FROM DB2 A KEYLIST B
 WHERE A.FIELD1 = B.FIELD1
 WHERE A.FIELD2 = B.FIELD2
 WHERE A.FIELD3 = B.FIELD3
 FOR FETCH ONLY ;
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
You should run your JOIN version of the code with SQL traces turned on to see the SQL query generated. I suspect that syntax will retrieve the entire DB2 table and the JOIN for record selection will be applied by FOCUS after the fact. The sample code below may give you better performance because a set of key values is provided for the DB2 extract and should limit the size of the extract. As Jack pointed out earlier, this method will still give you unwanted records, so the MATCH FILE is needed after the initial extract to restrict the final output to the desired record set.

TABLE FILE NONDB2
 BY FIELD1
 ON TABLE SAVE AS KEYLIST1
END
-*
TABLE FILE NONDB2
 BY FIELD2
 ON TABLE SAVE AS KEYLIST2
END
-*
TABLE FILE NONDB2
 BY FIELD3
 ON TABLE SAVE AS KEYLIST3
END
-*
TABLEF FILE DB2
 PRINT *
 WHERE (DB2.FIELD1 IN FILE KEYLIST1);
 WHERE (DB2.FIELD2 IN FILE KEYLIST2);
 WHERE (DB2.FIELD3 IN FILE KEYLIST3);
 ON TABLE HOLD AS DB2HOLD
END
-*
MATCH
 FILE NONDB2
  BY FIELD1
  BY FIELD2
  BY FIELD3
 RUN
 FILE DB2HOLD
  PRINT *
  BY FIELD1
  BY FIELD2
  BY FIELD3
 AFTER MATCH HOLD AS OUTFILE OLD
END

Another approach, which would eliminate the need for the MATCH FILE step, is to concatenate the key fields in a DEFINE and check the SQL trace to see if FOCUS is able to pass the DEFINE to DB2. Sometimes FOCUS is able to pass simple DEFINEs to the relational DB. If so, this approach should retrieve only the desired record set.

DEFINE FILE NONDB2
 ALL_KEYS/A?? = FIELD1 | FIELD2 | FIELD3 ;
END
-*
TABLE FILE NONDB2
 BY ALL_KEYS
 ON TABLE SAVE AS KEYLIST
END
-*
DEFINE FILE DB2
 ALL_KEYS/A?? = FIELD1 | FIELD2 | FIELD3 ;
END
-*
TABLEF FILE DB2
 PRINT *
 WHERE (ALL_KEYS IN FILE KEYLIST);
 ON TABLE HOLD AS DB2HOLD
END

The only problem with any of these FOCUS approaches is that the synonym for the DB2 table must be kept up-to-date.

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Guru
posted Hide Post
Is this a WebFocus question or an SQL question?


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Member
posted Hide Post
SQL. We are strictly Mainframe FOCUS.


MF FOCUS 7.6.5
Excel, TXT, CSV
 
Posts: 5 | Location: Nashville  | Registered: July 27, 2011Report This Post
Expert
posted Hide Post
JOIN FIELD1 AND FIELD2 AND FIELD3 IN NONDB2 TO ALL FIELD1 AND FIELD2 AND FIELD3 IN DB2 AS A1

TABLE FILE NONDB2
PRINT DB2.*
ON TABLE HOLD...
is not SQL.

A lot the WebFOCUS code is like FOCUS code. tablename.* will not work, but SEG.column-name will.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Guru
posted Hide Post
Francis,

You are right, but the original code was not Focus.
/*
TABLE FILE 'NON_DB2
PRINT *
ON TABLE HOLD AS HOLDNON
END

JOIN FIELD1 AND FIELD2 AND FIELD3 IN HOLDNON
TO FIELD1 AND FIELD2 AND FIELD3 IN DB2 AS
J1
END

TABLE FILE HOLDNON
PRINT

END
/*

That is Focus.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SELECT * FROM DB2 with JOIN to Non-DB2?

Copyright © 1996-2020 Information Builders