DB2 - Introduction




DB2 Introduction

The concept of "packages" in the DB2 Universal Database (UDB) system is often not well understood. This paper provides both general information about what packages are and how they are created and bound, as well as specific information about both the DataDirect Connect for ODBC (32-bit) and DataDirect Connect64 for ODBC (64-bit) drivers.

DataDirect Connect for ODBC and DataDirect Connect64 for ODBC each include a DB2 Wire Protocol ODBC driver that connects to DB2 UDB on Windows, UNIX/Linux, and mainframe servers without the use of DB2 UDB client software. The drivers automatically create and bind packages on DB2 UDB and also allow the user to modify these packages.

In this paper, the term "SQL Request" is used as a generic term referring to all application requests associated with a SQL statement. Internally, DB2 UDB processing actually works in terms of SQL requests rather than SQL statements.

When DB2 UDB processes SQL statements, each individual SQL request is mapped to a specific package. For a SQL statement to be processed, it must be associated with a DB2 UDB package. This is the case for application requests such as PREPARE, FETCH, and CLOSE. A more precise statement of how DB2 UDB processes SQL requests is to say that each individual request is mapped to a specific section entry within a specific package within DB2 UDB.

What is DB2 Introduction?

DB2 is a Relational DataBase Management System (RDBMS) originally introduced by IBM in 1983 to run on its MVS (Multiple Virtual Storage) mainframe platform. The name refers to the shift from the then prevalent hierarchical database model to the new relational model. Although DB2 was initially designed to work exclusively on IBM mainframe platforms, it was later ported to other widely used operating systems like UNIX, Windows and presently in Linux. DB2 is an integral part of IBM’s information management portfolio. It is a full-featured, high-performance database engine capable of handling large quantities of data and concurrently serving many users.

Db2 allows for enhanced operational database management solutions thanks to its on-premises and cloud-based options, industry-leading data compression, mixed workload performance optimization, modern data security, and flexible scaling and readjustment. Data warehouse solutions include the standard SQL language engine, which encompasses a wide range of data structure and types, making it inclusive for any business.

Another Db2 solution from IBM is Data Lake. Data Lake is an analytics solution focused on unlocking value from unstructured data sets. Known for its agility in helping enterprises build data-backed solutions, tapping into otherwise unstructured data sets provides another useful resource for building real-time analytics programs and applications. Syncsort can help you optimize your Db2 environment with our Elevate DB2 suite of Db2 performance tools. Improve running times and reduce resource costs by improving your SQP execution metrics. Contact Syncsort today to learn more.

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.

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.

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.

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.

What is a package?

A package is a cluster of information that controls the compilation of any static SQL statement, partially controls the compilation of any dynamic SQL statement, and influences the execution of any SQL request issued within its scope. Package information includes items such as the optimization level used during compilation, whether blocking is used for eligible cursors during execution, and what degree of parallelism is used during execution.

All of this information is stored as an entry representing the package in the SYSCAT.PACKAGES catalog table. For static SQL statements, a package also has a section associated with it for each statement. A section is the DB2 UDB executable version of a SQL statement. A package is used by DB2 UDB as a basic authorization control point. Privileges can be granted and revoked on the package as required to permit people to execute or maintain the package. This information is reflected in SYSCAT.PACKAGEAUTH.

Why are packages necessary?

Packages must exist for the user to be able to execute SQL statements against DB2 UDB. An application can be written using pure static SQL, a mix of static and dynamic SQL, or pure dynamic SQL. All of these approaches are supported within DB2 UDB through the use of packages. Static SQL statements are pre-compiled and have a pre-existing access path and therefore do not have to be compiled by the DB2 SQL compiler at run time. They are executed under the authorization of the user who bound the package rather than under that of the run-time user.

Dynamic SQL statements are compiled at run time, under the authorization of the run-time user, and must be used when all or part of the SQL statement is generated during application run time. This is the most common case with ODBC applications. Dynamic SQL statements incur a higher startup cost, but do not have to be recompiled when they are reused. They also use a more optimized access path based on current database statistics.

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.

How do DB2 accelerators work?

The IDAA appliance connects to z/OS and DB2 using a private network. The DB2 objects to be accelerated are defined and loaded in IDAA using DB2 stored procedures and a graphical user interface. Just configure a few settings and choices, and the installation is ready. Once an accelerator is installed and activated, the DB2 optimizer treats the appliance as a new access path and will offload SQL processing transparently when it is more efficient to do so. Not every query may be accelerated. Some may have incompatible SQL statements, or the DB2 optimizer may decide that offload is not the optimal choice.* Alternatively, you can also configure the optimizer to send every eligible query to the accelerator—regardless of any costing or heuristic consideration.