DB2 Binds and Access Paths

1. What is a DB2 bind?
   
   Answer: A DB2 bind is a process that builds an access path to DB2 tables.
   A critical aspect of the functionality and effectiveness of DB2 resides in the "bind" process. This process allows application programs to access the DB2 database efficiently and securely.

   The Bind Process in DB2

   In DB2, the "bind" process is a crucial stage in the application program preparation. It's during this phase where SQL statements from the program are "bound" into an executable form known as a "plan" or a "package." The bind process is instrumental in establishing a link between an application program and the DB2 database.

   The Role of Binding

   The role of binding can be broken down into three major functions:
   1. Authorization Checks: During the bind process, DB2 performs an authorization check to ensure the application program has the necessary permissions to access the database objects (tables, views, etc.) specified in the SQL statements.
   2. Optimization: DB2 carries out optimization during the bind process. It decides on the most efficient access path to retrieve data from the database. These paths are chosen based on the SQL statements in the program and the current state of the database. The determined access paths are stored in the plan or package.
   3. Consistency Checks: DB2 checks the SQL statements in the application program for syntax errors or inconsistencies during the bind process. This helps maintain the integrity of the database and enhances the overall performance and reliability of the application program.

   Bind Plan vs. Bind Package

   DB2 supports two types of binds: bind plan and bind package. A "plan" is an executable module containing the access paths to the data needed by an application program. A "package," on the other hand, is a smaller executable module, often forming part of a plan, and represents a single DBRM (Database Request Module) or SQL statement.
   Both types offer their unique advantages. Using packages can provide more granular control, and they enable efficient versioning and impact analysis when changes are made to the database or SQL queries.

---

2. What is a DB2 access path?
   
   Answer: An access path is the method used to access data specified in DB2 sql statements.

---

3. What is a DB2 plan?
   
   Answer: An application plan or package is generated by the bind to define an access path.

---

4. What is normalization and what are the five normal forms?
   
   Answer: Normalization is a design procedure for representing data in tabular format. The five normal forms are progressive rules to represent the data with minimal redundancy.

---

5. What are foreign keys?
   
   Answer: These are attributes of one table that have matching values in a primary key in another table, allowing for relationships between the tables.

---

6. Describe the elements of the SELECT query syntax.
   
   Answer: SELECT element FROM table WHERE conditional statement.

---

7. Explain the use of the WHERE clause.
   
   Answer: WHERE is used with a relational statement to isolate the object element or row.

---

8. What techniques are used to retrieve data from more than one table in a single SQL statement?
   
   Answer: Joins, unions and nested selects are used to retrieve data from more than one table in a single SQL statement.

---

9. What do the initials DDL and DML stand for and what is their meaning?
   
   Answer: DDL is data definition language and DML is data manipulation language.
   a) DDL statements are CREATE, ALTER, TRUNCATE.
   b) DML statements are SELECT, INSERT, DELETE and UPDATE.

---

10. What is a view and why do we use it?
    
    Answer: A view is a virtual table made up of data from base tables and other views, but not stored separately.