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.
Contents
- 1 About Sybase IQ
- 2 Get the software
- 3 Sample directory structure
- 4 Prepare the Operating System
- 5 Required packages
- 6 Prepare the Installation
- 7 Installation
- 8 Setup your environment
- 9 Create raw devices
- 10 Start the IQ server (utility database only)
- 11 Connect to the IQ server
- 12 Create a database
- 13 Create a configuration file
- 14 Create a user dbspace
- 15 Extend the db spaces with a second file
- 16 Getting started with users and tables
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);
