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     define based join in db2 sql

Read-Only Read-Only Topic
Go
Search
Notify
Tools
define based join in db2 sql
 Login/Join
 
Pam Kratt
Platinum Member
posted
Do define based joins work efficiently in db2 sql?

I am trying to do a define based join but it processes extremely slowly and acts as if the defined field accountid2 from xxtbfact is still 30 characters instead of 16. I decided to try a different table that has accountid = 16 (so no define was needed) and the same amount of records as xxtbfact and it came back in seconds. My query below comes back in minutes. I did notice in my trace that when I do the define based join, it does 3 separate sql statements whereas when I do my other table without the define it does 1 sql statement.

JOIN XXTBFACT.ACCOUNTID2 WITH ACCOUNTID IN XXTBFACT TO ACCOUNTID IN XXTBEAR AS J0
JOIN XXTBEAR.EMPLOYEEID IN XXTBFACT TO EMPLOYEEID IN XXTBEMP AS J1

DEFINE FILE XXTBFACT
ACCOUNTID2/A16=SUBSTR(30,ACCOUNTID,1,16,16,'A16');
END

TABLE FILE XXTBFACT
PRINT EMPLOYEEID AS 'SRAID'
ACCOUNTID
EMPLOYEENAME AS 'SRANAME'
WHERE ROLETYPE = 'SRA '

ON TABLE HOLD AS OUTFILE FORMAT
END
-EXIT


webfocus 8.105M; os: windows; pdf, html, exl2k, csv
 
Posts: 179 | Registered: November 10, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
When you turned on the traces, did it not give you messages about the DEFINE?


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
Francis Mariani
Expert
posted Hide Post
I don't think SUBSTR is translated to SQL. You should have got a message specifying that.


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
Pam Kratt
Platinum Member
posted Hide Post
the only message I got was NON-ELIGIBLE EXPRESSION IN DEFINE OR WHERE BASED JOIN which fits with what you are saying (I did not notice it until I looked now ... I just saw the no aggregation error). Is there a command that does convert to sql that can change a field from 30 to 12?


webfocus 8.105M; os: windows; pdf, html, exl2k, csv
 
Posts: 179 | Registered: November 10, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
If the 30 char field has trailing blanks then you may be able to join without the define, otherwise I don't think you can do it using WebFOCUS.


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
PBrightwell
Master
posted Hide Post
Pam,
Check with your DBA to see if you can create a view in DB2 with the files already joined and the fields you need from each database specified. Define based joins do not work efficiently in DB2 or Oracle because you lose your index. Creating a view should resolve that.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
GamP
Virtuoso
posted Hide Post
Funny thing is, if you code your request as follows:
JOIN XXTBFACT.ACCOUNTID2 WITH ACCOUNTID IN XXTBFACT TO ACCOUNTID IN XXTBEAR AS J0
JOIN XXTBEAR.EMPLOYEEID IN XXTBFACT TO EMPLOYEEID IN XXTBEMP AS J1

DEFINE FILE XXTBFACT
ACCOUNTID2/A16=EDIT(ACCOUNTID,'9999999999999999');
END

TABLE FILE XXTBFACT
PRINT EMPLOYEEID AS 'SRAID'
ACCOUNTID
EMPLOYEENAME AS 'SRANAME'
WHERE ROLETYPE = 'SRA '

ON TABLE HOLD AS OUTFILE FORMAT 
END

Then the request is transported to DB2 where the define is translated to .... a DB2 SUBSTR function!


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
hammo1j
Master
posted Hide Post
Pam

Amazingly SUBSTRs do not get converted but EDIT does.

Try replacing

ACCOUNTID2/A16=SUBSTR(30,ACCOUNTID,1,16,16,'A16');

with

ACCOUNTID2/A16=EDIT(ACCOUNTID,'9999999999999999');

And it might pass the result as a single query.
Francis' solution is spot on as well in that rdbms's do not require the exact matches on formats that wf requires so you can join VCHAR(30) to a VCHAR(50) provided they got the same data.

Pam

As you rightly point out if more than one query is generated by the SQL translator it becomes inefficient


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Jessica Bottone
Platinum Member
posted Hide Post
Pam, if you're comfortable writing your own SQL, you could try direct SQL pass-thru. If you've not heard of that, just do a search on IBI.com for the string 'SQL pass'. You'll get all sorts of stuff. If you're not comfortable and your DBA won't create a view for you, ask if they'll create the SQL for you then you can just cut/paste that into the SQL pass-thru.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
GamP and John - an amazing discovery! I'll have to remember that.


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
jgelona
Master
posted Hide Post
Some functions do get translated and EDIT is one of them as long as it is not a EDIT that converts between alpha/numeric. That has been documented since the FOCUS for UNIX 6.5 Oracle interface manaul and it has worked since we started using WebFOCUS. (I think our first WF release was 4.x.) If you can get/find/steal a copy of this manual, see chapter 7, The Interface Optimizer.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report 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     define based join in db2 sql

Copyright © 1996-2020 Information Builders