This document will help you troubleshoot problems where a client application using ODBC or Embedded SQL cannot connect to Adaptive Server Anywhere.

Connection attempts from client applications first locate the database server, and then use database, user ID, and password information to connect to a database on that server. This paper focuses on issues where the application cannot locate the database server, and the failed connection attempt returns the error "Database server not running".

This section presents some basic information. Later sections describe how to troubleshoot connections over TCP/IP networks and on SPX or IPX networks.

This document describes Adaptive Server Anywhere 6 and higher. While some concepts apply to SQL Anywhere 5.5, no attempt has been made to document the similarities and differences between SA 5.5 and Adaptive Server Anywhere. In particular, the examples will not work with SA 5.5 without significant changes.

Some examples use Adaptive Server Anywhere 7 executable names for simplicity, but all examples apply to other versions unless otherwise noted. For other Adaptive Server Anywhere versions, replace the 7 in the executable names with the major version number. Many of the examples and troubleshooting steps use the dbping utility. Adaptive Server Anywhere 6.0.0 and 6.0.1 do not include this utility. Users of Adaptive Server Anywhere 6.0.0 and 6.0.1 should consider upgrading to a newer versions since they contain a number of improvements and fixes, including significant performance improvements. Users of Adaptive Server Anywhere 6.0.0 and 6.0.1 who cannot upgrade, can use

dbisql -c "uid=dba;pwd=sql;<ping connection parameters>"

instead of

dbping -c "<ping connection parameters>" .

The Adaptive Server Anywhere Personal Server, dbeng7, supports the SharedMemory protocol on all platforms, as well as a local TCP/IP link on most platforms. The Network Server dbsrv7 supports SharedMemory and TCP/IP on all platforms.

Some Adaptive Server Anywhere version and platform combinations additionally support one or more of SPX, IPX, Netbios and NamedPipes. Most applications and users find the TCP/IP link to be the most reliable and efficient network communication protocol.

Applications can connect to the Personal Server only from the same machine on which the Personal Server is running. The Network Server supports connections from any machine, although SharedMemory and NamedPipes protocols can only be used to connect to servers running on the same machine as the client.

Troubleshooting Connections on a LAN

This section describes the database server command-line switches and client connection parameters needed for the client to locate the server when both client machine and server machine are on the same local area network (LAN).

When both machines are on the same LAN, a typical command-line for the network server is as follows:

dbsrv7 -x <remote communication link> -n <server name> <database file>

For example,

dbsrv7 -x tcpip -n my_asa_srv my_database.db

Other server and/or database command-line switches may be provided, but –n and –x are the relevant switches when establishing connections.

The –n command line switch before any database files specifies the server name. If no –n option is specified, the Adaptive Server Anywhere server name takes the name of the first database’s alias. A database alias is specified by the –n switch after the database file, and defaults to the database file name without a path or extension. On Windows, the Adaptive Server Anywhere server’s window title displays the server name.

The -x command-line switch lists the network protocols that can be used to connect to the server. You can fine tune connection protocols by using communication parameters following the protocol name, but such parameters are generally needed only when the server is not on the same local area network as the client machine, and are discussed later.

On the client side, normally only the ServerName and CommLinks connection parameters are required to locate an Adaptive Server Anywhere database server on the same LAN. The ServerName parameter matches the database server -n switch, and the CommLinks parameter is the client-side equivalent of the database server -x switch.

For example, to test a connection to the Adaptive Server Anywhere server my_asa_srv over TCP/IP, use a command line of the following form:

dbping -c "ServerName=my_asa_srv;CommLinks=tcpip"

For example, to test a connection default database on my_asa_srv with user id "fred" and password "flintstone":

dbping -d -c "eng=my_asa_srv;links=tcpip;uid=fred;pwd=flintstone"

Connection parameter names are case insensitive, but in some cases their values are case sensitive. For example, UiD=fred is allowed, but UiD=Fred fails on a case sensitive database.

ServerName, EngineName and ENG are synonyms. CommLinks, Links are also synonyms.

If you are unable to connect, you should ensure the communication link you are attempting to connect with is able to start successfully on both the Adaptive Server Anywhere server and client. For example, two database servers with the same server name cannot be started on a single TCP/IP or SPX network; the second server still starts, but does not successfully start the communications link on which there is a conflict.

1. Verify that the following lines appear together in the database server window:

Trying to start <communication link> link
<communication link> link started successfully.

If the line <communication link> link not started appears, restart the database server with the -z switch. This adds debugging information to the display, which will help to determine why the link did not start.

1. Add Debug=yes;LogFile=<debug filename> to the connection parameters. Non-Java Adaptive Server Anywhere connect dialogs have a Display debugging information in a log file check-box in the advanced tab which set these additional connection parameters. Attempting to connect to the Adaptive Server Anywhere server will create the debug log file.

2. Inspect the log file, and verify that the line Trying to start <communication link> link ... is followed by the line <communication link> link started successfully in the log file. If the link did not start, there will be debugging information in the log file that will help to determine why the link did not start.

Server Address Caching

When a connection is made to an Adaptive Server Anywhere server, the name and address of that server are saved in a file on the local machine called asasrv.ini (. asasrv.ini on UNIX). This file is called the server address cache. Server address caching can improve connection performance, but can also complicate connection troubleshooting.

This section describes the operation of the server address cache.

Once a server machine is located, and its name and address stored in the server address cache, subsequent connections do not require a network broadcast to all machines on the network. The elimination of unnecessary broadcasts can reduce network traffic and may speed up locating to the server.

The asasrv.ini file is normally in the Adaptive Server Anywhere win32 directory on Windows machines. On UNIX machines, the file is named asasrv.ini and is normally in the user’s home directory.

For example, assume the client machine and the database server are separated by a broadcast-blocking network switch, and the database server is using the TCP/IP communication link. If the server address is not cached, a client application will not be able to connect unless at minimum the TCP/IP host parameter is specified. Once a client application has connected, the server address is cached, and the TCP/IP host parameter is no longer required.

Connection problems can occur if all necessary parameters are not specified, even if the connection currently works because the address is in the server address cache. If the target database server is not running during a connection attempt, it is removed from the server address cache, and the application will not be able to connect once the Adaptive Server Anywhere server is running again. Moving the application to a different machine with the exact same setup may also cause connection failure.

The server address cache file is a convenience – you should not rely on an address being in the cache in order to connect to the server. You should always specify whatever connection parameters would be required if the address were not in the cache. This way, if the address in the cache becomes invalid, for example, if the server is moved to a different machine, or gets removed, if a connection attempt is made while the server is not running, your connection parameters will still allow you to connect to the server.

Troubleshooting TCP/IP Connection Failure

The following steps will help you to locate an Adaptive Server Anywhere server using the Adaptive Server Anywhere dbping utility. Dbping can be used to connect to an Adaptive Server Anywhere server using provided connection parameters. If only the –c parameter is provided to dbping, it locates an Adaptive Server Anywhere server, but does not connect to a database.

Several problems using an Adaptive Server Anywhere client or server over TCP/IP on Windows 2000 have been fixed in Emergency Bug Fixes (EBFs). Versions of Adaptive Server Anywhere running on Windows 2000 which do not include these fixes may have TCP/IP communication problems.

In order to troubleshoot an Adaptive Server Anywhere TCP/IP connection failure, the following information is required:

• The IP address and host name of the Adaptive Server Anywhere server machine
• The port number the Adaptive Server Anywhere server is using
• The Adaptive Server Anywhere server name

The following information is helpful but not required:

• Are the client machine and the Adaptive Server Anywhere server machine on a different TCP/IP subnet?
• Is there a firewall between the client machine and the Adaptive Server Anywhere server machine?
• Is there a broadcast-blocking network switch between the client machine and the Adaptive Server Anywhere server machine?

To determine the IP address and IP host name of the Adaptive Server Anywhere server machine use:

By default Adaptive Server Anywhere uses 2638 as its port number. Specifying –x tcpip(port=xxxx) on the dbsrv7 command line will override the default. If two Adaptive Server Anywhere servers are running on the same machine, they cannot both use the same port number. In Adaptive Server Anywhere 7.0.0 and up, if the default IP port number is in use, the server will automatically choose an available port number. If in any doubt of the Adaptive Server Anywhere server’s IP address or port number, stop the Adaptive Server Anywhere server, and restart it with –z appended to the dbsrv7 command line. In the server window, there will be one or more lines of the format Starting UDP listener on IP address 172.31.142.196:2638.

These lines give the IP address and port on which the server is listening. Note the address 127.0.0.1 is the loopback address – a special address which can be used on any machine to refer to itself. The address 0.0.0.0 is also a special address. Adaptive Server Anywhere for UNIX, as well as Adaptive Server Anywhere 6.0.0-6.0.2 for Windows display the messages My IP address is 172.31.142.196. Adaptive Server Anywhere 7 and up will also display the message Unable to start on default port; starting on port 49152 instead when the default IP port number is already in use.

To verify that the TCP/IP settings on the client and server machines and basic network setup are correct.

1. On the client machine, from a command prompt execute the following command:

ping <IP Address>

For example, if the server machine has an IP address of 172.31.149.196, execute

ping 172.31.149.196 .

2. If this command fails, then the problem is with the IP configuration on the server or client machine, or the network setup including, but not limited, to network cable connections. Correct the problem, and ensure ping is successful before continuing.

This and the following steps attempt connections to the Adaptive Server Anywhere server using dbping. Using dbping eliminates any potential problems with the application or ODBC configuration.

The following procedures are organized as follows. Below is a basic procedure for connecting to a server. But the procedure below may not have the specific information needed to connect. Still, because hosts, serverports and other parameter information may change, it is suggested you try the basic procedure below, and if no successful connection is obtained, move on to test using more specific information.

1. Ensure that the Adaptive Server Anywhere server is running on the server machine.

2. On the client machine, from a command prompt, for Adaptive Server Anywhere 6 only, ensure there is no client profile. Executing dbstop with no parameters will remove a client profile if one exists.

3. Ensure there is no SQLCONNECT environment variable set. Execute SET SQLCONNECT= on Windows. On UNIX, the command to do this will vary depending on which shell is being run.

4. Execute the following command:

dbping -c "eng=<Adaptive Server Anywhere server name>;links=tcpip"

For example:

dbping –c "eng=my_asa_srv;links=tcpip"

5. If successful, proceed to the section titled Using Successful Connection Parameters. This should succeed with most LAN configurations.

Test Adaptive Server Anywhere Connection with the Host Parameter

If testing using no TCP/IP parameters failed, but step 2 succeeded, you may need to specify the Adaptive Server Anywhere TCP/IP Host parameter. Providing the Host parameter may be required if the client machine and the Adaptive Server Anywhere server machine have different TCP/IP subnets, or have a firewall or broadcast-blocking network switch between them.

Repeat the test with more specificity. Use, links=tcpip (Host=<IP address or host name>). For example:

dbping -c "eng=my_asa_srv;links=tcpip(Host=172.31.149.196)"

You may also specify host name instead of IP address in the host parameter. If the IP address works, but the host name does not work, there may a TCP/IP configuration problem. If ping <host name> fails, then there is a TCP/IP configuration problem with the host name.

Test Adaptive Server Anywhere Connection with the Host and ServerPort Parameters

If testing using the host parameter failed, but step 2 succeeded, you may need to specify the Adaptive Server Anywhere TCP/IP port parameter. This should only be required if the server is not using the default port number. For Adaptive Server Anywhere 7, this should only be required if the server is not using the default port number and if the server is running on HP or NetWare. Repeat the step using more specificity, use links=tcpip(Host=<IP address or host name>,port=<port number>). For example:

dbping -c "eng=my_asa_srv;links=tcpip(Host=172.31.149.196;port=49152)"

The item ServerPort may be used instead of port.

Test Adaptive Server Anywhere Connection with host, port and UseUDP=no Parameters

If testing with the host and server port parameters failed, but step 2 succeeded, you may need to specify the Adaptive Server Anywhere TCP/IP UseUDP=no parameter. By default, Adaptive Server Anywhere clients use UDP packets to find the server. If there is a firewall between the client machine and the Adaptive Server Anywhere server machine, the firewall may block UDP packets. The UseUDP=no Adaptive Server Anywhere TCP/IP parameter causes Adaptive Server Anywhere to use strictly TCP packets, and not UDP packets. Repeat the test with more specificity, use links=TCPIP(Host=<IP address or host name>,port=<port number>;UseUDP=no). For example:

dbping -c "eng=my_asa_srv;links=TCPIP(Host=172.31.149.196;port=49152;UseUDP=no)"

If you are still unable to connect, see the section "Verifying the Communication Links Start Successfully". Also, UDP may be used instead of UseUDP. In Adaptive Server Anywhere 7.0.2 and above, the UseUDP parameter has been deprecated. Use the DoBroadcast=None parameter instead of UseUDP=no.

Troubleshooting SPX or IPX Connection Failure

Adaptive Server Anywhere 6 does not support SPX. Adaptive Server Anywhere 7 supports both SPX and IPX, although IPX is deprecated and will not be supported in future versions of Adaptive Server Anywhere. SPX is recommended over IPX when using Adaptive Server Anywhere 7, since SPX is typically more efficient and reliable than IPX. Adaptive Server Anywhere uses the NetWare file server’s bindery or IPX broadcasts to find Adaptive Server Anywhere servers by name. Adaptive Server Anywhere should be able to locate servers without any additional parameters on most LANs.

If you are unable to connect without the Host parameter, you will need to know the Adaptive Server Anywhere server’s address. To find this address, stop the Adaptive Server Anywhere server, and restart it with –z appended to the dbsrv7 command line. In the server window, there will be a line similar to

My IPX/SPX host address is 0:192:79:121:212:95/0:0:0:2 .

Specify this address as a IPX or SPX Host=<server address> parameter. For example:

dbping -c "eng=my_asa_srv;links=spx(host=0:192:79:121:212:95/0:0:0:2)"

If you are still unable to connect, see the section Verifying the Communication Links Start Successfully.

Using Successful Connection Parameters

Once you have the necessary connection parameters to connect to your Adaptive Server Anywhere server, you can try to connect to a database on the server. To connect to a database, the UserID and Password parameters are required unless Integrated Logon is used, and the DatabaseName parameter may also be necessary.

In the ODBC Data Source Configuration dialog assuming a TCP/IP connection: in the "Database" tab, put the Adaptive Server Anywhere server name in the Server Name; in the "Network" tab, check TCP/IP and type any necessary Adaptive Server Anywhere TCP/IP options. In the following example, to connect to the server called my_asa_srv, specify my_asa_srv for the Server Name, tick TCP/IP, and specify the additional TCP/IP options Host=172.31.149.196;port=49152.

dbping -c "eng=my_asa_srv;links=TCPIP(Host=172.31.149.196;port=49152)"