We are using SqlAnwhere10 to host multiple databases on the same engine.

This is the command we used to run multiple dbs on one engine:
dbsrv10 -c 8m -n testme ./demo.db -n db_demo ./library.db -n db_library

We have some tables in the demo.db and some in the library.db. We would like to do a join between tables in these two databases. We looked at the SQL SA10 documentation that suggested to create proxy tables. But this doesn't work for us. Trying to create a proxy table gives an error saying table could not be found in remote database.

Does anyone tips on how to get this kind of join to work.


Thanks,
J


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

Proxy tables are the solution. Perhaps you configured the remote server
incorrectly? Or the proxy table?


Jim Douglas wrote:

Chris, here is what we have and trying.

We have 1st database (db1) called demo.db which has a table called employees
We have 2nd database (db2) called library.db which has a table called
subscriber

We want to do a join between demo.employees and library.subscriber. We just
don't understand why we cant do a join using db namespaces.

We started db server to host 2 DBs
dbsrv10 -c 8m -n testme ./demo.db -n db_demo ./library.db -n db_library

Next we setup remote server for db2 which was successful:

create server db2 class 'sajdbc' using 'localhost:2638/library'

Next we tried to create proxy table for subscriber from db2 but this is
failing even though the user has access.

create exisiting table p_subscriber at 'db2...subscriber'

This fails. Error is:
-- Could not execute statement
-- The remote table 'db2...subscriber' could not be found
-- SQLCODE=-666 ODBC 3 State="HY000"
-- Line 1, Column 1

Can you suggest something?

Thanks,
J

"Chris Keating (Sybase iAnywhere)" <keating_spam_free (AT) ianywhere (DOT) com> wrote
in message news:47424b9e$1 (AT) forums-1-dub (DOT) ..

In article <4742567a$1@forums-1-dub>, zzyzxzz (AT) gmail (DOT) com says...
That's just the way ASA works.

Shouldn't this be "/db_library"?


--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Can you please post the command to create a remote DB server.

So far, we have tried all the following combinations and it still does not
seem to work.

create server db2 class 'sajdbc' using '/db_library'
create server db2 class 'sajdbc' using 'localhost:2639/db_library'
create server db2 class 'sajdbc' using 'localhost:2639/library'
create server db2 class 'saodbc' using '/db_library'
create server db2 class 'saodbc' using '/library'

I even tred using Sybase Central to create a remote server and this fails
whether I use odbc or jdbc.

Thanks,

Jim

<ns_dkerber (AT) ns_WarrenRogersAssociates (DOT) com> wrote

I would definitely suggest using ODBC over JDBC for performance reasons. In
this sample, I'm assuming that the SQL Anywhere driver has been properly
installed and is called "SQL Anywhere 10". If you want to create a DSN for
the db2 database, you could replace everything inside the using clause of
the create server command to specify the DSN name instead.

dbinit db1.db
dbinit db2.db
dbeng10 -n MyEng db1.db db2.db

dbisql -c eng=myeng;dbn=db1;uid=dba;pwd=sql
[ISQL] create table t1 ( pkey integer, c1 integer )

dbisql -c eng=myeng;dbn=db2;uid=dba;pwd=sql
[ISQL] create table t2 ( pkey integer, c1 integer )

dbisql -c eng=myeng;dbn=db1;uid=dba;pwd=sql
[ISQL] create server s_db2 class 'saodbc' using 'Driver=SQL Anywhere
10;eng=myeng;dbn=db2;uid=dba;pwd=sql'
[ISQL] create existing table t2 at 's_db2...t2'
[ISQL] select * from t1,t2 where t1.pkey = t2.pkey


--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"


"Jim Douglas" <zzyzxzz (AT) gmail (DOT) com> wrote

Thanks for the step-by-step tips. It appears like the driver may not be
installed because we are seeing the following error at the create server
step:

"Unable to connect to server...'
"Unable to load driver SQL Anywhere10"
SQLCODE=-656 ODBC 3 State="HY000"

How do we make sure the driver is installed and how do we install it.

Thanks,

Jim

"Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName (AT) ianywhere (DOT) com>
wrote in message news:4743584a$1 (AT) forums-1-dub (DOT) ..

After inserting a space and using "SQL Anywhere 10" for drivern name we can
now create remote server.

But there is still problem with creating proxy tables using existing table
command. The error we see is "

Unable to connect to server...'
Parse error: Cannot find .ini file
SQLCODE=-656 ODBC 3 State="HY000"


Jim


"Jim Douglas" <zzyzxzz (AT) gmail (DOT) com> wrote

Can you please post the exact start line you use for the database engine, as
well as the exact CREATE SERVER and CREATE EXISTING TABLE commands you
execute.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"


"Jim Douglas" <zzyzxzz (AT) gmail (DOT) com> wrote

Hi Reg,

I used the exact same commands you have typed in here but just replaced the
db names.

Your tips work for me when I start from scratch with a fresh db. That is,
once I started with a dbinit and added tables, I could do a join between 2
dbs running in the same engine.

But when I try the same sequence without a dbinit and table creates with the
sample demo.db file and another existing sybase db file we have , I notice
..ini file not found error when I try to create a proxy table. The remote
server is created successfully though. Any ideas how to debug this. I could
post the exisitng db file here if that helps.


Jim



"Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName (AT) ianywhere (DOT) com>
wrote in message news:4744869c$2 (AT) forums-1-dub (DOT) ..