So far we’ve learned about the very basic concepts of Db2, but now I want to dive in a bit and work with tables. Some of the material in blog may take a few more blogs to fully understand, but that is okay. The goal is to get you started. Later we will come back and learn all the details.
We will be using SQL (Structured Query Language) to communicate to our database. DSM (Data Server Manager) is the UI (user interface) that we can use to work with our database, but we should do as much as we can with SQL. Why use code when there is a user interface? I’m glad you asked.
The user interface is a great way to work with our database, don’t get me wrong. The problem with using the UI exclusevely is that you are limiting your usefulness. When writing an application that interfaces with the database, you are going to have to use code.
Additionally, SQL is a standard language that is used widely by other databases. As a developer, it is important to learn the information that is going to transfer between technologies. If we learn the SQL standard, we can easily work with Db2, Oracle, SQL Server, MySQL, etc, all of which are based on the SQL standard.
How to Use SQL in DSM
There will be a panel on the left containing a button Run SQL that will open a window similar to this one:
Upon opening this Run SQL tab, you will be prompted to give your credentials. These were set during the installation process (db2inst1 was the default and you chose the password):
You can optionally check Save credentials to repository to save you some effort in the future.
Creating a Table
Once you have the SQL panel open, type out this code:
This is the SQL needed to create a table with 6 columns. Each column is put on a new line with the data type immediately following the column identifier (column name). Putting each column on a new line is simply a convention. SQL is whitespace insensitive, meaning you can put spaces, tabs, and newlines to space out your code as you like. This is equally valid:
You may think this is harder or easier to read. It is ultimately up to you how you space out your code.
Once you have your SQL ready, you can execute your code by using the Run dropdown or issuing the shortcuts (such as CTRL S):
The Run Selected command will execute the SQL command that is currently clicked, up until the next semicolon (;). Alternatively you can highlight over multiple statements and then click Run Selected to execute them all.
The Run All button will execute everything in the document.
Once the script executes, there will be a new entry in the result panel below:
If for any reason there is an issue, the results will look like this:
Clicking the Log button will give you more information on the error and a link to the documentation for that specific error:
Once you have the table created, you can find in using DSM by going to Administer -> Tables.
From this page you can easily see all of the database tables. You can also see that the users table we just created is in the default db2inst1 schema. As we talked about in the data structure blog, a schema is a logical grouping of database objects. We will discuss schemas in more detail in the future.
Dropping a Table
We can drop this table that we just created very easily by issuing this SQL:
Be careful executing this command because it will delete everything inside of the table.
Looking to the Future – Primary Keys
We will discuss primary keys in detail, but for now it is important to understand that it is wise to label a particular column as a table’s primary key. Thus, if we recreate the table we just dropped, we might do it like so:
This addition is essential when it comes to data integrity, which we will discuss in more detail soon. Essentially, though, adding the PRIMARY KEY keyword forces each row to be unique and will be used as the basis for relationships between tables.
We’ve learned the very basics of DDL (Data Definition Language). This is the section of SQL that involves defining the structure of our database. Two of the important commands in DDL are CREATE TABLE and DELETE TABLE. Once we have the structure, we can begin working with data. That’s what we will be doing in the upcoming blog.