Introducing: Roll for Database!

Alright, so I’m playing around with something. I’ve developed a bit of a TikTok addiction after exploring the platform to connect with my 14-year-old. One of the post types I’ve enjoyed is the “Roll for ______” type, where the content creator uses dice typical to Dungeons and Dragons to choose combinations of things that are sometimes magical, sometimes ridiculous, and occasionally disgusting.

Roll for sandwich is a fun TikTok example. I particularly enjoyed a recent one where he had to spread marshmallow fluff on Garlic bread. I sew as a hobby, and I very much am enjoying the randomly generated quilt blocks.

I’m not quite ready to jump into a video format, though I can see going that way. I’m going to start with this spreadsheet of choices, and of course using SQL for my dice rolls!

This Article’s Rolls

Here’s the unedited Rolls using SQL’s RAND function:

$ db2 "select int(ceil(rand()*6)) from sysibm.sysdummy1"

1
-----------
          1

  1 record(s) selected.

$ db2 "select int(ceil(rand()*6)) from sysibm.sysdummy1"

1
-----------
          1

  1 record(s) selected.

$ db2 "select int(ceil(rand()*5)) from sysibm.sysdummy1"

1
-----------
          4

  1 record(s) selected.

$ db2 "select int(ceil(rand()*14)) from sysibm.sysdummy1"

1
-----------
          5

  1 record(s) selected.

RAND gives me a number between 0 and 1, and multiplying that by the top number in my range, and using the ceiling function gives me a number between 1 and the top number in my range.

From the spreadsheet, that means that I’m doing:

Phew, I’ve got one platform on the list (PostgreSQL) that I’ve never set up before, so I’m happy I don’t have to try to figure it out at that level.

I’m also skipping the data model roll for now since I don’t generally have those set up for most platforms. If this format proves to work for me, I’ll do more of the leg work there.

I’m not sure how much my chaos roll affects me here – case seems a tad less critical on cloud, but we’ll see.

Db2 on Cloud

I’m not going to be paying for any of the formats I use, so I’ll be using the lite tier of Db2 on Cloud that is free. I’ll just use the GUI to create and work with a database in this case because it is an easy method with IBM Cloud.

First, I go to https://cloud.ibm.com/ and log in using my pre-existing IBM ID. If you don’t have one, you can create an IBM ID for free. My basic dashboard looks like this:

On that screen, I’m going to select “Create Resource”. On the next screen, I’m going to type “DB2” (remember, no lower case!) and select the entry for Db2:

Now this next part is a bit tricky – you have to select a region where the free version (Db2 Lite) is available. I happen to know it exists in Dallas and that’s not too far from me as world distances go, so I select Dallas and the Db2 Lite plan:

Finally, on the right, I click the box to agree to the license agreement, and then click Create:

Now it takes me to the Resource list where I can wait for my resource to be provisioned:

Today’s Task

Once my Db2 on Cloud is provisioned, I can click on it to get options on how to interact with it. To meet the simplest requirements of the roll for database challenge, all I have to do is import a csv, which is strangely easy using the IBM Cloud interface.

First, I select “Go to UI”:

In the UI, I click on the data icon on the left:

This brings me into the data section with “Load Data” already selected from the tabs at the top. Here, I need to drag and drop a csv file in, and then click “Next”:

In this case, I’m using actor.csv from the sakila database.

Next, I need to select a schema to use, and a table. In this case, I’m selecting the only schema that is there. I’m also going to ask it to create the table for me. I put in a table name, and click “Create”. After clicking “Create”, the “Next” button becomes available:

On the next screen, we can pick options related to the columns and our data: Because my CSV had a header row with column names in it, we have column names! Data types are reasonable, if a bit specific. I’m going to click on the pencil next to the data types to allow a bit more space on those VARCHARs:

I get one final confirmation screen before starting the load:

An interim progress screen looks like this:

And the screen when it completes successfully looks like this:

And that’s it. I now have the data in a table that I can query:

Summary and Analysis

One of the things that made this feel strangely easy to me is that I’m used to having to actually create a table before loading data into it. The UI for Db2 on cloud makes this feel like a step I just have to click through, rather than an actual separate task.

I live in a world of devops, and using a GUI to create tables without checking the code in anywhere feels icky and isn’t something I’ve done in a production manner in years. There are other ways to interface with Db2 on cloud that could alleviate these concerns, and I’m sure a future dice roll will lead me there.

There are also a lot more options we can pull on for loading and inserting data in Db2.

What do you think of the format? Anyone like Roll for Database? Interesting to continue? Please share your thoughts in the comments below.