Extracting Data from Two DB2 Tables

Typically, data is extracted from a single input source.

Problem

You need to extract data from two DB2 tables.

Example

A company uses two DB2 tables to store human resource information. One table contains employee information and the second table contains project information. The tables have at least one column of data in common. A user wants to create a single report that displays columns from both tables. From the first table, the report will include the following columns :

- LASTNAME
- FIRSTNME
- HIREDATE
- SALARY

From the second table, the report will include the following columns :

- PROJNAME
- PRSTDATE
- PROJNO

The first table has a column, EMPNO, which contains the employee number. The second table has a column, RESPEMP, which contains the employee number for the person responsible for a particular project. This information is the key that ties the two tables together.

Solution

To extract the data needed from both tables, join the tables. On the File Organization dialog box, select the Use Freeform SQL Statements check box.

1 ) Set up a SELECT statement to show the columns to extract.

SELECT LASTNAME, FIRSTNAME, HIREDATE, SALARY, PROJNAME, PRSTDATE, PROJNO

2 ) For z/OS, complete the following task. For Windows, complete this task after your set up the WHERE statement in step 4. Assign these fields to keys or internal items. For ACR/Detail, list the keys before the internal items.

3 ) Set up a FROM statement to show both tables. FROM EMPLOYEE, PROJECT

4 ) Set up a WHERE statement to show the correlation of data in the two tables :

WHERE RESPEMP = EMPNO