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

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!
0