Db2 Basics: Introduction to IBM Db2

Posted by

What is Db2? This article is one of my few that is not directed at a Db2 DBA or someone trying to fill some of the roles of a Db2 DBA. This is directed, as much as I can manage, at any technologist trying to understand what Db2 is and the role it plays in an IT organization.

What is Db2?

Db2 is a Relational Database Management System(RDBMS). It organizes the storage of a vast array of types of data, and optimizes the later retrieval of that data. It implements the standard concepts of a relational database, as well as the SQL standard query language that is available on relational database systems. It also allows storage in non-relational formats such as XML and JSON.

Db2 comes out of the same IBM where E.F. Codd worked to develop the relational database concept in the first place. It has been around since the dawn of the relational database, but gets regular refreshes with new features all the time. There is a vast amount of engineering that has gone into Db2, driven by clients pushing IBM to make it better. Db2 can serve as a traditional relational database, a hierarchical database, an object-oriented database, an XML database, a store for JSON, or a key-value store, all depending on how exactly you implement it. It can have some interesting integrations with Spark.

Db2 is not the least bit open-source, but does have some free distributions. It is fully proprietary to IBM, and if you want to run it on larger servers, you must pay for proper licensing.

How is Db2 Licensed?

Like with any complicated software product, licensing is not easy to fully understand. First, there are some free editions of Db2 with “community support” that you can use to try things out or even to run smaller production environments. Community support means whatever you can find online for free to help you out, or whoever you can hire to help you with Db2.

Most frequently, Db2 is licensed by Processor Value Unit (PVU). PVU is a value that takes into account the number of processors and the power of the processors on the server you run DB2 on. There are several other methods of licensing DB2 that may apply to specific situations, including:

  • Authorized User – this method allows you to have specific users access the Db2 server. 10 authorized users does not mean “Any 10 people who happen to connect”, it means a specific list of 10 and only 10 people who connect to the database.
  • Terabyte – this licensing method lets you pay by the number of TB of data within the database, regardless of users or CPUS.
  • OEM – this licensing method lets you bundle Db2 with your software product when you sell it to others. If you buy a non-IBM product that includes Db2, this is the kind of licensing they use. The frustration with this is that you likely don’t have the ability to open PMRs with DB2 – your software vendor would have to do that.
  • Bundled – some IBM software products bundle Db2. Usually this is considered a Restricted license. You can open PMRs with IBM about Db2. Your entitlement to use Db2 may be restricted in some way, and the product documentation should tell you what edition of Db2 you get. With 11.1 and later, it is likely to be Db2 AWSE.
  • Site License – IBM does offer site licenses, with varying costs.

What Do I Get When I Pay for Db2 Licenses?

Most Db2 licenses consist of a first-time purchase of the software, and then an annual “S&S”. Both must be paid to be considered in compliance. There is also a “Db2 Direct” edition now that only consists of a monthly payment, and is particularly well suited for Db2 on a cloud environment.

Not only do you legally get the specified edition of Db2 and all the ancillary things it may include, you also get the right to call IBM Support for questions and problems. They don’t generally help with overall issues like “my database is slow” unless there is a suspected code issue, but you can call in and have them work with you on errors you encounter. If you encounter a specific bug, they may (or may not) be willing to provide you with a software patch to address that bug. Sometimes the answer is “upgrade to version X” or “update to fix pack N”. But if you know how to properly work with support, they can really help you out of some jams.

What Platforms Does Db2 Run on?

There are several separate code bases for Db2. Some of the concepts and implementations can be very different, and a DBA for one code base cannot always do the other platforms. The main platforms are:

  • Db2 for z/OS
  • Db2 for iSeries (AS400)
  • Db2 for Linux, Unix, and Windows (LUW)

Db2 also runs on Docker, now. There are decent images in the Docker store to use as a starting point.

Isn’t Db2 Legacy?

Coming out of college about 17 years ago, I thought Db2 was “Legacy”. In my college-kid mind, this meant it was on its way out. Well, I went to work for IBM straight out of college and at that time Db2 and Oracle were duking it out for the #1 spot in the RDBMS market. Db2 has dropped some market share since then, largely to MS SQL Server and some of the open source RDBMSes – MYSQL and Postgres. But if you go into the world of the highest volume transactional systems in the world, Db2 on the mainframe (z/OS) is still king. Db2 is still newly adopted and well-loved all over the place. This blog focuses on Db2 on LUW – midrange, and there is no shortage of jobs or consulting gigs.

Look up job postings, and you will see it Db2 is a skill in demand. Db2 DBAs may not be quite as overpaid as Oracle DBAs, but we tend to be a highly paid specialty all the same.

Why You Need A Specialist When Working With Db2 (or Any Relational Database)

There seems to be a move towards technologists lately that can handle all of the skills, especially in smaller enterprises. Why do you need a DataBase Administrator (DBA) as a specialist instead of just application developers who are decent with DB2 or operations/system analyst that handles databases along with applications?

A DBA has a particular skill set that gets very much in the weeds in a number of areas, and if you have a technology generalist supporting your databases, they’re likely to miss a lot of the nuances that make the difference between a highly efficient database and a system glued together with string and bubblegum. This means a Db2 implementation that did not involve a DBA to build or support may work just fine, but many factors can cause it to fall apart. It is best to involve a specialist during build and for ongoing support, monitoring, and optimization.

The role of the DBA incorporates components of the Systems Administrator, Developer, Architect, and Business Analyst, and integrates with the specialists you may have in each of those areas.

Isn’t the Relational Database Obsolete?

No. No-SQL has powerful use cases, but if you have data that can fit into a nice, clean relational schema, then that data can usually be more quickly updated, deleted, and queried in a relational database than a No-SQL system. Querying a relational database often offers a level of flexibility that you don’t get when retrieving data from a No-SQL system. In my highly-biased option, No-SQL cannot hold a candle to RDBMS performance for most transactional workloads. Nearly every company you do business with has relational databases on one platform or another. Relational databases still have a role to play.

That doesn’t mean they fit every data storage need. There are absolutely projects that work better with a no-SQL database.

What Features are Available in Db2?

DB2 has all the standard features you require out of a relational database. It enforces data integrity by default and offers a wide range of recovery options to fit nearly any possible scenario. DB2 offers several different formats of multi-server clustering for high availability, disaster recovery, and applying massive amounts of computing power to larger data sets. Db2 Blu is a columnar, memory-heavy database for analytics. Just ask, and you’re likely to find a feature in Db2 to address an issue or workload. Db2 is appropriate both for high-volume OLTP environments and giant data warehouses and analytics environments depending on how you implement it. Many of the features of Db2 are covered in the other 500 entries in this blog, and there are some features that are not covered. It is impossible to list every feature or even the major ones.

What Applications Work With Db2?

Like features, the list is quite long. SAP is one of the larger ones that usually certifies on Db2. Most IBM applications or middle ware like WebSphere and WebSphere Commerce work with Db2. Db2 has an Oracle compatibility mode that when engaged, allows the vast majority of code that runs against Oracle also run against Db2. Thousands of custom applications work with Db2. You can connect to Db2 from bash, KSH, PowerShell, Ruby, Java, Python, Jupyter Notebook, C++, and nearly every language you can think of. Several communication protocols work with Db2 such as DRDA, CLI, ODBC, and JDBC.

Db2 Database Servers

Db2 is often installed on a dedicated database server. There are several reasons for this. First is usually that your databases should be in the most protected firewall zone possible, and should not generally be exposed to the internet. Also, Db2 is often licensed by the server, so consolidating databases on a small subset of servers makes sense for cost reasons. Often database servers may be some of the more powerful servers in your enterprise because managing fast concurrent access to data properly may impact the performance of multiple applications.

Applications often connect into the database server(s) from whatever servers they live on.

Usually there is a production database and one or more non-production databases so that changes can be tested before they get to production server. These databases should be on different servers to isolate production as much as possible.

Learning Resources

One of the larger challenges with Db2 is getting education on it. While it seems like training and articles available on MS SQL Server are a dime a dozen, you have to search a bit more to find Db2 education. The IBM courses available are good, depending heavily on the instructor. There are learning paths for Db2 administration and Db2 development.

Db2 is a great thing to teach yourself. I have close to 500 blog articles here to help you find your way. My Db2 Basics series is particularly well suited to those starting out. The IBM tutorials for certification are actually really good, too, even if you’re not aiming at certification.

My favorite source for Db2 education is the IDUG conferences. There’s more education there than you can possibly absorb in a week, and great networking opportunities as well.

There are a number of Db2 consultants who will provide customized education. My favorite type of engagement is to go to a client’s office and spend time educating them, sometimes while building out a new system or doing a health check. Personalized training like that is the best way to meet you wherever your skills start and work with you on a plan to get the basics under your belt or to level up. My team at Xtivia often has time to help with this, though we obviously have to charge for it.

Perhaps the best way to learn with Db2 is just to download it and play with it. You can download Db2 for free, and install it, and then use the Db2 Demos to learn.

Db2 Twitter Accounts to Follow

The Db2 community may feel small, but we’re generally a nice, sharing bunch. With the possible exception of Stack Exchage, we welcome newbie questions, and are happy to point people in the direction of useful resources. A few accounts to consider following on Twitter include:
@ember_crooks – me!
@AskIBMAnalytics
@IDUGDB2
@DB2LUWAcademy
@erwin_hattingh
@craigmullins
@mnicolaIBM
@sap_aws
@globomike
@db2nightshow
@IBMDb2
@MKrafick
@ARSDB2
@idbjorh
@dbisoftware
@IvanLVWong
@DB2Geek

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

10 comments

  1. With Licenses the VPC — Virtual Processor Core is a very attractive licensing form, as it reliefs you from counting PVU consumptions for sub-capacity in virtual environments with IBM License Metric Tool (ILMT).
    If you are not using LMT but host a Db2 VM in your vSphere environment, you might not be eligible to use sub-cap pricing, but have to license the full server with all cores for Db2. So save the money and ask someone, who knows IBM licensing by heart, before it hits you during an IBM Audit later.

    1. Also, particularly small environments may not require ILMT and may be eligible for a manual tracking system, but IBM doesn’t make that easy.

  2. I have been using db2 for the last couple of years. Every time i read about db2, it seems all new all over again. I find this blog very much important.

    I would like to ask one question if i may,

    How do we handle LOB files in db2 especially when taking backups. We have a very huge database on the company I am working and we were not able to take an incremental database backup because of the nature of the LOB files ( the application vendors said) it is not possible for db2 to handle LOB files like ORACLE does.
    Because of this ‘inability’ of DB2 to handle LOB files we are forced to take full online/offline backup every day. The size of our database is over 1TB and it is taking us over 2 hours just to take backup.
    This gave me ( as a DBA ) a very hard time to manage my database.

    Can you please give me your opinion on that ?

    Thank you very much

    1. I’m a bit perplexed as to why LOBs would conflict with an incremental backup. To my knowledge, incremental backups are actually a strategy for dealing with non-logged LOBs. Are the lobs logged or non-logged? Are they inlined at all? How large are we talking?

  3. Hi
    I am planning to take DB2 11 fundamentals certification exam for Z/OS since there was no material to prepare please guide me how to prepare to pass the certification exam

    1. Hi Govinda,
      my recommendation is a visit of IDUG NA end of April and visit this crammer class https://www.idug.org/p/cm/ld/fid=1268 for Db2/z V11. You can also do the certification tests on site (partially for free).
      And if you never have been to an IDUG conference before, I can offer you the mentor program aka ‘Bring a Friend’.
      Ask you boss, this combined offer is much cheaper than visiting a class for certification testing.
      Cheers
      Roland
      P.S. There are also one day events in Bangalore or Chennai soon. Maybe this is closer for you, but they don’t have education classes for certification test.

  4. You should find Susan Lawson’s certification guide book which was written for this purpose. Susan has several very books for DB2 z for different versions. A quick amazon search should find what you need.

  5. Hi I would like to ask 1 Question..

    In My Environment we are using windows server in db2 luw . i want to take fullonline backup in local drive ‘ E ‘ and Restore to local ‘ D ‘ can you tel me step by step procedure

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.