This chapter explains how to run a cego database environment. This includes the setup of a database, the start and stop procedures, data export and import procedure and basic usage of the client programs cgclt, cgblow and cglog.

4.1   cego - Database backend

All database backend functionality is provided by the cego program. Cego provides several minor options to initialize the database, create database users and roles. Furthermode, cego provides two major run modes. The batch mode is used for an initial database load or database testing reasons. To serve client requests in a multithreading environment, cego should be started in daemon mode. All runtime options are described in more detail in the following.

4.1.1   Init mode

As a first step, the database should be initialized. Cego reads and writes all core information from a XML configuration file. This file initially can be created using the init mode. There are some mandatory parameters which should be specified with the command line. At least these are the database name and the configuration file name, the data should be written to.

$ cego --mode=init --mode=init --dbname=cgdb --pgsize=32786 --hostname=localhost --dbport=2200 --admport=2000 --logport=3000 --dbxml=cegodb.xml --qescmode=ON --csmode=ID 

Additionally, the database pagesize could be specified ( default pagesize 32786 ), the hostname the database should be connected to ( default localhost ) and values for db, admin and log ports. The quote escape mode can be set to ON or OFF to enable quote characters in string values ( escaped by '' ). The case sensitive mode is set to ID, so all identifier are handled case sensitive.

As a result, the configuration should look like

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

4.1.2   User setup mode

User information is stored with encrypted password information in the database XML configuration file. Database users are used to connect to the database db or admin port ( with the cgclt or cgadm program ) and perform database or administration tasks for any tableset. To access the database, first a user has to be created and initialized. To create it, the cego program is called using the adduser mode.

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

The role identifier describes the permission context of the user. Roles can be defined in a powerful way to control access on table level. For the database administration, the default role admin is used.

4.1.3   Role setup mode

Database roles are used to describe the access to the database. If created, a role can be assigned to any defined user or by creating a new user.

$ cego --mode=addrole --dbxml=cegodb.xml --role=myrole

4.1.4   Role permission setup mode

For a created role, a number of permission entries can be setup. A permission entry describes the following parameters

• Tableset
• Filter
• Permission

The tableset parameter identifies the corresponding tableset, the permission should be defined on. The filter is regular expression for the object names, which should be covered. The permission parameter specifies the kind of access, which should be allowed. It is either read, write, modify, exec or all. To identify the permission entry, a permission identifier has to specified To allow read access for all database objects starting with letter a, use the following permission entry

$ cego --mode=addperm --role=myrole --permid=P1 --tableset=TS1 --perm=read --filter="a.*" --dbxml=cegodb.xml

To allow any access to tableset TS1, use the following permission entry

$ cego --mode=addperm --role=myrole --permid=P2 --tableset=TS1 --perm=all --filter=all --dbxml=cegodb.xml

4.1.5   Tableset define mode

New tablesets can be defined using the tableset define mode. Since the command line options are pretty long, it is recommended to create a shell script around the cego definition command.
The script show below illustrates the complete definition of a tableset

$ cat defcgdb
TSROOT=./db
TSTICKET=./db/tsticket.xml
SYSSIZE=100
TMPSIZE=100
LOGFILESIZE=1000000
LOGFILENUM=3
APPSIZE=5000
SORTAREASIZE=5000000
cego --mode=define -tsdef=tsroot=${TSROOT},tsticket=${TSTICKET},syssize=${SYSSIZE},\
tmpsize=${TMPSIZE},logfilesize=${LOGFILESIZE},logfilenum=${LOGFILENUM},\
appsize=${APPSIZE},sortareasize=${SORTAREASIZE} --tableset=TS1 --dbxml=cegodb.xml

After the definition of the tableset has been done, the tableset is just known by the database but still can not be used. It first have to be created using the tableset creation mode.

4.1.6   Tableset creation mode

Any previous defined tableset just can be used, if all required datafiles have been created und initialized. For this, the creation mode can be used.

$ cego --mode=create --tableset=TS1 --dbxml=cegodb.xml

Please note : If the specified tableset already exists, any contained data is lost by the creation procedure

4.1.7   Tableset query cache setup mode

For a previous defined tableset, the querycache feature can be setup using the querycache mode.

$ cego --mode=querycache --tableset=TS1 --enable --maxsize=10000 --maxentry=10000 --hashrange=10 --threshold=1 --dbxml=cegodb.xml

4.1.8   Tableset import/export mode

For backup or database copy reasons, any tableset can be exported in a binary or XML formated file. The file can be saved and imported if required.
cego supports the following import and export modes

• xml import
• xml export
• binary import
• binary export
• plain import
• plain export

For machine independent exports, the XML export should be used. The data is stored in a readable form in a XML structure. Any contained blob data are encoded to ASCII. A faster way is provided by binary export and import. The data is stored to an export file without any ascii conversion. For larger tableset exports without any endian dependencies, this is the recommened way. If the tableset does not contain any blob or clob data, the plain export and import mode can be used. The whole table row is writtten in one piece to the export file, which results in a faster export and import procedure. Since lobs are stored in dedicated pages and are just referenced in a table row, plain exports will fail for lob tables.

$ cego --mode=xmlexport --tableset=TS1 --dbxml=cegodb.xml --expfile=exp.xml

Tableset TS1 is exported to the export file exp.xml. The data can be imported using the corresponding import mode

$ cego --mode=xmlimport --tableset=TS1 --dbxml=cegodb.xml --impfile=imp.xml --doLogging

The dologging option enabled redo logging and might be ommited, if a checkpoint is forced after the import has been completed.

For performance reasons, the binary or plain export can be used. Binary exports and imports are up to 10 times faster than XML exports and are recommended for high volume exports. Plain exports just can be used for tablesets without any lob data ( blob or clob ) but give another speedup since the row structure is kept.

$ cego --mode=binexport --tableset=TS1 --dbxml=cegodb.xml --expfile=exp.bin

The binary export can be imported with the following options

$ cego --mode=binimport --tableset=TS1 --dbxml=cegodb.xml --impfile=imp.bin --doLogging

For further speed up, the doLogging option might be ommited. In this case, no online redo logs are written by the database.

4.1.9   Tableset batch mode

For any batch processing reasons, the cego database can be started up in batch mode. The batch command sequence in the given sql file is processed subsequently and the program is terminated after the batch file has processed completely or any error has been occured.
If starting in batch mode, a number of command line arguments can be defined

The following sample performs a batch job with file batch.sql on tableset TS1 for user lemke.

$ cego --mode=batch --batchfile=batch.sql --dbxml=cegodb.xml --tableset=TS1 --user=lemke/lemke

4.1.10   Daemon mode

To serve client database requests, cego must be started in daemon mode. For this, a minimal valid XML database configuration file must be available. This may be

<?xml  version="1.0" ?>
<!DOCTYPE CEGO_DB_SPEC>
<DATABASE NAME="cegodb" PAGESIZE="16384" ADMINPORT="2000" LOGPORT="3000" DATAPORT="2200" PIDFILE="./pidfile">
</DATABASE>

Please note, that this minmal configuration file does not contain any tableset or admin user information. To be able to connect to the running damoen later on, you should create an admin user. See section Creating an user how to create an admin user.
If the configuration file is prepared, the daemon can be start up. For this, a number of command line arguments can be defined

The following sample starts cego in daemon mode. The database buffer pool is allocated with 1000 pages.

$ cego --mode=daemon --dbxml=cegodb.xml --poolsize=1000

If the daemons starts up, a child process is forked to run in background. To terminate the database daemon session, it is recommended to bring cego down softly. This can be achieved by sending a SIGINT signal to the daemon process with he following command

$ kill -s SIGINT <cego pid>

If the PIDFILE attribute is set up in the XML configuration file or via command line, the process id for the cego process can be retrieved from this file Assuming the pidfile was set to the file ./pidfile the database can be stopped with

$ kill -s SIGINT `cat ./pidfile`

To stay the server program in the foreground, the alternative server mode can be used. This might be useful for testing reasons, since the server can be terminated very easy by a Cntrl-C.

$ cego --mode=server --dbxml=cegodb.xml --poolsize=1000
Cego daemon up and running ...

4.1.11   General options

The following general options are available for all modes

4.2   cgclt - Console database client

If the cego server program was successfully started in daemon mode, a client database connection could be established using the client program cgclt. The client program is used to send any kind of SQL queries to the server ( via network ) and to receive the corresponding query results.

The program is invoked with the follwoing options

In the following sample, we use the host geek and port number 2200. We connect to the tableset TS1 with user lemke

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

If the prompt appears, the connection has been established. Now we can input any Cego SQL or database information retrievel command. See section The Cego SQL language for a detailed description of all available SQL commands.

Each command is completed with a semicolon ( ; ). So you are able to put in complex sql commands over several lines. Just if the semicolon is scanned, the command is sent to the cego server for further processing. To finish you client session, put in the quit command followed by a semicolon.

CGCLT > quit;
ok ( 0.000 s )

4.2.1   Batch file processing

Any cego database request can also be processed from a client via batchprocessing. For this, a corresponding batchfile is given as an argument to the client program.

$ cgclt --server=localhost --port=2200 --tablset=TS1 --user=lemke/lemke --batchfile=mybatch.sql

cgclt detects input from STDIN, so the batch file can also be send to a pipe

$ cat mybatch.sql | cgclt --server=localhost --port=2200 --tablset=TS1 --user=lemke/lemke

If any error occures during the batch processing, the client program terminates execution and returns an exitcode 1; To ignore any batch processing errors, you can give the ignore option to the command line

$ cgclt --server=localhost --port=2200 --tableset=TS1 --user=lemke/lemke --batchfile=mybatch.sql --ignore

4.2.2   SQL dump file creation

Database objects, which have been build can be dumped out to a SQL file. This might be useful for several purposes. For example a database SQL dump can be edited and reloaded to a different database. The cgclt supports the dump mode with the following command

$ cgclt --server=localhost --port=2200 --tablset=TS1 --user=lemke/lemke --dumpfile=mydump.sql

The --qescmode option must be specified, if single quotes in string values should be escaped following the double quote convention. Otherwise, a backslash is used as an escape character. In addition with the structure option --structure, the tableset is exported without any table content.

4.2.3   Single command processing

Sometimes it might be useful to execute just a single statement. For this, the command processing mode can be used.

$ cgclt --server=localhost --port=2200 --tablset=TS1 --user=lemke/lemke --cmd="select a from t1;"

The given command must be a complete SQL command including a closing semicolon .

4.2.4   Flow control

For large table data retrievel, the flow control can be enabled. If enabled, the row output is buffered and can be scrolled up and down. To enabled flow control, the command line option --flow has to be set.

$ cgclt --server=localhost --port=2200 --tablset=TS1 --user=lemke/lemke --flow

4.2.5   Query abortion

In case of any select, update or delete operation, it is possible to cancel a ( long ) running query request. For delete operations, some of the tuples might be lost, if there has not been started any embedding transaction. Since update operations are always embedded in a transaction ( user or forced ), a rollback is performed in any case.
Queries are canceled using the Cntrl-C key sequence.

CGCLT > update t1 set a = 10000;
^CAborting query ..
Error : Update aborted by user
Query failed
ok ( 2.705 s )
CGCLT > select count(*) from t1 where a = 10000;
+---------------------+
|                AGGR |
|            count(*) |
+---------------------+
|                   0 |
+---------------------+
ok ( 0.104 s )

4.2.6   Profile

Using profiles enables the user to use a predefined database connection record for building up database connections. The connections are defined in a profile called .cgprofile located int the users home directory. The profile can contains several profile entries of the following form

<pid>:<hostname>:<port>:<protocol><tableset>:<prompt>:<user>=<pwd>

Here is a sample .cgprofile

TS1:dude.local:2200:fastserial:TS1:TS1 > :lemke:lemke
TS2:cego.local:2200:fastserial:TS2:TS2 > :hugo:hugo

To use any of the profile entries, the cego client program has to be invoked with the profile command line parameter.

$ cgclt --profile=TS1

4.3   cgblow - Load simulation client

With the cgblow program, random insert, delete and update operations can be done on any user tables. I used this program for several kind of stability and performance tests. The program connects to the cego database as a normal cego database client program and performs its operations as given in the argument line.
The supported command line options for the program are described in the following

If server or port arguments are ommited, the query operations are printed to stddocout.
The insert format string is a comma separated list of insert column specs for int and string values. A column spec is of the form i:<int> for integer values or s:<int> for string values. The integer argument for integer values specified the range of the value while the integer argument for the string value specifies the length of the string.
We give two samples for using insert and delete mode.

$ cgblow --mode=insert --simulate --table=mytab --iset=i:10,s:10 --count=10 --append
set append on;
insert into mytab values (8,'EZQWNFAONB');
insert into mytab values (2,'HJLPKUIJAG');
insert into mytab values (0,'WATDXQRDRJ');
insert into mytab values (7,'NEVKXMOIZB');
insert into mytab values (8,'LYMPURYSYS');
insert into mytab values (9,'VNKWXUEOTU');
insert into mytab values (9,'FKNCNOOWGF');
insert into mytab values (4,'EXWRMMMVPL');
insert into mytab values (5,'FLZUIKCMDY');
insert into mytab values (0,'SPNQZQDQMI');

This inserts tuples to table mytab with an arbitrary integer column value between 0 and 10 and an arbitray string value of 10 byte.

$ cgblow --mode=delete --simulate --table=mytab --dcond=a:i:10 --count=10
delete from mytab where a=3;
delete from mytab where a=9;
delete from mytab where a=6;
delete from mytab where a=2;
delete from mytab where a=0;
delete from mytab where a=2;
delete from mytab where a=1;
delete from mytab where a=3;
delete from mytab where a=2;
delete from mytab where a=6;

With the above invocation, random delete operations for table mytab are generated, where the delete condition matches attribute value a as an integer value between 0 and 10. To show the arbitrary mode, we call cgblow with arb3 mode and all required random parameters.

$ cgblow --mode=arb3 --simulate --table=mytab --iset=i:10,s:2 --dcond=a:i:10 --pset=i:10,s:3 --uset=b:s:3 --ucond=a:i:10  --count=20
set append off;
insert into mytab values (2,'N');
update mytab set b = 'NH' where a = 8;
call (8,'K');
call (9,'UCE');
call (9,'WT');
insert into mytab values (1,'U');
insert into mytab values (5,'MY');
call (2,'WM');
call (1,'G');
insert into mytab values (5,'F');
update mytab set b = 'VS' where a = 6;
insert into mytab values (2,'UW');
delete from mytab where a=0;
update mytab set b = 'T' where a = 1;
delete from mytab where a=9;
update mytab set b = 'L' where a = 2;
delete from mytab where a=4;
insert into mytab values (3,'T');
insert into mytab values (5,'Z');
insert into mytab values (8,'NP');

4.4   cglog - Log reader

With the cglog program, anywritte online and offline redo log file can be read and analyzed. This might be useful for debugging reasons but also to trace the current transaction log for information about the current running transactions.

The following sample illustrates the usage of cglog

$ cglog --log=db/TS1redo0.log 
Read len : 25
LSN=1984:TS=02.05.2009 22:11:35:LogAction=SYNC:
--- No log data ---
Read len : 115
LSN=1985:TS=02.05.2009 22:11:35:ObjName=t1:ObjType=1:LogAction=CREATE:
--- Log Data Start ---
ObjectName: t1
ObjectType: table
TableSetId: 2
Table: t1
Schema: 
   TableName: 
   TableAlias: 
   AttrName: a
   Type: int
   Len: 4
   TableName: 
   TableAlias: 
   AttrName: b
   Type: string
   Len: 30
EntrySize = 63
FileId: 32
PageId: 0
LastFileId: 32
LastPageId: 0
EntrySize = 83
--- Log Data End   ---
Read len : 80
LSN=1986:TS=02.05.2009 22:11:35:ObjName=t1:ObjType=1:LogAction=INSERT:
--- Log Data Start ---
1|xxx|
--- Log Data End   ---
Read len : 80
LSN=1987:TS=02.05.2009 22:11:35:ObjName=t1:ObjType=1:LogAction=INSERT:
--- Log Data Start ---
1|ggg|
--- Log Data End   ---
Read len : 80
LSN=1988:TS=02.05.2009 22:11:35:ObjName=t1:ObjType=1:LogAction=INSERT:
--- Log Data Start ---
4|yyy|
--- Log Data End   ---
Read len : 80
LSN=1989:TS=02.05.2009 22:11:35:ObjName=t1:ObjType=1:LogAction=INSERT:
--- Log Data Start ---
3|zzz|
--- Log Data End   ---
Read len : 32
LSN=1990:TS=02.05.2009 22:11:35:ObjName=t1:ObjType=1:LogAction=DROP:
--- No log data ---

4.5   Localization

List all supported languages

$ locale -a
de_DE
de_DE.ISO8859-1
de_DE.ISO8859-15
de_DE.UTF-8
...

Set up the appropriate language

$ export LANG=en_US

Now cego or cgclt use the selected locale

4.6   Logging

The module base logging feature enables the database administrator to define a specific logging filter for individual logging requirements. The logging filter is defined in the database xml definition as module subtags of the database tag.

<DATABASE NAME="chkdb" PAGESIZE="16384" ....>
<MODULE NAME="ALL" LEVEL="NOTICE"></MODULE>
<MODULE NAME="CegoMain" LEVEL="NOTICE"></MODULE>
<MODULE NAME="CegoTableManager" LEVEL="DEBUG"></MODULE>
<MODULE NAME="CegoAdminThreadPool" LEVEL="DEBUG"></MODULE>
...
</DATABASE>

Four different logging levels are provided for each module

• NONE
• LOGERR
• NOTICE
• DEBUG

If the module name is ALL, the logging level is set up for all modules. You also may specify dedicated logging levels for the following registered modules

4.7   Compatibility attributes

To be compliant to other database products, cego provides a set of attributes, which can be set up in the database XML configuration file. The attributes are specified for the whole database in the DATABASE tag.

Sample

<DATABASE NAME="cegodb"...  CSMODE="ID" QESCMODE="ON"/>

4.8   Date format handling

Date handling is a tricky thing and is implemented for the common databases in several ways. Cego provides a format specification for printing date values but also for parsing date value strings. At default, the date and datetime formats are %d.%m.%Y and %d.%m.%Y %H:%M:%S. To change these values, corresponding entries have to be added to the database xml file.

To change the date printing format, the attribute DATETIMEFORMAT has to set up with a valid format string ( analog to the POSIX::strptime format string ).

For date scanning, a list of format can be specified using the XML DATETIMEFORMAT element entries. Setting up the VALUE attribute for each element specifies the format. Please note, that the pattern matching comes with the same order, as the entries occur. The format scanner cuts trailing characters, if a date string fits the given format. In this sense, it is useful to specify more complex formats at the beginning ( e.g. date time formats ), so these format are checked first.

Sample

<DATABASE NAME="cegodb"...  DATETIMEFORMAT="%a %b %d %H:%M:%S %Y"/>

<DATETIMEFORMAT VALUE="%d.%m.%Y %H:%M:%S"/>
<DATETIMEFORMAT VALUE="%d.%m.%Y"/>
<DATETIMEFORMAT VALUE="%Y%m%d"/>

4.9   Tuning attributes

For performance tuning reasons, several database configuration parameters can be set up.

4.10   Tableset init file

To perform post startup tasks, an init file can be set up for a tableset. The tableset init file is treated as a batch file and must contain standard cego SQL commands.

A good example to use init files are the adjustment of counter objects. Since counters values are NOT recovered for performance reasons, they must be adjusted manually or via the tableset init file.

You have to connect to the admin console to setup the initfile for a tableset.

CGADM > set initfile './counteradjust.sql' for TS1;
Init file set
ok ( 0.000 s )

The init file can contain the following line to reset counter mycount to the maximum value plus one to attribute value a in table t1

set counter mycount to ( select max(a) from t1 ) + 1;