User's Guide
   PART 4. Database Administration and Advanced Use
     CHAPTER 28. Accessing Remote Data       

Troubleshooting remote data access

This section provides some hints for troubleshooting remote servers.

  Features not supported for remote data

The following Adaptive Server Anywhere features are not supported on remote data. Attempts to use these features will therefore run into problems:

• ALTER TABLE statement against remote tables

• Triggers defined on proxy tables will not fire

• SQL Remote

• Java data types

• Foreign keys that refer to remote tables are ignored

• The READTEXT, WRITETEXT, and TEXTPTR functions.

• Positioned UPDATE and DELETE

• UPDATE and DELETE requiring an intermediate temporary table.

• Backwards scrolling on cursors opened against remote data. Fetch statements must be NEXT or RELATIVE 1.

• If a column on a remote table has a name that is a keyword on the remote server, you cannot access data in that column. Adaptive Server Anywhere cannot know all of the remote server reserved words. You can execute a CREATE EXISTING TABLE statement, and import the definition but you cannot select that column.

  Case sensitivity

The case sensitivity setting of your Adaptive Server Anywhere database should match the settings used by any remote servers accessed.

Adaptive Server Anywhere databases are created case insensitive by default. With this configuration, unpredictable results may occur when selecting from a case sensitive database. Different results will occur depending on whether ORDER BY or string comparisons are pushed off to a remote server or evaluated by the local Adaptive Server Anywhere.

  Connectivity problems

Take the following steps to be sure you can connect to a remote server:

• Determine that you can connect to a remote server using a client tool such as Interactive SQL before configuring Adaptive Server Anywhere.

• Perform a simple passthrough statement to a remote server to check your connectivity and remote login configuration. For example:

  FORWARD TO testasa {select @@version}

• Turn on remote tracing for a trace of the interactions with remote servers.

  SET OPTION cis_option = 2

  General problems with queries

If you are faced with some type of problem with the way Adaptive Server Anywhere is handling a query against a remote table, it is usually helpful to understand how Adaptive Server Anywhere is executing that query. You can display remote tracing as well as a description of the query execution plan:

SET OPTION cis_option = 6

  Queries blocked on themselves

If you access multiple databases on a single Adaptive Server Anywhere server, you may need to increase the number of threads used by the database server on Windows NT using the -gx command-line switch.

You must have enough threads available to support the individual tasks that are being run by a query. Failure to provide the number of required tasks can lead to a query becoming blocked on itself.