Tuesday, May 8, 2012

In Need of an SQL Factory

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):

 DROP TABLE IF EXISTS '|| schemaname ||'.template_utilization\;
 CREATE TABLE '||schemaname||'.template_utilization AS
    \'' || schemaname ||'\'::text AS schemaname,
    LOWER(template_used) AS inherit_from, 
      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
    '||SPLIT_PART(schemaname, '_', 1) || '_dwh.notes_db_detail db
    JOIN dna.database_type dt ON dt.dbtype_id = db.dbtype_id
    NOT db.is_removed AND db.in_scope
  '::text AS SQLStatement
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. 

No comments:

Post a Comment

I like interaction, thank you!

Note: Only a member of this blog may post a comment.