The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Generate DDL for Changed Configuration Parameters

Jim_KnicelyJim_Knicely - Select Field - Administrator

The CONFIGURATION_CHANGES system table records the change history of system configuration parameters. If you want to apply all of the changes to a different Vertica database, you can generate the necessary DDL commands to do that!

Example:

dbadmin=> SELECT DISTINCT ddl
dbadmin->   FROM (SELECT CASE
dbadmin(>                  WHEN current_level = 'DATABASE' OR (current_level = 'DEFAULT' AND INSTR(allowed_levels, 'DATABASE') > 0) THEN
dbadmin(>                    'SELECT set_config_parameter(''' || parameter || ''', ''' || value || ''');'
dbadmin(>                  WHEN current_level = 'NODE' THEN
dbadmin(>                    'ALTER NODE ' || configuration_changes.node_name || ' SET ' || parameter || ' = ''' || value || ''';'
dbadmin(>                END AS ddl
dbadmin(>           FROM configuration_changes
dbadmin(>           JOIN configuration_parameters
dbadmin(>             ON parameter = parameter_name
dbadmin(>          WHERE allowed_levels <> 'SESSION'
dbadmin(>          LIMIT 1 OVER (PARTITION BY configuration_changes.node_name, parameter ORDER BY event_timestamp DESC)) foo
dbadmin->  ORDER BY 1;
                               ddl
------------------------------------------------------------------
ALTER NODE v_test_db_node0001 SET TransactionMode = 'READ ONLY';
ALTER NODE v_test_db_node0002 SET TransactionMode = 'READ ONLY';
ALTER NODE v_test_db_node0003 SET TransactionMode = 'READ ONLY';
SELECT set_config_parameter('AWSEndpoint', '10.180.112.34');
SELECT set_config_parameter('AWSRegion', 'us-east-1');
SELECT set_config_parameter('AuditConfidenceLevel', '0');
SELECT set_config_parameter('AuditErrorTolerance', '100');
SELECT set_config_parameter('EnableForceOuter', '1');
SELECT set_config_parameter('EnableResegmentMerge', '0');
SELECT set_config_parameter('MaxParsedQuerySizeMB', '5120');
SELECT set_config_parameter('SortWorkerThreads', '5');
(11 rows)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/CONFIGURATION_PARAMETERS.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/CONFIGURATION_CHANGES.htm

Have fun!

Sign In or Register to comment.