Getting Started with SAND CDBMS: Basic Steps
4. Setting Up the Sample Database

The files required to set up the sample database are included on the SAND CDBMS CD-ROM in a directory called demo. Copy these files to the database directory before following the steps below; alternatively, include the full path when specifying the filenames to be used by the nisqlm and ndlm (data loader) utilities. The sample data files (with the .txt extension) and load specification script files (with the .ndl extension) must be located in the same directory, unless the script file is edited to indicate the location path of the data files.

To set up the tables for the sample database, first run the setup.sql file from the nisqlm SQL:n> prompt:

1. At the prompt, type .run followed by the file name (include the path if this file is not in the current directory), then press <Enter>:

session 1: test_alpha1@test
SQL:1> Run setup.sql

This executes a script file that contains the SQL commands to perform the following operations:

• create a user authorization ID called dbtest (with a new schema called dbtest as the user's default schema )
• create four named domains for the storage of data values within the dbtest schema
• create four tables called s, p, j, and spj within the schema
• COMMIT (make permanent) the above changes to the database
• exit from nisqlm and return to the operating system prompt.

Click to see the script.

Next, use the SAND CDBMS data-loading utility (ndlm) to bulk-insert data values from the source text files into the tables in the sample database. The sample flat files (.txt) must be located in the same directory as the load specification script files (.ndl). Note that only a user with INSERT and SELECT privileges on the target table can use the ndlm utility to execute a load operation.

2.  From the operating system command prompt, type ndlm, followed by the connection name and the database instance name, the user name DBA, and the name of the load specification script file that controls the data-loading operation (there is one load specification script file for each sample table: s.ndl, p.ndl, j.ndl, and spj.ndl). Include the file path if the load specification files are not in the current directory. Then press <Enter> to begin loading.

/usr/sand/testdb> ndlm test_alpha1 test DBA s.ndl

-or-

X:\TESTDB> ndlm test_alpha1 test DBA s.ndl

3. SAND CDBMS will show a preview of the data so that the format can be verified: press any key to move through the preview. When prompted to confirm the data format, press Y (for batch mode operation, these prompts can be suppressed by including the -b option flag in the ndlm invocation).

• SAND CDBMS shows the number of rows (data records) input to the database, and displays the time when it has finished inverting ("indexing") the columns in the table.
• The IMPORT DATA STATISTICS display shows the number of rows loaded from the file, and the number of rows loaded into the table; this information is also written to a text file called log.ndl (created in the current directory).
  
  

4. To populate the other tables with values, run ndlm as described above, using the p.ndl, j.ndl, and spj.ndl files. Click to view the load specification scripts.

Next, to access the information in the sample database tables, issue the SQL SELECT command using nisqlm.

5. From the command prompt, start nisqlm with the connection name, database instance name, and the new user name dbtest:

/usr/sand/testdb> nisqlm test_alpha1 test dbtest

-or-

X:\TESTDB> nisqlm test_alpha1 test dbtest

session1: test_alpha1@test
SQL:1>

You are automatically placed in dbtest’s default schema (also called dbtest) upon connecting: any unqualified references to database objects in subsequent SQL commands will be interpreted in the context of this schema. To reference tables, views or domains belonging to another schema, qualify the object name with the schema name in the following format:

schema-name.object-name

6. At the nisqlm SQL: prompt, type SELECT followed by an asterisk ( * ), meaning "return the contents of all columns", followed by the keyword FROM and the table name. All SQL statements executed with nisqlm must end with a semicolon. Press <Enter> to execute the command.

SQL:1> .output
OUTPUT FLAG IS ON
SQL:2> select * from s;

Note:
Issuing the Output command causes the data that appears on the screen to be a written to a file (in the database directory) called inter.out. To disable this feature, issue the command again. Note that the output file can grow quite large when logging query results. Be careful not to let the output file exhaust disk space.

For more information about SELECT and other SQL commands, consult the SAND CDBMS SQL Reference Guide.

7. SAND CDBMS displays all the data values in the table s. Repeat the command, specifying the appropriate table name, to see the contents of the tables p, j, and spj.


8. To quit nisqlm, issue the .exit command.

setup.sql

For an explanation of the Data Definition script, see:
Setting Up the Logical Structure of the Database Using DDL Scripts
 

CREATE AUTHORIZATION dbtest DEFAULT SCHEMA dbtest;
SET SCHEMA dbtest;

CREATE DOMAIN keyd   char(6);
CREATE DOMAIN named  char(20);
CREATE DOMAIN colord char(7);
CREATE DOMAIN cityd  char(15);

CREATE TABLE s
      ( sn      keyd,
        sname   named,
        status  smallint,
        city    cityd
      );

CREATE TABLE p
      ( pn      keyd,
        pname   named,
        color   colord,
        weight  smallint,
        city    cityd
      );

CREATE TABLE j
      ( jn      keyd,
        jname   named,
        city    cityd
      );

CREATE TABLE spj
      ( sn     keyd,
        pn     keyd,
        jn     keyd,
        qty    smallint
      );

COMMIT WORK;
Exit
 

---


s.ndl

For an explanation of the ndl script, see
Loading Data into Tables Using the SAND CDBMS Data Loader

 

import @s.txt
{
record
    {   fld1    6
        fld2    6
        fld3    2
        fld4    *\n
    }
dbtest.s
    {   sn      fld1
        sname   fld2
        status  fld3
        city    fld4
    }
}




---


p.ndl
 
import @p.txt
{
record
    {   fld1    6
        fld2    5
        fld3    6
        fld4    2
        fld5    *\n
    }
dbtest.p
    {   pn      fld1
        pname   fld2
        color   fld3
        weight  fld4
        city    fld5
    }
}




---


j.ndl
 
import @j.txt
{
record
    {   fld1   6
        fld2   8
        fld3   *\n
    }
dbtest.j
    {   jn      fld1
        jname   fld2
        city    fld3
    }
}




---


spj.ndl
 
import @spj.txt
{
record
    {   fld1    6
        fld2    6
        fld3    6
        fld4    *\n
    }
dbtest.spj
    {   sn     fld1
        pn     fld2
        jn     fld3
        qty    fld4
    }
}