When you ask Adaptive Server Anywhere to create a database, it creates the main database file, which contains the following objects, among others:
user tables
indexes
views
system tables
The maximum size of a database file depends on your file system and the page size you choose. Database files are limited to 256 million database pages or the filesize limit, whichever is reached first. UNIX files can be as large as 1 Tb, in some cases-see the Physical Limitations chapter of the Adaptive Server Anywhere Reference Manual or your Linux documentation for more information. You can set pages to be 1, 2, 4, 8, 16, or 32 kb in size, but it is not recommended that you use a page size of 1 kb. The default page size is 2 kb.
By default, Adaptive Server Anywhere also creates a file called the transaction log. Besides improving performance, the transaction log is vital to Adaptive Server Anywhere replication systems and database recovery in event of system failures. When possible, it is recommended that the transaction log be placed on a physical device (in most cases, a disk drive) separate from the main database file, to reduce the chances of both the main database file and transaction log being affected in the event of a media failure. You can specify the name and location of the transaction log when you create the database.
This section shows you how to create databases at either the command prompt or in Interactive SQL. You can also create databases through Sybase Central, if you prefer, by opening the Utilities folder under Adaptive Server Anywhere 7.
The command line utility for creating a database is dbinit.
Syntax:
dbinit [switches] db-file-name
db-file-name is the name you would like to give to your database file, for example, mydb.db. If you issue the command "dbinit -?" you'll be shown the above syntax, along with a list of options you can use.
To create your first Adaptive Server Anywhere database on Linux, enter the following command:
dbinit -t './logs/mydb.log' p 4096 mydb.db
This command creates a database in the current working directory called mydb.db with a page size of 4096 bytes, specified by the -p switch. Assuming the directory exists, it also creates the transaction log mydb.log in the subdirectory "logs," specified by the -t switch. Adaptive Server Anywhere databases carry the extension ".db" .
To create a database in Sybase Central, open the Adaptive Server Anywhere section of the left pane, and select Utilities. Double-click Create Database in the right pane, and follow the on-screen instructions.
There are two versions of the database server installed on your machine. If you are just using Adaptive Server Anywhere locally, use the personal database server (dbeng7). If you are going to connect to the Adaptive Server Anywhere database over a network, however, you should use the network database server (dbsrv7). Examples in this document use dbeng7, but the two commands are, for the most part, interchangeable. See the table below for specific differences.
Table 7. Differences between the Personal and Network database servers
| Personal database server | Network database server | |
| Name of executable | dbeng7 | dbsrv7 |
| Local connections | Yes | Yes |
| Network connections | No | Yes |
| Maximum number of connections | 10 | Depends on license |
| Available communications protocols | Shared memory, TCP/IP | Shared memory, TCP/IP |
| Maximum number of CPUs for request processing | 2 | Unlimited |
| Default/Maximum number of internal threads | 10/10 | 20/Unlimited |
Syntax:
(dbeng7 | dbsrv7) [server-switches] [database-file [database-switches], ]
database-file specifies the path and filename to the database. You aren't actually required to specify a database file when you start up the database server, but if you don't, you must specify a name for the server using the -n switch. By default, if you do not specify a name for the database, it takes on the name of the database file, minus the path and extension. Similarly, if you do not specify a name for the database server (which you can do in server-switches), it takes on the name of the first database that was started on it.
For full details on the usage of dbeng7 and dbsrv7, see "The database server" in the Adaptive Server Anywhere Reference.
To start up the Adaptive Server Anywhere personal database server, but not a database, and name it MyServer, issue the following command at a prompt:
dbeng7 -n MyServer
To start up the Adaptive Server Anywhere personal database server and name it MyServer, then start a database on MyServer from mydb.db, naming it MyDatabase, issue the following command:
dbeng7 -n MyServer mydb.db -n MyDatabase
In the latter case, if you don't name the database server MyServer, it would be named MyDatabase instead.
There's a plethora of other switches available for the server. You can get a full listing of them by typing "dbeng7 -?" at a command prompt. A few important switches include the following:
-c, for specifying Adaptive Server Anywhere's cache size
-x allows you to specify the communications protocols
-gt allows you to specify the number of processors to be used
-ud tells the server to run as a daemon in UNIX (explained below)
Sometimes it's necessary for the server to run outside of the current session (that is, regardless of who, if anyone, is logged in). To do so, use the -ud switch at the command line when starting the server to run it as a daemon.
The following command would start up a database server as a daemon, using the database we created before:
dbsrv7 -ud -n MyDatabase mydb.db
NOTE: Using "&" to run the database server in the background does not work.
Assuming you have the appropriate authority, you can stop the database server using any of the following methods:
the dbstop command line utility
using the STOP ENGINE SQL statement
pressing the Q key when the server display window has the focus
NOTE: While the term engine is part of the SQL statement's name, server is the common term now used. This document will use the term server unless referring explicitly to the STOP ENGINE SQL statement.
By default, any user can stop a personal database server, but only a user with the DBA authority can stop a network database server. (This default can be changed by using the -gk switch when starting the server-see the Adaptive Server Anywhere Reference for details.)
The command line utility syntax is as follows:
dbstop [switches] {name}
If you are issuing dbstop to stop a locally-running server, you can simply specify the name of the database server in {name}. If the server is not running locally, you need to create a connection to the server before you can tell it to stop. The -c switch allows you to specify a connection string for the database running on the server that you would like to stop. To stop MyServer, execute the following command:
dbstop -c "uid=DBA;pwd=SQL;eng=MyServer;dbn=MyDatabase"
In this instance, you could also just give the server name, since the server is running locally:
dbstop MyServer
The first command connects to the database named MyDatabase on the server MyServer, then stops the server named MyServer. In the case that no databases are active on the server, you have to add "dbn=utility_db" to the connection string.
Let's say "Club" is the name of one of the databases running on a server named "Goliath," and you want to stop all the databases running on Goliath, including Club. The following command accomplishes that, as well as shutting down the database server:
dbstop -c "uid=DBA;pwd=SQL;eng=Goliath;dbn=Club"
If you have a database server named "David" running without any databases started on it, you can stop the server using the following command:
dbstop -c "uid=DBA;pwd=SQL;eng=David;dbn=utility_db"
The syntax for the STOP ENGINE statement is as follows:
STOP ENGINE [ server-name ] [ UNCONDITIONALLY ]
The server named server-name is stopped. If server-name is omitted, the currently running database server is stopped. If UNCONDITIONALLY is specified, the database server is stopped whether or not there are still connections to the server.
It's also possible to stop individual databases without stopping the server, or any of the other databases that might be running on it. To do so, use the STOP DATABASE SQL statement.
Syntax:
STOP DATABASE database-name [ON engine-name] [UNCONDITIONALLY]
You specify the name of the database that you would like to stop in database-name, with the restriction that the database specified cannot be the currently connected one. The "ON engine-name" clause can be used only in Interactive SQL. You use it to specify the server that the database is running on. Outside of Interactive SQL, the database can only be stopped if it is on the current server. The UNCONDITIONALLY keyword forces databases to be stopped, even if there are connections to it. By default, you can't stop a database if there are connections active.
You can connect to an Adaptive Server Anywhere database via any of the following interfaces:
ODBC
OLE DB or ADO
Embedded SQL
Sybase Open Client
JDBC
Regardless of how you connect, you must specify some parameters, such as a username and password, to establish a connection to the database. These can be specified in a connection string, the SQLCONNECT environment variable, an ODBC data source configuration, or the fields of a dialog box.
In this section, you'll find explanations on how to connect via SQL and ODBC.
As the Adaptive Server Anywhere network server is a client/server database, you may connect to a Linux-hosted database from Windows-based PCs and other non-Linux devices, as well as Linux applications. Programming interfaces such as OLE DB or ADO are available ony on Windows, but can still be used against a Linux-hosted database.
Connection strings are frequently used when performing actions on a database. They consist of a list of parameter settings, delimited by semicolons and enclosed in double quotes. There should be no extra spaces in a connection string.
Example:
"uid=DBA;pwd=SQL"
The short strings of letters just before each equal sign (in this example, uid, pwd, and dbf) are called keywords, which each correspond to a connection parameter. There are many connection parameters available, and they are listed in the Connecting to a Database chapter of the Adaptive Server Anywhere User's Guide. They are also described in detail in the Connection and Communication Parameters chapter of the Adaptive Server Anywhere Reference.
When Adaptive Server Anywhere utilities are looking for connection parameters, they check the SQLCONNECT environment variable for any parameters that were left out of the connection string. If you're putting connection parameters into the SQLCONNECT environment variable, replace the equal signs with number (#) signs. In bash you would use the following command:
SQLCONNECT='uid#DBA;pwd#SQL'
The single quotes are necessary in the above command because semicolons can be used to separate bash commands. You can also use double quotes.
To make SQLCONNECT available in subsequent shells, you'd need to use "export SQLCONNECT" to export the SQLCONNECT variable to the environment. You may also want to put these commands into your .bash_profile (or .profile, if you're using another shell) if you want the same connection parameters to be available each time you log in.
To connect to a database from Interactive SQL, go to the Command menu, and choose "Connect...", then fill in the dialog box as appropriate.
ODBC (which stands for Open Database Connectivity) is an industry-standard interface for connecting client applications to relational and non-relational DBMSes. When you create an ODBC data source, it encapsulates the data and any other information required to get the data, including connection parameters.
To connect to Adaptive Server Anywhere from ODBC applications on Linux, you can either use Sybase's ODBC driver as a driver manager, or use a third-party ODBC driver manager such as iODBC or unixODBC. If you choose the latter route, follow the installation instructions for the driver manager you've chosen and choose dbodbc7.so (which resides in the sybase/SYBSsa7/lib directory) as the ODBC driver for Adaptive Server Anywhere.
If you choose the former route, you can use Adaptive Server Anywhere's ODBC driver as a driver manager if you will only be connecting to Adaptive Server Anywhere databases. To do so, you need to create a few symbolic links so that ODBC driver manager requests get routed to the Sybase ODBC driver. From the sybase/SYBSsa7/lib subdirectory, enter the following commands:
$ ln -s dbodbc7.so libodbc.so
$ ln -s dbodbc7.so libodbc.so.1
$ ln -s dbodbc7.so libodbcinst.so
$ ln -s dbodbc7.so libodbcinst.so.1
That's it!
Data sources exist on the client computer, with at least one for each database accessible via ODBC. They reside in the .odbc.ini file or in a separate .dsn file.
If the client computer is running Linux or another UNIX operating system, ODBC data sources can be used both for ODBC applications as well as for the Interactive SQL and Sybase Central utilities.
NOTE: The database server looks for .odbc.ini in the following locations, among several others:
ODBCINI environment variable
ODBCHOME and HOME environment variables
The user's home directory
The current directory
The path
The root directory
If no .odbc.ini file exists in your home directory, you'll have to create one in your home directory. You can check if one exists by using the command "ls -a ~/.odbc.ini".
You manage ODBC data sources using the dbdsn command line utility.
Syntax:
dbdsn [ modifier-switches ]
{ -l
| -d dsn
| -g dsn
| -w dsn [details-switches]
| -cl }
|
dbdsn has four main modes of operation, and its behaviour depends on whether you choose the -l, -d, -g, or -w switch. Where applicable, the name of the data source to be operated on is specified by dsn.
the -l switch lists the data sources that have been defined
the -d switch deletes the specified data source
the -g switch gives you the details of the specified data source
the -w switch creates a new DSN using parameters specified in details-switches
The most important details-switch is the -c switch, which allows you to specify the usual database connection parameters. You can also specify the name of a database server as a details-switch. Type "dbdsn -cl" to display a list of available connection parameters.
To create a new data source named MyNewDSN for the server MyServer, execute the following command at a shell prompt:
dbdsn -w MyNewDSN -c "uid=dba;pwd=sql;eng=MyServer"
If there is a data source named MyNewDSN already existing, dbdsn asks if you would like to overwrite it.
Conversely, to delete MyNewDSN, execute the following command:
dbdsn -d MyNewDSN
The modifier-switches control how dbdsn outputs its messages to screen, and whether or not data sources can be overwritten without confirmation. For more information on other dbdsn options, see "The Data Source utility" under the Database Administration Utilities chapter of the Adaptive Server Anywhere Reference.
Once you've created an ODBC data source, you can access it through the DSN (DataSourceName) connection string keyword.
For an ODBC data source called mydatasrc, for example, use the following connection string to connect to the database associated with it:
"dsn=mydatasrc"
NOTE: Explicitly-provided connection parameters and SQLCONNECT override any parameters provided in the ODBC data source, in that order.
NOTE: The FileDSN connection parameter is not yet available in version 7.0.2 of Adaptive Server Anywhere. Future versions of Adaptive Server Anywhere should support File DSNs.