Sybase 12.5 Server
(all platforms)
If you are installing LeasePak for the first time, follow
the procedures detailed in LeasePak Server Preparation and
Installation before proceeding with
this document. If you are upgrading or updating your LeasePak installation, follow the procedures in
LeasePak Server Upgrade and Conversion.
Use the blue Print button at the top of the page to print this document if needed as a reference
while preparing the DBMS server.
Procedure Overview
- Review system requirements:
Refer to the document System Requirements to verify that your server meets the minimum requirements for running the DBMS and LeasePak server software.
- Install the Sybase server software:
Installation of the DBMS server software is beyond the scope of the System Administration Guide. Refer to your Sybase documentation for installation procedures.
LeasePak requires a 'server page size' of 2K. Do not use a larger page size than 2K.
Required Volumes/Partitions
The devices listed below must be stored on unformatted (raw) volumes/partitions. Cylinder 0 of the disk contains the partition map. When preparing a raw partition for use with a Sybase device, do not use cylinder 0. To make sure you are not using cylinder 0, either format the disk so that disk slice 0 does not start on cylinder 0, or do not use disk slice 0 for Sybase devices at all.
Sybase master Device
| Examples | Your Values | |
|---|---|---|
| Type | raw partition | |
| Owner:Group | sybase:sybase | |
| Permissions | 640 | |
| Device name | master | |
| Device size | 1024 MB minimum | |
| Database name | master | |
| Database size | 256 MB minimum |
Remove Master Device from Pool of Default Devices
To remove the master device from the pool of default devices, from isql execute the following:
- sp_diskdefault master, defaultoff
- go
Remove Master Device from tempdb Database Segments
To remove the master device from the tempdb database segments, refer to your Sybase documentation or contact your MSI representative for more information.
Sybase sysprocsdev Device
| Examples | Your Values | |
|---|---|---|
| Type | raw partition | |
| Owner:Group | sybase:sybase | |
| Permissions | 640 | |
| Device name | sysprocsdev | |
| Device size | 1024 MB minimum | |
| Database name | sybsystemprocs | |
| Database size | 512 MB |
Sybase tempdb Database
| Examples | Your Values | |
|---|---|---|
| Type | raw partition | |
| Owner:Group | sybase:sybase | |
| Permissions | 640 | |
| Device name | tempdb | |
| Device size | 2048 MB minimum | |
| Database name | tempdb | |
| Database size | 1024 MB minimum |
LeasePak Data Devices(s)
| Examples | Your Values | |
|---|---|---|
| Type | raw partition | |
| Owner:Group | sybase:sybase | |
| Permissions | 640 | |
| Device name | msi_data01 | |
| Device size | 2048 MB minimum | |
| Database name | lpr_production | |
| Database size | 2048 MB minimum |
Do not store LeasePak data and LeasePak data transaction log devices on the same physical disk.
LeasePak Data Transaction Log Device(s)
| Examples | Your Values | |
|---|---|---|
| Type | raw partition | |
| Owner:Group | sybase:sybase | |
| Device name | msi_log01 | |
| Device size | 1024 MB minimum |
Do not store LeasePak data and LeasePak data transaction log devices on the same physical disk.
Additional Requirements
HP: Additional Requirements
Kernel Parameters
shmmax
Configure this either through SAM or by way of direct commands. After setting the value, reboot the system to rebuild the kernel.
| Description | Maximum Value | Your Value |
|---|---|---|
| shared memory maximum | the lesser of (1) the amount of lockable memory available, as displayed with the command
dmesg or (2) 1792 MB. You can set shmmax lower than the maximum,
depending on your DBMS processing needs. The shared memory maximum must be at least as large as the Adaptive Server 'total logical memory' configuration parameter plus the amount of shared memory required by non-DBMS components such as the LeasePak Queue Manager. |
Using SAM to configure shmmax enforces the 1792 MB limit. You can set
shmmax higher by using direct commands to configure the parameter and rebuild the
kernel.
Asynchronous I/O Device
Sybase requires an asynchronous I/O device. Configure the device using the following steps:
- Add the "Asynchronous Disk Pseudo Driver" to the kernel, either through SAM (Kernel Configuration, Drivers) or manually by way of direct commands.
- Create the asynchronous I/O device:
- execute the command
mknod /dev/async c 101 4 - change the owner:group to
sybase:sybase - change the permissions to
660
- execute the command
- set the DBMS server parameter
allow sql server async i/oto1(on).
Linux: Additional Requirements
Kernel Parameters
The value 526551040 is an example only.
Configure these parameters using the lesser of either your total physical memory or 4 GB. The shared memory maximum must be at least as large as the Adaptive Server 'total logical memory' configuration parameter plus the amount of shared memory required by non-DBMS components such as the LeasePak Queue Manager.
shmmax
To change this parameter without rebooting the system, use one of the following commands:
- sysctl -w kernel.shmmax=526551040
- or
- echo "526551040" > /proc/sys/kernel/shmmax
To load this value into the kernel after each boot, use the following command to add it to the file
/etc/sysctl.conf (otherwise the value will be lost on reboot):
- echo "kernel.shmmax=526551040" >> /etc/sysctl.conf
shmall
To change this parameter without rebooting the system, log in as root,
then use one of the following commands:
- sysctl -w kernel.shmall=526551040
- or
- echo "526551040" > /proc/sys/kernel/shmall
To load this value into the kernel after each boot, log in as root,
then use the following command to add it to the file /etc/sysctl.conf
(otherwise the value will be lost on reboot):
- echo "kernel.shmall=526551040" >> /etc/sysctl.conf
Sun: Additional Requirements
Kernel Parameters
shmsys:shminfo_shmmax
| Description | Maximum Value | Your Value |
|---|---|---|
| shared memory maximum | Set either at maximum physical memory or lower, depending on your DBMS processing needs. As a guide, you can use the following sliding scale: 70% of 2 GB total physical memory to 50% of 4 GB total physical memory. The shared memory maximum must be at least as large as the Adaptive Server 'total logical memory' configuration parameter plus the amount of shared memory required by non-DBMS components such as the LeasePak Queue Manager. |
DBMS Server Preparation
Version Verification
Verify you are running the correct dataserver for your platform. Either query the @@version
variable within isql or use the showserver command from the
/opt/sybase/ASE-12_5/install directory.
The version must be 12.5.4.
Disk Devices
You must initialize the raw partitions set up for the LeasePak Sybase database(s) and transaction log(s). Use the
Sybase command disk init to make Sybase disk devices from the raw partitions. For more
information on the disk init command, refer to your Sybase documentation or contact your MSI
representative.
To avoid problems recovering data from
a server crash, create and initialize separate partitions for the data and transaction log segments of the LeasePak
database. Do not place either segment on the partition used by the Sybase system databases such as
master, sybsystemprocs, or tempdb.
Sybase Parameters
Parameters listed below fall into one of 4 categories: those where LeasePak requires the listed value, those where the listed value is a minimum, those where the listed value is a maximum, and those listing an optimal value. Enter the required values exactly as listed. For those listing minimum, maximum, or optimal values, you can adjust them as needed (within any specified minimum/maximum bounds) to achieve peak DBMS server performance.
Sybase parameters not listed here:
for LeasePak to function correctly, leave all other Sybase parameters at their default values. In particular,
leave the parameter 'max parallel degree' set to 1. In most cases this is the Sybase
default, but check the parameter value to be sure. For more information, contact your MSI representative.
'additional network memory'
| Description | Minimum Value | Your Value |
|---|---|---|
| memory allotted for network activity | 'max network packet size' * number of users * number open connections * 1.2, where number open connections = 2 + number of simultaneously open windows (updates, reports, etc.) in an average user's LeasePak session, and 1.2 allows for 20% overhead. |
'allow sql server async i/o'
| Description | Required Value | Your Value |
|---|---|---|
| Switch for whether or not to use asynchronous I/O | 1 (use asynchronous I/O) | 1 |
'default data cache'
| Description | Optimal Value | Your Value |
|---|---|---|
| Size of the data cache in megabytes (MB) | Depends on the size and activity of your database(s) and the amount of available memory. There is a point of diminishing returns beyond which increasing the size of the data cache consumes extra resources without improving performance. |
To determine the number of megabytes in the default data cache, execute the following in isql:
- sp_cacheconfig 'default data cache', number_of_megabytes
- go
'max memory'
| Description | Minimum Value | Your Value |
|---|---|---|
| Maximum amount of shared memory that Sybase can allocate for its own use. | Value of the 'total logical memory' read-only configuration parameter. |
To calculate your value for 'max memory', first use the formula from 'total logical memory' to determine the value of 'total logical memory' in megabytes (MB). This is the minimum number_of_megabytes value for the following equation:
number_of_megabytes * 512 = value_of_max_memory
You may use a larger value for number_of_megabytes as needed. Once you have determined the value_of_max_memory, execute the following in isql:
- sp_configure 'max memory', value_of_max_memory
- go
Also see 'total logical memory'.
'max network packet size'
| Description | Minimum Value | Your Value |
|---|---|---|
| Maximum packet size for information passing between LeasePak client and DBMS server | 4096 bytes |
'max online engines'
| Description | Minimum Value | Your Value |
|---|---|---|
| Maximum number of engines that can be online at any one time | 1 |
Notes
- The number of engines should not exceed the number of CPUs on the server; in most cases, set the parameter to a value less than the number of CPUs, so that some CPUs are free to run non-Sybase processes.
- Configuring a large number of engines may cause a decrease in data cache performance due to cache spinlock contention among the engines. If the spinlock contention exceeds 10 percent, configure the 'global cache partition number' parameter to reduce cache spinlock contention.
'number of devices'
| Description | Minimum Value | Your Value |
|---|---|---|
| Maximum number of available devices | 50 |
'number of locks'
| Description | Minimum Value | Your Value |
|---|---|---|
| Maximum number of available locks within Sybase | 5000 (Sybase default) |
'number of engines at startup'
| Description | Minimum Value | Your Value |
|---|---|---|
| Number of engines brought online at startup | 1 |
Also see 'max online engines'.
'number of open databases'
| Description | Minimum Value | Your Value |
|---|---|---|
Maximum number of concurrent open databases (these include Sybase system databases such as
master, sybsystemprocs, and tempdb) |
25 |
'number of open indexes'
| Description | Minimum Value | Your Value |
|---|---|---|
| Maximum number of indexes that can be open simultaneously | 2000 |
'number of open objects'
| Description | Minimum Value | Your Value |
|---|---|---|
| Maximum number of objects that can be open simultaneously | 4000 |
'number of user connections'
| Description | Minimum Value | Your Value |
|---|---|---|
| Maximum number of simultaneous connections to the DBMS server | M * (G + 2), where M is maximum concurrent users and G is the average number of Genform windows concurrently open for each user. |
'procedure cache size'
| Description | Optimal Value | Your Value |
|---|---|---|
| Size of the procedure cache | Approximately 150 MB. You can increase or decrease this amount slightly as needed, but MSI recommends keeping it close to 150 MB. |
To determine the procedure cache size, use this equation:
number_of_megabytes * 512 = size_of_procedure_cache
To configure the parameter, execute the following in isql:
- sp_configure 'procedure cache size', size_of_procedure_cache
- go
To configure the procedure cache for 150 MB:
150 * 512 = 76800
- sp_configure 'procedure cache size', 76800
- go
'stack size'
| Description | Minimum Value | Your Value |
|---|---|---|
| Maximum size of the dataserver's execution and argument stack size | 88064 |
'total logical memory'
This is a read-only parameter, meaning you cannot make changes to it.
This is the amount of memory required by the current configuration of Sybase. To determine the value of 'total logical memory' in megabytes (MB), execute the following in isql:
- sp_configure 'total logical memory'
- go
Taking the number from the 'Run Value' column (not the 'Memory Used' column), divide it by 512 to obtain the value in megabytes (MB):
Number from 'Run Value' / 512 = Value of 'total logical memory' in MB
Database Owner
LeasePak now requires that you specify a database owner (dbo) for each LeasePak database, separate from the DBMS
administrative user (srvadm or sa). During installation, the LeasePak setup program will ask if you want to
automatically create database owners. If you answer 'Y', then LeasePak will automatically create a database owner name when needed. This
name will match the database name. LeasePak will prompt you to supply a new password for the database owner. If you answer 'N', you will
need to create dbo names and passwords for each new or converted LeasePak database.
For users upgrading from a version before 5.1a, the conversion program sgenlpux_conversion will do one of the following
for the database owner:
- If the
dbois notsrvadmorsa(you assigned a separatedboname to the database), the program does nothing; that is, it leaves the existingdboin place. You will not be prompted for a newdboname or password. - If the
dbois eithersrvadmorsa(as is the Sybase default and common to LeasePak databases from version 5.0a and before), the program requires that you supply a newdboname and password. If you have selected to automatically create database owners, the program will create adboname that matches the database name and prompt you for the new password. If you have selected not to automatically create database owners, the program will prompt you for both the name and new password.
Maintenance
For general maintenance of the LeasePak server, refer to the Maintenance and Troubleshooting section of LeasePak Server Configuration and Maintenance for more information.
Backups
Prevent data changes during a Sybase backup by
setting the sp_dboption "dbo use only" to true.
LeasePak db_snapshot and db_restore
You must use the LeasePak server scripts db_snapshot and db_restore to properly
back up LeasePak databases. Refer to the Backups section of
LeasePak Server Configuration and Maintenance for more information.
Database Dump
In addition to using snapshot and restore, periodically back up all
Sybase databases, including the system databases such as master, sybsystemprocs,
and tempdb, by using the Sybase dump utility. You must log on
isql as sa or other user with sa privileges in order to
use dump, and the backup server must be running. Refer to your Sybase documentation for more information.
To dump a database, type
$ isql -Usa
Password: sa_password
1> dump database database
2> to "device"
3> capacity = bytes
4> with init, unload, dismount
5> go
where
sa_password is your sa password
database is the name of your database (not the LeasePak environment name)
device is the full path of your disk or non-rewinding tape device (note the path is in quotation marks)
bytes for tape devices, the capacity in bytes (a 2 GB tape device is 2000000 bytes).
The terminal will display something similar to the following
Backup Server session id is: 19. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server. Backup Server: 6.58.1.1: Device '/dev/rmt/0mn' supports multiple dump files per volume set. Backup Server: 6.28.1.1: Dumpfile name 'duction961270ED0F' section number 0001 mounted on tape drive '/dev/rmt/0mn' Backup Server: 4.58.1.1: Database lpr_production: 616 kilobytes DUMPed. Backup Server: 4.58.1.1: Database lpr_production: 1266 kilobytes DUMPed. Backup Server: 4.58.1.1: Database lpr_production: 1784 kilobytes DUMPed. Backup Server: 4.58.1.1: Database lpr_production: 2354 kilobytes DUMPed. Backup Server: 4.58.1.1: Database lpr_production: 3016 kilobytes DUMPed. Backup Server: 4.58.1.1: Database lpr_production: 10786 kilobytes DUMPed. Backup Server: 4.58.1.1: Database lpr_production: 31280 kilobytes DUMPed. Backup Server: 4.58.1.1: Database lpr_production: 31682 kilobytes DUMPed. Backup Server: 3.43.1.1: Dump phase number 1 completed. Backup Server: 3.43.1.1: Dump phase number 2 completed. Backup Server: 3.43.1.1: Dump phase number 3 completed. Backup Server: 4.58.1.1: Database lpr_production: 31690 kilobytes DUMPed. Backup Server: 3.42.1.1: DUMP is complete (database lpr_production).
If you automate Sybase dumps in
cron, protect the sa password from unauthorized users.
To restore a database from a dump, you must first, if needed, recreate it with the LeasePak script
db_create. Verify that the Sybase backup server is running, then type
$ isql -Usa
Password: sa_password
1> load database database
2> from "device"
3> with unload, dismount
4> go
where
sa_password is your sa password
database is the name of your database (not the LeasePak environment name)
device is the full path of your disk or non-rewinding tape device (note the path is in quotation marks).
The terminal will display something similar to the following
Backup Server session id is: 12. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server. Backup Server: 6.28.1.1: Dumpfile name >duction961270ED0F= section number 0001 mounted on tape drive '/dev/rmt/0mn' Backup Server: 4.58.1.1: Database lpr_production: 1592 kilobytes LOADed. Backup Server: 4.58.1.1: Database lpr_production: 3194 kilobytes LOADed. Backup Server: 4.58.1.1: Database lpr_production: 4712 kilobytes LOADed. Backup Server: 4.58.1.1: Database lpr_production: 6112 kilobytes LOADed. Backup Server: 4.58.1.1: Database lpr_production: 7580 kilobytes LOADed. Backup Server: 4.58.1.1: Database lpr_production: 8994 kilobytes LOADed. Backup Server: 4.58.1.1: Database lpr_production: 33808 kilobytes LOADed. Backup Server: 4.58.1.1: Database lpr_production: 76308 kilobytes LOADed. Backup Server: 4.58.1.1: Database lpr_production: 119828 kilobytes LOADed. Backup Server: 4.58.1.1: Database lpr_production: 122902 kilobytes LOADed. Backup Server: 4.58.1.1: Database lpr_production: 122910 kilobytes LOADed. Backup Server: 3.42.1.1: LOAD is complete (database lpr_production). Use the ONLINE DATABASE command to bring this database online; SQL Server will not bring it online automatically.
To bring the database online, type
1> online database database
2> go
where
database is the name of your database (not the LeasePak environment name)
The terminal will display something similar to
Database 'lpr_production' is now online.
Transaction Logs
The LeasePak script db_create
automatically sets trunc log on chkpt to true. For production databases, you must
set trunc log on chkpt to false in order to properly back up transaction logs.
Back up transaction logs frequently to prevent a loss of data changes made between full backups. You can use the Sybase
dump utility to do this, either manually or automatically within cron. You can dump
transaction logs while users are connected to the DBMS.
Use a sp_thresholdaction procedure to dump a transaction log when it reaches a certain size. Refer to your
Sybase documentation or contact your MSI representative for more information.
When you dump transaction logs to more than one tape, you must reload them in the exact same order that you dumped them. Refer to your Sybase documentation for more information.
Database Consistency Checker
Run the Sybase Database Consistency Checker utilities dbcc checkdb and
dbcc checkalloc on your database(s) at least once a week. This will identify database problems
before they are propagated into your backups. The checkdb locks and unlocks database tables as it checks them,
and the checkalloc option performs a considerable amount of I/O, so either schedule the procedures for
off-hours or run them on a copy of the database(s). Refer to your Sybase documentation or contact your MSI representative for more
information.
LeasePak Documentation Suite
©
by McCue Systems Incorporated. All rights reserved.
The information contained in this document is the property of McCue Systems, Inc. Use of the information contained herein is restricted. Conditions of use are subject to change without notice. McCue Systems, Inc. assumes no liability for any inaccuracy that may appear in this document; the contents of this document do not constitute a promise or warranty. The software described in this document is furnished under license and may be used or copied only in accordance with the terms of said license. Unauthorized use, alteration, or reproduction of this document without the written consent of McCue Systems, Inc. is prohibited.