× News Cego SysMT Croom Web Statistics Impressum
Menu
SysMT Logo
Show German
2   Getting Started
Back to TOC

To get in touch with the Cego database system, we will create a sample database. Performing subsequent tasks to create the database, to start it up and connect to it using the standard client program cgclt will give a basic understanding of the system.

This assumes a successful compiliation and installation of the cego database programs. Please see the README file in the distribution for a description of the required steps.

2.1   Building up a tableset using cgmkdb

The easiest way to create a cego tableset is using the cgmkdb utility script in the tools directory. The script can be either started in basic or, if required, in advanced mode to setup further database and tableset parameters. As an example, we create a database called mydb with a database user cego and a tableset called TS1 using the cgmkdb utility. 

$ ./cgmkdb
===========================
Cego Database Configuration
===========================

Database Part
-------------
Enter database name [cgdb]: mydb
Enter root path [/usr/local/mydb]: 
Enter tableset name  [TS1]: 
Enter database user name [cego]: 
Enter database user password [cego]: 
Creating database mydb in directory /usr/local/mydb ...
 * Generating database configuration xml ... OK
 * Creating admin user for the database ... OK
 * Creating role ALL ... OK
   + Creating role permission ... OK
   + Creating user:cego ... OK
 * Creating database start script ... OK
 * Creating database stop script ... OK
Done.

Tableset Part
-------------
 * Creating a tableset ... 
   + Defining tableset:TS1 ... OK
   + Creating tableset:TS1 ... OK
Done.

The database mydb has been created with a default tableset TS1. In a cego database, a tableset is a complete transaction space including any database objects like tables, procedures, views and so on. In addition to the basic tableset, corresponding start and stop scripts located in the subdirectory /usr/local/mydb have been generated.

The database now can be started up using the generated start script mydb_start 

$ cd /usr/local/mydb
$ ./mydb_start
Starting cego with tablesets TS1 ...
Cego daemon up and running ...

To shutdown the database, the generated shutdown script mydb_stop can be used. 

$ ./mydb_stop
Stopping cego database-instance:mydb with tableset:TS1 ...
 * Signaling PID:17967 ... Done.
 * Waiting for lockfile to clear ...
   This takes a few seconds. Please be patient!
   Lockfile cleared in 19 seconds.
 * Housekeeping ... Done.
Database shutdown complete.

The next section will describe, how to build up a tableset manually step by step. The unpatient user can skip this section and read about how to use the tableset.

2.2   Building up a tableset manually

To build up a cego database tableset from scratch manually is also pretty easy. The following steps illustrate the way to do so.

Step I - Create an initial database configuration file

A cego database description is based on an appropriate XML configuration file, which contains all required information about the database. As a first step, we have to create this kind of XML document. An initial configuration file can be created using the init mode of the cego program.

For this, change to the directory where the database should be located. We assume this is /usr/local/mydb 

$ cd /usr/local/mydb
$ cego --mode=init --dbxml=mydb.xml --dbname=mydb

As a result, the following file should be produced 

<?xml  version="1.0" ?>
<!DOCTYPE CEGO_DB_SPEC>
<DATABASE NAME="mydb" PAGESIZE="16384" HOSTNAME="localhost" ADMINPORT="2000"
LOGPORT="3000" DATAPORT="2200" PIDFILE="./pid" CSMODE="ID" MAXTSID="0" QESCMODE="ON">
<MODULE NAME="ALL" LEVEL="NOTICE"></MODULE>
</DATABASE>

If required, the file still can be modified manually, Later on, if the database is set up, the configuration file should just be edited with advanced cego knowledge.

Step II - Create a database admin user

As next, a database admin user should be created. This user is used later on to connect to the database for further admin actions. To create the database admin user, you have to call the cego program in the following manner 

$ cego --mode=adduser --dbxml=mydb.xml --user=cgadm/cgadm --role=admin

The mode parameter adduser invokes cego in the appropriate way to setup a database user. With the parameter --dbxml, a valid configuration file is expected. We use the XML file created in the first step. Username and password are specified with the --user option. The password is stored in a encrypted form in the configuration file. The spefified user must not exist in the database configuration file.

If called successful, the configuration file should contain a admin user entry as listed below. 

<?xml version="1.0"?>
<!DOCTYPE CEGO_DB_CONFIG>
<DATABASE NAME="mydb" PAGESIZE="32786" HOSTNAME="localhost" ADMINPORT="2000"
LOGPORT="3000" DATAPORT="2200" PIDFILE="./pid" CSMODE="ID" MAXTSID="0" QESCMODE="ON">
<USER NAME="cgadm" PASSWD="3469c5c767f9cd3dbfa6f84296c992e1" TRACE="OFF" ROLE="admin">
</USER>
</DATABASE>

In this phase, the database configuration file could still be edited manually ( for example to delete a created user entry ) If the database is started up with the corresponding file, the file in under control by the database and should NOT be edited anymore.

The configuration file is prepared now to start up the database daemon.

Step III - Start the database daemon

To receive database admin and client requests, the cego database daemon has to be started up. In a simple way, this is done with 

$ cego --mode=daemon --dbxml=mydb.xml
cego daemon up and running ...

At default, the daemon program writes a log file cego.log to the directory, the daemon is started from ( you may change this using the --logfile parameter of the cego program ). You may trace the successful start procedure of the daemon by listing the logfile ( e.g. using tail -f ) If started successful, the database is ready to receive any connection requests.

Please note : For our first sample, the directory the daemon is started from should be readable and writable for the daemon. Logfile and database files are written to this location.

Step IV - Connecting to the daemon

Before any database client requests can be served, a valid tableset must be set up. To do this, we connect to the database using the cgadm admin program. 

$ cgadm --server=localhost --port=2000 --user=cgadm/cgadm
CGADM >

With the option --server and --port options, the host connection to the database server is specified. The admin host port must match with the specified value in the database configuration file. The specified user name must be a valid user account with administrator rights.

Step V - Define a tableset

Using the cgadm program, you can define a new tableset now. In a simple way, just type the following command. 

CGADM > define tableset TS1;
Tableset TS1 defined
ok ( 0.001 s )

The new defined tableset should be printed, if all registered tablesets of the database are listed. 

CGADM > list tableset;
+---------------------+-----------+-----------+------+------+------+------+
| TS                  | TS        | TS        | TS   |   TS |   TS |   TS |
| NAME                | RUNSTATE  | SYNCSTATE | ARCH |  SYS | TEMP |  APP |
+---------------------+-----------+-----------+------+------+------+------+
| TS1                 | DEFINED   | SYNCHED   | OFF  |   0% |   0% |   0% |
+---------------------+-----------+-----------+------+------+------+------+
Tableset list
ok ( 0.000 s )

In status DEFINED, the tableset is just registered with all required parameters for further tableset processing. Detailed information for the tableset can be retreieved via the show tableset command 

CGADM > show tableset TS1;
+---------------------+------------------------------------------+
|        TABLESETINFO | TABLESETINFO                             |
|           PARAMETER | VALUE                                    |
+---------------------+------------------------------------------+
|            Tableset | TS1                                      |
|            RunState | DEFINED                                  |
|           SyncState | SYNCHED                                  |
|             Primary | cora                                     |
|           Secondary | cora                                     |
|            Mediator | cora                                     |
|            RootPath | /usr/local/mydb                          |
|             LogUser | null                                     |
|              Ticket | /usr/local/mydb/TS1_ticket.xml           |
|            InitFile | null                                     |
|          Checkpoint | 3600                                     |
|     SystemPageTotal | 100                                      |
|      SystemPageUsed | 0                                        |
|       TempPageTotal | 3000                                     |
|        TempPageUsed | 0                                        |
|        AppPageTotal | 3000                                     |
|         AppPageUsed | 0                                        |
|        SortAreaSize | 10000000                                 |
|                 Tid | 0                                        |
|                CLSN | 0                                        |
|                WLSN | 0                                        |
|            ArchMode | OFF                                      |
|         AutoCorrect | ON                                       |
|          AppendMode | OFF                                      |
|             CP Dump | OFF                                      |
|          TableCache | OFF                                      |
|           TC Filter | null                                     |
|         TC MaxEntry | 0                                        |
|          TC MaxSize | 0                                        |
|        TC HashRange | 0                                        |
|          QueryCache | OFF                                      |
|         QC MaxEntry | 0                                        |
|     QC MaxEntrySize | 0                                        |
|        QC HashRange | 0                                        |
|             LogSize | 1000000                                  |
|             LogFile | /usr/local/mydb/TS1_redo0.log            |
|             LogFile | /usr/local/mydb/TS1_redo1.log            |
|             LogFile | /usr/local/mydb/TS1_redo2.log            |
+---------------------+------------------------------------------+
Tableset information
ok ( 0.020 s )

At default, a system file, a temp file and and application data file are defined for the tableset. These filenames can be listed with the list datafile command 

CGADM > list datafile for TS1;
+------------------------------------+--------------+--------------+--------------+
| DATAFILEINFO                       | DATAFILEINFO | DATAFILEINFO | DATAFILEINFO |
| FILENAME                           | FILETYPE     |         SIZE |         USED |
+------------------------------------+--------------+--------------+--------------+
| /usr/local/mydb/TS1.sys            | SYSFILE      |          100 |            0 |
| /usr/local/mydb/TS1.temp           | TEMPFILE     |         3000 |            0 |
| /usr/local/mydb/TS2_data.dat       | APP          |         3000 |            0 |
+------------------------------------+--------------+--------------+--------------+
Tableset information
ok ( 0.001 s )

Please note, that all tableset files reside in the tableset root directory /usr/local/mydb. If this is not appropriate, the tableset root directory can be changed to a different location. 

CGADM > set tsroot '/usr/local/mydb/data' for TS1;
TSRoot set
ok ( 0.000 s )

Now all tableset file will reside in the subdirectory called data. Please ensure, that the given directory path exists and is writable for the database daemon.

Step VI - Create the tableset

The new defined tableset can be created now. For this just type 

CGADM > create tableset TS1;
Tableset TS1 created
ok ( 0.092 s )

The run state of the tableset should be changed to state OFFLINE 

CGADM > list tableset;
+-----------+----------+-------------+
| TSL       | TSL      | TSL         |
| NAME      | RUNSTATE | SYNCSTATE   |
+-----------+----------+-------------+
| TS1       | OFFLINE  | SYNCHED     |
+-----------+----------+-------------+
Tableset list
ok ( 0.000 s )

You may retrieve a listing of all configured database files with the following admin command. 

CGADM > list datafile for TS1;
+------------------------------+--------------+--------------+--------------+
| DATAFILEINFO                 | DATAFILEINFO | DATAFILEINFO | DATAFILEINFO |
| FILENAME                     | FILETYPE     |         SIZE |         USED |
+------------------------------+--------------+--------------+--------------+
| /usr/local/mydb/TS1.sys      | SYSFILE      |          100 |            0 |
| /usr/local/mydb/TS1.temp     | TEMPFILE     |         3000 |            0 |
| /usr/local/mydb/TS1_data.dat | APP          |         3000 |            0 |
+------------------------------+--------------+--------------+--------------+
Tableset information
ok ( 0.000 s )

Please note : In tableset offline state, the used sizes of the datafiles are not indicated. This is done, if the tableset is online.

Step VII - Start the tableset

The created tableset now is set to online state by using the start command 

CGADM > start tableset TS1;
Tablset TS1 started
ok ( 0.003 s )

The resulting run state of the tableset should be changed to ONLINE 

CGADM > list tableset;
+-----------+----------+-------------+
| TSL       | TSL      | TSL         |
| NAME      | RUNSTATE | SYNCSTATE   |
+-----------+----------+-------------+
| TS1       | ONLINE   | SYNCHED     |
+-----------+----------+-------------+
Tableset list
ok ( 0.000 s )

Now the used size of the datafiles should be indicated correctly. 

CGADM > list datafile for TS1;
+------------------------------+--------------+--------------+--------------+
| DATAFILEINFO                 | DATAFILEINFO | DATAFILEINFO | DATAFILEINFO |
| FILENAME                     | FILETYPE     |         SIZE |         USED |
+------------------------------+--------------+--------------+--------------+
| /usr/local/mydb/TS1.sys      | SYSFILE      |          100 |           16 |
| /usr/local/mydb/TS1.temp     | TEMPFILE     |         3000 |           15 |
| /usr/local/mydb/TS1_data.dat | APP          |        10000 |            0 |
+------------------------------+--------------+--------------+--------------+
Tableset information
ok ( 0.000 s )

Step VIII - Create role and user

As a last administration step, we add an appropriate database role and user. First, we create the database role ALL 

CGADM > create role ALL;
Role ALL created
ok ( 0.000 s )

Now we assign a permission with all rights for all tables in tableset TS1. ( More advanced right settings are explained later on in a dedicated chapter ) This can be done with 

CGADM > set permission TS1_P with tableset=TS1, filter='ALL', right=ALL for ALL;
Permission TS1_P set
ok ( 0.000 s )

The definiton of the role ALL can be shown with the show role command. 

CGADM > show role ALL;
+----------------+---------------------+----------------+----------------+
|       ROLEINFO |            ROLEINFO |       ROLEINFO |       ROLEINFO |
|         PERMID |            TABLESET |         FILTER |           PERM |
+----------------+---------------------+----------------+----------------+
|          TS1_P |                 TS1 |            ALL |            ALL |
+----------------+---------------------+----------------+----------------+
ok ( 0.001 s )

Now we add a database user to the configuration 

CGADM > add user lemke identified by 'lemke';
User lemke added
ok ( 0.001 s )

To allow user lemke to perform any database operation, the previous created role ALL is assigned to the user 

CGADM > assign role ALL to lemke;
Role set for user lemke
ok ( 0.013 s )

Now the database is ready to use by user lemke. In the next section, we will see how the database can be used by this user.

2.3   Using the first tableset

The cgclt program can be used to perform database user requests. We invoke the client program with the following command 

$ cgclt --server=localhost --port=2200 --tableset=TS1 --user=lemke/lemke
CGCLT >

Now we can create our first table object using the following sql command 

CGCLT > create table mytab ( primary a int not null, b string(30) );
Table mytab created
ok ( 0.002 s )

The created table can be checked using the tableinfo command. Since we have specified column a as a primary key, a primary index has also been created for the table. 

CGCLT > tableinfo mytab;
+-------------------------------+----------------+----------------+
|                     TABLEINFO |      TABLEINFO |      TABLEINFO |
|                          NAME |           TYPE |          PAGES |
+-------------------------------+----------------+----------------+
|                         mytab |          table |              1 |
|                    mytab_pidx |  primary index |              1 |
+-------------------------------+----------------+----------------+
ok ( 0.002 s )

To put data into the table, you can use the SQL insert command 

CGCLT > insert into mytab values ( 42, 'this is mytext');
Query executed
ok ( 0.001 s )

Finally, the inserted data can be retrieved using a SQL select command 

CGCLT > select * from mytab;
+-----------+-------------------------------+
|           |                               |
|         a |                             b |
+-----------+-------------------------------+
|        42 |                this is mytext |
+-----------+-------------------------------+
ok ( 0.023 s )

If you have reached this step, your cego database system basically is running for you. Please see the subsequent chapters for a more detailed view about the features of the system.