How to configure parameters in Sybase

There are three types of paramaters in Sybase:
1. Dynamic – dynamic parameter changes take affect immediately
2. Static – static parameter changes take effect when server is restarted
3. Read-only – read-only parameters cannot be changed
Dynamic and static parameters we can change in two ways:

1. Modifying configuration file.

This file should be placed in $SYBASE_ASE folder and named by default <server_name>.cfg:

echo $SYBASE_ASE

/usr/local/sybase/ASE-15_0

In my case it’s called ASE_2k.cfg:
head -20 ASE_2k.cfg

##############################################################################
#
#               Configuration File for the Sybase SQL Server
#
#               Please read the System Administration Guide (SAG)
#               before changing any of the values in this file.
#
##############################################################################
[Configuration Options]
[General Information]
config file version = DEFAULT
[Backup/Recovery]
recovery interval in minutes = DEFAULT
print recovery information = DEFAULT
tape retention in days = DEFAULT

If we are not sure what is the name of parameter file our server is using, we can check it :
1> select value2 from master..syscurconfigs where config = 114
2> go

value2
/usr/local/sybase/ASE-15_0/ASE_2k.cfg
(1 row affected)

During startup Sybase makes backup configuration file ,and deletes old backup file if present.
Backup file name is <server_name>.bak in my case it is ASE_2k.bak.

2. Using  command: sp_configure “parameter_name”, parameter value eg:

1> sp_configure “user log cache size”, 4096
2> go

Parameter Name   Default   Memory Used  Config Value  Run Value  Unit  Type
user log cache size   2048                 0                    4096             2048           bytes    static
(1 row affected)

Configuration option changed. Since the option is static, Adaptive Server must
be rebooted in order for the change to take effect.
Changing the value of ‘user log cache size’ to ‘4096’ increases the amount of
memory ASE uses by 126 K.
(return status = 0)

We can use this command to reset parameter to it’s default value:
1> sp_configure “user log cache size”,0,”default”
2>go

Parameter Name   Default    Memory Used  Config Value  Run Value  Unit  Type
user log cache size 2048                   0                      2048              2048       bytes   static
(1 row affected)
Configuration option changed. Since the option is static, Adaptive Server must
be rebooted in order for the change to take effect.
Changing the value of ‘user log cache size’ to ‘2048’ reduces the amount of
memory ASE uses by 126 K. The reduced memory may be reused when this configure
value changes, but will not be released until ASE restarts.
(return status = 0)

When we are changing parameters by sp_configure command,
Sybase is backuping old config file and creating new ASE_2K.cfg with modifed values:
diff ASE_2k.025 ASE_2k.cfg
203c203
<       user log cache size = 4096
>       user log cache size = DEFAULT

Backuped files filenames are like: ASE_2K.<number> , number after reaching 999 it switch back to 001.
Command sp_configure can be used also to:
– display configuration paramater values
sp_configure [“parameter_name”|”group_name”]

1> sp_configure “user log cache size”
2> go

Parameter Name  Default  Memory Used  Config Value  Run Value  Unit  Type
user log cache size  2048     0        2048           2048     bytes  static
(1 row affected)
(return status = 0)

– reading and writing configuration files:
sp_configure “configuration file”, 0, {write|restore|verify|”read”}, “filename”

Where:
write – writes the current active configuration into the given file name
restore – creates a file with a given name that contains currently configured, but not necessarily active, configuration values
verify – performs validation checking on values in the file
“read” – performs the verify and reads the values that pass validation into the server. Only works if changes made within the file are dynamic

Leave a Response