Skip to Content
Technical Articles

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

Source

And, of course, SAP blogs are still a bit behind the times (or I need glasses) as I can’t for the life of me see how to attach a file to this blog … *arg* … so here’s a cut-n-paste of the source code for rs__helpconfig

NOTE: Oddities in the formatting (below) are related to the capabilities of this bog site; original formatting should be retained if you cut-n-paste from below into something like notepad, etc.

-- run the following in your RSSD

if exists(select	name
	  from		sysobjects
	  where		type = 'P'
	  and		name = 'rs__helpconfig')
begin
	drop proc rs__helpconfig
end
go

create proc rs__helpconfig

(@configname	varchar(40)	= NULL
,@identifier	varchar(400)	= NULL
,@orderby	varchar(100)	= 'identifier'	-- one of { 'identifier' | 'configname' }
)

/*************************************************************************************************

	Permission to use, copy, modify and distribute this code for NON-commercial purposes
	and without fee is hereby granted provided that this copyright notice appears in all copies.

	The author of this procedure shall not be liable for any damages as a result of using, 
	modifying or distributing this code.

	Mark Parsons
	Iron Horse, Inc
	Iron Horse Consulting, Inc

	--------------

	rs__helpconfig	@configname	-- will be wrapped in a pair of wildcard characters (%)
					-- optional
					-- default = '%'

			@identifier	-- will be 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'

	Beefed up version of rs_configure which adds an identifier column so we can see what
	object (ie, repserver, RSI, db/connection or (RDB) table) a config setting belongs to.

	Limitations
	===========

	- this is a display-only stored proc; it is not possible to make any configuration
		changes via this proc; for configuration changes see the 'configure 
		replication server' and 'alter connection' commands

	- if supplied, @configname/@identifier will not be run through upper()/lower() processing;
		case (in)sensitive searching will depend on the underlying database (ASE, ASA) setting

	- does not display info for the 'dsi_cmd_separator' config setting; ran into display issues
		when the config included non-printable characters

	Assumptions
	===========

	- execute() functionality exists

	History
	==========
	05/13/2004	Mark Parsons	initial design based on rs_configure + info gleaned
					from various rs_help* procs

	06/23/2012	Mark Parsons	added support for rs_tbconfig (new in RS 15.5)

	11/12/2016	Mark Parsons	added support for eRSSD, ie, recoded to work with T-SQL
					and Watcom-SQL

	05/12/2017	Mark Parsons	added support for rs_databases.connid column (new in SRS 15.7);
					adds support for alternate connections and dedicated routes

*************************************************************************************************/

as

set nocount on

declare @this_RS	varchar(400),
	@where		varchar(500),
	@cmd		varchar(16364),
	@rc		int

select	@this_RS = charvalue
from	rs_config
where	optionname = 'oserver'

select	@configname	= ltrim(rtrim(@configname)),
	@identifier	= ltrim(rtrim(@identifier)),
	@orderby	= lower(ltrim(rtrim(@orderby)))

select	@configname	= '%' + case @configname when '' then NULL else @configname end + '%',
	@identifier	= '%' + case @identifier when '' then NULL else @identifier end + '%',
	@orderby	= lower(@orderby),
	@rc		= 0

if @orderby not in ('configname','identifier')
begin
	raiserror 27340 "Invalid @orderby value; must be one of { 'identifier' | 'configname' }"
	select @rc = -1
	return @rc
end

/******************************************

	To allow this stored proc to work in various versions of RS/RSSD we create the #rs_databases
	table and then dynamically populate it from the real rs_databases table based on the existence
	of certain columns.
*/

create table #rs_databases
(dsname			varchar(30)	null
,dbname			varchar(30)	null
,connid			int		null		-- introduced with ASE 15.7
)

---------

if exists(select 1
	  from	 sysobjects o,
		 syscolumns c
	  where	 o.name = 'rs_databases'
	  and	 c.id   = o.id
	  and	 c.name = 'connid')
begin
	-- SRS 15.7+
	select	@cmd = "insert #rs_databases select distinct dsname, dbname, connid from rs_databases"
end
else
begin
	-- pre SRS 15.7
	select	@cmd = "insert #rs_databases select distinct dsname, dbname, dbid   from rs_databases"
end

execute(@cmd)

---------

create index idx2 on #rs_databases(connid)

/******************************************

	dump results into #configs ; allows for dynamically adding rs_tbconfig values, also allows
	us to compress output column widths (similar to ASE's sp_autoformat)

	The #configs.Identifier column can take on one of the following formats:

		<RS>				-- this repserver
		<RDS>.<RDB>			-- database/connection name
		<RSI>				-- route name
		<RDS>.<RDB>.<owner>.<table>	-- database/connection name plus owner/table name

**********************/

-- repserver specific settings

select	@this_RS			as Identifier,
	rc.optionname			as Config_Name,
	rc.charvalue			as Config_Value

into #configs

from	rs_config	rc,
	rs_tvalues	rt

where	rc.optionname	*= rt.name
and	rt.type		 = 'CF'
and	rc.objid	 = 0

and	rc.optionname	!= 'dsi_cmd_separator'
and	rc.optionname	like @configname

union all

-- database/connection specific settings

select	d.dsname + '.' + d.dbname	as Identifier,
	rc.optionname			as Config_Name,
	rc.charvalue			as Config_Value

from	rs_config	rc,
	rs_tvalues	rt,
	#rs_databases	d

where	rc.optionname	*= rt.name
and	rt.type		 = 'CF'
and	rc.objid	!= 0
and	d.connid	 = hextoint(bintostr(substring(rc.objid,1,4)))

and	rc.optionname	!= 'dsi_cmd_separator'
and	rc.optionname	like @configname

union all

-- rsi/route specific settings

select	s.name				as Identifier,
	rc.optionname			as Config_Name,
	substring(rc.charvalue,1,30)	as Config_Value

from	rs_config	rc,
	rs_tvalues	rt,
	rs_sites	s

where	rc.optionname	*= rt.name
and	rt.type		 = 'CF'
and	rc.objid	!= 0
and	s.id		 = hextoint(bintostr(substring(rc.objid,1,4)))

and	rc.optionname	!= 'dsi_cmd_separator'
and	rc.optionname	like @configname

-- add table specific settings if the rs_tbconfig table exists

if exists(select 1
	  from	 sysobjects
	  where	 type = 'U'
	  and	 name = 'rs_tbconfig')
begin
	 execute("insert #configs
		  select d.dsname + '.' + d.dbname + '.' + rtrim(t.objowner) + '.' + t.objname	as Identifier,
			 t.optionname								as Config_Name,
			 substring(t.charvalue,1,30)						as Config_Value
		  from	 rs_tbconfig	t,
			 #rs_databases	d
		  where	 d.connid     = t.dbid
		  and	 t.optionname like '" + @configname + "'")

end

/******************************************

	sp_autoformat is not available in ASA, and reproducing the entire proc would be overkill for
	what we want; instead, we can just hardcode the steps we need to compress the column widths

	exec sp_autoformat	'#configs',
				NULL,		-- select/display all columns
				@where,	
				@orderby
*/

select	@where		= "where Identifier like '" + @identifier + "'",
	@orderby	= "order by "+
				case	when @orderby = "identifier"
					then "Identifier, Config_Name"
					else "Config_Name, Identifier"
				end

-- obtain max column widths; these numbers (@w#) will be used to dynamically limit the display width

declare @w1	int,
	@w2	int,
	@w3	int

select	@w1 = 2 + isnull(max(char_length(Identifier)),0),
	@w2 = 2 + isnull(max(char_length(Config_Name)),0),
	@w3 = 2 + isnull(max(char_length(Config_Value)),0)
from	#configs

-- build query to display the contents of #configs

select	@cmd =	"select  convert(varchar("+convert(varchar,@w1)+"),Identifier) 	 as 'Identifier',"	+ " " +
			"convert(varchar("+convert(varchar,@w2)+"),Config_Name)  as 'Config_Name',"	+ " " +
			"convert(varchar("+convert(varchar,@w3)+"),Config_Value) as 'Config_Value'"	+ " " +
		 "from #configs"									+ " " +
		 @where											+ " " +
		 @orderby

execute (@cmd)

---------

return @rc

go

grant execute on rs__helpconfig to public
go

 

6 Comments
You must be Logged on to comment or reply to a post.
  • 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

    • I tend to write/maintain code based on what I can use at my clients.

      To date I’ve yet to come across any clients using the SQLAnywhere/ERSSD, which is primarily due to clients already having ASE (and usually other RDBMSs) in house and not wanting to take on the overhead of having to train their DBAs in yet another RDBMS.

      Converting the proc from ASE to ASA/SQLAnywhere shouldn’t be too hard; the hardest part would likely be the replacement of the call to sp_autoformat … certainly doable, but at that point I’d probably opt for porting sp_autoformat to ASA/SQLAnywhere, which in turn would likely lead to some other detours/porting …

      • Mark,

        Excellent points about the ERSSD.

        However, I think we will see a growoing tendency to use the ERSSD because of its self tuning properties, which are rather nice and because the RS install handles the initial setup.

        Yes, there is a bit of a learning curve even for those of us who have used it in the distant past, but the incentive should be good as, like I say, “SAP SQL Anywhere is Anywhere”, including mobile devices like cel phones.

        That opens a great new avenue for us doesn’t it.

        Regards,

        Jean-Pierre

    • The original attachment was lost when (a few years ago) SAP rebuilt their community websites; and for some reason SAP has decided that attachments will no longer be supported for blogs; hopefully there should be no problems doing a cut-n-paste of the source code from the updated (as of 13 Dec 2019) blog.