Easysoft ODBC-Zortec System Z Driver ODBC and SQL Conformance

API Support:

• Easysoft ODBC-Zortec System Z Driver API entry points
• Functions provided by the ODBC Driver Manager
• Functions provided by the Easysoft ODBC-Zortec System Z Driver Setup library
• Functions currently not supported, but planned for future release

SQL:

• Supported Features

Easysoft ODBC-Zortec System Z Driver Supported Features:

• Statement Types
• Unions
• Table Reference
• Joins
• Predicates
• Scalar Functions
• Set Functions
• Conversion Functions
• Conditional Functions
• Literals
• Optimizations
• Informational Schema
• Data Types
• ODBC Features

API Support

The Easysoft ODBC-Zortec System Z Driver provides the following API entry points:

The following functions are provided by the ODBC Driver Manager:

The following functions are provided by the Easysoft ODBC-Zortec System Z Driver Setup library:

The following functions are currently not supported, but are planned for future release:

SQL

Supported Features

The Easysoft ODBC-Zortec System Z Driver supports the ODBC minimum SQL grammar with the majority of SQL92 extensions. These additional features are not dependent on support from the target drivers.

Easysoft ODBC-Zortec System Z Driver Supported Features:

Statement Types

The Easysoft ODBC-Zortec System Z Driver supports the following statements:

• COMMIT
• CREATE VIEW
• DELETE STATEMENT (positioned)
• DELETE STATEMENT (searched)
• DROP VIEW
• GRANT
• INSERT
• REVOKE
• ROLLBACK
• SELECT
• SELECT FOR UPDATE
• UPDATE (positioned)
• UPDATE (searched)

Unions

The Easysoft ODBC-Zortec System Z Driver supports UNION and UNION ALL.

Table Reference

The Table reference list in a select can contain all or any of:

• Table name
• Sub Query
• Join

Joins

The Easysoft ODBC-Zortec System Z Driver supports the following types of joins:

• INNER
• LEFT OUTER
• RIGHT OUTER
• FULL OUTER
• NATURAL

The joining condition may be specified with the ON or USING clause. Both the left and right source can be a table name, subquery or another join. Joins can be nested with no restriction on depth.

Joins can be specified in both SQL92 and ODBC format:

SQL92

SELECT * from x LEFT OUTER JOIN y ON x.a = y.a

ODBC

SELECT * from {oj x LEFT OUTER y ON x.a = y.a }

Predicates

The Easysoft ODBC-Zortec System Z Driver supports the following predicates:

• Comparison ( a = b )
• BETWEEN ( a BETWEEN b AND c )
• LIKE ( a LIKE '%green%' )
• NULL ( a IS NOT NULL )
• 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 ))

Subqueries in predicates can be correlated or non correlated:

Correlated

SELECT a FROM b WHERE c = ALL ( SELECT x FROM y WHERE z = a )

Non correlated

SELECT a FROM b WHERE c = ALL ( SELECT x FROM y WHERE z = 12 )

Scalar Functions

The provides all the functions required by ODBC and also functions from SQL92. Functions can be specified in either ODBC or SQL92 format:

SQL92

SELECT
  CURRENT_DATE,
  EXTRACT( YEAR FROM Employee.data_of_birth )
FROM
  Employee

ODBC

SELECT
  {fn CURRENT_DATE()},
  {fn EXTRACT( YEAR FROM Employee.data_of_birth )}
FROM
  Employee

The following is a complete set of supported functions:

Pseudo Variable Functions

• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• CURRENT_USER
• USER

SQL92 Functions

• BIT_LENGTH
• CHAR_LENGTH
• CHARACTER_LENGTH
• OCTET_LENGTH
• POSITION
• SUBSTRING
• TRIM

ODBC Functions

• ASCII
• CHAR
• CONCAT
• DIFFERENCE
• INSERT
• LCASE
• LEFT
• LENGTH
• LOCATE
• LTRIM
• REPEAT
• REPLACE
• RIGHT
• RTRIM
• SOUNDEX
• SPACE
• UCASE
• ABS
• ACOS
• ASIN
• ATAN
• ATAN2
• CEILING
• COS
• COT
• DEGREES
• EXP
• FLOOR
• LOG
• LOG10
• MOD
• PI
• POWER
• RADIANS
• RAND
• ROUND
• SIGN
• SIN
• SQRT
• TAN
• TRUNCATE
• CURDATE
• CURTIME
• DAYNAME
• DAYOFMONTH
• DAYOFWEEK
• DAYOFYEAR
• EXTRACT
• HOUR
• MINUTE
• MONTH
• MONTHNAME
• NOW
• QUARTER
• SECOND
• TIMESTAMPADD
• TIMESTAMPDIFF
• WEEK
• YEAR
• DATABASE
• IFNULL

Set Functions

The Easysoft ODBC-Zortec System Z Driver supports the following Set Functions:

• COUNT( * | ALL | DISTINCT )
• AVG( ALL | DISTINCT )
• MIN( ALL | DISTINCT )
• MAX( ALL | DISTINCT )
• SUM( ALL | DISTINCT )

Conversion Functions

The Easysoft ODBC-Zortec System Z Driver supports both the SQL92 CAST function and the ODBC CONVERT FUNCTION for conversion between compatible data types.

Conditional Functions

The Easysoft ODBC-Zortec System Z Driver supports CASE statements and the shorthand forms NULLIF and COALESCE.

Literals

All SQL92 and ODBC32 literals are supported and can be specified in either form:

SQL92

DATE '1999-01-02', INTERVAL '10-2' YEAR TO MONTH

ODBC

{d '1999-01-02'}, {INTERVAL '10-2' YEAR TO MONTH}

Optimizations

The Easysoft ODBC-Zortec System Z Driver performs several optimizations to improve performance including the following:

Query optimization

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
UNION
SELECT * FROM x WHERE b = 20 and c = 30

Table Optimization

In cases where indexes are present on tables the will (if necessary) rearrange the order that tables are processed to enable the index to be used, which 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:

SELECT * FROM a, b, c
WHERE a.catalog_number = c.catalog_number
AND b.catalog_number = a.catalog_number
AND a."desc" = b."desc"
AND c.retail = a.retail
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, but if table optimization is enabled then the query takes under 2 seconds (on the same machine).

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), but with optimization the same query will return in under 10 seconds.

Informational Schema

The Easysoft ODBC-Zortec System Z Driver provides an informational schema view of the tables supplied by the target data sources.

The following tables are available:

• INFO_SCHEMA.CHARACTER_SETS
• INFO_SCHEMA.COLLATIONS
• INFO_SCHEMA.COLUMN_PRIVILEGES
• INFO_SCHEMA.COLUMNS
• INFO_SCHEMA.INDEXES
• INFO_SCHEMA.SCHEMATA
• INFO_SCHEMA.SERVER_INFO
• INFO_SCHEMA.SQL_LANGUAGES
• INFO_SCHEMA.TABLE_PRIVILEGES
• INFO_SCHEMA.TABLES
• INFO_SCHEMA.USAGE_PRIVILEGES
• INFO_SCHEMA.VIEWS

Data Types

The following data types are supported:

• SQL_CHAR
• SQL_VARCHAR
• SQL_LONGVARCHAR
• SQL_NUMERIC
• SQL_DECIMAL
• SQL_SMALLINT
• SQL_INTEGER
• SQL_REAL
• SQL_FLOAT
• SQL_DOUBLE
• SQL_BIT
• SQL_TINYINT
• SQL_BIGINT
• SQL_BINARY
• SQL_VARBINARY
• SQL_LONGVARBINARY
• SQL_TYPE_DATE
• SQL_TYPE_TIME
• SQL_TYPE_TIMESTAMP
• SQL_INTERVALS (all types)

ODBC Features

The Easysoft ODBC-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):

SQL_ASYNC_MODE

Asynchronous operation is supported both at the statement and connection level (SQL_AM_STATEMENT)

SQL_COLUMN_ALIAS

The data source supports column alias using the optional AS clause

SQL_CORRELATION_NAME

Correlation names are supported and can be any valid user-defined-name

SQL_DATETIME_LITERALS

All SQL92 Datetime literals are supported

SQL_GETDATA_EXTENSIONS

SQLGetData can be called for any column bound or unbound

SQL_GROUPBY

The columns in the GROUP BY clause and the select list are not related (SQL_GB_NO_RELATION)

SQL_INDEX_KEYWORDS

All keywords are supported