This section documents the ODBC and SQL conformance in the Easysoft SQI-Zortec System Z Driver and supported Zortec System Z data types.
This section specifies the level of SQL and ODBC conformance in the Easysoft SQI-Zortec System Z Driver.
The Easysoft SQI-Zortec System Z Driver provides the following API Entry points:
The following functions are provided by the ODBC Driver Manager:
SQLDataSources | Core |
SQLDrivers | Core |
SQLAllocConnect | Core |
SQLAllocEnv | Core |
SQLError | Core |
SQLGetConnectOption | Core |
SQLSetParam | Core |
The following functions are provided by the Setup DLL:
SQLConfigDriver | Core |
SQLConfigDSN | Core |
The following functions are currently not supported but are planned for future release:
SQLBulkOperations | Level 1 |
SQLCopyDesc | Core |
The Easysoft SQI-Zortec System Z Driver supports the following statements:
The Easysoft SQI-Zortec System Z Driver supports UNION and UNION ALL.
The Table reference list in a select can contain all or any of:
The Easysoft SQI-Zortec System Z Driver supports the following types of joins:
The joining condition may be specified with the ON or USING clause.
Both the left and right source can be a table name, sub query or another join.
Joins can be nested with no restriction on depth, and in both SQL92 and ODBC format:
SELECT * from x LEFT OUTER JOIN y ON x.a = y.a
SELECT * from {oj x LEFT OUTER y ON x.a = y.a }
The Easysoft SQI-Zortec System Z Driver supports the following predicates:
IN value_list ( a IN ( 1, 2, 3 ))
IN sub_query ( a IN ( SELECT x FROM y )
Quantified comparison ( a = ALL ( SELECT x FROM y ))
Exists ( EXISTS( SELECT x from y ))
The sub queries in predicates can be correlated or non correlated:
SELECT a FROM b WHERE c = ALL ( SELECT x
SELECT a FROM b WHERE c = ALL ( SELECT x FROM y
The Easysoft SQI-Zortec System Z Driver provides all the functions required by ODBC and also functions from SQL92.
Functions can be specified in SQL92 or ODBC format:
SELECT CURRENT_DATE, EXTRACT( YEAR FROM
Employee.data_of_birth ) FROM Employee
SELECT {fn CURRENT_DATE()}, {fn EXTRACT( YEAR
FROM Employee.data_of_birth )} FROM Employee
The following is a complete set of supported functions:
The Easysoft SQI-Zortec System Z Driver supports the following aggregate functions:
The Easysoft SQI-Zortec System Z Driver supports both the SQL92 CAST function and the ODBC CONVERT FUNCTION for conversion between compatible data types.
The Easysoft SQI-Zortec System Z Driver supports CASE statements and the shorthand forms NULLIF and COALESCE.
All SQL92 and ODBC32 literals are supported and can be specified in either form:
DATE '1999-01-02', INTERVAL '10-2' YEAR TO MONTH
{d '1999-01-02'}, {INTERVAL '10-2' YEAR TO MONTH}
The Easysoft SQI-Zortec System Z Driver performs several optimizations to improve performance including the following:
The WHERE clause of a query will be rewritten into a form that allows more efficient processing of data. For example the query:
SELECT * FROM x WHERE ( a = 10 or b = 20 ) and c = 30
Will be changed into the equivalent:
SELECT * FROM x WHERE a = 10 and c = 30
SELECT * FROM x WHERE b = 20 and c = 30
In cases where indexes are present on tables, the Easysoft SQI-Zortec System Z Driver will if necessary rearrange the order in which tables are processed to enable the index to be used.
This can lead to huge increases in performance.
For example, consider the following query where tables a, b and c each have 800 rows and an index on catalog_number.
WHERE a.catalog_number = c.catalog_number
AND b.catalog_number = a.catalog_number
AND a.catalog_number = b.catalog_number
When run with the table order specified the query takes about 350 seconds on a given test configuration.
If table optimization is enabled then, on the same machine, the query takes under 2 seconds.
The effect of this optimization is most noticeable on some of the queries that comprise the TPC-D benchmark set.
Without this optimization some of the queries can be considered to never end (still running after a day), with optimization the same query will return in under 10 seconds.
The Easysoft SQI-Zortec System Z Driver provides an informational schema view of the tables supplied by the target data sources.
The following tables are available:
The following data types are supported:
The Easysoft SQI-Zortec System Z Driver provides FORWARD ONLY, STATIC and KEYSET CURSORS and also provides the following additional ODBC features (reported via the SQLGetIinfo API call):
Asynchronous operation is supported both at the statement and connection level (SQL_AM_STATEMENT)
The data source supports column alias using the optional AS clause
Correlation names are supported and can be any valid user-defined-name
All SQL92 Datetime literals are supported
SQLGetData can be called for any column, bound or unbound
The columns in the GROUP BY clause and the select list are not related (SQL_GB_NO_RELATION)
The following tables list the Zortec System Z data types and edit masks which are supported by the Easysoft SQI-Zortec System Z Driver, and the SQL data types to which data is converted.
You will probably be familiar with the Zortec System Z types and edit masks because they can be displayed within Zortec System Z.
For example, the following Zortec System Z screen shows the columns in a table (the Type and Edit columns display type and edit mask information respectively):
Double-click on a column to display the attributes of the column:
This screen shows that the LASER-DISC-NAME column contains alpha numeric data (Type = AN), has no edit mask, and can contain up to 55 characters (Length = 55).
The Comments column in the following tables refers to the fields in Zortec System Z which are used when converting the data to the appropriate SQL data type.
For example, alpha numeric data (AN) is converted to the SQL_VARCHAR data type and its length is determined by the Length value in Zortec System Z.
A utility that checks whether your data contains any unsupported data types is provided with the Easysoft SQI-Zortec System Z Driver.
See "Checking for unsupported data types" on page 74 for details of using this utility.
If you do find any unsupported data types, please contact Easysoft at support@easysoft.com, who will endeavour to support them in future releases of the software.
(No mask) | SQL_VARCHAR | Length |
B | SQL_VARCHAR | Length |
BZ | SQL_VARCHAR | Length |
CN | SQL_VARCHAR | Length |
UC | SQL_VARCHAR | Length |
SS | SQL_VARCHAR | Length |
YN | SQL_VARCHAR | Length |
(No mask) | SQL_DOUBLE | |
B | SQL_DOUBLE | |
Z | SQL_DOUBLE | |
BZ | SQL_DOUBLE | |
$ | SQL_DOUBLE | |
B$ | SQL_DOUBLE | |
* | SQL_DOUBLE | |
B* | SQL_DOUBLE | |
$9 | SQL_DOUBLE | |
$B | SQL_DOUBLE | |
$Z | SQL_DOUBLE | |
$* | SQL_DOUBLE | |
(No mask) | SQL_DOUBLE | |
B | SQL_DOUBLE | |
Z | SQL_DOUBLE | |
BZ | SQL_DOUBLE | |
$ | SQL_DOUBLE | |
B$ | SQL_DOUBLE | |
* | SQL_DOUBLE | |
B* | SQL_DOUBLE | |
$9 | SQL_DOUBLE | |
$B | SQL_DOUBLE | |
$Z | SQL_DOUBLE | |
$* | SQL_DOUBLE | |