Voice of the DBA

Author: way0utwest

The Loss of Knowledge

This article has a great opening quote. It says: “We are drowning in information but starved for knowledge”. It’s from John Naisbitt, who wrote the book, Megatrends in 1988. I think this quote can be very apropo in organizations as we have data, we have plenty of reports deriving information, but sometimes we don’t have a lot of knowledge, especially when there has been turnover in our staff.

We can train new people on many things, but not everything. The knowledge of the culture, of what others know, of the little strange bugs or behaviors that can’t get fixed, the tribal knowledge accumulated by living in an environment. These are the learnings that can’t easily be replaced, and until they are, often new employees are less productive.

Of course, sometimes new employees will view the world in a different light and find solutions others haven’t considered. That happens, but it’s more likely that they will make mistakes, break something, or just be less effective than their predecessors.

There are no shortage of articles, like the one above or this one, that discuss the concerns IT leadership has about employee turnover. Perhaps the leadership does see that as a problem, but often first level managers don’t. Often leadership doesn’t realize how poorly trained or effective first line managers are in working with their technical staffs. I sometimes think that the world in Dilbert is far too prevalent precisely because of very poor management skills. It certainly seems that efforts made to retain employees are relatively rare in many companies.

In my career, we’ve had some boom and bust times in the market for developers. There have been times where anyone with a certification or a hint of experience could get hired (or get a raise). There were other times when people were careful to hold onto their jobs because finding a new one could be hard.

I think employers should not only invest in their staffs, but work to train and upskill them, demand more from them over time, but treat them fairly with more than just a paycheck. The dividends from a hardware allowance, a training budget, and more can easily pay for themselves with better productivity and the lack of fees to recruiters. You should certainly hold staff accountable and responsible for getting work done, but make sure you also treat them fairly and support them.

That’s if you also spend time managing your managers and ensuring they balance the demands they make of employees with the support needed to ensure their staff performs well. If you ignore the managers, you might as well ignore the staff, set aside more recruiter fees in your budget, and hope for the best.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

April 14, 2023
Flyway Desktop PoC–Adding a Shadow and Baseline Script

In the last post, I created a baseline marker for Flyway in each database. This set the version in the dev and QA databases to v1. However, I also need a baseline script, at least the tool asks for one, so this is the process if you have objects in your production or other downstream databases.

I’ll do this for SQL Server and then PostgreSQL.

Why do this?

The main reason to create a baseline script is to note which objects already exist in production. For these objects, I don’t want to track these are changes in their current form.

For example, if I already have a CountryCodes table in production, when I create a project, I want to tell Flyway Desktop that this table exists in production, so if the dev version matches, don’t add this to scripts. If it doesn’t, then I’ve done something in development and need an ALTER script deployed to prod.

What was the Other Baseline?

The first baseline in this post, is a version marker. I hate that this is the case, but both Flyway (pre-Redgate) and Flyway Desktop (evolved from SQL Change Automation), had the concept of a baseline, but these were somewhat different things.

Flyway Baseline – The initial version of the database. Don’t deploy any scripts that are <= to this version.

Flyway Desktop Baseline – A script that has the structure and code of all objects that exist in the target database(s).

We can create a baseline script for Flyway, which looks for a B script, but the baseline command expects that you create this script manually. This is used to populate a new database with the baseline migration script prior to running all other scripts.

Setting up the Baseline Script

Flyway Desktop makes it easy to create a baseline script, and in fact, prompts you to do so.

In my project, if I go to the Schema Model (first) tab, I see there is an object in Development. This was the table I created when I set up the database. The goal is to get this table to other environments.

This table doesn’t exist in QA. I do have the flyway_schema_history table, which was the result of the baseline command.

If I go to the Generate Migrations (second) tab, I see this. The first thing that the tool wants is a Shadow database.

The shadow is essentially a development V-1 (v minus one) version. This is where I test all migrations, compare the state with development, and then determine what’s changed. This is just a regular database, but I create this outside of Flyway Desktop. For me, I created a database (FWPoc_1_Dev_Shadow) and then clicked Set up shadow database to get this dialog. You can name this anything.

I enter details, and test the connection before saving this. In general, this ought to be saved to my user settings as I’ll have my own shadow different from other developers. I DO NEED to click the “ok to erase data” box.

Once this is done, I now see another prompt on the Generate Migrations tab. Now I need a baseline script. I don’t have anything, but I will click the button.

This gives me a dialog to pick a target database. This target is used to get the initial set of objects to populate in the baseline script. You can use production or a copy (recommended) as the target database.

My QA is the same as prod, so I add that with the proper connection string and then I see the target here for the Baseline. I am ignoring static (or lookup/reference data for now). I’ll click the Baseline button.

This runs and … nothing.

Which makes sense, as there is nothing in my target database. I actually get an error after this, which tells me that it doesn’t make sense to baseline an empty database.

I wish that were surfaced earlier. In any case, if I close this, I get the same image above, saying I don’t have a baseline. For now, I’ll ignore that.

Summary

Not much happened in this post. I added a shadow database, which I’ll use to generate scripts. I tried to baseline, but that errored, as it should. I really don’t need a baseline, so I’ll come back to this later in another format.

For now, I’ve advanced the SQL Server project. I’ll actually repeat these steps for the PostgreSQL one, but it’s really creating a new database for the shadow and setting a connections string. Everything else looks the same.

The next post will actually generate a script and deploy this to QA.

April 12, 2023
Big Data Downsides

Companies often want more data to help them make decisions on how they run their business. There has been this quest to gather and analyze as much data as possible to increase the efficiency of their operations to help reduce costs or increase profits. This has led to the importance of data as an asset, and the need for more data professionals in many organizations.

That’s good for many of us that work with data.

However, using data to try and improve your efficiency has a downside. It can lead you to a very narrow focus in your approach. That can be good in narrow, well-defined areas, such as minimizing the distance driven or packing containers. For less focused tasks, such as telling a story or writing code, this can mean you get stuck in a rut and limit your opportunities to improve.

There’s an interesting article about big data and Hollywood, specifically looking at the types of products produced. Big data analysis leads companies to aim for the most effective types of movies that make money. Good for a company, not so good for society. Arguably, not even good for a company over time as people will tire of the same story, or type of story over time. Eventually, making simple decisions based on past data will start to fail.

I can see the same thing in other industries as well. Using Big Data to drive decisions can help, but many of the areas where we use these techniques will evolve and change over time. The way we solve problems with code change over time as we develop new tools, techniques, platforms, languages, etc. There isn’t a perfect way to design a database or write a CRUD app precisely because new capabilities or new possibilities emerge. You could say the same things about marketing, manufacturing, medicine, and many other endeavors.

This isn’t to imply big data and complex analysis isn’t helpful or useful. It’s just not everything. We need to balance human input, with some creativity, some instinct, some diverse thought, and some guessing. Most importantly, we ought to experiment and learn, not only from what machines might extrapolate, but from how humans change their thinking over time.

Find a balance, accepting some imperfection in your process and in the world at large. Hopefully that will lead you to some success.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

April 12, 2023
Creating a Simple Graph in RedisGraph
I delivered a session on graph databases, and in it I used RedisGraph to show how you can work with graph data on that platform. This blog shows a basic creation of a graph in RedisGraph. This isn’t intended to be a comprehensive post, but more a basic look at the code to work with graphs.

Adding Data

I connect to Redis with the command line. If you don’t know how to do that, I covered this in another post. Once I’m connected, I can run this code to create a graph and a node:
```
GRAPH.QUERY Northwind "CREATE (:employee {employeeID: 10, firstName:'Steve', title:'President'})-[:REPORTS_TO]->(:employee {employeeID: 11, firstName:'Tia', title:'CEO'})"
```
This will create a graph, two nodes, and one edge between them. This starts with GRAPH.QUERY, which let’s Redis know this is a graph query. I then give the name of the graph, which is Northwind since I based this on the Northwind dataset.

Next I use a Cyper language CREATE and enter the data in what is very similar to JSON. I label a node (employee), add the properties and values (employeeID and firstName with 10 and Steve as values).

I use the square brackets with the edge name in between this and another node. This will get me the results shown below. You can see that one label, two nodes, six properties, and 1 relationship created.

I can get the employee nodes with this code:
```
GRAPH.QUERY Northwind "MATCH (e:employee ) return e"
```
This returns the two nodes.

If I want to see the relationship, I need to ask for that with this code:
```
GRAPH.QUERY Northwind "MATCH (e:employee)<-[:REPORTS_TO]-(sub) RETURN sub.firstName as employee, e.firstName as manager"
```
This returns my graph as a series of data elements.

Tada!

There is more you can do and certainly you can create more complex (or larger) graphs in Redis. The drivers from various languages should return JSON to you that you can deserialize and visualize or otherwise process results.

A lot of this is basic, but if you want to experiment with Redisgraph, a container and the CLI is a good way to get started.
April 10, 2023