You can create a database interactively in isql. Some other database administration tools can meet the API requirements enumerated in this section and let you create databases interactively, while others require a script.
In any case, it is preferable to use a data definition file (DDL script) because it provides an easy way to “replay” your statements if a statement fails. It is easier to start over from a source file than to retype interactive SQL statements.
Firebird creates a dialect 3 database by default. If you wish to create a dialect 1 database, the first statement in the script (or the prior action in your admin tool) should be SET SQL DIALECT 1;
The next statement—or the first, for a dialect 3 database—must be the CREATE DATABASE or CREATE SCHEMA statement, using the following syntax:
“DATABASE” or “SCHEMA”?
CREATE DATABASE and CREATE SCHEMA are the same statement. It is merely a question of preference which you use.
Mandatory and Optional Attributes
The only mandatory attribute for the CREATE statement is the file specification—the name of the primary database file and the filesystem path to its location.
Database Path and Name
The file specification must be a fully qualified, absolute path to the file. The path must be in a valid format for the operating system platform. For POSIX:CREATE DATABASE '/opt/databases/mydatabase.fdb'
For Win32:CREATE SCHEMA 'd:\databases\mydatabase.fdb'
You can use either forward slashes (/) or backslashes (\) as directory separators. Firebird automatically converts either type of slash to the appropriate type for the server operating system.
The enclosing single quotes for file_specification are not optional. All elements of the file specification are case sensitive on POSIX platforms.
Creating a Database Remotely
When creating a database from a client workstation, or locally on Linux Superserver, either interactively or using a script, you must include the host name: For POSIX:CREATE DATABASE 'myserver:/opt/databases/mydatabase.fdb'
For Linux SS Local, as previously, orCREATE DATABASE 'localhost:/opt/databases/mydatabase.fdb'
For Win32:CREATE SCHEMA 'NTServer:d:\databases\mydatabase.fdb'
If you are logged in as SYSDBA, then SYSDBA will own the new database unless you include the clause specifying the USER and PASSWORD. Although it is optional to designate an owner, it is highly desirable to do so. However, for security reasons, you will probably wish to remove the user’s password from the script before archiving it with other system documentation.CREATE DATABASE '/opt/databases/mydatabase.fdb' USER 'ADMINUSR' PASSWORD 'yyuryyub';
The optional PAGE_SIZE attribute is expressed in bytes. If you omit it, it will default to 4096 bytes with isql. Some other tools apply their own defaults, so there is a strong argument for specifying it explicitly in the script. The page size can be 1024, 2048, 4096, 8192, or 16384. Any other numbers will be resolved back to the next lowest number in this range. For example, if you specify 3072, Firebird will create a database with a page size of 2048.
Factors Influencing Choice of Page Size
Choosing a page size is not a question of applying some “rule.” It will do no harm to begin with the default size of 4K. When the time comes to tune the database for performance improvements, you can experiment by backing up the database and restoring it with different page sizes.
The page size you choose can benefit performance or affect it adversely, according to a number of factors having mostly to do with the structures and usage of the most frequently accessed tables. Each database page will be filled to about 80 percent of its capacity, so think in terms of an actual page size that is around 125 percent of the size you determine to be the minimum.
The row size of the most frequently accessed tables may have an effect. A record structure that is too large to fit on a single page requires more than one page fetch to read or write to it, so access can be optimized by choosing a page size that can comfortably accommodate one row or simple row multiples of these high-volume tables.
The number of rows that your main tables can be predicted to accommodate over time may have an influence. If multiple rows can be accommodated in a single page, a larger page size may reduce the overall tally of data and index pages that need to be read for an operation.
Default Character Set
This is strongly recommended unless all—or nearly all—of your text data will be in U.S. ASCII.
Getting Information About the Database
Once you have created and committed the database, you can display its details in isql using the SHOW DATABASE command:
Sweep Interval and Transactions
For information about sweeping and sweep interval, refer to the section “Database ‘Hygiene’” later in this chapter. The values of the oldest (“oldest interesting”), oldest active, and next transactions are important for performance and server behavior.
Forced writes is synonymous with synchronous writes. On platforms that support asynchronous writes, Firebird databases are created with forced writes enabled by default. The phrase “disabling forced writes” means switching the write behavior from synchronous to asynchronous.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
Introduction To Client/server Architecture
About Firebird Data Types
Date And Time Types
Blobs And Arrays
From Drawing Board To Database
Creating And Maintaining A Database
Firebird’s Sql Language
Expressions And Predicates
Querying Multiple Tables
Ordered And Aggregated Sets
Overview Of Firebird Transactions In
Programming With Transactions
Introduction To Firebird Programming
Developing Psql Modules
Error Handling And Events
Security In The Operating Environment
Configuration And Special Features
Interactive Sql Utility (isql)
Database Backup And Restore (gbak)
Housekeeping Tool (gfix)
Understanding The Lock Manager
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.