CA VISION:Builder/CA VISION:Two and DB2 Database Access

Document ID : KB000055651
Last Modified Date : 14/02/2018
Show Technical Document Details

If in the course of your typical business data processing, access to data stored in your DB2 databases is a common requirement, a tool such as CA VISION:Builder or CA VISION:Two can be a valuable asset. Both of these 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 CA VISION:Builder provides database update functionality that is not available in CA VISION:Two. Whereas Builder provides both retrieval and update functionality, Two provides only retrieval capability. Any references to the CA VISION:Builder product in the remainder of this document apply to the CA VISION:Two product as well.

In CA 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 CA VISION:Builder, you code a SELECT statement as part of the declaration of the logical file within the CA 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 CA 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 CA 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, CA 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 CA 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 CA 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 CA VISION:Builder and CA 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 CA 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.