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. |
In this section: Displaying the Effects of UPDATE and DELETE Commands Issuing Data Adapter Environmental Commands Issuing Native SQL Commands (Non-SELECT) Creating a FOCUS View With Direct SQL Passthru How to: |
The following is a request syntax summary for native SQL commands, including SELECT statements, and for data adapter environmental commands; subsequent sections provide examples
SQL [sqlengine]
command [;]
[TABLE FILE SQLOUT]
[options]
END
where:
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
SQL [sqlengine] SET PASSRECS {OFF|ON}
where:
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 data 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 can issue one or more data adapter SET commands in a request using Direct SQL Passthru. Data adapter SET commands are not passed to the RDBMS; the data adapter maintains them in memory.
The following example specifies the DB2 RDBMS as the target RDBMS, issues four data 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, MVS, or CMS.
set sqlengine=db2
> > sql set autoclose on fin
> > sql set ssid dsn
> > sql set plan p7029910
> > sql set dbspace public.space0
> > sql ?
(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 - :
(FOC1459) USER SET PLAN FOR CALL ATTACH IS - :
(FOC1460) INSTALLATION DEFAULT PLAN IS - : P7029910
(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 DEFAULT
(FOC1424) ISOLATION LEVEL FOR DB2 TABLE INTERFACE IS :
(FOC1441) WRITE FUNCTIONALITY IS - : ON
(FOC1445) OPTIMIZATION OPTION IS - : ON
(FOC1420) OPTIMIZATION OF ALL=ON AS LEFT JOIN - : OFF
(FOC1763) IF-THEN-ELSE OPTIMIZATION IS - : OFF
(FOC1484) SQL ERROR MESSAGE TYPE IS - : DBMS
(FOC1497) SQL EXPLAIN OPTION IS - : OFF
(FOC1552) INTERFACE DEFAULT DATE TYPE - : NEW
(FOC1446) DEFAULT DBSPACE IS - : PUBLIC.SPACE0
(FOC1453) DEFAULT INDEX SPACE PARAMETERS - : USING STOGROUP PMSSTGRP
> >
See Environmental Commands, for data adapter SET commands.
When the data 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 data 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). |
Example: |
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 each vendor's products for all branches located in New York. The subquery retrieves New York branch numbers. The request does not specify additional 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;
END
The output is:
PAGE 1
VENDOR_NUMBER PRODUCT __SUM(NUMBER_OF_UNITS)
------------- ------- ----------------------
1 RADIO 15
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 data adapter generates the SQLOUT Master File for each SQL SELECT query. The SQLOUT Master File supports read-only access. The data 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. |
How to: Alter Length and Scale of Numeric Columns Returned Control the Precision of the Oracle NUMBER Data Type Example: Altering the Length and Scale of Numeric Columns Returned Customizing Output of a Direct SQL Passthru Report Request Reference: |
Note: The data 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 DB2, DB2 for VM, and IDMS/SQL Data Adapters use the SQL DESCRIBE function to obtain column information:
The data 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 |
I4 |
same, if p=38,s=0 |
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.
SQL [sqlengine] SET CONVERSION {RESET|dtype} [RESET|PRECISION {value|MAX}]
where:
nn [mm]
DATA TYPE | MAX Precision |
INTEGER | 11 |
REAL | 9 |
FLOAT | 20 |
DECIMAL | 33 |
Note:
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:
SQL 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:
SQL 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:
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
.
.
.
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 15
3 MICRO 9
When customizing a report, standard FOCUS report request syntax applies, subject to the following rules:
You can create a named, internal Master File (FOCUS view) for a particular SELECT statement with the data 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. |
How to: Create a FOCUS View With Direct SQL Passthru Example: Reference: |
SQL [sqlengine] PREPARE view_name FOR
SELECT....[;]
END
where:
The keywords 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 in TABLE requests 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 10) 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 15 **
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 |