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?

asked 19 Apr '15, 02:47

gg99's gravatar image

gg99
193202229
accept rate: 0%

edited 20 Apr '15, 11:00

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109

Comment Text Removed
1

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.

(20 Apr '15, 03:09) Volker Barth
Comment Text Removed

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

(20 Apr '15, 12:33) gg99

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.

(20 Apr '15, 13:48) Breck Carter
Replies hidden

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

(20 Apr '15, 17:06) gg99

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';

(21 Apr '15, 03:18) Volker Barth

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.

(21 Apr '15, 03:21) Volker Barth

@Volker: Thanks, I fixed that.

(21 Apr '15, 13:30) Breck Carter

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

(22 Apr '15, 00:35) gg99

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.

(22 Apr '15, 03:17) Volker Barth
More comments hidden
showing 3 of 9 show all flat view

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

permanent link

answered 23 Apr '15, 13:00

gg99's gravatar image

gg99
193202229
accept rate: 0%

converted 24 Apr '15, 03:08

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665

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:

×137
×63
×6
×3

question asked: 19 Apr '15, 02:47

question was seen: 647 times

last updated: 23 Apr '15, 13:00