From 8db79c5a36c87a2dae0842ef6d30c86d44eac7ea Mon Sep 17 00:00:00 2001
From: Andreas Gerler <baron@bundesbrandschatzamt.de>
Date: Fri, 16 Dec 2016 12:01:16 +0100
Subject: [PATCH] more sql yasnippets for vertica

- out of my latest incident analyses came a bunch of new queries.

Signed-off-by: Andreas Gerler <baron@bundesbrandschatzamt.de>
---
 snippets/sql-interactive-mode/errors          |  5 ++++
 snippets/sql-interactive-mode/get-partitions  |  5 ++++
 snippets/sql-interactive-mode/get-projections |  5 ++++
 .../sql-interactive-mode/rebalance_status     | 24 +++++++++++++++++++
 .../sql-interactive-mode/resource-pool-move   |  5 ++++
 snippets/sql-interactive-mode/sessions        |  5 ++++
 .../sql-interactive-mode/storage-containers   | 17 +++++++++++++
 snippets/sql-interactive-mode/user_sessions   |  5 ++++
 8 files changed, 71 insertions(+)
 create mode 100644 snippets/sql-interactive-mode/errors
 create mode 100644 snippets/sql-interactive-mode/get-partitions
 create mode 100644 snippets/sql-interactive-mode/get-projections
 create mode 100644 snippets/sql-interactive-mode/rebalance_status
 create mode 100644 snippets/sql-interactive-mode/resource-pool-move
 create mode 100644 snippets/sql-interactive-mode/sessions
 create mode 100644 snippets/sql-interactive-mode/storage-containers
 create mode 100644 snippets/sql-interactive-mode/user_sessions

diff --git a/snippets/sql-interactive-mode/errors b/snippets/sql-interactive-mode/errors
new file mode 100644
index 00000000..f5044323
--- /dev/null
+++ b/snippets/sql-interactive-mode/errors
@@ -0,0 +1,5 @@
+# -*- mode: snippet -*-
+# name: errors
+# key: errors_
+# --
+SELECT DISTINCT error_level, message FROM error_messages $0WHERE message ILIKE 'Vertica suggests%';
diff --git a/snippets/sql-interactive-mode/get-partitions b/snippets/sql-interactive-mode/get-partitions
new file mode 100644
index 00000000..99230b71
--- /dev/null
+++ b/snippets/sql-interactive-mode/get-partitions
@@ -0,0 +1,5 @@
+# -*- mode: snippet -*-
+# name: get-partitions
+# key: get-part
+# --
+SELECT * FROM v_monitor.partitions WHERE table_schema = '$1' AND projection_name LIKE '%$2%';$0
\ No newline at end of file
diff --git a/snippets/sql-interactive-mode/get-projections b/snippets/sql-interactive-mode/get-projections
new file mode 100644
index 00000000..1dc0ff92
--- /dev/null
+++ b/snippets/sql-interactive-mode/get-projections
@@ -0,0 +1,5 @@
+# -*- mode: snippet -*-
+# name: get-projections
+# key: get-proj
+# --
+SELECT GET_PROJECTIONS('$1');$0
\ No newline at end of file
diff --git a/snippets/sql-interactive-mode/rebalance_status b/snippets/sql-interactive-mode/rebalance_status
new file mode 100644
index 00000000..acee6924
--- /dev/null
+++ b/snippets/sql-interactive-mode/rebalance_status
@@ -0,0 +1,24 @@
+# -*- mode: snippet -*-
+# name: rebalance_status
+# key: rebalance_status_
+# --
+SELECT rebalance_method rebalance_method,
+       status,
+       count(*) AS COUNT
+FROM
+  (SELECT rebalance_method,
+          CASE
+	      WHEN (separated_percent = 100
+                    AND transferred_percent = 100) THEN 'Completed'
+	      WHEN (separated_percent <> 0
+                    AND separated_percent <> 100)
+                   OR (transferred_percent <> 0
+		       AND transferred_percent <> 100) THEN 'In Progress'
+	      ELSE 'Queued'
+          END AS status
+   FROM rebalance_projection_status
+   WHERE is_latest) AS tab
+GROUP BY 1,
+         2
+ORDER BY 1,
+         2;$0
diff --git a/snippets/sql-interactive-mode/resource-pool-move b/snippets/sql-interactive-mode/resource-pool-move
new file mode 100644
index 00000000..918bf43b
--- /dev/null
+++ b/snippets/sql-interactive-mode/resource-pool-move
@@ -0,0 +1,5 @@
+# -*- mode: snippet -*-
+# name: resource-pool-move
+# key: rpmv
+# --
+SELECT * FROM resource_pool_move ORDER BY move_timestamp DESC LIMIT 40;$0
\ No newline at end of file
diff --git a/snippets/sql-interactive-mode/sessions b/snippets/sql-interactive-mode/sessions
new file mode 100644
index 00000000..8f17fe7e
--- /dev/null
+++ b/snippets/sql-interactive-mode/sessions
@@ -0,0 +1,5 @@
+# -*- mode: snippet -*-
+# name: sessions
+# key: sess_
+# --
+SELECT * FROM sessions;$0
\ No newline at end of file
diff --git a/snippets/sql-interactive-mode/storage-containers b/snippets/sql-interactive-mode/storage-containers
new file mode 100644
index 00000000..ee82f79c
--- /dev/null
+++ b/snippets/sql-interactive-mode/storage-containers
@@ -0,0 +1,17 @@
+# -*- mode: snippet -*-
+# name: storage-containers
+# key: storage-containers
+# --
+SELECT s.node_name,
+       p.table_schema,
+       s.projection_name,
+       count(DISTINCT s.storage_oid) storage_container_count,
+       count(DISTINCT partition_key) partition_count,
+       count(r.rosid) ros_file_count
+FROM storage_containers s
+LEFT OUTER JOIN partitions p ON s.storage_oid = p.ros_id
+JOIN vs_ros r ON r.delid = s.storage_oid
+GROUP BY 1,
+         2,
+         3
+ORDER BY 4 DESC LIMIT 50;$0
\ No newline at end of file
diff --git a/snippets/sql-interactive-mode/user_sessions b/snippets/sql-interactive-mode/user_sessions
new file mode 100644
index 00000000..c924efc5
--- /dev/null
+++ b/snippets/sql-interactive-mode/user_sessions
@@ -0,0 +1,5 @@
+# -*- mode: snippet -*-
+# name: user_sessions
+# key: ussess_
+# --
+SELECT session_id, transaction_start FROM sessions WHERE user_name = '$1' ORDER BY 2 desc;$0
\ No newline at end of file
-- 
2.47.3