DB2 Exam C2090-320 Preparation Guide

Introduction

Welcome

Congratulations on your purchase of DB2 Exam C2090-320 Preparation Guide.  This book will help you pass IBM Exam C2090-320 and become an IBM Certified Database Associate - DB2 11 Fundamentals for z/OS.   It will provide you with instruction, examples and questions/answers to help you learn and to gauge your readiness for the exam.  You’ll get a good exposure to the DB2 11 knowledge you’ll be tested on.  

Why You Should Certify

Earn More Money

According to the 2017 IT SKILLS and SALARY REPORT, the salary difference between certified and non-certified IT staff is $8400 or about 11.7 percent more!  See page 6.  Certification pays!

https://mindhubpro.pearsonvue.com/v/vspfiles/documents/2017_Global_Knowledge_SalaryReport.pdf

Get Better Work Assignments

When you are recognized as a certified expert in a technology, you are more likely to be assigned to high profile jobs, such as developing new systems.  Get DB2 certified and be recognized when the new projects are staffed!

Master Your Profession

Getting certified requires you to learn more about DB2 capabilities and features than non-certified staff who have often used only a small percentage of DB2 capabilities.  Through certification, you gain greater mastery of your profession – you become an expert!

How to Pass the IBM C2090-320 Exam

To maximize your chances of passing IBM Exam C2090-320, I recommend that you acquire three things:

1.   Knowledge of DB2 11.

2.   Experience with DB2 11.

3.   Practice with answering DB2 11 exam questions.

Knowledge of DB2 11

My personal experience with IBM exams is that they do not ask tricky or convoluted questions.  However exam C2090-320 will seriously test your knowledge of DB2 11.  Even if you have several years of experience with DB2, you may find yourself challenged by some of the topics.  Also, the amount of detail in the IBM product manuals can be overwhelming.  Most people will find this study guide to be helpfully focused on exam objectives as opposed to covering every minute detail of DB2.  

Experience with DB2 11

Unless your shop has already upgraded to DB2 11, you may not have any experience with it.  Still, much of the information you will be tested on has not changed over the last few versions of DB2.  You’ll see some familiar topics on the objectives list, plus a few that are probably unfamiliar.  My  suggestion is to practice with whatever version of DB2 you have, and then put some extra study effort on the new features of DB2 11.

Practice Exam Questions for DB2 11

Besides the study guide you are reading, I recommend a companion book I wrote entitled DB2 Exam C2090-320 Practice Questions.   It contains 180+ questions and answers to give you an idea of the kinds of questions to expect and to help you to gauge your readiness to take the exam.  Lots of questions and answers, lots of drilling for the exam! 

Knowledge, experience and practice questions.  Will that guarantee that you’ll pass the exam?  Of course, nothing is guaranteed in life.  But if you put sufficient effort into a well-rounded study plan that includes all three of the above, I believe you have a very good chance of passing exam C2090-320 on the first try.    

Finally, thanks for your purchase of this book.  If you feel that this study aid helped you in preparing for your IBM DB2 exam, please leave a positive book review at the place you bought it. I’ll really appreciate that.  

Thanks and good luck!

Robert Wingate

IBM Certified Application Developer – DB2 11 for z/OS

C2090-320 Exam Objectives

IBM Certified Database Associate - DB2 11 Fundamentals for z/OS

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

a.  Basic ability to write a DML SQL statement

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

c.  Basic knowledge of most commonly used special registers

d.  Basic knowledge of built-in functions

Section 2 - Security (8%)

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

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

Section 3 - Planning (17%)

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

b.  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

c.  Basic knowledge of subsystem parameters

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

e.  Basic knowledge of data sharing concepts

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

g.  Basic knowledge of encoding scheme concepts

Section 4 - Operations (14%)

a.  Basic knowledge of DB2 commands and DSN commands

b.  Basic knowledge of DB2 utilities

c.  Basic knowledge of troubleshooting (Explain, SQL Codes)

Section 5 - Data Concurrency (10%)

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

b.  Basic knowledge of locking

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

Section 6 - Application Design (19%)

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

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

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

d.  Basic knowledge of program preparation and BIND options

e.  Basic knowledge of referential integrity and constraints

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

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

Section 7 - Working with Database Objects (17%)

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

b.  Basic ability to write a DDL SQL statement

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

d.  Basic ability to look up information in the DB2 catalog

Working with SQL and XML

Overview

Data Manipulation Language (DML) is used to add, change and delete data in a DB2 table.  DML is one of the most basic and essential skills you must have as a DB2 professional.  In this section we’ll look at the five major DML statements:  INSERT, UPDATE, DELETE, MERGE and SELECT.  Yes, some would argue that SELECT is not actually a data manipulation verb, but I cannot imagine an IBM exam that doesn’t include some questions about SELECT.  So we’ll definitely cover it.

XML data access and processing is another skill that you need to be familiar with.  DB2 includes an XML data type and various functions for accessing and processing XML data. I’ll assume you have a basic understanding of XML, but we’ll do a quick review anyway. Then we’ll look at some examples of creating an XML column, populating it, modifying it and manipulating it using XML functions such as XQuery.  We’ll cover some more advanced XML topics in another section of the book.  

Special registers allow you to access detailed information about the DB2 instance settings as well as certain session information.  CURRENT DATE is an example of a special register. You can access special registers in an application program and then use the information. 

Built-in functions can be used in SQL statements to return a result based on an argument.  Think of them as productivity tools in that they can be used to replace custom coded functionality in an application program and thereby simplify development and maintenance. Whether your role is application developer, DBA or business services professional, the DB2 built-in functions can save you a great deal of time if you know what they are and how to use them.

Database, Tablespace and Schema Conventions

Throughout this book we will be using a database called DBHR which is a database for a fictitious human relations department in a company.  The main tablespace we will us is TSHR.  Finally, our default schema will be HRSCHEMA.  In some cases we will explicitly specify the schema in our DDL or SQL.  If we don’t explicitly specify a schema, it means we have defined the HRSCHEMA schema as the CURRENT SCHEMA so we don’t need to specify it. 

If you are following along and creating examples on your own system, you may of course use whatever database and schema is available to you on your system.  If you want the basic DDL to create the objects named above, it is as follows:

CREATE DATABASE DBHR;  COMMIT WORK;

CONNECT TO DBHR USER USING ;

CREATE TABLESPACE DBHR; COMMIT WORK;

CREATE SCHEMA HRSCHEMA AUTHORIZATION

Basic ability to write a DML SQL statement

Data Manipulation Language (DML) is at the core of working with relational databases. You need to be very comfortable with DML statements:  INSERT, UPDATE, DELETE, MERGE and SELECT.  We’ll cover the syntax and use of each of these.  For purposes of this section, let’s plan and create a very simple table. Here are the columns and data types for our table which we will name EMPLOYEE.

The table can be created with the following DDL:

CREATE TABLE HRSCHEMA.EMPLOYEE(             

EMP_ID INT NOT NULL,               

EMP_LAST_NAME VARCHAR(30) NOT NULL,

EMP_FIRST_NAME VARCHAR(20) NOT NULL,

EMP_SERVICE_YEARS INT NOT NULL WITH DEFAULT 0,           

EMP_PROMOTION_DATE DATE,           

PRIMARY KEY(EMP_ID));

We also need to create a unique index to support the primary key:

CREATE UNIQUE INDEX NDX_EMPLOYEE  

ON EMPLOYEE (EMP_ID);       

INSERT Statement

The INSERT statement adds one or more rows to a table.  There are three forms of the INSERT statement and you need to know the syntax of each of these.   

1.   Insert via values

2.   Insert via select

3.   Insert via FOR N ROWS

Insert Via Values

There are actually two sub-forms of the insert by values.  One form explicitly names the target fields and the other does not. Generally when inserting a record you explicitly specify the target fields, followed by a VALUES clause that includes the actual values to apply to the new record. Let’s use our EMPLOYEE table for this example:

INSERT INTO EMPLOYEE

(EMP_ID,            

 EMP_LAST_NAME,     

 EMP_FIRST_NAME,    

 EMP_SERVICE_YEARS, 

 EMP_PROMOTION_DATE)

VALUES (3217,       

'JOHNSON',          

'EDWARD',           

4,                  

'01/01/2017')

A second sub-form of the INSERT statement via values is to omit the target fields and simply provide the VALUES clause.  You can do this only if your values clause includes values for ALL the columns in the correct positional order. 

Here’s an example of this second sub-form of insert via values:

INSERT INTO EMPLOYEE  

VALUES (7459,         

'STEWART',            

'BETTY',              

7,                    

'07/31/2016')         

Some additional rules to remember for the INSERT statement: 

1.   If a column is defined with a DEFAULT value, and you want to assign that default value to the column, then you can simply specify the DEFAULT keyword for that column in the values clause.  DB2 will automatically populate the field with its default value.

2.   Each column that is defined as NOT NULL must have an entry in the values clause.  If the not-nullable column has a DEFAULT value attribute, you can specify DEFAULT in the values clause.  If you do not specify DEFAULT, then you must assign a specific value for this column in the values clause or an error will result.

3.   If a column is not defined as NOT NULL (or if it is explicitly defined as NULL meaning NULL values are allowed), and you don’t want to assign a value to that column, then you must specify NULL for the column in the values clause. 

4.   Note that EMP_ID is defined as a primary key on the table. If you try inserting a row for which the primary key already exists, you will receive a -803 error SQL code (more on this later when we discuss table objects in detail).

Here’s an example of specifying the DEFAULT value for the EMP_SERVICE_YEARS column, and the NULL value for the EMP_PROMOTION_DATE.

INSERT INTO EMPLOYEE 

(EMP_ID,             

EMP_LAST_NAME,       

EMP_FIRST_NAME,      

EMP_SERVICE_YEARS,   

EMP_PROMOTION_DATE)  

VALUES (9134,        

'FRANKLIN',          

'ROSEMARY',          

DEFAULT,             

NULL);               

When you define a column using WITH DEFAULT, you do not necessarily have to specify the actual default value in your DDL.  DB2 provides implicit default values for most data types and if you just specify WITH DEFAULT and no specific value, the implicit default value will be used. 

In the EMPLOYEE table we specified WITH DEFAULT 0 for the employee’s service years.  However, the implicit default value is also zero because the column is defined as INTEGER.  So we could have simply specified WITH DEFAULT and it would have the same result. 

The following table denotes the default values for the various data types.

Default Values for DB2 Data Types

Before moving on to the Insert via Select, let’s take a look at the data we have in the table so far.

Insert via Select

You can use a SELECT query to extract data from one table and load it to another.  You can even include literals or built in functions in the SELECT query in lieu of column names (if you need them).  This is often useful for loading tables. Let’s do an example.

Suppose you work in HR and you have an employee recognition request table named EMPRECOG.  This table is used to generate/store recognition requests for employees who have been promoted during a certain time frame.  Once the request is fulfilled, the date completed will be populated by HR in a separate process.  The table specification is as follows:

The DDL to create the table might look like this:

CREATE TABLE HRSCHEMA.EMPRECOG(                  

EMP_ID INT NOT NULL,                    

EMP_PROMOTION_DATE DATE NOT NULL,       

EMP_RECOG_RQST_DATE DATE                

NOT NULL WITH DEFAULT,                  

EMP_RECOG_COMP_DATE DATE);

Your objective is to load this table with data from the EMPLOYEE table for any employee whose promotion date occurs during the current month.  The selection criteria could be expressed as:

SELECT                         

EMP_ID,                        

EMP_PROMOTION_DATE             

FROM EMPLOYEE                  

WHERE MONTH(EMP_PROMOTION_DATE)

 = MONTH(CURRENT DATE)         

To use this SQL in an INSERT statement on the EMPRECOG table, you would need to add another column for the request date (EMP_RECOG_RQST_DATE).  Let’s use the CURRENT DATE function to insert today’s date. Now our select statement looks like this:

SELECT                         

EMP_ID,                        

EMP_PROMOTION_DATE,

CURRENT DATE AS RQST_DATE             

FROM HRSCHEMA.EMPLOYEE                  

WHERE MONTH(EMP_PROMOTION_DATE)

    = MONTH(CURRENT DATE)  

Assuming we are running the SQL on January 10, 2017 we should get the following results:

Finally, let’s create the INSERT statement for the EMPRECOG table.  Since our query does not include the EMP_RQST_COMP_DATE (the request complete column will be populated by another HR process when the request is complete), we must specify the target column names we are populating.  Otherwise we will get a mismatch between the number of columns we are loading and the number in the table.

Of course, in circumstances where you have values for all the table’s columns, you needn’t include the column names and you can just use the INSERT INTO and SELECT statement.  But in many cases it is handy to include the target column names, even when you don’t have to.  It makes the DML more self-documenting and helpful for the next developer.

Here is our SQL:

INSERT INTO EMPRECOG            

(EMP_ID,                       

 EMP_PROMOTION_DATE,           

 EMP_RECOG_RQST_DATE)          

 SELECT                          

 EMP_ID,                         

 EMP_PROMOTION_DATE,             

 CURRENT DATE AS RQST_DATE       

 FROM EMPLOYEE                   

 WHERE MONTH(EMP_PROMOTION_DATE) 

  = MONTH(CURRENT DATE)          

After you run the SQL, query the EMPRECOG table, and you can see the result:

The above is what we expect.  Only one of the employees has a promotion date in January, 2017. This employee has been added to the EMPRECOG table with request date of January 10 and a NULL recognition completed date.

Insert via FOR N ROWS

The third form of the INSERT statement is used to insert multiple rows with a single statement.  You can do this with an internal program table and host variables. We haven’t talked yet about embedded SQL but we’ll do a sample program now in COBOL, and I’ll assume you know the COBOL language.

We’ll use our EMPLOYEE table and insert two new rows using the INSERT via FOR N ROWS. Note that we define our host variables with OCCURS 2 TIMES to create arrays, and then we load the arrays with data before we do the INSERT statement.  Also notice the FOR 2 ROWS clause at the end of the SQL statement.  You could also have an array with more than two rows.  And the number of rows you insert using FOR X ROWS can be less than the actual array size.

       IDENTIFICATION DIVISION.

       PROGRAM-ID. COBEMP1.

      *  PROGRAM USING DB2 INSERT FOR MULTIPLE ROWS  

       ENVIRONMENT DIVISION.

       DATA DIVISION.

       WORKING-STORAGE SECTION.

           EXEC SQL

             INCLUDE SQLCA

           END-EXEC.

           EXEC SQL

             INCLUDE EMPLOYEE

           END-EXEC.

           01 HV-EMP-VARIABLES.

           10  HV-ID             PIC S9(9) USAGE COMP OCCURS 2 TIMES.

           10  HV-LAST-NAME      PIC X(30) OCCURS 2 TIMES.

           10  HV-FIRST-NAME     PIC X(20) OCCURS 2 TIMES.

           10  HV-SERVICE-YEARS  PIC S9(9) USAGE COMP OCCURS 2 TIMES.

           10  HV-PROMOTION-DATE PIC X(10) OCCURS 2 TIMES.

       PROCEDURE DIVISION.

       MAIN-PARA.

           DISPLAY SAMPLE COBOL PROGRAM: MULTIPLE ROW INSERT.

      *  LOAD THE EMPLOYEE ARRAY

           MOVE +4720               TO HV-ID (1).

           MOVE 'SCHULTZ'           TO HV-LAST-NAME(1).

           MOVE 'TIM'               TO HV-FIRST-NAME(1).

           MOVE +9                  TO HV-SERVICE-YEARS(1).

           MOVE '01/01/2017'        TO HV-PROMOTION-DATE(1).

           MOVE +6288               TO HV-ID (2).

           MOVE 'WILLARD'           TO HV-LAST-NAME(2).

           MOVE 'JOE'               TO HV-FIRST-NAME(2).

           MOVE +6                  TO HV-SERVICE-YEARS(2).

           MOVE '01/01/2016'        TO HV-PROMOTION-DATE(2).

      *  LOAD THE EMPLOYEE TABLE

           EXEC SQL

              INSERT INTO HRSCHEMA.EMPLOYEE

              (EMP_ID,

               EMP_LAST_NAME,

               EMP_FIRST_NAME,

               EMP_SERVICE_YEARS,

               EMP_PROMOTION_DATE)

              VALUES

              (:HV-ID,

               :HV-LAST-NAME,

               :HV-FIRST-NAME,

               :HV-SERVICE-YEARS,

               :HV-PROMOTION-DATE)

               FOR 2 ROWS

           END-EXEC.

           STOP RUN.

An additional option for the multiple row INSERT is to specify ATOMIC or NOT ATOMIC.  Specifying ATOMIC means that if any of the row operations fails, any successful row operations are rolled back.  It’s all or nothing.  This may be what you want, but that will depend on your program design and how you plan to handle any failed rows.

           EXEC SQL

              INSERT INTO HRSCHEMA.EMPLOYEE

              (EMP_ID,

               EMP_LAST_NAME,

               EMP_FIRST_NAME,

               EMP_SERVICE_YEARS,

               EMP_PROMOTION_DATE)

              VALUES

              (:HV-ID,

               :HV-LAST-NAME,

               :HV-FIRST-NAME,

               :HV-SERVICE-YEARS,

               :HV-PROMOTION-DATE)

               FOR 2 ROWS

               ATOMIC

           END-EXEC.

           STOP RUN.

A brief query shows our table data to be the following:

Note:  On the ATOMIC option, you have another choice which is to specify NOT ATOMIC CONTINUE ON SQLEXCEPTION.   In this case any successful row operations are still applied to the table, and any unsuccessful ones are not.  The unsuccessfully inserted rows are discarded.   The key point here is that NOT ATOMIC means the unsuccessful inserts do not cause the entire query to fail.  Make sure to remember this point!  We’ll look at how to determine which rows are the bad rows later when we get to the troubleshooting section.

Note: You can also INSERT to an underlying table via a view.  The syntax is exactly the same as for inserting to a table. This topic will be considered in a later chapter.

UPDATE Statement

The UPDATE statement is pretty straightforward.  It changes one or more records based on specified conditions.  There are two forms of the UPDATE statement:

1.   The searched update

2.   The positioned update

Searched Update

The searched update is performed on records that meet a certain search criteria using a WHERE search clause.  The basic form and syntax you need to know for the searched update is:

UPDATE

SET FIELDNAME =

WHERE

For example, recall that we left the promotion date for employee 9134 with a NULL value. Now let’s say we want to update the promotion date to October 1, 2016.  We could use this SQL to do that:

UPDATE HRSCHEMA.EMPLOYEE

SET EMP_PROMOTION_DATE = ‘10/01/2016’

WHERE EMP_ID = 9134;

If you have more than one column to update, you must use a comma to separate the column names. For example, let’s update both the promotion date and the first name of the employee.  We’ll make the first name Brianna and the promotion date 10/1/2016.

UPDATE HRSCHEMA.EMPLOYEE