CSQL Installation
<< Overview Of CSQL MMDB Cache System - TOC - CSQL Data Types >>
This chapter contains configuration information that user would be known before installation as well as complete information about building and installing CSQL. In addition; a complete structure on directory and all the important files are also mentioned.
Where to find CSQL
From the below two web sites, the product could be downloaded.
- http://www.sourceforge.net/projects/csql (open Source)
- http://www.csqldb.com (Enterprise)
OS Platform support
- Linux operating system on Intel x86 architecture
- Linux operating system on Intel x86_64 architecture
- Solaris operating system on Intel x86 architecture
- Solaris operating system on Sparc 32 architecture
- Solaris operating system on Sparc 64 architecture
Installation Prerequisites
- UnixODBC
- JDK 1.6
- readline library
Directory Layout and Files
CSQL directory layout is divided into the following sections.
| Directories and Files | Contains |
| include | Header files |
| bin | Client tools |
| lib | Shared libraries and jar file |
| docs | Manuals |
| sample | Sample configuration files |
| examples | Sample programs for ODBC, JDBC, SQLAPI and PHP |
| demo | Performance and caching demonstration |
| setupenv.ksh | Script to set up environment variables |
| ChangeLog | |
| LICENSE | |
| README |
Running examples
The examples directory has some subdirectories and each one has individual example file along with Makefile and README file. The user should watch the README file that will guide to compile and run these examples.
| Directory Name | File Name |
| sqlapi | sqlapiexample.c |
| isql | create.sql, drop.sql, insert.sql, select.sql, delete.sql |
| jdbc | jdbcexample.java |
| odbc | odbcexample.c |
| php | phpexample.php |
Note: Make sure the server is started in another terminal and setupenv.ksh is executed before running the examples.
How to Configure CSQL
The default database size for CSQL is 10 MB. If the size would be changed to more than 10 MB, then the system variable kernel.shmmax should be set to either same size or more than the size of the database. Only the superuser has the privilege to run this command. User can ask system administrator to set it in case the super user of the system is someone.
The following command sets the kernel parameter to 1 GB,
# /sbin/sysctl –w kernel.shmmax=1000000000 kernel.shmmax=1000000000
After setting up the kernel parameter, the user should set the CSQL environment variables using below command.
$cd <CSQL_ROOT> $. ./setupenv.ksh
This will set all the environmental variables defined in setupenv.ksh file present in <CSQL_ROOT> directory. In the below section, the environment variables have been discussed in details.
If user is evaluating or trying out CSQL for the first time, he may skip the next section as setupenv.ksh script sets the necessary environment variables.
Note: Run setupenv.ksh in each and every terminal or console before operation is started with CSQL.
Setting up environmental variables (setupenv.ksh)
CSQL has a set of environmental variables that need to be set before starting the server. To carry out the various database operations, either by using the CSQL tools or using executables which link with CSQL libraries, the following environmental variables need to be set. Let us assume CSQL_ROOT is the absolute path where CSQL is installed.
CSQL_INSTALL_ROOT
This should be set to the full path of where CSQL is installed. If CSQL is installed in /opt/csql, set this environment variable as follows
$ export CSQL_INSTALL_ROOT=/opt/csql
CSQL_CONFIG_FILE
There is a configuration file called csql.conf in the CSQL_ROOT directory, to whom the csqlserver reads during loading up. This file has the configuration variables for the CSQL system.
$ export CSQL_CONFIG_FILE=<CSQL_ROOT>/sample/csql.conf
LD_LIBRARY_PATH
This variable is set to locate the CSQL’s lib directory that contains CSQL specific libraries.
$ export LD_LIBRARY_PATH=<CSQL_ROOT>/lib:$LD_LIBRARY_PATH
PATH
This variable is set to locate the CSQL bin directory that contains the CSQL executables.
$ export PATH=<CSQL_ROOT>/bin:$PATH
CLASSPATH
This variable is set to locate the libraries for the JDBC driver of CSQL.
$ export CLASSPATH=<CSQL_ROOT>/lib/CSqlJdbcDriver.jar:.
Running the below given script from the CSQL root directory will set all the above variables automatically.
$. ./setupenv.ksh
Configuration File (csql.conf)
CSQL subsystem requires some system parameters that need to be set before starting CSQL database. Hence CSQL defines some of the system configuration variables that need to be defined. These configuration variables are defined in a file called csql.conf. Some of the parameters mentioned in this file may have to be tweaked based on the requirements. If the CSQL is evaluated or tried out for the first time, this section might be skipped, as the default csql.conf file present under sample directory of CSQL installation root directory is sufficient for demonstration purposes. All the lines except the lines followed by # are read from this file during server start up. These configuration variables are divided logically into following classes.
- Server section variables
- Client section variables
- Cache section variables
Server section variables
It is very important to note that for Server section parameters, the value should be the same for the server process and all the CSQL client processes, which connects to it. Otherwise, behavior is undefined.
PAGE_SIZE
Each database is logically divided into pages and allocation happens in this unit of pages. Increasing this value will reduce frequent allocation of pages. This value should be multiple of 1024 bytes. This value may be set to the OS page size which is usually 8192 bytes which is a default value is set.
MAX_PROCS
This is the number of processes that can connect and work with the database concurrently. This value can be set anywhere between 10 and 8192 depending on the number of users who may access the database. Default value 100 is set for this variable.
MAX_SYS_DB_SIZE
This is the maximum size of the system database where the metadata are stored. The value can be anywhere between 1 MB and 1GB. It should be multiples of PAGE_SIZE. Default size is 1048576 bytes.
MAX_DB_SIZE
This is the maximum size of the user database where user data are stored. This value can be set anywhere between 1MB and 2GB. It should be multiples of PAGE_SIZE. Default size is 10485760 bytes.
SYS_DB_KEY
Shared memory key to be used by the system to create and locate system database. The value can be anywhere between 10 and 8192. The default key value is 2222.
USER_DB_KEY
Shared memory key to be used by the system to create and locate user database. The value can be anywhere between 10 and 8192. This should not be the same as SYS_DB_KEY. The default key value is 3333.
LOG_FILE
It specifies full path for a directory, where CSQL log files are stored. Make sure that this directory exists before start the server. The default path is /tmp/csql/log/log.out for the log directory.
STDERR_FILE
Specifies full path for the standard error file to store all the errors during database operations. Default value is stderr (console).
MAP_ADDRESS
This is the virtual memory start address at which the shared memory segment will be created and attached. The default value for the variable is set to 4000000000.
DURABILITY
It is a flag, which specifies whether to enable durable transactions and recovery in case of server restart. By default, it is set to false.
DURABLE_MODE
Specifies the durability mode for redo log generation. Valid values are
1 ->uses standard redo logging with O_APPEND mode
2 ->uses standard logging with serialization (acquire lock before every write operation)
3 ->uses O_SYNC flag
4 ->uses O_DIRECT flag
Default value is 1.
MMAP
This is a Boolean value. This configuration variable should be set to true only when durability is required and the user database is in GB. This variable is intended for some specific applications where server start up is critical.
DATABASE_FILE
It specifies full path for a directory, where CSQL stores checkpoint and redo log files for providing durable transactions and recovery. Make sure that this directory exists before start the server. The default path is /tmp/csql/db.
CSQL_SQL_SERVER
CSQL supports network access from remote machine using client/server mode. This flag specifies whether to enable client/server mode access. By default it is set to false.
PORT
This specifies the port at which CSQL server will be listening for client connections. Default value is 5678.
MSG_KEY
CSQL uses message queues for communication internally. This specifies the key to be used for creating that message queue. It is an internal parameter. Users need not modify this value.
SHM_KEY
CSQL stores control information in separate shared memory segment internally. This specifies the key to be used for creating that shared memory segment. It is an internal parameter. Users need not modify this value.
Client section variables
MUTEX_TIMEOUT_SECS
Mutex timeout interval in seconds. When requesting for mutex, if it is acquired by anyone else, then the requester will wait for this specified time interval before it checks whether it is free to acquire.
MUTEX_TIMEOUT_USECS
Mutex timeout interval is in microseconds. The cumulative of the seconds and microseconds set will be used for the mutex timeout. The default value is 5000 set for this variable.
MUTEX_TIMEOUT_RETRIES
Number of retries before csql gives mutex timeout error. The default value for this variable is 10.
LOCK_TIMEOUT_SECS
Lock timeout interval in seconds. When requesting for lock, if it is acquired by anyone else, then the requester will wait for this interval before it checks whether it is free to acquire. Default value is zero.
LOCK_TIMEOUT_USECS
Lock timeout interval in microseconds. The cumulative of the seconds and microseconds set will be used for the lock timeout. Default value is 5000.
LOCK_TIMEOUT_RETRIES
Before CSQL gives a lock time error, how many times it retries. Default value is 10.
LOG_LEVEL
Specifies the logging level. Important actions based on the log level set are logged into file specified by LOG_FILE when this is enabled. Valid values are 0 for disable logging, 1 for Fine, 2 for Finer and 3 for Finest. Default value is 0.
Cache section variables
CACHE_TABLE
It is a flag, which specifies whether to enable caching of tables from the target database. Default value is false.
DSN
DSN name is used to connect to the target database for caching. This name should be present in the odbc.ini file with the respective odbc library of target database.
SITE_ID
It uniquely identifies a cache instance in multi node caching. The default value for this variable is 1.
TABLE_CONFIG_FILE
File name where the cached table information is stored. Specify the file name with full path. Default value is set to /tmp/csql/csqltable.conf
DS_CONFIG_FILE
This file has the information about the Data Source name from which the table will be cached. This file can’t be blank at the time of caching. Default value is set to /tmp/csql/csqlds.conf.
ENABLE_BIDIRECTIONAL_CACHE
It is a flag, which specifies whether to enable bi-directional caching for cached tables. Direct updates to target database will be brought into CSQL cache table automatically making cache coherent.
CACHE_RECEIVER_WAIT_SECS
It specifies the time interval in seconds, for CSQL server to wait if there is no update logs from the target database. This flag is used only in case of bi-directional caching.
CACHE_MODE
This specifies the mode for updatable caching from CSQL to target database. It can either be SYNC for synchronous updates or ASYNC for asynchronous updates. By default it is set to SYNC mode.
REPLICATION
It is a flag, which specifies whether to enable replication. This should be set to true for multi node coherent caching deployment.
REPLICATION_SITES
It specifies the maximum number of sites in the replication quorum. Default value is 16
NETWORK_CONFIG_FILE
It specifies the file name where the replication quorum information is specified. Specify the file name with full path. Default value is set to /tmp/csql/csqlnw.conf
Starting and Stopping csqlserver
Once CSQL is configured the server is ready to start. The CSQL Server can be invoked by running the following command:
$csqlserver Sys_DB [0001MB] User_DB [0010MB] Database Server Started…
If the output on screen looks similar to the above, then the server is ready for operations. To stop the server, just press <Ctrl + C> , where the server is running.
Received signal 2 Stopping the server Server Exiting.
The above output message is displayed during the exit. This will stop the server gracefully by doing the necessary clean ups.
Troubleshooting
When the user works with CSQL, he may find some problems. This section gives a description about problems and how to fix it.
Errors while running csqlserver
The following errors may come at the time of starting of the server because the environment variable might not be set up or the server is unable to create the log file.
csqlserver command not found
This below error is thrown when the $PATH environmental variable is not set for CSQL.
$ csqlserver -bash: csqlserver: command not found
Run the following command from CSQL_ROOT directory.
$. ./setupenv.ksh
Now run csqlserver and it should work.
Unable to create the log file
When the server is started the user may come across a message like “Unable to start the logger”. Below example is for the error scenario.
$ csqlserver 4822:3086075584:Logger.cxx:101:Unable to create log file. Check whether server started Unable to start the logger
This above error is thrown when csqlserver is not able to create the log file. Create the directory defined for LOG_FILE in csql.conf file, which is present in CSQL_ROOT directory. For example, LOG_FILE=/tmp/csql/logl/log.out
$ mkdir –p /tmp/csql/log
The log file can be created in different directory with change the value of LOG_FILE in csql.conf file.
Errors while running Client
The below error occur when the csqlserver is started by one user and would be accessed by another user.
$csql 22845:3085903568:Logger.cxx:101: Unable to create log file.Check whether server started
<< Overview Of CSQL MMDB Cache System - TOC - CSQL Data Types >>