Using VISION:Builder to access DB2 Database.
Article ID: 55651
Updated On:
Products
Issue/Introduction
How to use VISION:Builder or VISION:Two to access DB2 databases.
Resolution
Both products provide a simple easy to use tool that can retrieve data from DB2 databases, manipulate the data as required and then present that data in a variety of formats.
The differentiation in these two products is that VISION:Builder provides database update functionality that is not available in VISION:Two. Whereas Builder provides both retrieval and update functionality, Two provides only retrieval capability. Any references to the VISION:Builder product in the remainder of this document apply to the VISION:Two product as well.
In VISION:Builder, database input is organized into sources known as logical files. The primary database input source is identified as the MASTER file and the secondary database input sources are known as CORDn files (n = 1 thru 9). Although the source databases for these files may be any combination of DB2, IMS, VSAM or sequential, this article will focus solely on the access to DB2 databases. Access to data in DB2 databases for any of these logical files is accomplished using the SELECT statement in SQL. With VISION:Builder, you code a SELECT statement as part of the declaration of the logical file within the VISION:Builder application. For example, the declaration for a master file that will access a DB2 database may be as follows:
FILE MASTER INPUT,
SQL "SELECT WORKDEPT, HIREDATE, LASTNAME, FIRSTNME, EMPNO",
"FROM DSN8710.EMP ORDER BY WORKDEPT, HIREDATE DESC"
The SELECT statement is coded within the SQL clause of the VISION:Builder FILE statement and may be continued over multiple lines of code. Following is an example of a simple application using the above SELECT statement:
CONTROL DB2 D71A INM4CALL
FILE REPORT
FILE MASTER INPUT,
SQL "SELECT WORKDEPT, HIREDATE, LASTNAME, FIRSTNME, EMPNO",
"FROM DSN8710.EMP ORDER BY WORKDEPT, HIREDATE DESC"
;
REPORT WORKDEPT, HIREDATE, LASTNAME, FIRSTNME, EMPNO
GROUP BY WORKDEPT
FORMAT WIDTH 80
END REPORT
The column names in the SELECT statement become the field names that are used to refer to these fields throughout the VISION:Builder application coding. In the above example, a simple REPORT statement lists the columns in the DB2 table. The application coding allows for the full complement of logical, arithmetic, and string processing operations along with reporting and other data output commands that may be used with data from DB2 tables. The examples in this article only include simple reporting statements for the purpose of completeness. The focus is on the usage of the DB2 SELECT statement for data access.
A variation of the above example is one that uses a CORD1 file to obtain the name of the department related to the WORKDEPT column in the MASTER file. The coding for this example is as follows:
CONTROL DB2 D71A INM4CALL
FILE REPORT
FILE MASTER INPUT,
SQL "SELECT WORKDEPT, HIREDATE, LASTNAME, FIRSTNME, EMPNO",
"FROM DSN8710.EMP ORDER BY WORKDEPT, HIREDATE DESC"
FILE CORD1, DIRECT BY O.WORKDEPT
SQL "SELECT DEPTNAME FROM DSN8710.DEPT",
"WHERE DEPTNO = :O.WORKDEPT"
;
REPORT WORKDEPT, 1.DEPTNAME, HIREDATE, LASTNAME, FIRSTNME, EMPNO
GROUP BY WORKDEPT
ITEM WORKDEPT NONPRINT
END REPORT
In the above example, VISION:Builder functionality is used to perform the logical join of the EMP and DEPT tables. An alternate approach would be to let DB2 join the tables and then only retrieve the joined information as a single input source. The coding for this example is as follows:
CONTROL DB2 D71A INM4CALL
FILE REPORT
FILE MASTER INPUT,
SQL "SELECT WORKDEPT DEPTNAME HIREDATE LASTNAME FIRSTNME,"
"EMPNO FROM DSN8710.EMP, DSN8710.DEPT",
"WHERE WORKDEPT = DEPTNO",
"ORDER BY WORKDEPT, HIREDATE DESC"
;
REPORT WORKDEPT DEPTNAME HIREDATE LASTNAME FIRSTNME EMPNO
GROUP BY DEPTNAME
ITEM WORKDEPT NONPRINT
END REPORT
The ability of DB2 to summarize data can be exploited by VISION:Builder as shown in the following example:
CONTROL DB2 D71A INM4CALL
FILE REPORT
FILE MASTER INPUT ,
SQL "SELECT WORKDEPT, SUM(SALARY), SUM(BONUS), SUM(COMM)
"FROM DSN8710.EMP GROUP BY WORKDEPT"
;
MAIN: PROC
TOTALCOMP: FIELD TYPE P, LEN 8, DEC 2 ;Total compensation
;
LET T.TOTALCOMP = "SUM(SALARY)" + "SUM(BONUS)" + SUM(COMM)"
REPORT WORKDEPT "SUM(SALARY)" "SUM(BONUS)" "SUMCOMM)",
T.TOTALCOMP
TOTAL T.TOTALCOMP AT LEVEL G ;Grand summary for total comp.
END REPORT
END PROC
In the above example, DB2 will provide the sum of the SALARY, BONUS and COMM columns grouped by WORKDEPT. These sums, as with other DB2 function specifications, can be referenced in VISION:Builder coding by surrounding the function reference with quotation marks. The TOTAL command in the above example will compute a grand summary of the TOTALCOMP temporary field.
As can be seen, accessing DB2 data with VISION:Builder and VISION:Two can be as easy as coding the DB2 SELECT statement along with appropriate run control, arithmetic, logical, and data output statements. Data from multiple DB2 tables can be joined either by DB2 itself or by VISION:Builder as appropriate. Furthermore, data in DB2 tables may be joined to data within IMS, VSAM or sequential databases as needed. The power and flexibility of these products provide a convenient and easy to use tool for accessing and processing the data in your DB2 databases.
Feedback
