3.4. Loading the Database Definition

Documentation

VoltDB Home » Documentation » Administrator's Guide
< previoustable of contentsnext >

3.4. Loading the Database Definition

Stored procedures are compiled into classes and then packaged into a JAR file, as described in the section on installing stored procedures in the Using VoltDB manual. To fully load the database definition you will need one or more JAR files of stored procedure classes and a text file containing the data definition language (DDL) statements that declare the database schema.

Responsibility for loading the database schema and stored procedures varies from company to company. In some cases, operators and administrators are only responsible for initiating the database; developers may load and modify the schema themselves. In other cases, the administrators are responsible for both starting the cluster and loading the correct database schema as well.

If the schema and stored procedures are predefined, you can include them when you initialize the database root directory and VoltDB will preload them when the database starts for the first time. Otherwise, you can load the schema and class files using the sqlcmd utlity after the database starts. The following sections describe each approach.

3.4.1. Preloading the Schema and Classes When You Initialize the Database

If the database schema is predefined, you can include it when you initialize the database root directory, using the --schema and --classes arguments to the voltdb init command. The --schema flag lets you specify one or more text files containing SQL DDL statements and the --classes flag lets you specify one or more JAR files containing the classes associated with any stored procedures you want to declare.

Note that DDL statements and Java classes can be order-dependent. For example, a stored procedure definition can depend on the existence of a table definition to define its partitioning column. VoltDB loads any classes before loading the schema file. However, you should be sure to specify the individual schema files or JAR files in the order you want them loaded.

Also, you must specify the same files, in the same order, when initializing all nodes of the cluster. For example:

$ voltdb init --dir=~/db \  
              --schema=tables.sql,streams.sql,procs.sql    \ 
              --classes=globalprocs.jar,myprocs.jar

3.4.2. Loading the Schema and Classes After the Database Starts

If you are responsible for defining the correct schema once the database is running, or modifying an existing schema, you can do this using the sqlcmd utility. The following example assumes the schema is contained in two files: storedprocs.jar and dbschema.sql. Once the database cluster has started, you can start the sqlcmd utility and load the files at the sqlcmd prompt using the sqlcmd load classes and file directives:

$ sqlcmd
1> load classes storedprocs.jar;
2> file dbschema.sql;

Note that when loading the schema, you should always load the stored procedures first, so the class files are available for any CREATE PROCEDURE statements within the schema.

< previoustable of contentsnext >