Hi

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

asked 10 May, 09:25

DbWizard's gravatar image

DbWizard
164
accept rate: 0%

edited 13 May, 04:34

Volker%20Barth's gravatar image

Volker Barth
33.5k330483711


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

permanent link

answered 10 May, 12:31

Volker%20DB-TecKy's gravatar image

Volker DB-TecKy
4303513
accept rate: 26%

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

(10 May, 13:42) DbWizard
Replies hidden
1

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

(10 May, 14:29) Volker DB-TecKy

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

(10 May, 15:53) DbWizard
Replies hidden

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

(10 May, 16:08) Volker DB-TecKy

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...

(10 May, 19:46) Volker Barth

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!

(13 May, 02:32) DbWizard
showing 2 of 6 show all flat view

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.

permanent link

answered 13 May, 04:40

Volker%20Barth's gravatar image

Volker Barth
33.5k330483711
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×65
×29
×28
×5

question asked: 10 May, 09:25

question was seen: 106 times

last updated: 13 May, 04:40