Jan 10 2018
With WhereScape you have the freedom to do whatever you want. With time passing, I figured out, what is important to me and what not.
But how to ensure that things are done always the same way? And not only for me but also for my collegues?
We started to create meta views for objects, columns and indexes. On top of them we created “error views” to list all issues which were important to us: e.g. all tables and indexes should be compressed, data type for hash columns should be binary, etc.
For some rules we implemented a metadata cleaning service which constantly scanned for issues and fixed them straight away.
But some issues were still views which we needed to check regularly.
Lately when I gave some thoughts to this issue, I came to the conclusion that this is not an efficiant way to do it. When we develop, I want to get feedback straight away. Not checking once a week or every day my error views for issues.
It is out of scope to believe, that we can persuade WhereScape to change their software to our way of thinking. There are too many different views from customers in the market.
How can we achieve to get some feedback in our work? Did we forget something? Do the objects have the correct Key Types? We can’t change the way, how the system works, but maybe we are able to pass some error messages into our output. And what is our main output? Tables with their corresponding stored procedures.
I created another section in our stored procedures, where we list all meta data checks. If the check is ok, I add the message as a comment. If there is an error, then without the comment dashes. In that way, the compilation of the stored procedure will fail and we can check the messages!
We created 2 templates:
nu3_utility_metadata(for any platform-independent metadata checks)
nu3_sqlserver_utility_metadata(for any SQL Server checks)
What could be platform-independent checks:
- A hub needs one hub hash key
- A link needs one link hash key
- A satellite needs either hub or link hash key (and only 1)
- A satellite needs a change hash key
- If the stage tables has hash keys, make sure that all columns belong to a hash key definition
- In a satellite, verify that all columns which are hashed in the change hash key on the stage table are also available in the satellite
SQL Server checks could be:
- All hash columns should be of data type ‘binary’
- Objects of type DataStore, Satellite, Link, Hub should have a Clustered Columnstore Index
- Any index of stage tables should have the feature ‘Drop Index before Table Update’ enabled
These are just some ideas.
I believe to include such things into the template, abuses the original idea of templating. But it helps to get the meta data quality straight.