DB2 Outer Joins, Subselect, and Group By Statements

1. What are the characteristics of an "outer join" in DB2?
   
   Answer:
   In DB2, an outer join is a type of join operation that combines rows from two or more tables based on a related column, but unlike an inner join, it includes rows from one or both tables even if they do not have matching values. There are three types of outer joins in DB2:

   1. Left Outer Join:
      • This join returns all rows from the left table (or the first table in the query) and the matching rows from the right table. If no match is found, the result will include `NULL` for columns from the right table.
      • Example:

        SELECT * FROM A LEFT OUTER JOIN B ON A.id = B.id;
        

        
        This will return all rows from table A and the matching rows from table B. If there is no match, `NULL` values are returned for columns from table B.
   2. Right Outer Join:
      • This join returns all rows from the right table (or the second table in the query) and the matching rows from the left table. If no match is found, the result will include `NULL` for columns from the left table.
      • Example:

        SELECT * FROM A RIGHT OUTER JOIN B ON A.id = B.id;`
        

        
        This will return all rows from table B and the matching rows from table A. If there is no match, `NULL` values are returned for columns from table A.
   3. Full Outer Join:
      • This join returns all rows from both tables. If a row in one table does not have a matching row in the other table, the result will still include that row, with `NULL` for the missing columns.
      • Example:

        SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id;`
        

        
        This will return all rows where there is a match between tables A and B, plus all unmatched rows from both tables with `NULL` in place of the missing data.

   
   Characteristics of an Outer Join in DB2:

   • Inclusion of Non-Matching Rows: Unlike an inner join, outer joins return rows even when there are no matching rows in the joined table.
   • Null Handling: In outer joins, non-matching columns from the related table are represented with `NULL` values.
   • Directionality: The difference between left, right, and full outer joins lies in which table's rows are fully preserved. Left outer joins preserve the left table, right outer joins preserve the right table, and full outer joins preserve both.

   
   These types of outer joins allow for more flexible queries where you may want to include all records from one table regardless of whether they have corresponding matches in another table.
   An outer join includes rows from tables when there are no matching values in the tables.

---

2. What is a subselect? Is it different from a nested select?
   
   Answer: A subselect is a select which works in conjunction with another select. A nested select is a kind of subselect where the inner select passes to the where criteria for the outer select.

---

3. What is the difference between group by and order by?
   
   Answer: Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.

---

4. What is the purpose and function of the EXPLAIN statement in DB2?
   
   
   The `EXPLAIN` statement in IBM DB2 serves as a diagnostic tool designed to provide detailed insights into the query execution strategy chosen by the DB2 optimizer. This powerful feature enables database administrators and developers to analyze the access path selected for SQL statements, including SELECT, INSERT, UPDATE, and DELETE operations. By elucidating how DB2 intends to execute a query, the `EXPLAIN` statement aids in understanding the efficiency of query processing, identifying potential performance bottlenecks, and facilitating targeted optimization efforts.
   
   Purpose of the EXPLAIN Statement:
   The primary purpose of the `EXPLAIN` statement is to offer transparency into the internal decision-making process of the DB2 optimizer regarding query execution. This visibility into the optimizer's choices allows for a deeper understanding of how queries interact with the database structure, including the use of indexes, join methods, and data access strategies. By providing this level of detail, the `EXPLAIN` statement empowers users to make informed decisions on database design, query formulation, and performance tuning, ultimately leading to optimized database performance and resource utilization.
   Functionality and Key Features:

   1. Access Path Analysis: The `EXPLAIN` statement reveals the access path chosen for a query, including whether the query will use table scans, index scans, or a combination of both. This information is crucial for assessing the efficiency of data retrieval methods employed by the query.
   2. Join Strategies: For queries involving joins between tables, `EXPLAIN` outlines the join methods selected by the optimizer, such as nested loop joins, hash joins, or merge scan joins. Understanding these strategies helps in evaluating the effectiveness of join operations and their impact on query performance.
   3. Index Evaluation: The statement provides insights into which indexes, if any, are utilized by the query. This assists in verifying the effectiveness of existing indexes and identifying opportunities for creating new indexes to enhance performance.
   4. Cost Estimation: `EXPLAIN` offers estimates of the cost and cardinality associated with executing the query. Cost estimates, expressed in timerons, reflect the expected resource consumption, while cardinality estimates indicate the number of rows the query is expected to process or return. These metrics are invaluable for comparing the relative efficiency of different query formulations or execution plans.
   5. Sorting and Grouping Analysis: The tool also details operations such as sorting and grouping, indicating when and how these operations are performed within the query execution plan. This aids in understanding the impact of such operations on query performance, especially for large datasets.
   6. Output to EXPLAIN Tables: The results of an `EXPLAIN` statement are typically written to a set of dedicated EXPLAIN tables within the DB2 database. These tables store comprehensive details about the execution plan, allowing for offline analysis and historical comparison of execution strategies.
   
   IBM DB2 Admin

   Utilization in Performance Tuning:

   The `EXPLAIN` statement is an integral component of the DB2 performance tuning process. Database administrators and developers utilize this tool to dissect query execution plans, identify inefficiencies such as unnecessary full table scans or suboptimal join methods, and explore alternative query designs or index strategies to mitigate performance issues. Additionally, the `EXPLAIN` statement can be used iteratively, allowing users to compare the effects of various tuning efforts on query execution plans and costs, thereby guiding continuous performance improvement initiatives. In conclusion, the `EXPLAIN` statement in DB2 is an essential diagnostic tool that provides a window into the DB2 optimizer's decision-making process, offering valuable insights that drive the optimization of query performance and database resource utilization. Its comprehensive analysis capabilities make it an indispensable resource for database administrators and developers committed to maintaining and enhancing the efficiency and effectiveness of DB2 database operations.
   Answer: The explain statement provides information about the optimizer's choice of access path of the SQL.

---



5. What is tablespace?
   
   Answer: Tables are stored in tablespaces (hence the name). There are three types of tablespaces:
   1. simple,
   2. segmented and
   3. partitioned.

---




6. What is a cursor and what is its function?
   
   Answer: An embedded sql statement may return a number of rows while the programming language can only access one row at a time. The programming device called a cursor controls the position of the row.

---

7. What is Referential Integrity?
   
   Answer: Referential integrity refers to the consistency that must be maintained between primary and foreign keys. For example, every foreign key value must have a corresponding primary key value.

---

8. Usually, which is more important for DB2 system performance: 1) CPU processing or 2) I/O access?
   
   Answer: I/O operations are usually most critical for DB2 performance (or any other database for that matter).

---

9. Is there any advantage to denormalizing DB2 tables?
   
   Answer: Denormalizing DB2 tables reduces the need for processing intensive relational joins and reduces the number of foreign keys.

---

10. What is the database descriptor?
    
    Answer: The database descriptor, DBD is the DB2 component that limits access to the database whenever objects are created, altered or dropped.

Using a Nested Table Expression as the right operand of a join

SELECT PROJECT, COALESCE(PROJECTS.PROD#, PRODNUM) AS PRODNUM,
PRODUCT, PART, UNITS
FROM PROJECTS LEFT JOIN
 (SELECT PART,
    COALESCE(PARTS.PROD#, PRODUCTS.PROD#) AS PRODNUM,
    PRODUCTS.PRODUCT
  FROM PARTS FULL OUTER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#) AS TEMP  
    ON PROJECTS.PROD# = PRODNUM;

SELECT PART, SUPPLIER, PRODNUM, PRODUCT
  FROM (SELECT PART, PROD# AS PRODNUM, SUPPLIER
    FROM PARTS
    WHERE PROD# < '200') AS PARTX
  LEFT OUTER JOIN PRODUCTS
    ON PRODNUM = PROD#;

PART     SUPPLIER     PRODNUM   PRODUCT
======= ============  =======  ==========
WIRE    ACWF          10       GENERATOR
MAGNETS BATEMAN       10       GENERATOR
OIL     WESTERN_CHEM  160      ----------

Left Join

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments
ON employees.department = departments.name;

+--------+-----------+
| name   | name      |
+--------+-----------+
| John   | HR        |
| Alice  | IT        |
| Bob    | HR        |
| Sarah  | Sales     |
+--------+-----------+