Hi all,

I have to create a report where part of the info is in Oracle, part of the info is in DB2.
I've already have the data from oracle in a hold file, for example EMPLOYEE with field EMP_ID, TITLE
I need report other data about the employee, for example NAME, ADDRESS. These information is in DB2
table. I did some search, but couldn't find any similar issue. In one post, it seems say you can't not
join between oracle and db2. Even though, it is possible to join, we prefer not to do so because of the huge amount of records in the EMPLOYEE table.

I know how to call a DB2 stored procedure with a parameter like EMP_ID. But it seems impractical to loop
each record in the hold, call the stored procedure and write the result somewhere.
The second solution is combine all emp_id1,emp_id2....emp_idn to one string and pass to a store procedure.
It seems also impractical because the maximum length of string webfocus and DB2 may be not long enough.
The third option is to write all the emp_id from the hold file to a flat file and ftp to database server and then the stored procedure get these IDs from there. But I am not sure if it is easy for DB2 to read flat file.
The Last option is to add a temp data in db2 and write the emp_id to this table. And the DB2 read from this table and return result set. But I am not sure if I can insert record to a db2 table from WebFOCUS.

That is all I can think of. But I don't like either of them.
Has anyone run into similar situations?
Any solution, suggestion, comments are welcomed and appreciated.


Thanks and Regards

Alu

This message has been edited. Last edited by: Kerry,

DB2: If you use a join, WF will in essence create an rpc and run it with a series of arguments based on the rows of the HOLD file. Try the join, from the HOLD file to the DB2 table, use a trace to see what SQL is generated, and see for yourself whether its resonse time is acceptable.

Are values of the UNIT_CODE key repeated in the HOLD file? If so you may be able to improve performance of the table request on the join to db2, by making the first hold file a two-segment focus file, with the first segment keyed just to UNIT_CODE. Use a multiverb request:

TABLE FILE ...
COUNT ENTRIES NOPRINT
BY UNIT_CODE
PRINT (or SUM) ...
BY UNIT_CODE
BY whatever
ON TABLE HOLD FORMAT FOCUS
END
That way the join will only be traversed once per distinct key value. (Of course Hold format Focus introduces some overhead, so you have to see how it balances out.)


FTOA: Use F (rather then D) format to suppress the commas. Or append a lower-case c to the D format: '(D10.2c)'.

Alu Please try and keep your posts to a single topic, it makes life much easier,

Jack has given you a solution to the first issue.

I disagree with his answer to the second as its packed it should be PTOA

However I really do suggest that you learn to search the forum, exactly that same question
exact same column UNIT_CODE, exact same value 10,020,313,480 was asked by someone who is
obviously doing exactly the same job as you.

How many of you guys are working on the same job, does not seem very efficient to me.

Hi JG,

You are right. The second part is exactly that same question
exact same column UNIT_CODE, exact same value 10,020,313,480 was asked by someone else. I am not intented to ask this question. I found it when I was doing rearch in the forum and copied it to a text edit and wanted to look at it later. I make a mistake putting it together with my question.

Sorry for that. Next time I will double check before posting. But I do search first before asking question.

Alu

then there's always the MATCH command
it doesn't care what your source filetypes are.
MATCH FILE oraclething
SUM something...
BY SOMECOMMONFIELD
RUN
FILE db2thing
SUM somethingelse
BY SOMECOMMONFIELD
AFTER MATCH HOLD OLD-OR-NEW
END
..read up on the MATCH command. its very controllable, not dependent on the sorts of things that make joins difficult.
Your common fields can be DEFINEd .

Ultra handy technique.
a FOCUS-unique spectacular feature.

JG and Susannah,
I like your simplistic approaches. The proper use of the MATCH FILE command with apporopriate filtering works like a champ everyt time I use it.
-Doug

"simplistic" is a pejorative, btw.

Thank you Jack, JG and Susannah for giving very valuable ideas and solutions. The MATCH command solved my problem. and I will remember this trick in future assignment.

Alu