What Does a DB2 Beginner Need to Learn?

By Joe Geller posted Aug 25, 2014 11:55 AM


There are several categories of DB2 beginner.  You might be a novice programmer, or an experienced programmer who is new to database access.  Or, you might have experience with a different DBMS, but not DB2.  Others might be new DBAs, either with or without programming experience.  How long do you remain a beginner?  Since you have found the IDUG website, there is a good chance you are not brand new.  If that is the case, some of your colleagues may be less experienced than you and you can be their mentor and introduce them not only to the Beginners Blog, but to the other content of IDUG.

How long do you remain a beginner is one question.  Another question is how long to you need to keep learning about DB2 (and Java, and all other aspects of I.T.)?  The answer to that is “forever”.  There is always more you can learn about programming techniques, SQL features to accomplish more with less effort, database design, etc.  If your goal is to be good at your craft, then you need to spend time developing your skills.

So, what are some of the things you need to learn, and what things do you need to do to learn them?  What follows is my list of the tools, techniques and process of building your DB2 expertise.

1)      Have the manuals handy.  You can always plug away at a problem until you hit the right syntax, semantics and get the results you want, but you can really save a lot of time and effort if you can look those up.  The DB2 manuals are fairly good and are readily available online (just type DB2 Vxx into Google for a link).  These are invaluable references.  There are also IBM Redbooks, which focus on specific aspects of DB2 and are often better suited for learning what can be done and how to do it.  These are the minimum set of  manuals you should download to your PC:  (note that there are different names for the LUW and z/OS manuals)

  1. SQL Reference
  2. Codes (all the error messages) (Message Reference in LUW)
  3. Utilities Reference (z/OS)
  4. Command Reference (z/OS – commands, LUW – commands and utilities)
  5. Administration Guide (z/OS) – DBAs in particular need this
  6. Application Programming and SQL Guide (z/OS)

2)      Learn to code SQL DML (SELECT, INSERT, UPDATE, DELETE) statements.  Obviously this is needed by programmers, but DBAs also need to know how to access the database.  DBAs are called upon to help resolve problems – performance problems, data access problems (i.e. the program doesn’t seem to return the correct data), etc.

3)      Learn the database structure – the tables and columns, and also the indexes.  You may have a data model to work with (created by whoever designed the database), but often this is either not complete, or out of date.  It is really very easy to query the database catalog to find out what columns are in a table (and their data type), what indexes exist, etc.  You can write Select statements to query the catalog, but even easier is to use a tool to navigate through the database.  Data Studio is a free tool (Eclipse based) from IBM that you run on your PC and connect to your databases.  For z/OS, there are also catalog tools from either IBM or various other vendors to accomplish the same type of navigation, but Data Studio is very nice and free.

4)      Learn database design – normalization, logical models, etc.  Understanding what makes a good data model will help you understand the relationships between the different tables in your databases.

5)      Learn the basics of performance.  Writing a program is only step 1 of your job.  Step 2 is making sure that the program functions correctly (you might be surprised how often testing is inadequately done).  Step 3 is to make sure it performs well.  An online transaction that takes 20 seconds to execute is not going to make anybody happy.

  1. Access paths – what are the ways DB2 can access the data?
  2. Indexes – what are they, how are they used effectively, etc.
  3. Explain – tools to show you the access path for your queries.
  4. Statistics – why they are essential for good performance;  how to see what the statistics for a table are, and how DB2 uses them to determine an access path.

6)      How do you learn performance?

  1. The manual “Managing Performance”
  2. The  IDUG website – we have a large number of articles on various aspects of performance (available to registered users (no cost involved)).  In addition, you have access to past IDUG conference presentations.  Many are geared to beginners, and many others to more experienced developers and DBAs.
  3. Test and measure – try out alternatives, see what access path is chosen by DB2 and see how they run.

7)      User Group Meetings – The IDUG Tech conferences are an amazing way to learn and network with your peers.  However, most shops do not often send beginners to the conferences (which being in other cities have cost for travel as well as registration).  But, there are regional user groups all around the country.  These provide a full day of presentations at either no or very low cost.  The IDUG website lists these regional user groups.  Hopefully your managers will let you take advantage of such a bargain.

8)      Utilities and commands – DBAs in particular need to know how to maintain a database – why are backups (image copies) essential; how to do backups; what is a reorg, when should it be run and how to run it; runstats – what it is, why it is important and which options to use.


You’ll notice that I didn’t give you many answers.  I’ve given you pointers to the types of things you need to work on.  You will have to take charge of your own education.  If you don’t have the time for education, you just have to make the time, even if it is your own time and not your company’s time.  You may prefer eating out, going to clubs or movies, or playing tennis.  So do I (at least some of those).  But, you can always sacrifice sleep if you want to do it all.  Better yet, you should try to convince your boss to allot some time each week for you to improve your skills.  Even better, learning these things is actually part of your everyday job.  To use a function you are unfamiliar with, you need to look it up in the SQL Reference manual; when you get an error message (and you will), you need to look up what it means; to write a query, you need to look up the columns and their data types and the indexes.  So, you have to learn how to navigate the DB2 catalog.