VISION:Inquiry Native SQL Syntax Support

Products

• Vision:Inquiry

Issue/Introduction

Does VISION:Inquiry support native SQL syntax?

Resolution

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.