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     [SOLVED] Switching Servers

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Switching Servers
 Login/Join
 
intsoccersuperstar
Member
posted
I am trying to write a query utilizing data from two servers. My code looks like this:

SQL SQLORA SET SERVER server1
SQL SQLORA PREPARE SQLOUT FOR

[code]

;

END
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS firstdata
END

SQL SQLORA SET SERVER server2
SQL SQLORA PREPARE SQLOUT2 FOR

[code that wants to access my output from the first query...doesn't seem to recognize the existence of the firstdata table]

END
TABLE FILE SQLOUT2
PRINT *
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET PAGE-NUM OFF
END

As always any help would be greatly appreciated, thanks!

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


intsoccersuperstar
WebFOCUS 8105m
Windows, Excel
 
Posts: 16 | Registered: October 17, 2018Report This Post
BabakNYC
Virtuoso
posted Hide Post
In your example, the HOLD file is a binary temp file on disk not an Oracle table. I doubt you'll be able to write SQL Passthru code utilizing that HOLD file. How are you trying to access the file created from the first query?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
intsoccersuperstar
Member
posted Hide Post
I simply want to join the file created from the first query to a table that resides on server2. Utilizing SQL, if possible, but if that is not an option I can try to do it in WebFocus language.


intsoccersuperstar
WebFOCUS 8105m
Windows, Excel
 
Posts: 16 | Registered: October 17, 2018Report This Post
MartinY
Virtuoso
posted Hide Post
You will need write access on the server1.
Also, server2 must be able to read server1.
But you should be able to do the following (or similar to):
SQL SQLORA SET SERVER server1
SQL SQLORA PREPARE SQLOUT FOR

[code]

;

END

ENGINE SQLORA SET DEFAULT_CONNECTION Connexion_ToServer1

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS firstdata FORMAT SQLORA
END

SQL SQLORA SET SERVER server2
SQL SQLORA PREPARE SQLOUT2 FOR


Connexion_ToServer1 must be defined as a valid connexion in RS that will point to server1 and a valid DB where you have (or the generic user that runs the query) the write access.

But then, I'm not sure if the "firstdata" will already be available to query against it from a direct SQL.

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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
BabakNYC
Virtuoso
posted Hide Post
The problem with writing this into an Oracle instance is that it'll persist in the DBMS. DBA's usually don't like users writing stuff into their databases. If all you want to accomplish is to join the answer sets from two Oracle instances, you could create two hold files and join them for the final output. That way the hold files will go away once you're done.

By the way I think you should be able to create master files for the two Oracle servers and use WebFOCUS to join them. In that case you'll have to use WebFOCUS code to accomplish the query instead of SQL. That might be a better approach.

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


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
intsoccersuperstar
Member
posted Hide Post
I found a kind of embarrassingly easy fix; I was doing what I was doing because I only needed to access a single table on server 2, but apparently I could pull that into my original query simply with the following:

tablename@server2 alias

I think I overcomplicated things when trying to explain what I was trying to do. Thanks for your help Babak and Martin.


intsoccersuperstar
WebFOCUS 8105m
Windows, Excel
 
Posts: 16 | Registered: October 17, 2018Report 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     [SOLVED] Switching Servers

Copyright © 1996-2020 Information Builders