Installation guidelines IQ 16

This page describes how to install and configure a Sybase IQ server, version 16.0.

If you are new to Sybase IQ, it will also give some information about the product and terminology.

About Sybase IQ

About the product

Here are 2 videos that might be worth watching.

To get a basic feeling of the product you can watch this sales video from SAP	This video is more targeted towards DBA's who already have experience with Sybase IQ and it highlights the new features in version 16. (you might want to skip the first 5 minutes as this is mostly sales talk).

Some terminology

Sybase IQ stores it's data in various places, here is a very brief overview of them:

• Catalog store or SYSTEM dbspace, holds the meta data of the system, the table definitions, the stored procedures, etc. This is the .db file.
• Transactionlog, holds transactional information of activity in the catalog store. This is the .log file.
• Mirror logfile. An optional (but recommended) file that mirrors the transactionlog. There is no default file extension so use .mirror.

Then we have various dbspaces where the user data is stored, sorts are taking place, etc.

• IQ_SYSTEM_MAIN, holds various internal structures for checkpointing, backups, etc. These are the .iq files.
• IQ_SYSTEM_TEMP, holds temporary tables and is used for sorting data. These are the .iqtmp files.
• IQ_SHARED_TEMP, holds temporary data if you use Distributed Query Processing.
• User main, this dbspace holds your data. No default file extension here so use .iq.

Each dbspace (execept for SYSTEM) consists of various files.

Get the software

First download IQ.

For a licensed version you need to go http://sybase.subscribenet.com (account required) or to the Sybase eShop http://eshop.sybase.com/eshop/buy?id=48151

A trial version can be downloaded here: http://scn.sap.com/community/developer-center/analytic-server

Sample directory structure

In this document the following directory structure is used:

• /opt/sybase/iq - holds the Sybase IQ software
• /var/sybase/IQ1 – holds logfiles for the IQ instance and the for the catalog store
• /var/sybase/IQ1/iq_files – holds the dbspaces or symbolic links to raw devices.

Prepare the Operating System

Before installing Sybase IQ 16 for a production environment, make sure you have read and followed the recommendations in the hardware size guide http://scn.sap.com/docs/DOC-41455

Check that the filesystem for the Sybase software is big enough. A full installation needs approx. 1.5 Gb so 4 Gb should give you enough room to handle upgrades too. In this case we install the software in /opt/sybase/iq16. Database files will be stored on raw devices and in a subdirectory in /var/sybase.

Become root and add a group "sybase" and a user "sybase" to the system. Also create the needed directories.

mkdir -p /opt/sybase 
mkdir -p /var/sybase 
groupadd sybase 
useradd -g sybase -d /opt/sybase sybase 
chown sybase:sybase /opt/sybase 
chown sybase:sybase /var/sybase 

Check that your operating system has the required patches and configuration settings. Look in the installation documentation at http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc10083.1600/doc/html/title.html for detailed instructions. When you run the IQ installation, it will also check for some required packages.

Required packages

For RedHat / Centos, you need to install these packages:

yum install csh
yum install file
yum install libaio
yum install libXext
yum install libXrender
yum install libXtst
yum install libXi

Based on Ubuntu Linux (unsupported but seems to work), you need to install these packages:

apt-get install libaio1 
apt-get install csh 
apt-get install libxrender1
apt-get install libxtst6
apt-get install ia32-libs 

Prepare the Installation

Now, switch to the newly created user:

su - sybase 

You should now be in the directory /opt/sybase. Make a work directory and put the downloaded Sybase software in it.

mkdir work 
mkdir iq16 
cd work 
<put the software in this directory> 
tar -xf <software-distro> 

Installation

Go into the directory with the installed software. Run the installer (for instance ./setup.bin).

./setup.bin 

Answer the questions and install into /opt/sybase/iq16, select Typical or Full as Install Set. You can select what license type you need, Licensed or Evaluation. For the licenced version you need an account at http://sybase.subscribenet.com to generate the license keys. The evaluation license allows you to run IQ with all its features for 30 days. Accept the default port numbers for Sybase Control Center, and specify the password for the SCC administrator. Do not start Sybase Control Center.

Once the installation is complete you can remove the work directory and its content.

cd /opt/sybase 
rm -Rf work 

Errors when running setup.bin

java not found

When you get an error like this

exec: 2508: /tmp/install.dir.10616/Linux/resource/jre/bin/java: not found 

you need to install software to run 32 bit binaries on a 64 bit machine. Become root again and install the required packages.

On ubuntu:

apt-get install ia32-libs 

On Redhat

yum install glibc-devel.i386 

Fontconfig error

Ignore the error "Fontconfig error: Cannot load default config file" if you get it.

Setup your environment

In /opt/sybase/iq16 you will find files like IQ.sh and IQ.csh. Depending on the type of shell you use, you need to source in one of these. For instance, when your default shell is bash you should do this:

. /opt/sybase/iq16/IQ.sh 

Is is fairly common to activate such a script from your login script.

Create raw devices

Raw devices are recommended and you need them for the IQ Main Store, the Temporary Store and the User Stores. For testing purposes use 2Gb for the Main store and 1 Gb for the Temporary store. You can always add more when needed, but plan well ahead in case you want to run any serious production.

The number of files in a dbspace are important, as well as the size and expected size in the future. In this example we will use 2 files per dbspace but use 8 to 12 files in production environments, each placed on a separate LUN.

Do not forget to chown the raw devices to the sybase user. Check if this setting is persistent after a reboot.

Start the IQ server (utility database only)

Create a directory where server and database specific files will be stored

mkdir /var/sybase/IQ1
cd /var/sybase/IQ1
mkdir iq_files

Set the variable IQLOGDIR16 to a directory where the logfiles will be stored. When you do not set the variable, they will go to $IQDIR16/logfiles (/opt/sybase/iq16/IQ-16_0/logfiles).

export IQLOGDIR16=/var/sybase/IQ1

Start the IQ server with only the (virtual) utility database active. Use the start_iq command and specify the name of the server using the -n flag.

start_iq -n IQ1

Connect to the IQ server

Use dbisql to connect to the IQ server. You may need to set the DISPLAY variable.

dbisql

When you get the error “Could not initialize class java.awt.Toolkit” make sure that the required packages are installed. See above section.

Specify “DBA” for the User ID, “sql” for the password, choose “Connect to a running database on this computer” and “utility_db” as the database name. Click Connect.

Alternatively, you can use dbisqlc with a basic gui or iqisql but both of these tools are deprecated.

Create a database

The create database command has several option that can have a big impact on performance. See notes below or study the Sybase documentation to get the best result. Example:

create database 'IQ1.db' 
transaction log on 'IQ1.log' 
mirror 'IQ1.mirror' 
message path 'IQ1.iqmsg' 
iq path 'iq_files/IQ1_01.iq' 
iq size 2000
iq reserve 2000 
temporary path 'iq_files/IQ1_01.iqtmp' 
temporary size 1000
temporary reserve 1000
dba user 'DBA'
dba password '4yourEyes' 

When the database has been successfully created you can stop the IQ server

stop engine

and then exit from dbisql.

exit 

Notes for the "create database" command.

• The default page size for IQ files (.iq and .iqtmp files) is 128 Kb. When a single table in the database is expected to grow above 8 Tb or will have more than 4 billion rows choose the next bigger IQ page size of 256Kb.
• When creating a database or a dbspace you can ommit the fully specified path to the filename. When you use filenames relative to the IQ server it is more easy to move a database to another server.
• When using raw devices do not use the clause "iq size" and/or "temporary size". IQ will determine the size of the raw device and use it completely.
• When the database is created and you know that it will grow after some time, add the “iq reserve” clause to the “create database” command. This will create a contiguous free list that is already large enough for the future size of the database and so prevents a fragmented free list. Do the same when the user dbspace is created (see below). Then add the “reserve” clause.

Create a configuration file

In the directory /var/sybase/IQ1 create a configuration file called params.cfg with at least the following content

-n IQ1 
-iqmc 4000 
-iqtc 6000 

This configuration file sets the name of the server, the main cache size to 4000Mb, the temp cache size to 6000Mb. Specify as much memory as you can but leave a safe margin for other processes. Sybase recommends to keep the ration between main and temp cache 3:7 or 4:6 but it really depends on the type of activity on the IQ instance. Then start the database

start_iq @./params.cfg ./IQ1.db 

Create a user dbspace

Log on with dbisql and use the username/password combination as specified with the create database command. Then create a user space where all tables for the application should be stored. Example:

create dbspace user_main 
using file user_main_01 'iq_files/user_main_01.iq' 
size 5000 MB
reserve 5000 MB
iq store; 

As we do not want to store any user data in IQ_SYSTEM_MAIN transfer the privileges to create objects to user_main.

set option public.default_dbspace = 'user_main'; 
grant create on user_main to public; 
revoke create on iq_system_main from public; 

Extend the db spaces with a second file

For performance reasons you should use multiple files per dbspace. Here are examples to extend the 3 different dbspaces: IQ_SYSTEM_MAIN, IQ_SYSTEM_TEMP and user_main.

alter dbspace IQ_SYSTEM_MAIN 
add file main_02 'iq_files/IQ1_02.iq' 
size 2000 MB reserve 2000 MB; 

alter dbspace IQ_SYSTEM_TEMP 
add file temp_02 'iq_files/IQ1_02.iqtmp' 
size 1000 MB reserve 1000 MB; 

alter dbspace user_main 
add file user_main_02 'iq_files/user_main_02.iq' 
size 5000 MB
reserve 5000 MB;

Try to keep all the files in any dbspace to the same size to achieve a situation that all files are more or less filled at the same capacity.

By default, Sybase IQ stripes the date over the various files within a dbspace. For performance reasons it is best to have IQ handle the striped writes rather than a SAN storage system. Doing striped writes from both sides (SAN and IQ) may be counter productive.

You can run these stored procedures to view the dbspaces and their files

exec sp_iqfile;
exec sp_iqdbspace;

Getting started with users and tables

create user develop identified by 'GoodLuck';
grant create any object to develop;

Log off from dbisql and reconnect with the credentials of the new user. Then create the first table.

create table company(
company_id	int		not null,
company_name	varchar(30)	not null);