DB2 Flashcards
Explain Second Normal Form
Non key columns provide a fact about the key.
Explain First Normal form
All occurrences of a record should contain the same number of records.
Explain 3rd normal Form.
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.
Explain 4th Normal Form
No row contains 2 or more independent multi-valued facts about an entity.
Explain stage 1 and stage 2 predicates
Stage 1
Sarge able (searchable argument) Uses the data manager (DM)
Stage 2
Non Sargeable , non indexable, uses RDS
SQL - How would you find out the total rows on a DB2 table?
SELECT COUNT(*) WITH UR
Or
Check catalog RTS if re-orged regularly.
SQL - How do you eliminate duplicate values in SQL.
SELECT DISTINCT or preferably, ensure predicate uses full key
SQL - How would you find the highest value for a column?
SELECT MAX(col name)
SQL - How would you select the first five characters of the column FIRSTNAME of Table EMP.
SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP
SQL - How would you concatenate FIRSTNAME and LASTNAME in SQL to give a complete name.
Select Strip(FIRSTNAME) CONCAT “ “ CONCAT strip(LASTNAME) as FULLNAME
SQL - How would you list employees who are not assigned to a project.
SELECT FIRSTNAME
,LASTNAME
FROM. EMP
WHERE PROJECT IS NULL
Explain CS,RR and UR.
Cursor Stability
Locks released on page at once
Repeatable Read
Locks retained until end of transaction
Uncommitted Read
No locks
Describe the physical storage of
DATE
TIME
TIMESTAMP
Physical
DATE 4 Bytes
TIME 3 Bytes
TIMESTAMP 10 Bytes
Layout DATE CHAR(08) TIME CHAR(06) TIMESTAMP CHAR(26)
Name the various locking levels.
Row
Page
Table
Table space
SQL - What would you accomplish with GROUP BY and HAVING.
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.
What is a cursor, why is it used.
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
Describe UNION all
UNION
Will return the result of 2 separate queries which retrieve identical columns, deduplicating rows.
UNION ALL
Will not remove duplicates.
Describe what happens at the statement OPEN Cursor
Cursor placed on first row unless an order by is used, then all rows are retrieved and sorted.
SQL - can MAX be used on a CHAR column?
Yes.
SQL - the following statement is returning inaccurate results, offer possible reasons why?
Check definition of SALARY, are NULLS allowed?
What is the largest value a BIGINT column can hold?
8 bytes
9 quintillion
Describe aggregate functions and provide examples
Built in maths functions like
SUM
COUNT
MAX
How would you select a row using the index.
Use all unique key columns in predicate.
Version 9
New features.
OLAP processing RANK DENSE RANK ROW NUMBER Optimising sub queries DATA TYPE XML
What is a correlated sub query.
A sub query where the inner query refers to the table in the outer query.
Version 10 Features
Enhanced User Degined Functions SELECT IN RETURN TIMESTAMP extension TIMEZONE UTC Temporal tables DSNULI XML modify Progressive LOB streaming Online reorganise of LOBS
Expand the following DDL DML DCL SQL
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.Explain 2 phase commit.
DB2 updates Logs
- Retains locks
CICS update logs
- Retains Locks
DB2 log commit
- release locks
CICS release locks
Log 2 phase commit record.What is a scalar query
Returns 1 result
1 row and 1 column.
SQLCODE
-805
-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
Explain different access types
Direct Index lookup
Matching index scan
Non matching index scan
SQL - UNION
What is the restriction of using UNION in an SQL statement.
Must be used within a cursor
Explain difference between II and BETWEEN
IN(1,4,7)
BETWEEN 1 AND 7
IN specifies a list BETWEEN specifies a range.
DCL
Show example of definition of a VARCHAR column
10 REMARKS
49. REMARKS-LEN PIC S9(4) COMP.
49 REMARKS-TEXT PIC X(1926)
Describe - Indoubt thread.
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.
