Querying ch

SAP ASE includes the ch_events view as part of the sybsecurity database.

ch_events presents configuration change history data in an easy to read format. You can query ch_events directly, or use the sp_confighistory system procedure to generate reports on configuration changes history. Either method provides the same information.

Using the select command provides the flexibility of the Transact-SQL™ language to qualify your result set (you must first move to the sybsecurity database before selecting from the ch_events view). sp_confighistory provides a more streamlined result set.

For example, if you make these configuration changes in SAP ASE:

sp_dboption sybsystemprocs, "delayed commit", false
sp_cacheconfig pub_cache, '10M'
sp_cacheconfig pub_log_cache, '2000K', logonly

Then shut down and restart the server, sp_confighistory returns:

sp_confighistory

 area     type            target         element             oldvalue
         newvalue    mode timestamp           username instanceid
 -------- --------------- -------------- ------------------- --------
         ----------- ---- ------------------- -------- ----------
 AUDIT    global auditing NULL           NULL                off
         on          NULL Aug 22 2013 11:56AM sa             NULL
 DATABASE sp_dboption     sybsystemprocs delayed commit      true
         false       NULL Aug 22 2013  3:16PM sa             NULL
 CACHE    sp_cacheconfig  pub_cache      NULL                10240
         not changed NULL Aug 22 2013  3:18PM sa             NULL
 CACHE    sp_cacheconfig  pub_log_cache  cache type: logonly 2000
         not changed NULL Aug 22 2013  3:19PM sa             NULL
 SUSD     shutdown        NULL           NULL                NULL
         NULL        NULL Aug 22 2013  3:49PM sa             NULL
 SUSD     startup         NULL           tigger              NULL
         NULL        NULL Aug 22 2013  3:50PM NULL           NULL

Include the date with sp_confighistory to select the changes over a period of time. This example shows all changes made after August 23, 2013:

sp_confighistory "Aug 23 2013"

 area type     target element oldvalue newvalue mode timestamp
         username instanceid
 ---- -------- ------ ------- -------- -------- ---- -------------------
         -------- ----------
 SUSD shutdown NULL   NULL    NULL     NULL     NULL Aug 23 2013  9:00AM
         sa             NULL
 SUSD startup  NULL   tigger  NULL     NULL     NULL Aug 23 2013 10:38AM
         NULL           NULL

Issuing select provides this result set:

use sybsecurity
go
select * from ch_events
go

area        type                 target            element              oldvalue    newvalue     mode
                   timestamp                 username        instanceid
----------  -------------------  ----------------  -----------------    ----------  ---------    --------------
                   -----------------------   -----------     ---------------
 AUDIT      global auditing      NULL              NULL                 off         on           NULL
                   Aug 22 2013 11:56AM       sa              NULL
 DATABASE   sp_dboption          sybsystemprocs    delayed commit       true        false        NULL
                   Aug 22 2013  3:16PM       sa              NULL
 CACHE      sp_cacheconfig       pub_cache         NULL                 10240       not changed  NULL
                   Aug 22 2013  3:18PM       sa              NULL
 CACHE      sp_cacheconfig       pub_log_cache     cache type: logonly  2000        not changed  NULL
                   Aug 22 2013  3:19PM       sa              NULL
 SUSD       shutdown             NULL              NULL                 NULL        NULL         NULL
                   Aug 22 2013  3:49PM       sa              NULL
 SUSD       startup              NULL              tiger                NULL        NULL         NULL
                   Aug 22 2013  3:50PM       NULL            NULL

Include the last parameter to see the last items changed:

sp_confighistory last

 area type    target element oldvalue newvalue mode timestamp
         username instanceid
 ---- ------- ------ ------- -------- -------- ---- -------------------
         -------- ----------
 SUSD startup NULL   tigger  NULL     NULL     NULL Aug 22 2013  3:50PM
         NULL           NULL

Querying ch_events