Novell Home

Tuning the Inventory Database

Novell Cool Solutions: Trench
By Tim Heywood

Digg This - Slashdot This

Posted: 19 Jul 2000
 

Current version: ZENworks 2

Note: Some of this information was inadvertently left off of the original posting on July 19. We apologize for any confusion.

This is the Sybase switch info on how to tune an ASA602 Database. This will help anyone who's perhaps the merest bit confused by the Inventory Database (lots of you, judging from our mail). Tim is currently working on a project in the UK producing a reporting tool for the ZENworks inventory (ZEN 2 and ZfD3), about which we'll be telling you more in the future.


Switch Descriptions

  • @ filename

    Read in switches from a configuration file

  • @ envvar

    Read in switches from an environment variable

  • -?

    Display usage information

  • -b

    Run in bulk operations mode

  • -c cache-size

    Set maximum cache size

  • -C console

    Run server on specified console [UNIX]

  • -d

    Disable asynchronous I/O [OS/2, Windows NT, NetWare]

  • -df

    Force direct I/O [Windows 3.x]

  • -di

    Use direct I/O if possible [Windows 3.x]

  • -e

    Enable packet encryption [network server]

  • -ga

    Automatically unload the database after the last connection closed. In addition, shut down after the last database is closed.

  • -gb level

    Set database process priority class to level [Windows NT, OS/2]

  • -gc num

    Set checkpoint timeout period to num minutes

  • -gd level

    Set database starting permission

  • -ge size

    Set the stack size for threads that run external functions [not UNIX or Windows 3.X]

  • -gf

    Disable firing of triggers

  • -gk level

    Set the permission that is required to stop the server

  • -gm num

    Limit the maximum number of connections

  • -gn num

    Set the number of threads

  • -gp size

    Set the maximum page size to size bytes.

  • -gr num

    Set the maximum recovery time to num minutes

  • -gs size

    Set the thread stack size to size bytes

  • -gt num

    Set the number of CPUs for request processing

  • -gu level

    Set the permission level for utility commands: utility_db, all, none, dba

  • -gw num

    Set the interval (in milliseconds) for background processing

  • -gx

    Disable dual threading [network server, Windows 95 and Windows NT]

  • -l password

    (lower case L) Lock the keyboard with the specified password [network server ]

  • -m

    Truncate the transaction log after each checkpoint, for all databases

  • -n name

    Use name as the name of the database server

  • -o filename

    Output messages to the specified file

  • -p packet-size

    Set the maximum network packet size [network server]

  • -q

    Quiet mode - suppress output

  • -r

    Disable multiple-row fetching

  • -s

    Set the syslog facility ID (none, user, daemon, local0,..., local7) [UNIX]

  • -ss

    The screen is blanked after the number of minutes specified in this command- line switch. [UNIX, NetWare, QNX]

  • -ta sec

    Scan time for terminated applications?default 30 seconds[Windows 3.X, Windows 95, Windows NT]

  • -ti min

    Client idle time before shutdown -- default 240 minutes [network server]

  • -tl sec

    Default liveness timeout for clients in seconds -- default 120 seconds [network server]

  • -tq time

    Set quitting time [network server]

  • -tr sec

    Active request termination if no retries?default 60 seconds [network server]

  • -u

    Use buffered disk I/O [Windows 95 and Windows NT]

  • -ud

    Run as a daemon [UNIX]

  • -ut min

    Touch temporary files every min minutes [UNIX]

  • -v

    Log old values of all columns on UPDATE or DELETE, for all databases

  • -x list

    Comma-separated list of communication links to try

  • -y

    Run as a Windows 95 service [Windows 95]

  • -Z

    Provide diagnostic information on communication links [network server]

That's the basics, more detail below... sorry that this is so dry, but that's the subject...

Details

  • @filename

    Read in command-line switches from the supplied file.

    The file may contain line breaks, and may contain any set of command line switches. For example, the following command file holds a set of command line switches for a server named myserver that starts with a cache size of 4Mb and loads the sample database:
    -c 4096
    -n myserver
    c:\asa6\asademo.db

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

  • @environment-variable

    Read in command-line switches from the supplied environment variable. The environment variable may contain any set of command line switches. For example, the first statement sets an environment variable that holds command line switches for a database server that starts with a cache size of 4Mb and loads the sample database. The second statement starts the database server:
    set envvar=-c 4096 c:\asa6\asademo.db
    dbsrv6 @envvar

    Note:
    Environment variable given priority

    If you have both a file and an environment variable with the value of your @ command-line switch, the environment variable is used.

  • -?

    Display usage information.

  • -b

    Use bulk operation mode.

    This is useful when you are loading large quantities of data into a database through the Interactive SQL INPUT command. The -b option should not be used if you are using LOAD TABLE to bulk load data.

    The database server allows only one connection by one application. It does not keep a rollback log or a transaction log, and the multi-user locking mechanism is turned off. When you first start the database server after loading data with the -b switch, you should use a new log file.

    Bulk operation mode does not disable the firing of triggers.

  • -c cache-size

    Sets the size of the file that is used for caching database pages. Any cache size less than 10000 is assumed to be K-bytes (1K = 1024 bytes). Any cache size 10000 or greater is assumed to be in bytes. The cache size may also be specified as

    nK
    or
    nM

    (1M = 1024K). By default, the database server uses 2 megabytes of memory for caching. The more cache memory that can be given the server, the better will be its performance.

    Note:
    NetWare database server

    There is a trade off between memory for the database server and memory for the NetWare file system buffers. A larger database server cache will improve database server performance at the expense of NetWare file system performance. If the database server cache is too big, NetWare will report an error that there is insufficient memory for cache buffers.

    NetWare memory requirements increase with every new directory and file on the file server. To track memory usage on the NetWare server, load monitor.nlm (if it is not already loaded) and select "Resource Utilization". Extra memory for your NetWare server computer could improve database performance and/or file server performance dramatically.

  • -C console

    This option is for UNIX only. It specifies on which console number the database server display should run. If this option is omitted, then the database server uses the current console if running in the foreground, or the first unused console if it is running in the background.

    The server display appears on a console, as follows:

    By default, the server display uses the current console.

    If you start the database server as a background task (using &), the server display uses a different console.

    With a -C 0 (zero) command line, the database server uses the first unused console.

    With a -C n command line, the database server uses console n . For example,
    dbsrv6 -C 4
    uses the console /dev/con04.

    With a -C device command line, the database server uses the specified device. This allows the database server display to use a different node from the current node. For example,
    dbserver -C //1/dev/con03
    uses console 3 on node 1 for the server display.

    If you start the database server using the -q switch, there is no server display.

  • -d disable asynchronous I/O

    Use synchronous I/O rather than asynchronous I/O. Asynchronous I/O is generally the preferred option.

    Since Windows 3.x and Windows 95 systems use synchronous I/O by default, this option applies only to Windows NT, OS/2 and NetWare systems, which use asynchronous I/O by default.

  • -df force direct I/O

    For Windows 3.x only. The default I/O method for Windows 3.x is to use normal DOS input and output instead of direct input and output.

    This option forces the use of direct, or asynchronous, I/O, rather than normal DOS calls. Since asynchronous I/O is the default setting for Windows NT, OS/2 and NetWare systems, this option has no effect in those environments.

    Asynchronous I/O is not supported in Windows 95 environments.

  • -di use direct I/O if possible

    For Windows 3.x only. The default I/O method for Windows 3.x is to use normal DOS input and output instead of direct input and output.

    When the -di option is supplied, the database server tests to see if it is possible for direct I/O to be used before it is implemented.

    With this switch, the server will not use direct I/O under Windows for Workgroups 3.11 or for some highly fragmented database files.

    Asynchronous I/O is not supported in Windows 95 environments.

  • -e

    Encrypt all packets that are transmitted to and from all clients over the network. By default, packets are not encrypted, thus opening a potential security risk. If you are concerned about the security of network packets, use the -e switch. Encryption does marginally affect performance. This switch does not apply to the personal server.

  • -ga

    The meaning of this switch depends on whether you are running a personal server or a network server.

    Notes:
    The -ga switch is not supported in Netware 3.11.

  • Network server

    Specifying this switch on the network server causes each database to be unloaded after the last connection to it is dropped. The database server itself does not shut down.

  • Personal server

    In addition to unloading each database after the last connection is dropped, the personal server shuts down when the last database is stopped.

  • -gb level

    OS/2 and Windows NT only. Set the database process priority class to level. Level must be one of idle , normal (the default), high , or maximum. Idle is provided for completeness, and maximum may interfere with the running of your computer. Normal and high are the commonly used settings.

  • -gc num

    Set the maximum desired length of time (in minutes) that the database server will run without doing a checkpoint. The default value is 60 minutes.

    When a database server is running with multiple databases, the checkpoint time that is specified by the first database started is used unless overridden by this switch. If a value of 0 is entered, the default value of 60 minutes is used.

  • -gd level

    Set the database starting permission to level . This is the permission level that allows a user to cause a new database file to be loaded by the server. The level can be one of the following:

    • DBA

      Only users with DBA authority can start new databases.

    • ALL

      All users can start new databases.

    • NONE

      Starting new databases is not allowed.

    The default setting is ALL for the personal database server and DBA for the network database server.

  • -ge size

    Sets the stack size for threads running external functions, in bytes. The default is 16384 (16K). This switch is used only for OS/2, Windows 95, Windows NT, and NetWare.

  • -gf

    Disable firing of triggers by the server.

  • -gk level

    Set the permission that's required to stop the database server (using dbstop to level. The level can be one of the following:

    • DBA

      Only users with DBA authority can use dbstop to stop the server (the default).

    • ALL

      All users can use dbstop to stop the server.

    • NONE

      The server cannot be stopped using dbstop.

  • -gm num

    Limit the maximum number of connections to the server at one time. If this number is greater than the number that is allowed under licensing and memory constraints, it has no effect.

  • -gn num

    Sets the number of execution threads that will be used in the database server while running with multiple users.

  • gp size

    Sets the maximum page size allowed, in bytes. The size must be one of 1024, 2048, or 4096. When a database server is running with multiple databases, the page size that is specified by the first database is used unless overridden by this switch. Without using this option, an attempt to load a database file with a page size larger than the page size of the database first loaded will fail.

  • -gr num

    Sets the maximum desired length of time (in minutes) that the database server will take to recover from system failure.

    For more information, see RECOVERY_TIME option.

    When a database server is running with multiple databases, the recovery time that is specified by the first database started is used unless overridden by this switch.

  • -gs size

    Sets the stack size of every internal execution thread in the server. The value entered is multiplied by four to produce the stack size in bytes.

    The default number of execution threads and the stack size of each thread is operating system dependent. The number of execution threads is controlled by the -gn switch, and has a default value of twenty for all platforms. The default value of size is operating system dependent. You may want to use the - gs option to lower the memory usage of the database server in environments with limited memory.

  • -gt num

    Sets the number of CPUs to use for request processing. By default, the database server uses all CPUs available on the machine.

  • -gu level

    Sets permission levels for utility commands such as CREATE DATABASE and DROP DATABASE. The level can be set to one of the following: utility_db , all , none, dba.

    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 permit all users , no users, or users with dba authority to execute utility commands.

  • -gw num

    Sets the interval for background processing, in milliseconds. At each interval, the server carries out one I/O operation. The default setting is 500 (half a second).

  • -gx

    Disable dual operating-system threading. This option is available for the Windows 95 and Windows NT versions only. It applies only to the network server.

    On machines with more than one processor, the network request management and the request processing are run by default as two separate operating-system threads. Use the -gx switch if you want the database server to never use more than one processor of a multi-processor machine. This switch applies only to the network server.

  • -l password

    Lock the keyboard with the specified password.

  • -m

    Truncate (delete) the transaction log when a checkpoint is done, either at shutdown or as a result of a checkpoint scheduled by the server. This provides a way to automatically limit the growth of the transaction log. Checkpoint frequency is still controlled by the CHECKPOINT_TIME and RECOVERY_TIME options (which you can also set on the command line).

    The -m option is useful where high volume transactions that require 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 against media failure on the device that contains 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.

    Note:
    Replicated databases

    Do not use the -m option with databases that are being replicated. Replication inherently relies on transaction log information.

  • -n name

    Set the name of the database server. By default, the database server receives the name of the database file with the path and extension removed. For example, if the server is started on the file c:\sybase\asa6\asademo.db and no -n switch is specified, the name of the server is asademo.

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

    Note:
    There are two n switches

    The -n switch is positional. If it appears after a database file name, it has a different meaning. See "Database switches" later in this section.

    The server name can be used on the connect statement. In all environments, there is a default database server that will be used if no server name is specified, provided at least one database server is running on the computer.

  • -o filename

    Print all server message window output to a file.

  • -p packet-size

    Set the maximum size of communication packets. The default is 512 bytes. The minimum value is 280 bytes and the maximum is 16000. This switch applies only to the network server.

  • -q

    Do not display the server screen.

  • -r

    Fetch only one row per network request. By default, when the database server gets a simple fetch request, it fills one network packet with several rows so that subsequent sequential fetches do not require network traffic. This is often referred to as blocking of fetches. This switch disables multiple-row fetching for all clients. This switch applies to the network server only.

    For more information on the -r switch, see the PREFETCH option.

  • -s

    For UNIX servers, this sets the ID that is used in messages to the syslog facility. The default is user.

  • -ss minutes

    For character-mode displays only (UNIX, NetWare, OS/2), a screen blanking option is provided. The screen is blanked after the number of minutes specified in this command-line switch.

  • -ta seconds

    For Windows 3.x, Windows NT, and Windows 95 only. The database server periodically scans the connection list and disconnects any connections associated with terminated applications connected directly to the server. The scan period can be controlled using the -ta switch, and has a default value of 30 seconds. Setting the value to zero prevents scanning.

  • -ti minutes

    Disconnect connections that have not submitted a request for minutes minutes. The default is 240 (4 hours). A client machine in the middle of a database transaction will hold locks until the transaction is ended or the connection is terminated. The -ti option is provided to disconnect inactive connections, freeing their locks. This switch applies only to network servers.

  • -tl seconds

    A liveness packet is sent periodically across a client/server TCP/IP or IPX communications protocol to confirm that a connection is intact. If the server runs for a liveness timeout period (default 2 minutes) without detecting a liveness packet, the communication is severed. The server drops any connections associated with that client. UNIX clients do not do liveness checking. This switch applies only to the network server.

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

    Liveness packets are sent at an interval of the (liveness timeout)/4.

  • -tq time

    Shut down the server at a specified time. This is useful for setting up automatic off-line backup procedures (see Backup and Data Recovery ). The format for the time is in hh:mm (24 hour clock), and can be preceded by an optional date. If a date is specified, the date and time must be enclosed in double quotes and be in the format " YYYY/MM/DD HH:MM ". This switch applies only to the network server.

  • -tr seconds

    An active request and connection is terminated if the server does not receive a retransmit within the specified number of seconds. This switch applies only to the network server.

    The default setting is 60 seconds. The -ts client command-line switch controls the client retry time.

  • -u

    This option applies to the Windows 95 and Windows NT database servers only. Files are opened using the operating system disk cache in addition to the database cache.

    While the operating system disk cache may improve performance in some cases, in general better performance is obtained without this switch, using the database cache only.

    If the server is running on a dedicated machine, you should not use the -u option, as the database cache itself is generally more efficient. You may want to use the -u option if the server is running on a machine with several other applications (so that a large database cache may interfere with other applications) and yet IO-intensive tasks are run intermittently on the server (so that a large cache will improve performance).

  • -ud

    For UNIX servers, this switch causes the process to run as a daemon. Using this option you can cause the server to continue running after the current user logs out.

  • -ut min

    For UNIX servers, this switch causes the server to touch temporary files at specified intervals.

  • -v

    Causes the network server to record in the transaction log the previous values of each of the columns whenever a row of any loaded database is updated or deleted. By default, the server will only record enough information to uniquely identify the row (primary key values or values from a not null unique index). This switch is useful for working on a copy of a database file.

  • -x list

    Use only the listed communication links. For example,
    -x tcpip,ipx
    allows only TCP/IP and IPX communications.

    A shared-memory protocol is provided for same-machine communications. It is always made available for both network server and personal server.

    The default set of communications links is to try all settings that are supported by the database server that you are running on your operating system. The list is a comma-separated list of settings taken from the following list:

    • ALL

      Start all communications links that are supported on this platform by the server that you are starting. This is the default.

    • DDE

      Supported by Windows 95, for same-machine communication from Windows 3.x applications.

    • IPX

      Supported by NetWare, Windows NT, and Windows 95 network servers.

    • NamedPipes

      Supported on Windows NT and OS/2, for same-machine communication from Windows 3.x applications.

    • NetBIOS

      Supported by Windows NT, Windows 95, OS/2 network database servers.

    • NONE

      Do not start any communications links except for the shared memory link.

    • TCPIP

      Supported by the network server on all operating systems. This communications link is also supported by the personal database server for same-machine communications.

      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;...}"

      For a description of available parameters, see Network communications parameters.

  • -y

    Runs the server as a Windows 95 service. If the server registered as a Windows 95 service, it continues to operate whether users log on or off, and shutdown commands are ignored.

  • -Z

    Provides diagnostic information on communications links on startup. This should only be used when tracking problems. This option only accepts upper case letter. This switch applies only to the network server.

Recovery switches

  • -a log-file

    Apply the named transaction log. This is used to recover from media failure on the database file (see Backup and Data Recovery ). When this option is specified, the database server applies the log and then terminate -- it will not continue to run.

  • -f

    This option is used for recovery: to force the database server to start after the transaction log has been lost (For more information see Backup and Data Recovery).

    If there is no transaction log, the database server carries out a checkpoint recovery of the database and then terminates -- it does not continue to run. You can then restart the database server without the -f option for normal operation.

    If there is a transaction log in the same directory as the database, the database server carries out a checkpoint recovery, and a recovery using the transaction log, and then terminates -- it does not continue to run. You can then restart the database server without the -f option for normal operation.

Database switches

  • -m

    Truncate (delete) the transaction log when a checkpoint is done, either at shutdown or as a result of a checkpoint scheduled by the server. This provides a way to automatically limit the growth of the transaction log. Checkpoint frequency is still controlled by the CHECKPOINT_TIME and RECOVERY_TIME options (which you can also define on the command line).

    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 against media failure on the device that contains 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 switch is the same as the -m server switch, but applies only to the current database or the database identified by the database-file command-line variable.

    Note:
    Replicated databases
    Do not use the -m option with databases that are being replicated. Replication inherently relies on transaction log information.

  • -n name

    Sets the name of the database. 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.

    By default, the database receives the name of the database file with the path and extension removed. For example, if the database is started on.c:\asa6 \asademo.db and no -n switch is specified, the name of the database is asademo.

  • -v

    Causes the server to record in the transaction log the previous values of each of the columns whenever a row of the specified database is updated or deleted. By default, the server only records enough information to uniquely identify the row (primary key values or values from a not null unique index). This switch is useful for working on a copy of a database file.


Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com

© 2014 Novell