Mckoi SQL Database
Home / Documentation / FAQ / Support / Download
Mckoi SQL Database - Frequently Asked Questions (FAQ)

General Questions

1.1. What is Mckoi SQL Database?
1.2. Is Mckoi SQL Database Free?
1.3. How do I pronounce 'Mckoi'?
1.4. Is there a mailing list?
1.5. How do I report a bug?

JavaTM Questions

2.1. What versions of JavaTM does Mckoi SQL Database require?
2.2. Where can I find a version of JavaTM that can run Mckoi SQL Database?
2.3. What version of JavaTM does the JDBCTM driver support?

SQL Questions

3.1. What is SQL? Where can I learn SQL?
3.2. What level of the SQL standard does Mckoi SQL Database support?
3.3. Does Mckoi SQL Database support transactions?

General Features Questions

4.1. How do I define an AUTO_INCREMENT column that generates unique indentifiers for a table?
4.2. How do I upgrade a database that I created with an older version?
4.3. How do I repair a corrupt database?

Database Configuration Questions

5.1. How do I change the configuration of my Mckoi SQL Database?
5.2. How much memory should I give to the internal cache?

JDBCTM API Questions

6.1. What is the JDBCTM API?
6.2. How do I use the JDBCTM Driver in my application?
6.3. How much of the JDBCTM API is supported?

Database Server Installation Questions

7.1. I have installed a JavaTM runtime, how do I start the database server?
7.2. How do I run the database server as a background process?
7.3. I'm running Unix. How do I run Mckoi SQL Database as a daemon that starts automatically when the machine boots?
7.4. I'm running Windows NT. How do I run Mckoi SQL Database as a service?
7.5. How do I shut down the database server?

Stand-alone Questions

8.1. How do I embed a stand-alone version of Mckoi SQL Database into my application?
8.2. Can a single database be accessed as both stand-alone and as a database server? Can the database data files be shared across different JavaTM VMs in stand-alone embedded mode?

Performance Questions

9.1. Why is the first query slower than the second time it is run?
9.2. The database takes up too much memory. How do I reduce the memory requirements?
9.3. Inserting and updating records is really slow. Can't it go any faster?

General Questions

1.1. What is Mckoi SQL Database?

Mckoi SQL Database is an SQL (Structured Query Language) Database management system written for the JavaTM platform. Mckoi SQL Database is optimized to run as a client/server database server for multiple clients, however it can also be embedded in an application as a stand-alone database. It is highly multi-threaded and features an extendable object-oriented engine.

Mckoi SQL Database started as an internal project and has since evolved from its inception in 1998. The main goals of the project are a code base that is simple to maintain and extend, ease of use and administration, robustness, multiple concurrent access, and performance.

1.2. Is Mckoi SQL Database Free?

Mckoi SQL Database is released under the GNU General Public License. You may freely use, modify and redistribute the software provided it is under the terms of the GPL license. The license is available at http://www.gnu.org/copyleft/gpl.html. If you wish to use the database under a different license then contact us for further details.

1.3. How do I pronounce 'Mckoi'?

Our name is pronounced as in McCoy ("The Real McCoy") or if you prefer the great Star Trek doctor 'Doctor McCoy'. We actually named the database after our Koi goldfish named Mckoi.

1.4. Is there a mailing list?

We have set up a mailing list for general comments, questions and suggestions. To subscribe to the mailing list send a blank message to mckoidb-subscribe@mckoi.com. A searchable archive of all posts to the list is being managed by a web application backed by a Mckoi SQL database server at http://mckoi.com/database/mail/list.jsp?list=mckoidb.

1.5. How do I report a bug?

Bug reports can either be sent to the mailing list or sent personally to the developer at toby@mckoi.com. If the report includes attachments that are larger than 50K in size then send it to the developer rather than the list.

Bug reports are a great way to contribute to the project. When submitting a bug report include as much information as you can on the circumstances that caused the error. It is often useful to include a cut and paste from the 'debug.log' file at the time the error occurred with your bug report. The fastest way to get a bug fixed is to send a simple Java application or an SQL script that reproduces the error.

JavaTM Questions

2.1. What versions of JavaTM does Mckoi SQL Database require?

Mckoi Database server requires the JavaTM 2 platform (runtime version 1.2 or above). It is currently being developed and tested with the Sun JDK version 1.3 Hotspot server running on Windows NT and Linux.

2.2. Where can I find a version of JavaTM that can run Mckoi SQL Database?

JavaTM runtimes are available from Sun, IBM plus other vendors. Sun provides Java runtimes for Win32, Solaris and Linux from their website at http://www.javasoft.com/j2se/. IBM also provides various Java runtimes for a number of platforms at http://www.ibm.com/java/jdk/download/.

2.3. What version of JavaTM does the JDBCTM driver support?

Mckoi implements version 3 of the JDBCTM specification. JDBC version 3 is a part of Java 2 (runtime version 1.4 and greater). Note that the Mckoi JDBC driver is backwardly compatible with all runtime versions of Java 2. We are considering releasing a cut down version of the Mckoi JDBCTM driver for use with JDBC version 1 which is a part of Java 1.1.

SQL Questions

3.1. What is SQL? Where can I learn SQL?

SQL stands for Structured Query Language. It has become the standard for manipulating and querying data in a relational database. SQL is widely taught in higher education CS courses. There are several books and online tutorials that teach SQL.

3.2. What level of the SQL standard does Mckoi SQL Database support?

Mckoi SQL Database does not support the full SQL standard. The entire SQL standard encompasses a huge number of features and goals, some of which do not mesh with the goals of our software. However, we endeavor to work towards SQL-92 entry level support and include all of the most useful features from the newer SQL standards.

Having said that, in the first release Mckoi SQL Database supports the SQL standard to a comparable level of other SQL database management systems available. See the manual for further details on the SQL syntax supported.

3.3. Does Mckoi SQL Database support transactions?

Mckoi SQL Database fully supports transactions. Mckoi SQL Database supports the stongest isolation level, TRANSACTION_SERIALIZABLE. See the Transactions section in the documentation for further details.

General Features Questions

4.1. How do I define an AUTO_INCREMENT column that generates unique indentifiers for a table?

Use the UNIQUEKEY(table_name) function. For example,

INSERT INTO MyTable ( my_unique_id, my_value )
    VALUES ( UNIQUEKEY('MyTable'), 'record value' )
With a clever use of the DEFAULT clause when creating a table you can make it automatic. eg.
CREATE TABLE MyTable
  ( my_unique_id NUMERIC DEFAULT UNIQUEKEY('MyTable'),
    my_value LONGVARCHAR(1000) )
The UNIQUEKEY function is an atomic operation that will return a set of sequential numbers for a table on a first serve basis.

4.2. How do I upgrade a database that I created with an older version?

See the README.txt file in the distribution for instructions on upgrading a database.

4.3. How do I repair a corrupt database?

A database may be repaired by using the conglomerate repair tool. The conglomerate repair tool is used with the following command (from the distribution directory);

java -cp mckoidb.jar com.mckoi.tools.DBConglomerateRepairTool -path [path to data files]

The repair tool will scan all tables and repair as much as it is able.

Database Configuration Questions

5.1. How do I change the configuration of my Mckoi SQL Database?

Mckoi SQL Database configuration is handled through a single configuration file. The configuration file contains information such as the directory of the data files on the local file system, the amount of memory used by the internal cache plus other tuning and debugging options.

There is a default database configuration file provided with the standard distribution. Go to the directory where you installed the database and edit the file named 'db.conf'. This file includes detailed comments about each database property.

5.2. How much memory should I give to the internal cache?

Providing more memory to the internal cache will improve the performance of most queries however there is no point allocating a large cache for a database that only stores a small amount of data. I recommend an internal cache size of 4 Megabytes for every 100 Megabytes stored in a database server. Note that the 4/100 cache rule does not apply in all cases and your cache requirements may vary depending on the nature of the data and your queries.

For an embedded database in an application where memory usage needs to be kept at a minimum I recommend a smaller cache size ( 1 Megabyte or smaller ).

JDBCTM API Questions

6.1. What is the JDBCTM API?

JDBCTM is the core JavaTM API that Java applications / applets / servlets use to communicate with a database. The JDBCTM API documentation can be found at http://java.sun.com/products/jdk/1.2/docs/api/java/sql/package-summary.html.

Mckoi SQL Database provides an implementation of the JDBCTM version 2 API. Demonstrations of using the Mckoi JDBCTM driver can be found in the tutorials section in the documentation.

6.2. How do I use the JDBCTM Driver in my application?

If you are connecting to a remote Mckoi database server you must include the 'mkjdbc.jar' jar archive in your application classpath. For example;

java -cp mkjdbc.jar [Your application start class]

Your Java application can then register the JDBCTM Driver and connect to the database. If you are embedding the complete Mckoi SQL database engine in to your application then you must include the 'mckoidb.jar' jar archive in your application classpath.

The following code demonstrates a Java application that uses the Mckoi JDBCTM driver to connect to a remote Mckoi database server.

java.sql.Connection connection;
try {
  // Register the Mckoi JDBC Driver,
  Class.forName("com.mckoi.JDBCDriver").newInstance();

  // The Database URL specifies the address of the Mckoi
  // database server.  This URL tells the driver to try
  // to make a connection to a database server running
  // on host myhost.mydomain.
  String url = "jdbc:mckoi://myhost.mydomain/";
    
  // Make the connection to the remote host,
  connection = java.sql.DriverManager.getConnection(
                                   url, username, password);
       
}
catch (Exception e) {
  System.err.println("JDBC Driver initialization failure.");
  e.printStackTrace(System.err);
  return;
}
// ... 'connection' is now used to talk with the Database 

For further examples, see the tutorials section in the documentation.

6.3. How much of the JDBCTM API is supported?

As of the first release, we support almost all of the features of the JDBCTM 2.0 API. Scrollable ResultSets are supported (insensitive only). Updateable ResultSets are not supported. If you feel the driver is lacking an important feature then feel free to email toby@mckoi.com or post a message on our mailing list.

Database Server Installation Questions

7.1. I have installed a JavaTM runtime, how do I start the database server?

First you must create a new database. Go to the command prompt, change to the directory where you installed the distribution and type;

java -jar mckoidb.jar -create [admin username] [admin password]

This will create a new database with the configuration options found in the 'db.conf' file in the current directory. The [admin username] and [admin password] part should be substituted with a name and password that the database admin will use to access the database. The admin user is given complete control of the database.

To start the server after the database has been created, type;

java -jar mckoidb.jar

Note that some JavaTM virtual machines only permit an application to allocate a maximum of 16 megabytes of memory by default. This may not be enough for a larger database. Refer to your JavaTM vendor documentation for details on how to increase this limit. To increase the memory available in the Sun JVM implementation, use the -Xmx switch. For example;

java -Xmx64m -jar mckoidb.jar

This will allow the application to consume a maximum of 64 megabytes of memory.

7.2. How do I run the database server as a background process?

This depends on the Operating System you are running. With the Sun JavaTM runtime on Win32 you can use;

javaw -Xmx64m -jar mckoidb.jar

On Unix, the standard way of running a process in the background is to append '&' to the end of the command.

7.3. I'm running Unix. How do I run Mckoi SQL Database as a daemon that starts automatically when the machine boots?

Many Unix systems require a database startup script to be added to the /etc/rc.d/init.d/ directory. Then symlink from one of the rc directories. Refer to your OS documentation for further details.

7.4. I'm running Windows NT. How do I run Mckoi SQL Database as a service?

There are several freely available tools on the internet that allow you to set up a Java application as an NT service. See the following links;

7.5. How do I shut down the database server?

Pressing CTRL-C or killing the JavaTM process while the database is running is a bad idea. If you do, the database may need to go through a recovery process the next time it is restarted. It is always best to cleanly shut down the database. Use the following command to shut down a database server running on the local machine;

java -jar mckoidb.jar -shutdown [admin username] [admin password]

Where [username] and [password] is the admin username and password. A database server may also be shut down remotely by sending a SHUTDOWN query when the admin user is connected.

Stand-alone Questions

8.1. How do I embed a stand-alone version of Mckoi SQL Database into my application?

You need to include the Mckoi SQL Database jar archive in your application classpath. For example;

java -cp mckoidb.jar [Your application start class]

NOTE: You do not need to include the JDBCTM Driver jar-ball (mkjdbc.jar) because the JDBCTM Driver is included in mckoidb.jar

The local database is accessed through the JDBCTM Driver URL. The following code demonstrates how this works;

java.sql.Connection connection;
try {
  // Register the Mckoi JDBC Driver,
  Class.forName("com.mckoi.JDBCDriver").newInstance();

  // The Database URL specifies that the database is to be
  // found in the local file system.
  String url = ":jdbc:mckoi:local://C:/mckoi/db.conf";
    
  // Make a connection to a local database,
  connection = java.sql.DriverManager.getConnection(
                                   url, username, password);
       
}
catch (Exception e) {
  System.err.println("JDBC Driver initialization failure.");
  e.printStackTrace(System.err);
  return;
}
// ... 'connection' is now used to talk with the Database 

Note that the example above assumes the database configuration file is located at 'C:\mckoi\db.conf'.

8.2. Can a single database be accessed as both stand-alone and as a database server? Can the database data files be shared across different JavaTM VMs in stand-alone embedded mode?

No for both answers. If you wish for multiple clients to share access to a database then you will need to run the engine as a server. If you only require that one application has exclusive access to the database, use the embedded local version.

Performance Questions

9.1. Why is the first query slower than the second time it is run?

There are multiple reasons for this. The first is the behaviour of modern JIT compilers. A modern JavaTM JIT compiler, such as Hotspot, compiles the most heavily used code to machine instructions for faster execution. This means the first time the database is accessed, it may be run through a slower JavaTM interpreter. Once the hotspots have been found and compiled, the next query will run faster.

Secondly, the internal database cache has no knowledge of the most heavily accessed data when the first query is executed. After the data for a query has been fetched from disk, the cache is filled with information that the next query may be able to utilize.

9.2. The database takes up too much memory. How do I reduce the memory requirements?

The default configuration has all performance optimizations turned on. Performance optimizations sacrifice memory for better performance, so to reduce memory requirements you will need to alter the performance properties in the database configuration file.

The most obvious property to change is the data cache size. A small database has little to gain from a large cache anyway so try reducing this first. See the documentation on the configuration properties for further options for optimizing the database.

Don't forget to shutdown and restart the database after the configuration file is changed.

9.3. Inserting and updating records is really slow. Can't it go any faster?

The insert and update operations are actually very fast, however during large batches of database changes most of the work is spent managing transaction and index changes. One way to improve insert and update performance is to turn auto-commit off (it is on by default) and committing the entire batch of insert/update operations in one go. Using this strategy there will be less overhead for index/transaction maintenance and all transaction data is flushed to disk in a single disk write.

The overhead of a transaction commit can also be lessened if the 'dont_synch_filesystem=enabled' configuration property is present in the configuration file. Enabling the 'dont_synch_filesystem' property will prevent the engine waiting for the result of a commit to be synchronized with the disk. The downside to enabling this property is if the operating system crashes after a commit there is a greater chance of losing the data that was committed in the transaction.


Last Updated: Mon Aug 16 00:27:17 PDT 2004
Mckoi SQL Database Copyright © 2000 - 2004 Diehl and Associates, Inc. All rights reserved.