Last year, I think it was in March 2016, IBM introduced a new certification test for DB2 for z/OS - C2090-320 DB2 11 Fundamentals for z/OS. It is an entry level test that opens the door for other more advanced certifications, like DB2 Database or System Administration. Before this test was introduced you had to pass DB2 Fundamentals for LUW and z/OS, whose content was mostly very common, but there were also some specific parts for LUW only and some specifics for z/OS were missing. That has changed with this new test and you can focus on DB2 for z/OS only. You can see more in the certification roadmap, which is unfortunately not up to date, so use this link later for future updates.

Now, is this DB2 11 Fundamentals worth doing or not? Of course it depends. I know that taking the exam is not the cheapest thing, but on the other hand, IBM provides the exams for free at several events like IDUG conferences. I also think that just preparing for an exam is worthwhile because you need to study materials, which can help you in understanding the technology better. Because this is an entry level exam designed for the beginners, it is not hard if you already have some experience with DB2 for z/OS. The Fundamentals exam is also required to obtain the certification for the advanced certifications.

OK, if you've read until this point, I believe I attracted your attention. So, where do you find any good information for studying? First, you can start at the IBM certification webpage. You can see some details about the certification (IBM Certified Database Associate - DB2 11 Fundamentals for z/OS) and the corresponding test (Test C2090-320: DB2 11 Fundamentals for z/OS). Specifically, the objectives should be your landing page. Here you can see what's really required to pass the exam. As you can see, there are seven sections, which we will briefly visit later in this article, and for each there is a percentage of questions in the test. I would also like to point out one important thing, there is a sample test you can take right now for free! And don't worry, the answer key is at the end. This should give you some basic assessment of your DB2 knowledge and how much you are fluent with DB2.

As far as I know, there is no certification guide published that could help you study for the exam. However, there are other sources. For basic SQL and non z/OS specific chapters, you could use two sources available for DB2 10.1 LUW: (1) Database fundamentals ebook, and (2) The Preparation guide for DB2 LUW 10.1. However, I believe that what you need is mostly contained in the DB2 11 for z/OS documentation, especially in the Introduction to DB2 for z/OS manual. I admit that the overall documentation is pretty huge, but we will try to sort that out a bit and provide useful links of where to start. In this article we will provide a few links to the documentation that should guide you through the preparation for the aforementioned exam, or at least it should give you some starting points.

Ready? Then, let's go through the test sections and subsections with a few comments. If you have any other notes, please share them in the comments below.

Section 1 - Working with SQL and XML (14%)

This section should cover the basic to intermediate knowledge of SQL. If you already have some prior experience with SQL this should be no problem for you. Maybe XML might be more complicated, but here are some links for learning more.

Basic ability to write a DML SQL statement

• DML (Data Manipulation Language) basically covers SELECT, INSERT, UPDATE, and DELETE
• The Introduction to DB2 manual includes very basic information about accessing the data, how the SELECT statement works, including more advanced techniques like table joins and subqueries.
• Also review the examples of subselects.
• Here you can find basic information about how to modify DB2 data using INSERT, UPDATE, DELETE, but also MERGE, and TRUNCATE.
• The previous links should give you the basic knowledge about the DML, however, if you want to learn more, refer to the Application developer guide for more details. Especially, the section adding and modifying data, and accessing the data.
• There is also a short tutorial about querying and modifying data using SPUFI.

Basic ability to access and process XML data (XQuery, Xpath)

• XML might be more complex, depending on your prior knowledge. You should probably start reading here, where you will find the introduction and links to other relevant materials.
• To see a basic workflow in XML, make sure to review this short tutorial. I believe that understanding this is almost sufficient for this test.
• For more details, see this section, which describes the ways to retrieve the XML data. Namely, how to retrieve an entire XML document and how to retrieve a portion of XML document using the XMLQUERY
• You will use XQuery or XPath as arguments in the XMLQUERY function, see an overview of those technologies.
• There are two ways of updating the XML columns - updating the entire document, and updating only a part of the XML document.

Basic knowledge of most commonly used special registers

• The documentation says that "A special register is a storage area that is defined for an application process by DB2® and is used to store information that can be referenced in SQL statements". You can see a full list of special registers here.
• Also refer to this section for general rules how to work with special registers.
• The question is what are the most commonly used special registers, it probably depends on the application, but definitely you should understand the different groups of special registers, for example:
  • schema and server identification - CURRENT SCHEMA, CURRENT SERVER, and CURRENT SQLID.
  • date/time registers - CURRENT TIMESTAMP, CURRENT DATE, CURRENT TIME, and CURRENT TIMEZONE
  • temporal special registers - CURRENT TEMPORAL BUSINESS_TIME and CURRENT TEMPORAL SYSTEM_TIME
  • client identification - like CURRENT CLIENT_APPLNAME and CURRENT CLIENT_USERID

Basic knowledge of built-in functions

• DB2 comes with a set of built-in functions, see the common types - aggregate functions, scalar functions, table functions, and row
• The most common aggregate functions are probably AVG, COUNT, and SUM.
• There are really many scalar functions, covering the basic math functions, timestamp conversion, character conversions and stripping, and other conversions. But there are many others, for example COALESCE, which returns a first non-NULL value, similarly to NULLIF.
• From the table functions, I would point out mainly the XMLTABLE function that returns a table from a XQuery expression.

Section 2 - Security (8%)

Security is one of the most complex things in my opinion. Moreover, if you think you understand it, there is always one more thing.. Take it easy and look what basic concepts we need to understand here.

Basic knowledge of restricting data access (authorities, privileges, views, profiles, roles, trusted contexts)

• In this section of the exam you should understand the basic ways how to restrict access to data. There is a nice overview of the means you can use to secure your data.
• See the basic information about the authorities and privileges and how to grant and revoke For more details refer to this advanced section of the Managing Security manual.
• Database views are an elegant and simple means to restrict and provide access to data.
• A Database Role groups together one or more privileges. You can grant privileges to a role using the GRANT statement.
• A Trusted context enables the establishment of a trusted relationship between DB2 and an external entity via an authorization ID and trust attributes, which specify characteristics of a connection (for example IP address, domain name, job name, …). A trusted context can have a default role, specific roles for individual users, or no roles.
• For learning more about the security profiles, see this

Basic ability to write a Data Control Language (DCL) SQL statement

• The data control language basically covers GRANT and REVOKE
• You can learn much more details here in the Managing Security guide.

Section 3 - Planning (17%)

Planning section covers topics that you should understand before using DB2 for z/OS as your database server.

Basic ability to connect to DB2 servers (demonstrate ability to use remote access)

• You can connect to DB2 for z/OS remotely using the DDF via DRDA
• In the documentation, you can see some examples how to connect to DB2.

Basic knowledge of different types of tables (Base, MQT, Auxiliary, Partitioned, Temporal at a high level; when is it appropriate to use each type) and table spaces

• Let's start with different types of DB2 tables:
  • Base tables are the most common table type and contain the rows of data with columns of the defined types.
  • MQTs (or Materialized Query Tables) contain pre-computed results of complex queris
  • Partitioned tables partition the tables according to defined column values. Keep in mind that not all data types can be used in the partition expression, see details by the PARTITIONING clause of CREATE TABLE.
  • There are three types of temporal tables - application period, system period, and bi-temporal tables. You can learn more details here.
• DB2 tables are stored in DB2 tablespaces, which are basically storage structures, while tables are logical structures. There are several types of tablespaces, some of them are historical and deprecated. For the new development you should focus on Universal table spaces - Partition by growth and range partitioned tablespaces.
• If you use LOBs (Large Objects), you will need LOB tablespaces, which store the auxiliary tables. Similarly, for XML, there are XML DB2 creates the XML tablespaces implicitly. XML and LOB tablespaces are always associated with the base tablespace containing the base table that has XML or LOB columns.

Basic knowledge of subsystem parameters

• DB2 subsystem parameters (also called ZPARMS) are settings that apply to an entire DB2 subsystem.
• You can see a list of all subsystem parameters, but I don't think it is necessary to memorize them all.

Basic knowledge of DB2 architecture (address spaces, logs, IRLM)

• This section introduces the components of the DB2 for z/OS, especially its main address spaces.
• IRLM (Internal Lock manager) serializes access to the DB2 data and controls the locking.
• DB2 log contains the information needed to recover the results of program information. DB2 log is crucial to support ACID You can learn more about transactional log on wikipedia.

Basic knowledge of data sharing concepts

• DB2 data sharing enables applications running on multiple DB2 subsystems (members) of the group to read and modify the same data.
• Here is a list of advantages of DB2 data sharing.
• It is important to understand how DB2 protects the consistency and an how an update is made in the data sharing group.

Basic knowledge of database workloads (transactional processing vs. analytics)

• See the sample scenarios for using DB2 for differences between transactional and analytics

Basic knowledge of encoding scheme concepts

• See the following section for an introduction into encoding.
• Make sure to understand when and how the character conversion works, see the information about the Character conversions.
• Much more details can be found in the Internationalization guide.

Section 4 - Operations (14%)

By operations it is usually meant the management of the data and the DB2 subsystem so that the applications can run seamlessly.

Basic knowledge of DB2 commands and DSN commands

• See an overview of various commands, including DB2 and DSN commands. This section describes the types of commands.
• Here is a list of all DB2 commands.
• DSN commands are executed via TSO command processor, and probably the most common used are BIND and RUN.

Basic knowledge of DB2 utilities

• DB2 utilities help to maintain the data in your databases, see here for a brief overview and how to run the utilities.
• Also make sure you understand the types of the utilities, especially the difference between the online and offline utilities, and what are the utilities for data backup and recovery and why it is important to run them regularly.

Basic knowledge of troubleshooting (Explain, SQL Codes)

• During the operations various types of errors or problems may appear. Here is an introduction to troubleshooting such problems.
• From the application developer perspective, it is important to understand the SQL codes and how to check the execution of SQL statements.
• For helping to solve the application performance problems, see how to use the EXPLAIN

Section 5 - Data Concurrency (10%)

Data concurrency is one of the most overlooked issues during the application development. It is usually because the test environment is not that complex as the real production environment and as such does not have the huge amount of concurrently running applications processing the same data.

Basic knowledge of transaction management (COMMIT, ROLLBACK, AUTOCOMMIT and SAVEPOINT)

• This topic is really crucial in order to understand how DB2 controls the updates to data.
• You need to understand the transactions and application processes.
• Commit, rollback, and savepoint operations are described in this Learn more about undoing selected changes by using save points here.
• Please note that some types of applications use so called autocommit, which means that each statement is treated as a single and complete transaction. This is usually used by some JDBC and ODBC

Basic knowledge of locking

• DB2 uses locks to maintain the data integrity.
• See this section for types of locks and problems that may appear with concurrent applications.

Given a situation, basic knowledge to identify the isolation levels that should be used

• Isolation levels are a core mean for managing the isolation of concurrently running applications. For details, what isolation level to choose, refer to the Managing performance guide.
• Review the recommendations for database designers and for application designers.

Section 6 - Application Design (19%)

Application design usually has to deal with various aspects of the planned application. This fundamentals exam covers only a handful of basic points you need to consider for your applications.

Basic ability to create and call a stored procedure or a user defined function (understanding of passing parameters and obtaining results)

• An introduction to Stored procedures and their types can be found here.
• Refer to this section for an introduction about creating a stored procedure, especially the Stored procedures
• Applications can call a stored procedure using a CALL Much more details on that topic can be found here.
• Similarly to the previous bullets, this is an overview of the DB2 functions.
• How to create and call a function. More details on that topic is here.

Basic knowledge of temporary tables (how they are created and when they should be used)

• DB2 provides two types of temporary tables - Created temporary tables and Declared temporary tables; see the main differences here.
• Both types are available to application developers for storing intermediate data.
• Created temporary tables have the definition in the DB2 catalog, but each process has its own instance.
• Declared temporary tables are defined by the application and the declaration is not saved in the catalog. The qualifier for the Declared temporary is SESSION, and each instance of the application has its own instance of such table.

Basic knowledge of triggers (how they function; when they might be used)

• Triggers define actions that are executed when a delete, insert, or update operations occurs on a specified table.
• Triggers are created by a CREATE TRIGGER More details, including examples for different types of triggers can be found here.

Basic knowledge of program preparation and BIND options

• If you are creating a program with static SQL, you need to follow the preparation process. Here is an overview of the process.
• See this section for more details about the individual steps - precompile, compile, bind.
• Here is an overview of the bind process and the BIND options.

Basic knowledge of referential integrity and constraints

• DB2 provides several types of constraints - referential constraints, check and unique constraints.
• DB2 ensures the referential integrity between the tables that have referential constraints. Here is an introduction of how to create a relationship between two tables.
• The most important thing is to understand the referential constraints rules.
• For more details, see this section for referential constraints rules as well as how to define the parent keys.
• Check constraint is a rule that specifies what values are allowed in one or more columns. See some examples for check constraints here.
• Unique constraint is a rule guaranteeing that the value for a column is valid only if it is unique.

Basic knowledge of non-relational data concepts (XML data, LOB data)

• Besides the traditional relation data, DB2 can also store XML, LOB, or JSON data.
• DB2 saves XML in its hierarchical form, this technology is called pureXML.
• See the benefits of XML in DB2 and the ways to use it.
• A XML tablespace is created implicitly when you create an XML column in a base table. Each XML column has its own tablespace. Here is an overview of the storage structure of XML data.
• LOBs (Large Objects) can store graphics, video, binary or even large text strings.
• See an example how to create a LOB tablespace.

Basic knowledge of Temporal (Time Travel) Tables - System-period, Application-period, and Bi-temporal - ability to create and query

• In a previous section, we have already discussed the types of temporal
• See an example how the system temporal tables can be used for auditing.
• It is important to understand how to query the temporal tables and how to use the special registers - CURRENT TEMPORAL BUSINESS TIME and CURRENT TEMPORAL SYSTEM_TIME.

Section 7 - Working with Database Objects (17%)

The ability to work with the database objects is common to all roles in the DB2 environment. You should make yourself familiar how to create and use the database objects like tables and indexes.

Basic ability to demonstrate usage of IBM-supplied and user-defined data types

• IBM supplies a set of data types available for use in your tables.
• Users can define distinct types and arrays.

Basic ability to write a DDL SQL statement

• DDL (Data Definition language) helps to create, modify, and drop the DB2 objects. For example:
  • CREATE DATABASE creates a database.
  • CREATE TABLESPACE creates a tablespace
  • CREATE TABLE creates a table
  • CREATE INDEX creates an index
• The Programming for DB2 guide has an entire chapter on how to create and modify database objects.
• Usually, this is a part of the database administrator job, so you can find more details in the DB2 Administration guide.

Basic ability to identify characteristics and properties of DB2 objects (Tables, Indexes, Views,…)

• You can see the characteristics of DB2 objects by their corresponding CREATE statements, for example CREATE TABLE, CREATE INDEX, CREATE VIEW, and other statements.
• In case of views it is important to understand how inserts and updates work through a view and how to identify a read-only view.
• In case of indexes, what are the types of indexes and their attributes.

Basic ability to look up information in the DB2 catalog

• DB2 catalog is a database contain metadata about the data that DB2 controls.
• There are many catalog tables that contain information about the DB2 objects, for example:
  • SYSTABLES contains the information about tables and views,
  • SYSTABLESPACE contains the information for each tablespace from the logical perspective,
  • refer to the SYSTABLEPART for information about each partition and its underlying dataset,
  • SYSINDEXES provides information about each index,
  • for information about each index dataset, refer to the SYSINDEXPART,
  • SYSINDEXSPACESTATS and SYSIBM.SYSTABLESPACESTATS contain the real time statistics for indexes and tablespaces.
• You can find many interesting catalog queries in this section of the documentation.
• The DSNTESQ member of the SAMPLIB library contains many SQLs to verify the integrity of the DB2 catalog and where you can find important relations between the catalog objects.