Hello

we are using DB2 as RDBMS. I build a business view (cluster join) in which there are inner joins and left outer joins. When building a report in InfoAssist on this business view, I see the next things in the SQL trace:

FOC2510 - FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
FOC2513 - OUTER JOIN (ALL=ON or ALL=PASS) CANNOT BE PASSED
FOC2675 - CANNOT PASS INNER JOIN TO SEGMENT GWTB311 BELOW AN OUTER ONE
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2592 - RDBMS-MANAGED JOIN HAS BEEN DISABLED

I also see that WebFOCUS is building 5 separate queries, in other words it is getting all the data from DB2 and then does his own thing.

Questions:
- where can I find more detailed information about the FOC-messages?
- How do I solve these messages, esepecially FOC2513 and FOC2675? I am rather new to solving these things. Eager to learn but need some help

Regards
Ron

This message has been edited. Last edited by: FP Mod Chuck,

If you issue a ? error number you'll get the details of what the error means. For instance, ? 2513 will return the following:

  
Detail:
(FOC2513) OUTER JOIN (ALL=ON or ALL=PASS) CANNOT BE PASSED

Optimization was disabled due to the previous issuance of a SET ALL = ON
or ALL = PASS command while the given SQL engine doesn't support a proper
type of OUTER JOIN. For instance, SET ALL=ON is passable to DB2 (v.4 and
up) and ORACLE while SET ALL=PASS can be passed to SYBASE and Informix.

So the left outer join might be causing this in your cluster master file. When creating a join either in InfoAssist or in the master file, if WebFOCUS can't translate your request into an optimized SQL request, it will select the rows it requires to perform the join on the reporting server. This means you could end up with table scans and multiple selects that you're seeing in the trace. There are some basic rules that you can follow to avoid turning optimization off. Here's a good place to start: http://documentation.informati..._PROCESSING_d0e47924

Hai,

Looking at our settings in the WebConsole for DB2 it says:
- Optimization ON
- OPIFTHENELSE ON
- Aggregate awareness installation default

Looks good I think.

Then you say:
Optimization was disabled due to the previous issuance of a SET ALL = ON
or ALL = PASS command while the given SQL engine doesn't support a proper
type of OUTER JOIN.

What do you mean by this? Where is SET ALL given a value? I don't do that. I just created a business view, which the user uses to make a report. Nowhere in the business view I give SET ALL a value. On what level is this set? Do I manually have to update the master file?

Regards
Ron

I believe your left outer join automatically sets all. So, even if you set optimization on and don't set all on, depending on the join you have in your master file, you could create a situation where WebFOCUS just can't translate what you're asking for into an optimized SQL join. To test this, remove the outer joins and see how the SQL trace handles your request.

One alternative to all of this (if all your tables are in the same DB2 instance) is to create a DB2 view that has your desired joins in place and provide your InfoAssist users a master file for that view. This will force all the joins to happen inside DB2 and obviate the need to worry about how your master file should join tables together.

Hai BabakNYC,

I really appreciate your answers.

The outer joins are an essential part of the business view. I can delete them just for testing purposes, but I need them in real life.
So, you say that whenever I use an outer join, it can't be transformed to an optimized SQL join??

Making a DB2 view instead is an option. But why making a DB2 view if I have WebFOCUS, AppStudio which has the possibility to make a view itself, a business view? You are almost saying that WebFOCUS can only handle inner joins. We want to use WebFOCUS functionality.

Regards
Ron

What I'm saying is WebFOCUS can probably do all the joins you can throw at it. But depending on what you or your InfoAssist user asks for, it will either send an optimized join to DB2 OR it'll say there isn't a join like this in DB2 and I'll try to do it myself. The former is the default, and the latter is what happens if you have certain conditions present. Outer joins don't always turn optimization off. However, the combination of certain requests does. The reason I suggest a DB2 view instead of a cluster master is that if your joins are complex or you want to accomplish very robust data access, it's much easier to push them into the database rather than the metadata. That way, you just don't have to think too hard about what does or does not turn optimization off. There is absolutely a benefit in using WebFOCUS to create cluster joins. One such case is if you are joining tables from different types of tables. As a matter of practicality, I always pick the fastest and easiest solution. Pointing an InfoAssist user to a DB2 view that takes out the join complexity is the fastest solution. Would you be able to create a cluster join in App Studio that'll do the same thing? Of course you can. But if a DB2 view is an option, I'd pick that. This way, all you have to do in your master file is to organize your fields in nice BV's and you're all set.

What do you mean with: "there isn't a join like this in DB2 and I'll try to do it myself"?

And what do you mean with "One such case is if you are joining tables from different types of tables."?

The problem with DB2 views is that we need a DBA for that. We have an Agile way of working in which we can produce business views fast. All main tables already are business views and we just "copy them as an existing master" and join them to the fact table.

Concerning this warning
FOC2675 - CANNOT PASS INNER JOIN TO SEGMENT GWTB311 BELOW AN OUTER ONE

If I issue "? 2675" I get nothing. What does this warning exactly mean?

Regards
Ron

quote:

What do you mean with: "there isn't a join like this in DB2 and I'll try to do it myself"?

A DEFINE based join might turn off optimization and your join will not be passed to DB2.

And what do you mean with "One such case is if you are joining tables from different types of tables."?

Joining a HOLD file to DB2 is such a join. DB2 wouldn't know about your HOLD file so WebFOCUS will bring back the data it needs from DB2 and joins it to the HOLD file inside WebFOCUS Reporting Server.

Sometimes the error message just doesn't give you an explanation other than the message itself. I remember a long time ago I ran into FOC2675. I think someone told me to try SET SHORTPATH =SQL to see if I can force the DBMS to take over the join. It's worth a try.

In a different project, because I didn't want to ask the DBA for a view, I just created the SQL with all the JOINs in Oracle and used that SQL statement to create a master file. Effectively, you get the same thing. The master file won't require joins because they're already in the SQL Select I'm sending to the database. If none of these work in your case, you may need to show your master file to IB tech support to see if they can make more concrete suggestions so you can use cluster masters and not turn off optimization.

The creation of my business view resulted in a master file. Where in that master file do I set SHORTPATH=SQL??

All SET commands belong to the report. You could add it to a Reporting Object or alternatively you can put your SET in the profile which will apply to every report. But before you do that, you have to make sure it's what you want for everyone. For now, just add it to the beginning of your report to test.

Hi Ronibi

I can tell you're pretty new to the product. Welcome to the world of WebFOCUS!

I have a simple question that I don't think you have given us the info for. If you issue:
? SET
by itself in a focexec, what is the ALL parameter set to?

The ALL parameter is actually really old and has been around since before we were reading SQL based data sources.

If you find that ALL is actually set to ON, you could try finding where it was set that way.. and override it in your own focexec with SET ALL=OFF.

Let me give you brief explanation of ALL. What that did - back in the old days, was act like a left outer join does today. If it's set to ON, it means give me ALL the results of my join even if records only exist in my first part of my JOIN and there are no records in the second part.

So you joined 2 tables (Lets say T1 and T2), and you wanted ALL the records in T1 regardless of whether or not they were found in T2, you'd add SET ALL=ON to your code.

This was done back then because our JOIN syntax didn't know about LEFT OUTER in old releases.

So what's up with SET ALL=PASS? Well, that was for cases where we have a WHERE test on a field in table 2 (T2 in our example). Again, to try to get you LEFT OUTER like result, what you need is a way to say:

Give me all the records in T1 if the WHERE T2.fieldname ='bob'... AND by the way- if there is no T2 record found (the LEFT OUTER bit), then I want to let the WHERE T2.fieldname='bob' PASS the test... Allowing ALL the T1 records where the join didn't resolve but when the JOIN did resolve, t2.fieldname needs to be 'bob'.

I know it's a lot to learn at first.

I hope this bit of history helps you understand why SET ALL=ON and SET ALL=PASS make WebFOCUS believe you are trying to do a LEFT OUTER. When FOCUS sees that ALL=ON or PASS, it starts thinking it needs to manage the JOIN's itself.

That results in multiple SQL SELECT statements.

Since you're just learning, let me point out that most of our GUI tools generate FOCUS code in the background. It's a big advantage if you know where to go look up what the code means that it generates.

Download the 4 manuals you see under Reporting Language from 8202 PDFs

The first manual is the 'crlang' Command Language Reference. That'll give you definitions of all the reporting code you see.

The 2nd one isn't really clearly named, but it's what we'd call the Dialog Manager manual. Any commands you see that start with a dash (-) at the beginning of the line.

The 3rd Describing Data is all about knowing what the Master and Access files are telling you.

The 4th gets into Functions / User Written Subroutines that you can use in either Define's or maybe in your Dialog Manager code.

Keep these 4 books handy. Look up things to see if it helps you understand stuff.

Good luck!
Toby

quote:

Originally posted by BabakNYC:
All SET commands belong to the report. You could add it to a Reporting Object or alternatively you can put your SET in the profile which will apply to every report. But before you do that, you have to make sure it's what you want for everyone. For now, just add it to the beginning of your report to test.

The report is made using InfoAssist without a Reporting Object. How do I add something at the beginning? Copy the code in a new procedure and add the SET parameter for test?

Thanks Toby.

I am rather new concerning the way WebFOCUS handles database requests. Because of our high values I prefer an RDBMS-managed join instead of a WebFOCUS managed join. Performs faster.
So I try to understand what happens, I try to learn how I can optimize the requests.
And I try to learn to interpret warning and error messages, understand them in detail such as:

FOC2513 - OUTER JOIN (ALL=ON or ALL=PASS) CANNOT BE PASSED
FOC2675 - CANNOT PASS INNER JOIN TO SEGMENT GWTB311 BELOW AN OUTER ONE

Thanks for pointing out the four manuals. I am overall, so not in detail, familiar with it. I will use them as reference work.
Although I don't think the manuals will discuss in detail why warnings/errors are given and how you can solve them.

When I am back at work tomorrow I will issue the SET command and tell you what the value is.
Thanks for your help

Regards
Ron

Hi Ron

So you're on the right track for sure. Usually it'll be more efficient if you can pass off the work to the RDBMS.

I found it interesting - the lack of information about the FOC2675 that we can see.

I checked in 8105m and got no response from ? 2675 either.

In 8203, I get:
(FOC2675) JOIN CONFIGURATION "SQL LEFT_OUTER SQL INNER SQL" OPTIMIZED ONLY FOR SHORTPATH=SQL

Since our error message numbers don't really change, but our messages do over time, lets hope this one is in 8203 is actually more useful.

To answer your question to Babak, yes, just make a copy and stick whatever SET commands you want at the top for experimenting.

The idea of a 'short path' is exactly what SET ALL was about back in the day (I've been doing this for 35 years or more now).. it's worth looking for that newer SET SHORTPATH=SQL to try adding to the top of your focexec too just in case it helps.

If it does help, the next step will be for us to figure out when to apply that SET statement for your users.

Good luck! let us know how it goes.

Toby Mills, CISSP

Executing ? SET in a procedure results in:
SET ALL = OFF

Does this mean that when WebFOCUS manages the JOIN the LEFT OUTER becomes INNER??

On the other hand I read that if INNER or LEFT OUTER is explicitly given in the JOIN command, or set in a synonym, the setting for the ALL command is ignored.

This is a part of my business view, whereby LEFT_OUTER is mentioned. So I think that SET ALL is ignored??

DEFINE D_TX_BEDR_REACH/P21.2C!E=IF GWVW7731.SEPA_RICHT_CD EQ 'CTR' OR 'CTS' AND GWVW7731.SEPA_TX_TYP_CD EQ 'NOR' THEN (TX_BEDR / 100) ELSE 0; $
SEGMENT=GWTB403, SEGTYPE=KU, PARENT=GWVW7731, CRFILE=PS_MA/GWTB403, CRINCLUDE=ALL,
JOIN_WHERE=GWVW7731.DAG_KEY EQ GWTB403.DAG_KEY;, $
DEFINE JAARMAAND/I6 WITH DAG_NM=GWTB403.JAAR * 100 + GWTB403.MND_NR;
TITLE='Jaarmaand', $
SEGMENT=GWTB409, SEGTYPE=KU, PARENT=GWTB403, CRFILE=PS_MA/GWTB409, CRINCLUDE=ALL,
JOIN_WHERE=GWTB403.MND_KEY EQ GWTB409.MND_KEY;, $
SEGMENT=GWTB420, SEGTYPE=KU, PARENT=GWTB403, CRFILE=PS_MA/GWTB420, CRINCLUDE=ALL,
JOIN_WHERE=GWTB403.W_KEY EQ GWTB420.W_KEY;, $
SEGMENT=GWTB355, SEGTYPE=KU, PARENT=GWVW7731, CRFILE=PS_MA/GWTB355, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
JOIN_WHERE=GWVW7731.INIT_DISTR_KANL_ID EQ GWTB355.INIT_DISTR_KANL_ID;, $

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

I now understand why this occurs

FOC2675 - CANNOT PASS INNER JOIN TO SEGMENT GWTB311 BELOW AN OUTER ONE

We have a fact table (T1) with an account number in IBAN format. It reads a dimension table (T2) containing accounts by using a left join. Because not all accounts are in T2.
Next T2 joins to T3 by using account number in BBAN format. This is an inner join.

So, in SQL

select
from T1
left outer join T2
on T1.IBAN = T2.IBAN
inner join T3
on T3.BBAN = T2.BBAN

But why can't WebFOCUS pass this?

I tested with multiple settings.

SET ALL = OFF (default)

FOC2510 - FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
FOC2513 - OUTER JOIN (ALL=ON or ALL=PASS) CANNOT BE PASSED
FOC2675 - CANNOT PASS INNER JOIN TO SEGMENT GWTB311 BELOW AN OUTER ONE
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2592 - RDBMS-MANAGED JOIN HAS BEEN DISABLED
SELECT
T1."DAG_KEY",
T1."BBO_KNT_KEY",
T1."SEPA_RICHT_CD",
T1."SEPA_TX_TYP_CD",
T1."DBTR_IBAN",
T1."CRDT_IBAN",
T1."TX_BEDR",
T1."TX_AANT"
FROM
GWEGDBA.GWVW7731 T1
WHERE
(T1."DBTR_IBAN" = 'NL56RABO0350801681') AND
(T1."SEPA_TX_TYP_CD" = 'NOR')
FOR FETCH ONLY;
SELECT
T2."JAAR",
T2."MND_NR",
T2."DAG_NM"
FROM
GWEGDBA.GWTB403 T2
WHERE
(T2."DAG_KEY" = ?) AND
(((T2."JAAR" * 100) + T2."MND_NR") = 201806) AND
(T2."JAAR" = 2018)
FOR FETCH ONLY;
SELECT
T5."REK_NR"
FROM
GWEGDBA.GWTBD18 T5
WHERE
(T5."IBAN" = ?)
FOR FETCH ONLY;
SELECT
T6."GEBRT_DAT"
FROM
GWEGDBA.GWTB311 T6
WHERE
(T6."REK_NR" = ?)
FOR FETCH ONLY;
SELECT
T7."KNT_NR"
FROM
GWEGDBA.GWTB404 T7
WHERE
(T7."KNT_KEY" = ?)
FOR FETCH ONLY;
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

SET ALL = ON or SET ALL = PASS or SET SHORTPATH = SQL all lead to this. I see the problem in the DEFINE. Will test later with DTPART. Still there also is FOC2525/2509/2524

FOC2525 - FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: GWTBD18
FOC2509 - RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE
FOC2524 - JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2565 - THE OBJECT Define_Geboorte_Jaar_Debet OF BY/ACROSS CANNOT BE CONVERTED TO SQL
FOC2566 - DEFINE Define_Geboorte_Jaar_Debet CANNOT BE CONVERTED TO SQL
FOC2584 - CANNOT IMPLICITLY CONVERT VALUE OF FIELD GEBRT_DAT TO USAGE YY
FOC2593 - USE FUNCTION DTPART
SELECT
T1."DAG_KEY",
T1."BBO_KNT_KEY",
T1."SEPA_RICHT_CD",
T1."SEPA_TX_TYP_CD",
T1."DBTR_IBAN",
T1."CRDT_IBAN",
T1."TX_BEDR",
T1."TX_AANT",
T2."JAAR",
T2."MND_NR",
T2."DAG_NM",
T5."IBAN",
T5."REK_NR",
T6."REK_NR",
T6."GEBRT_DAT",
T7."KNT_KEY",
T7."KNT_NR"
FROM
( ( ( ( GWEGDBA.GWVW7731 T1
INNER JOIN
GWEGDBA.GWTB403 T2
ON (T2."DAG_KEY" = T1."DAG_KEY") )
LEFT OUTER JOIN
GWEGDBA.GWTBD18 T5
ON T5."IBAN" = T1."CRDT_IBAN" )
LEFT OUTER JOIN
GWEGDBA.GWTB311 T6
ON T6."REK_NR" = T5."REK_NR" )
LEFT OUTER JOIN
GWEGDBA.GWTB404 T7
ON T7."KNT_KEY" = T1."BBO_KNT_KEY" )
WHERE
(T1."DBTR_IBAN" = 'NL56RABO0350801681') AND
(T1."SEPA_TX_TYP_CD" = 'NOR') AND
(((T2."JAAR" * 100) + T2."MND_NR") = 201806) AND
(T2."JAAR" = 2018)
FOR FETCH ONLY;
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

Morning Ron

Thanks for the updates! Looks like you're getting really close to a good looking SQL statement.

First - you're right about SET ALL=OFF... the OFF (default) setting will try to pass off INNER's if it can. So you should be okay leaving it like it is and only need to use SET SHORTPATH=SQL. I'd try it like that and see if you still get the output you showed last.

As long as it's just one SQL statement, then you're on the right track.

Now for the messages. Those messages come from a trace called SQLAGGR and it's meant to be an informative set of messages that tells you two things.

1) Can I pass off the JOIN's to the RDBMS?
2) Can I pass off Aggregation (like max. etc) to the database?

If either condition is not true, WF tries to give you a hint as to WHY it can't do the task.

Now, for your messages, you're first part, that normally talks about whether or not JOINs can be passed off is more of a soft warning to check you SQL manually to make sure it looks like you want it to look. WebFOCUS pays attention to the KEYS= attribute and compares that to the number of WHERE tests it sees for a segment. If your KEYS is not filled in (like a 0), or the number is inaccurate, you'll get this message. You can safely ignore this message if you can tell by looking at the SQL that all the WHERE tests look good.

The second part - where it usually tells you whether it can pass off aggregation - thats telling you that since WF sees a DEFINE'd field that it doesn't know how to pass off to DB2, it decides to not do aggregation. These days it even gives you a hint as to what DEFINE is causing it problems.

All in all, I think you've about got it beat

A good thing to try is to yank that SQL out (copy/paste) and then use your own RDBMS tools to see what it looks like. You an also do an Explain Plan to see if you're happy with the results.

It's kinda tough for you because you're using InfoAssist and don't have a lot of control over exactly what gets passed. You users may create a DEFINE that trips up the WF RDBMS optimizer and their query may not be optimized. It's easier when you're developing a specific report to control the SQL that is passed off.

Good job isolating and experimenting to get it going this well. You'll be doing a lot of that kind of thinking while using WebFOCUS.

Toby

Hai Toby

some questions to get it straight.

So, if SET ALL = OFF and WebFOCUS manages the join, a left outer becomes an inner?

But as stated before, in my business view, my master, left outer is mentioned. Does this mean SET ALL = OFF is ignored? Because that's what the theory says.

SET SHORTPATH = FOCUS is the same as SET ALL = OFF?

Would you be so kind as to have a look at my reply in this post from posted July 25, 2018 08:14 AM? This is the content:


FOC2675 - CANNOT PASS INNER JOIN TO SEGMENT GWTB311 BELOW AN OUTER ONE

We have a fact table (T1) with an account number in IBAN format. It reads a dimension table (T2) containing accounts by using a left join. Because not all accounts are in T2.
Next T2 joins to T3 by using account number in BBAN format. This is an inner join.

So, in SQL

select
from T1
left outer join T2
on T1.IBAN = T2.IBAN
inner join T3
on T3.BBAN = T2.BBAN

But why can't WebFOCUS pass this?



You advice to leave SET ALL as it is (OFF) and only use SET SHORTPATH=SQL.
How do I embed this in a business view that is used by the business?? We have no reporting object for it. Should I then add it manually to the master file of the business view? Can I then still open this view in the GUI?

Regards
Ron

Hi Ron

Well, I don't have a great answer for you here.

If you put SET SHORTPATH=SQL in your edasprof.prf, it will apply to EVERY query you run for all adapters. So you'd have to be sure that's what you really want.

Another thought that crossed my mind was about DB2 in particular. Each RDBMS has special settings that might be helpful.

I wonder - if you issue:

ENGINE SQLDB2 ?

and run it, it should list your current DB2 specific settings. Maybe 'aggregate awareness' might be useful? See the Adapter Administration guide to learn more about this. I'm not sure it's a fit for you.


If there is any option to change only the DB2 adapter behavior, that would at least keep you from causing SHORTPATH=SQL to always be used even for other databases you guys might have at the bank.

Realize that I think this setting really only applies at the time someone is actually going to hit the WebFOCUS Reporting Server... Meaning, I don't think you'll find anything on the client side to help set this right (Short of you typing it in a Focexec). That kind of leaves us only with things that happen automatically on the reporting server (like server profiles such as edasprof.prf or group profiles etc).

The other guys here have experience with this sort of thing too. Maybe putting up a different server node just to handle your infoassis queries for example. Then only have that service / server do the SET SHORTPATH=SQL for example.

I don't feel quite familiar enough with your setup to feel like I can give solid advice on where to set SHORTPATH.

Oh - just had an idea. What if you made up your Cluster Join Master, and inside the Master, you make an MFD_PROFILE entry? that causes a focexec to run every time the Master is touched. That Focexec could contain just the SET SHORTPATH=SQL. Yeah - that might work so that only the master you're using has that SETting enabled.

Something to think about .

Later!
Toby Mills, CISSP

Thnx for all the answers. I will make a new discussion to get more insight in the FOC2675

regards Ron