SQL Server overview

Overview

Model 204 provides industry standard SQL access to Model 204 data through client-server technology. The Model 204 SQL Server provides full SQL processing in the basic Model 204 address space or virtual machine (ONLINE module) in the z/OS, z/VSE, and VM operating systems. The client-server architecture allows the Model 204 SQL Server to service networked PC clients.

SQL topics

The SQL documentation consists of the pages listed below. This list is also available as a "See also" link from each of the pages.

• SQL Server overview
• SQL Server installation
• SQL catalog
• SQL representation of Model 204 data
• SQL Data Definition Language (DDL)
• SQL DDL from the Table Specification Facility
• SQL catalog reporting and querying
• SQL Data Manipulation Language (DML)
• SQL DDL syntax
• SQL reserved words
• SQL DDL mapping of the demonstration database

Model 204 SQL processing configurations

SQL processing for PC clients is provided by the Model 204 Connect★ Suite. The following figure illustrates the PC clients that can be configured with the Connect★ 32-bit ODBC Model 204 driver, using a TCP/IP connection to PC clients. SQL processing for PC clients is also provided by JDBC for Model 204 or .NET Framework.

For more information, see the Connect★ wiki pages.

Model 204 SQL Server and clients

How the SQL Server works within Model 204

Model 204 SQL processing is a Model 204 access method such as User Language (SOUL) or the Host Language Interface (HLI). The SQL Server invokes Model 204 DBMS operations and provides a combination of Model 204 and SQL database functionality. This section discusses some of the characteristics of the SQL Server and Model 204 interaction.

SQL Server provides seamless operation

After installing Model 204 and bringing up the Model 204 Online, preparation for SQL access requires only that you define a mapping of your Model 204 files to the SQL catalog. SQL Server processing is activated automatically and functions transparently to provide responses to client requests.

The typical sequence of tasks required to access the Model 204 SQL Server is the following:

• Install Model 204, running the appropriate installation jobs for the type of SQL client you are and the SQL utilities you require.
• Include in your Model 204 Online job the Model 204 SQL IODEV, RCL IODEV, and CCAIN parameters, sizing requirements, catalog file, and subsystems, and at least one TCP/IP thread definition.
• For SQL processing, populate the SQL catalog with SQL DDL definitions for your Model 204 files.
• Issue SQL DML for SQL processing; issue SOUL commands, programs, and procedures for RCL processing.

SQL Server operates concurrently

The Model 204 SQL Server may operate concurrently and may share data files with SOUL and the Host Language Interface. Model 204 coordinates record and resource locking among the three interfaces. The server takes advantage of Model 204 indexing and file organization efficiencies.

SQL DML and SQL DDL may be executed simultaneously

While Model 204 SQL Data Manipulation Language (DML) is being issued against Model 204 files, you can simultaneously run Model 204 SQL Data Definition Language (DDL) against the SQL catalog. However, SQL DDL may update only SQL objects that are not currently being accessed by SQL DML statements.

SQL DDL and Model 204 DDL are independent

Model 204 SQL DDL maps Model 204 databases to SQL tables by creating records in the SQL catalog. However, the SQL catalog is not active like a dictionary: SQL DDL execution does not cause Model 204 DDL to execute. Conversely, Model 204 DDL changes to files that are cataloged in the SQL catalog are not automatically reflected in the SQL catalog.

File preparation is minimal

The task of preparing your Model 204 files for SQL processing is largely that of defining your Model 204 files to the SQL catalog. Model 204 SQL processing uses the standard Model 204 file system, and supports all Model 204 file types.

The lone requirement for files used in SQL processing is that they be transaction backout (TBO) files. Model 204 automatically backs out incomplete transactions for TBO files when problems prevent the completion of a request. The TBO requirement guarantees that all uncommitted SQL updates can be backed out and prevents confusing transaction restrictions.

SQL supports Model 204 file groups with limitations. An SQL table can map to (and be as large as) at most a single Model 204 file. However, you can simulate file groups with SQL views and retrieve (but not update) data through the simulation. See Simulating file groups.

Field attribute functionality is available

Model 204 fields are mapped to SQL columns in the SQL catalog. The functionality of most Model 204 field attributes is available for Model 204 SQL processing. You must properly define the SQL columns corresponding to the Model 204 fields to make use of the attribute functionality. You can make use of the following field attributes:

KEY	STRING	INVISIBLE
ORDERED	FLOAT	OCCURS
NUMERIC RANGE	BINARY	DEFERRABLE
CODED	UNIQUE	LEN

INVISIBLE fields are translated to SQL columns that can be used in certain circumstances to select data but cannot themselves be updated by SQL applications.

Only the first occurrences of multiply occurring OCCURS fields are accessible in SQL if mapped normally to an SQL column. For complete access, however, you can translate these fields into nested tables, a Model 204 SQL processing extension to standard SQL DDL.

SQL processing adds to Model 204 Online requirements

To support Model 204 SQL processing, you must make changes to the job or EXEC that brings up your Model 204 Online. These changes include the following:

• Additional file definition statements for the SQL catalog and for utilities that support the Model 204 SQL Server
• SQL and RCL IODEV thread definitions
• Adjustments to CCAIN parameters; additional CCAIN parameters
• SQL connection DEFINE commands for TCP/IP
• Model 204 SQL Server area size increase

SQL processing relies on SQL security

To access the Model 204 SQL Server, SQL processing clients must pass Model 204 login security and any external security software that is in effect. Two of the Model 204 SQL supporting utilities, CCATSF and CCACATREPT, are protected by Model 204 Application Subsystem security.

Model 204 field and record security are not represented and not enforced through the Model 204 SQL Server interface.

The primary security protection for issuing SQL DDL and DML is provided by SQL GRANT and REVOKE statements and SQL views. GRANT and REVOKE define who is allowed to perform a given operation on a given SQL object. Views allow you to define subsets of the database to which you can selectively grant access.

You can also replace Model 204 SQL security with privilege checking by an external security package. You provide user exits to the security package in a Model 204-defined format. For details, see SQL security exits.

Model 204 SQL processing components

The Model 204 SQL Server integrates SQL processing into the core of Model 204. The SQL Server processes SQL DDL and DML from workstation interfaces.

The SQL Server works along with supporting software that provides data definition, packaging, and transfer, and has optional tools that simplify client use of the SQL Server.

The configuration of the SQL processing components when Model 204 is running under z/OS, z/VM, or z/VSE is shown in Model 204 SQL processing components.

Note: The following figure illustrates the PC clients that can be configured with the Connect★ Suite JDBC driver and the .NET Framework driver. Only a TCP/IP connection to a PC client can be configured.

Model 204 SQL processing components

SQL Server components

The following are the principal components of the Model 204 SQL Server. These components are typically transparent to a Connect★ client.

SQL Engine	Includes the SQL Compiler, Optimizer, and Evaluator. The Engine is responsible for compiling SQL syntax strings, checking SQL semantics, optimizing database access, generating code to accomplish SQL requests, and executing the generated code. Each SQL statement generates data, status information, or both for the requesting application.
SQL Server Front End (SSFE)	The presentation level, or access layer to the SQL Engine. Primarily, SSFE accepts client request packets, processes the requests in each packet for the SQL Engine, and returns the SQL processing results in the form of a result packet. Performing corresponding functions to the SSFE is the SQL Server Client Front End.

SQL Server associated software

The SQL Server works in conjunction with the following Model 204 software. Except for the SQL catalog, this software is transparent to the client:

SQL catalog	Houses the SQL catalog information for Model 204 files defined with SQL DDL. The CCACAT system file is created during installation of Model 204.
SQL communications interface	Receives SQL requests from TCP/IP and passes them to the SQL Server Front End, and the interface receives result packets from the SQL Server Front End and routes them back to the client. This interface is a Model 204 module used only for SQL processing.
SQL Client Front End (SCFE)	Is the counterpart to the SSFE, described above. SCFE groups and sends client requests and receives and distributes results to the client. SCFE is platform independent: it is available as a Model 204 module on the mainframe or included as part of the Connect★ workstation installation software.

SQL Server supporting tools

The following Model 204 tools support SQL Server processing:

Table Specification facility (TSF)	Provides an interactive, menu-driven facility for mapping existing Model 204 files to SQL tables and columns. The TSF generates a stream of DDL statements you can edit, transmit to a workstation and submit to the Connect★ Visual Interface. The TSF is available on the mainframe only.
Catalog Reporting facility (CCACATREPT)	Provides a menu-driven facility for generating DDL from and reports of the SQL catalog contents. With the CCACATREPT, you can review your SQL object definitions, names, and privileges or use DDL it generates to repopulate the SQL catalog. CCACATREPT is available on the mainframe only.

The following PC tool supports SQL server processing.

Connect★ Visual Interface

Submits SQL DDL streams to the SQL catalog and populates the catalog with your valid DDL definitions.The Connect★ Visual Interface is a component of the Connect Star Suite for Model 204 and is available only on a PC workstation. Note: Each DDL statement submitted to the CVI must terminate with a semicolon (;).

SQL intersystem processing interfaces

Transport of the data between the SQL Server and its clients relies on TCP/IP. Model 204 TCP/IP, along with IBM's TCP/IP software on the mainframe, enables a TCP/IP network connection from Model 204 to PC clients.

Model 204 SQL standards

This section describes the SQL standards adhered to in this Model 204 SQL support.

ANSI SQL 1989 and 1992

Model 204 SQL provides all clients ANSI SQL 1989 support and some ANSI SQL 1992 support for SQL DDL and DML. Unless otherwise specified, references in this topic to standard SQL are to the ANSI SQL 1989 and standards.

SQL Server DDL and DML support includes some extensions to and omissions from the standard. These deviations from the syntax or functionality of the standard are described in SQL Data Definition Language (DDL) and SQL Data Manipulation Language (DML).

Federal Information Processing Standards

SQL DDL and DML supported by the Model 204 SQL Server are compliant with the Federal Information Processing Standards (FIPS Pub 127-1).

Open Database Connectivity

Connect★ provides Level 1 compliance with Microsoft's Open Database Connectivity (ODBC) Interface. ODBC is a de facto industry standard, based on the SAG standard for an SQL Call Level Interface, for inter-application substitutability. The Connect★ ODBC feature lets Model 204 access data from an ODBC-compliant Windows spreadsheet, application development, and word processing packages.

The Model 204 JDBC driver incorporates JDBC 2.5 with no extended functionality.

The Model 204 .NET Framework driver is .NET 2.0 compliant.

Model 204 SQL clients

This section provides brief overviews of the Model 204 SQL client configurations.

For the specific current client hardware and software configuration requirements for Model 204 SQL processing, see the Connect★ Suite and SQL documentation.

SQL processing from the PC client

Connect★ Suite gives PC clients SQL and Remote Command Line (RCL) access to Model 204 data.

PC clients

PC clients for Connect★ Suite must be using a workstation with a LAN connection to the mainframe that supports TCP/IP communications software.

Connect^★ Suite application program interface

For Connect★ application program interfaces, PC clients can use the Model 204 32-bit ODBC driver, J204 JDBC driver, or Model204Client .NET Framework data provider to support SQL application programs.