User's Guide
   PART 1. Working with Databases
     CHAPTER 2. Connecting to a Database       

Troubleshooting connections

Who needs to read this section? 

In many cases, establishing a connection to a database is straightforward using the information presented in the first part of this chapter.

However, if you are having problems establishing connections to a server, you may need to understand the process by which Adaptive Server Anywhere establishes connections in order to resolve your problems. This section describes how Adaptive Server Anywhere connections work.

The procedure the software follows is exactly the same for each of the following types of client application:

• ODBC     Any ODBC application using the SQLDriverConnect function, which is the common method of connection for ODBC applications. Many application development systems, such as Powersoft PowerBuilder and Power++, belong to this class of application.

• Embedded SQL     Any client application using Embedded SQL and using the recommended function for connecting to a database (db_string_connect).

  The SQL CONNECT statement is available for Embedded SQL applications and in Interactive SQL. It has two forms: CONNECT AS... and CONNECT USING... The CONNECT USING statement uses db_string_connect. All the database administration tools, including Interactive SQL, use db_string_connect.

  The steps in establishing a connection

In order to establish a connection, Adaptive Server Anywhere carries out the following steps:

1. Locate the interface library     The client application must locate the ODBC driver or Embedded SQL interface library.

2. Assemble a list of connection parameters     Connection parameters may be provided in several places, such as data sources, a connection string assembled by the application, and an environment variable. The parameters are assembled into a single list.

3. Locate a server     Using the connection parameters, a database server must be located on your machine or over a network.

4. Locate the database     Once the server is located, the database to which you wish to connect must be located.

5. Start a personal server     If no server can be located, Adaptive Server Anywhere will attempt to start a personal database server and load the database.

Each of these steps is described in detail in the following sections.

  Locating the interface library

The client application makes a call to one of the Adaptive Server Anywhere interface libraries. In general, the location of this DLL or shared library is transparent to the user. Here we describe how the library is located, in case of problems.

ODBC driver location 

For ODBC, the interface library is also called an ODBC driver. An ODBC client application calls the ODBC driver manager, and the driver manager locates the Adaptive Server Anywhere driver.

The ODBC driver manager looks in the supplied data source in the odbc.ini file or registry to locate the driver. When you create a data source using the ODBC Administrator, Adaptive Server Anywhere fills in the current location for your ODBC driver.

Embedded SQL interface library location 

Embedded SQL applications call the interface library by name. The name of the Adaptive Server Anywhere Embedded SQL interface library is as follows:

• Windows NT and Windows 95     dblib6.dll

• Windows 3.x     dblib6w.dll

• OS/2     dblib62.dll

• UNIX     dblib6 with an operating-system-specific extension.

The locations that are searched depend on the operating system:

• PC operating systems     In PC operating systems such as Windows and Windows NT, files are looked for in the current directory, in the system path, and in the Windows and Windows\system directories.

• UNIX operating systems     In UNIX, files are looked for in the system path and the user path.

When the library is located 

Once the interface library is located, a connection string is passed to it. The interface library uses the connection string to assemble a list of connection parameters, which it uses to establish a connection to a server. The following section describes how the list of connection parameters is assembled.

  Assembling a list of connection parameters

The following figure illustrates how the interface libraries assemble the list of connection parameters they will use to establish a connection.

Notes 

Key points from the figure are as follows:

• Precedence     Parameters held in more than one place are subject to the following order of precedence:

  Connection string > SQLCONNECT > data source

  That is, if a parameter is supplied in a data source and in a connection string, the connection string value overrides the data source value.

• Failure     Failure at this stage occurs only if a data source that does not exist in the client connection file is specified in the connection string or SQLCONNECT.

• Common parameters     Depending on other connections already in use, some connection parameters may be ignored. These include the following:

  • Autostop     Ignored if the database is already loaded.

  • CommLinks     The specifications for a network protocol are ignored if another connection has already set parameters for that protocol.

  • CommBufferSize     Ignored if another connection has already set this parameter.

  • CommBufferSpace     Ignored if another connection has already set this parameter.

  • Unconditional     Ignored if the database is already loaded or the server is already running.

When the list of connection parameters is complete, it is used by the interface library to attempt to connect.

  Locating a server

The next step in establishing a connection is to attempt to locate a server. If the connection parameter list includes a server name (ENG parameter), a search is carried out first for a personal server of that name. Following that, a search is carried out over a network. If no ENG parameter is supplied, a default server is looked for.

     If a server is located, the next step is to locate or load the required database on that server. For information, see Locating the database.

     If no server is located, the next step is to attempt to start a personal server. For information, see Starting a personal server.

Notes 

• For local connections, locating a server is simple. For connections over a network, you can use the CommLinks parameter to tune the search in many ways by supplying network communication parameters.

• The network search involves a search over one or more of the protocols supported by Adaptive Server Anywhere. For each protocol, the network library starts a single port. A single port is used for all connections over that protocol at any one time.

• A set of network communication parameters can be specified for each network port in the argument to the CommLinks parameter. These parameters are used only when the port is first started. If a particular network port is already started, any connection parameters for that port in CommLinks are ignored.

• Each attempt to locate a server (the local attempt and the attempt for each network port) involves two steps. The first is to look in the server name cache to see if a server of that name is available. The second is to use the available connection parameters to attempt a connection.

  Locating the database

If a server is successfully located, the next step is to locate the database. The following figure illustrates how databases are located:

  Starting a personal server

If no server can be located, the interface libraries attempt to start a personal server using other parameters. The Start and DBF parameters can be used to start a personal server.

The START parameter takes one of two kinds of argument:

• Personal server command line     To start a personal server.

• Client compatibility command line     For compatibility with Version 5 applications, a client executable command line is accepted.

If a START parameter is not available, there is an attempt to start a personal server on the file indicated by the DBF. If an ENG parameter is supplied in addition to a DBF parameter, it is used as the name of the server.

  Server name caching for faster connections

The network libraries look for a database server on a network by broadcasting over the network using the CommLinks connection parameter.

Tuning the broadcast 

The CommLinks parameter takes as argument a string that lists the protocols to use and, optionally for each protocol, a variety of network communication parameters that tune the broadcast.

     For a complete listing of network communications parameters, see Network communications parameters.

Caching server information 

Broadcasting over large networks to search for a server of a specific name can be time-consuming. To speed up network connections (except for the first connection to a server), when a server is located, the protocol it was found on and its address are saved to a file.

The server information is saved in a file named asasrv.ini, in your Adaptive Server Anywhere executable directory. The file contains a set of sections, each of the following form:

[Server name]
Link=protocol_name
Address=address_string

How the cache is used 

When a connection specifies a server name, and a server with that name is not found, the network library looks first in the server name cache to see if the server is known. If there is an entry for the server name, an attempt is made to connect using the link and address in the cache. If the server is located using this method, the connection is much faster, as no broadcast is involved.

If the server is not located using cached information, the connection string information and CommLinks parameter are used to search for the server using a broadcast. If the broadcast is successful, the server name entry in the named cache is overwritten.

  Interactive SQL connections

The Interactive SQL utility has a different behavior from the default Embedded SQL behavior when a CONNECT statement is issued while already connected to a database. If no database or server is specified in the CONNECT statement, Interactive SQL connects to the current database, rather than to the default database. This behavior is required for database reloading operations.

     For an example, see CONNECT statement.

  Testing that a server can be found

The dbping command-line utility is provided to help in troubleshooting connections. In particular, you can use it to test if a server with a particular name is available on your network.

The dbping utility takes a connection string as a command-line option, but by default only those pieces required to locate a server are used. It does not attempt to start a server.

Examples 

The following command line tests to see if a server named Waterloo is available over a TCP/IP connection:

dbping -c "eng=Waterloo;CommLinks=tcpip"

The following command tests to see if a default server is available on the current machine.

dbping

     For more information on dbping options, see The DBPing utility.