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.

Installing PostgreSQL

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.

Establishing a PostgreSQL Database

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 into a file named .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.

Installing Sybase

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 1 then press enter three times to configure the client library.

Press 2 then press enter three times to configure the server library.

Press 3 then press enter three times to configure the embedded SQL for C system.

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 “Atwo 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:

  1. In /etc/profile, add the following lines after the first PATH statement:
    SYBASE=/home/sybase
    export SYBASE
    PATH="$PATH:$SYBASE/bin"

    This will allow all users to run Sybase server programs without specifying the full path.
  2. The Sybase servers will not be automatically started at system boot. To configure them to start in this manner, run the following commands as the super user:
    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
  3. The Sybase server startup scripts leave inactive shells in the process table. To remove these inactive processes for peak efficiency, add the command “exec” to the first line that does not begin with a pound sign (#) to all of the RUN files in /home/sybase/install (with the default server names, the files will be RUN_SYBASE and RUN_SYB_BACKUP).
  4. The interactive SQL interpreter that is shipped with Sybase (called 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.
  5. A bug in the older versions of the Linux kernel prevented network connections to the Sybase server from working properly. This bug was fixed in 2.0.36 prepatch 7, which is included in Red Hat Linux 6.0. However, if Sybase is installed on an older system (such as Red Hat Linux 5.1), network connections will not work properly unless the kernel is upgraded. The final 2.0.36 kernel was issued shortly before this book went to press, and the release of the Linux 2.2 kernel was imminent. If an older system is being used, it must have kernel 2.0.36 for proper Sybase network operation.
  6. The Sybase ASE package requires the GNU C library (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.

Sybase Devices

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.

Sybase Configuration

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.