Section 17.1. Sybase Architecture

17.1. Sybase Architecture

As in the other database chapters, it is important to understand the design of the database that is being backed up, so this chapter starts with a discussion of Sybase architecture. This is similar to the information provided in Chapter 15, but contains specific information about Sybase. Just as in the overview chapter, we start with the power user's view of the database, continue with a view for the database administrator, and finish with a diagnostic and recovery procedure.

17.1.1. Overview of the Sybase Architecture

Sybase works similarly on Windows and Unix systems and is shipped in two flavorsclient software and server software. Applications use client libraries to communicate with the database server. Client libraries are distributed in a variety of formats and are normally compiled into applications.

Every system that has Sybase applications that run client programs requires a small client software installation. Database servers are normally dedicated systems running only Sybase ASE database software. Sybase server software is high-performance and supports both multiple processors and multithreading. The Sybase system also runs one or more additional server processes such as backup servers (one per system), replication servers, and monitor servers.

Sybase architecture is very similar to that of Microsoft SQL Server because in 1998, Microsoft purchased a license for Sybase's source code and distributed it as Microsoft SQL Server. The architecture, structure, and internal commands are therefore similar between the two products. In Sybase, though, you need to script backups and server maintenance or schedule them using the ASE Job Scheduler. A strength of Sybase is that it scales extremely well from Windows systems through larger Unix systems and has a well-deserved reputation as a high-performance database system.

17.1.2. Sybase Command-Line Utilities

There are a few common Sybase command-line utilities that you should know about. These applications exist both in your OCS- VERSION and ASE- VERSION directory. It does not matter which program you use, but you should make sure the version you use is reasonably current. Very old versions of these clients do not support all the datatypes that the server does.

17.1.2.1. isql

isql is a no-frills command that sends SQL commands to your database server. The basic syntax is isql U user -P password -S server. isql responds with a prompt, (>) at which you can type SQL commands to your server. Transact-SQL (T-SQL) command batches are sent from isql to your server whenever it finds the string go on a separate line. isql can be used to test connectivity and to perform basic server administration.

Other interesting commonly used arguments to isql include i file and o file, which specify input and output files respectively. It is also common, when scripting SQL commands, to use Unix here document syntax. For example:

isql Usa SSERVERNAME Ppassword << EOF exec sp_who go exit EOF

17.1.2.2. bcp

The bcp command imports and exports a single table or view in or out of the database (to a file). This command is used commonly for data loads and for file extracts. bcp runs at the operating system level like isql and takes the following parameters:

bcp [[database_name.]owner.]table_name {in | out} datafile [-c|-n]  -S servername -U username -P password t{fld_delimeter} r{row_delimeter}

The first parameter to bcp is the name of the table or view to be copied. Choose in to copy the data from a file into that table and out to copy the data from that table to a file.

bcp can run in two modes: native and character mode. Native mode (set with the n option) reads and writes output files in system native mode (that is, integers are stored as native integers and floats as native floats). Native mode files are not human-readable but can be significantly smaller and faster than those in character (ASCII) mode. In character mode, the files are human-readable. If you are copying in character mode, you need to specify a field delimiter with the t flag. A rarely used character such as ~ or & makes a good delimiter. If you are using XML, however, you should use other delimiters. Sybase supports multicharacter delimitersfor example, \t~\t as a field delimiter and \r\n as a row delimiter. You could use a comma to create a .csv file this way, but if any of your data contains the delimiter character, your output will be corrupted. Files copied in character mode can be used interchangeably between operating systems while native bcp files scan only be used safely on systems with similar operating systems.

17.1.2.3. dsedit

The dsedit utility is a graphical editor for the Sybase interfaces file. The interfaces file maps server names to a hostname, port/socket number, and network protocol. The network protocol can also contain some other information for situations like fail over, and can contain mappings for the server to listen on more than one network interface or socket. Although the file can usually be edited in a text editor, administrators use dsedit because the interfaces files on some operating systems use a packed notation that is not human-readable (you can read it but it makes no sense).

On Windows, the interfaces file is found in $SYBASE/ini/sql.ini.

The tli string in the Solaris interfaces file is a hex string containing port and IP addresses. If you have an entry that looks like the following:

SYBSRVR  master tli tcp /dev/tcp \x000204018196c4510000000000000000

it can be interpreted as follows:

x0002 header info 0401  port number (1025 decimal) 81    first part of IP address  (129 decimal) 96    second part of IP address (150 decimal) c4    third part of IP address (196 decimal) 51    fourth part of IP address (81 decimal)

This tli address is equivalent to the following entry in other operating systems, where the IP address of system sybhost is 129.150.196.81:

SYBSRVR     master tcp ether sybhost 1025

17.1.3. Required Environment Variables

The following environment variables are commonly set for Sybase:

SYBASE

Set this variable to the parent directory where you installed Sybase. This should not include the ASE version; that is the purpose of the SYBASE_ASE variable.

DSQUERY

Set this variable to the default server you wish to connect to.

SYBASE_ASE

Set this variable to the name of the subdirectory where the version of Sybase you're working with is located. For example, set it to ASE-15_0 if you are working with 15.0, or to ASE-12_5 for 12.5. The server software is located in $SYBASE/$SYBASE_ASE.

PATH

Set the path to include appropriate executable files.

LD_LIBRARY_PATH

Set this variable to help programs you compile find the appropriate libraries.

SYBASE_OCS

Set this variable to OCS-15_0 if your client software is 15.0, or OCS-12_5 if it is version 12.5. The client software is located in $SYBASE/$SYBASE_OCS.

You do not need to set these variables by hand when you log in because Sybase provides prebuilt environment files that set them. These variables are set in the SYBASE.sh and SYBASE.csh files that are located in your $SYBASE directory. You need to source one of the environment files before performing common operations like starting your database server.

Sybase client applications require $SYBASE to be set in order to find the interfaces file and may require $LD_LIBRARY_PATH for applications to find dynamic shared libraries.