SQL Data Definition Language (DDL) - Revision history

JAL: misc formatting

2017-06-16T21:43:37Z

misc formatting

JDamon: Corrected the three CREATE TABLE statements and the three SELECT POL_NO statements

2015-06-09T04:39:14Z

Corrected the three CREATE TABLE statements and the three SELECT POL_NO statements

← Older revision		Revision as of 04:39, 9 June 2015
Line 978:		Line 978:
	First, map an SQL table to each of the files in the Model 204 group. Then create a view defined as a UNION (or UNION ALL) of SQL SELECT statements, each of which selects all the rows in one of the tables mapped to the group. This union of SELECT statements is extended functionality to the CREATE VIEW statement.</p>		First, map an SQL table to each of the files in the Model 204 group. Then create a view defined as a UNION (or UNION ALL) of SQL SELECT statements, each of which selects all the rows in one of the tables mapped to the group. This union of SELECT statements is extended functionality to the CREATE VIEW statement.</p>
	<p>		<p>
	The following example shows three table definitions and then a view definition based on those tables that simulates a Model 204 file group:</p>		The following example shows three table definitions and then a view definition based on those tables that simulates a Model 204 file group. The example assumes that three Model 204 files named CLAIMS89, CLAIMS90 and CLAIMS91 are allocated to the run and are usually accessed as a group, named CLAIMS, from SOUL programs. However, since SQL tables cannot be mapped to file groups, this provides a way to simulate groups in SQL:</p>
	<p class="code">CREATE TABLE ~~POLICIES_89~~		<p class="code">CREATE TABLE CLAIMS89
	( POL_NO INTEGER, ACC INTEGER, STATE CHAR(2) )		( POL_NO INTEGER, ACC INTEGER, STATE CHAR(2) )

	CREATE TABLE ~~POLICIES_90~~		CREATE TABLE CLAIMS90
	( POL_NO INTEGER, ACC INTEGER, STATE CHAR(2) )		( POL_NO INTEGER, ACC INTEGER, STATE CHAR(2) )

	CREATE TABLE ~~POLICIES_91~~		CREATE TABLE CLAIMS91
	( POL_NO INTEGER, ACC INTEGER, STATE CHAR(2) )		( POL_NO INTEGER, ACC INTEGER, STATE CHAR(2) )

	CREATE VIEW MA_POLICIES (POLICY_NO, ACCIDENTS) AS		CREATE VIEW MA_POLICIES (POLICY_NO, ACCIDENTS) AS
	SELECT POL_NO, ACC FROM ~~POLICIES_89~~ WHERE STATE = 'MA'		SELECT POL_NO, ACC FROM CLAIMS89 WHERE STATE = 'MA'
	UNION ALL		UNION ALL
	SELECT POL_NO, ACC FROM ~~POLICIES_90~~ WHERE STATE = 'MA'		SELECT POL_NO, ACC FROM CLAIMS90 WHERE STATE = 'MA'
	UNION ALL		UNION ALL
	SELECT POL_NO, ACC FROM ~~POLICIES_91~~ WHERE STATE = 'MA'</p>		SELECT POL_NO, ACC FROM CLAIMS91 WHERE STATE = 'MA'</p>
	<p>		<p>
	You can use SELECT statements against the view to query the "group" of tables. However, you <b>cannot</b>		You can use SELECT statements against the view to query the "group" of tables. However, you <b>cannot</b>

ELowell: /* DDL statement-level security */

2015-04-15T20:13:49Z

DDL statement-level security

← Older revision		Revision as of 20:13, 15 April 2015
Line 1,520:		Line 1,520:
	This section also contains a discussion of view privileges and an example showing the application of statement security rules, especially for CREATE VIEW and GRANT. </p>		This section also contains a discussion of view privileges and an example showing the application of statement security rules, especially for CREATE VIEW and GRANT. </p>

	<p class="note"><b>Note:</b> You can replace Model 204 SQL statement security with privilege checking by an external security package. You provide user exits to the security package in a Model 204-defined format, as described in the [[~~Security interfaces~~]] topic.</p>		<p class="note"><b>Note:</b> You can replace Model 204 SQL statement security with privilege checking by an external security package. You provide user exits to the security package in a Model 204-defined format, as described in the [[SQL security exits]] topic.</p>
	<p>		<p>
	The SQL Server passes to the user exit all the information necessary to perform privilege checking identical to the Model 204 SQL privilege checking. The extent of the checking done is an option of the user exit.</p>		The SQL Server passes to the user exit all the information necessary to perform privilege checking identical to the Model 204 SQL privilege checking. The extent of the checking done is an option of the user exit.</p>

ELowell: /* SQL DDL processing */

2015-04-01T21:14:41Z

SQL DDL processing

← Older revision		Revision as of 21:14, 1 April 2015
Line 1,910:		Line 1,910:
	<p>		<p>
	Each DDL statement is committed upon successful execution without regard to the SQL Auto Commit setting for the data source.</p>		Each DDL statement is committed upon successful execution without regard to the SQL Auto Commit setting for the data source.</p>

			==See also==
			{{Template:SQL topic list}}

	[[Category: Model 204 SQL processing]]		[[Category: Model 204 SQL processing]]

ELowell at 20:51, 6 March 2015

2015-03-06T20:51:16Z

Show changes

ELowell: Created page with "This topic describes the characteristics of the Model 204 SQL Data Definition Language (DDL). DDL statements are SQL statements that are used to maintain the SQL catalog by cr..."

2015-02-25T21:52:22Z

Created page with "This topic describes the characteristics of the Model 204 SQL Data Definition Language (DDL). DDL statements are SQL statements that are used to maintain the SQL catalog by cr..."

New page

This topic describes the characteristics of the Model 204 SQL Data Definition Language (DDL). DDL statements are SQL statements that are used to maintain the SQL catalog by creating and altering table, view, and column definitions that describe Model 204 file data. DDL also includes SQL GRANT and REVOKE statements that define the security associated with SQL tables, views, and columns.

Emphasis in this topic is on the characteristics of Model 204 SQL DDL that differ from the ANSI SQL 1989 standard DDL. Unless otherwise specified, references to "the standard" in this topic are to the ANSI SQL 1989 standard. 

The description of the Model 204 SQL DDL is organized by function (creating, altering, dropping, and granting), including basic syntax for the statements that perform each function. 

A diagram of the entire Model 204 SQL DDL statement syntax is found in [[SQL DDL syntax]].