DB2 11.1 for LUW: Basic Training for Application Developers

By Robert Wingate

This book will help you learn the basic information and skills you need to develop applications with DB2 11.1 for Linux, Unix and Windows. The instruction, examples and questions/answers in this book are a fast track to becoming productive as quickly as possible. The content is easy to read and digest, well organized and focused on honing real job skills. DB2 11.1 for LUW Basic Training for Application Developers is a key step in the direction of mastering DB2 application development so you'll be ready to join a technical team.

• Language: English
• Publisher: Robert Wingate
• Release date: Mar 24, 2019
• ISBN: 9781386758181

Robert Wingate

Robert Wingate is a computer services professional with over 30 years of IBM mainframe and distributed programming experience. He holds several IBM certifications, including IBM Certified Application Developer - DB2 11 for z/OS, and IBM Certified Database Administrator for LUW. He lives in Fort Worth, Texas.  

Book preview

Introduction

Welcome

Congratulations on your purchase of DB2 11.1 for LUW: Basic Training for Application Developers.  This book will help you learn the essential information you need to know about DB2 11.1 so you can be productive as soon as possible.  You’ll receive instruction, examples and questions/answers to help you learn and to gauge your readiness for development work on a DB2 technical team.    

Assumptions:

While I do not assume that you know a great deal about DB2, I do assume that you’ve worked in a LINUX, UNIX or Windows environment and know your way around.  Also I assume that you have a working knowledge of the JAVA programming language which we will use for all the embedded SQL examples (in most cases I also created parallel c# .NET examples). All in all, I assume you have:

1.   A working knowledge of Windows (or Unix/Linux) files and navigation.

2.   A basic understanding of SQL.

3.   Proficiency using either Java or .NET

4.   Access to a computer running Windows 7 or higher (or UNIX/LINUX)

Knowledge of DB2 11.1

If you are a beginner, this book should give you what you need to get started, and to develop a solid DB2 LUW foundation. Even if you have years of experience with DB2, you may find yourself challenged by some of the topics.  I encourage you to fill in the gaps with the information in this text book. You may find new techniques and ways of accomplishing things in DB2.    

Experience with DB2 11.1

Unless your shop has upgraded to DB2 11.1, you may not have any experience with it.  My suggestion is to download the free DB2 Express-C product and install it on your local machine.  That way you can follow along with the examples given in this text.  You can also come up with your own training project and work through it at your own pace. 

Knowledge and experience.  Will that guarantee that you’ll succeed as a DB2 LUW application developer?  Of course, nothing is guaranteed in life.  But if you put sufficient effort into a well-rounded study plan that includes both of the above, I believe you have a very good chance of excelling as an application developer in the DB2 Linux, UNIX and Windows world.     

Best of luck!

Robert Wingate

IBM Certified Application Developer – DB2 11 for z/OS

C:\Books\IBM Books\DB2 LUW Basic Training for Developers\Kobo\DB2 11.1 for LUW Basic Training for Application Developers REV3 KOBO_files\image001.jpg

CHAPTER ONE:  BASIC TOOLS FOR DB2 LUW

Welcome to DB2 11.1 for LUW: Basic Training for Application Developers!  Before we get into development activities, I want to introduce you to the environment we’ll be working in.  If you’ve ever used DB2 for LUW, you’ve almost certainly encountered these tools.  But let’s make sure we’re all familiar with how to install and access DB2, and how to use the basic DB2 tools available. We’ll take a brief look at installing DB2 Express-C, IBM Data Studio, and two programming language IDEs which you may want to use for Java or .NET programming with DB2.

DB2 Express-C 11.1 

You can download DB2 Express-C for free from the IBM web site.  We’ll be using that edition for this text.  If you have at least 8GB of main memory on your computer (plus Windows 7 Professional or better), you can instead download the new Db2 Developer Community Edition.  That product is full featured while DB2 Express-C has just a few limitations.   All of the examples in this text book will work with either version of DB2.  

We will install and use DB2 Express-C.

At this writing, the link to download DB2 products is:

http://www.ibm.com/analytics/us/en/db2/trials/

If for some reason the link is broken, simply Google search DB2 Express-C free download.

Installing DB2 is pretty straightforward.  You’ll need to decompress the download file into an installation directory of your choice.  Then you can run the installer.  Simply double click on the setup.exe file in the decompressed install directory. 

db2x1.png

After a few moments you should see this screen.  You can check out the release, planning and upgrade information if you wish.  When ready to install simply click on Install a Product. 

db2x2.png

Click on Install New.

db2x3.png

You may see a Publisher Not Verified screen.  If so, click on Run.

db2x4.png

On the next screen click Next.

db2x5.png

On the following panel, you’ll need to accept the user agreement and then click Next.

db2x6.png

On the next panel, I recommend that you select a typical install, and then click Next.

db26.png

On the next panel, I recommend that you select the first option to simply install DB2.  Then click on Next.

db27.png

On the next panel, select an installation directory or accept the default, and then click on Next.

db28.png

On the next panel, select the appropriate SSH server option.  I recommend that you select the first option to autostart the IBM SSH server at startup.  Then click Next.

db2x7.png

Now you must specify security for the DB2 Admin server.  I strongly recommend that you use a local or domain user account and password.  I recommend that you do NOT use the Local System account because it is not secure.  In my case I used the local account robert – this is what I logon to my laptop with.  Click Next.

db210.png

On the next panel, if you want to configure any special settings for your DB2 instance you can click on Configure.  Otherwise simply click Next.

db211.png

Review your choices and then click on Install.

db212.png

You’ll see a status panel and various activities as DB2 installs. 

db213.png

Eventually you will see a panel that says Setup is Complete.  Click Finish.

db214.png

You’ll see this Welcome screen.  You won’t need to create the SAMPLE database (that was done automatically by the install).  The next section will take you through downloading and installing IBM Data Studio.  You can close this window.  

db2x8.png

Finally, once DB2 is installed you can navigate to the basic tools that are installed with DB2 such as the command window.  Select All Programs from your Windows Launcher and then click on IBM DB2 DB2COPY(Default).   Right click on the DB2 Command Line Processor and select Open (or Run as Administrator if you are an admin on this computer).  From here you can issue DB2 commands. 

Suppose for example you want to start the DB2 Admin Server.  You could issue that command as:   DB2START

db2x9.png

You could issue other commands, such as to create a database, but we’ll do most of our work in the IBM Data Studio which is our next topic.

IBM Data Studio

I strongly recommend that you install and use IBM Data Studio.  It’s an excellent IDE that you really cannot afford to be without.  While you could perform many operations using the Command Line Processor (CLP), I suggest you use Data Studio – it’s easier.

If you did not choose the DB2 Express-C post-installation option to download IBM Data Studio (when you installed DB2 Express-C), here is a link to download it:

https://www.ibm.com/developerworks/downloads/im/data/

Once you’ve downloaded the zip file, you’ll need to decompress it into an installation directory of your choosing.  In my case, I have decompressed into a directory named DataStudioInstall. 

Click on the imLauncherWindows.bat application file.

DS1.png

This screen will appear.  Select the Install option.

ds2.png

Choose either an Administrative or Non-Administrative Installation.  If you have admin privileges, I suggest you choose the Administrative Installation or Update.

ds3.png

Click on Run.

ds4.png

Click on Install.

ds5.png

Click to check mark the IBM Data Studio client, and then click on Next.

ds7.png

Accept the terms of the license agreement, and then click Next.

ds8.png

Click Next at the following screen.

ds10.png

Click Next on this screen.

ds11.png

Click Next on this screen.

ds12.png

At this panel, click on Install.

ds13.png

You’ll see various activities while the product installs.

ds14.png

Now click Finish.

ds15.png

To start the Data Studio, use your program launcher and select Data Studio 4.1.2 Client. You will be asked to specify a workspace.  I recommend you take the default and click OK.

ds17.png

You should then see this window which means you are in the Data Studio IDE.  If you are not in the Administer Databases perspective, select it from the dropdown show below.

ds18.png

Now expand the tree by clicking on localhost.  You should see a single node which is the DB2 instance name (which will be DB2 if you accepted the default during setup). 

ds19.png

Assuming you created the Sample database when you installed DB2, you can go ahead and create a connection to that database in Data Studio.  To do this, right click on the DB2 node and then select New Connection to a Database. 

You should see a Connection Parameters window.  Now fill in your login id and password.  Next, you can test the connection by clicking on the Test Connection button. Assuming the connect action succeeded, you should get a window that indicates the Connection Succeeded.

Now click the Finish button on the Connection Parameters window.

ds20.png

Now you can see the Sample database in the Data Studio tree.

ds21.png

If you click on SAMPLE, the database tree will expand and you’ll see all the database objects organized according to type in a tree structure.

ds22.png

To see all the tables in the SAMPLE database, click on Tables.

ds23.png

To create a new object, you can right click the folder for the object type you want to create, and then fill in the required details.  For example, let’s create a table named TESTTBL. Right click the Tables folder and then select the Create Table option.  You’ll be prompted to select a schema.  For now, just click on your logonid which should be in the selection list.

ds24.png

You’ll be notified that you are making changes to a database object.  Click on OK.

ds25.png

Type the name of the new table into the properties window, then click on the Columns button.

ds26.png

Click on the + button to create a new column, or click on the X button to delete one.

ds27.png

Let’s add a couple of columns.  One column will be named ID which will be an integer and the primary key (check the primary key check box), and the other column is named DESCRIPTION which will be a varchar (30).  Both columns will be defined as NOT NULL. 

Finally, click on the Generate DDL button at the top of the table definition screen.  This will generate the Data Definition Language commands to create the table. 

ds28.png

You’ll see a screen that shows the DDL that was generated.  If you need to change anything in the DDL, this is where you can do it. Otherwise, click on Finish.

ds29.png

At this point we have created but not executed the DDL.  You’ll see this screen with the DDL at the top.  To execute the DDL, click on the forward green arrow.

ds30.png

Now then you can review your results in the Status window.  As we can see, the result is successful.

ds31.png

The table example shows how to create objects using the Data Studio object tree.  This is very handy, and I encourage you to use the Data Studio functions.  However, in this text book we’ll usually create our objects by simply executing DDL through the Data Studio SQL window.  This is partly to save space and partly to ensure that you actually learn the DB2 statements (you might not otherwise because Data Studio automates a lot).  In the previous example we could have simply provided