Having trouble with using multiple local database query

[Former Member]

drop server remote_db2;

CREATE SERVER remote_db2
CLASS 'saodbc'
USING 'db2'; // so far so good, no error

CREATE EXISTING TABLE zProxy_pf_DailyValuation_Summary_by_Grp
/* AT 'remote_db2.dba.pf_DailyValuation_Summary_by_Grp'; // not found, so try next */
at 'remote_db2.dba.pf_DailyValuation_Summary_by_Grp';   / also not found

the error message was: Could not execute statement. Remote server '' could not be found SQLCODE=-659, ODBC 3 State="HY000" Line 9, column 1

what did I do wrong?

[VolkerBarth]

CREATE SERVER remote_db2 CLASS 'saodbc' USING 'db2'; // so far so good, no error

Note: The "so far so good" does not really tell that much, AFAIK SQL Anywhere does not try to connect to the remote server when applying the CREATE SERVER statement, so it's quite possible the connection information stored in the DSN "db2" might not be fitting. In that case it would be the common behaviour that CREATE SERVER is successful and the following CREATE EXISTING TABLE statements will fail.

[Former Member]

db2 is not exactly a dsn but rather one of the server names that shows up in the connection popup database tab after clicking find servers I had previously tried also the actual connection string in place of db2;I got the same error in exec the stmt for proxy table what else can do? background: the local databases were prestarted with dbeng11

[Breck_Carter]

This assumes that db2 is the engine and database name....

CREATE SERVER remote_db2 CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 11;ENG=db2;DBN=db2';

CREATE EXISTING TABLE zProxy_pf_DailyValuation_Summary_by_Grp
   AT 'db2_server..dba.pf_DailyValuation_Summary_by_Grp';

If you don't have a DSN you must provide a connection string with DRIVER.

Also, the AT string has four positional components, not three, with the second component (database) being ommitted for SQL Anywhere.

[Former Member]

thx, I tried but failed even explicitly adding the parameter ;DatabaseKey=theRquiredKey also tried using the users dataset 'dsn=d:\\db2.dsn'

The user dataset content is:

[ODBC] DRIVER=SQL Anywhere 11

DatabaseKey=theRquiredKey

Integrated=YES

AutoStop=YES

DatabaseFile=d:\\db2.db

I added extra blank line to the above so the content does not appear as a long line in the resulting comment

[VolkerBarth]

Note: In Breck's sample, the first part of the AT clause should match the server name from the CREATE SERVER statement, i.e.

... AT 'remote_db2..dba.pf_DailyValuation_Summary_by_Grp';

[VolkerBarth]

What is the "The user dataset content"?

The current server (on machine 1) should be able to connect to the remote server with the same connection string information you would use when making, say, an DBISQL connection from machine 1 to the remote server.

[Breck_Carter]

@Volker: Thanks, I fixed that.

[Former Member]

I should clarify. user dataset content is what is in d:\\db2.dsn file - is actually the user defined obdc definition of the local database connection parameters

And I forgot to put an extra blank line after [ODBC] so the illustration got run into non sensical single line

so I tried with error:

CREATE SERVER remote_db2 CLASS 'saodbc' USING 'DBN=db2;DSN=d:\\db2.dsn';
CREATE EXISTING TABLE zProxy_pf_DailyValuation_Summary_by_Grp
at 'remote_db2.dba.pf_DailyValuation_Summary_by_Fin_Grp';

ended up the same old error

[VolkerBarth]

As Breck has stated above:

Also, the AT string has four positional components, not three, with the second component (database) being ommitted for SQL Anywhere.

So I guess your statement is still missing a second dot before the "dba", which makes "pf_DailyValuation_Summary_by_Fin_Grp" the owner name...

Besides that, IMHO, it's ambiguous (or at least not that clear) to put both "DSN=..." and "DBN=..." in the USING clause - the "DBN=..." entry should instead be part of the ODBC DSN entry itself.

[Former Member]

thx.

I just failed to use dsn as part of using parameter. I ended explicitly specify all required values including dbkey value surrounded by double quotes to valid trouble with the single in the dbkey value.

that got me pass the create server statement. Finally executed the query per suggestion without trouble.

thx again