If you do not issue the SET SQLENGINE command, you must specify the target RDBMS in any SQL command you want to pass directly to the RDBMS.
Including a target RDBMS in your command overrides the SET SQLENGINE command. For example, you can specify your RDBMS and override an existing OFF setting. If you do not specify a target RDBMS (either in your command or with the SET SQLENGINE command), the SQL keyword invokes the Translator.
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:
Indicates the target RDBMS. Acceptable values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE 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.
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.
For SQL SELECT requests only, are report formatting options or operations.
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:
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:
Indicates the target RDBMS. Acceptable values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.
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.
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:
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.
How to:
Reference: |
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 adapter calculates USAGE and ACTUAL formats based on the column data type and length. The following charts outline these calculations.
|
ACTUAL Formats MISSING= | ||
---|---|---|---|
Data Type: |
USAGE Format: |
OFF |
ON |
DATE |
YYMD |
DATE |
same |
TIME |
HHIS |
HHIS |
same |
TIMESTAMP |
HYYMDm |
HYYMDm |
same |
INTEGER |
I11 |
I4 |
same |
SMALLINT |
I6 |
I2 |
I4 |
DECIMAL |
Pp.s |
P((p+1)/2) |
P((p+1)/2) if p>15, P8 if p<=15 |
Where p is precision and s is scale. Precision in the USAGE format includes the decimal point and sign; precision in the ACTUAL format excludes them. | |||
FLOAT (4 byte) |
F9.2 |
F4 |
same |
FLOAT (8 byte) |
D12.2 |
D8 |
same |
VARCHAR(n) |
An |
An |
same |
Where n <= 254 characters. | |||
VARCHAR(n) |
TX50 |
TX |
same |
Where 254 < n <= 4094 characters. Note: VARCHAR strings > 4094 characters are not supported. | |||
CHAR(n) |
An |
An |
same |
GRAPHIC(n) |
A(2n+2) |
Kn |
same |
Where n <= 127 characters. | |||
VARGRAPHIC(n) |
A(2n+2) |
Kn |
same |
Where n <= 127 characters Note: VARGRAPHIC(n) where n >127 characters is not supported. |
Note: Results of expressions are also one of these data types.
ACTUAL Formats MISSING= | |||
---|---|---|---|
Data Type: |
USAGE Format: |
OFF |
ON |
DATE |
YYMD |
DATE |
same |
INTEGER |
I11 |
I4 |
same |
SMALLINT |
I6 |
I2 |
I4 |
DECIMAL |
Pp.s |
P((p+1)/2) |
P((p+1)/2) if p>15, P8 if p<=15 |
Where p is precision and s is scale. Precision in the USAGE format includes the decimal point and sign; precision in the ACTUAL format excludes them. | |||
FLOAT (8 byte) |
D12.2 |
D8 |
same |
VARCHAR(n) |
An |
An |
same |
CHAR(n) |
An |
An |
same |
BYTE |
An |
An |
same |
BYTEINT |
I6 |
I2 |
I4 |
VARBYTE |
An |
An |
same |
GRAPHIC(n) |
A(2n+2) |
Kn |
same |
Where n <= 127 characters. | |||
VARGRAPHIC(n) |
A(2n+2) |
Kn |
same |
Where n <= 127 characters Note: LONG VARGRAPHIC (VARGRAPHIC(n) where n >127 characters) is not supported. |
Note: Results of expressions are also one of these data types.
ACTUAL Formats MISSING= | |||
---|---|---|---|
Data Type: |
USAGE Format: |
OFF |
ON |
DATE |
HYYMDS |
HYYMDS |
same |
NUMBER(p,s) |
I11 Pp.s D12.2
|
I4 P((p+1)/2) D8
|
same, if p=38,s=0 P8 if p<=15 same,
if p>15
|
Where p is precision and s is scale. Precision in the USAGE format includes the decimal point and sign; precision in the ACTUAL format excludes them. | |||
VARCHAR(n) |
An |
An |
same |
Where n £ 254 characters. | |||
LONG(n) |
TX50 |
TX |
same |
Where 254 < n <= 4094 characters. Note: VARCHAR strings > 4094 characters are not supported. | |||
CHAR(n) |
An |
An |
same |
RAW(n) |
A2n |
A2n |
same |
Where n <= 128 characters Note: Oracle converts RAW data to and from CHAR data. The data is represented as 1 hexadecimal character, which is equivalent to 2 alphanumeric characters. |
Note: Results of expressions are always floating point.
You can use the SET CONVERSION command to alter the length and scale of numeric columns displayed from a SELECT request. That is, you can control the USAGE attribute in the dynamically created Master File.
{ENGINE|SQL} [sqlengine] SET CONVERSION {RESET|dtype} [RESET|PRECISION {value|MAX}]
where:
Indicates the target RDBMS. Acceptable values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.
Returns precision and scale values that you previously altered back to the data adapter defaults. If you specify RESET immediately following the SET CONVERSION command, all data types return to the defaults. If you specify RESET following a particular data type, only columns of that data type are reset.
Applies the command only to columns of a specific data type. Valid datatypes are:
INTEGER (and, for Oracle, SMALLINT).
DECIMAL.
Single precision floating point. Not supported for Oracle or Teradata.
Double precision floating point.
Is the precision in the following form:
nn [mm]
where:
Must be greater than 1 and less than the maximum allowable value for the data type. (See description of MAX.)
Is the scale. Valid with DECIMAL, REAL, and FLOAT data types. If you do not specify a value for scale, the current scale setting remains in effect.
Sets the precision to the maximum allowable value for the indicated data type:
DATA TYPE |
MAX Precision |
---|---|
INTEGER |
11 |
REAL |
9 |
FLOAT |
20 |
DECIMAL |
33 |
Note: You must include space for the decimal point and for a negative sign (if applicable) in your precision setting.
To set the precision for all INTEGER fields to 7:
SQL DB2 SET CONVERSION INTEGER PRECISION 7
To set the precision for all DOUBLE PRECISION fields to 14 and the scale to 3:
ENGINE DB2 SET CONVERSION FLOAT PRECISION 14 3
To set the precision for all INTEGER fields to the default:
SQL DB2 SET CONVERSION INTEGER RESET
To set the precision and scale for all fields to the defaults:
ENGINE DB2 SET CONVERSION RESET
You can use the ORANUMBER setting to override the default mapping of a NUMBER data type with precision between 32 and 38.
SQL [SQLORA] SET ORANUMBER {COMPAT|DECIMAL}
where:
Indicates the Oracle Data Adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Indicates that the NUMBER data type with precision between 32 and 37 will be mapped to format D20.2.This is the default.
Indicates that the NUMBER data type with precision between 32 and 37 will be mapped to format P33.2.
The following example illustrates how to customize the default report output from the example in Issuing SQL SELECT Commands. The example adds the TABLE FILE SQLOUT command to the SELECT statement, and follows it by a report heading and AS phrases that rename column headings. The primary purpose of the TABLE FILE extension is for report formatting:
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
The output is
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
When customizing a report, standard FOCUS report request syntax applies, subject to the following rules:
How to: Reference: |
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
where:
Indicates the target RDBMS. Acceptable values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.
Names the Master File (FOCUS view). The name can be eight characters long and must conform to FOCUS naming conventions for Master Files (see Describing Tables to FOCUS).
Is any SELECT statement.
The attributes and default aliases in the generated Master File are the same as those for the SQLOUT Master File (see The SQLOUT Master File). Only its file or member name and the FILENAME value reflect the view name you specify in the PREPARE command.
With Direct SQL Passthru, the PREPARE command only creates an internal Master File. The RDBMS does not return data until you execute a TABLE request referencing the FOCUS view. PREPARE does not generate an Access File. You supply the table names in SELECT statement FROM clauses.
Master Files created with PREPARE reside in memory, so you cannot edit or print them. They function like any other FOCUS Master File. For example, you can specify them with TableTalk. You can assign DEFINE fields to them or use them in MATCH FILE commands. You can also issue the ?F query to list the field names of the FOCUS view.
In this example, the DB2 RDBMS is the target RDBMS. The SQL PREPARE command creates a view named TOTPROD:
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
After the TOTPROD view is created, you can assign a virtual HI_STOCK field to the TOTPROD Master File and specify the virtual field in a report request:
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
The output is
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.
The one restriction on FOCUS views created with Direct SQL Passthru involves using them with the FOCUS JOIN command. You cannot join two FOCUS views or a view with another FOCUS-readable source. You can, however, create a HOLD file of data extracted from the FOCUS view and use the HOLD file in the join.
Information Builders |