Easysoft SQI-Zortec System Z Driver User Guide - Technical Reference

Technical Reference for Easysoft SQI-Zortec System Z Driver

This section documents the ODBC and SQL conformance in the Easysoft SQI-Zortec System Z Driver and supported Zortec System Z data types.

Appendix Guide

Conformance

This section specifies the level of SQL and ODBC conformance in the Easysoft SQI-Zortec System Z Driver.

API Support

The Easysoft SQI-Zortec System Z Driver provides the following API Entry points:

Function ODBC Conformance
SQLAllocHandle Core
SQLBindCol Core
SQLBindParameter Core
SQLBrowseConnect Level 1
SQLCancel Core
SQLCloseCursor Core
SQLColAttribute Core
SQLColumnPrivileges Level 2
SQLColumns Core
SQLConnect Core
SQLDescribeCol Core
SQLDescribeParam Level 2
SQLDisconnect Core
SQLDriverConnect Core
SQLEndTran Core
SQLExecDirect Core
SQLExecute Core
SQLFetch Core
SQLFetchScroll Core
SQLForeignKeys Level 2
SQLFreeHandle Core
SQLFreeStmt Core
SQLGetConnectAttr Core
SQLGetCursorName Core
SQLGetData Core
SQLGetDescField Core
SQLGetDescRec Core
SQLGetDiagField Core
SQLGetDiagRec Core
SQLGetEnvAttr Core
SQLGetFunctions Core
SQLGetInfo Core
SQLGetStmtAttr Core
SQLGetTypeInfo Core
SQLMoreResults Level 1
SQLNativeSql Core
SQLNumParams Core
SQLNumResultCols Core
SQLParamData Core
SQLPrepare Core
SQLPrimaryKeys Level 1
SQLProcedureColumns Level 1
SQLProcedures Level 1
SQLPutData Core
SQLRowCount Core
SQLSetConnectAttr Core
SQLSetCursorName Core
SQLSetDescField Core
SQLSetDescRec Core
SQLSetEnvAttr Core
SQLSetPos Level 1
SQLSetStmtAttr Core
SQLSpecialColumns Core
SQLStatistics Core
SQLTablePrivileges Level 2
SQLTables Core

Figure 29: API Entry points

The following functions are provided by the ODBC Driver Manager:

Function ODBC Conformance
SQLDataSources Core
SQLDrivers Core
SQLAllocConnect Core
SQLAllocEnv Core
SQLError Core
SQLGetConnectOption Core
SQLSetParam Core

Figure 30: ODBC Driver Manager functions

The following functions are provided by the Setup DLL:

Function ODBC Conformance
SQLConfigDriver Core
SQLConfigDSN Core

Figure 31: Setup DLL functions

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

Function ODBC Conformance
SQLBulkOperations Level 1
SQLCopyDesc Core

Figure 32: Unsupported functions

Statement types

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

ALTER TABLE

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 SQI-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 SQI-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, sub query or another join.

Joins can be nested with no restriction on depth, and in both SQL92 and ODBC format:

SQL

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 SQI-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 ))

The sub queries 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 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:

SQL

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

Aggregate Functions

The Easysoft SQI-Zortec System Z Driver supports the following aggregate functions:

COUNT( * | ALL | DISTINCT )

AVG( ALL | DISTINCT )

MIN( ALL | DISTINCT )

MAX( ALL | DISTINCT )

SUM( ALL | DISTINCT )

Conversion 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.

Conditional Functions

The Easysoft SQI-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}

Optimization

The Easysoft SQI-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 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.

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.

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.

Informational Schema

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:

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

Cursors

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):

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

Zortec System Z data types

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):

Figure 33: The Zortec System Z Datanames screen

Double-click on a column to display the attributes of the column:

Figure 34: The Zortec System Z column attributes screen

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.

AN - Alphanumeric

System Z Edit mask SQL Data Type Comment
(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

Figure 35: Zortec System Z alphanumeric data types

BI - Unsigned binary

System Z Edit mask SQL Data Type Comment
(No mask) SQL_NUMERIC Length * 2, Decimals
B SQL_NUMERIC Length * 2, Decimals
Z SQL_NUMERIC Length * 2, Decimals
BZ SQL_NUMERIC Length * 2, Decimals
$ SQL_NUMERIC Length * 2, Decimals
B$ SQL_NUMERIC Length * 2, Decimals
* SQL_NUMERIC Length * 2, Decimals
B* SQL_NUMERIC Length * 2, Decimals
$9 SQL_NUMERIC Length * 2, Decimals
$B SQL_NUMERIC Length * 2, Decimals
$Z SQL_NUMERIC Length * 2, Decimals
$* SQL_NUMERIC Length * 2, Decimals
JD SQL_DATE
AP SQL_VARCHAR(13) format "(999)999-9999"
P SQL_VARCHAR(8) format "999-9999"
SS SQL_VARCHAR(11) format "999-99-9999"
D3 SQL_DATE
D4 SQL_DATE
DR SQL_DATE
DC SQL_DATE

Figure 36: Zortec System Z unsigned binary data types

SB - Signed binary

System Z Edit mask SQL Data Type Comment
(No mask) SQL_NUMERIC Length * 2, Decimals
B SQL_NUMERIC Length * 2, Decimals
Z SQL_NUMERIC Length * 2, Decimals
BZ SQL_NUMERIC Length * 2, Decimals
$ SQL_NUMERIC Length * 2, Decimals
B$ SQL_NUMERIC Length * 2, Decimals
* SQL_NUMERIC Length * 2, Decimals
B* SQL_NUMERIC Length * 2, Decimals
$9 SQL_NUMERIC Length * 2, Decimals
$B SQL_NUMERIC Length * 2, Decimals
$Z SQL_NUMERIC Length * 2, Decimals
$* SQL_NUMERIC Length * 2, Decimals
JD SQL_DATE
AP SQL_VARCHAR(13) format "(999)999-9999"
P SQL_VARCHAR(8) format "999-9999"
SS SQL_VARCHAR(11) format "999-99-9999"
D3 SQL_DATE
D4 SQL_DATE
DR SQL_DATE
DC SQL_DATE

Figure 37: Zortec System Z signed binary data types

UP - Unsigned Packed Decimal

System Z Edit mask SQL Data Type Comment
(No mask) SQL_NUMERIC Length * 2, Decimals
B SQL_NUMERIC Length * 2, Decimals
Z SQL_NUMERIC Length * 2, Decimals
BZ SQL_NUMERIC Length * 2, Decimals
$ SQL_NUMERIC Length * 2, Decimals
B$ SQL_NUMERIC Length * 2, Decimals
* SQL_NUMERIC Length * 2, Decimals
B* SQL_NUMERIC Length * 2, Decimals
$9 SQL_NUMERIC Length * 2, Decimals
$B SQL_NUMERIC Length * 2, Decimals
$Z SQL_NUMERIC Length * 2, Decimals
$* SQL_NUMERIC Length * 2, Decimals
SS SQL_VARCHAR(11) format "999-99-9999"

Figure 38: Zortec System Z unsigned packed data types

SP - Signed Packed Decimal

System Z Edit mask SQL Data Type Comment
(No mask) SQL_NUMERIC Length * 2, Decimals
B SQL_NUMERIC Length * 2, Decimals
Z SQL_NUMERIC Length * 2, Decimals
BZ SQL_NUMERIC Length * 2, Decimals
$ SQL_NUMERIC Length * 2, Decimals
B$ SQL_NUMERIC Length * 2, Decimals
* SQL_NUMERIC Length * 2, Decimals
B* SQL_NUMERIC Length * 2, Decimals
$9 SQL_NUMERIC Length * 2, Decimals
$B SQL_NUMERIC Length * 2, Decimals
$Z SQL_NUMERIC Length * 2, Decimals
$* SQL_NUMERIC Length * 2, Decimals
SS SQL_VARCHAR(11) format "999-99-9999"

Figure 39: Zortec System Z signed packed decimal data types

UN - Unsigned ASCII numeric

System Z Edit mask SQL Data Type Comment
(No mask) SQL_NUMERIC Length, Decimals
B SQL_NUMERIC Length, Decimals
Z SQL_NUMERIC Length, Decimals
BZ SQL_NUMERIC Length, Decimals
$ SQL_NUMERIC Length, Decimals
B$ SQL_NUMERIC Length, Decimals
* SQL_NUMERIC Length, Decimals
B* SQL_NUMERIC Length, Decimals
$9 SQL_NUMERIC Length, Decimals
$B SQL_NUMERIC Length, Decimals
$Z SQL_NUMERIC Length, Decimals
$* SQL_NUMERIC Length, Decimals
DR SQL_DATE
D SQL_DATE
DC SQL_DATE
SS SQL_VARCHAR(11) format "999-99-9999"
AP SQL_VARCHAR(13) format "(999)999-9999"

Figure 40: Zortec System Z unsigned ASCII numeric data types

SN - Signed ASCII numeric

System Z Edit mask SQL Data Type Comment
(No mask) SQL_NUMERIC Length, Decimals
B SQL_NUMERIC Length, Decimals
Z SQL_NUMERIC Length, Decimals
BZ SQL_NUMERIC Length, Decimals
$ SQL_NUMERIC Length, Decimals
B$ SQL_NUMERIC Length, Decimals
* SQL_NUMERIC Length, Decimals
B* SQL_NUMERIC Length, Decimals
$9 SQL_NUMERIC Length, Decimals
$B SQL_NUMERIC Length, Decimals
$Z SQL_NUMERIC Length, Decimals
$* SQL_NUMERIC Length, Decimals

Figure 41: Zortec System Z signed ASCII numeric data types

FP - Floating point

System Z Edit mask SQL Data Type Comment
(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

Figure 42: Zortec System Z floating point data types

DP - Double Precision Float

System Z Edit mask SQL Data Type Comment
(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

Figure 43: Zortec System Z double precision float data types