(all platforms)
- Procedure Overview
- Required Volumes/Partitions
- Additional Requirements
- HP
- Linux
- Sun
- DBMS Preparation
- Version Verification
- Disk Devices
- Sybase Parameters
- Database Owner
- Maintenance and Troubleshooting
- Backups
- Database Consistency Checker
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.
Perform the required OS configurations.
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.
Prepare the Sybase server for LeasePak.
Perform DBMS server maintenance as needed.
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.
| 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 |
To remove the master device from the pool of default devices, from isql execute the following:
sp_diskdefault master, defaultoff
go
To remove the master device from the tempdb database segments, refer to your Sybase documentation or contact your NetSol representative for more information.
| 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 |
| 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 |
| 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 |
| Examples | Your Values | |
| Type | raw partition | |
| Owner:Group | sybase:sybase | |
| Device name | msi_log01 | |
| Device size | 1024 MB minimum |
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
set the DBMS server parameter allow sql server async i/o to 1 (on).
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
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. |
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.
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 NetSol 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.
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 NetSol 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'
| 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 NetSol 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
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 dbo is not srvadm or sa (you assigned a separate dbo name to the database), the program does nothing; that is, it leaves the existing dbo in place. You will not be prompted for a new dbo name or password.
If the dbo is either srvadm or sa (as is the Sybase default and common to LeasePak databases from version 5.0a and before), the program requires that you supply a new dbo name and password. If you have selected to automatically create database owners, the program will create a dbo name 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.
For general maintenance of the LeasePak server, refer to the Maintenance and Troubleshooting section of LeasePak Server Configuration and Maintenance for more information.
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.
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.
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 NetSol 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.
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 NetSol representative for more information.