Easytrieve Plus DB2 * SQL/DS Interface Option

Introduction

Facilities: EASYTRIEVE PLUS provides optional processing facilities that interface with DB2 and SQL/DS data bases. This interface provides complete facilities for information retrieval and maintenance of SQL data bases.
Unit of Work: Each EASYTRIEVE PLUS job activity is considered as a separate SQL unit of work. A COMMIT WORK statement is executed following each job activity that contains SQL statements. A ROLLBACK WORK statement is executed if EASYTRIEVE PLUS detects an error condition or if you code a STOP EXECUTE statement.
Automatic and Controlled Processing: You can access the data base in one of two ways: automatic table processing or controlled processing using control statements similar to those used in COBOL. With automatic processing you can retrieve selected data (or all data) from every row within a table or view.
Field Definition: Before the SQL interface is used, you must define the fields to be retrieved from the relational data base to EASYTRIEVE PLUS. These fields, called HOST VARIABLES, can be either working storage fields, or fields in an active file.

SQL data types and corresponding EASYTRIEVE PLUS field definitions
SQL Data Type	EASYTRIEVE PLUS Field Definition
INTEGER	4 B 0
SMALLINT	2 B 0
FLOAT	none
DECIMAL (precision,scale)	x P y - Note 1
CHAR (length)	x A
CHAR (length)	x M
VARCHAR	x A VARYING
VARCHAR	x M VARYING
LONG VARCHAR	x A VARYING
LONG VARCHAR	x M VARYING
GRAPHIC (length)	x K - Note 2
VARGRAPHIC	x K VARYING
LONG VARGRAPHIC	x K VARYING
DATE	10 A
TIME	8 A
TIMESTAMP	26 A
NULL indicator variables	2 B 0
none	x N y
none	x B y - Note 3
none	x U y
Note 1 For SQL DECIMAL data types, the scale is the same as the decimal places of an EASYTRIEVE PLUS field. SQL precision refers to the total number of digits that can occur in the packed field. EASYTRIEVE PLUS length refers to the number of bytes occupied by the packed field. An EASYTRIEVE PLUS field that is 5 P 2 is the equivalent of and SQL DECIMAL data type of precision = 9 and scale = 2. SQL does not support EASYTRIEVE PLUS packed fields with lengths > 8. Note 2 EASYTRIEVE PLUS double byte fields are twice the length of SQL GRAPHIC and VARGRAPHIC fields. Note 3 EASYTRIEVE PLUS binary fields other than 2 B 0 and 4 B 0 are not supported by SQL.

EASYTRIEVE PLUS SQL Control Statements
The following statements define data base activities:
SELECT statement	- describes automatic table processing.
SQL statement	- provides controlled retrieval and maintenance.
FILE statement with SQL keyword	- both automatic and controlled.
The following syntax rules apply to SQL statements (SQL and SELECT):
Operators must be separated by blanks. Standard EASYTRIEVE PLUS continuation conventions are followed. Commas are not ignored. The colon is used to identify host/indicator variables, not as a qualification separator. An SQL statement cannot be followed by another statement on the same source record.

Processing Overview
Automatic Processing
`* * * WORKING STORAGE * * * W-ACTNO W 002 B 0 W-ACTKWD W 006 A W-ACTDESC W 020 A VARYING *********************************************************************** JOB INPUT SQL SELECT ACTNO, + ACTKWD, + ACTDESC + FROM DSN8310.ACT + INTO :W-ACTNO, + :W-ACTKWD, + :W-ACTDESC PRINT RPT1`
Controlled Processing
SQL DECLARE ACT CURSOR FOR + SELECT ACTNO, + ACTKWD, + ACTDESC + FROM DSN8310.ACT FILE PRTFL01 PRINTER * * * WORKING STORAGE * * * W-ACTNO W 002 B 0 W-ACTKWD W 006 A W-ACTDESC W 020 A VARYING ********************************************************************** JOB INPUT NULL SQL OPEN ACT PERFORM CHECKSQL DO WHILE SQLCODE NE 100 SQL FETCH ACT INTO :W-ACTNO, :W-ACTKWD, :W-ACTDESC PERFORM CHECKSQL IF SQLCODE NE 100 PRINT RPT1 END-IF END-DO SQL CLOSE ACT PERFORM CHECKSQL STOP ********************************************************************** * CHECK THE SQLCODE ************************************************************************ CHECKSQL. PROC IF SQLCODE NE 0 AND + SQLCODE NE 100 DISPLAY 'SQL ERROR ' SQLCODE STOP EXECUTE END-IF END-PROC

SQL Include Interface

Introduction

The SQL INCLUDE interface provides a means to automatically generate EASYTRIEVE PLUS field definitions from the SQL catalog. This eliminates the need to code host variable definitions in the library section of your program.

The interface returns column names and data types from a particular SQL table or view. This information is then used to automatically generate EASYTRIEVE PLUS field definitions.

When used, the SQL INCLUDE statement must precede any other SQL or SELECT statements and must be coded in the LIBRARY DEFINITION section of your EASYTRIEVE PLUS program.

SQL INCLUDE Statement

SQL INCLUDE [(column ...)] [LOCATION] FROM tablename

Parameters

[(column ...)]

A list of one or more columns following the SQL INCLUDE keywords indicates that field names will be generated for these columns. The column name(s) must be enclosed within parentheses. If no column names are specified, all columns from the table will be used.

[LOCATION]

Use this optional parameter to specify the location at which the field definitions will be generated. If you do not specify LOCATION, W (a W-type working storage field) is the default.

FROM tablename

FROM identifies the table definition to be defined to EASYTRIEVE PLUS.

Operation

The generated EASYTRIEVE PLUS field names are the same as the SQL column names.

Heading and mask information are not retrieved from the catalog.

EXAMPLE 1 of SQL INCLUDE


                                                                      
SQL INCLUDE FROM DSN8310.ACT                                          
                                                                      
FILE PRTFL01 PRINTER                                                  
                                                                      
**********************************************************************
JOB INPUT SQL                                                         
                                                                      
  SELECT ACTNO,                                                     + 
         ACTKWD,                                                    + 
         ACTDESC                                                    + 
  FROM DSN8310.ACT                                                  + 
  INTO :ACTNO,                                                      + 
       :ACTKWD,                                                     + 
       :ACTDESC

EXAMPLE 2 of SQL INCLUDE

(EASYTRIEVE PLUS working storage fields for all the columns in the DSN8310.ACT table are automatically created.)



 SQL INCLUDE FROM DSN8310.ACT                                         
                                                                      
 FILE PRTFL01 PRINTER                                                 
                                                                      
 *********************************************************************
 JOB INPUT SQL                                                        
                                                                      
   SELECT *                                                          +
   FROM DSN8310.ACT                                                  +
   INTO :ACT

Specifying Automatic Input

Automatic input is specified using the JOB statement and either the SELECT statement with the select-clause, the FILE statement with the SQL keyword plus select-clause, or a combination of these statements. The possible approaches are as follows:

JOB INPUT SQL
SELECT select-clause
FILE filename SQL (select-clause)
JOB INPUT filename
FILE filename SQL
JOB INPUT filename
SELECT select-clause
FILE filename SQL (select-clause)
JOB INPUT filename
SELECT select-clause

File Statement

The EASYTRIEVE PLUS FILE statement can be used to identify a file as an SQL file and to specify the select-clause. When used in this way, the FILE statement is limited to the following syntax.

FILE filename SQL [(select-clause)] [DEFER]

filename

This is the SQL filename.

SQL [select-clause)]

SQL identifies filname as an SQL file. Select-clause is specified in the same manner as the select-clause used on the SELECT statement. If select-clause is coded it must be within parentheses. If select-clause is not coded, a SELECT statement containing the select-clause must be coded following the JOB statement. If select-clause is not coded, filename cannot be used on a GET statement.

[DEFER]

DEFER causes an SQL cursor to be opened at the execution of the first GET statement. This means that the cursor is opened after the user-specified start procedure. If DEFER is not coded, the cursor is opened before the user-specified start procedure.

JOB statement

The syntax of the JOB statement for SQL is as follows:


           {filename}
JOB INPUT  {SQL     } ....
           {        }

SELECT statement

Code the SELECT statement immediately following the:

JOB INPUT SQL statement, in which case the SELECT statement is required.

JOB INPUT filename statement where:

the select-clause was not coded on the FILE statement. In this case the SELECT statement is required.
you want to override the select-clause that was given on the FILE statement. In this case, the SELECT statement is optional.

Select-Clause Syntax

[DISTINCT] or [ALL]

DISTINCT eliminates duplicate rows. ALL specifies that duplicate rows are not eliminated. ALL is the default.

FROM table-name [correlation-name]

Table-name specifies the table from which data is to be retrieved. Correlation-name may be used to difine an alias for the table-name that immediately precedes the correlation-name.

[WHERE search-condition]

Search-condition is used to specify conditions for the retrieval of data. The search-condition is applied to the result of the FROM clause.

[GROUP BY column-name]

GROUP BY is used to group data from the FROM and WHERE clauses.

[HAVING search-condition]

Search-condition is further used to specify the data to be provided to the user. HAVING can be used to compare the results of all the returned data with a specific value within the data provided (i.e., the minimum or maximum value).

[UNION...]

The UNION is used to include rows from another table.

[ORDER BY column-name [ASC or DESC ]]

ORDER BY returns the rows of the result table in the order of the values of the specified column-names.

INTO :host-variable

The INTO clause identifies where the column values are to be placed. The INTO clause must be the last clause coded in the select-clause.

Specifying Controlled Processing

SQL Statement

DECLARE

The SQL DECLARE statement defines a cursor. Any host variables referenced in a DECLARE statement must have been defined prior to the coding of the DECLARE statement. The DECLARE statement must be coded in the Library Definition Section.

SQL DECLARE cursor-name CURSOR FOR select-clause

OPEN

The OPEN statement is used to open a cursor.

SQL OPEN cursor-name

FETCH

The FETCH statement positions a cursor on the next row of its resultant table and assigns the values of that row to host variables.

SQL FETCH cursor-name INTO :host-variable [, :host-variable...]

CLOSE

The CLOSE statement closes a cursor.

SQL CLOSE cursor-name

COMMIT

The COMMIT statement terminates a unit of work and commits the changes that were made by that unit of work.

SQL COMMIT

ROLLBACK

The ROLLBACK statement terminates a unit of work and backs out the changes that were made by that unit of work.

SQL ROLLBACK

DELETE

The DELETE statement is used to delete rows from a table.


SQL DELETE FROM table-name [correlation-name]                            +
   [WHERE {search-condition      }]
   [      {CURRENT OF cursor-name}]

INSERT

The INSERT statement is used to insert rows into a table.


SQL INSERT INTO table-name                                               +
      [(column-name [, column-name] ...]                                 +
      VALUES {constant     } [  {constant     }     ]
           ( {host-variable} [, {host-variable} ... ]

UPDATE

The UPDATE statement is used to update the values of specified columns in rows of a table.


SQL UPDATE table-name [correlation-name]                                  +
    SET column-name = {expression}                                        +
        [, column-name = {expression} ... ]                               +
   [WHERE {search-condition} ]

GET Statement

The syntax for the GET statement for SQL is as follows.

GET filename [STATUS]

When an SQL filename is specified on a GET statement, an SQL FETCH statement is issued, and the next available row is returned from the SQL cursor. The STATUS keyword returns the SQLCODE in the system-defined field FILE-STATUS (defined as 4 B 0). If STATUS is not coded, FILE-STATUS contains zeros. To use an SQL filename on GET, code the select-clause on the FILE statement.

Additional Examples
SPUFI Exercise Sample Program 2
SQL INCLUDE FROM SCTINST.HRS_ACCT_DIST_TB FILE PRTFL01 PRINTER ********************************************************************* JOB INPUT SQL SELECT EMP_ID, + ACCT_NUM + FROM SCTINST.HRS_ACCT_DIST_TB + WHERE CD_NUM = 1 + INTO :EMP_ID, + :ACCT_NUM PRINT RPT1 ********************************************************************* REPORT RPT1 PRINTER PRTFL01 TITLE 01 'AUBURN UNIVERSITY ADMINISTRATIVE COMPUTING SERVICES' TITLE 02 'SPUFI EXERCISE SAMPLE PROGRAM 2' TITLE 03 'SELECTED DATA FROM SCTINST.HRS_ACCT_DIST_TB' TITLE 04 'PGM=EZDBK05A' LINE 01 EMP_ID + ACCT_NUM
Example of selecting (joining) data from two tables SPUFI Exercise Sample Program 3
`FILE PRTFL01 PRINTER * * * WORKING STORAGE * * * W-ID W 009 A W-FNAME W 015 A W-LNAME W 025 A W-ACCTNUM W 010 A W-HOME3 W 025 A ************************************************************************ JOB INPUT SQL SELECT X.EMP_ID, + ACCT_NUM, + EMP_FIRST_NAME, + EMP_LAST_NAME + FROM SCTINST.HRS_ACCT_DIST_TB X, + SCTINST.HRS_EMP_ROOT_TB Y + WHERE CD_NUM = 1 AND + X.EMP_ID = Y.EMP_ID + ORDER BY EMP_LAST_NAME, + EMP_FIRST_NAME + INTO :W-ID, + :W-ACCTNUM, + :W-FNAME, + :W-LNAME`
Example of selecting (joining) data from two tables SPUFI Exercise Sample Program 3 Using SQL INCLUDE and qualifying working storage names.
SQL INCLUDE FROM SCTINST.HRS_ACCT_DIST_TB SQL INCLUDE FROM SCTINST.HRS_EMP_ROOT_TB FILE PRTFL01 PRINTER ********************************************************************* JOB INPUT SQL SELECT X.EMP_ID, + ACCT_NUM, + EMP_FIRST_NAME, + EMP_LAST_NAME + FROM SCTINST.HRS_ACCT_DIST_TB X, + SCTINST.HRS_EMP_ROOT_TB Y + WHERE CD_NUM = 1 AND + X.EMP_ID = Y.EMP_ID + ORDER BY EMP_LAST_NAME, + EMP_FIRST_NAME + INTO :HRS_ACCT_DIST_TB.EMP_ID, + :ACCT_NUM, + :EMP_FIRST_NAME, + :EMP_LAST_NAME PRINT RPT1 ********************************************************************** * PRINT REPORT OF COMBINED DATA ************************************************************************ REPORT RPT1 PRINTER PRTFL01 TITLE 01 'AUBURN UNIVERSITY ADMINISTRATIVE COMPUTING SERVICES' TITLE 02 'SELECTED DATA FROM SCTINST.HRS_ACCT_DIST_TB' TITLE 03 'AND SCTINST.HRS_EMP_ROOT_TB' TITLE 04 'PGM = EZDBK06A' LINE 01 HRS_ACCT_DIST_TB:EMP_ID + ACCT_NUM + EMP_FIRST_NAME + EMP_LAST_NAME
Example of selecting (joining) data from three tables
`JOB INPUT SQL SELECT X.EMP_ID, + ACCT_NUM, + EMP_FIRST_NAME, + EMP_LAST_NAME, + HOME_LINE3 + FROM SCTINST.HRS_ACCT_DIST_TB X, + SCTINST.HRS_EMP_ROOT_TB Y, + SCTINST.HRS_EMP_ADDR_TB Z + WHERE (CD_NUM = 1) AND + (X.EMP_ID = Y.EMP_ID AND + X.EMP_ID = Z.EMP_ID) + ORDER BY EMP_LAST_NAME, + EMP_FIRST_NAME + INTO :W-ID, + :W-ACCTNUM, + :W-FNAME, + :W-LNAME, + :W-HOME3`
Example of SQL INCLUDE and selecting all columns
SQL INCLUDE FROM DSN8310.ACT FILE PRTFL01 PRINTER *********************************************************************** JOB INPUT SQL SELECT * + FROM DSN8310.ACT + INTO :ACT PRINT RPT1 *********************************************************************** REPORT RPT1 PRINTER PRTFL01 TITLE 01 'AUBURN UNIVERSITY ADMINISTRATIVE COMPUTING SERVICES' TITLE 02 'ALL COLUMNS FROM DSN8310.ACT - ACTIVITY TABLE' TITLE 03 'PGM=EZDBK10A' HEADING ACTNO ('ID') HEADING ACTKWD ('KEYWORD') HEADING ACTDESC ('DESCRIPTION') LINE 01 ACTNO + ACTKWD + ACTDESC

OIT Applications Support

Last Modified:

Easytrieve Plus DB2 * SQL/DS Interface Option

Introduction

SQL data types and corresponding EASYTRIEVE PLUS field definitions

EASYTRIEVE PLUS SQL Control Statements

The following statements define data base activities:

The following syntax rules apply to SQL statements (SQL and SELECT):

Processing Overview

Automatic Processing

Controlled Processing

SQL Include Interface

SQL INCLUDE Statement

EXAMPLE 1 of SQL INCLUDE

EXAMPLE 2 of SQL INCLUDE

Specifying Automatic Input

SELECT statement

Specifying Controlled Processing

SQL Statement

GET Statement

Additional Examples

SPUFI Exercise Sample Program 2

Example of selecting (joining) data from two tables SPUFI Exercise Sample Program 3

Example of selecting (joining) data from two tables SPUFI Exercise Sample Program 3 Using SQL INCLUDE and qualifying working storage names.

Example of selecting (joining) data from three tables

Example of SQL INCLUDE and selecting all columns

Example of selecting (joining) data from two tables
SPUFI Exercise Sample Program 3

Example of selecting (joining) data from two tables
SPUFI Exercise Sample Program 3
Using SQL INCLUDE and qualifying working storage names.

Example of SQL INCLUDE
and selecting all columns