I had a need this morning to create a master file with two segments from two different sources, one DB2 and the other SQL*Server. Left Outer, Defined Field in DB2 (the parent) to match to the key field in SQL*Server (the child). I hadn't done this in about ten years so I had to look it up, but I used the Synonym Builder and got the job done. Easy Peasy.

So I test. The result appears to kick off a table scan in DB2, even when I have a criterion that applies solely to that part of the select. I ask for all records where the Expiration_Year is greater than 2016 (should return about a million rows) but instead the select pulls all fifteen million rows for consideration of additional criteria on the SQL*Server table. The result is correct, but the time to run the report is about ten minutes due to the table scan. I'm expecting the IBM operators to call anytime now asking me why I need records from 1976.

Is there a secret to having the DB2 criteria passed to DB2 for consideration prior to returning the record set? This master file is to be used by non-WF experts so I don't want to press them to produce hold files or SQL pass-through. It needs to be simple above all, but fast if I can get it.

J.

This message has been edited. Last edited by: <Emily McAllister>,

I always thought DEFINE based joins turn off optimization.

I would never join tables of different dbms types, despite what I.B. might tell us. I'd create HOLD files - ALPHA, FOCUS or XFOCUS, then join after.

Sorry, couldn't hear you Francis, I was clamping my hands on my ears and singing very loudly as I read your response.


Optimization off may be the case if there's a define in the master file, though this is a selection on the parent so it would seem an arbitrary thing to turn off. It's not entangled with the selection criteria at all.

The define is to pull 9 characters off of one of the fields. So I have (in the parent)

DEFINE SHORT_VIN/A9 = EDIT(VIN, '99999999$9');

The JOIN_WHERE in the master file is this --

JOIN_WHERE=DB2_PARENT.SHORT_VIN EQ SQLMSS_CHILD.SHORT_VIN;

Doing a selection on the Expiration_Date shouldn't have anything to do with this. But, maybe the coding isn't smart enough to figure that out.

I wonder if

JOIN_WHERE=EDIT(DB2_PARENT.SHORT_VIN, '99999999$9') EQ SQLMSS_CHILD.SHORT_VIN;

would work, then I could leave the DEFINE out of the parent's master file. Looks like I'm pushing my luck with that option, but it may work better.

The nature of the environment I'm working in requires me to "augment" production data with information from other sources, and to present it to junior programmers in a way that they can make it work (including via InfoAssist) without a lot of extra steps. I was hoping there was a way to do that without returning full tables for each run.

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

John, I'd turn SQL traces on and set XRETRIEVAL OFF. Then I'd start by simplifying the query to see what SQL gets generated. Meanwhile, that DEFINE cannot be converted to SQL. How about creating a DBMS View that does the edit for you, create and use a master of this view (without the DEFINE)...

The view is an interesting idea. Might take me three months to get it through the hoops for creation on DB2, but may solve the issue completely.

I made that change to the JOIN_WHERE to see if I could remove the DEFINE field from DB2 and I can. It works and returns data. But, dead slow. So it would appear that it's not making a first-pass on DB2 to get all records and then combining them.

The advantage of leaving it as it is is that it likely will take the people using the Reporting Object or the Master File significantly more time to create routines that execute more quickly. Their hourly rate drives the cost factor on the decision, so it's cheaper to have the slower routine that's simpler to use. But if the view can give me both sides of the coin I'll owe you a beer.

J.

quote:

But, dead slow

have you taken a look at the SQL?

Meanwhile, "At the Mast" - in which body of water? It is important, as the beer tastes different depending on where you are.

This message has been edited. Last edited by: Francis Mariani,

quote:

Originally posted by Francis Mariani:
Meanwhile, "At the Mast" - in which body of water? It is important, as the beer tastes different depending on where you are.

It's remarkably hard to convince people of that. Someone will ask me what my favorite beer is and I have to reply "it depends". Drinking a Guinness when it's 95 degrees out on the deck of a boat in the Chesapeake's oh-so-undependable breeze is worthy of a grimace. But drinking a Bud Light Lime in December, absurd. You need to adjust to conditions.

For years I ran the front half of a Benneteau Frers 50, in the Chesapeake racing series, generally putting myself in the Mast position. Four kids have pulled me away from that, but in a few years they'll be gone and I'll find my way back. But I might need to change my moniker to On The Trim instead. Ain't getting any younger.