I plan to evaluate a DBMS based on columns (not rows like Oracle, SQL Server…), called Sybase IQ these days, in order to see if we can avoid some aggregation in DSS environments. It’s a nice little niche product, quite handy, but due to its niche status, you don’t find a lot about it in the www. I want to try to change this a little, little bit today.
Installing Sybase IQ 15.1 is not that complicated. By following the installation guide from sybase.com, it took me about an hour to figure out what’s to do, and another to get the box up and running. Creating and starting a demo database is described in the same paper, and worked flawlessly.
But now my problem did rise: My target is to do mass data tests within the DB, so I need mass data storage access, preferably on a raw device, bypassing the file system caches of my Linux/ext3 system. Since using persistent device names and directly accessed (“raw”) devices from /dev/disk/by-id works greatly with my Oracle systems, I headed for this. My final attempt, after looking up some SQL syntax, was:
CREATE DBSPACE KLMTEST USING FILE DF1 '/dev/disk/by-id/scsi-1HITACHI_730109670008' IQ STORE;
But just forget it:
Could not execute statement.
The file '/dev/disk/by-id/scsi-1HITACHI_730109670008' already exists.
-- (st_database.cxx 2215)
SQLCODE=-1010000, ODBC 3 State="HY000"
Line 1, column 1
So what, OF COURSE the device exists…
After looking up another ton or such of documentation (what I should have done earlier, yesyesyes), I found out that Sybase only will use “classical” raw devices in /dev/raw. Wow. So this one is way better:
create dbspace klmtest using file klmtest1 '/dev/raw/raw1' IQ store;
Another pitfall FYI: If /dev/rawctl is not readable for the SYBASE Linux user, you will earn an error like “the raw device is not open”. But now, I have a nice, huge tablespace, and no data in it. But how to transfer data will become another blog entry in the next days.
Best regards
Martin Klier
PS: Make sure that the internal system dbspace, IQ_SYSTEM_MAIN, has enough space to hold the management information for a maybe huge new dbspace of any kind: Otherwise you will get a speaking error message to fix this up.
PS2: My tools used:
- a graphical SQL editor, “dbisql”
- a graphical DBA tool to get an overview, Sybase Central, called via “scjview”
- good, old bash shell, best DBA tool ever 😉