 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Connecting 2 different Databases
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Connecting 2 different Databases Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] Connecting 2 different Databases | Login/Join |
| Member |
I'm in WebFocus 7703 with Oracle 10. I need to JOIN/MATCH 2 Tables, easch on two different Databases. I found a USE statement that is suuppose to be put in the configuration file (edsprof.prf or suprof.prf). The full statement found in a search is: USE CAR ON FOCSU01 END My problem is that I put it in the suprof.prf and my fex still can't find the table in the USE pointing to the other server. Has anyon ever done this (connecting 2 dirrerent databases) and if so, is the USE statement the way to go? (Maybe someone can elaborate on it for me?). Thanks so much, gayleThis message has been edited. Last edited by: <Kathryn Henning>, WF 7.7.3 Window XP EXCEL 2010 | ||
|
| Expert |
Gayle, could you please confirm: Are these WebFOCUS databases, like the CAR file? Is this on a mainframe or a windows server? Where are the masters and files for these two databases? If they're in application folders, do you really need a USE statement? I haven't used SU in a long time, isn't that only for updating databases? 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 | |||
|
| Member |
WebFocus is Windows based and my 2 Databases are Oracle tables. The documentation I found regaurding the USE stagement preferred the SUPROF.prf, but said the edaprof.prf was OK. (I hadn't heard of SU until this and I've coded 20yr.) Gayle WF 7.7.3 Window XP EXCEL 2010 | |||
|
| Master |
The three classic WebFOCUS commands for 'connecting' data from different sources are JOIN, MATCH, and MORE. Walter Brengel's presentation is a nice review of these three commands: http://www.informationbuilders.../presentations/13376 The USE command, another method of 'connecting', is for the FOCUS database only. I generally only use MATCH for data I've already extracted with filtering (smaller datasets).This message has been edited. Last edited by: David Briars, | |||
|
| Expert |
Gayle, as far as I know, you shouldn't need USE for Oracle tables (sorry I didn't notice your first statement regarding Oracle). Since you've been coding for 20 years, you know that you need to determine if the metadata (.mas and .acx) are created and their location - they're usually located in an Application directory (C:\ibi\apps\aaa). The metadata names may be different than the Oracle table names. The cross database join could be inefficient if the databases are accessed via different WF data adapter connections. 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 | |||
|
| Expert |
I wonder if the confusion with USE is that it is also an SQL command. Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Virtuoso |
Gayle, If your tables are on 2 different databases, this means you have 2 connections. Can you access each table separately without having to issue a CONNECT? If so and the tables are not too big, you should be able to JOIN. If the tables are large, I suggest you HOLD the data from each TABLE and then either JOIN or MATCH the extracted data. Good luck! Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF | |||
|
| Member |
I have an Oracle table on a server, with a mas & acx created within its won application and there is another table on another server defined with a mas & acx in its own application. They are both in an Oracle databbase structure and I want to know if I can access one from the other and bring them together in one FEX with a JOIN or MATCH. If anyone knows how this can be accomplished, I welcome the help. Thanks so much for all the comments and sugested thoughts so far. Gayle WF 7.7.3 Window XP EXCEL 2010 | |||
|
| Virtuoso |
Gayle, Can you issue a TABLE request to your first Oracle table and get an output? Same to the second? If so, you should be able to do a MATCH between the two. If not, then you have a connection problem. I suggest you do this in steps as suggested above. If both TABLE requests work then issue a MATCH FILE request. If you need some help in coding come back. As for a JOIN, yes, but if the Oracle tables are large you will pay a price in response time. Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF | |||
|
| Member |
I can retreive data from each TABLE with their own TABLE REQUESTS, as long as each TABLE REQUEST is in it's own FOMAIN (I'm in WF MRE). I am trying to see if there is a way to connect the two data sources from both servers before the JOIN/MATCH (at my bosses request) to possibly compare the two databases. Yes, I know there are other toods out there that will do that. He wanted to know if WF could and how. Gayle WF 7.7.3 Window XP EXCEL 2010 | |||
|
| Expert |
I'm not sure what MRE domains have to do with data retrieval. You certainly can join tables from two different databases. If you turn SQL traces on, you will see messages about cross database joins. The Oracle adapter will generate individual SQL SELECT statements and run WebFOCUS JOINs instead of SQL ones. This ends up being the same as extracting the data from each table and then doing the join, except that WF handles it. 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 | |||
|
| Member |
Francis, Thanks so much, but can you be more specific? It seems there should be some code before the JOIN telling WF where the secondary table (ie: server name, database name, etc). That's why I thought the USE command wuld work. It specified the server name with the table name, it just donesn't include the database name. With the trace ON, I still get the same results, 'can't find the table'. Gayle WF 7.7.3 Window XP EXCEL 2010 | |||
|
| Platinum Member |
Is the a connection between the two different Oracle servers? Or could you have one created? With a connection between servers you can develop SQL Oracle SQL Developer to extract the data. Use the WebFocus SQL pass thru to run the SQL. The format the output in WebFocus. Generally it is better to use the SQL pass thru when doing complex things against a SQL data source. Jim Morrow Web Focus 7.6.10 under Windows 2003 MVS 7.3.3 | |||
|
| Virtuoso |
Gayle, To make things clear: 1. The USE command is only for FOCUS files 2. To read an SQL table, WF uses the .acx file linked to the .mas file 3. In the .acx you have a connection attribute so that WF can access the correct server/database Can you post the .acx files for your 2 tables? Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF | |||
|
| Member |
Jim and Danial, I am aware of the SQL option. We jsut finished setting up a new application doing exactly what you recommended. I also understand the ACX files with the MAS's, which is where I"ve been stuck. What we were looking/hoping for was an embedded statement (such as the DYNAM ALOC )that I could put either in the MAS or teh FEX or even the edasprof that would point WF to the other server/database/table. I'm not sure it matters any more but the ACX's aree: SEGNAME=OC_R_FUN, TABLENAME=ADVDBOWNER.R_FUND, CONNECTION=advprd, KEYS=2,$ and SEGNAME=GLCOMPONENTS, TABLENAME=MAXIMO.GLCOMPONENTS, CONNECTION=FAMX7TRN, KEYS=1, $ Thank you very ;much for hanging in there with me and if this additional info triggerrs any new ideas, please feel free to share. Gayle WF 7.7.3 Window XP EXCEL 2010 | |||
|
| Expert |
The acx file tells you everything you need to know: SEGNAME=OC_R_FUN, TABLENAME=ADVDBOWNER.R_FUND, CONNECTION=advprd, KEYS=2,$ The database adapter connection is "advprd". The schema name is 'ADVDBOWNER". The table name is "R_FUND". The master name is "OC_R_FUN". You should be able to find the corresponding acx and mas file, if they were generated. If there's a WF database connection set up for the second database, and you cannot find the metadata, then generate it via the reporting server console. 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 | |||
|
| Virtuoso |
Gayle, Continuing what Francis says: If you can get results from TABLE FILE OC_R_FUN and from TABLE FILE GLCOMPONENTS that means that the connection strings exist. If not, generate them again. If so, then MATCH FILE OC_R_FUN ... RUN FILE GLCOMPONENTS ... END should produce your output file. Good luck. Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF | |||
|
| Member |
Daniel and Francis, Yes, but if GLCOMPONENTS is not defined in the same DOMAIN as OC_R_FUN the FOCEXEC can't find it and you can't create a synonym within an application tht has a different database, different data server in a different application so the FOCEXEC can find it. The only way I've brought data in from another source has been with a DYNAM ALLOC statement in the MAS or FEX, which can't be used in this situation. I was looking for something like it htough that would reference the data server, database and app. Gayle WF 7.7.3 Window XP EXCEL 2010 | |||
|
| Virtuoso |
Gayle, Are you working MRE? WebFOCUS on a MAINFRAME? Can you put both MAS and ACX in the WebFOCUS search path? Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF | |||
|
| Member |
I am in the MRE WF on windows (not MAINFRAME) and I can run a table request with each table in their respective DOMAINS, but am trying to find a way to run them both in the same DOMAIN whhich can point to only 1 data server with 1 database. You can write a FEX ointing to a different database than the database assigned to the DOMAIN by changing the server & app in the properties of the FEX, but again, it's ony 1 server, 1 app with 1 database. I need to access 2 databases, 2 data server in 2 different apps. Gayle WF 7.7.3 Window XP EXCEL 2010 | |||
|
| Silver Member |
So... EDASERV1 contains the connection and definition for OC_R_FUN EDASERV2 contains the connection and definition for GLCOMPONENTS ? And currently, your MR Domains or individual reports (whether they are eon the same MR server or not), contain settings that direct them to use EDASERV1 or EDASERV2? WF: WebFocus 7.7.03 Data: Oracle, MSSQL, DB2 OS: Windows Output: HTML/AHTML,PDF,EXL2K FORMULA, COMT | |||
|
| Expert |
I can't believe this still isn't resolved. As long as both APP folders are in the Application Path then there should be no problems. Or, you can use APP commands to append or prepend app folders: APP SHOWPATH APP LIST APP PREPENDPATH DOGWOOD GEMINI APP SHOWPATH APP SHOWPATH - displays the list of app folders currently in the app path APP LIST - displays the list of app folders found in the approot (usually C:\ibi\apps) APP PREPENDPATH - prepends one of more app folders to the app path APP SHOWPATH - displays the list of app folders currently in the app path - this will include the newly prepended app folders. Or, how about specifying the app: TABLE FILE APP1/TABLE1 SUM ... END TABLE FILE APP2/TABLE2 SUM ... ENDThis message has been edited. Last edited by: Francis Mariani, 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 | |||
|
| Member |
I have been unexpectedly sice last week, so I apologize for not getting back to all of you. This issue has been resolved by using the SQL to access the first table on it's database and a WF table request to HOLD and then a repeat for the 2nd table on it's database and then JOIN the 2 HOLDS. I've not done it yet, but that's the plan. Thanks so much for all the helpful suggestions and ideas and making my first time on FOCAL POINT a great experience! Gayle WF 7.7.3 Window XP EXCEL 2010 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Connecting 2 different Databases
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Connecting 2 different DatabasesCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |