SQL DDL from the Table Specification Facility - Revision history

ELowell: /* DDL statements generated by the TSF */

2017-10-27T19:07:07Z

DDL statements generated by the TSF

← Older revision		Revision as of 19:07, 27 October 2017
Line 57:		Line 57:

	===DDL statements generated by the TSF===		===DDL statements generated by the TSF===
	<p class="note"><b>Note:</b> Currently the CCATSF subsystem does not generate DDL statement delimiters, semicolons by default, which are required by the CVI utility. You can add delimiters by hand or use another SQL utility that does not require DDL delimiters. The Connect<span class="superstar">★</span> ODBC unsupported utilities, DDLWIN and CLIIVP, are examples of utilities which expect DDL input lines without delimiters.</p>		<p class="note"><b>Note:</b> Currently the CCATSF subsystem does not generate DDL statement delimiters, semicolons by default, which are required by the CVI utility. You can add delimiters by hand or use another SQL utility that does not require DDL delimiters. The [[Connect*_for_ODBC\|Connect<span class="superstar">★</span> for ODBC]] unsupported utilities, DDLWIN and CLIIVP, are examples of utilities which expect DDL input lines without delimiters.</p>

	The TSF is designed for the initial definition of SQL catalog objects. It generates the following DDL statements:		The TSF is designed for the initial definition of SQL catalog objects. It generates the following DDL statements:

JAL: Dictionary and SQL server from RKTools main menu

2017-06-23T00:28:57Z

Dictionary and SQL server from RKTools main menu

← Older revision		Revision as of 00:28, 23 June 2017
Line 3:		Line 3:
	You use the TSF to create new tables, not to modify existing ones.		You use the TSF to create new tables, not to modify existing ones.

	The TSF is the Model 204 subsystem CCATSF. It is subject to typical Application Subsystem security, as described in [[System requirements for Application Subsystems]]. This is the case whether you are accessing the TSF directly from your Model 204 command line as described on this page, or whether you are accessing from the [[RKWeb]] interface. RKWeb uses [[Janus Web Legacy Support]] to serve facsimiles of the panels described on this page.		The TSF is the Model 204 subsystem CCATSF. It is subject to typical Application Subsystem security, as described in [[System requirements for Application Subsystems]]. This is the case whether you are accessing the TSF directly from your Model 204 command line as described on this page, or whether you are accessing from the [[RKTools#mainmenu\|RKTools main menu]] or the [[RKWeb]] interface. RKWeb uses [[Janus Web Legacy Support]] to serve facsimiles of the panels described on this page.

JAL: /* Logging in */ rename link

2017-06-16T23:19:00Z

Logging in: rename link

← Older revision		Revision as of 23:19, 16 June 2017
Line 204:		Line 204:

	===Logging in ===		===Logging in ===
	Before you can log in to the TSF, the CCATSF subsystem must already have been started (with the Model 204 <var>START SUBSYSTEM</var> command). If you access the TSF from the RKWeb interface, this command is probably already issued as part of the ~~recommended set up during~~ [[RKTools installation#Janus Web rules for RKWeb\|~~RKTools installation~~]].		Before you can log in to the TSF, the CCATSF subsystem must already have been started (with the Model 204 <var>START SUBSYSTEM</var> command). If you access the TSF from the RKWeb interface, this command is probably already issued as part of the [[RKTools installation#Janus Web rules for RKWeb\|recommended set up for RKWeb]].

	To log in to TSF, at your Model 204 Online prompt, type:		To log in to TSF, at your Model 204 Online prompt, type:

JAL: mention RKWeb

2017-06-16T20:22:06Z

mention RKWeb

Show changes

JAL: link repair

2015-04-06T18:12:05Z

link repair

← Older revision		Revision as of 18:12, 6 April 2015
Line 362:		Line 362:
	<ul>		<ul>
	<li>		<li>
	Model 204 INVISIBLE fields are displayed with bright highlighting. You can select these fields as columns (for nonnested tables), but their SQL use is restricted. For example, you cannot update such a column in SQL. INVISIBLE field restrictions are described in [[~~Mapping~~ Model 204 data ~~to SQL~~#Using Model 204 file data features\|Using Model 204 file data features]] and in [[SQL Data Manipulation Language (DML)#Using SQL DML against INVISIBLE fields\|Using SQL DML against INVISIBLE fields]].</li>		Model 204 INVISIBLE fields are displayed with bright highlighting. You can select these fields as columns (for nonnested tables), but their SQL use is restricted. For example, you cannot update such a column in SQL. INVISIBLE field restrictions are described in [[SQL representation of Model 204 data#Using Model 204 file data features\|Using Model 204 file data features]] and in [[SQL Data Manipulation Language (DML)#Using SQL DML against INVISIBLE fields\|Using SQL DML against INVISIBLE fields]].</li>
	<li>		<li>
	You cannot select Model 204 fields that have the attribute AT-MOST-ONE or OCCURS 1 (meaning that the field does not multiply occur) as SQL columns in a nested table (the exception to this rule is the primary key field which might be defined this way).</li>		You cannot select Model 204 fields that have the attribute AT-MOST-ONE or OCCURS 1 (meaning that the field does not multiply occur) as SQL columns in a nested table (the exception to this rule is the primary key field which might be defined this way).</li>
Line 408:		Line 408:
	For example, when doing an index count, in SQL "not equal" (<>) is true only if there is a value for the field on the record that is not equal to the search string. In Model 204, if the field is not present on the record, the NOT NULL values are included in the count using the index, because Model 204 does not keep an index value NULL or NOT NULL. Once the NOT NULL count is established using the index, the Model 204 SQL processor has to check every record in that set to see whether or not there is a value for the field, and, for each record that does not have a value, reduce the index count by one. This can be very costly in terms of I/O and CPU consumption. Alternatively, if you define the column as CHAR (<var class="term">n</var>) NOT NULL, Model 204 SOUL simply does an indexed search to evaluate the "not equal" query.>		For example, when doing an index count, in SQL "not equal" (<>) is true only if there is a value for the field on the record that is not equal to the search string. In Model 204, if the field is not present on the record, the NOT NULL values are included in the count using the index, because Model 204 does not keep an index value NULL or NOT NULL. Once the NOT NULL count is established using the index, the Model 204 SQL processor has to check every record in that set to see whether or not there is a value for the field, and, for each record that does not have a value, reduce the index count by one. This can be very costly in terms of I/O and CPU consumption. Alternatively, if you define the column as CHAR (<var class="term">n</var>) NOT NULL, Model 204 SOUL simply does an indexed search to evaluate the "not equal" query.>

	For more information about matching Model 204 and SQL data formats, see [[~~Mapping~~ Model 204 data ~~to SQL~~#Matching Model 204 and SQL data formats\|Matching Model 204 and SQL data formats]].		For more information about matching Model 204 and SQL data formats, see [[SQL representation of Model 204 data#Matching Model 204 and SQL data formats\|Matching Model 204 and SQL data formats]].

	The NULL field defaults to Y (yes) except for the following cases in which it defaults to N (no): all columns in a nested table, and the primary key of a parent table.		The NULL field defaults to Y (yes) except for the following cases in which it defaults to N (no): all columns in a nested table, and the primary key of a parent table.
Line 462:		Line 462:
	Also specify CHARACTER if the data field can ever contain nonnumeric data values.		Also specify CHARACTER if the data field can ever contain nonnumeric data values.

	For more information about matching Model 204 and SQL data formats, see [[~~Mapping~~ Model 204 data ~~to SQL~~#Matching Model 204 and SQL data formats\|Matching Model 204 and SQL data formats]].		For more information about matching Model 204 and SQL data formats, see [[SQL representation of Model 204 data#Matching Model 204 and SQL data formats\|Matching Model 204 and SQL data formats]].

	===Len (length)===		===Len (length)===
Line 470:		Line 470:
	The default value is the Model 204 length; if the Model 204 field is not preallocated or float, the default is 255.		The default value is the Model 204 length; if the Model 204 field is not preallocated or float, the default is 255.

	For more information about Model 204 SQL data conversions, see [[~~Mapping~~ Model 204 data ~~to SQL~~#Optimizing Model 204 data conversion\|Optimizing Model 204 data conversion]] and [[~~Mapping~~ Model 204 data ~~to SQL~~#Observing data precision limits\|Observing data precision limits]].		For more information about Model 204 SQL data conversions, see [[SQL representation of Model 204 data#Optimizing Model 204 data conversion\|Optimizing Model 204 data conversion]] and [[SQL representation of Model 204 data#Observing data precision limits\|Observing data precision limits]].

	===Prec (precision)===		===Prec (precision)===
Line 478:		Line 478:
	If your data format is FLOAT, you must specify an integer value between 1 and 53 for binary precision (between 1 and 21 is equivalent to REAL; between 22 and 53 is equivalent to DOUBLE).		If your data format is FLOAT, you must specify an integer value between 1 and 53 for binary precision (between 1 and 21 is equivalent to REAL; between 22 and 53 is equivalent to DOUBLE).

	For more information about the precision available for Model 204 SQL processing, see [[~~Mapping~~ Model 204 data ~~to SQL~~#Observing data precision limits\|Observing data precision limits]].		For more information about the precision available for Model 204 SQL processing, see [[SQL representation of Model 204 data#Observing data precision limits\|Observing data precision limits]].

	===Scale===		===Scale===

JAL: link repair

2015-04-06T17:51:37Z

link repair

← Older revision		Revision as of 17:51, 6 April 2015
Line 232:		Line 232:
	The table name can contain up to 18 characters (A-Z, 0-9, and underscore). No embedded blanks are allowed. Table names must begin with an alphabetic character.		The table name can contain up to 18 characters (A-Z, 0-9, and underscore). No embedded blanks are allowed. Table names must begin with an alphabetic character.

	The TSF applies certain truncation and compression rules (described further in [[SQL ~~Server~~ Data Definition Language (DDL)#Mapping table names to file names\|Mapping table names to file names]]) to the SQL table name you specify and compares the resulting name to the corresponding Model 204 file name. If the resulting name does not match the Model 204 file name, the TSF automatically adds a SYSNAME clause with the Model 204 file name to the TSF-generated DDL.		The TSF applies certain truncation and compression rules (described further in [[SQL Data Definition Language (DDL)#Mapping table names to file names\|Mapping table names to file names]]) to the SQL table name you specify and compares the resulting name to the corresponding Model 204 file name. If the resulting name does not match the Model 204 file name, the TSF automatically adds a SYSNAME clause with the Model 204 file name to the TSF-generated DDL.

	To see a list of pending tables, names from table definitions you have not deleted from the TSF, place your cursor in the input area for this field and press PF1.		To see a list of pending tables, names from table definitions you have not deleted from the TSF, place your cursor in the input area for this field and press PF1.

ELowell: /* Generating DDL to an output file (Completion panel) */

2015-04-01T21:15:22Z

Generating DDL to an output file (Completion panel)

← Older revision		Revision as of 21:15, 1 April 2015
Line 714:		Line 714:
	<p>		<p>
	As stated earlier, the CVI utility requires that the DDL input statement must each be delimited, which the CCATSF utility currently does not generate. This delimit character, the semicolon (;), must be manually supplied by the user, if the CVI utility is to be used.</p>		As stated earlier, the CVI utility requires that the DDL input statement must each be delimited, which the CCATSF utility currently does not generate. This delimit character, the semicolon (;), must be manually supplied by the user, if the CVI utility is to be used.</p>

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

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

ELowell at 23:26, 12 March 2015

2015-03-12T23:26:26Z

← Older revision		Revision as of 23:26, 12 March 2015
Line 18:		Line 18:
	===TSF processing sequence===		===TSF processing sequence===
	The following table summarizes the general processing that goes on within the Table Specification facility.		The following table summarizes the general processing that goes on within the Table Specification facility.
	<p class="caption">TSF processing</p>		<p class="caption" style="width:350px">TSF processing</p>
	<table>		<table>
	<tr>		<tr>
Line 124:		Line 124:
	Command line (===>) is near the bottom of the panel. Use the command line to enter commands in lieu of PF keys. The minimum abbreviation for a command is displayed in capital letters and is generally the first three characters (for example, DEL for DELete). </li>		Command line (===>) is near the bottom of the panel. Use the command line to enter commands in lieu of PF keys. The minimum abbreviation for a command is displayed in capital letters and is generally the first three characters (for example, DEL for DELete). </li>
	</ul>		</ul>
	<p class="caption">TSF panel map</p>		<p class="caption" style="width:400px">TSF panel map</p>
	<p class="figure">[[File:SQL_Server_UG_fig_5-2_TSF_panel_map.gif]]</p>		<p class="figure">[[File:SQL_Server_UG_fig_5-2_TSF_panel_map.gif]]</p>

	===ENTER and PF key conventions ===		===ENTER and PF key conventions ===
	At the bottom of each TSF panel is a list of the PF keys that you can use on the panel. [[#Table Specification facility Enter and function keys\|Table Specification facility Enter and function keys]] displays the TSF key conventions.		At the bottom of each TSF panel is a list of the PF keys that you can use on the panel. [[#Table Specification facility Enter and function keys\|Table Specification facility Enter and function keys]] displays the TSF key conventions.
	<p class="caption">Table Specification facility Enter and function keys</p>		<p class="caption" style="width:400px">Table Specification facility Enter and function keys</p>
	<table>		<table>
	<tr>		<tr>
Line 205:		Line 205:
	[[#Main Menu panel\|Main Menu panel]] shows an example of the Main Menu with user entries in four of the fields. These entries initiate the definition of Mark's CLIENTS table, which is mapped to the Model 204 demonstration database CLIENTS file. Examples in subsequent sections follow this mapping to completion.		[[#Main Menu panel\|Main Menu panel]] shows an example of the Main Menu with user entries in four of the fields. These entries initiate the definition of Mark's CLIENTS table, which is mapped to the Model 204 demonstration database CLIENTS file. Examples in subsequent sections follow this mapping to completion.

	<p class="caption">Main Menu panel</p>		<p class="caption" style="width:400px">Main Menu panel</p>
	<p class="figure">[[File:SQL_Server_UG_fig_5-3_Main_Menu.gif]]</p>		<p class="figure">[[File:SQL_Server_UG_fig_5-3_Main_Menu.gif]]</p>
	<p>Press Enter, then fill in SCHEMA and Model 204 File name.</p>		<p>Press Enter, then fill in SCHEMA and Model 204 File name.</p>
Line 320:		Line 320:
	[[#Column List panel\|Column List panel]], [[#Column List, second panel\|Column List, second panel]], and [[#Column List, third panel\|Column List, third panel]], show a Column List panel with user column selections filled in.		[[#Column List panel\|Column List panel]], [[#Column List, second panel\|Column List, second panel]], and [[#Column List, third panel\|Column List, third panel]], show a Column List panel with user column selections filled in.

	<p class="caption">Column List panel</p>		<p class="caption" style="width:400px">Column List panel</p>
	<p class="figure">[[File:SQL_Server_UG_fig_5-4_Column_List.gif]]</p>		<p class="figure">[[File:SQL_Server_UG_fig_5-4_Column_List.gif]]</p>

	<p class="caption">Column List, second panel</p>		<p class="caption" style="width:400px">Column List, second panel</p>
	<p class="figure">[[File:SQL_Server_UG_fig_5-5_Column_List_2nd.gif]]</p>		<p class="figure">[[File:SQL_Server_UG_fig_5-5_Column_List_2nd.gif]]</p>

	<p class="caption">Column List, third panel</p>		<p class="caption" style="width:400px">Column List, third panel</p>
	<p class="figure">[[File:SQL_Server_UG_fig_5-6_Column_List_3rd.gif]]</p>		<p class="figure">[[File:SQL_Server_UG_fig_5-6_Column_List_3rd.gif]]</p>

Line 384:		Line 384:
	[[#Column Attributes panel\|Column Attributes panel]] shows a Column Attributes panel before any user modifications.		[[#Column Attributes panel\|Column Attributes panel]] shows a Column Attributes panel before any user modifications.

	<p class="caption">Column Attributes panel</p>		<p class="caption" style="width:400px">Column Attributes panel</p>
	<p class="figure">[[File:SQL_Server_UG_fig_5-7_Column_Attr.gif]]</p>		<p class="figure">[[File:SQL_Server_UG_fig_5-7_Column_Attr.gif]]</p>

	The following figure shows the Column Attributes panel after user modifications.		The following figure shows the Column Attributes panel after user modifications.

	<p class="caption">Modified Column Attributes panel</p>		<p class="caption" style="width:400px">Modified Column Attributes panel</p>
	<p class="figure">[[File:SQL_Server_UG_fig_5-8_Mod_Column_Attr.gif]]</p>		<p class="figure">[[File:SQL_Server_UG_fig_5-8_Mod_Column_Attr.gif]]</p>
	===Specifying attributes===		===Specifying attributes===
Line 499:		Line 499:
	[[#Completion panel\|Completion panel]] shows an example of a Completion panel with user specifications for generating the DDL to the OUTDDL file.		[[#Completion panel\|Completion panel]] shows an example of a Completion panel with user specifications for generating the DDL to the OUTDDL file.

	<p class="caption">Completion panel</p>		<p class="caption" style="width:400px">Completion panel</p>
	<p class="figure">[[File:SQL_Server_UG_fig_5-9_Completion.gif]]</p>		<p class="figure">[[File:SQL_Server_UG_fig_5-9_Completion.gif]]</p>

Line 566:		Line 566:
	When you complete your definition, press PF5 to edit the panel, store the updates, and provide an empty panel to generate a new multi-column unique definition. Press PF7 or PF8 to move to the previous or to the next definition.		When you complete your definition, press PF5 to edit the panel, store the updates, and provide an empty panel to generate a new multi-column unique definition. Press PF7 or PF8 to move to the previous or to the next definition.

	<p class="caption">Multi-Column Unique panel</p>		<p class="caption" style="width:400px">Multi-Column Unique panel</p>
	<p class="figure">[[File:SQL_Server_UG_fig_5-10_Multi-Col_Unique.gif]]</p>		<p class="figure">[[File:SQL_Server_UG_fig_5-10_Multi-Col_Unique.gif]]</p>
	===Specifying a multicolumn unique key===		===Specifying a multicolumn unique key===
Line 621:		Line 621:
	[[#Grant Authority panel\|Grant Authority panel]] shows a Grant Authority panel with user specifications for granting privileges for all four DML actions to all valid Model 204 SQL users. These users can grant these privileges to others ("With grant option" clause of GRANT statement is included).		[[#Grant Authority panel\|Grant Authority panel]] shows a Grant Authority panel with user specifications for granting privileges for all four DML actions to all valid Model 204 SQL users. These users can grant these privileges to others ("With grant option" clause of GRANT statement is included).

	<p class="caption">Grant Authority panel</p>		<p class="caption" style="width:400px">Grant Authority panel</p>
	<p class="figure">[[File:SQL_Server_fig_5-11_Grant_Authority.gif]]</p>		<p class="figure">[[File:SQL_Server_fig_5-11_Grant_Authority.gif]]</p>
	===Authority===		===Authority===
Line 650:		Line 650:

	<p class="note"><b>Note:</b> The DDL at the bottom of the generated stream that defines the multicolumn unique key is displayed for example purposes only. You cannot generate such DDL using an unaltered version of the Model 204 demonstration database. The demonstration database includes no field designed to serve as a multicolumn unique key.</p>		<p class="note"><b>Note:</b> The DDL at the bottom of the generated stream that defines the multicolumn unique key is displayed for example purposes only. You cannot generate such DDL using an unaltered version of the Model 204 demonstration database. The demonstration database includes no field designed to serve as a multicolumn unique key.</p>
	<p class="caption">TSF-generated DDL</p>		<p class="caption" style="width:400px">TSF-generated DDL</p>
	<p class="code">SET SCHEMA DEMO		<p class="code" style="width:400px">SET SCHEMA DEMO
	CREATE TABLE CLIENTS		CREATE TABLE CLIENTS
	( CITY		( CITY
Line 696:		Line 696:
	<p>		<p>
	After generating DDL to an output file, you receive the following message:</p>		After generating DDL to an output file, you receive the following message:</p>
	<p class="code"><nowiki>**********************************************************************		<p class="code" style="width:550px"><nowiki>**********************************************************************
	* *		* *
	* WARNING: Generation of DDL to an output file causes this utility *		* WARNING: Generation of DDL to an output file causes this utility *

ELowell: /* DDL processing */

2015-03-12T23:19:32Z

DDL processing

← Older revision		Revision as of 23:19, 12 March 2015
Line 10:		Line 10:
	The following figure shows the relationship between the Model 204 files, the TSF, manually created DDL, the CVI utility, and the SQL catalog.		The following figure shows the relationship between the Model 204 files, the TSF, manually created DDL, the CVI utility, and the SQL catalog.

	<p class="caption">DDL processing overview</p>		<p class="caption" style="width:350px">DDL processing overview</p>

	<p class="figure">		<p class="figure">

ELowell: /* Specifying GRANT authority (Grant Authority panel) */

2015-03-12T22:32:06Z

Specifying GRANT authority (Grant Authority panel)

← Older revision		Revision as of 22:32, 12 March 2015
Line 622:		Line 622:

	<p class="caption">Grant Authority panel</p>		<p class="caption">Grant Authority panel</p>
	<p class="figure">[[File:~~SQL_Server_UG_fig_5~~-11_Grant_Authority.gif]]</p>		<p class="figure">[[File:SQL_Server_fig_5-11_Grant_Authority.gif]]</p>
	===Authority===		===Authority===
	You must specify at least one authority value. Valid values are SELECT, INSERT, DELETE, UPDATE, and ALL. If you specify ALL, do not specify any other value.		You must specify at least one authority value. Valid values are SELECT, INSERT, DELETE, UPDATE, and ALL. If you specify ALL, do not specify any other value.