User's Guide

Part III. Using SQL Anywhere

Chapter 17. Working with Database Objects

Some application design systems, such as Powersoft PowerBuilder, contain facilities for creating databases. These tools construct SQL statements that are submitted to the SQL Anywhere engine, typically through its ODBC interface. If you are using one of these tools, you do not need to construct SQL statements to create tables, assign permissions, and so on.

This chapter describes the SQL statements supported by SQL Anywhere. You can use these statements directly if you are building your database from an Interactive SQL tool, such as ISQL. Even if you are using an application design tool, you may want to use SQL statements to add features to the database if they are not supported by the system's database design tool.

Initializing a database

Initializing a database creates the root file for storing your database and the system tables, which hold the schema definition as you build your database.

Database files are compatible among all versions of SQL Anywhere. A database created from any operating system can be used from another operating system by copying the database file(s). A database created with a standalone database engine can be used with any SQL Anywhere network server as long as the server is not an earlier release.

You create a database using the database initialization utility. Database initialization is not controlled by SQL statements. Once the database is initialized, you can connect to it and build the objects in the database using SQL statements. If you are using Sybase Central, the SQL statements are constructed for you and you do not have to enter them yourself.

Accessing the initialization utility

A full description of the initialization utility, with the options available when you create a database is given in "The Initialization utility". The initialization utility can be accessed in the following ways:

• In Sybase Central, click the Database Utilities folder in the left panel, then double-click Create Database to start the Create Database Wizard, which leads you through the process.
• For Windows 3.x and OS/2, use the ISQL Database Tools window. To open this window, select the Database Tools menu item from the Window menu. Select Create Database from the Tools list, and enter the path and name of the file in which you wish to store the database in the Database File field. It is recommended that you give the database file the standard filename extension of DB. Press the Create button. This displays another dialog box for specifying options such as page size and collation sequence.
• Use the DBINIT command (DBINITW for Windows). For a full description of the DBINIT command, see "The Initialization utility".

  For example, the following command will create a database called COMPANY.DB:

       dbinit company.db
  

• Command line parameters allow different options for the database. For example, the following command creates a database with a 4K page size:

       dbinit -p 4096 company.db
  

• Use the ISQL DBTOOL statement. The database utility programs are available from within ISQL using the DBTOOL command. For example, the following command creates a database called COMPANY.DB:

       DBTOOL CREATE DATABASE 'company.db'
  

• The following command creates a database with 4K pages:

       DBTOOL CREATE DATABASE 'company.db' PAGE SIZE 4096
  

Adding database files

When a database is initialized, it is composed of one file. This first database file is called theroot file. All database objects and all data are placed in the root file. For many databases, it is convenient to keep the database as a single file. This section is intended only for users of large databases.

Each SQL Anywhere database file has a maximum size of 2 GB, so you may wish to divide large databases among more than one file. (On Windows NT, this limitation is removed, and files can be up to a terabyte). You create a new database file, or dbspace, using the CREATE DBSPACE statement. A new dbspace may be on the same disk drive as the root file or on another disk drive. You must have DBA authority to create new database files.

When created, a new dbspace has no contents. When you create a new table you can place it in the new dbspace with an IN clause in the CREATE TABLE statement. For information on creating tables, see "Creating tables". If no IN clause is used, the table is placed in the root file. Each table must be contained in a single dbspace, and SQL Anywhere has a maximum of twelve dbspaces per database. By default, indexes are placed in the same dbspace as their table, but they can be placed in a separate dbspace by supplying an IN clause.

Example

The following command creates a new dbspace called library in the file LIBRARY.DB in the same directory as the root file:

     CREATE DBSPACE library

     AS 'library.db'

To create a table and place it in the library dbspace, you can use the following command:

     CREATE TABLE Library_Books (
     title char(100),
     author char(50),
     isbn char(30)
     ) IN library

If you wish to split existing database objects among several dbspaces, you need to unload your database and modify the command file for rebuilding the database. To do so, add IN clauses to specify the dbspace for each table you do not wish to place in the root file.

Creating a dbspace in Sybase Central

To create a dbspace in Sybase Central:

1. Connect to the database.
2. Click the DB Spaces folder for that database.
3. Double-click Add DB Space in the right panel.
4. Enter the dbspace name and filename
5. Click OK to create the dbspace.

Preallocating space for database files

SQL Anywhere automatically takes new disk space for database files as needed. Unless you are working with a large database with a high rate of inserts and deletes, you do not need to worry about explicitly allocating spaced for database files. SQL Anywhere does allow preallocation of disk space for database files or for transaction logs for those cases where rapidly changing database files could lead to excessive file fragmentation on the disk, and possible performance problems.

You can preallocate disk space for a database file or for the transaction log using the ALTER DBSPACE statement. For more information on this statement, see "ALTER DBSPACE statement".

For example, the following statement adds 200 pages to the database file with dbspace name library. (The database page size is fixed when the database is created.)

     ALTER DBSPACE library
     ADD 200

Running a disk defragmentation utility after preallocating disk space helps ensure that the database file is not fragmented over many disjoint areas of the disk drive. Performance can suffer if there is excessive fragmentation of database files.

Preallocating disk space in Sybase Central

To preallocate disk space for a dbspace in Sybase Central:

1. Connect to the database.
2. Click the DB Spaces folder for that database.
3. Double-click the dbspace in the right panel.
4. Click Add Pages, and enter the number of database pages to preallocate.
5. Click OK.

Erasing a database

Erasing a database deletes all tables and data from disk, including the transaction log that records alterations to the database.

All SQL Anywhere databases are marked as read-only to prevent accidental modification or deletion of the database files.

You can erase database files using the Erase utility. For a full description of the Erase utility, see "The Erase utility".

Accessing the Erase utility

You can access the Erase utility using any of the following methods:

• Using Sybase Central . Click the Database Utilities folder, and double-click Erase Database to display the Erase Database Wizard, which leads you through the process.
• Using the ISQL Database Tools window . To open this window, select the Database Tools menu item from the Window menu. Select EraseDatabase or Write File from the Tools list, and enter the name of the database file in the Database File field. The database is erased when you press the Erase button.
• Using the DBERASE command-line utility (DBERASEW for Windows). The following command erases the database company.db and its transaction log:

       dberase company.db
  

  You will be asked to confirm that you really want to erase the files. To erase the files, typey and press enter.

• Using the DBTOOL statement . All the database utility programs are available from within ISQL using DBTOOL. For example, the following command erases the database company.db and its transaction log: DBTOOL DROP DATABASE company.db

The Erase utility can also be used to erase write files and log files. For a description of write files, see "The Write File utility".