The administration of a Relational Database Management System (RDBMS) is not normally viewed as an enjoyable task by the general population of the computer science profession. The current technology behind such systems is more than twenty years old, and the standard database access language is now hindering the development of true object-oriented database design.
However, the World Wide Web is generating renewed interest in database systems. To construct a web application that truly interacts with browser clients almost mandates the use of a database server.
An RDBMS enables the fast storage and retrieval of large amounts of information. The interface that is used to insert, manipulate, and extract data is called the Structured Query Language (SQL), which was developed by IBM for their DB2 database product in the 1970s.
Two database servers will be addressed in this text: PostgreSQL and Sybase.
PostgreSQL, the successor to the Postgres RDBMS, implements a subset of ANSI-standard SQL and runs on a variety of UNIX platforms. Precompiled binaries for PostgreSQL are now shipped standard in Red Hat Linux 6.0. PostgreSQL was developed in a university environment, and the performance of the database at high loads is not good.
Sybase Adaptive Server Enterprise is a commercial RDBMS that has been
available since 1983.
Sybase is very powerful, but it's administration is much more complex than
PostgreSQL. It is available freely on Red Hat Linux with few restrictions.
The RPM binaries for Sybase are on the CD included with this text in the
/sybase directory.
The implementation of a Sybase database will require much more
planning than PostgreSQL.
Without an interface to link it to the Web, however, a SQL database will be of little use. Middleware software is required to meld HTML and SQL into a single format. This software will be discussed in the next chapter on Dynamic Content Web Sites.
If preparing a fresh installation of Red Hat Linux 6.0,
installation of PostgreSQL is simple. Just select the SQL server
from the “Components to Install” menu at setup time, then later
in the setup indicate that the PostgreSQL server should be
started at boot in the “Services” menu. The software will be
installed, a “postgres” user will be added as a DataBase
Administrator (DBA), and startup scripts and links will be
installed under /etc/rc.d to spawn the database
server in the appropriate system run levels (supported
by the UNIX init utility).
If the installed copy of Red Hat Linux 6.0 did not include the database components, they can be installed them at a later time if access to the RPMs containing PostgreSQL can be obtained. The RPMs can be found on the distribution CD included with this text (under RedHat/RPMS) or off the Red Hat FTP site. They can be installed and configured by running the following commands as root:
rpm -Uvh postgresql-6.4.2-3.i386.rpm rpm -Uvh postgresql-clients-6.4.2-3.i386.rpm rpm -Uvh postgresql-devel-6.4.2-3.i386.rpm ntsysv
The
ntsysv command will allow the selection of which
system services are started at boot time (it creates links from
files in
/etc/rc.d/init.d to files in
/etc/rc.d/rcX.d). If you want PostgreSQL to start at boot time,
make sure that it is selected.
If you are running an earlier release of Red Hat Linux (4.2 or
below), you can still install an RPM containing PostgreSQL. Look
for postgresql in the contrib areas on the ftp://ftp.redhat.com
site.
The following set of examples will create a database that implements a Web-enabled shopping list.
Once PostgreSQL is installed, its system databases must be initialized. This will not be necessary on an upgrade installation, but it will be required. If the installer is unsure if this step is necessary, the root account should startup the Postgres subsystem with the following command:
/etc/rc.d/init.d/postgresql start
If this command fails, the following commands must be run (otherwise, they should be skipped):
su - postgres PGDATA=/var/lib/pgsql PGLIB=/usr/lib/pgsql initdb exit /etc/rc.d/init.d/postgresql start
Afterwards, users should be created that will maintain the databases. Those users must also be registered with the PostgreSQL server.
The commands below will add a user named
luser to
a Red Hat system, then register both
luser and
nobody with the database server.
If a different account name than
luser is desired, just substitute the modified name for
luser in the rest of this document.
As the root user, issue the following commands:
useradd luser su - postgres createuser luser Enter user's postgres ID or RETURN to use unix user ID: 500 -> (enter) Is user "luser" allowed to create databases (y/n) y Is user "luser" allowed to add users? (y/n) n createuser: luser was successfully added createuser nobody Enter user's postgres ID or RETURN to use unix user ID: 99 -> (enter) Is user "nobody" allowed to create databases (y/n) n Is user "nobody" allowed to add users? (y/n) n createuser: nobody was successfully added don't forget to create a database for nobody exit
(The text in boldface above
indicates the commands you must enter, while the text in the
normal typeface indicates the system's response).
Don't forget to set the UNIX password for the “luser”
account with the passwd command.
The user
nobody needs to be registered with the
database because the Web server runs under this userid - PostgreSQL
makes a strong association between the UNIX user and the database user,
unlike Sybase where the two are completely separate. This
action will enable the Web server to run queries against the
database server.
Next, login as “luser” and run the following command:
createdb shopping
This command creates a database. Older DBMS applications, like dBase and its derivatives, called tabular collections of data databases. Relations could be established between these databases. This is not so in PostgreSQL. In this new paradigm, databases contain tables, and these tables contain the tabular data. Relations are established between tables that lie within a database. The previous command only creates the database; the tables are created in a later step.
Now, while logged in as “luser,” enter the following command to initiate the interactive SQL interpreter:
psql shopping
The SQL interpreter will print a welcome message:
Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: shopping shopping=>
SQL commands may be entered directly at the prompt. They may span many lines if necessary. The command is not issued until a semicolon is encountered.
Enter the following text at the prompt to create a table to hold the shopping list:
CREATE TABLE list (
item TEXT,
vendorcode INT,
quantity INT);
This command creates a table named “list,” which is composed of three fields. The fields correspond to columns in a spreadsheet (although a PostgreSQL database can be orders of magnitude larger than the largest spreadsheets). The names of the fields are “item,” “vendorcode,” and “quantity.” The fields have an associated data type. For instance, the vendorcode and quantity fields are both defined as type integer (they can only contain numbers, not text). The item field is of type text, and its size can be of any length.
SQL is not case-sensitive, so any combination of upper- and lower-case characters can be used with its commands. The style used here follows that of most popular tutorial texts on database design, which allows one to easily distinguish the SQL reserved words from the variable names.
The following command creates another table named “vendors” with two fields, one for integers, the other for text.
CREATE TABLE vendors (
vendorcode INT,
vendorname TEXT);
The tables have been created and they are empty. The SQL INSERT command can be used to populate the tables with several values, as shown:
INSERT INTO vendors VALUES (100, 'Super Grocer');
INSERT INTO list VALUES ('Root Beer', 100, 3);
The previous insert syntax is valid and is very useful for one-time jobs. However, if columns are added to or removed from the tables, the above insert syntax will most likely fail. An alternate syntax is available that can specify the columns into which data will be inserted. Any columns not mentioned will receive NULL values (if such values are allowed).
INSERT INTO vendors (vendorcode, vendorname)
VALUES (101, 'General Department Store');
INSERT INTO vendors (vendorcode, vendorname)
VALUES (102, 'General Auto Parts');
INSERT INTO list (item, vendorcode, quantity)
VALUES ('Ice Cream', 100, 1);
INSERT INTO list (item, vendorcode, quantity)
VALUES ('Napkins', 101, 50);
INSERT INTO list (item, vendorcode, quantity)
VALUES ('Spark Plugs', 102, 4);
The data can be examined with the SQL SELECT command, shown here:
SELECT item, vendorcode, quantity FROM list;
The SQL interpreter should respond with:
item |vendorcode|quantity -----------+----------+-------- Root Beer | 100| 3 Ice Cream | 100| 1 Napkins | 101| 50 Spark Plugs| 102| 4 (4 rows)
Fields can be rearranged or omitted from the SELECT command by modifying the field names:
SELECT item, quantity FROM list; item |quantity -----------+-------- Root Beer | 3 Ice Cream | 1 Napkins | 50 Spark Plugs| 4 (4 rows)
As an alternative, an asterisk can be used to indicate that all fields are desired from the SELECT query:
SELECT * FROM list; item |vendorcode|quantity -----------+----------+-------- Root Beer | 100| 3 Ice Cream | 100| 1 Napkins | 101| 50 Spark Plugs| 102| 4 (4 rows)
An asterix can be very useful when querying a database with interactive sessions. However, its use should be avoided in programs, as columns inserted into or removed from the database will change the physical order of the data returned. Specifying the desired columns will prevent this effect.
Notice in the previous table that the numerical vendorcodes are printed rather than the more useful vendornames. The latter are actually contained within the “vendors” table:
SELECT * FROM vendors;
vendorcode|vendorname
----------+------------------------
100|Super Grocer
101|General Department Store
102|General Auto Parts
(3 rows)
In the case of these two tables, however, the vendorcode field is not very useful. It would be much more appropriate to list the vendorname field from the vendors table when printing the list table. Such a thing is possible by establishing a relation:
SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode = vendors.vendorcode;
item |vendorname |quantity
-----------+------------------------+--------
Root Beer |Super Grocer | 3
Ice Cream |Super Grocer | 1
Napkins |General Department Store| 50
Spark Plugs|General Auto Parts | 4
(4 rows)
In the example above, the FROM clause specifies that two tables are to be used. The WHERE clause specifies the conditions for the relation. Relations such as these are called joins in SQL.
When two tables are used in a SELECT statement, such as the one above, the tablename.fieldname syntax is used to distinguish between the tables and fields.
There are many more options to the SQL SELECT statement; so many that SELECT is the most powerful command in the SQL language. Here is a slight variation on the previous example:
SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode = vendors.vendorcode
ORDER BY item;
item |vendorname |quantity
-----------+------------------------+--------
Ice Cream |Super Grocer | 1
Napkins |General Department Store| 50
Root Beer |Super Grocer | 3
Spark Plugs|General Auto Parts | 4
(4 rows)
Here the ORDER BY clause causes the output to be sorted alphabetically by the item field.
It is also possible to use UNIX Regular Expressions in SELECT statements with PostgreSQL (a feat which not many commercial database servers can equal):
SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode = vendors.vendorcode
AND list.item ~ '^[I-N]'
ORDER BY item;
item |vendorname |quantity
---------+------------------------+--------
Ice Cream|Super Grocer | 1
Napkins |General Department Store| 50
(2 rows)
One interesting point about SQL join operations is that records in one table that will not join with records in the other are omitted (although such records can be included with an outer join). If you run the following INSERT command:
INSERT INTO list (item, vendorcode, quantity)
VALUES ('African Violet', 103, 1);
And then immediately follow it with the previous SELECT statement:
SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode = vendors.vendorcode
ORDER BY item;
You will notice that the “African Violet” row in the “list” table was not printed. However, if you run the following command (and then re-run the SELECT):
INSERT INTO vendors (vendorcode, vendorname) VALUES (103, 'ACME Plant Store');
It will appear.
Records can be modified with the SQL of the following syntax:
UPDATE list SET item = 'African Violets' WHERE vendorcode = 103;
The above command modifies all records from vendor 103. There is only a single item in the table with this vendor, so the behavior in this case is correct. A more restrictive WHERE clause might be required were there additional records with the same vendor.
If you wish to delete rows from the database, you can use the SQL DELETE command:
DELETE FROM list WHERE item = 'African Violet'; DELETE FROM vendors WHERE vendorcode = 103;
Be careful, because “DELETE FROM list;” would wipe out all the data, leaving the table empty.
In a production environment -- where tables contain a large number of rows -- SQL operations may be faster if an “index” is defined. In this case, the commands to create the indexes are:
CREATE INDEX listtab ON list (vendorcode); CREATE UNIQUE INDEX vendortab ON vendors (vendorcode);
Notice that a unique index is created on the “vendors” table, because each vendor will have a unique vendor code.
PostgreSQL keeps copies of modified or deleted rows in a database. This allows the database to be restored to the state it had at a previous date. Unfortunately, this also means that a large amount of storage could be consumed by inactive data. To clean your database of such inactive data, use the commands:
VACUUM list; VACUUM vendors;
In the next section, the Web server will be connecting to the database to perform SELECT operations. Under Red Hat Linux, the Web server process runs under user identity “nobody.” To grant this user permission to SELECT from the database, run the following commands:
REVOKE ALL ON list FROM nobody; GRANT SELECT ON list TO nobody; GRANT INSERT ON list TO nobody; REVOKE ALL ON vendors FROM nobody; GRANT SELECT ON vendors TO nobody;
The REVOKE ALL commands above remove all access permissions. Using such a REVOKE before a GRANT ensures that no previous permissions remain to the user.
If you are finished with the SQL interpreter, you can log out of it by typing:
\q
The pg_dump command is a useful utility that
allows easy backup and transport of PostgreSQL databases. It
generates a text file composed of the SQL commands required to
recreate a database. To dump the shopping database, use the shell
command line:
pg_dump shopping > db.out
This file can be manipulated with a normal text editor. To
reload the database, enter the shell command:
psql -e shopping < db.out
As a last point,
psql uses the GNU Readline
library. It is configured by default to accept Emacs keystrokes
to edit the command line (that is, the up and down arrows cycle
through the previous and next commands, Control-A moves to the
beginning of the line, and so forth). However, if you write the
single line:
set editing-mode vi.inputrc located in your account home
directory, then Vi commands can be used instead. Be warned that
this changes all programs that use GNU Readline, including Bash
and Gdb.
The Sybase Adaptive Server Enterprise RDBMS is much faster and more powerful than PostgreSQL, but it also requires much more planning and effort to install properly.
The Sybase server is included on the CD-ROM included with this book.
It can be found in the /sybase directory.
Sybase produces an HTML document with extensive documentation on the installation of Linux Sybase, but the document can be confusing, so the installation will also be fully documented in this chapter.
The server software will require approximately 110MB of disk space. The documentation, if installed, requires another 40MB. Additional space must be allocated for user databases (this space must be set aside before the database is even created).
The RPM will install the server software in the /opt
directory. Unless a separate file system has been created and mounted
as /opt, this will place the Sybase server in the root file system.
This is not a desirable location.
The /home directory is usually mounted as a separate file
system. If a soft link from /opt to /home is
placed in the root directory, the Sybase server software can be easily
installed in /home. This location will also make the preparation
of PHP slightly easier, as is explained in the next chapter.
Enter the following commands as the super user to set the link from
/opt to /home:
cd /; ln -s home opt
The Sybase Adaptive Server Enterprise is installed by running the following command against the RPM:
rpm -Uvh sybase-ase-11.0.3.3-1.i386.rpm
The server license agreement will be displayed in the terminal window. The space bar can be used to page through the text. At the end of the license, the agreement must be accepted before installation can begin. To accept the agreement, enter:
Yes
The normal RPM software installation will commence. When it is completed, a prompt will be issued to create a “sybase” user and group. If the user and/or group already exists, it will not be affected (it is safe to answer yes if the server is being installed a second time). To add the “sybase” user and group, enter:
y
If the “sybase” group does not exist, a prompt will be issued to create it. To create the “sybase” group, enter:
y
If the “sybase” user does not exist, a prompt will be issued to create it. To create the “sybase” user, enter:
y
The system will prompt for a UNIX password for the “sybase” account in the usual way.
After the password has been set for “sybase”, the installation of the server software is complete.
If desired, the Sybase documentation can be installed with the following command:
rpm -Uvh sybase-doc-11.0.3.3-1.i386.rpm
To configure and activate the server with sybinit, enter
the command:
su - sybase
A prompt will be issued to proceed with sybinit. To continue,
enter:
y
The following menu will be displayed:
SYBINIT 1. Release directory: /home/sybase 2. Edit / View Interfaces File 3. Configure a Server product 4. Configure an Open Client/Server product Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Enter 3 and press return to configure the server. The following menu will be displayed:
CONFIGURE SERVER PRODUCTS
Products:
Product Date Installed Date Configured
1. SQL Server 10 Sep 1998 17:47
2. Backup Server 10 Sep 1998 17:47
Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help.
Enter the number of your choice and press return:
Enter 1 and return to configure the SQL server.
NEW OR EXISTING SQL SERVER 1. Configure a new SQL Server 2. Configure an existing SQL Server 3. Upgrade an existing SQL Server Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Enter 1 and return to configure a new SQL server.
ADD NEW SQL SERVER 1. SQL Server name: SYBASE Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
The name of the Sybase server must now be chosen.
If several Sybase servers are to be installed on the local network, it
would be best to give each server a unique name (not the default name of
SYBASE). However, if the server will never communicate
with other Sybase database servers, it is convenient to leave the name
as SYBASE.
If there are several Sybase servers which must communicate, descriptions
of all servers must be entered into a plain text file named
/home/sybase/interfaces. Each Sybase server must have its
own copy of the interfaces file (it is analogous to the /etc/hosts
file).
To enter a new name, enter 1 and press return. Enter a new server name, and press return. Then hold the Control key and press “A” to continue.
To keep the server name SYBASE, hold the Control key and
press “A” to continue.
SQL SERVER CONFIGURATION 1. CONFIGURE SERVER'S INTERFACES FILE ENTRY Incomplete 2. MASTER DEVICE CONFIGURATION Incomplete 3. SYBSYSTEMPROCS DATABASE CONFIGURATION Incomplete 4. SET ERRORLOG LOCATION Incomplete 5. CONFIGURE DEFAULT BACKUP SERVER Incomplete 6. CONFIGURE LANGUAGES Incomplete 7. CONFIGURE CHARACTER SETS Incomplete 8. CONFIGURE SORT ORDER Incomplete 9. ACTIVATE AUDITING Incomplete Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Each step in the above menu must be completed before the SQL server can be activated. Some steps will require substantial configuration, while others will present settings that are normally only reviewed and confirmed
To begin the configuration, enter 1 and return.
SERVER INTERFACES FILE ENTRY SCREEN
Server name: SYBASE
1. Retry Count: 0
2. Retry Delay: 0
3. Add a new listener service
Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help.
Enter the number of your choice and press return:
Enter 3 and return to configure the listener service for the SQL server.
EDIT TCP SERVICE 1. Hostname/Address: gondor 2. Port: 3. Name Alias: 4. Delete this service from the interfaces entry Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Enter 2 and return to configure the TCP port for the SQL server.
Enter 7100 for the port and press return.
Finally, hold the Control key and press “A” to continue.
When asked if the information is correct, answer Y.
To exit the SERVER INTERFACES FILE ENTRY menu, hold the Control key and press “A”.
When asked if the interfaces file should be written, answer Y.
SQL SERVER CONFIGURATION 1. CONFIGURE SERVER'S INTERFACES FILE ENTRY Complete 2. MASTER DEVICE CONFIGURATION Incomplete 3. SYBSYSTEMPROCS DATABASE CONFIGURATION Incomplete 4. SET ERRORLOG LOCATION Incomplete 5. CONFIGURE DEFAULT BACKUP SERVER Incomplete 6. CONFIGURE LANGUAGES Incomplete 7. CONFIGURE CHARACTER SETS Incomplete 8. CONFIGURE SORT ORDER Incomplete 9. ACTIVATE AUDITING Incomplete Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Note that the first step is now complete. Press 2 and return to configure the master device.
MASTER DEVICE CONFIGURATION 1. Master Device: /home/sybase/master.dat 2. Size (Meg): 21 Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Hold the Control key and press “A” to exit the master device configuration. Alternately, you may adjust the size or location of the master device with the options on the menu (unless you know why you want to change these options, it is safe to leave them at their default).
The following warning message will be issued:
WARNING: '/home/sybase/master.dat' is a regular file which is not
recommended for a Server device.
This warning can be ignored. Linux supports only regular files (not block mode devices). Press return to pass the warning.
SQL SERVER CONFIGURATION 1. CONFIGURE SERVER'S INTERFACES FILE ENTRY Complete 2. MASTER DEVICE CONFIGURATION Complete 3. SYBSYSTEMPROCS DATABASE CONFIGURATION Incomplete 4. SET ERRORLOG LOCATION Incomplete 5. CONFIGURE DEFAULT BACKUP SERVER Incomplete 6. CONFIGURE LANGUAGES Incomplete 7. CONFIGURE CHARACTER SETS Incomplete 8. CONFIGURE SORT ORDER Incomplete 9. ACTIVATE AUDITING Incomplete Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Press 3 and return to configure the system procedures database.
SYBSYSTEMPROCS DATABASE CONFIGURATION 1. sybsystemprocs database size (Meg): 16 2. sybsystemprocs logical device name: sysprocsdev 3. create new device for the sybsystemprocs database: yes 4. physical name of new device: /home/sybase/sybprocs.dat 5. size of the new device (Meg): 16 Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Hold the Control key and press “A” to exit the system procedures database configuration. Alternately, you may adjust the size or location of the system procedures database and device with the options on the menu (unless you want to add stored procedures in the future, it is safe to leave the settings at their default).
SQL SERVER CONFIGURATION 1. CONFIGURE SERVER'S INTERFACES FILE ENTRY Complete 2. MASTER DEVICE CONFIGURATION Complete 3. SYBSYSTEMPROCS DATABASE CONFIGURATION Complete 4. SET ERRORLOG LOCATION Incomplete 5. CONFIGURE DEFAULT BACKUP SERVER Incomplete 6. CONFIGURE LANGUAGES Incomplete 7. CONFIGURE CHARACTER SETS Incomplete 8. CONFIGURE SORT ORDER Incomplete 9. ACTIVATE AUDITING Incomplete Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Press 4 and return to configure the error log.
SET ERRORLOG LOCATION 1. SQL Server errorlog: /home/sybase/install/errorlog Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Hold the Control key and press “A” to exit the error log configuration. Alternately, you may adjust the location of the error log with the options on the menu.
SQL SERVER CONFIGURATION 1. CONFIGURE SERVER'S INTERFACES FILE ENTRY Complete 2. MASTER DEVICE CONFIGURATION Complete 3. SYBSYSTEMPROCS DATABASE CONFIGURATION Complete 4. SET ERRORLOG LOCATION Complete 5. CONFIGURE DEFAULT BACKUP SERVER Incomplete 6. CONFIGURE LANGUAGES Incomplete 7. CONFIGURE CHARACTER SETS Incomplete 8. CONFIGURE SORT ORDER Incomplete 9. ACTIVATE AUDITING Incomplete Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Press 5 and return to configure the backup server.
SET THE SQL SERVER'S BACKUP SERVER 1. SQL Server Backup Server name: SYB_BACKUP Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
If you have changed the name of your Sybase server, then you should change the name of the backup server as well..
When the name of the backup server is configured, Hold the Control key and press “A” to exit.
SQL SERVER CONFIGURATION 1. CONFIGURE SERVER'S INTERFACES FILE ENTRY Complete 2. MASTER DEVICE CONFIGURATION Complete 3. SYBSYSTEMPROCS DATABASE CONFIGURATION Complete 4. SET ERRORLOG LOCATION Complete 5. CONFIGURE DEFAULT BACKUP SERVER Complete 6. CONFIGURE LANGUAGES Incomplete 7. CONFIGURE CHARACTER SETS Incomplete 8. CONFIGURE SORT ORDER Incomplete 9. ACTIVATE AUDITING Incomplete Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Press 6 and return to configure the server language.
CONFIGURE LANGUAGES
Current default language: us_english
Current default character set: ISO 8859-1 (Latin-1) - Western European 8-b
character set.
Current sort order: Binary ordering, for the ISO 8859/1 or Latin-1 charact
(iso_1).
Select the language you want to install, remove, or designate as the default la
guage.
Language Installed? Remove Install Make default
1. us_english yes no no yes
2. chinese no no no no
3. french no no no no
4. german no no no no
5. japanese no no no no
6. spanish no no no no
Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help.
Enter the number of your choice and press return:
Unless you are bilingual, hold the Control key and press “A” to exit.
SQL SERVER CONFIGURATION 1. CONFIGURE SERVER'S INTERFACES FILE ENTRY Complete 2. MASTER DEVICE CONFIGURATION Complete 3. SYBSYSTEMPROCS DATABASE CONFIGURATION Complete 4. SET ERRORLOG LOCATION Complete 5. CONFIGURE DEFAULT BACKUP SERVER Complete 6. CONFIGURE LANGUAGES Complete 7. CONFIGURE CHARACTER SETS Incomplete 8. CONFIGURE SORT ORDER Incomplete 9. ACTIVATE AUDITING Incomplete Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Press 7 and return to configure the character sets.
CONFIGURE CHARACTER SETS
Current default language: us_english
Current default character set: ISO 8859-1 (Latin-1) - Western European 8-b
character set.
Current sort order: Binary ordering, for the ISO 8859/1 or Latin-1 charact
(iso_1).
Select the character set you want to install, remove, or designate as the defau
set.aracter
Character set Installed? Remove Install Make default
1. ASCII, for use with unsp yes no no no
2. Code Page 437, (United S no no no no
3. Code Page 850 (Multiling no no no no
4. ISO 8859-1 (Latin-1) - W yes no no yes
5. Macintosh default charac no no no no
6. Hewlett-Packard propriet no no no no
Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help.
Enter the number of your choice and press return:
Hold the Control key and press “A” to exit.
SQL SERVER CONFIGURATION 1. CONFIGURE SERVER'S INTERFACES FILE ENTRY Complete 2. MASTER DEVICE CONFIGURATION Complete 3. SYBSYSTEMPROCS DATABASE CONFIGURATION Complete 4. SET ERRORLOG LOCATION Complete 5. CONFIGURE DEFAULT BACKUP SERVER Complete 6. CONFIGURE LANGUAGES Complete 7. CONFIGURE CHARACTER SETS Complete 8. CONFIGURE SORT ORDER Incomplete 9. ACTIVATE AUDITING Incomplete Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Press 8 and return to configure the sort order.
CONFIGURE SORT ORDER
Current default language: us_english
Current default character set: ISO 8859-1 (Latin-1) - Western European 8-b
character set.
Current sort order: Binary ordering, for the ISO 8859/1 or Latin-1 charact
(iso_1).
Select a sort order.
Sort Order Chosen
1. Binary ordering, for the ISO 8859/1 or Latin-1 character set (is yes
2. General purpose dictionary ordering. no
3. Spanish dictionary ordering. no
4. Spanish case and accent insensitive dictionary order. no
5. Spanish case insensitive dictionary order. no
6. Dictionary order, case insensitive, accent insensitive. no
7. Dictionary order, case insensitive. no
8. Dictionary order, case insensitive with preference. no
Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help.
Enter the number of your choice and press return:
Hold the Control key and press “A” to exit.
SQL SERVER CONFIGURATION 1. CONFIGURE SERVER'S INTERFACES FILE ENTRY Complete 2. MASTER DEVICE CONFIGURATION Complete 3. SYBSYSTEMPROCS DATABASE CONFIGURATION Complete 4. SET ERRORLOG LOCATION Complete 5. CONFIGURE DEFAULT BACKUP SERVER Complete 6. CONFIGURE LANGUAGES Complete 7. CONFIGURE CHARACTER SETS Complete 8. CONFIGURE SORT ORDER Complete 9. ACTIVATE AUDITING Incomplete Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Press 9 and return to configure server auditing.
ACTIVATE AUDITING 1. Install auditing: no 2. sybsecurity database size (Meg): 5 3. sybsecurity logical device name: sybsecurity 4. create new device for the sybsecurity database: no Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Unless you want to configure auditing and are aware of the storage requirements, hold the Control key and press “A” to exit.
SQL SERVER CONFIGURATION 1. CONFIGURE SERVER'S INTERFACES FILE ENTRY Complete 2. MASTER DEVICE CONFIGURATION Complete 3. SYBSYSTEMPROCS DATABASE CONFIGURATION Complete 4. SET ERRORLOG LOCATION Complete 5. CONFIGURE DEFAULT BACKUP SERVER Complete 6. CONFIGURE LANGUAGES Complete 7. CONFIGURE CHARACTER SETS Complete 8. CONFIGURE SORT ORDER Complete 9. ACTIVATE AUDITING Complete Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
The configuration of the SQL server is now complete. Hold the Control key and press “A” to activate the server.
When prompted to “Execute the SQL Server Configuration Now?” answer:
Y
The master device warning will be repeated. It can be ignored - press return to skip past it.
The system will issue the following messages as the server is configured:
Running task: create the master device. Building the master device ..........Done Task succeeded: create the master device. Running task: update the SQL Server runserver file. Task succeeded: update the SQL Server runserver file. Running task: boot the SQL Server. waiting for server 'SYBASE' to boot... waiting for server 'SYBASE' to boot... Task succeeded: boot the SQL Server. Running task: create the sybsystemprocs database. sybsystemprocs database created. Task succeeded: create the sybsystemprocs database. Running task: install system stored procedures. ................................................................................ ................................................................................ ........................Done Task succeeded: install system stored procedures. Running task: set permissions for the 'model' database. Done Task succeeded: set permissions for the 'model' database. Running task: set the default character set and/or default sort order for the SQL Server. Setting the default character set to iso_1 Sort order 'binary' has already been installed. Character set 'iso_1' is already the default. Sort order 'binary' is already the default. Task succeeded: set the default character set and/or default sort order for the SQL Server. Running task: set the default language. Setting the default language to us_english Language 'us_english' is already the default. Task succeeded: set the default language. Configuration completed successfully. Press <return> to continue.
Press return to continue to the next phase of the server installation.
NEW OR EXISTING SQL SERVER 1. Configure a new SQL Server 2. Configure an existing SQL Server 3. Upgrade an existing SQL Server Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Hold the Control key and press “A” to exit this screen.
CONFIGURE SERVER PRODUCTS
Products:
Product Date Installed Date Configured
1. SQL Server 10 Sep 1998 17:47 08 Nov 1998 13:25
2. Backup Server 10 Sep 1998 17:47
Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help.
Enter the number of your choice and press return:
Hold the Control key and press “A” to exit this screen.
SYBINIT 1. Release directory: /home/sybase 2. Edit / View Interfaces File 3. Configure a Server product 4. Configure an Open Client/Server product Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
The next step will add an interface description for the backup server. Press 2 and enter to add the interface entry.
INTERFACES FILE TOP SCREEN Interfaces File: 1. Add a new entry 2. Modify an existing entry 3. View an existing entry 4. Delete an existing entry Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Press 1 and return to configure the backup server interface file entry.
CREATE NEW INTERFACES FILE ENTRY 1. Server name: Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
The name of the backup server must be entered. Press 1 and return to add the new server name. Enter the name SYB_BACKUP (or the name that you have chosen for your backup server) and press return. Hold the Control key and press “A” to accept the name.
SERVER INTERFACES FILE ENTRY SCREEN
Server name: SYB_BACKUP
1. Retry Count: 0
2. Retry Delay: 0
3. Add a new listener service
Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help.
Enter the number of your choice and press return:
At this menu, enter 3 and return to add a listener for the backup server.
EDIT TCP SERVICE 1. Hostname/Address: gondor 2. Port: 3. Name Alias: 4. Delete this service from the interfaces entry Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Enter 2 and return to configure the TCP port for the backup server.
Enter 7110 for the port and press return.
Finally, hold the Control key and press “A” to continue.
When asked if the information is correct, answer Y.
To exit the SERVER INTERFACES FILE ENTRY menu, hold the Control key and press “A”.
When asked if the interfaces file should be written, answer Y.
INTERFACES FILE TOP SCREEN Interfaces File: 1. Add a new entry 2. Modify an existing entry 3. View an existing entry 4. Delete an existing entry Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Hold the Control key and press “A” to leave the interfaces menu.
SYBINIT 1. Release directory: /home/sybase 2. Edit / View Interfaces File 3. Configure a Server product 4. Configure an Open Client/Server product Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Now that the backup server is defined in the interfaces file, it must be configured and activated. Press 3 and enter.
CONFIGURE SERVER PRODUCTS
Products:
Product Date Installed Date Configured
1. SQL Server 10 Sep 1998 17:47 08 Nov 1998 13:25
2. Backup Server 10 Sep 1998 17:47
Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help.
Enter the number of your choice and press return:
Press 2 and enter to configure the backup server.
NEW OR EXISTING BACKUP SERVER 1. Configure a new Backup Server 2. Configure an existing Backup Server Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Press 1 and enter to configure a new backup server.
ADD NEW BACKUP SERVER 1. Backup Server name: SYB_BACKUP Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
If the name of the backup server is correct, hold down the control key and press “A”.
BACKUP SERVER CONFIGURATION 1. Backup Server errorlog: /home/sybase/install/backup.log 2. Enter / Modify Backup Server interfaces file information 3. Backup Server language: us_english 4. Backup Server character set: iso_1 5. Backup Server tape configuration file: /home/sybase/backup_tape.cfg Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Hold down the Control key and press “A” to start the backup server.
When asked if the backup server should be configured, answer “Y”.
The system will issue the following messages as the server is configured:
Running task: update the Backup Server runserver file. Task succeeded: update the Backup Server runserver file. Running task: boot the Backup Server. waiting for server 'SYB_BACKUP' to boot... Task succeeded: boot the Backup Server. Configuration completed successfully. Press <return> to continue.
The last step that remains is to configure the system libraries. Press return to proceed.
NEW OR EXISTING BACKUP SERVER 1. Configure a new Backup Server 2. Configure an existing Backup Server Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Hold down the Control key and press “A” to return to the previous menu.
CONFIGURE SERVER PRODUCTS
Products:
Product Date Installed Date Configured
1. SQL Server 10 Sep 1998 17:47 08 Nov 1998 13:25
2. Backup Server 10 Sep 1998 17:47 08 Nov 1998 17:41
Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help.
Enter the number of your choice and press return:
Hold down the Control key and press “A” to return to the previous menu.
SYBINIT 1. Release directory: /home/sybase 2. Edit / View Interfaces File 3. Configure a Server product 4. Configure an Open Client/Server product Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help. Enter the number of your choice and press return:
Press 4 and enter to configure the Sybase libraries.
CONFIGURE CONNECTIVITY PRODUCTS
Products:
Product Date Installed Date Configured
1. Open Client Library 10 Sep 1998 17:4
2. Open Server Library 10 Sep 1998 17:4
3. Embedded SQL/C Precomp 10 Sep 1998 17:4
Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help.
Enter the number of your choice and press return:
Press
Press
Press
CONFIGURE CONNECTIVITY PRODUCTS
Products:
Product Date Installed Date Configured
1. Open Client Library 10 Sep 1998 17:4 08 Nov 1998 17:4
2. Open Server Library 10 Sep 1998 17:4 08 Nov 1998 17:5
3. Embedded SQL/C Precomp 10 Sep 1998 17:4 08 Nov 1998 17:5
Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help.
Enter the number of your choice and press return:
All tasks within sybinit are now complete.
Hold down the Control key and press “A”
two times to exit the sybinit utility.
When sybinit has exited to a shell prompt, exit from
the prompt to return to super user status.
Following are three steps that can be taken to further configure Sybase Adaptive Server Enterprise 11.0.3.3 on Linux:
/etc/profile, add the following lines after the first
PATH statement:SYBASE=/home/sybase
export SYBASE
PATH="$PATH:$SYBASE/bin"cd /etc/rc.d/rc3.d
ln -s ../init.d/sybase S85sybase
ln -s ../init.d/sybase K15sybase
cd /etc/rc.d/rc5.d
ln -s ../init.d/sybase S85sybase
ln -s ../init.d/sybase K15sybase
isql) is not as powerful as some open-source
tools that have become available. In particular, the sqsh
SQL shell for Sybase includes support for GNU Readline and X windows.
The sqsh home page is at http://www.voicenet.com/~gray/sqsh.html
and a copy of the source and a binary are included in the sybase
directory of the CD that accompanies this text.
Copy the sqsh binary to a location that
is visible in the shell path ($SYBASE/bin is a good location).
Note that the manual page will not be installed if the package is not built.
glibc).
The Sybase server will not run on systems with the older C library
(libc5), but there are a set of CT-Lib client libraries available
for these platforms (libc5 platforms include Red Hat Linux 4.2,
Slackware 3.2, etc.). The DB-Lib library is specifically not
available. These libraries will enable older Linux systems to access any
TCP/IP-enabled Sybase server, and they are included in the
sybase/oldlibs directory on the CD-ROM that accompanies this
text.
The best way to shut down the Sybase server is with the following set of commands:
[root@gondor /root]# /home/sybase/bin/isql -U sa -P -S SYBASE
1> shutdown
2> go
Server SHUTDOWN by request.
The SQL Server is terminating this process.
DB-LIBRARY error:
Unexpected EOF from SQL Server.
Please note that when the password is changed for the Sybase “sa” account in the next section, that password must be provided as an argument to the “-P” parameter above. Substitute the appropriate server name for the “-S” parameter if it is not SYBASE.
Unlike PostgreSQL, Sybase will not natively use space in a Linux file system
(no matter what the type - ext2fs or otherwise). In a UNIX environment,
the preferred approach to Sybase data storage is a raw character-mode
disk partition, effectively a partition made with fdisk
that is formatted and utilized directly by Sybase.
Linux does not support the use of character-mode disk partitions.
Fortunately, Sybase also
supports the use of “device” files -
standard files created anywhere in the
file system (not to be confused with the I/O device files in the
/dev directory). Sybase device files are initialized and formatted
by Sybase, both in the sybinit installer and in the interactive SQL interpreter.
The drawback to placing Sybase database files within the file system is that write operations are buffered. Sybase can write data to its device files and receive a confirmation of the write, but find that Linux has buffered the data and the write has not actually taken place at all. In the case of a server crash, data which Sybase confirmed as safely committed to the media is actually lost.
It is actually possible to use a raw disk partition (such as
/dev/sdb1) as a Sybase device file, but the partitions
are block-mode devices and will be buffered. At this time, there is
no character-mode storage that would bring high-reliability to Linux
Sybase ASE.
It is unfortunate that Sybase is shipped with default settings that will quickly destroy the server if they are not changed.
Sybase is shipped with a single default device called “master.” If the master device becomes full, the server will crash. The first thing that the DBA should do is create a new set of devices for database storage, select one of the new devices as the default storage device, then remove the default storage designation from the master device.
Some recommend that no default devices be selected. This will force an explicit device selection when databases are created, and will ensure that those who create databases make conscious decisions about the allocation of storage.
Performance can be greatly improved when devices are created on physically separate disk drives, especially in high-utilization environments.
The size of Sybase devices is specified in 2KB blocks (1MB = 512 blocks).
These configuration changes are made with the Sybase “isql”
(Interactive SQL) utility, or sqsh if it was installed.
isql can be invoked with:
isql -U sa -S SYBASE
If sqsh with the same arguments, an interactive SQL session
will be initiated that is supported by GNU Readline (for command repetition)
and X Windows.
The “-U” option selects the account under which to login.
The “-S” option selects the server (it must be defined in the
interfaces file). This option can be omitted if the server is named SYBASE,
since this server name is assumed by default. If the shell cannot find
the isql binary, enter the full path (/home/sybase/bin/isql, or
/opt/sybase/bin/isql, or the location for sqsh).
The user will be prompted for a password
before the SQL session begins; there is no initial password for the
“sa” account, so just press enter.
The version number of the Sybase server can be printed with the following command:
select @@version go SQL Server/11.0.3.3/P/Linux Intel/Linux 2.0.36 i586/1/OPT/Thu Sep 10 13:42:44 CEST 1998
The following commands, which will add a new 20MB default device and
remove the master device from the default device list, should be entered at
the isql prompt:
disk init name="gendev", physname="/home/sybase/gendev.dat", vdevno=2, size=10240 go sp_diskdefault master, defaultoff go sp_diskdefault gendev, defaulton go
At this point, a password should be set for the sa account,
and additional users can be created.
These users can be granted permission to create databases. Some DBAs are hesitant to grant such privileges to their users, and prefer to create all databases themselves and transfer ownership afterwards.
Please note that Sybase users have absolutely nothing to do with UNIX accounts. In fact, Sybase runs under many non-UNIX operating systems that have no support for UNIX logins.
The commands below create a set of users similar to the PostgreSQL examples earlier in this chapter:
sp_password NULL, sapass go sp_addlogin luser, luserpass go sp_adduser luser go grant create database to luser go sp_addlogin nobody, nobodypass go sp_adduser nobody go
A Sybase database has two main types of storage: data areas and transaction logs. Data areas contain the data associated with tables and indexes. Transaction logs contain the sequential changes to the database which form a complete audit trail.
Database backups are normally two-phase. Occasional complete database backups are augmented by more regular backups of transactions. In the event that the database must be restored from the backup media, the complete database is restored first, then the incremental transactions are restored afterwards.
Making a backup of the transaction log will cause the log to be cleared.
If the transaction log becomes full, no more changes to the database can
take place. The transaction log can be immediately cleared by issuing
the SQL command “dump transaction with no_log,”
but this invalidates all incremental backups; a complete database dump
must then be performed to ensure data integrity (this will also produce
a stern warning in the error log).
The data areas and transaction logs can share the same space on a device, or they can be allocated separately. However, if they share the same space, the transaction log can never be backed up separately from the database - all backups that are performed will be complete database backups; incremental transaction log backups will not be allowed.
Enter the following to create a 5MB device for transaction logs:
disk init name="gentran", physname="/home/sybase/gentran.dat", vdevno=3, size=2560 go quit
The rest of this example can be conducted as user luser.
Use isql to log in as luser:
isql -U luser -S SYBASE
To create a shopping database similar to the one discussed earlier in this
chapter which utilizes the devices created with the previous commands,
enter the following at the isqlprompt:
CREATE DATABASE shopping ON gendev=20 LOG ON gentran=5 go
Please note that the sizes listed above are in megabytes, unlike the
2KB page size used by disk init.
The Sybase luser account can be modified to use the
shopping database by default:
sp_modifylogin luser, defdb, shopping go
To finish creating a database similar to the one created in the PostgreSQL examples earlier in this chapter, enter:
USE shopping
go
CREATE TABLE list (
item VARCHAR(32) NULL,
vendorcode INT NULL,
quantity INT NULL)
go
CREATE TABLE vendors (
vendorcode INT NULL,
vendorname VARCHAR(32) NULL)
go
INSERT INTO vendors (vendorcode, vendorname)
VALUES (100, 'Super Grocer')
go
INSERT INTO vendors (vendorcode, vendorname)
VALUES (101, 'General Department Store')
go
INSERT INTO vendors (vendorcode, vendorname)
VALUES (102, 'General Auto Parts')
go
INSERT INTO list (item, vendorcode, quantity)
VALUES ('Root Beer', 100, 3)
go
INSERT INTO list (item, vendorcode, quantity)
VALUES ('Ice Cream', 100, 1)
go
INSERT INTO list (item, vendorcode, quantity)
VALUES ('Napkins', 101, 50)
go
INSERT INTO list (item, vendorcode, quantity)
VALUES ('Spark Plugs', 102, 4)
go
CREATE CLUSTERED INDEX listtab ON list (vendorcode)
go
CREATE UNIQUE CLUSTERED INDEX vendortab ON vendors (vendorcode)
go
GRANT SELECT ON list TO nobody
go
GRANT INSERT ON list TO nobody
go
GRANT SELECT ON vendors TO nobody
go
SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode = vendors.vendorcode
ORDER BY item
go
If the following sequence of commands are entered, Sybase will respond with:
item vendorname quantity -------------------------------- -------------------------------- ----------- Ice Cream Super Grocer 1 Napkins General Department Store 50 Root Beer Super Grocer 3 Spark Plugs General Auto Parts 4 (4 rows affected)
One specific indexing feature should be discussed in the above example. The above commands created a clustered index on each table. Creating a clustered index in Sybase actually orders the data in the table in the method specified in the index creation statement. The important point about clustered indexes is that the space released by SQL DELETE statements is not reused unless a clustered index is defined. Only a single clustered index can be defined for a table (the data can only be organized one way). Clustered indexes can be removed from a table with a SQL DROP INDEX command. Normal indexes can be created by omitting the CLUSTERED keyword.
Sybase also has a utility called “bcp” which can be used to copy database data in and out of operating system files. However, “bcp” is beyond the scope of this book.
Also note that Sybase supports outer joins, as demonstrated by the following example:
INSERT INTO list (item, vendorcode, quantity)
VALUES ('African Violet', 103, 1)
go
SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode *= vendors.vendorcode
ORDER BY item
go
item vendorname quantity
-------------------------------- -------------------------------- -----------
African Violet NULL 1
Ice Cream Super Grocer 1
Napkins General Department Store 50
Root Beer Super Grocer 3
Spark Plugs General Auto Parts 4
Note the *= in the WHERE clause of the above select - it allows all rows of the first table to be included, regardless of the existence of a matching entry in the secondary table. The =* relation reverses the effect. The command below will add a vendor to obviate the need for the outer join:
INSERT INTO vendors (vendorcode, vendorname) VALUES (103, 'ACME Plant Store') go
Sybase databases can be updated with normal SQL syntax:
UPDATE list SET item = 'African Violets' WHERE vendorcode = 103;
Occasionally, the transaction logs for the shopping database should be cleared. This is done with the following:
dump tran shopping go
This command will dump the transaction log to the default backup device
(the sp_helpdevice command can be called to list the available
devices). The transaction log will be cleared when the dump is complete.
It is perfectly safe to run this command while the server is being accessed
by other users.
dump tran shopping with truncate_only go
This command will clear the transaction log without actually running a backup. Don't use this command if you have critical data integrity concerns.
dump database shopping go
This command will dump the entire database out to the default backup device. The dump file can only be read by Sybase servers of the same release running on the same hardware platform. Please note that the transaction log will not be cleared. Dump the transaction log separately.