Script to Extract Resource Pool Definition in Vertica
The Script will Extract Resource Pool Definitions in Vertica.
This script will only recreate the pool that are created by the users and not the internal pools. If you want to get the definition of the internal pool you will need to WHERE NOT is_internal line from the script.
SELECT 'CREATE RESOURCE POOL ' || name || CASE
WHEN memorysize IS NULL THEN
' '
ELSE
' MEMORYSIZE ' || '''' || memorysize || ''''
END || CASE
WHEN maxmemorysize = '' THEN
' '
ELSE
' MAXMEMORYSIZE ' || '''' || maxmemorysize || ''''
END || CASE
WHEN executionparallelism = 'AUTO' THEN
' '
ELSE
' EXECUTIONPARALLELISM ' || '''' || executionparallelism || ''''
END || CASE
WHEN NULLIFZERO(priority) IS NULL THEN
' '
ELSE
' PRIORITY ' || '''' || priority || ''''
END || CASE
WHEN runtimepriority IS NULL THEN
' '
ELSE
' RUNTIMEPRIORITY ' || runtimepriority
END || CASE
WHEN runtimeprioritythreshold IS NULL THEN
' '
ELSE
' RUNTIMEPRIORITYTHRESHOLD ' || runtimeprioritythreshold
END || CASE
WHEN queuetimeout IS NULL THEN
' '
ELSE
' QUEUETIMEOUT ' || queuetimeout
END || CASE
WHEN maxconcurrency IS NULL THEN
' '
ELSE
' MAXCONCURRENCY ' || maxconcurrency
END || CASE
WHEN runtimecap IS NULL THEN
' '
ELSE
' RUNTIMECAP ' || '''' || runtimecap || ''''
END || ' ; '
FROM v_catalog.resource_pools
WHERE NOT is_internal
ORDER BY name;CREATE RESOURCE POOL dbd
MEMORYSIZE '0%'
RUNTIMEPRIORITY MEDIUM
RUNTIMEPRIORITYTHRESHOLD 0
QUEUETIMEOUT 0 ;
