Starting the database server

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

The UNIX versions of Sybase IQ provide the script start_asiq and the Windows version of IQ provides start_asiq.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). start_asiq also includes some parameters and calculates others. For more information about switches specific to your operating system, see the Sybase IQ Installation and Configuration Guide.

start_asiq -n server-name
[ server-options ] [ database-file [ database-options ] ]

The following tables list the available switches for the start_asiq server-options and database-options parameters.

You can list the start_asiq options using the command

start_asiq -?

Sybase recommends that you start and stop multiplex databases using the Sybase Central interface. If you cannot run Sybase Central, or if you need to start multiplex databases from a script, use the startup commands described in this section instead.

Startup or shutdown operations require multiple commands:

• A start_asiq command to start the appropriate server(s).

• One or more dbremote command(s) to control communication between the write and query servers. dbremote is always run on the write server host.

• A dbstop or stop_asiq command to stop the server(s).

You can specify the following multiplex options on the start_asiq command line for managing multiplex databases.

For help on dbremote options, type:

dbremote -h

Sybase provides shell scripts in the write server database directory to start dbremote for each server in the multiplex. There is currently no service mechanism to start these processes automatically; this is up to the user. For example, on a Windows system, a DBA may add an entry in the Windows registry so that these processes start as soon as the DBA logs into the system. Note that dbremote is only required for data replication and servers run without it are still viable. Old data versions will not be recovered at the write server when dbremote is running.

The following example starts the multiplex server Server02. If it was configured to be a query server, it will come up as a query server automatically.

start_asiq @params.cfg -n Server02 
-x "tcpip(port=1234)" mpxdb.db

Then, on the write server’s host:

dbremote -c "uid=DBA;pwd=SQL;eng=Server02;links=tcpip{host=ciaran;port=1234};dbf=mpxdb"
-v -o outfile.out

For more examples of startup and dbremote commands, generate the administrative scripts as described in Sybase IQ System Administration Guide. Use a text editor to view the commands in the scripts.

The start_asiq recovery-options are a subset of the database-options that are used only for database recovery operations.

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 options. For example, the following Windows command file holds a set of command line options for a server named myserver that allows 15 connections, sets the maximum Catalog page size to 4 MB, and loads the sample database:

-gm 15
-gp 4096
-n myserver
c:\sybase\ASIQ-12_5\demo\asiqdemo.db

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

start_asiq @c:\config.txt

set envvar= “-gp 4096 -gm 15” c:\sybase\ASIQ-12_5\demo\asiqdemo.db start_asiq @envvar

The default value of -c in the default.cfg file and start_asiq 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.

The unit P is a percentage of the physical system memory. 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, you would specify:

start_asiq -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 option is provided (either on the command line or using the start_asiq 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.

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 still increases if the database server would otherwise run into the error

Fatal Error: dynamic memory exhausted 

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

Prior to version 12.6, the + or - values were not accepted: specifying the -ct option enabled character set translation.

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 boot 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, this is recommended 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 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, you must do the following:

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

• If your system has between 2 Gb and 16 Gb 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 16 Gb 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 will not be able to address memory beyond 16 Gb.

• If your system has more than 4 Gb 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 on to Windows as Administrator.

  2. From the Start menu, choose Settings > Control Panel.

  3. Open the Administrative Tools folder.

  4. Double-click Local Security Policy.

  5. Open Local Policies in the left pane.

  6. Double-click User Rights Assignment in the left pane.

  7. Double-click the Lock Pages In Memory policy in the right pane.

     The Local Security Policy Setting dialog appears.

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

     The Select Users or Groups dialog appears.

  9. Select the user ID from the list 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 2 Mb, AWE is not used.

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

3. The AWE cache is no smaller than 2 Mb. 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 128 Mb 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 2 Mb. 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 4 KB and dynamic cache sizing is disabled.

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

{ dbsrv9 | dbeng9 } -ec encryption-options …

encryption-options:

 { NONE | SIMPLE | ECC_TLS (CERTIFICATE=filename; CERTIFICATE_PASSWORD=password )   
| RSA_TLS (CERTIFICATE=filename; CERTIFICATE_PASSWORD=password )   
| ALL } , …

The -ec option instructs the database server to accept only connections from ODBC, OLE DB, or embedded SQL interfaces that are encrypted using one of the specified types. Connections over the TDS protocol, which include Java applications using jConnect, are always accepted, regardless of encryption.

By default, communication packets are not encrypted, which poses a potential security risk. If you are concerned about the security of network packets, use the -ec option. Encryption affects performance only marginally. The -ec option controls the server's encryption settings and requires one or more of the following parameters in a comma-separated list:

• none — Accepts only connections that are not encrypted.

• simple — Accepts connections that are encrypted with simple encryption. This type of encryption is supported on all platforms. Simple encryption is not as strong as Certicom encryption.

• ECC_TLS — Currently not supported by Sybase IQ.

• RSA_TLS — Accepts connections that are encrypted using RSA-based encryption technology. To use this type of encryption, both the server and the client must be operating on Solaris, Linux, AIX, or any supported Windows platform, and the connection must be over the TCP/IP port. This parameter accepts the following arguments:

  • certificate — the file name of the certificate. The default value is rsaserver.crt.

  • certificate_password— the password for the certificate named above. The password for rsaserver.crt is test.

• all — Accepts connections that are not encrypted (none), or encrypted with simple encryption (simple). This is the default.

The dbtls9.dll and dbrsa9.dll files contain the Certicom code used for encryption and decryption. When you connect to the server, if the appropriate file cannot be found, or if an error occurs, a message appears on the server console in debug mode. The server does not start if the types of encryption specified cannot be initiated.

The client's and the server's encryption settings must match or the connection will fail. The server automatically encrypts client transmissions that request encryption if -ec none is not specified.

The following sample startup lines show how you start the server with each of the three types of encryption:

start_asiq -ec simple,certicom(certificate=sample.crt; certificate_password=tJ1#m6+W) -x tcpip asiqdemo.db

start_asiq -ec ecc_tls(certificate=sample.crt; certificate_password=tJ1#m6+W) -x tcpip asiqdemo.db

start_asiq -ec rsa_tls(certificate=rsaserver.crt; certificate_password=test) -x tcpip asiqdemo.db

The engine must either not be a Windows service, or 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, you can click Cancel to continue when the dialog box prompt appears.

Both uppercase and lowercase syntax are acceptable.

Both uppercase and lowercase syntax are acceptable.

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.

The allowed values are as follows:

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 you need to specify this parameter in the start_asiq command. To calculate its value use the following formula:

gn_value = gm_value + 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, you must specify utility_db as the database name (that is, there is no database file) and the utility database password (stored in the file util_db.ini) must be known.

The allowed values are as follows:

For example,

-iqlocalreplay "all" 

This example replays all DDL commands from the local TLV log if the server was just synchronized.

Sybase recommends using -iqnumbercpus only in the following situations:

• On machines with Intel^® CPUs and hyperthreading enabled, setting -iqnumbercpus to the number of CPUs available

• On 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 -m server switch 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.

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_asiq -n svrname mydb.db -n dbname

The server name must be used on the connect statement to specify to which server you wish to connect. In all environments, there is always a default database server that will be used if no server name is specified provided at least one database server is running on the system. Always specify a server name to avoid using the default name. 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.

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

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

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

Specifies no server screen display (no console for the server).

• 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. These dialogs do not appear if the -qs option is used.

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 not logged to the -o or -oe logs, but rather to the Windows Application Event Log.

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

Using -sb 0 causes the server not to 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, this option causes the server to be unlisted when using dblocate.

Using -sb 1 causes the server to not respond to broadcasts from dblocate, while leaving 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 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), it is possible that liveness packets will 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.

The list is a comma-separated list of settings taken from the following list: TCPIP, or NamedPipes.

For example,

-x tcpip,ipx

allows only TCP/IP and IPX communications.

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

For some protocols, additional parameters may be provided, in the 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;...)"

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

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

Use the -xs option to specify which web protocols you want to use to listen for client connection broadcasts.

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

If you specify the -xs option with one or more protocols, the server attempts to listen for client requests using the specified protocol(s).

Regardless of which 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:

This feature can also be turned on using the remember_last_statement server setting. 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, not the statements within the procedure.

• ALL logs all SQL statements and other requests to the server.

• NONE turns off SQL statement logging. This is the default.

• SQL logs the following types of requests only:

  • CONTROL_START_DATABASE

  • CONTROL_STOP_ENGINE

  • CONTROL_STOP_DATABASE

  • STMT_PREPARE

  • STMT_EXECUTE

  • STMT_EXECUTE_IMM

  • STMT_EXECUTE_ANY_IMM

  • SQL_OPTION_SET

  • BACKUP

  • DELETE_FILE

  • COMMIT

  • ROLLBACK

  • PREPARE_TO_COMMIT

  • CONNECT

  • DISCONNECT

  • BEGIN_TRANSACTION

  • STMT_DROP

  • CURSOR_OPEN

  • CURSOR_EXPLAIN

  • CURSOR_CLOSE

  • CURSOR_RESUME

  • Errors

The units G, K, and M can be either lower case or upper case. If G, K, or M is not supplied, 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 the specified size (in this case 10K).

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, it is recommended that where this option is used, the transaction log be placed on a separate device or partition from 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.

Both database servers and databases can be named. 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, if the server is started on c:\sybase\ASIQ-12_6\demo\asiqdemo.db and no -n option is specified, then the name of the database is asiqdemo.

For naming conventions, see -n server option.

Be sure to follow correct procedures when using -iqfrec and -iqdroplks. See Sybase IQ Troubleshooting and Error Messages Guide for details.