SAP ASE

Configuration of Sybase Database #

All the following command will be used for configuring or consulting a SAP Database D04, please replate D04 with your database SID.

Connect to database via ISQL command line tool #

d04adm$ isql -Usapsa -SD04 -X

Check Database Page Size #

This value will be used when creating dump configuration settings.

1> select @@maxpagesize
2> go

 -----------
       16384

Activate database logging #

In order to be able to recover a database with Point In Time options, yu must activate database logging.

1> exec master..sp_dboption D04, 'trunc log on chkpt', false
2> go
Database option 'trunc log on chkpt' turned OFF for database 'D04'.
Running CHECKPOINT on database 'D04' for option 'trunc log on chkpt' to take
effect.
(return status = 0)

Dumphistory File #

Every single time Sybase finishes a DUMP action, Dump History catalog is updated. This file holds the actual backup catalog for current database.

This file will be located at $SYBASE/$SYBASE_ASE/dumphist file. This file should not be edited. ex: /sybase/D04/ASE-16_0/dumphist

Enable Dumphistory File #

1> use master
2> go
1> sp_configure 'enable dump history', 1
2> go
1> sp_configure 'dump history filename', 'dumphist'
2> go

Show Dumphistory configuration #

1> sp_configure 'dump history filename'
2> go
 Parameter Name         Default    Memory Used Config Value Run Value    Unit                 Type
 --------------------- ----------- ----------- ------------ ------------ -------------------- --------------------
 dump history filename   dumphist    0           dumphist     dumphist     name                 dynamic

1> sp_configure 'enable dump history'
2> go
 Parameter Name         Default    Memory Used Config Value Run Value    Unit                 Type
 --------------------- ----------- ----------- ------------ ------------ -------------------- --------------------
 enable dump history     0           0           1            1            switch               dynamic

Show Dumphistory #

1> sp_dump_history
2> go
DUMP
----
 Dump_Type    Dbid    Database_name   Stripes  Dump_instant                 File                                               Server_name  Compression_lvl  Password  Status    Label  Dump_date
 -----------  ------  --------------  -------  ---------------------------  -------------------------------------------------  -----------  ---------------  --------  --------  -----  --------------------------
 DATABASE     4        D04            4        May 22 2020  9:41:29:070AM   emory::compression=-2::D04.DB.20200522.093515.000    *          0                no        Success   *      May 22 2020  9:41:33:096AM
 DATABASE     4        D04            4        May 22 2020  9:41:29:070AM   emory::compression=-2::D04.DB.20200522.093515.001    *          0                no        Success   *      May 22 2020  9:41:33:096AM
 DATABASE     4        D04            4        May 22 2020  9:41:29:070AM   emory::compression=-2::D04.DB.20200522.093515.002    *          0                no        Success   *      May 22 2020  9:41:33:096AM
 DATABASE     4        D04            4        May 22 2020  9:41:29:070AM   emory::compression=-2::D04.DB.20200522.093515.003    *          0                no        Success   *      May 22 2020  9:41:33:096AM
 DATABASE     4        D04            4        May 22 2020 11:28:44:076AM   emory::compression=9::D04.DB.20200522.105442.000     *          0                no        Success   *      May 22 2020 11:29:29:353AM
 DATABASE     4        D04            4        May 22 2020 11:28:44:076AM   emory::compression=9::D04.DB.20200522.105442.001     *          0                no        Success   *      May 22 2020 11:29:29:353AM
 DATABASE     4        D04            4        May 22 2020 11:28:44:076AM   emory::compression=9::D04.DB.20200522.105442.002     *          0                no        Success   *      May 22 2020 11:29:29:353AM
 DATABASE     4        D04            4        May 22 2020 11:28:44:076AM   emory::compression=9::D04.DB.20200522.105442.003     *          0                no        Success   *      May 22 2020 11:29:29:353AM
 DATABASE     4        D04            0        May 22 2020 11:30:06:163AM   dump.error                                           *          0                no        Error            May 22 2020 11:30:06:163AM
 DATABASE     4        D04            4        May 22 2020 11:34:45:076AM   emory::::D04.DB.20200522.113046.000                  *          0                no        Success   *      May 22 2020 11:34:53:513AM
 DATABASE     4        D04            4        May 22 2020 11:34:45:076AM   emory::::D04.DB.20200522.113046.001                  *          0                no        Success   *      May 22 2020 11:34:53:513AM
 DATABASE     4        D04            4        May 22 2020 11:34:45:076AM   emory::::D04.DB.20200522.113046.002                  *          0                no        Success   *      May 22 2020 11:34:53:513AM
 DATABASE     4        D04            4        May 22 2020 11:34:45:076AM   emory::::D04.DB.20200522.113046.003                  *          0                no        Success   *      May 22 2020 11:34:53:513AM

CONFIG
------

ALTER DATABASE
--------------
(return status = 0)

Purge Dumphistory file #

With this command we will be able to purge Sybase backup catalog (there is no reaseon for doing this)

1> use master
2> go
1> sp_dump_history @operation = 'purge'
2> go

Dump Profiles #

In order to make it easier for backup adminsitrator to launch backups with specific settings, it is a best practice to create dump profiles.

We can create as many Dump Profiles as we want, each one with different settings. This can be useful when we want to use, for example, one profile for Full Backups, other for Incremental Backup, another one for logs backups, and so on.

To perform these operations connect to the database using iSQL as sybsid user.

isql -Usapsa -S<SID> -X

Options to be configured:

  • config_name : The name of the current configuration. This name will be used on dump actions
  • ext_api : streamidentifier + :: + vendor_specific_information string
    • emory::[compression=#compression_value]
  • #compression_value - Compresion level to be used on dump actions
    • 0 : No Compression
    • 1 : Best Speed, worst compression level
    • 2
    • 3
    • 4
    • 5
    • 6 : [Default compression]
    • 7
    • 8
    • 9 : Best Compression, slowest
    • -1 : Default compression
    • -2 : HuffmanOnly
      HuffmanOnly disables Lempel-Ziv match searching and only performs Huffman entropy encoding. This mode is useful in compressing data that has already been compressed with an LZ style algorithm (e.g. Snappy or LZ4) that lacks an entropy encoder. Compression gains are achieved when certain bytes in the input stream occur more frequently than others. Note that HuffmanOnly produces a compressed output that is RFC 1951 compliant. That is, any valid DEFLATE decompressor will continue to be able to decompress this output.

      When using compression settings on dump configurations, as compression is CPU intensive process, we should not use more stripes than the number of vCPU that our server has.

      If we do not use compression settings, the recomendation is to use 2 or 3 stripes per CPU

  • [num_stripes] : Number os stripes to be used in parallel (max 32)
  • [blocksize]: Specify the block size to override the default value for all dump devices. The block size must be at least one database page and must be an exact multiple of the database page size.

Examples:

Configure dump profile to be used for Backups on system with 4 vCPUs with no compression
1> use master
2> go

1> sp_config_dump
    @config_name = 'emory_S8',
    @ext_api = 'emory::',
    @num_stripes = '8',
    @blocksize = '16384'
2> go

Configure dump profile to be used for Backups on system with 8 vCPUs with compression
1> use master
2> go

1> sp_config_dump
    @config_name = 'emory_S8_C5',
    @ext_api = 'emory::compression=5',
    @num_stripes = '8',
    @blocksize = '16384'
2> go
Configure dump profile to be used for Log backups on system with 4 vCPUs with compression
1> use master
2> go

1> sp_config_dump
    @config_name = 'emory_S4_C1',
    @ext_api = 'emory::compression=1',
    @num_stripes = '4',
    @blocksize = '16384'
2> go
List all configurations
1> use master
2> go

1> sp_config_dump
2> go
 dumpconfig
 ------------------------------------------------
 emory_S8
 emory_S8_C5
 emory_S4_C1
View specific settings
1> use master
2> go

1> sp_config_dump emory_S8_C5
2> go
 optionname          optionvalue
 -----------------   ---------------------
 ext_api             emory::compression=5
 num_stripes         8
 blocksize           65536

You can also configure this settings using SAP Control Center Application