Generate DDL for Changed Configuration Parameters
Jim_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!
0