Users can embed SQL SELECT statements in their queries using Native SQL Syntax facility of VISION:Inquiry to access and get the information from DB2 tables in online (IMS and CICS) and batch environments. Since the DB2 tables are accessed directly from the DB2 catalog, there is no need to define and maintain them in the VISION:Inquiry system database.
The VISION:Inquiry features available for this facility are:
- Use of DISPLAY command to generate the output report. You can also add the title lines to your output report using the title facility of the DISPLAY command.
- Use of the LIMIT command to limit the data in the output report.
- Use of EXTRACT command to send the output data to a sequential dataset.
- Use of OUTPUT command to send the output report to another terminal/printer.
- Use of PDD command to get the characteristics of data elements of a DB2 table directly from the DB2 catalog.
The queries can be stored in the system database and then can be called by name for execution or edited by the Text Editor facility.
To take advantage of this facility:
- The DB2 option of the product needs to be installed on the system.
The embedded SQL SELECT statement should be delimited by the EXECSQL and ENDEXEC keywords. For example, executing the following query will generate a report from the DYLINQ.IIEMP_SAL DB2 table.
DISPLAY EXECSQL SELECT * FROM DYLINQ.IIEMP_SAL ENDEXEC LIMIT 10;;
- The output report will look like:
EMPLOYEE YEAR YEAR_TO_DATE DEDUCTIONS
10103 94 52,000.00 7,400.00
10103 95 64,000.00 9,000.00
10104 94 48,000.00 6,400.00
10104 95 59,000.00 8,200.00
10105 95 15,600.00 1,370.00
21116 94 15,600.00 1,260.00
21116 95 18,800.00 1,980.00
21124 93 24,000.00 2,020.00
21124 94 30,000.00 3,140.00
21124 95 39,000.00 6,000.00
- To get the column characteristics of a DB2 table, you can use the PDD command. For example, executing the following query will generate a report showing the characteristics of the columns of the DYLINQ.IIEMP_SAL DB2 table.
PDD EXECSQL DYLINQ.IIEMP_SAL ENDEXEC;;
The output report will look like:
SQLNAME SQLTYPE SQLLEN PRCSN SCALE NULL
------------------------------ -------- ------ ----- ----- ----
EMPLOYEE CHARACTR 000005 N
YEAR CHARACTR 000002 N
YEAR_TO_DATE DECIMAL 000005 09 02 Y
DEDUCTIONS DECIMAL 000004 07 02 Y
The VISION:Inquiry Reference Guide has more detailed information and examples about using this facility.