DB2

Question 0

Explain Second Normal Form

Answer 0

Non key columns provide a fact about the key.

Question 1

Explain First Normal form

Answer 1

All occurrences of a record should contain the same number of records.

Question 2

Explain 3rd normal Form.

Answer 2

Each non key column is independent of other non key columns and is dependant only on the key.
Each non key column must provide a fact about the key.

Question 3

Explain 4th Normal Form

Answer 3

No row contains 2 or more independent multi-valued facts about an entity.

Question 4

Explain stage 1 and stage 2 predicates

Answer 4

Stage 1
Sarge able (searchable argument) Uses the data manager (DM)
Stage 2
Non Sargeable , non indexable, uses RDS

Question 5

SQL - How would you find out the total rows on a DB2 table?

Answer 5

SELECT COUNT(*) WITH UR

Or

Check catalog RTS if re-orged regularly.

Question 6

SQL - How do you eliminate duplicate values in SQL.

Answer 6

SELECT DISTINCT or preferably, ensure predicate uses full key

Question 7

SQL - How would you find the highest value for a column?

Answer 7

SELECT MAX(col name)

Question 8

SQL - How would you select the first five characters of the column FIRSTNAME of Table EMP.

Answer 8

SELECT SUBSTR(FIRSTNAME,1,5)
FROM EMP

Question 10

SQL - How would you concatenate FIRSTNAME and LASTNAME in SQL to give a complete name.

Answer 10

Select Strip(FIRSTNAME) CONCAT “ “ CONCAT strip(LASTNAME) as FULLNAME

Question 11

SQL - How would you list employees who are not assigned to a project.

Answer 11

SELECT FIRSTNAME
,LASTNAME
FROM. EMP
WHERE PROJECT IS NULL

Question 12

Explain CS,RR and UR.

Answer 12

Cursor Stability
Locks released on page at once

Repeatable Read
Locks retained until end of transaction

Uncommitted Read
No locks

Question 13

Describe the physical storage of
DATE
TIME
TIMESTAMP

Answer 13

Physical
DATE 4 Bytes
TIME 3 Bytes
TIMESTAMP 10 Bytes

Layout
DATE CHAR(08)
TIME CHAR(06)
TIMESTAMP CHAR(26)

Question 13

Name the various locking levels.

Answer 13

Row
Page
Table
Table space

Question 14

SQL - What would you accomplish with GROUP BY and HAVING.

Answer 14

GROUP BY will display groups of data with their aggregate function result.
HAVING will restrict the result to those that satisfy the HAVING clause.

I.e HAVING count(*) > 1.

Question 15

What is a cursor, why is it used.

Answer 15

Used in COBOL program when the result of a query will retrieve more than one row.

DECLARE Cursor
OPEN Cursor

PERFORM until no more
FETCH Cursor
END PERFORM

Close Cursor

Question 16

Describe UNION all

Answer 16

UNION

Will return the result of 2 separate queries which retrieve identical columns, deduplicating rows.

UNION ALL

Will not remove duplicates.

Question 17

Describe what happens at the statement OPEN Cursor

Answer 17

Cursor placed on first row unless an order by is used, then all rows are retrieved and sorted.

Question 18

SQL - can MAX be used on a CHAR column?

Answer 18

Yes.

Question 19

SQL - the following statement is returning inaccurate results, offer possible reasons why?

Answer 19

Check definition of SALARY, are NULLS allowed?

Question 20

What is the largest value a BIGINT column can hold?

Answer 20

8 bytes

9 quintillion

Question 21

Describe aggregate functions and provide examples

Answer 21

Built in maths functions like

SUM
COUNT
MAX

Question 22

How would you select a row using the index.

Answer 22

Use all unique key columns in predicate.

Question 23

Version 9

New features.

Answer 23

OLAP processing
RANK
DENSE RANK
ROW NUMBER
Optimising sub queries
DATA TYPE XML

Question 24

What is a correlated sub query.

Answer 24

A sub query where the inner query refers to the table in the outer query.

Question 25

Version 10 Features

Answer 25

Enhanced User Degined Functions
SELECT IN RETURN
TIMESTAMP extension
TIMEZONE UTC
Temporal tables
DSNULI
XML modify 
Progressive LOB streaming
Online reorganise of LOBS

Question 26

Expand the following 
DDL
DML
DCL
SQL

Answer 26

DDL - Data Definition Language
           Create Database, Table space
           Table Etc.
DML - Data Modification Language
DCL - Data Control Language
            I.E. DCLGEN
SQL - Structured Query Language.

Question 27

Explain 2 phase commit.

Answer 27

DB2 updates Logs
       - Retains locks
CICS update logs
       - Retains Locks
DB2 log commit
       - release locks
CICS release locks 
          Log 2 phase commit record.

Question 28

What is a scalar query

Answer 28

Returns 1 result

1 row and 1 column.

Question 29

SQLCODE

-805

Answer 29

-805. Package not found in plan.
-818. Plan load mismatch , DBRM
different to load module.
-911 resource unavailable
-913 deadlock
-904 resource unavailable
+100 Row not found

Question 30

Explain different access types

Answer 30

Direct Index lookup
Matching index scan
Non matching index scan

Question 31

SQL - UNION

What is the restriction of using UNION in an SQL statement.

Answer 31

Must be used within a cursor

Question 32

Explain difference between II and BETWEEN

Answer 32

IN(1,4,7)
BETWEEN 1 AND 7

IN specifies a list BETWEEN specifies a range.

Question 33

DCL

Show example of definition of a VARCHAR column

Answer 33

10 REMARKS
49. REMARKS-LEN PIC S9(4) COMP.
49 REMARKS-TEXT PIC X(1926)

Question 34

Describe - Indoubt thread.

Answer 34

In a connection failure or system crash, the commit status of some threads may be unresolved.

When CICS and DB2 are restarted, these are either resolved automatically or by using RECOVER INDOUBT.