IBM DB2 for i frequently asked questions

What is DB2 for i ?

DB2 for i is the relational database management system (RDBMS) that is built into the IBM i Operating System that runs on IBM’s Power Systems Servers. It is an extensible, high performance, scalable database that adheres to many industry standards while leveraging the IBM i architecture to maintain its value proposition of lower total cost of ownership.

Do I need to buy DB2 for i?

DB2 for i is built into IBM i at no additional charge. This includes the core database management system, the SQL/Query engine, storage management, security, and more. There are several features that you can add onto the core database functionality, including products for data replication, parallel processing, and Business Intelligence.

What level of IBM i supports DB2 for i?

DB2 for i is not relevant to a specific OS (Operating System) level, but rather, built into the OS at all levels. There is only one RDBMS within IBM i. The current level of DB2 for i is Version 7 Release 1 (7.1). The functionality of DB2 for i will differ with each version/release of IBM i, as new enhancements are introduced with each new version.

How does DB2 for i compare to DB2 for Linux/UNIX/Windows (LUW)?

All the DB2 family members share common attributes (like SQL Standard syntax support), but also differ in their optimization for the specific platform and operating environments, and differ in packaging and in some administrative areas. At any point in time, feature/function differences may exist as well, depending on delivery schedules and market requirements.

SQL Syntax is very common across the DB2 products.

Version/Release naming will differ because, as mentioned above, DB2 for i follows the IBM i version/release numbering scheme, and not the DB2 LUW version/release scheme (you should not assume a comparison of version/release is a reflection on functional equivalency).

Because DB2 is included as part of IBM i, packaging is obviously different, whereas DB2 for Linux/UNIX/Windows is a common code base that can be installed on many different operating systems and platforms.

Graphical tools to administer the database are provided as part of System i Navigator component of IBM i, whereas DB2’s Control Center would be used to administer DB2 UDB for LUW. Other “centers”, such as Replication Center, Development Center, and Warehouse Center can be used across all DB2 products.

Does DB2 for i support “PureXML?”

DB2 LUW offers a feature called “pureXML” which provides a capability to store XML documents in DB2, and access them via XML-like interfaces. DB2 for i contains built-in XML integration functions compatible with the DB2 family, including the ability to store XML documents in columns of DB2, and XML publishing and other integration functions. Add the no charge Omnifind Text Search product to provide sophisticated text search capabilities over XML (or other) documents stored in DB2.

Is DB2 for i “optimized” for SAP?

Yes. For many years, there has been a joint development team between DB2 for i and SAP. In addition to this joint team, the SAP and DB2 development teams work very closely together through a single IBM focal point, ensuring requirements from SAP are translated into compatible features across our DB2 portfolio. This ensures that requirements that are implemented in DB2 for Linux/UNIX/Windows will be implemented in a consistent manner in DB2 for i and vice versa.

Some of the specific features added to DB2 for i to optimize for SAP include:

More DB2 features that are designed to meet SAP future needs are already being developed for the next release and still more items for are planned for the following release.

Do I need DB2 Connect to connect my Linux, UNIX, or Windows clients to DB2 for i?

The answer is that DB2 Connect can be used, but it is not necessarily a requirement. DB2 Connect can be used to connect applications from those environments to any DB2 server via ODBC, .NET, JDBC, or using CLI/DRDA interfaces. So if the only connectivity required is from Windows or Linux, to DB2 for i (no other DB2 family member), you might consider the IBM i Access for Windows middleware (ODBC, OLE DB, .NET) or Java Toolbox JDBC drivers available at no charge with the IBM i operating system. However, DB2 Connect also includes additional programmer productivity features through .NET (Visual Studio) or PHP (Zend Studio) plug-ins for DB2. DB2 Connect can also be used for DB2 data “federation”, such as the ability to join data from two DB2 (or Informix) databases with a single SQL statement. DB2 Connect’s mobility on demand feature includes a synchronization server to replicate (and synchronize) data from DB2 Servers with DB2 products running in a mobile device.

Can you run DB2 InfoSphere Warehouse on a Power System Server in an IBM i partition?

Infosphere Warehouse is a packaging of DB2 for Linux/UNIX/Windows (LUW) with some additional data warehouse oriented tools. The elements of Infosphere Warehouse do not support IBM i, but can certainly run in a Linux or AIX partition on a Power System server.

Other database systems tout the ability to do tasks in parallel or to partition data for better performance. Can DB2 for i do that?

Yes. DB2 for i introduced parallel database functionality, also known as SMP (Symmetric Multi-Processing) capability in V3R1 (1995), and has enhanced those functions significantly since then. The DB2 for i SMP feature is an operating system add-on that can significantly improve performance of database tasks by breaking the processing across multiple CPUs. By doing this, SMP optimizes the available resources to complete the database task in a cost-effective manner.

In addition, DB2 for i, with the DB2 Multisystem feature, can also partition data across multiple servers/partitions, or partition data locally (within the same server/partition). One of the key benefits of this is to expand the size of a single database table/file, without application changes. However, it should be noted that other databases use the notion of “data partitioning” to address performance issues. Because these databases do not employ single-level storage (single-level storage is a fundamental design point of IBM i), they are restricted on how much space they can address. Furthermore, they cannot effectively use all the processors on a single box for a single unit of work. Database partitions enable them to overcome both problems.

Because of Single-Level Storage, and the ability for near-linear scalability in an SMP environment, “data partitioning” as it is called in other database management systems is not required, and this also reduces the amount of technical resource required to manage the partitioned environment. For IBM i customers interested in data partitioning, a technical review is highly recommended.

DB2 for i DBAs are not readily available in the inventory of available skills in the marketplace. I can find lots of DBAs for other database products. Wouldn’t an investment in one of these other databases be a better solution?

Availability of DBA skills on the marketplace is a subject that requires some further investigation. For instance, keep in mind that (a) many typical DBA tasks required in other database products are not required on DB2 for i, (b) DBAs have many skills that are transferable to DB2 for i, and (c) the DBA “community” has evolved because of the need for this specific skill on other database management systems.

As noted, many of the skills of a DBA are common across all relational databases. This includes knowledge of SQL, relational data modeling, indexing, and query optimization. With the exception of query optimization, learning these skills on DB2 for i is a matter of learning new user interfaces.

Query optimization on DB2 for i will require some education for the DBA. IBM has developed a four day class called “DB2 for i SQL Performance Tuning and Monitoring Workshop.”

Lastly, it is important to note that many of the tasks to manage other databases are not required with DB2 for i. These tasks may include creation and monitoring of tablespaces, logs, and cache area disk usage, re-balancing of indexes, running utilities to gather statistics, and partitioning of data. The IBM i architecture and sophisticated cost-based optimizer of DB2 for i automates these processes.

Does DB2 for i require a DBA?

Maybe! Again, a “traditional” DBA will spend more of their time with DB2 for i doing data modeling, SQL coding, developing indexing strategies, and SQL optimization. If the application is SQL based, this is a critical element to ensuring appropriate design and SQL optimization. A person with an understanding of SQL optimization is highly recommended to fully leverage the benefits of DB2 for i.

But as traditionally defined, typical tasks required by other database systems will NOT be required due to the advanced architecture of IBM i. Many of the typical DBA tasks are handled automatically by the OS (data striping, collection of statistics for use by the SQL Optimizer, managing log or buffer space).

What are DDS and RPG? Am I really using DB2 or a flat file system?

DB2 for i has many programming and operational interfaces available for application developers, DBAs, and operations/support personnel. However, the database engine is still the same regardless of the interface being used. With this architecture, you actually have MORE flexibility in how you use the database and what skills are available in the marketplace to help develop or support.

DDS (Data Description Specifications) provide one option to define database tables and indexes. RPG is a common programming language in the IBM i world. These interfaces, commonly referred to as “native” interfaces, were in place before SQL became an industry standard and continue to be used heavily in an IBM i environment.

DB2 for i also supports SQL interfaces, and you can create tables and indexes through SQL, generally considered the “open” standard for database development. DB2 for i also supports many different high level languages (e.g., Cobol, JAVA), 4GL and CASE tools, and web or client/server based interfaces (JDBC, ODBC, Microsoft’s .NET framework) to allow a tremendous amount of flexibility while still leveraging the underlying advantages of the single RDBMS!

Where can I find more education on DB2 for i?

Contact IBM

Browse Power Systems

Next generation applications for big data and analytics and cognitive computing are providing unprecedented insights into opportunities, threats and efficiencies. IBM Power Systems is at the forefront of delivering solutions to gain faster insights from analyzing both structured information and unstructured big data. With the secure, flexible and open platform of IBM Power Systems plus solutions and software, organizations can outpace their competitors by delivering faster services, providing differentiated offerings and turning operational cost into investment opportunity.

To draw insights and make better decisions, businesses rely on the secure, flexible and open platform of IBM Power Systems. Built with the first processor designed for big data workloads, the design of Power Systems combines the computing power, memory bandwidth and I/O in ways that are easier to consume and manage, building on strong resiliency, availability and security.

IBM Power Systems deliver flexibility and choice of operating systems to enable your business to support the next generation applications for big data and analytics and cognitive computing that are transforming how organizations work today. Whether running 1, 2, or all 3 - coupled with PowerVM, they maximize the benefit of Power Systems in your business.

Transform your business with Systems Software that enables virtualization, high availability, flexibility, security and compliance on Power Systems™. IBM’s integrated approach to developing Systems and Systems Software together delivers optimized results with Power Systems.

As an open innovation platform, Power Systems is optimized for big data and analytics performance and to deliver scale-out economics and security for the cloud. IBM and IBM Business Partner solutions exploit key capabilities in IBM Power Systems.

Over the last five years thousands of clients have migrated to IBM Power Systems. Learn how Power Systems has helped them support next generation applications for big data and analytics and cognitive computing on an open platform for choice while improving business performance, reducing risk, and establishing a platform for growth.

Additional information