The following is a request syntax summary for native SQL commands, including SELECT statements, and for adapter environmental commands; subsequent sections provide examples

{ENGINE|SQL} [sqlengine] 
command [;]
[TABLE FILE SQLOUT]
[options]
END

where:

sqlengine

Indicates the target RDBMS. Acceptable values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

command

Is one SQL command, or one or more adapter SET commands.

;

For SQL SELECT requests only, the semicolon is required if you specify additional FOCUS report options.

TABLE FILE SQLOUT

For SQL SELECT requests only, allows you to specify additional FOCUS report options or subcommands. To create a Master File you can use throughout the FOCUS session, see Creating a FOCUS View With Direct SQL Passthru.

options

For SQL SELECT requests only, are report formatting options or operations.

END

Terminates the request. Is optional for adapter SET commands, the SQL commands COMMIT WORK and ROLLBACK WORK, the DB2 CONNECT command, and the adapter parameterized Passthru commands BEGIN SESSION, END SESSION, and PURGE (see Parameterized SQL Command Summary). Required for all other commands.

Note:

• The OFF setting is valid only for the SET SQLENGINE command.
• You cannot issue an SQL command together with adapter SET commands in one request.

You can use the SET PASSRECS command to display the number of rows affected by a successfully executed Direct SQL Passthru UPDATE or DELETE command. The syntax is

{ENGINE|SQL} [sqlengine] SET PASSRECS {OFF|ON}

where:

sqlengine

Indicates the target RDBMS. Acceptable values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

OFF

Is the default value. The adapter provides no information as to the number of records affected by a successfully executed Direct SQL Passthru UPDATE or DELETE command.

ON

Provides the following message after the successful execution of a Direct SQL Passthru UPDATE or DELETE command:

(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND: #/operation

For example, a DELETE command that executes successfully and affects 20 rows generates the following message:

(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND: 20/DELETE

In addition to this message, the adapter updates the &RECORDS system variable with the number of rows affected. You can access this variable using Dialogue Manager, and display it with the ? STAT query.

Note:

• You must use Direct SQL Passthru syntax to issue UPDATE or DELETE commands in order to invoke the SET PASSRECS command.
• The FOC1364 message is for informational purposes only and does not affect the &FOCERRNUM setting.

The &ROWSAFFECTED variable is populated with the number of rows affected by a Direct SQL Passthru INSERT, UPDATE, or DELETE command. It is populated regardless of the PASSRECS setting. &ROWSAFFECTED is initialized to -1 and is set to -1 by any Direct SQL Passthru command that does not return the number of rows affected, such as a SELECT statement. The value of &ROWSAFFECTED is overwritten each time a Direct SQL Passthru INSERT, UPDATE, or DELETE command is executed, so if you want to retain it, you must copy it to another variable or store it in a calculated field.

You can issue one or more adapter SET commands in a request using Direct SQL Passthru. Adapter SET commands are not passed to the RDBMS. The adapter maintains them in memory.

The following example specifies the DB2 RDBMS as the target RDBMS, issues four adapter SET commands, and issues the SQL ? query command to display the updated parameters. Notice that the request does not include the environmental qualifiers TSO or MVS.

SET SQLENGINE=DB2
SQL SET AUTOCLOSE ON FIN
SQL SET SSID DSN
SQL SET PLAN P7029910
SQL SET DBSPACE PUBLIC.SPACE0
SQL ?

The output is

(FOC1440) CURRENT SQL INTERFACE SETTINGS ARE :                 
(FOC1442) CALL ATTACH FACILITY IS             -  : ON          
(FOC1447) SSID FOR CALL ATTACH IS             -  : DSN         
(FOC1448) ACTIVE PLAN FOR CALL ATTACH IS      -  : USERCAF     
(FOC1459) USER SET PLAN FOR CALL ATTACH IS    -  : P7029910    
(FOC1460) INSTALLATION DEFAULT PLAN     IS    -  : M727703B    
(FOC1503) SQL STATIC OPTION IS                -  : OFF         
(FOC1444) AUTOCLOSE OPTION IS                 -  : ON FIN      
(FOC1496) AUTODISCONNECT OPTION IS            -  : ON FIN      
(FOC1499) AUTOCOMMIT OPTION IS                -  : ON COMMAND  
(FOC1449) CURRENT SQLID IS                    -  : SYSTEM DEFAU
(FOC1424) ISOLATION LEVEL FOR DB2 TABLE INTERFACE IS  :        
(FOC1491) FETCH BUFFERING FACTOR              -  : 100         
(FOC1441) WRITE FUNCTIONALITY IS              -  : ON          
(FOC1445) OPTIMIZATION OPTION IS              -  : OFF         
(FOC1763) IF-THEN-ELSE OPTIMIZATION IS        - : ON           
(FOC1484) SQL ERROR MESSAGE TYPE IS           -  : DBMS        
(FOC1497) SQL EXPLAIN OPTION IS               -  : OFF         
(FOC1552) INTERFACE DEFAULT DATE TYPE         -  : NEW         
(FOC1446) DEFAULT DBSPACE IS                  -  : PUBLIC.SPACE

See Adapter Commands, for information about adapter SET commands.

When the adapter identifies SQL commands, it passes them to the RDBMS for immediate execution.

With Direct SQL Passthru, one SQL command can span several lines without any prefix or continuation characters. You must complete the command or request with the END keyword.

This example specifies the DB2 RDBMS as the target RDBMS, creates the DPBRANCH table, and inserts rows.

SET SQLENGINE=DB2
SQL CREATE TABLE DPBRANCH
    (BRANCH_NUMBER    INTEGER      NOT NULL,
     BRANCH_NAME      CHAR(5)      NOT NULL,
     BRANCH_MANAGER   CHAR(5)      NOT NULL,
     BRANCH_CITY      CHAR(5)      NOT NULL)
 IN PUBLIC.SPACE0 ;
END
SQL INSERT INTO DPBRANCH VALUES (1,'WEST','PIAF','NY') ;
END
SQL INSERT INTO DPBRANCH VALUES (2,'EAST','SMITH','NY') ;
END

Consult File Descriptions and Tables for sample tables.

When you issue an SQL SELECT request using the Direct SQL Passthru facility, the adapter passes the request directly to the RDBMS. FOCUS does not examine it. The RDBMS evaluates the request and sends storage requirements to FOCUS for future request results (answer sets).

Based on the storage requirements, FOCUS creates an internal Master File named SQLOUT (discussed in The SQLOUT Master File. The SQLOUT Master File functions as a template for reading and formatting the request results.

The SQLOUT Master File resides in memory. You cannot edit or print it. It exists only for the duration of the request. Subsequent requests cannot reference it. To create an internal Master File that exists for the entire FOCUS session, see Creating a FOCUS View With Direct SQL Passthru.

After FOCUS prepares the storage area and the SQLOUT Master File, the RDBMS executes the SQL SELECT request, retrieves the rows, and returns the answer set to FOCUS. The answer set is, in effect, a default report. FOCUS performs minimal additional formatting. When the report is complete, the internal SQLOUT Master File is discarded.

The RDBMS reads data once for SELECT requests using Direct SQL Passthru. FOCUS does not hold or re-read data locally, except for some types of TABLE subcommands (for example, to re-sort or summarize rows). For performance reasons, you should incorporate as many operations as possible (particularly, sorting and aggregation operations) in your SELECT statement and rely on FOCUS for formatting and operations not available through the RDBMS.

The following example illustrates a SELECT statement and its default report. The SELECT statement retrieves, from the inventory table, the total number of individual units of products from each vendor for all branches located in New York. The subquery retrieves New York branch numbers. The request does not specify additional report formatting:

ENGINE DB2
SELECT VENDOR_NUMBER, PRODUCT, SUM(NUMBER_OF_UNITS)
FROM DPINVENT
WHERE BRANCH_NUMBER IN
  (SELECT BRANCH_NUMBER
   FROM DPBRANCH
   WHERE BRANCH_CITY = 'NY')
GROUP BY VENDOR_NUMBER, PRODUCT
ORDER BY VENDOR_NUMBER, PRODUCT;
END

The output is:

VENDOR_NUMBER  PRODUCT          E03
-------------  -------          ---
            1  RADIO             10
            3  MICRO              9

To produce the preceding default report, the Direct SQL Passthru facility implicitly issues the following TABLE request against the SQLOUT Master File:

TABLE FILE SQLOUT
PRINT *
END

Internally, the process produces an SQLOUT Master File (described in The SQLOUT Master File) that you can use for FOCUS report formatting commands.

For an example of a SELECT request that includes FOCUS report formatting commands, see The SQLOUT Master File.

To give you access to FOCUS report formatting facilities, the adapter generates the SQLOUT Master File for each SQL SELECT query. The SQLOUT Master File supports read-only access. The adapter creates this internal Master File in memory based on information from the RDBMS. You cannot edit or print the SQLOUT Master File. It exists only for the immediate request, so subsequent requests cannot reference it.

Note: The adapter does not generate an associated Access File, since the SQL statement is stored in memory.

The SQLOUT Master File describes the answer set. Each field represents one data element in the outermost SELECT list, reflecting the flat row that the RDBMS returns.

The following is the SQLOUT Master File created for the example in Issuing SQL SELECT Commands:

FILENAME=SQLOUT, SUFFIX=SQLDS, $
  SEGNAME=SQLOUT, SEGTYPE=S0, $
    FIELD='VENDOR_NUMBER' , E01, USAGE=I11 ,ACTUAL=I4 ,MISSING=OFF, $
    FIELD='PRODUCT'       , E02, USAGE=A5  ,ACTUAL=A5 ,MISSING=OFF, $
    FIELD='__SUM(NUMBER_OF_UNITS)',
                             E03, USAGE=I11 ,ACTUAL=I4 ,MISSING=OFF,  $

The FILENAME and the SEGNAME values are SQLOUT. The SUFFIX is SQLDS, SQLDBC, SQLIDMS, or SQLORA depending on the target engine for the request. The SEGTYPE is S0. These values are constant.

The Adapters for DB2 and IDMS/SQL use the SQL DESCRIBE function to obtain column information:

• The FIELDNAME value is the column name. For Teradata, the field name is the same default value assigned to the alias name. For expressions or functions, the FIELDNAME value depends on the RDBMS. For example, the DB2 RDBMS does not return a column name, so the field name and the alias are assigned the same default values.
• The ALIAS value is set to E0n (n starts at 1 and is incremented by 1 for each data element in the outermost SELECT list).
• The MISSING value is ON if the column allows nulls. Otherwise, it is set to OFF.

The adapter calculates USAGE and ACTUAL formats based on the column data type and length. The following charts outline these calculations.

DATE

TIME

TIMESTAMP

INTEGER

SMALLINT

DECIMAL

FLOAT  (4 byte)

FLOAT  (8 byte)

VARCHAR(n)

VARCHAR(n)

CHAR(n)

GRAPHIC(n)

VARGRAPHIC(n)

DATE

INTEGER

SMALLINT

DECIMAL

FLOAT (8 byte)

VARCHAR(n)

CHAR(n)

BYTE

BYTEINT

VARBYTE

GRAPHIC(n)

VARGRAPHIC(n)

DATE

NUMBER(p,s)

VARCHAR(n)

LONG(n)

CHAR(n)

RAW(n)

{ENGINE|SQL} [sqlengine] SET CONVERSION {RESET|dtype} [RESET|PRECISION {value|MAX}]

SQL DB2 SET CONVERSION INTEGER PRECISION 7

ENGINE DB2 SET CONVERSION FLOAT PRECISION 14 3

SQL DB2 SET CONVERSION INTEGER RESET

ENGINE DB2 SET CONVERSION RESET

SQL [SQLORA] SET ORANUMBER {COMPAT|DECIMAL}

SQL DB2
SELECT VENDOR_NUMBER, PRODUCT, SUM(NUMBER_OF_UNITS)
FROM DPINVENT                                      
WHERE BRANCH_NUMBER IN                             
  (SELECT BRANCH_NUMBER                            
   FROM DPBRANCH                                   
   WHERE BRANCH_CITY = 'NY')                       
GROUP BY VENDOR_NUMBER, PRODUCT                    
ORDER BY VENDOR_NUMBER, PRODUCT;                   TABLE FILE SQLOUT
"Number of Units of Each Vendor's Products"
"         in New York Branches            "
" "
PRINT E01 AS 'Vendor,Number'
      E02 AS 'Product,Name'
      E03 AS 'Total,Units'
END

 NUMBER OF RECORDS IN TABLE=        2  LINES=      2
PAGE     1
 Number of Units of Each Vendor's Products
          in New York Branches
 Vendor  Product      Total
 Number  Name         Units
 ------  -------      -----
      1  RADIO           10
      3  MICRO            9

You can create a named, internal Master File (FOCUS view) for a particular SELECT statement with the adapter SQL PREPARE command. Unlike the SQLOUT Master File, you can generate reports with this FOCUS view for the entire FOCUS session.

In any FOCUS session, you can describe an unlimited number of FOCUS views.

{ENGINE|SQL} [sqlengine] PREPARE view_name FOR
SELECT....[;]
END

SET SQLENGINE=DB2
SQL PREPARE TOTPROD FOR
SELECT VENDOR_NUMBER, PRODUCT, SUM(NUMBER_OF_UNITS)
FROM DPINVENT
WHERE BRANCH_NUMBER IN
  (SELECT BRANCH_NUMBER
   FROM DPBRANCH
  WHERE BRANCH_CITY = 'NY')
GROUP BY VENDOR_NUMBER, PRODUCT
ORDER BY VENDOR_NUMBER, PRODUCT;
END

DEFINE FILE TOTPROD
  HI_STOCK/A2 = IF (E03 GT 9) THEN '**' ELSE '  ' ;
END
TABLE FILE TOTPROD
"Number of Units of Each Vendor's Products"
"         in New York Branches            "
" "
PRINT E01 AS 'Vendor,Number'
      E02 AS 'Product,Name'
      E03 AS 'Total,Units'
      HI_STOCK AS ''
FOOTING
"** = Too many units in stock,"
"     reevaluate purchasing. "
END

 NUMBER OF RECORDS IN TABLE=        2  LINES=      2
PAGE     1
Number of Units of Each Vendor's Products
         in New York Branches
Vendor  Product      Total
Number  Name         Units
------  -------      -----
     1  RADIO           10  **
     3  MICRO            9
** = Too many units in stock,
     reevaluate purchasing.