4. Basic Usage

4.1. Starting and Stopping the Server

Starting the server is done with the startserver utility. During installation a script file was created named RUN_ and then the name of the server. Since the file name will vary with your server name, it is generally referred to as the RUN_SERVER file. To start your server, make sure all environment variables (in particular $SYBASE) are set correctly. Check with showserver that the server is not already running - there is no risk of starting the server twice, but you may get some confusing error messages about the master device already being in use. Assuming the server is not started, execute the following: startserver -f RUN_SYBASEAssuming that you named your server "SYBASE" at install time, this should make various messages starting with a timestamp scroll over your screen and hopefully end with messages about the default character set and sort order. If not, read the messages carefully for any errors. If they have scrolled out of your screen buffer you can read the error log file instead, located in the same directory as the RUN_SERVER file.

4.2. Connecting to the Server

Once the server is up and running, you can attempt to connect to it. Start with the simplest tool, which is also the traditional utility for Sybase administration and use; isql. It takes many parameters, as listed in the Utility Guide, but we only need a few. Make your first connection like this: isql -Usa -P -SserverReplace the -S parameter with your logical server name that you chose during installation.

	Remember to change the blank sa password
	The -U parameter is the login name, we are here logging in as "sa" the Sybase equivalent of "root". The password specified with the -P parameter is blank, which is the default password for this user. It should be fairly obvious that you don't want to leave your most powerful login with a blank password. Note that the ASE logins are independent from the OS logins and passwords.

The result should be a simple prompt:

1>

If the connection did not succeed, make sure the server is running and that the port is reported by netstat -na to have a LISTENING state.

You can now start typing T-SQL commands and use the keyword "go" as a terminator.

isql -Usa -P -SSERVER 1> select @@version 2> go ----------------------------------------------------------------------------------------------------------------------------------------------- Adaptive Server Enterprise/12.5.0.3/EBF 10980 ESD#1/P/Linux Intel/Linux 2.4.18-18.7.xsmp i686/rel12503/1919/32-bit/OPT/Mon Mar 24 20:49:12 2003 (1 row affected)

4.3. Your First Useful Commands

The first thing to do is to set a password for "sa" and then create a new login with less privileges that we can use to play around with. By default you are placed in the master database once you are logged in. We will make our new login default to an example database instead to avoid creating objects in the master database by mistake. So, we first set a password for sa by calling the stored procedure sp_password with old and new passwords as parameters:

1> exec sp_password NULL, "Secr3t" 2> go Password correctly set. (return status = 0)

Now sa has a new password, changed from the old null default. We add a new login with sp_addlogin:

1> exec sp_addlogin "sybtest", "SomePass" 2> go Password correctly set. Account unlocked. New login created. (return status = 0)

We can log out by telling isql to disconnect using the exit command.

1> exit

Since this is not a T-SQL command but a directive to isql we don't need to terminate with "go".

The isql command can also be used non-interactively to apply scripts of T-SQL to the server. One such script that is shipped with the server is installpubs2, located in the scripts directory. This is a very simple example database for a bookshop or a publishing house holding data about books, authors, publishers and so on. It is used in Sybase manuals and training courses and also in some SQL books. Microsoft SQL Server contains a similar database in addition to the Northwind example database they have added. In order to create the database, use the -i parameter to read the script in. Have a look at the file first so you understand the basics of what it is doing; it will create a database named pubs2 and several tables populated with data. It is time to execute the script. We'll do this as sa who will also become the owner (dbo - database owner ) of the database. We redirect the output to a file we call errors.out. The -e parameter tells isql to also echo the T-SQL commands to the same file, giving more output but making it easier to match any errors to the commands causing them.

bash$ cd $SYBASE/$SYBASE_ASE/scripts bash$ isql -Usa -PSecr3t -SSYBASE -iinstallpubs2 -e -oerrors.out

In order to allow our new login full privileges to this sample database we change ownership of the database to the new login. Here's how we give the database away with sp_changedbowner:

isql -Usa -PSecr3t -SSYBASE 1> use pubs2 2> go 1> exec sp_changedbowner sybtest 2> go DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role. Database owner changed. (return status = 0)

We can now log in interactively as our new user and check what has been installed.

1> exit bash$ isql -Usybtest -PSomePass -SSYBASE 1> use pubs2 2> go 1> sp_help 2> go [Lots of output deleted - the command displays all objects in the current database]

(Note that we don't actually have to use "exec" to execute a stored procedure, the server will assume any non-keyword is a procedure.)

1> quit

One last command as the sa login in order to make life more convenient when we continue to use our new login - we make the new pubs2 database the default database.

1> exit bash$ isql -Usa -PSecr3t -SSYBASE 1> sp_modifylogin sybtest, "defdb", "pubs2" 2> go Default database changed. (return status = 0)

4.4. Stopping the Server

In order to stop the server in a controlled fashion, log in as sa and issue the shutdown command.

bash$ isql -Usa -PSecr3t -SSYBASE 1> shutdown 2> go Server SHUTDOWN by request. The SQL Server is terminating this process. CT-LIBRARY error: ct_results(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect

You will immediately be disconnected and a message is printed by isql to warn you of this fact. You can check the error log for a message about the server being shutdown and you can verify that the process is no longer running with showserver.

4.5. Maintenance

One of the most important aspects of being a database administrator may be the backup. The I/O load of a relational database means little rest for the hard drives and once a drive fails the database is in need of serious disaster recovery. Even a mistyped command may result in the need to revert to a previous backup generation. For this purpose, a separate server application called the Backup Server is used. It is by default named the same as your server with an extension of BCK. Start it with startserver -f RUN_SYBASE_BCK. Certain commands typed into the isql propmt will be forwarded from the dataserver process to the backupserver process, which will then proceed with the actual backup (in Sybase terminology, this is a database dump) while processing in the database continues unaffected. You should schedule database dumps (usually via cron) to run at low activity hours. A typical full database bacup is simply done like this:

isql -Usa -PSecr3t -SSYBASE 1> dump database pubs2 to "/mnt/backup/pubs2.bkp" 2> go Backup Server session id is: 8. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server. Backup Server: 4.41.1.1: Creating new disk file /mnt/backup/pubs2.bkp. Backup Server: 6.28.1.1: Dumpfile name 'pubs2011710275E ' section number 1 mounted on disk file '/mnt/backup/pubs2.bkp' Backup Server: 4.58.1.1: Database pubs2: 396 kilobytes DUMPed. Backup Server: 4.58.1.1: Database pubs2: 602 kilobytes DUMPed. Backup Server: 3.43.1.1: Dump phase number 1 completed. Backup Server: 3.43.1.1: Dump phase number 2 completed. Backup Server: 3.43.1.1: Dump phase number 3 completed. Backup Server: 4.58.1.1: Database pubs2: 610 kilobytes DUMPed. Backup Server: 3.42.1.1: DUMP is complete (database pubs2). 1>

You can restore this back into your database using the load database command.

As time passes while users are doing modifications in the database, adding, deleting or changing data, all operations are being written to the transaction log. This keeps track of changes so they can be undone by an implicit or explicit rollback, or for the undo/redo phases of revocery at startup. This transaction log should normally be placed on a device of its own for several reasons, but a small test database can be created on a single mixed log and data device.

Apart from the performance benefits of spreading I/O, one reason for keeping the log and data separate is for recovery purposes. You can at regular intervals, depending upon your recovery needs, dump this log of changes to the database. Together with the full database dump, this transaction log dump now constitute an incremental backup. Should a restore become necessary, you can load the database dump, then load all subsequent transaction log dumps. There is even an "until_time" option to the load command enabling you to specify the exact time you want to restore until, abandoning any mistakes done after that time. Dumping the log is done with a similar syntax:

isql -Usa -PSecr3t -SSYBASE 1> dump transaction database to "/mnt/backup/dbtrandump2003_08_27_T23_32.bkp" 2> go

Note that we could not do this with pubs2 as it was not created with a separate log fragment.

Unless you keep dumping the transaction log, it will just keep growing until it fills up it's space and starts reporting error 1105. Users will be suspended and appear to be hanging while the situation remains unresolved. Dump the transaction log to file or tape, or simply truncate it if you don't use incremental backups.

isql -Usa -PSecr3t -SSYBASE 1> dump transaction pubs2 with truncate_only 2> go 1>

Other maintenance commands you should read up on are

• DBCC, the DataBase Consistency Checker which will verify that the physical integrity of the data structures on the ASE devices are OK.

• update statistics, which will make sure that ASE has a correct view of how your data is distributed in your tables, enabling it to make the best decisions of how to retrieve the data in the shortest possible time.

4.6. Other Sybase Utilities

There are some other external utilities that are useful to know. The Java based applications need the JDBC driver installed, this is included with newer servers under the product name jConnect. There is a separate CD that comes with ASE 11.9.2.x and 12.5.0.x called "PC Client CD". This is a set of Windows-based tools for administration, pop the CD into your Windows box and the installer will start up so you can select components from the CD.

• bcp or Bulk Copy is a command-line utility with a plethora of parameters that imports table data from flat files and exports data out to files.

• Sybase Central is a GUI tool for database adminstration. It used to be a native Win32 application, but in ASE 12.5.0.x it is now Java based and can be installed on Linux. Note that you also need to install and register the plug-in for administering ASE since Sybase Central is only a framework which is used for many Sybase products by registering their respective plugins.

• Jisql is a Java based GUI version of isql with some neat features such as command history and table and column name lookup.