Skip to Content
Technical Articles
Author's profile photo Mark A Parsons

CODE: rs__helpconfig : RSSD procedure for displaying configuration settings

What is this

Beefed up version of the old rs_configure stored proc; expands the Identifier column to display RSI, DSI and table-level names.

The current version of the stored proc (included at the bottom of this blog post) has been designed to support SRS versions 12.6 through 16.0, as well as both types of RSSDs (ASE and SQLAnywhere). If you find an issue then post a comment with the issue and/or any fixes you may find necessary to run in your environment.

Limitations

The stored proc is limited to displaying those config settings that have an entry in the (RSSD) rs_config table.

For newer versions of SRS this means that some (undocumented) configs displayed by the (SRS) admin config command (eg, savepoint_replication), or default config settings that have not been explicitly modified by the DBA (eg, dsi_keep_triggers) will not be displayed by this stored proc.

On the other other hand, some configs that have been explicitly set by the DBA (eg, DSI/table level settings for dsi_command_convert) will not be displayed by the (SRS) admin config command but will be displayed by rs__helpconfig.

A more complete/thorough listing is possible but would require a more extensive programming approach (eg, java, perl, shell script, etc).

Usage

NOTE: For those familiar with my previous version of this stored proc, I’ve switched the first 2 input parameters of the stored proc; over time I found most of my queries were for specific configurations (as opposed to identifiers) so @configname is now the first input parameter.

rs__helpconfig
     @configname, -- input is wrapped in a pair of wildcard characters (%)
                  -- optional
                  -- default = '%'

     @identifier, -- input is wrapped in pair of wildcard characters (%)
                  -- optional
                  -- default = '%'

     @orderby     -- one of { 'identifier' | 'configname' }
                  -- 'identifier' => 'order by Identifier, Config_Name'
                  -- 'configname' => 'order by Config_Name, Identifier'
                  -- optional
                  -- default = 'identifier'

Examples

1 – display all config settings; use default ‘identifier‘ ordering (Identifier,Config_Name): 

rs__helpconfig
go

 Identifier                  Config_Name                      Config_Value
 --------------------------- -------------------------------- --------------------------
 ASE200.aoh_rdb_alt          dsi_command_convert              none
 ASE200.tgt1                 dsi_command_convert              none
 ASE200.tgt1..da_test_tab1   dsi_command_convert              none
 HXE.HXE                     batch                            off
 HXE.HXE                     db_packet_size                   16384
 HXE.HXE                     dsi_bulk_copy                    on
 HXE.HXE                     dsi_connector_type               odbc
 HXE.HXE                     dsi_dataserver_make              hdb
 HXE.HXE                     dsi_proc_as_rpc                  on
 HXE.HXE                     dsi_quoted_identifier            on
 HXE.HXE                     dsi_serialization_method         wait_after_commit
 HXE.HXE                     dynamic_sql                      on
 RS200                       CONFIG_OPTION_RS1571SP202_S      1
 RS200                       RS1571SP200_REPDEF_UPDATED       1
 RS200                       RS1571SP202_FUNCSTR_UPDATED      1
 RS200                       RS1571SP204_DDLDTMAP_UPDATED     1
... snip ...
 RS200                       unused_login_expiration          0
 RS200                       use_security_services            off
 RS200                       use_ssl                          off
 RS200                       varchar_truncation               off
 RS200                       ws_sqldml_replication            off

2 – OK, not bad, but how about ordering by config name and then identifier:

rs__helpconfig @orderby='configname'
go

 Identifier                  Config_Name                      Config_Value
 --------------------------- -------------------------------- --------------------------
 RS200                       CONFIG_OPTION_RS1571SP202_S      1
 RS200                       RS1571SP200_REPDEF_UPDATED       1
 RS200                       RS1571SP202_FUNCSTR_UPDATED      1
 RS200                       RS1571SP204_DDLDTMAP_UPDATED     1
 RS200                       RS1571SP204_HIVE_UPDATED         1
 RS200                       RSEBF1571302_VERIFYDDL_RSSD_UP   1
 RS200                       audit_dest                       log
 RS200                       audit_enable                     off
 HXE.HXE                     batch                            off
 RS200                       batch                            on
 RS200                       block_size                       16
 RS200                       block_sub_for_repdef_in_pub      off
 RS200                       byte_order                       65536
 RS200                       cap_prs_num_threads              2
... snip ...
 RS200                       sts_full_cache_rs_whereclauses   off
 RS200                       sub_daemon_sleep_time            120
 RS200                       sub_sqm_write_request_limit      1048576
 RS200                       trace
 RS200                       truncation_mode                  capture
 RS200                       unicode_format                   string
 RS200                       unified_login                    not_required
 RS200                       unused_login_expiration          0
 RS200                       use_security_services            off
 RS200                       use_ssl                          off
 RS200                       varchar_truncation               off
 RS200                       ws_sqldml_replication            off

3 – Hmmmm, isn’t there a configuration setting for autocorrection … I seem to recall it had the word ‘command‘ in the name:

rs__helpconfig 'command'
go

 Identifier                  Config_Name           Config_Value
 --------------------------- --------------------- ------------
 ASE200.aoh_rdb_alt          dsi_command_convert   none
 ASE200.tgt1                 dsi_command_convert   none
 ASE200.tgt1..da_test_tab1   dsi_command_convert   none
 RS200                       command_retry         3
 RS200                       dsi_command_convert   none

4 – Ah, yes, dsi_command_convert; but I’d like to order the output by config name and then identifier:

rs__helpconfig 'command',null,'configname'
go

 Identifier                  Config_Name           Config_Value
 --------------------------- --------------------- ------------
 RS200                       command_retry         3
 ASE200.aoh_rdb_alt          dsi_command_convert   none
 ASE200.tgt1                 dsi_command_convert   none
 ASE200.tgt1..da_test_tab1   dsi_command_convert   none
 RS200                       dsi_command_convert   none

5 – How about all of the configs associated with the ASE200 dataserver:

rs__helpconfig null,'ASE200'
go

 Identifier                  Config_Name                      Config_Value
 --------------------------- -------------------------------- --------------------------
 ASE200.aoh_rdb_alt          dsi_command_convert              none
 ASE200.tgt1                 dsi_command_convert              none
 ASE200.tgt1..da_test_tab1   dsi_command_convert              none

 

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      very helpful.thanks.

      Author's profile photo Terry Penna
      Terry Penna

      Hi Mark

      Wonder if you have ever tested this proc or have created a different proc for an ERSSD?

      ERSSD are being used more and more these days and I was just wondering if you had such a script.

      This script is very helpfull.

      Thanks

      Terry

      Author's profile photo Trugal Fatania
      Trugal Fatania

      I am new here so I need to know where can I download this script.