• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1170
  • Last Modified:

How to create a new Database in Sybase 15.3?

I'm new to Sybase and want to create a new database.  How should I do so?  Is there any documentation for common usage for me for reference?  Thanks.
9 Solutions
You may need to create disk devices first before creating databases. You can check available devices by sp_helpdevice stored proc. You should check 'disk init' command if you need to create devices.

Chapter 7 of System Administration Guide: Volume 1 explains database device initialization
Which database ?  ASE, IQ, or SA ?
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

thomaszhwangAuthor Commented:
First, you must start the Utility server....    aka    start_iq -n utility_db

Then run the following SQL against it:       (this is a Linux example...)   all subdirectories must exist...
- this will create a 128k page size server

CREATE DATABASE '/opt/sybase/DB/myiq/cat/myiq.db'
 IQ PATH '/opt/sybase/DB/myiq/dev/IQ_SYSTEM_MAIN.dat'
 IQ SIZE 300
 IQ PAGE SIZE 131072
 MESSAGE PATH '/opt/sybase/DB/log/myiq.iqmsg'
 TEMPORARY PATH '/opt/sybase/DB/myiq/dev/IQ_SYSTEM_TEMP.dat'

Then you can add a user-defined DbSpace, with added DbFiles as your main store....

You will need to create a rudimentary .cfg file for your new server:

# myiq.cfg
# ----------------------------------------------------------------------
# This file contains the IQ startup parameters.  All servers
# started will default to these parameters, unless overriden by contents
# of parameter list.
# ----------------------------------------------------------------------
# Must be in the format: One parameter per line
# ----------------------------------------------------------------------

-n myiq
-x tcpip{host=myhostname;port=6666}

-c  32m
-gc 20
-gd dba
-gk dba
-gl all
-gm 50
-gp 4096
-ti 4400

-iqmc 500
-iqtc 1000

# uncomment to log all SQL to a file
#-zo /cs/sybase/DB/log/SQLTRAP.log
#-zr SQL

-n myiq
start your new server:

start_iq @myiq.cfg myiq.db

Then you need some more SQL to create a user-defined DbSpace and add additional DbFiles as needed:  

Run the following against your new myiq server...

-- Create user defined IQ_MAIN and add devices to it

create dbspace IQ_MAIN using file IQ_MAIN '/opt/sybase/DB/myiq/dev/IQ_MAIN.dat'
size 100

alter dbspace IQ_MAIN add file IQ_MAIN_00 '/opt/sybase/DB/myiq/dev/IQ_MAIN_00.dat'
size 100

-- now revoke create on IQ_SYSTEM_MAIN from public
-- and grant create on IQ_MAIN to public

revoke create on IQ_SYSTEM_MAIN from public ;
grant create on IQ_MAIN to public ;
Then run the following against the myiq server to get some decent initial options:
(make sure any directories referenced have been created..)

set option dba.chained = 'OFF' ;
set option public.chained = 'OFF' ;

set option dba.minimize_storage = 'ON' ;
set option public.minimize_storage = 'ON' ;

set option dba.force_no_scroll_cursors = 'ON' ;
set option public.force_no_scroll_cursors = 'ON' ;

set option public.checkpoint_time = 20 ;

-- Neutral settings for query plans, to enable, just toggle public.query_plan_as_html = 'ON'
set option dba.query_plan = 'OFF' ;
set option dba.query_detail = 'ON' ;
set option dba.query_timing = 'ON' ;
set option dba.query_plan_after_run = 'ON' ;
set option dba.index_advisor = 'ON' ;
set option dba.query_plan_as_html = 'OFF' ;
set option dba.query_plan_as_html_directory = '/opt/sybase/DB/myiq/log/html' ;

set option public.query_plan = 'OFF' ;
set option public.query_detail = 'ON' ;
set option public.query_timing = 'ON' ;
set option public.query_plan_after_run = 'ON' ;
set option public.query_plan_as_html = 'OFF' ;
set option public.query_plan_as_html_directory = '/opt/sybase/DB/myiq/log/html' ;

set option public.index_advisor = 'ON' ;

set option public.default_dbspace = 'IQ_MAIN' ;

And that will get you started...  the rest is up to you to add groups, add users, grant group memberships, set up permissions,  add schema.... etc etc...  

The documentation set for 15.3 starts here:


- David
thomaszhwangAuthor Commented:
I was trying to use the Sybase Central.  I suppose this tool should be something equivalent to SQL Server Management Studio.  After I log in, it looks like I'm directly working with a sample database, so I clicked the "Work with server [server name]" button to work at the server level.  Then I clicked "Create a database on server [server name]".  It started a wizard.  I used default value for most of the settings and click the "Finish" button, then it shows me the following error.  It looks like a file permission error.  Any idea?  Thanks.

java.sql.SQLException: [Sybase][ODBC Driver][Sybase IQ]Operation failed on file due to file permissions. File: D:\Sybase DB Data
-- (hos_ion.cxx 177)
The database file 'D:\Sybase DB Data\test.db' could not be created.
Yes, looks like file permissions.  Everything done through Sybase Central is done via an agent, so this agent must have permissions to do these things.  Unfortunately Sybase Central is not a great way to learn how IQ works.  Basically that wizard will try to guide you through the creation of a catalog (.db file) and several DbFiles (main and temp storage areas.)  At the end of the day it relies on a pre-existing, running server to create the database, so you must have the demo database installed and running in order to connect to it in Sybase Central.  In this scenario the demo database would be used in place of the utility_db server to create the new database.  In the first screens you see you are given the option of starting a utility server (utility_db), or connecting to an existing server (like the demo database).

If your software is installed on drive D: and it won't let you write to D: because of your system policies, perhaps you can try specifying paths to devices on drives (or network shares as necessary) you CAN write to...  

Careful:  on Windows systems you may have to escape your backslashes in paths (write them as a double backslash) for use with IQ if they are not quoted strings....  eg  E:\\MyFiles\\MyIQServer\\MyIQServer.db  


- David

thomaszhwangAuthor Commented:

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now