Generate DDL for Changed Configuration Parameters

Posted June 13, 2019 by Jim Knicely, Vertica Principal Solution Architect

Business card that says Expert Tips,
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/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/CONFIGURATION_PARAMETERS.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/CONFIGURATION_CHANGES.htm

Have fun!