Starting the database server

The database startup utility start_iq starts a Sybase IQ network database server.

The UNIX versions of Sybase IQ provide the script start_iq, and the Windows version of IQ provides start_iq.exe. These scripts verify that your environment is set correctly and start the server with all required switches preset to recommended defaults (along with any switches you add). The start_iq utility also includes some parameters and calculates others. For switches that are specific to your operating system, see the Installation and Configuration Guide.

start_iq [ server-options ] [ database-file 
[ database-options ], ...]

Sybase recommends that you specify the -n switch to run Sybase IQ effectively. The -n command-line switch is a server switch that names the server. For example start_iq -n myServer @iqdemo.cfg iqdemo.db. Specifying the -n switch prevents you from unintentionally connecting to the wrong server.

You can list the start_iq switches using the command:

start_iq -?

The following table lists the available switches for the start_iq server options parameters.

If there is a problem starting the server, start_iq returns a non zero value. If you did not specify a log file after the -o switch on startup, the error is written to the first one of the following that is defined:(Windows)

• %ALLUSERSPROFILE%\SybaseIQ\IQ15_1\logfiles\

• (UNIX / Linux) $IQDIR15/logfiles/

The following table lists the available switches for the start_iq database options parameters. These options apply only to the preceding database in the command syntax.

If you use a relative path, the path is read relative to the current working directory of the server. You can supply a full path.

On Windows you can supply a path that conforms to the Universal Naming Convention (UNC) format:

\\server\volume\path\file.ext

The file may contain line breaks, and may contain any set of command-line switches. For example, the following Windows command file holds a set of command line switches for a server named iqdemo that allows 10 connections, sets the maximum catalog page size to 4096 bytes, and loads the iqdemo database:

# iqdemo.cfg
# ------------------------------------------------
# Default startup parameters for the IQ demo database
# ---------------------------------------------------
-n  iqdemo
-x  tcpip{port=2638}
# The following parameters are also found in the configuration file
# %IQDIR!%\scripts\default.cfg.  Any parameters not specified below
# and not in the start up parameter list, will be added by start_iq
# using default.cfg as a guide.				

-c  48m
-gc 20
-gd all
-gl all
-gm 10
-gp 4096
-iqmc 32
-iqtc 24

If this configuration file is saved as c:\config.txt, the file can be used in a command line as follows:

start_iq @c:\config.txt

set envvar= -gp 4096 -gm 15 
c:\sybase\IQ-15_1\demo\start_iq -n myserver @envvar iqdemo.db

The default value of -c in the default.cfg file and start_iq is 32MB (-c 32M) for Windows platforms, and 48MB (-c 48M) for UNIX platforms. For IQ databases, Sybase recommends that you use this default or set -c to a higher value.

You can use % as an alternative to P, but as most non-UNIX operating systems use % as an environment variable escape character, you must escape the % character. For example, to use 20 percent of the physical system memory, specify:

start_iq -c 20%% ...

On UNIX operating systems, if the cache size specified with -c is greater than the amount of available memory, the database server uses a maximum cache size that is calculated as follows:

If no -c switch is provided (either on the command line or using the start_iq default), the database server computes the initial catalog store cache allocation as follows:

1. The database server uses 32MB as the minimum default cache size.

2. The database server computes a runtime-specific minimum default cache size, which is the lesser of the following items:

   • 25% of the physical memory of the machine.

   • The sum of the sizes of the main database files specified on the command line. Additional dbspaces apart from the main database files are not included in the calculation. If no files are specified, this value is zero.

3. The database server allocates the greater of the two values computed.

Do not use -c in the same configuration file or command line with -ch or -cl. For related information, see the -ch cache-size option below.

Ordinarily, the database server automatically takes additional cache as needed. You can disable automatic cache increase due to high server load by using -ca 0 on the command line. The cache size continues to increase if the database server would otherwise run into this error:

Fatal Error: dynamic memory exhausted 

or if the Java VM requires memory that would otherwise lead to a fatal error.

• collection of requested pages starts or stops (controlled by the -cc server option)

• page reloading starts or stops (controlled by the -cr server option)

Because Windows 2000, Windows XP, and Windows Server 2003 support Address Windowing Extensions, you can use the -cw option to take advantage of large cache sizes based on the maximum amount of physical memory in the system. Remember, though, that the size of the catalog store cache has much less impact on performance for IQ databases than the IQ main and temporary buffer caches.

*You must restart the operating system using the /3GB option to use a cache of this size.

When using an AWE cache, almost all of the available physical memory in the system can be allocated for the cache.

If you can set a catalog store cache of the desired size using a non-AWE cache, Sybase recommends that you do so, because AWE caches allocate memory that can only be used for the catalog store. This means that while the database server is running, the operating system and the IQ store caches cannot use the memory that is allocated for the catalog store cache.

AWE caches do not support dynamic cache sizing. Therefore, if an AWE cache is used and you specify the -ch or -cl options to set the upper and lower cache size, they are ignored.

To start a database server with an AWE cache:

• Have at least 130MB of memory available on your system.

• If your system has between 2GB and 16GB of memory, add the /3GB option to the Windows boot line in the “[operating systems]” section of the boot.ini file.

  If your system has more than 16GB of memory, do not add the /3GB option to the Windows boot line in the “[operating systems]” section of the boot.ini file because Windows cannot address memory beyond 16GB.

• If your system has more than 4GB of memory, add the /PAE option to the Windows boot line in the “[operating systems]” section of the boot.ini file.

• Grant the “Lock pages in memory” privilege to the user ID under which the server is run. The following steps explain how to do this on Windows 2000.

  1. Log in to Windows as Administrator.

  2. Select Start > Settings > Control Panel.

  3. Open the Administrative Tools folder.

  4. Double-click Local Security Policy.

  5. In the left pane, open Local Policies.

  6. In the left pane, double-click User Rights Assignment.

  7. In the right pane, double-click the Lock Pages In Memory policy.

  8. In the Local Security Policy Setting dialog, click Add.

  9. Select the user ID and click Add.

  10. In the Local Security Policy Setting dialog, click OK.

  11. Restart the computer for the setting to take effect.

If you specify the -cw option and the -c option on the command line, the database server attempts the initial cache allocation as follows:

1. The AWE cache is no larger than the cache size specified by the -c option. If the value specified by the -c option is less than 2MB, AWE is not used.

2. The AWE cache is no larger than all available physical memory less 128MB.

3. The AWE cache is no smaller than 2MB. If this minimum amount of physical memory is not available, an AWE cache is not used.

When you specify the -cw option and do not specify the -c option, the database server attempts the initial cache allocation as follows:

1. The AWE cache uses 100% of all available memory except for 128MB that is left free for the operating system.

2. The AWE cache is no larger than the sum of the sizes of the main database files specified on the command line. Additional dbspaces apart from the main database files are not included in the calculation. If no files are specified, this value is zero.

3. The AWE cache is no smaller than 2MB. If this minimum amount of physical memory is not available, an AWE cache is not used.

When the server uses an AWE cache, the catalog cache page size is at least 4KB, and dynamic cache sizing is disabled. On 64-bit Windows platforms, the cache page size is at least 8KB.

For more information about dynamic cache sizing, see the -ch and -cl server options.

The engine cannot be a Windows service, or it must be a Windows service with the interact with desktop option turned on.

When used with supported tools, this option always prompts the user for the encryption key, even if a key is not necessary. If you know that a key is not necessary, click Cancel to continue when the dialog box prompt appears.

If there is a transaction log in the same directory as the database, the database server performs a checkpoint recovery, and a recovery using the transaction log, and then shuts down—it doesn't continue to run. You can then restart the database server without the -f option for normal operation.

You can use either uppercase and lowercase syntax for the options.

You can use either uppercase and lowercase syntax for the options.

The LOAD TABLE statement reads files from the database server machine.

To control access to the file system using these statements, the -gl command-line switch allows you to control the level of database permission that is required to use these statements.

Both uppercase and lowercase syntax are acceptable.

The database server allows one extra DBA connection above the connection limit to allow a DBA to connect to the server and drop other connections in an emergency.

Each connection uses a thread for each request, and when the request is completed, the thread is returned to the pool for use by other connections. As no connection can have more than one request in progress at one time, no connection uses more than one thread at a time.

An exception to this rule is if a Java application uses threads. Each thread in the Java application is a database server execution thread.

On Windows, specify this parameter in the start_iq command. To calculate its value, use:

gn_value >= gm_value * 1.5

The utility_db level restricts the use of these commands to only those users who can connect to the utility database. The all, none, and dba levels respectively permit all users, no users, or connected users with DBA authority to execute utility commands.

To connect to the utility database, specify utility_db as the database name (that is, there is no database file) and specify the utility database password (stored in the file util_db.ini).

-iqmsgnum is an integer 0 – 64 (inclusive). The default value is 0, which means that messages are wrapped in the main message log file.

A -iqmsgnum value n greater than 0 means that the server maintains n message log archives. For example, this command specifies that the server maintain 3 archives of the message log:

start_iq -n iqdemo iqdemo.db ... <other options> ... -iqmsgsz 100 -iqmsgnum 3

A -iqmsgsz value n greater than 0 means that the message log can grow up to n megabytes in size. For example, the following command limits the size of the message log to 100MB:

start_iq -n iqdemo iqdemo.db ... <other options> ... -iqmsgsz 100

Sybase recommends that you use -iqnumbercpus only on:

• Machines with Intel CPUs and hyperthreading enabled, setting -iqnumbercpus to the number of CPUs available

• Machines where an operating system utility has been used to restrict Sybase IQ to a subset of the CPUs within the machine

Setting -iqnumbercpus higher than the number of available CPUs may affect performance.

The allowed values are as follows:

The -m server switch is useful if you are processing high-volume transactions requiring fast response times, and the contents of the transaction log are not being relied upon for recovery or replication.

To avoid database file fragmentation, when you use this switch place the transaction log on a separate device or partition from the database itself.

If you start the server with the -m switch, you cannot create a database.

Each server name must be unique across the local area network (domain). This prevents you from unintentionally connecting to the wrong server. The host name and port number combination does not uniquely identify the server.

The server name is interpreted according to the character set of the machine, as no database collation exists at startup time. The server name must be a valid identifier. Long server names are truncated to 40 characters. On NetBIOS, 16 characters is the maximum length. For multiplex server names, 30 characters is the maximum.

The server name specifies the name to be used on client application connection strings or profiles.

start_iq -n svrname mydb.db -n dbname

The server name must be used on the connect statement to specify the server you are connecting to. In all environments, there is always a default database server that is used if no server name is specified, provided at least one database server is running on the system.

Multiple database servers with the same name are not allowed to run on TCP/IP anywhere on the network, even on separate ports.

Under some circumstances, changing the compression threshold may improve performance of a compressed connection by allowing you to compress packets only when compression will increase the speed at which the packets are transferred.

If the client and server specify different compression threshold settings, the client setting takes precedence.

• No unique index or primary key for table 'table_name'

• Database file “mydatabase.db” consists of nnn fragments

• Could not open/read database file: <database file>

• A database server with that name has already started

On Windows platforms, if the server is not being autostarted, these errors appear in a dialog and must be cleared before the server stops.

If there is an error loading the language DLL, no dialog appears if -qs was specified on the command line, and not in @environment-variable or @filename syntaxes. This error is logged in the Windows Application Event Log.

Usage errors are suppressed if -qs is on the command line, but not in @filename or @environment-variable expansion.

-sb 0 means the server does not start up any TCP/UDP broadcast listeners. In addition to forcing clients to use the DoBroadcast=NONE and HOST= options to connect to the server, -sb 0 causes the server to be unlisted when using dblocate.

-sb 1 means the server does not respond to broadcasts from dblocate, but leaves connection logic unaffected. You can connect to the server by specifying LINKS=tcpip and ENG=<name>.

The -tl switch on the server sets the liveness timeout for all clients that do not specify a -tl switch.

When there are more than 200 connections, the server automatically calculates a higher LivenessTimeout value based on the stated LivenessTimeout value. This enables the server to handle a large number of connections more efficiently.

Clients send liveness packets at an interval of between one third and two thirds of the LivenessTimeout value on each idle connection. Large numbers of liveness packets are not sent at the same time. If liveness packets take a long time to send (depending on the network, the machine's hardware, and the CPU and network load on the machine), liveness packets may be sent after two thirds of the LivenessTimeout. A warning appears in the server console if the liveness sends take a long time. If this warning occurs, consider increasing the LivenessTimeout value.

• abort – the UNIX abort function is called, and a core file is generated.

• default – the database server behaves in the same manner as abort in all cases, except when a device-full fatal error occurs. In this case, it behaves in the same manner as defunct. This action prevents the system from trying to write a core file on a full device. This is the default behavior.

• defunct – the database server continues running and does not call abort. Any new connection attempts made to the database server receive the SQL error of the original fatal error.

list is a comma-separated list of tcpip or namedpipes settings. For example:.

For example,

-x tcpip,ipx

allows only TCP/IP and IPX communications.

The default is to try all settings supported by the database server on your operating system.

For some protocols, you can provide additional parameters, in this format:

-x tcpip(PARM1=value1;PARM2=value2;...)

For UNIX, quotation marks are required if more than one parameter is supplied:

-x "tcpip(PARM1=value1;PARM2=value2;...)"

-xs { all | none | web-protocols } …

web-protocols:     { [ http | https | http_fips ] parmlist },… parmlist:     ( parm=value;…)

Use the -xs option to specify the Web protocols you want to use to listen for client connection broadcasts. If you specify one or more protocols, the server attempts to listen for client requests using the specified protocol(s).

If you do not specify the -xs option, the server does not attempt to listen for Web requests.

Regardless of the settings you choose for the -xs option, the server always listens for connection broadcasts using the shared memory protocol. You can specify any of the following:

Alternatively, you can enable this feature using the remember_last_statement option of the sa_server_option system procedure. You can obtain the most recently-prepared SQL statement for a connection using the LastStatement property function. The sa_conn_activity stored procedure allows you to obtain the most recently-prepared SQL statement for all current connections to the database on the server.

For stored procedure calls, only the outermost procedure call appears.

You can specify G, K, and M units using either uppercase or lowercase. If you do not specify units, any integer less than 10 000 is assumed to be in kilobytes, and any integer 10,000 or greater is assumed to be in bytes.

When the request log file reaches the size specified by either the -zs option or the sa_server_option system procedure, the file is renamed with the extension .old appended (replacing an existing file with the same name if one exists). The request-level log file is then restarted.

By default there is no limit. The value is in kilobytes.

The following example shows how the -zs option is used to control log file size. Suppose you start a database server with the following options on the command line:

-zr all -zs 10 -zo mydatabase.log

A new log file mydatabase.log is created. When this file reaches 10K in size, any existing mydatabase.old files are deleted, mydatabase.log is renamed to mydatabase.old, and a new mydatabase.log file is started. This process is repeated each time the mydatabase.log file reaches 10K.

-ec encryption-options …

encryption-options:

 { NONE | 
	SIMPLE | 
TLS ( TLS_TYPE=cipher;
[FIPS={Y|N};] 
IDENTITY=server-identity-filename;
IDENTITY_PASSWORD=password ) } , …

The -m option is useful where high volume transactions requiring fast response times are being processed, and the contents of the transaction log are not being relied upon for recovery or replication. When this option is selected, there is no protection provided against media failure on the device containing the database files.

To avoid database file fragmentation, Sybase recommends that if you use the -m option, you place the transaction log on a separate device or partition than the database itself.

This option is the same as the -m server option, but applies only to the current database or the database identified by the database-file command-line variable.

Since a database server can load several databases, the database name is used to distinguish the different databases. However, Sybase strongly recommends that you run only one database on an IQ server. If you must run two databases, start two IQ database servers on different ports.

By default, the database receives as a name the file name with the path and extension removed. For example, you start a server on c:\sybase\IQ-15_1\demo\iqdemo.db and do not specify the -n option, then the name of the database is iqdemo. To avoid using the default name, always specify a server name.

For naming conventions, see the -n server option.