Question Re: View containing data from multiple databases in Technology Q&A

View containing data from multiple databases

Former Member — Fri, 10 May 2019 09:25:17 GMT

In a 3rd party integration szenario I have the following challenge:

On 1 Sybase server, there are 3 ERP companies stored in 3 dbs. The table structures on these 3 dbs are 100% identical. In my application I would like to show data from all 3 companies together in 1 view. Let's say, I want to see addresses from all 3 companies.

Is there a way to create a view containing data from more than 1 db (meaning from more than 1 company)?

Tanks in advance, your help is greatly appreciated. Heinz

Re: View containing data from multiple databases

Former Member — Fri, 10 May 2019 12:31:52 GMT

Hello Heinz,

not sure if I got your question right, but if: You may create a remote server entry for each of your DBs that is not local, and then a proxy table for each of these tables. It'll probably be helpful to use a different owner for each remote user which will allow you to use identical sets of proxy user names. Finally, you create a view unioning (UNION ALL) the proxy tables (and a local table, if required). It's probably a good idea to add a column with a literal value unique to each branch of the union indicating the origin / physical location of the rows.
However, the optimizer's capabilities to optimize queries with non-trivial restrictions against such a view may be limited. You may have to use stored procedures with a result set as wrappers in cases where this causes problems.

HTH

Volker

Re: View containing data from multiple databases

Former Member — Fri, 10 May 2019 13:42:35 GMT

Hi Volker Thank you for your answer. I probably don't have too many administrative rights on that Sybase server. If I got your answer right, there is nothing like "just a view" containing the rows of all companies? The DBs are all local on the same machine in the same database engine (in sql server this would equal to "in the same instance"). So maybe or should I say hopefully no need for remote servers? And yes, I this works, I will add a column as an indicator per row. Thanks much for clarifying. Heinz

Re: View containing data from multiple databases

Former Member — Fri, 10 May 2019 14:29:16 GMT

That's one probably the most fundamental difference between SQL Anywhere and MSSQL (or ASE). You connect to a single database, not to a server. Connectionwise, databases on the same server are just as separate as they would be on different servers. At the same time, you don't need administrative rights on the server, but the specific rights to create proxy objects in the database holding them plus credentials for the database you try to access via proxy.

HTH

Volker

Re: View containing data from multiple databases

Former Member — Fri, 10 May 2019 15:53:21 GMT

Ouch, seems to be my misconception ;-(

When reading about possible solutions, I found this post on Stackoverflow. https://stackoverflow.com/questions/31113458/how-to-join-tables-in-different-database-on-the-same-sybase-server

Made me thinking it should be possible to just change the DB within the same connection. Just out of curiosity: Whats going on in the following snippet? Do they change the DB anyway, or do I misundertand the Term DB?

select tabA.*,tabC.* from DatabaseB..TableA tabA, DatabaseA..TableC tabC where tabA.xxx = tabC.xxx

Thanks again to bring some light into it. Very appreciated Heinz

Re: View containing data from multiple databases

Former Member — Fri, 10 May 2019 16:08:02 GMT

The Stackoverflow is about SAP (formerly Sybase) Adaptive Server Enterprise aka ASE. If you're using ASE, it works precisely like that, but it would mean that you're asking in the wrong forum. If you're using SAP (formerly Sybase) SQL Anywhere, you're in the right place here, but the Stackoverflow post doesn't match your environment.

HTH

Volker

Re: View containing data from multiple databases

VolkerBarth — Fri, 10 May 2019 19:46:11 GMT

Just to add to ASE: If you are familiar with MS SQL, ASE is in many respects rather similar because both share a common origin...

Re: View containing data from multiple databases

Former Member — Mon, 13 May 2019 02:32:04 GMT

I was not aware about the difference between this 2 products. But a least, I'm in the right forum here. Thanks for sharing your knownledge!

Re: View containing data from multiple databases

VolkerBarth — Mon, 13 May 2019 04:40:06 GMT

Here are some more questions with samples how to access tables from other SQL Anywhere databases:

I'm sure there are several more tagged woth "remote-data-access" or "proxy-tables".

As to the UNION ALL statement: In case performace suffers (this is sometimes a problem with remote data), it's a common strategy to copy remote data to the "local" database, say to a (possibly local temporary table) via INSERT ... SELECT FROM myProxyTable, and then to run the UNION ALL on the local copy.