SQL representation of Model 204 data - Revision history

JAL: misc formatting

2018-08-07T22:47:14Z

misc formatting

JAL: /* Understanding nested tables */ add hyphen

2018-08-07T21:17:42Z

Understanding nested tables: add hyphen

← Older revision		Revision as of 21:17, 7 August 2018
Line 174:		Line 174:
	===Understanding nested tables===		===Understanding nested tables===
	<p>		<p>
	In the relational model, SQL tables must be normalized: at each row-column position in a table, there can be only one value (or a null). Repeating, or multiple, values are not allowed. This is not a requirement in Model 204 databases. In a single Model 204 record an individual field can have a set of, or multiple, values. Such a field is called a multiply occurring field. A group of multiply occurring fields is also allowed. </p>		In the relational model, SQL tables must be normalized: at each row-column position in a table, there can be only one value (or a null). Repeating, or multiple, values are not allowed. This is not a requirement in Model 204 databases. In a single Model 204 record an individual field can have a set of, or multiple, values. Such a field is called a multiply occurring field. A group of multiply occurring fields is also allowed. </p>
	<p>		<p>
	The Model 204 SQL nested table extension allows you to take advantage of Model 204 multiply occurring fields. This extension permits the mapping of a file with multiply occurring fields to a main (<i>parent</i>) SQL table plus one or more subsidiary (<i>nested</i>) tables related to the parent. The nested tables contain only columns that map to multiply occurring fields and a single key column that joins the nested table to its parent.</p>		The Model 204 SQL nested table extension allows you to take advantage of Model 204 multiply occurring fields. This extension permits the mapping of a file with multiply occurring fields to a main (<i>parent</i>) SQL table plus one or more subsidiary (<i>nested</i>) tables related to the parent. The nested tables contain only columns that map to multiply occurring fields and a single key column that joins the nested table to its parent.</p>
	<p>		<p>
	The values of the nested table joining key column (<i>nesting key,</i>		The values of the nested-table joining-key column (<i>nesting key,</i>
	or <i>foreign key</i>) are the same as those of the <i>primary key</i> in the parent table. These values must be unique. If the nesting key is a composite of two or more columns, you can have the Model 204 SQL Server generate and maintain a SYSTEM primary key.</p>		or <i>foreign key</i>) are the same as those of the <i>primary key</i> in the parent table. These values must be unique. If the nesting key is a composite of two or more columns, you can have the Model 204 SQL Server generate and maintain a SYSTEM primary key.</p>
	<p>		<p>
	A nested table can have only one parent table and must belong to the same SQL schema as the parent. A parent table, which must not be nested, can have multiple nested tables.</p>		A nested table can have only one parent table and must belong to the same SQL schema as the parent. A parent table, which must not be nested, can have multiple nested tables.</p>

ELowell: /* Converting SQL data types for display */

2017-10-27T18:56:14Z

Converting SQL data types for display

← Older revision		Revision as of 18:56, 27 October 2017
Line 856:		Line 856:
	<ul>		<ul>
	<li>If from FLOAT, results might be in scientific notation, and the number of significant digits displayed is not under user control. </li>		<li>If from FLOAT, results might be in scientific notation, and the number of significant digits displayed is not under user control. </li>
	<li>Truncation and/or rounding rules might vary with the client platform. For example, where 2 and 5 are the sixth and seventh digits, respectively, of a retrieved value, Model 204 SQL might round up and display 3 for a Connect<span class="superstar">★</span> client. </li>		<li>Truncation and/or rounding rules might vary with the client platform. For example, where 2 and 5 are the sixth and seventh digits, respectively, of a retrieved value, Model 204 SQL might round up and display 3 for a [[Connect*\|Connect<span class="superstar">★</span>]] client. </li>
	<li>Your data might be truncated (with a warning message) if the display format is not long enough.</li>		<li>Your data might be truncated (with a warning message) if the display format is not long enough.</li>
	</ul>		</ul>
Line 872:		Line 872:
	Model 204 4-byte (and 8-byte) floating point numbers use IBM mainframe floating point representation and can accommodate exponents as large as 75. IBM PC clients use IEEE floating point representation and are limited to exponents as large as 38 for 4-byte floating point numbers. If you are an IBM PC client and want to retrieve the full Model 204 4-byte floating point range, you must request data type conversion to 8-byte float (DOUBLE or FLOAT 22 or greater) or CHARACTER.</li>		Model 204 4-byte (and 8-byte) floating point numbers use IBM mainframe floating point representation and can accommodate exponents as large as 75. IBM PC clients use IEEE floating point representation and are limited to exponents as large as 38 for 4-byte floating point numbers. If you are an IBM PC client and want to retrieve the full Model 204 4-byte floating point range, you must request data type conversion to 8-byte float (DOUBLE or FLOAT 22 or greater) or CHARACTER.</li>
	</ul>		</ul>

	===LOB fields in SQL statements===		===LOB fields in SQL statements===
	The SQL Server supports the definition, update and retrieval of columns containing CLOB (Character Large Object) and BLOB (Binary Large Object) data.		The SQL Server supports the definition, update and retrieval of columns containing CLOB (Character Large Object) and BLOB (Binary Large Object) data.

ELowell at 15:30, 27 October 2017

2017-10-27T15:30:41Z

← Older revision		Revision as of 15:30, 27 October 2017
Line 942:		Line 942:

	[[Category: Model 204 SQL processing]]		[[Category: Model 204 SQL processing]]
	~~[[Category: Connect*]]~~

ELowell at 19:55, 26 October 2017

2017-10-26T19:55:45Z

← Older revision		Revision as of 19:55, 26 October 2017
Line 942:		Line 942:

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

ELowell: /* File groups */

2015-07-20T22:23:48Z

File groups

← Older revision		Revision as of 22:23, 20 July 2015
Line 77:		Line 77:
	If there is a group file with <var class="term">filename1</var> and a file with <var class="term">filename1</var>, Model 204 will open the group file and try to process the SQL request against the group file.</p>		If there is a group file with <var class="term">filename1</var> and a file with <var class="term">filename1</var>, Model 204 will open the group file and try to process the SQL request against the group file.</p>

	Model 204 precedence algorithm tries to open a ~~Group-~~file <var class="term">filename1</var> first. If ~~Group-~~file <var class="term">filename1</var> does not exist, Model 204 tries to open ~~File~~ <var class="term">filename1</var>. Take care when using the same name for a group-file and a file; the file cannot be accessed directly through SQL because of the precedence algorithm.		Model 204 precedence algorithm tries to open a group file <var class="term">filename1</var> first. If group file <var class="term">filename1</var> does not exist, Model 204 tries to open file <var class="term">filename1</var>. Take care when using the same name for a group file and a file; the file cannot be accessed directly through SQL because of the precedence algorithm.

	See ~~the~~ [[OPEN FILE command]] for a more detailed explanation.		See <var>[[OPEN FILE command\|OPEN FILE]]</var> for a more detailed explanation.

	====Sorted or hash key files====		====Sorted or hash key files====

ELowell at 22:07, 20 July 2015

2015-07-20T22:07:38Z

← Older revision		Revision as of 22:07, 20 July 2015
Line 1:		Line 1:

	Successful SQL processing of the data in Model 204 files requires an accurate SQL catalog definition of the files. Essential to the catalog definition is knowing how to take advantage of Model 204 database features in SQL. This ~~chapter~~ discusses how to work with Model 204 files in SQL: from preliminary file preparation to SQL accommodation of Model 204 file features, data formats, and data handling.		Successful SQL processing of the data in Model 204 files requires an accurate SQL catalog definition of the files. Essential to the catalog definition is knowing how to take advantage of Model 204 database features in SQL. This topic discusses how to work with Model 204 files in SQL: from preliminary file preparation to SQL accommodation of Model 204 file features, data formats, and data handling.

	==Representing Model 204 data in SQL==		==Representing Model 204 data in SQL==

JAL: link repair

2015-04-06T18:03:56Z

link repair

← Older revision		Revision as of 18:03, 6 April 2015
Line 170:		Line 170:
	A nested table is a Model 204 SQL extension that makes Model 204 multiply occurring fields available to SQL access. This section introduces the basic design of the feature and includes explanation of a simple example. </p>		A nested table is a Model 204 SQL extension that makes Model 204 multiply occurring fields available to SQL access. This section introduces the basic design of the feature and includes explanation of a simple example. </p>
	<p>		<p>
	For information about using Model 204 SQL DDL to define nested tables to the SQL catalog, see [[SQL Data Definition Language (DDL)#Rules for nested table columns\|Rules for nested table columns]]. For information about creating nested tables with the Table Specification facility, see [[~~Creating~~ DDL ~~with~~ the Table Specification Facility#Defining nested tables\|Defining nested tables]]. For information about using SQL DML to access nested tables, see [[SQL Data Manipulation Language (DML)#Retrieving a particular occurrence of a multiply occurring group\|Retrieving a particular occurrence of a multiply occurring group]].</p>		For information about using Model 204 SQL DDL to define nested tables to the SQL catalog, see [[SQL Data Definition Language (DDL)#Rules for nested table columns\|Rules for nested table columns]]. For information about creating nested tables with the Table Specification facility, see [[SQL DDL from the Table Specification Facility#Defining nested tables\|Defining nested tables]]. For information about using SQL DML to access nested tables, see [[SQL Data Manipulation Language (DML)#Retrieving a particular occurrence of a multiply occurring group\|Retrieving a particular occurrence of a multiply occurring group]].</p>

	===Understanding nested tables===		===Understanding nested tables===

JAL: link repair

2015-04-06T16:32:10Z

link repair

← Older revision		Revision as of 16:32, 6 April 2015
Line 17:		Line 17:
	<li>SQL multicolumn unique constraints require addition of a new field.		<li>SQL multicolumn unique constraints require addition of a new field.
	<p>		<p>
	To enforce an SQL multicolumn unique constraint, you must first define and populate a special Model 204 UNIQUE field, which provides the constraint index. See [[~~Model 204~~ SQL Data Definition Language#Specifying a multicolumn UNIQUE key\|Specifying a multicolumn UNIQUE key]]. </p>		To enforce an SQL multicolumn unique constraint, you must first define and populate a special Model 204 UNIQUE field, which provides the constraint index. See [[SQL Data Definition Language (DDL)#Specifying a multicolumn UNIQUE key\|Specifying a multicolumn UNIQUE key]]. </p>
	</li>		</li>
	<li>		<li>
Line 73:		Line 73:
	====File groups====		====File groups====
	<p>		<p>
	Model 204 file groups cannot be represented directly in SQL. However, you can simulate file groups with a view, and you can retrieve (but not update) data through the view. See [[~~Model 204~~ SQL Data Definition Language#Simulating file groups\|Simulating file groups]].</p>		Model 204 file groups cannot be represented directly in SQL. However, you can simulate file groups with a view, and you can retrieve (but not update) data through the view. See [[SQL Data Definition Language (DDL)#Simulating file groups\|Simulating file groups]].</p>
	<p>		<p>
	If there is a group file with <var class="term">filename1</var> and a file with <var class="term">filename1</var>, Model 204 will open the group file and try to process the SQL request against the group file.</p>		If there is a group file with <var class="term">filename1</var> and a file with <var class="term">filename1</var>, Model 204 will open the group file and try to process the SQL request against the group file.</p>
Line 82:		Line 82:

	====Sorted or hash key files====		====Sorted or hash key files====
	If a Model 204 file is a sorted or hash key file, specify the sort or hash key as a column in the table for the file. If the sort or hash key is required in the file (Model 204 FILEORG parameter X'02' option is set), define the column NOT NULL. For more information about mapping SQL columns to sort or hash keys, see [[~~Model 204~~ SQL Data Definition Language#Defining columns\|Defining columns]].		If a Model 204 file is a sorted or hash key file, specify the sort or hash key as a column in the table for the file. If the sort or hash key is required in the file (Model 204 FILEORG parameter X'02' option is set), define the column NOT NULL. For more information about mapping SQL columns to sort or hash keys, see [[SQL Data Definition Language (DDL)#Defining columns\|Defining columns]].

	====Files with multiply occurring fields====		====Files with multiply occurring fields====
Line 112:		Line 112:
	The most efficient way to map multiple record types within the same file is to use <b>SQL views</b>. After defining all the fields in each record type to the SQL base table you are creating, you define a view for each record type. The view definition contains a listing of the columns associated with the record type. </p>		The most efficient way to map multiple record types within the same file is to use <b>SQL views</b>. After defining all the fields in each record type to the SQL base table you are creating, you define a view for each record type. The view definition contains a listing of the columns associated with the record type. </p>
	<p>		<p>
	You can then execute simpler queries against the view, taking advantage of the preselection of required columns in the view definition. For an example of views defined for mixed record type files, see [[~~Model 204~~ SQL Data Definition Language#Mapping files with mixed record types\|Mapping files with mixed record types]]. </p>		You can then execute simpler queries against the view, taking advantage of the preselection of required columns in the view definition. For an example of views defined for mixed record type files, see [[SQL Data Definition Language (DDL)#Mapping files with mixed record types\|Mapping files with mixed record types]]. </p>

	====File data indexes====		====File data indexes====
Line 170:		Line 170:
	A nested table is a Model 204 SQL extension that makes Model 204 multiply occurring fields available to SQL access. This section introduces the basic design of the feature and includes explanation of a simple example. </p>		A nested table is a Model 204 SQL extension that makes Model 204 multiply occurring fields available to SQL access. This section introduces the basic design of the feature and includes explanation of a simple example. </p>
	<p>		<p>
	For information about using Model 204 SQL DDL to define nested tables to the SQL catalog, see [[~~Model 204~~ SQL Data Definition Language#Rules for nested table columns\|Rules for nested table columns]]. For information about creating nested tables with the Table Specification facility, see [[Creating DDL with the Table Specification Facility#Defining nested tables\|Defining nested tables]]. For information about using SQL DML to access nested tables, see [[~~Model 204~~ SQL Data Manipulation Language#Retrieving a particular occurrence of a multiply occurring group\|Retrieving a particular occurrence of a multiply occurring group]].</p>		For information about using Model 204 SQL DDL to define nested tables to the SQL catalog, see [[SQL Data Definition Language (DDL)#Rules for nested table columns\|Rules for nested table columns]]. For information about creating nested tables with the Table Specification facility, see [[Creating DDL with the Table Specification Facility#Defining nested tables\|Defining nested tables]]. For information about using SQL DML to access nested tables, see [[SQL Data Manipulation Language (DML)#Retrieving a particular occurrence of a multiply occurring group\|Retrieving a particular occurrence of a multiply occurring group]].</p>

	===Understanding nested tables===		===Understanding nested tables===
Line 304:		Line 304:
	</table>		</table>
	<p>		<p>
	Using the Model 204 SQL DDL for nested tables discussed in [[~~Model 204~~ SQL Data Definition Language#Creating nested tables\|Creating nested tables]], you map this file to the SQL parent table PEOPLE and the nested tables REV_HIST and TASKS. Logically the data appears to the SQL Server as follows: </p>		Using the Model 204 SQL DDL for nested tables discussed in [[SQL Data Definition Language (DDL)#Creating nested tables\|Creating nested tables]], you map this file to the SQL parent table PEOPLE and the nested tables REV_HIST and TASKS. Logically the data appears to the SQL Server as follows: </p>
	<table>		<table>
	<tr>		<tr>
Line 491:		Line 491:
	<p>You can preserve these Model 204 retrieval efficiencies in SQL by making sure that you assign compatible SQL data types to the Model 204 fields you are using. Usually you define an SQL numeric data type for numeric Model 204 data, and you define the SQL nonnumeric data type for nonnumeric data, although not all cases are this straightforward.</p>		<p>You can preserve these Model 204 retrieval efficiencies in SQL by making sure that you assign compatible SQL data types to the Model 204 fields you are using. Usually you define an SQL numeric data type for numeric Model 204 data, and you define the SQL nonnumeric data type for nonnumeric data, although not all cases are this straightforward.</p>
	<p>		<p>
	[[#Model 204 indexes and data format mappings\|Model 204 indexes and data format mappings]] and [[Model 204~~/SQL~~ data ~~format incompatibilities~~\|Model 204/SQL data format incompatibilities]] list compatible mappings. [[#Model 204 indexes and data format mappings\|Model 204 indexes and data format mappings]] shows permissible mappings of Model 204 data format attributes and the Model 204 indexes that are preserved or lost by such mappings. [[Model 204~~/SQL~~ data ~~format incompatibilities~~\|Model 204/SQL data format incompatibilities]] shows mappings that are not permitted because they present serious data conversion problems. </p>		[[#Model 204 indexes and data format mappings\|Model 204 indexes and data format mappings]] and [[#Optimizing Model 204 data conversion\|Model 204/SQL data format incompatibilities]] list compatible mappings. [[#Model 204 indexes and data format mappings\|Model 204 indexes and data format mappings]] shows permissible mappings of Model 204 data format attributes and the Model 204 indexes that are preserved or lost by such mappings. [[#Optimizing Model 204 data conversion\|Model 204/SQL data format incompatibilities]] shows mappings that are not permitted because they present serious data conversion problems. </p>
	<p class="caption">Model 204 indexes and data format mappings</p>		<p class="caption">Model 204 indexes and data format mappings</p>
	<table>		<table>
Line 760:		Line 760:
	The 4-byte integer precision limit is in effect for all operations involving this data, and any operations that exceed this limit result in an error message.		The 4-byte integer precision limit is in effect for all operations involving this data, and any operations that exceed this limit result in an error message.

	<p class="note"><b>Note:</b> Remember that the Model 204 BINARY field attribute has at most 30 bits of precision available. Mapping Model 204 fields to SQL DECIMAL, NUMERIC, INTEGER, and SMALLINT columns is subject to the restrictions listed in [[#Model 204~~/SQL~~ data ~~format incompatibilities~~\|Model 204/SQL data format incompatibilities]].</p>		<p class="note"><b>Note:</b> Remember that the Model 204 BINARY field attribute has at most 30 bits of precision available. Mapping Model 204 fields to SQL DECIMAL, NUMERIC, INTEGER, and SMALLINT columns is subject to the restrictions listed in [[#Optimizing Model 204 data conversion\|Model 204/SQL data format incompatibilities]].</p>

	====Floating point====		====Floating point====
Line 839:		Line 839:
	<p>		<p>
	If the SQL data type precision you specify does not match the precision of the field data, the actual precision of the data returned to you has the lower precision of the two. If you specify a lower precision than the stored data, the data is truncated to give the lower precision you specify; if you specify a higher precision than the stored data, the data is expanded to meet your specification, but it retains the lower precision of its storage format. </p>		If the SQL data type precision you specify does not match the precision of the field data, the actual precision of the data returned to you has the lower precision of the two. If you specify a lower precision than the stored data, the data is truncated to give the lower precision you specify; if you specify a higher precision than the stored data, the data is expanded to meet your specification, but it retains the lower precision of its storage format. </p>
	<p>To achieve the precision you specify for an SQL column, make sure the column is mapped to the Model 204 FLOAT LEN that matches your specification. Also, remember that your mappings are subject to the incompatibility restrictions listed in [[#Model 204~~/SQL~~ data ~~format incompatibilities~~\|Model 204/SQL data format incompatibilities]]. </p>		<p>To achieve the precision you specify for an SQL column, make sure the column is mapped to the Model 204 FLOAT LEN that matches your specification. Also, remember that your mappings are subject to the incompatibility restrictions listed in [[#Optimizing Model 204 data conversion\|Model 204/SQL data format incompatibilities]]. </p>
	<p>You can also map a Model 204 FLOAT field to an SQL DECIMAL or NUMERIC column. The precision considerations are the same as above. For example, mapping a DECIMAL 4 to FLOAT LEN 8 loses precision, wastes space, or both. </p>		<p>
	<p>In general, Model 204 SQL [[Floating point conversion, rounding, and precision rules\|precision and conversion rules]] match those for Model 204 SOUL.</p>		You can also map a Model 204 FLOAT field to an SQL DECIMAL or NUMERIC column. The precision considerations are the same as above. For example, mapping a DECIMAL 4 to FLOAT LEN 8 loses precision, wastes space, or both. </p>
			<p>
			In general, Model 204 SQL [[Floating point conversion, rounding, and precision rules\|precision and conversion rules]] match those for Model 204 SOUL.</p>
	<p>		<p>
	For more information about Model 204 floating point fields, see the [[Field design]] topic.</p>		For more information about Model 204 floating point fields, see the [[Field design]] topic.</p>
Line 880:		Line 882:
	====SQL statements supporting CLOB/BLOB data types====		====SQL statements supporting CLOB/BLOB data types====
	<p>		<p>
	The CREATE TABLE, SELECT, INSERT, UPDATE, and DELETE SQL statements have been enhanced to support the CLOB and BLOB data types. See [[~~Model 204~~ SQL Data Definition Language#Using CLOB or BLOB data\|Using CLOB or BLOB data]] for details on using large object data in CREATE TABLE.</p>		The CREATE TABLE, SELECT, INSERT, UPDATE, and DELETE SQL statements have been enhanced to support the CLOB and BLOB data types. See [[SQL Data Definition Language (DDL)#Using CLOB or BLOB data\|Using CLOB or BLOB data]] for details on using large object data in CREATE TABLE.</p>

	====SELECT====		====SELECT====

ELowell: /* ONLINE Parameter Considerations */

2015-04-01T21:14:07Z

ONLINE Parameter Considerations

← Older revision		Revision as of 21:14, 1 April 2015
Line 935:		Line 935:
	====ONLINE Parameter Considerations====		====ONLINE Parameter Considerations====
	The [[SQLBUFSZ parameter]] defines the length of the maximum incoming SQL message.		The [[SQLBUFSZ parameter]] defines the length of the maximum incoming SQL message.

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

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