User's Guide
   PART 1. Working with Databases
     CHAPTER 3. Working with Database Objects       

Working with databases

Some application design systems, such as Powersoft PowerBuilder, contain facilities for creating database objects. These tools construct SQL statements that are submitted to the server, 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 for defining database objects. You can use these statements directly if you are building your database from an interactive SQL tool, such as Interactive SQL. 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 design tool.

For more advanced use, database design tools such as Powersoft PowerDesigner provide a more thorough and reliable approach to developing well-designed databases.

  Initializing a database

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

You create a database using the database initialization utility or the CREATE DATABASE statement. Once the database is initialized, you can connect to it and build the tables and other objects that you need in the database.

Database file compatibility 

An Adaptive Server Anywhere database is an operating system file. It can be copied to other locations just like any other file is copied.

Database files are compatible among all operating systems. A database created from any operating system can be used from another operating system by copying the database file(s). Similarly, a database created with a personal server can be used with a network server. Adaptive Server Anywhere servers can manage databases created with earlier versions, but old servers cannot manage new databases.

Using the CREATE DATABASE statement 

You can use the CREATE DATABASE statement to create databases. For example, the following statement creates a database file c:\temp\temp.db.

CREATE DATABASE 'c:\\temp\\temp.db'

The directory path is relative to the database server. The permissions required to execute this statement are set on the server command line, using the -gu command-line option. The default setting is to require DBA authority.

     For a full description, see CREATE DATABASE statement.

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.

• Use the dbinit command (dbinitw for Windows 3.x). For a full description of 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

  Creating Windows CE databases from Sybase Central

Sybase Central has features to make database creation easy for Windows CE databases. If you have Windows CE services installed on your Windows 95 or Windows NT desktop, you get an option to create a Windows CE database when you create a database from Sybase Central (Windows Edition). Sybase Central enforces the requirements for Windows CE databases, and optionally copies the resulting database file to your Windows CE machine.

  Erasing a database

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

All database files 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 DBERASE command-line utility     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, type y and press enter.

• Using the DROP DATABASE statement     For information on this statement, see DROP DATABASE statement.

• Using the Interactive SQL Database Tools window     To open this window, select the Database Tools menu item from the Window menu. Select Erase Database 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.

     For information on using multiple files for a database, and managing other optional files, such as write files, see Working with Database Files.