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>,

How many records are in the non-DB2 table? Is it a FOCUS hold file or stored in some other DB?

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.

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

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;

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.

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.

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.

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.

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

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,

Is this a WebFocus question or an SQL question?

SQL. We are strictly Mainframe FOCUS.

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,

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.