What? An article on a Friday instead of a Tuesday? What’s going on there? Don’t worry, I’m not sick and anarchy has not broken out in Austria. There is a very simple explanation:
Right now I’m at the Automic World in Orlando, and just as this post is being published, I’m giving a presentation on the subject of this article: I’m explaining to my audience that SQL queries on the AE database are worthwhile – if you know what you’re doing.
And here is the written version: 3 convincing reasons to write database queries and also the 3 biggest risks you are taking by doing so.
By the way, you can download the slides from my talk at the end of this article. There you will find more detailed information about the use cases and the scripts I presented.
Reason 1: The Needle in the Haystack
A client came to me with the following problem: Most of the jobs in his workflow for invoicing had a post condition. But, unfortunately, not all of them. Therefore, he defined a default post condition for all jobs that did not yet have one.
There were hundreds of workflows and more than 2,500 jobs on this client’s system. No post condition was defined for about 10% of them.
Without SQL, this task could take a lifetime. With SQL, on the other hand, we are talking about a few lines of code in order to find the entries of relevant jobs in the database.
I’m not going to go into detail here – you can download the slides for my Automic World presentation at the bottom of this article. You will find additional information regarding the technical details there, as well as the script I used to solve this problem.
SQL queries allow you to quickly find elements in your database that meet certain criteria.
Reason 2: Connecting the Data Quickly
The AE database is large, to say the least. Depending on the complexity of your system, there are various areas, clients and levels. To extract relevant information is labor-intensive and especially slow – unless you use SQL.
Even short and simple queries can save you much time and trouble. I’m introducing a detailed examples in my presentation: During downtime caused by an application controlled by ONE Automation, scheduled tasks were skipped.
In order to later find all tasks in active schedules that were skipped, you need to connect information from various tables.
A description of this example would be too long for this article. If you want to know how it works in detail, you have two options:
- You will find scripts and graphics of my approach in the slides for my Automic World presentation.
- Alternatively, you can wait two weeks when I will be presenting this example in more detail in this blog.
Reason 3: The Hidden and the Missing
Making things visible that are not even there – how is that supposed to work? I’m demonstrating this with two examples:
Errors can easily occur when transporting from Dev to Prod. For example, you can transport a workflow but forget a job that belongs to this workflow. This job will then be missing in Prod and the workflow ends with an error message.
With a simple and fast query on the database, you can find out if elements are missing in the system.
As a regular reader of this blog, you are already familiar with the second example: You can find information in the data that has not yet been predefined. For example, the critical path of a workflow.
More about that in the article Analysis of Critical Paths.
The Risks of SQL Queries
I’ve mentioned this at the beginning of this article: You have to be careful when working with the database. SQL offers cool advantages, but you have to know how to handle it.
In my view, there are three risks that require your special attention.
Risk 1: Unintended Side Effects
The first risk is also the most dangerous: If you change something directly in the database, you are endangering your entire system. Small changes can have unexpected effects.
Luckily, there is a simple solution for mitigating this risk:
Never ever change anything directly in the database!
Risk 2: Compromising Performance
Queries make demands on your database. If you don’t pay attention, your queries can have an effect on the performance of your ONE Automation.
There are various measures to reduce risks:
- Develop queries with SQL Clients
- Develop queries on a development system before executing them in the production system
- Control the size of the query results
- Keep an eye on system performance and runtime
Although SQL queries affect performance, one thing remains true: If you want to access data and information in your Automation Engine, there is no faster and more resource-conserving way than with SQL!
Risk 3: Mousse au Chocolat
This point is the least dangerous – but also the most difficult to fix.
Do you know the emoji in this illustration?
Acquaintances of mine thought that this meant Mousse au Chocolat, and were shocked when I enlightened them with the actual meaning. Some of their text messages took on a whole new meaning…
The same can happen with SQL queries: Perhaps your query delivered something totally different from what you had planned. No problem if you notice it right away. But often it is not so obvious.
How to become a SQL Master
Was I able to pique your curiosity? Of course, there are more than 3 reasons to becoming familiar with SQL and the database – unfortunately, there are also more risks.
There is only one thing for profiting maximally from SQL and database queries: You must learn how to handle them properly.
For a successful introduction, I have 3 recommendations for you:
- Download the slides of my presentation here and try to understand the examples.
- Read my series of articles on the introduction to SQL querying: Part 1 and part 2
- Participate in my AE Database Knowledge Workshop and immerse yourself for 3 days in the details of database and SQL queries.