Generate DDL for Changed Configuration Parameters

Jim_Knicely

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!