Having performed hundreds of DNA engagements at primarily large customers in the world, our DNA data warehouse allows us to harvest factual knowledge regarding deployment and utilization trends inside IBM Lotus Notes & Domino environments. Once the research question properly translates into an SQL select statement, we need to apply that statement across all the customer schemas from where we wish to harvest the data.
However, each DNA engagement is unique and tailored to the specific customer. So are the corresponding tables in each customer schema. Consequently, having to select all schemas to apply our query to, is a time consuming task.
I decided to automate this process. Below I'm explaining what I did.
One way to discover which schemas we can run our queries on, is to use pg_tables:
SELECT schemaname FROM pg_tables WHERE tablename = 'user_session';
Next, I needed to craft output that in itself represents an SQL statement. (Beware to escape quotes properly):
SELECT
'
DROP TABLE IF EXISTS '|| schemaname ||'.template_utilization\;
CREATE TABLE '||schemaname||'.template_utilization AS
SELECT
\'' || schemaname ||'\'::text AS schemaname,
LOWER(template_used) AS inherit_from,
CASE
WHEN template_type IS NULL THEN ''Not Classified''
ELSE template_type
END AS template_type,
dt.description AS database_type,
COUNT(*) AS num_databases,
COUNT(distinct db.replica_id) AS num_replicaids
FROM
'||SPLIT_PART(schemaname, '_', 1) || '_dwh.notes_db_detail db
JOIN dna.database_type dt ON dt.dbtype_id = db.dbtype_id
WHERE
NOT db.is_removed AND db.in_scope
GROUP BY
template_used,
template_type,
dt.description\;
'::text AS SQLStatement
FROM
pg_tables
WHERE tablename = 'notes_db_detail';
Executing this query outputs a row for each schema where the table exists, where each row represents the final query we can execute against the (176!) schemas we wanted. Copy the output and paste this into a new window and press F5.
