With DBMaker you can easily create and manage your database. Extensive cross-platform support and a unique open architecture allow you to deploy your database application across several platforms, and easily move to larger systems as your needs grow. You can easily scale from a small single-user database on a notebook computer all the way to a large multi-user database distributed around the world. In this chapter you will learn:

How to choose a valid name for your database.
How to partition your data.
How start the dmSQL command-line tool.
How to create a database.
How to configure a database.
How to start and terminate a database.
How to connect to and disconnect from a database.

4.1 Naming your Database

Using DBMaker, it is possible to have several different databases running on a single computer at the same time. In order to tell DBMaker which database you want to connect to, and so you know yourself which one you are connecting to, you need some way to identify one database from another. DBMaker does this using database names.

You should choose a database name that is unique among all computers that will connect to the database. Since DBMaker stores configuration information for all local and remote databases in the dmconfig.ini configuration file, using the same name for two different databases will cause a conflict. DBMaker won't be able to tell which of the configuration sections is for which database, and may write the configuration information in the wrong place. As a result of this, information found in the configuration file may not be valid for one or both of the databases. To see the names of the databases that already exist on your computer, you can check the section headings in the dmconfig.ini file.

You should carefully choose the name you want to use before executing the CREATE DB command, because you cannot change the name you give your database once you have created it. Database names can be one to eight characters in length, and can contain letters, numbers, and the underscore character. Some examples of valid names for a database are:

Tutorial
Parts_db
Region_1
1_Region

Database names are not case-sensitive. This means that you can enter the name as Tutorial when you create the database, and users can log on using different capitalization, such as tutorial and TUTORIAL. Tutorial is the name of the database that will be used throughout this book.

4.2 Understanding the Configuration File

DBMaker stores all configuration information for each database (including the database name) in a file called the dmconfig.ini configuration file. This file contains a database configuration section for each database that you can connect to from your computer. The dmconfig.ini configuration file is a regular ASCII text file, and can be edited with any text editor.

In most cases, DBMaker looks at the configuration information when a database starts. If you change this information after you start your database, it will not take effect until the next time you start the database. However, there are some configuration options that are only required when you connect to a database. You can change this information anytime before you connect to the database, and the new values will be used when the next connection is made.

The configuration parameters play an important role in the performance of DBMaker. You should be aware of the effects of each configuration parameter and estimate the best values to use to ensure DBMaker will run smoothly. You can refer to the Database Administrator's Reference for a full description of the configuration parameters and the keywords DBMaker uses to control them.

File Format

The dmconfig.ini configuration file is divided into sections called database configuration sections. Every database you can connect to has its own database configuration section, and the values in that section control the configuration operations for that database.

Each database configuration section is made up of a section header followed by one or more keyword lines. The section header is the name of the database enclosed in square brackets. The keyword lines consist of a keyword and a corresponding value (or values). The format of a typical dmconfig.ini configuration file is shown below:

[section_header_1]          
keyword1 = value1 ;text following a semicolon is a comment 
keyword2 = value2
   .
   .
 
[section_header_2]
keyword3 = value3 value4 ;spaces or commas may be used  
keyword4 = value5 ;as delimiters between values
      .
      .

Key words in the dmconfig.ini configuration file are not case-sensitive. Keyword values may or may not be case-sensitive, depending on the keyword and the operating system the database is running on. The dmconfig.ini configuration file is an ASCII text file, so you can edit it with any text editor capable of opening and saving ASCII text files.

Section Names

The name of each section corresponds to the name of the database that will use the configuration options found in that section when it starts up. The section name begins with a left square bracket ([) followed by the name of the database, and ends with a right square bracket (]). The brackets are required to enclose the section name, and the left bracket must be the first character on that line.

Keywords

Following each section name is a list of keywords and their values. These values will be used by the database that corresponds to the section heading for configuration when it starts. The statement keyword=value assigns the specified value to a keyword. If a keyword requires or supports multiple values, you can separate individual values with either spaces or commas. The value of a keyword can be an integer or a string, depending on the keyword itself.

If DBMaker cannot find a keyword in dmconfig.ini, it will use a default value for that keyword. Depending on their purpose, keywords may be used either at start time or connect time.For a complete list of keywords and their values, refer to the Database Administrator's Guide, Appendix B.

Comments

Any string or symbol that is written after the semi-colon (;) is considered a comment and ignored by DBMaker. You can use comments to remind you what a keyword is for, why you chose a specific value for a keyword, or what the original value for a keyword was if you change it temporarily.

File Location

On Windows systems, the dmconfig.ini configuration file is in the WINDOWS directory, or whichever directory you installed the Windows operating system into. If you are using Windows 95, Windows 98, or Windows NT 4.0, you can also open the dmconfig.ini file from the Start menu. Click the Start button, point to Programs, point to DBMaker 3.5, and then click DBMaker Configuration File (dmconfig.ini).

On Unix systems, the dmconfig.ini configuration file can be in one of three locations. When starting a database, DBMaker will scan these three locations in the order listed below to locate a dmconfig.ini file with a section name that corresponds to the database:

The current directory.
The directory specified in the environment variable DBMAKER.
The ~dbmaker/data directory.

If a dmconfig.ini file is found and the section name is also found, the keywords defined in that section will be used. If the section name can't be found in that file, DBMaker will continue searching for a dmconfig.ini file in the next directory until the section name is found.

4.3 Creating a Configuration File

You should not normally need to create a new dmconfig.ini file yourself, since the DBMaker installation program will automatically create one for you. When you create a database, DBMaker will examine the dmconfig.ini configuration and look for a configuration section name that corresponds to the name of the database you are creating. If it finds a matching configuration section, it will then check for any creation-time configuration options, and use the values it finds for those options when it creates your new database.

This means you should create the database configuration section with a text editor before a database is created if you have any creation-time options where you want to use a value other than the default value, so that those parameters will take effect when you create that database. If DBMaker can't find a configuration section in dmconfig.ini while creating a database, it will automatically create the section in the first dmconfig.ini file it finds, or in a new dmconfig.ini file if it cannot find any existing ones. When DBMaker creates a new configuration section for you, it uses the default values for all creation-time configuration options. In general, the default values for the creation-time configuration options should be fine for most of your databases.

4.4 Introducing dmSQL

dmSQL is a character-based, interactive interfaces that let you directly use the full power and functionality of the SQL query language found in DBMaker. You can use dmSQL to manipulate the database and perform ad-hoc SQL queries and see the result sets immediately. dmSQL is often the only method of exploiting the full power of the database without creating programs using a conventional programming language.

Starting dmSQL

Since most of the examples in this tutorial use dmSQL, you need to know how to start this application, and should familiarize yourself with the program before using it. The UNIX versions of DBMaker provide a single-user version (dmsqls) and a client/server version (dmsqlc) of the dmSQL application. It is important to note that you must use dmsqls to create a database in UNIX regardless of whether it is going to be a single-user or client/server database.

This is necessary because you can only configure a database to run in client/server mode after you create it. You will learn how to configure a new database for client/server operation later in this chapter. On Windows platforms, the functionality of both dmsqls and dmsqlc have been combined into a single program, dmsql32.exe. If you are using Windows, you don't have to worry about which version of dmSQL to use, since one program does it all.

_To start the dmSQL command-line utility in Windows 95/98/NT:

Click the Start button, point to Programs, point to DBMaker 3.5, and then click dmSQL. This starts the dmSQL application.

_ To start the dmSQL command-line utility in UNIX:

At the command line, type the following command and press the Enter key. This changes the current directory to the ~dbmaker/3.5/bin directory.

cd ~dbmaker/3.5/bin

At the command line, type the following command and press the Enter key. This starts the dmSQL application.

dmsqls

The dmSQL Workspace

After starting dmSQL, you should see the dmSQL workspace on Windows systems, or the dmSQL> command-line prompt on UNIX systems. The graphic below shows the Windows version of dmSQL.

Figure 4-1: The dmSQL Window

The dmSQL window contains the following areas:

Title Bar-The title bar displays the program name ("dmSQL") and the Minimize, Maximize, and Close buttons.
Menu Bar-The menu bar displays dmSQL's pull-down menu titles. Each menu contains a list of related commands.
Toolbar-The toolbar is a palette of command buttons and drop-down list boxes for many commonly used functions.
Command Entry Area-The command entry area is the main window in the dmSQL workspace, where you enter commands, and where dmSQL runs scripts and displays text.
Status Bar-The status bar describes the current activity in the workspace, and displays the current time.

4.5 Creating a Database

Creating the database is probably the simplest part of designing and implementing a database in DBMaker. To create the Tutorial database, simply type the CREATE DATABASE command with a name for your database.

_To create the Tutorial database using dmSQL:

Type the following at the dmSQL command prompt and press the Enter key.

CREATE DATABASE Tutorial;

This command creates an empty database named Tutorial. Before it creates the database, however, it looks in the dmconfig.ini file to see if a section with the same name as the database you are trying to create already exists. If it does already exist, DBMaker will use the keyword values for any creation-time configuration options from that section to create the database.

In this case, you didn't create a database configuration section before creating the database, so DBMaker will create one for you and use the default values for all creation-time configuration options.

Connection Handles

After executing the CREATE DATABASE command, you will see the following line on your screen.

USE db #1 connected to db:<Tutorial> by user:<SYSADM>

This indicates that the database you created named Tutorial is connected to USE#1, and that you are connected with the user name SYSADM. You can have as many as eight database connections at the same time using dmSQL, so you need some way to identify which database connection you are using. DBMaker uses the term USE to indicate which database connection is currently active.

This USE is also known as a connection handle. Since you can have up to eight database connections, there are also eight connection handles, from USE#1 up to USE#8. The first database connection you make is on USE#1, the second on USE#2, all the way up to USE#8. To view all currently connected databases in dmSQL, you can enter the USE command at the dmSQL command line.

_To view all currently connected databases using dmSQL:

Type the following at the dmSQL command prompt and press the Enter key.

USE;

After you execute this command, dmSQL will show you a list of all the databases you are connected to. Since you just created the Tutorial database, you should see something similar to the following example:

dmSQL> USE; 
USE db #1 connected to db:<TUTORIAL> by user:<SYSADM>(CURRENT)

In this case, there is only one database, so there is only one connection handle (USE#1). This almost the same as what you see when you create a new database, except the database you are currently connected to displays (CURRENT) after the connection information. This indicates that the database you created named Tutorial is connected to USE#1, and that you are connected with the user name SYSADM.

The following example shows what you might see if you were connected to more than one database:

dmSQL> use; 
USE db #1 connected to db:<TUTORIAL> by user:<SYSADM>(CURRENT) 
USE db #2 connected to db:<DBSAMPLE> by user:<SYSADM> 
USE db #3 connected to db:<EXDM35> by user:<SYSADM>

In this example, you can see that the database you are currently connected to is the Tutorial database, which is indicated by the text (CURRENT) after the connection information.

To connect to a database on a connection handle other than USE#1, you must switch to the USE you want to connect on before making the connection. To switch to another connection handle in dmSQL, you can enter the USE command followed by the number of the connection handle you want to switch to at the dmSQL command line.

_ To switch to connection handle number two using dmSQL:

Type the following at the dmSQL command prompt and press the Enter key.

USE 2;

Default User

When you create a database, you are automatically connected with the SYSADM user name. The SYSADM user is the most powerful user in a database. The SYSADM can create new user accounts for other people, and has all rights and privileges on all database objects. However, since you just created your database, there aren't any database objects in your database right now.

Even after creating your database, it is still not possible for other users to connect to the database. When you create a new database it automatically starts in single-user mode, which only allows one user connection at a time. This gives you the opportunity to change the SYSADM password from the default value (no password), to something that is known only to you. If you don't change the SYSADM password, anyone can use the SYSADM account to connect to your database and gain full control over it. Changing the SYSADM password will be covered in a later section.

To allow other users to connect, you must run the database in one of the multiple-user modes. This can be single-user mode (on UNIX), multiple-connection mode (on Windows), or client/server mode (on both Windows and UNIX). To run your database in multiple-connection mode, you must terminate the database and then restart it. To run it in client/server mode, you must terminate the database and add some additional keywords in dmconfig.ini.

_ To terminate the Tutorial database using dmSQL:

Type the following at the dmSQL command prompt and press the Enter key.

TERMINATE DATABASE;

4.6 Understanding Database Modes

DBMaker allows you to start your database in one of several different database modes. Each mode provides different options for connecting to and accessing your database, giving you the ability to scale your database from a simple single-user system on one computer to a large multi-user system distributed across several computers.

The database modes available to you depend on the platform your database server runs on, and where you want to connect from. DBMaker has three different database modes: single-user, multiple-connection, and client/server.

Single-User Mode

Single-user mode is only available on the UNIX/Linux platforms. This is a simplified version of DBMaker for non-sharable databases. The main advantages of this mode are the smaller application size and faster execution speed for most database operations, since locks, security, and network support are not required for a single user database. A limitation of this mode is that since only one connection can be made to the database at a time, the database cannot run any of the extra servers or daemons, such as backup server, replication server, or global transaction server. Another limitation is that you must access the database from the host machine, since the database is not available over the network.

Multiple-Connection Mode

Multiple-connection mode is only available on the Windows platform. One advantage of this mode is that you can have multiple connections to a database, with the full range of security and reliability features of DBMaker. Similar to single-user mode, all connections must access the database from the host machine, since there is no network support. A limitation of this mode is that the database does not support any of the extra servers or daemons, such as backup server, replication server, or global transaction server.

Client/Server Mode

Client/server mode is available on all platforms. This mode permits multiple connections to a database from any computer connected to the host computer via a TCP/IP network, and provides the full range of security, reliability, and concurrency control features of DBMaker. In addition, data sent across the network can be encrypted for additional security. This mode supports all of the extra servers and daemons, such as backup server, replication server, and global transaction server.

4.7 Configuring the Database Mode

Before you start using your database, you should decide which database mode you are going to use. Although DBMaker has three different database modes, your choices are somewhat limited by the operating system you are using.

On Windows, you can choose between multiple-connection mode and client/server mode. Multiple-connection mode is the best choice if you are going to use your database on a single computer, since it has most of the benefits of client/server mode, but doesn't require a TCP/IP network connection and doesn't require any special configuration.

Client/server mode is your only choice if you are going to run your database on one computer and access it from another, since this is the only mode for Windows that includes network support.

On UNIX, you can choose between single-user mode and client/server mode. Single-user mode is useful if you are going to use your database on a single computer, and if you don't require more than one connection to the database. As on Windows, client/server mode is your only choice if you are going to run your database server on one computer and access it from another, and also if you need more than one connection, since this is the only mode for UNIX that includes network support and also the only mode that allows multiple connections.

Single-User

There is no special configuration required for using single-user mode on UNIX, other than remembering that there is no security, and that you can only have one connection to the database. This means that anyone can access your database without using a password, and that you must connect to your database from the same computer where it is physically located.

Multiple-Connection

There is no special configuration required for using multiple-connection mode on Windows, other than remembering that there is no network support. This means that you have all the benefits of client/server mode, except that you must connect to your database from the same computer where it is physically located.

Client/Server

Client/server mode requires some configuration to work properly. To run a database in client/server mode, you must be connected to a TCP/IP network, and have TCP/IP network protocol support installed on all computers that will be used to connect to the database. Most operating systems today, including Windows 95/98/NT and UNIX, provide native support for the TCP/IP protocol. To check if you have TCP/IP support installed on your computer, you should consult your operating system documentation.

Changes to dmconfig.ini
In addition, you need to add the additional lines shown below to the [Tutorial] section of dmconfig.ini. Note that the additional lines are shown in bold.

[TUTORIAL] 
DB_DBDIR=C:\DBMAKER\TUTORIAL\DATABASE    
DB_USRID=SYSADM 
DB_SVADR=127.0.0.1 
DB_PTNUM=54321

The DB_SVADR keyword is required on both the client and server sides when running a database in client/server mode. This keyword specifies the IP address of the computer that will be acting as the server for the database. You should replace the number shown above with the IP address of your computer.

The DB_PTNUM keyword is required on both the client and server sides when running a database in client/server mode. This keyword specifies the port number the server will listen for connection requests on.

After making the changes to dmconfig.ini, you are ready to restart your database in client-server mode. Starting a database in client/server mode is a little different than in single-user mode. You must start the database with the DBMaker server, and then connect with a client application, such as dmSQL.

Starting a Client/Server Database
You must use the DBMaker server to start a client/server database. The DBMaker server starts the database, and then waits for database clients (such as dmSQL) to connect. After a client connects, it accepts commands from the client and returns the results.

When you run the DBMaker Server, you provide the name of the client/server database you want to start, your user name, and your password. To start a database, you must have DBA or SYSADM security privileges.

_ To start a client/server database in Windows:

1. Click the Start button, point to Programs, point to DBMaker 3.5, and then click DBMaker Server. This starts the DBMaker Server application and displays the Start Database dialog.
2. In the Database Name box, select TUTORIAL from the list of available databases.
3. In the User Name box, type SYSADM if it does not already appear.

_ To start dmServer in UNIX:

At the command line, type the following command and press the Enter key. This changes the current directory to the ~dbmaker/3.5/bin directory.

cd ~dbmaker/3.5/bin

At the command line, type the following command and press the Enter key. This starts the DBMaker Server and runs the Tutorial database.

dmserver -u SYSADM TUTORIAL

You should see the following message after the database starts.

DBMaker 3.5 

Copyright 1995-1999 CASEMaker Inc. All 
rights reserved. 

SQL Server bound to port 54321 

The database has started successfully. 
    

Database Server is running in the background 
mode. 

Process ID = 28030

Now that you have started the database in client/server mode, you can use dmSQL to connect to it the same way as you would in single-user or multiple-connection mode.

4.8 Connecting To a Database

You use the CONNECT command to connect to a database. This command works for single-user, multiple-connection, and client/server databases, but you must remember to start the database first when using a client /server database.

The CONNECT command has three parameters: the database name, the user name, and the user password. Since you have not yet created any additional users in the database, and have not changed the default SYSADM password, use the SYSADM user name with no password for now.

_ To connect to the TUTORIAL database:

Type the following command at the dmSQL command prompt and press the Enter key.

    CONNECT TO TUTORIAL SYSADM;

If you try connecting to a client/server database and get a message like `cannot connect to server', you should check to make sure you have started the DBMaker Server and that the database you are trying to connect to is running.

4.9 Disconnecting From a database

You use the DISCONNECT command to disconnect from a database. This command works for single-user, multiple-connection, and client/server databases.

The DISCONNECT command does not have any parameters, it simply disconnects from the database on the active connection handle.

_ To disconnect from the TUTORIAL database:

Type the following command at the dmSQL command prompt and press the Enter key.

    DISCONNECT;

If you disconnect from a client server database, the your database continues to run, and the DBMaker Server continues to wait for clients to connect. To stop a client/server database, you must terminate it using the TERMINATE DB command.

_ To terminate the TUTORIAL database when running in client/server mode:

Type the following command at the dmSQL command prompt and press the Enter key.

    TERMINATE DB;