We use cookies to give you the best experience possible. By continuing we’ll assume you’re on board with our cookie policy

See Pricing

What's Your Topic?

Hire a Professional Writer Now

The input space is limited by 250 symbols

What's Your Deadline?

Choose 3 Hours or More.
Back
2/4 steps

How Many Pages?

Back
3/4 steps

Sign Up and See Pricing

"You must agree to out terms of services and privacy policy"
Back
Get Offer

Sample Database to Help Users of Nonstop Sql/Mp

Hire a Professional Writer Now

The input space is limited by 250 symbols

Deadline:2 days left
"You must agree to out terms of services and privacy policy"
Write my paper

S Sample Database To help users of NonStop SQL/MP become familiar with the product’s features, Tandem includes a sample database and sample application on the product site update tape (SUT). The sample database demonstrates the use of NonStop SQL/MP in a Pathway transaction processing environment. It includes several host language programs that use embedded SQL statements to access the sample database. Users can also access the sample database with SQLCI commands. The source code, database creation and load ? les, and installation instructions for the sample database and application are stored on a ? e named DOCUMENT on a subvolume named ZTSQLMSG when NonStop SQL/MP is installed. (The volume name is speci? ed at installation time by the user who installs NonStop SQL/MP. To determine the volume name, check with the group that installs NonStop SQL/MP at your site. ) If you set your volume default to the appropriate disk volume, you can use the following command to print the installation instructions: 35> TGAL /IN ZTSQLMSG. DOCUMENT, OUT $S. #printer/ The sample database is used as the basis for many examples in the NonStop SQL/MP manuals.

In the examples, most of the sample database is presented as installed on three subvolumes—PERSNL, SALES, and INVENT—on a single volume. Each subvolume contains a catalog and tables relating to a speci? c operation in the organization as follows: PERSNL SALES Contains the EMPLOYEE, JOB, and DEPT tables, which hold personnel data. Contains the CUSTOMER, ORDERS, ODETAIL, and PARTS tables, which are used for order data. Also contains the SUPPKANJ table, which accepts Kanji data for the supplier’s name and address. Contains the SUPPLIER, PARTSUPP, PARTLOC, and ERRORS tables, which hold inventory data. INVENT

Don't use plagiarized sources. Get Your Custom Essay on
Sample Database to Help Users of Nonstop Sql/Mp
Just from $13,9/Page
Get custom paper

One table in the sample data base—the PARTLOC table—can be installed as a partitioned table. The examples show it as partitioned over three volumes on two different nodes. See the DOCUMENT ? le for additional details about the sample database or the sample application. See the NonStop SQL/MP programming manual for the host language you use for a ? gure that shows the tables in the sample data base and the relationships between them. NonStop SQL/MP Reference Manual—142115 S-1 SAVE Command SAVE Command SAVE is an SQLCI command that saves in a ? le the values of one or more session attributes in command format.

The ? le can be executed later with an OBEY command. SAVE {ALL } {ENV } {DEFINES } { } { { [ PARAM ] param-name [, param-name ] … } } { { PARAM * } } { } { { [ LAYOUT ] layout-opt [,layout-opt ] … } } { { LAYOUT * } } { } { [ SESSION ] { session-opt [, session-opt ]… }} { {* }} { } { { [ STYLE ] style-opt [, style-opt ] … } } { { STYLE * } } { } { REPORT { report-cmd [, report-cmd ] … } } { { * } } { } { COMMAND { “command-string”} } { { number } } { { -number } } TO file [ ( section-name ) ] [ CLEAR ] ; ALL saves all the session attributes: environmental parameters, DEFINEs, user-de? ed parameters, session options, layout options, style options, current report formatting commands, and the most recent SELECT command. ENV saves the environmental attributes: SYSTEM, VOLUME, CATALOG, OUT, LOG, and OUT_REPORT. DEFINES saves the current =_DEFAULTS DEFINE as an ALTER DEFINE command and saves all other current DEFINEs as ADD DEFINE commands. { [ PARAM ] param-name [, param-name ] … } { PARAM * } saves user-de? ned parameters as SET PARAM commands; param-name is the name of a user-de? ned parameter. You can specify a list of parameter names to save NonStop SQL/MP Reference Manual—142115 S-2 SAVE Command peci? c parameters or specify an asterisk (*) to save the names and values of all current parameters. { [ LAYOUT ] layout-opt [, layout-opt ] … } { LAYOUT * } saves the layout options you specify; layout-opt is a single layout option. An asterisk (*) speci? es all layout options. See SET LAYOUT Command on page S-35 for more information about layout options. [ SESSION ] { session-opt [, session-opt ] … } { * } saves the session options you specify; session-opt is a single session option. An asterisk (*) speci? es all session options. See SET SESSION Command on page S-39 for more information about session options. [ STYLE ] style-opt [, style-opt ] … } { STYLE * } saves the style options you specify; style-opt is a single style option. STYLE * speci? es all style options. See SET STYLE Command on page S-46 for more information about style options. REPORT { report-cmd [, report-cmd ] … } { * } saves the report formatting commands you specify and, if speci? ed, the most recent SELECT command. report-cmd is one of these commands: BREAK, BREAK FOOTING, BREAK TITLE, DETAIL, NAME, PAGE FOOTING, PAGE TITLE, REPORT FOOTING, REPORT TITLE, SELECT, SUBTOTAL, and TOTAL. REPORT * speci? es all of these commands.

If you enter multiple BREAK TITLE, BREAK FOOTING, NAME, or SUBTOTAL commands, all versions are saved. To save a speci? c version of a command from the history buffer, enter SAVE COMMAND command-string. COMMAND {“command-string”} {number } {-number } saves commands stored in the history buffer. command-string is a character string that speci? es the most recent command in the history buffer that begins with the string. NonStop SQL/MP Reference Manual—142115 S-3 Examples—SAVE number is a positive integer that refers to the ordinal number of a command in the history buffer. -number s a negative integer that indicates the position of a command in the history buffer relative to the current command. See HISTORY Command on page H-4 for more information. TO file speci? es a disk ? le, process ? le, or terminal name. If the ? le does not exist, SQLCI creates an EDIT ? le. section-name is the simple name of a section header of the form ? SECTION section-name. SQLCI writes the section header in the line preceding the attribute values you are saving. CLEAR directs SQLCI to clear the disk or process ? le before saving the commands. If you omit this parameter, SQLCI appends the commands to existing text.

You cannot clear individual sections; SQLCI clears the whole ? le. The SAVE command is useful for creating an OBEY command ? le that contains DEFINEs you use repeatedly or a report de? nition you have created using the report options and commands. Examples—SAVE • Suppose you enter the following commands during your SQLCI session: >> VOLUME \SYS1. $VOL1. INVENT; >> LOG SUBV2. MAYLOG; >> SYSTEM \SYS1; >> ADD DEFINE =EMPLOYEE, CLASS MAP, +> FILE \SYS1. $VOL1. PERSNL. EMPLOYEE; >> ADD DEFINE =ORDERS, CLASS MAP, +> FILE \SYS1. $VOL1. SALES.

ORDERS; >> ADD DEFINE =CAT, CLASS CATALOG, +> SUBVOL \SYS1. $VOL1. PERSNL; NonStop SQL/MP Reference Manual—142115 S-4 Search Conditions You want to set up this session environment and add these DEFINEs each time you execute a particular query. To save the commands, enter the following commands: >> SAVE ENV TO SETUP1 CLEAR; >> SAVE DEFINES TO SETUP1; The ? rst command saves the VOLUME, LOG, and SYSTEM commands in the ? le SETUP1. The next command appends all of the ADD DEFINE commands to SETUP1. To set up the environment and DEFINEs, enter: >> OBEY SETUP1;

Search Conditions A search condition is a set of predicates (or other search conditions) combined with logical operators (AND, OR, or NOT) that speci? es criteria for choosing rows from tables or views. You can use a search condition in the WHERE clause in a SELECT, DELETE, or UDPATE statement; in the HAVING clause in a SELECT statement; in the ON clause in a SELECT statement that involves a join; in the CHECK clause in the CREATE CONSTRAINT statement; and in the select-statement portion of a subquery or of the INSERT or CREATE VIEW statement. [ NOT ] { p } { (p) } [ { AND } [ NOT ] { p } ] … { OR } { (p) } ] p is: BETWEEN Comparison predicate (=, , , =) EXISTS IN LIKE NULL Quantified predicate (ANY, ALL, or SOME) search-condition NOT reverses the truth value of the subsequent predicate or search condition. BETWEEN Comparison predicate, EXISTS, IN, LIKE, NULL, and Quantified predicate are predicates that specify conditions that must be satis? ed for a row to be operated on. For more information, see the entries for speci? c predicate types. search-condition is another search condition. NonStop SQL/MP Reference Manual—142115 S-5 Considerations—Search Conditions OR speci? s that the search condition is true if either of the surrounding predicates or search conditions are true. AND speci? es that the search condition is true only if both the surrounding predicates or search conditions are true. Considerations—Search Conditions • • • Order of evaluation SQL evaluates search conditions in the following order, ? rst to last: predicates within parentheses, NOT, AND, OR. Column references Within a search condition, a reference to a column refers to the value of that column in the row evaluated by the search condition. Subqueries If a search condition contains a predicate of the form xpression comparison-operator subquery and the subquery returns no values, the predicate evaluates to null. If you include a subquery in a search condition, SQL applies the subquery to each row of the table that is the result of the previous clauses, the uses that result to evaluate the search condition in relation to a speci? c row. • Operation A statement that contains a search condition operates on a row only if that row satis? es the search condition. For example, in a DELETE statement, any row that satis? es the search condition speci? ed in the WHERE clause is deleted.

In a SELECT statement, from each row or group of rows that satis? es the search condition, the columns speci? ed in the select list are returned. A search condition connected by the OR operator might execute successfully even though it includes a predicate that can evaluate to false or null. If any predicate is true, the values of the remaining predicates are irrelevant. • Evaluation to null If a search condition contains a predicate of the form expression comparison-operator subquery and the subquery returns no values, the predicate evaluates to null. NonStop SQL/MP Reference Manual—142115 S-6

Examples—Search Conditions For example, the following predicate evaluates to null because the subquery returns no value (there is no part number with more than 1500 units in stock): PARTNUM = (SELECT PARTNUM FROM ODETAIL WHERE QTY_ORDERED > 1500) Examples—Search Conditions • The following example searches for values in rows where the quantity is less than 9, the delivery date is prior to November 2, 1991, and the order number in the ORDERS table equals the order number in the ODETAIL table: QTY_ORDERED < 9 AND DELIV_DATE > SECURE *. *. * FROM CATALOG $VOL1.

SUBV1 “NUUU”; >> (press the Break key) >> SECURE *. *. * FROM CATALOG $VOL1. SUBV1 “NUUU”; You can also use the FC command to repeat the same SECURE command. Note that restarting a partially-completed SECURE operation from the beginning can cause errors if the operation changes ownership. You might no longer have authority to specify ownership for the ? les whose ownership has already changed. You can avoid this problem by using the START option in qualifiedfileset-list to restart the operation from the point at which it stopped. Examples—SECURE Command • • •

The following command resecures all SQL objects from catalog $VOL1. PERSNL located on subvolume DEPT or JOB so that users on other nodes in the network can read them but only the owner can write to or purge them: SECURE (DEPT. *, JOB. *) FROM CATALOG $VOL1. PERSNL, “NUUU”; The following command changes the security and sets the CLEARONPURGE attribute of all tables, views, and programs from catalog CAT on subvolume SV: SECURE SV. * FROM CATALOG CAT, “NUUU” CLEARONPURGE ON; Each of the following SECURE commands uses a different form of a Guardian user ID to change the owner of a table: SECURE $VOL1.

PERSNL. EMPLOYEE,, OWNER DEPT3. MGR NO LISTALL; SECURE $VOL1. PERSNL. EMPLOYEE,, OWNER 9,001 NO LISTALL; NonStop SQL/MP Reference Manual—142115 S-10 SECURE File Attribute SECURE File Attribute SECURE is a Guardian ? le attribute that corresponds to the security string that controls Guardian security for a table, index, collation, protection view, or Guardian ? le. SECURE “rwep” “rwep” is a four-character string that speci? es the Guardian read, write, execute, and purge security for an object or ? le. (See Security on page S-11 for more information. ) Considerations—SECURE File Attribute • Write access requires read access To provide user groups with write access to tables, views, indexes, or catalogs, you must specify read access as well as write access for those groups. Network access requires remote passwords A SECURE attribute that permits access to a ? le from other nodes in the network is necessary, but not suf? cient, to allow users to access the ? le over the network. Network users must also be authorized to access the node on which the ? le resides as well as authorized to access that speci? c ? le. Examples—SECURE File Attribute • •

The following SECURE attribute permits other users on the network to execute the ? le but permits only the owner to read, write, or purge the ? le: SECURE “OONO” The following SECURE attribute permits other users on the network to read or update the ? le but permits only the owner to purge or execute the ? le: SECURE “NNOO” Security Authorization to access NonStop SQL/MP objects is maintained by the Guardian environment and checked by NonStop SQL/MP. Each NonStop SQL/MP object has associated security values that determine who can read, write to, execute, and purge the object.

SQL programs in Guardian ? les and other Guardian ? les used in conjunction with NonStop SQL/MP applications also use the security provided by the Guardian environment. Each Guardian ? le has an associated set of security values like those for NonStop SQL/MP objects. NonStop SQL/MP Reference Manual—142115 S-11 User IDs NonStop SQL/MP objects, SQL programs in Guardian ? les, and other Guardian ? les can optionally use the Safeguard security management facility, a product that provides security features beyond those of standard Guardian security.

The Safeguard subsystem can secure NonStop SQL/MP objects at the volume or subvolume level and can secure all other Guardian ? les at the volume, subvolume, or ? le level. SQL programs in OSS ? les and other OSS ? les use OSS security, which differs from Guardian security. A user who runs an SQL program in an OSS ? le has both an OSS identity (which determines the user’s authorization to access OSS ? les) and a corresponding Guardian identity (which determines the user’s authorization to access NonStop SQL/MP objects and other Guardian ? les).

The remainder of this entry describes the general principles of Guardian security as they relate to access to NonStop SQL/MP objects, including access from both Guardian and OSS NonStop SQL/MP programs. For more detailed information about Guardian, Safeguard, and OSS security, see the Guardian User’s Guide, the Safeguard Reference Manual, and the OSS User’s Guide. User IDs Each user authorized to log on to a node in a Tandem NonStop network is identi? ed by a Guardian user ID that consists of a Guardian group number and Guardian user number and that corresponds to a Guardian group name and Guardian user name.

The Guardian user ID is the combination of the group number and user number (not the user number alone) or the combination of the group name and user name (not the user name alone). The user ID is normally represented in one of the following forms: 8,55 DEVEL. JIM –Sets wide report line –From VIEWS table WHERE VIEWNAME = “view-name”; –Fully qualified name –in uppercase letters A grouped view is a view de? ned with a CREATE VIEW AS clause that contains a GROUP BY or HAVING clause that is not in a subquery, contains an aggregate function in the select list, or refers to a grouped view in the FROM clause.

A shorthand view whose de? nition is based on a union of SELECT statements or that contains a LEFT JOIN operator, cannot participate in another join operation. A shorthand view whose de? nition contains any join operation cannot be the inner table of a LEFT JOIN. • GROUP BY clause and the select list NonStop SQL/MP Reference Manual—142115 S-24 Considerations for UNION If you include a GROUP BY clause, the columns you refer to in expressions in the select list must be either grouping columns or arguments of a function. There will be no more than one row in the result table for each group.

For example, if AGE is not a grouping column, you can refer to AGE only in a function invocation, such as AVG (AGE). If you do not include a GROUP BY clause, but you specify a function in the select list, all rows of the result table form a group. AVG and SUM result in a single value for the table, and COUNT counts all rows. In this case, the select list can contain only functions because there are no grouping columns. If you specify a GROUP BY clause, a function applies to each row of each group. The result of AVG and SUM is a value for each group. COUNT returns, for each group, the number of rows in that group. • Multibyte character sets Columns containing multibyte characters cannot be displayed on all types of output devices. Control of the SORTPROG process for large tables You can alter the =_SORT_DEFAULTS de? ne to specify a scratch ? le name, and certain other options to be used by the SORTPROG process. This technique is useful if you are selecting data from a large table. See =_SORT_DEFAULTS DEFINE on page Z-3 or the FastSort Manual for more information. Considerations for UNION Within the following discussion of the UNION operation: • • • The contributing SELECT statements are called SELECT1 and SELECT2.

The contributing tables resulting from the SELECT statements are called TABLE1 and TABLE2. The table resulting from the UNION operation is called RESULT. Characteristics of UNION Columns For columns in the same ordinal position in TABLE1 and TABLE2: • • • If both columns contain character strings, the corresponding column in RESULT contains a character string whose length is equal to the greater of the two contributing columns. If both columns contain variable-length character strings, then RESULT contains a variable-length character string whose length is equal to the greater of the two contributing columns.

If both columns are of exact numeric data types, then RESULT contains an exact numeric value whose precision and scale are equal to the greater of the two contributing columns. NonStop SQL/MP Reference Manual—142115 S-25 ORDER BY clause and UNION operator • • If both columns are of approximate (? oating point) numeric data types, then RESULT contains an approximate numeric value whose precision is equal to the greater of the two contributing columns. If both columns are of date-time data types, then RESULT contains a DATETIME value whose precision is the most signi? cant start ? eld to the least signi? cant end ? ld from the ranges of DATETIME ? elds in the contributing columns. For example, if the column in TABLE1 shows YEAR TO DAY and the column in TABLE2 shows MONTH TO MINUTE, the precision of the corresponding column in RESULT is YEAR TO MINUTE. If both columns are of INTERVAL data type, then RESULT contains an INTERVAL value whose range of ? elds is the most signi? cant start ? eld to the least signi? cant end ? eld from the ranges of INTERVAL ? elds in the contributing columns. The range of INTERVAL ? elds in RESULT must not contain both year-month and daytime ? elds. (The year-month ? elds are YEAR and MONTH.

The day-time ? elds are DAY, HOUR, MINUTE, SECOND, and FRACTION. ) If both columns are described with NOT NULL, then RESULT does not allow null values; otherwise, RESULT allows null values. The view cannot participate in a join operation. A SELECT operation on the view cannot specify a GROUP BY or HAVING clause. A SELECT operation on the view cannot specify aggregate functions on any view column. • • • • • For shorthand views using UNION, the following restrictions apply: ORDER BY clause and UNION operator In a query containing a UNION operator, the ORDER BY clause de? nes an ordering on the result of the union.

A SELECT statement cannot have an individual ORDER BY clause. You can specify an ORDER BY clause only as the last clause following the ? nal SELECT statement (SELECT2 in this example). The ORDER BY clause in RESULT speci? es the ordinal position of the sort column either by using an integer or by using the column name from the select list of SELECT1. For example, the following SELECT illustrates correct use of ORDER BY: SELECT A FROM T1 UNION SELECT B FROM T2 ORDER BY A The following SELECT is incorrect, however, because the ORDER BYclause does not follow the ? nal SELECT: SELECT A FROM T1 ORDER BY A UNION SELECT B FROM T2

The following SELECT is also incorrect: SELECT A FROM T1 UNION (SELECT B FROM T2 ORDER BY A) Because the subquery (SELECT B FROM T2… ) is processed ? rst, the ORDER BY clause does not follow the ? nal SELECT. NonStop SQL/MP Reference Manual—142115 S-26 GROUP BY Clause, HAVING Clause, and the UNION Operator GROUP BY Clause, HAVING Clause, and the UNION Operator In a query containing a UNION operator, the GROUP BY or HAVING clause is associated with the SELECT statement that it is a part of (unlike the ORDER BY clause, which is associated with the result of a union operation).

The groups are visible in the result table of the particular SELECT statement. The GROUP BY and HAVING clauses cannot be used to form groups in the result of a union operation. UNION ALL and Associativity The UNION ALL operation is associative, meaning that the following two queries return the same result: (SELECT * FROM TABLE1 UNION ALL SELECT * FROM TABLE2) UNION ALL SELECT * FROM TABLE3; SELECT * FROM TABLE1 UNION ALL (SELECT * FROM TABLE2 UNION ALL SELECT * FROM TABLE3); If both the UNION ALL and UNION operators are present in the query, however, the result depends on the order of evaluation.

A parenthesized union of SELECT statements is evaluated ? rst, from left to right, followed by a left-to-right evaluation of the remaining union of SELECT statements. Examples—SELECT • The following SQLCI example retrieves information from the EMPLOYEE table for employees with a job code greater than 500, and employees in departments with numbers less than or equal to 3000, displaying the results in ascending order by job code. No locks are held while the query is processed. >> SET LIST_COUNT 3; >> SELECT JOBCODE, DEPTNUM, FIRST_NAME, LAST_NAME, SALARY +> FROM PERSNL.

EMPLOYEE +> WHERE JOBCODE > 500 AND DEPTNUM ORDER BY JOBCODE +> BROWSE ACCESS; JOBCODE DEPTNUM FIRST_NAME LAST_NAME SALARY ——- ——- ————— ———– ———600 1500 JIMMY SCHNEIDER 26000. 00 600 1500 JONATHAN MITCHELL 32000. 00 900 1000 SUE CRAMER 19000. 00 S> LIST NEXT 2; 900 1500 SUSAN CHAPMAN 17000. 00 900 2000 BILL WINN 32000. 00 S> CANCEL; NonStop SQL/MP Reference Manual—142115 S-27 Examples—SELECT • The following SQLCI example displays selected rows grouped by job code in ascending order.

The select-list contains only grouping columns and functions because each group results in one row. >> SELECT JOBCODE, AVG (SALARY) +> +> +> +> FROM PERSNL. EMPLOYEE WHERE JOBCODE > 500 AND DEPTNUM > SELECT JOBCODE, AVG (SALARY) +> +> +> +> +> FROM PERSNL. EMPLOYEE WHERE DEPTNUM 500 ORDER BY JOBCODE; • The following example selects data from more than one table by specifying the table names in the FROM clause and specifying the condition for selecting rows of the result in the WHERE clause. The condition is called a join predicate.

This query joins the EMPLOYEE and JOB tables by combining each row of the EMPLOYEE table with each row of the JOB table; the result is the Cartesian product of the two tables. The join predicate speci? es that any row with equal job codes is included in the result table. All other rows are eliminated. >> VOLUME $VOL1. PERSNL; >> SELECT JOBDESC, FIRST_NAME, LAST_NAME, SALARY +> +> +> FROM EMPLOYEE, JOB WHERE EMPLOYEE. JOBCODE = JOB. JOBCODE AND EMPLOYEE. JOBCODE IN (900, 300, 420); The logical steps that determine the result of the previous query are as follows:

NonStop SQL/MP Reference Manual—142115 S-28 Examples—SELECT 1. Join the tables. EMPLOYEE Table EMPNUM 1 1 … JOBCODE 100 100 … JOB Table SALARY 175500 175500 . 1 100 175500 900 JOBCODE 100 200 JOBDESC MANAGER PROD SUPV . SECRETARY 568 568 300 300 39500 39500 100 200 MANAGER PROD SUPV 568 300 39500 900 MANAGER 2. Drop rows with unequal job codes. EMPLOYEE Table EMPNUM 1 . 207 . 568 300 420 . 39500 300 … JOBCODE 100 . 33000 420 . SALESREP … JOB Table SALARY 175500 JOBCODE 100 . ENGINEER JOBDESC MANAGER 3. Drop rows with job codes not 900, 300 or 420. EMPLOYEE Table EMPNUM 75 . 178 . 07 . 568 300 420 . 39500 300 900 . 33000 420 . SALESREP … JOBCODE 300 . 28000 900 . ENGINEER … JOB Table SALARY 32000 JOBCODE 300 . SECRETARY JOBDESC SALESREP NonStop SQL/MP Reference Manual—142115 S-29 Examples—SELECT 4. Process select list, leaving only four columns. EMPLOYEE Table JOBDESC SALESREP . SECRETARY . ENGINEER . SALESREP . . MARK . DESIREE . JOHN . FIRST_NAME TIM . CHOU . FOLEY . EVANS 39500 33000 JOB Table LAST_NAME WALKER . 28000 SALARY 32000 • The following SQLCI example selects from three tables and groups the rows by job code and (within job code) by department number.

Only job codes 300, 420, and 900 are selected. The minimum and maximum salary for the same job in each department is computed, and the rows are ordered by maximum salary. >> VOLUME $VOL. PERSNL; >> SELECT E. JOBCODE, E. DEPTNUM, MIN (SALARY), MAX (SALARY) +> +> +> +> +> FROM EMPLOYEE E, DEPT D, JOB J WHERE E. DEPTNUM = D. DEPTNUM AND E. JOBCODE = E. JOBCODE AND E. JOBCODE IN (900, 300, 420) GROUP BY E. JOBCODE, E. DEPTNUM ORDER BY 4; • The following example presents two ways to select data about orders by customers from California.

The price for the total quantity ordered is computed for each order number. >> VOLUME $VOL. SALES; >> SELECT ORDERNUM, SUM (QTY_ORDERED * PRICE) +> +> +> +> +> FROM PARTS P, ODETAIL O WHERE O. PARTNUM = P. PARTNUM AND ORDERNUM IN (SELECT ORDERNUM FROM ORDERS O, CUSTOMER C WHERE O. CUSTNUM = C. CUSTNUM AND STATE = “CALIFORNIA”) GROUP BY ORDERNUM; NonStop SQL/MP Reference Manual—142115 S-30 Examples—SELECT >> VOLUME $VOL1. SALES; >> SELECT ORDERNUM, SUM (QTY_ORDERED * PRICE) +> +> +> +> +> +> FROM PARTS P, ODETAIL O WHERE O. PARTNUM = P.

PARTNUM AND ORDERNUM IN (SELECT ORDERNUM FROM ORDERS WHERE CUSTNUM IN (SELECT CUSTNUM FROM CUSTOMER WHERE STATE = “CALIFORNIA” ) ) GROUP BY ORDERNUM; • The following SQLCI example selects the value in the AUDIT column of the FILES catalog table in the PERSNL catalog. By displaying this column, you can see whether a table is de? ned as audited (Y) or nonaudited (N). >> SELECT AUDIT FROM PERSNL. FILES +> WHERE FILENAME = “\SYS1. $VOL1. PERSNL. JOB”; AUDIT —-Y — 1 row(s) selected. • The following SQLCI example uses a table, T, that has a column, C, of data type CHARACTER.

There are n possible values of C, any of which can occur multiple times. The query returns the percent distribution of any value of C across the entire table. Sample Table T: C -N1 N1 N1 N2 N3 N3 I 1 4 6 2 3 5 NonStop SQL/MP Reference Manual—142115 S-31 SERIALWRITES File Attribute The SQLCI query and the result: >> –Print the percents >> SELECT Y. C, (100. 0*COUNT(*))/(COUNT(DISTINCT X. I) * +> +> +> C -N1 N2 N3 FROM T X, T Y GROUP BY Y. C; (EXPR) ——————–50. 0 16. 6 33. 3 COUNT(DISTINCT X. I)) — 3 row(s) selected.

The query joins table T with itself; X and Y are correlation names so that a query can compare one row of the table with every other row. The GROUP BY Y. C clause produces sets of n*v values for each distinct C value in which the following are true: n is the number of rows in the table. v is the number of occurrences of a particular C value. COUNT(DISTINCT X. I) is n for each group. The second term in the select list represents the following equation: (n*v)/(n*n) = (v/n) The SELECT statement displays a distinct value of C together with its percentage distribution over the table.

SERIALWRITES File Attribute SERIALWRITES is a Guardian ? le attribute that speci? es whether to write data serially or in parallel to the two disk devices that make up a mirrored volume. SERIALWRITES applies to key-sequenced, relative, and entry-sequenced tables and to indexes. { SERIALWRITES | NO SERIALWRITES } SERIALWRITES NO SERIALWRITES Selects serial mirror writes Selects parallel mirror writes The table default is SERIALWRITES. NonStop SQL/MP Reference Manual—142115 S-32 Considerations—SERIALWRITES The index default is its table’s value at index creation. Considerations—SERIALWRITES Selecting a SERIALWRITES value For audited tables and indexes, use NO SERIALWRITES. For nonaudited tables or indexes, weigh the value of performance versus data reliability. SERIALWRITES can degrade response time, but it can also improve the reliability of data not protected by TMF auditing. • Explanation of serial versus parallel writes SERIALWRITES tells the system to write one data block at a time to a mirrored pair of disks. If a system failure occurs, the failure affects only one disk, so the system uses the good copy of the data block after the failure.

With parallel writes (NO SERIALWRITERS), the system writes to both disks of a mirrored pair simultaneously; performance is improved, but a system failure can affect both copies of the block being written. SET DEFINE Command SET DEFINE is an SQLCI command that sets a value for one or more DEFINE attributes in the working attribute set. The working attribute set determines values for attributes you do not specify in an ADD DEFINE command. (SET DEFINE is similar to the TACL command SET DEFINE and the OSS command set_de? ne. ) SET DEFINE { LIKE define } [, attr value ] … ; { attr value }

LIKE define speci? es the name of an existing DEFINE to use as a model for the new values of the working attribute set, optionally modi? ed by attr value pairs that follow the LIKE clause. If you use the LIKE clause, you cannot specify the CLASS attribute. attr value is the name and value of a DEFINE attribute to add to the working attribute set. (See DEFINEs on page D-26 for information about DEFINE attributes. ) Considerations—SET DEFINE • The working attribute set consists of values for the attributes of the current class. Only one class of attributes can be in the working set at one time.

NonStop SQL/MP Reference Manual—142115 S-33 Examples—SET DEFINE For example, if you use SET DEFINE to set attribute values for class TAPE and enter the following command, the working set provides values for all attributes except FILEID: ADD DEFINE =T, CLASS TAPE, FILEID BACKUP • • • • • • • Attributes are set in the order they are speci? ed. Because the CLASS attribute erases the working set, you should set the CLASS attribute ? rst, then set values for the other attributes. When you include CLASS in a SET DEFINE command, you establish a new working attribute set in which each attribute has its initial setting.

If the value of an attribute is a Guardian name or subvolume name, the name is expanded immediately using the current default node, volume, and subvolume. If an error occurs on SET DEFINE, the working attribute set is unchanged. An attribute value does not change until you reset it with the RESET DEFINE command or another SET DEFINE command. SET DEFINE checks that the value you enter is valid for the attribute you specify and that the attribute is valid for the current class. Attribute consistency is not checked until you issue an ADD DEFINE, ALTER DEFINE, or SHOW command.

It may be helpful to use the SHOW DEFINE command to display the current working attribute set before you use a SET DEFINE command. Examples—SET DEFINE In the following example, SET DEFINE establishes a working attribute set for CLASS CATALOG. The ADD DEFINE does not specify any attributes or attribute values, so the working attribute set is associated with the DEFINE. In this case, the =CAT DEFINE is mapped to subvolume \SYS1. $VOL1. PERSNL: SET DEFINE CLASS CATALOG, SUBVOL \SYS1. $VOL1. PERSNL; ADD DEFINE =CAT; SET DEFMODE Command SET DEFMODE is an SQLCI command that enables or disables the use of DEFINEs in the current SQLCI session. SET DEFMODE is similar to the TACL command SET DEFMODE. ) SET DEFMODE { ON } ; { OFF } ON enables the use of DEFINEs. If DEFMODE is ON, you can execute commands that contain DEFINE names and you can create, modify, or delete DEFINEs, display information about DEFINES, and propagate existing DEFINEs to any processes you start from the SQLCI session. NonStop SQL/MP Reference Manual—142115 S-34 Examples—SET DEFMODE ON is the default. OFF disables the use of DEFINEs. With DEFMODE OFF, you cannot execute commands that contain DEFINE names and you cannot add DEFINEs or propagate existing DEFINEs to another process.

You can modify, delete, and display information about existing DEFINEs. Examples—SET DEFMODE • The following example enables the use of DEFINEs: >> SET DEFMODE ON; SET LAYOUT Command SET LAYOUT is an SQLCI report writer command that sets layout options. Layout options affect the way a report appears on the screen or printed page. SET [ LAYOUT ] option [ , option ] … ; option is: { { { { { { { { { CENTER_REPORT LEFT_MARGIN LINE_SPACING LOGICAL_FOLDING PAGE_COUNT PAGE_LENGTH RIGHT_MARGIN SPACE WINDOW { OFF | ON } number number { ON | OFF } { number | ALL } { number | ALL } number number { TAB number | column } } } } } } } } } }

You cannot specify the same option more than once in a single SET LAYOUT command. For information about a speci? c option, see the entry for that option. Examples—SET LAYOUT • The following command sets the left margin for reports at byte position eight and also sets double spacing: >> SET LAYOUT LEFT_MARGIN 8, LINE_SPACING 2; NonStop SQL/MP Reference Manual—142115 S-35 SET PARAM Command SET PARAM Command SET PARAM is an SQLCI command that sets values for parameters in your SQLCI session. SET PARAM overrides parameter values you set prior to entering SQLCI, but only for the duration of the SQLCI session.

SET [ PARAM ] ? param value [ , ? param value ]… ; value is: { literal } { CURRENT_TIMESTAMP } { COMPUTE_TIMESTAMP (date) } ?param is the name of the parameter to receive a value. An SQL parameter name is an SQL identi? er preceded by a question mark. literal is a numeric or string literal, optionally enclosed in single or double quotation marks. Enclosing quotation marks are required only for string literals that include blank or comma characters; they make no different otherwise. For example, the following SET PARAM commands are equivalent: SET PARAM ? NUM 9001, ? STR string; SET PARAM ? NUM “9001”, ?

STR “string”; Within enclosing quotes of the same type, two quotes are treated as a single quote. For example, the following SET PARAM commands are equivalent: SET PARAM ? QUOTE ” “” “; SET PARAM ? QUOTE ‘ ” ‘; CURRENT_TIMESTAMP is the Julian timestamp for the current date and time. COMPUTE_TIMESTAMP (date) is the Julian timestamp for the date and time you specify in date in the following form: {mm/dd/yyyy } {mm/dd/yyyy hh:nn:ss:mss:uss} yyyy Year, from 1 through 3999, 1 to 4 digits mm dd Month, from 1 through 12, 1 to 2 digits Day, from 1 through 31, 1 to 2 digits NonStop SQL/MP Reference Manual—142115 S-36

Considerations—SET PARAM hh nn ss mss uss Hour, from 0 through 23, 1 to 2 digits Minute, from 0 through 59, 1 to 2 digits Second, from 0 through 59, 1 to 2 digits Millisecond, from 0 through 999, 1 to 3 digits Microsecond, from 0 through 999, 1 to 3 digits date cannot be an expression. Considerations—SET PARAM • Data types for parameters SQL determines the data type for a parameter based on how you use the parameter in an SQL statement. As a result, a literal value you assign to a parameter might be interpreted differently in different SQL statements.

For example, the string “123” assigned to a parameter might be treated as an integer, a character string, or as an illegal value—depending on whether you use the parameter in a statement that requires an integer, a character string, or a date-time value. Similarly, a parameter that appears to have the value of a string literal with an associated character set, such as _KANJI”c1c2” (where “c1c2” indicates one double-byte character) is interpreted as having the following value: c1c2 _KANJI”c1c2” _K if the data type is CHAR(2) CHAR SET KANJI if the data type is CHAR(12) if the data type is CHAR(2)

See Parameters on page P-12 for information about the way SQL determines data types for parameters. See CAST Function on page C-4 for information about using the CAST function to specify a numeric or character data type for a parameter. • Passing parameters to report formatting commands You cannot use parameters in report formatting commands. A method for using TACL macros to pass parameters to report formatting commands is described in the NonStop SQL/MP Report Writer Guide. Examples—SET PARAM • In the following example, the SELECT statement in the FINDSUP2 ? le ? nds suppliers of a speci? ed part.

The suppliers are located in a speci? ed state. VOLUME $VOL1. INVENT; SELECT S. SUPPNUM, SUPPNAME FROM SUPPLIER S, PARTSUPP WHERE S. SUPPNUM = PARTSUPP. SUPPNUM AND PARTNUM = ? PN AND STATE = ? ST; NonStop SQL/MP Reference Manual—142115 S-37 Examples—SET PARAM Before you can execute the SELECT statement, you must specify the state and part number with a SET PARAM command, as shown: >> SET PARAM ? ST TEXAS, ? PN 4103; >> OBEY FINDSUP2; You do not have to enclose TEXAS in quotation marks because SQLCI determines from the STATE column de? nition that the column has a character data type. •

In the following examples, suppose that dates are in timestamp format and that the OBEY command ? le INSORD contains the following commands: INSERT INTO SALES. ORDERS VALUES (? ONUM, ? TODAY, ? DDATE, ? REP, ? CUSTNUM); To insert an order, set the order date to the current day and enter a speci? c delivery date using the date conversion functions. The numbers of the order, sales representative, and customer are entered as numeric literals: >> SET PARAM ? ONUM 800661, ? TODAY CURRENT_TIMESTAMP, +> ? DDATE COMPUTE_TIMESTAMP ( 5/23/1988 ), +> ? REP 221, ? CUSTNUM 7654; >> OBEY INSORD;

The following values are inserted in ORDERS: ORDERNUM ORDER_DAT E DELIV_DATE SALESREP CUSTNUM 800661 (timestamp for current date and time) (timestamp for 5/23/88) 221 7654 Suppose you have set values for some of the parameters of the previous INSERT command using the PARAM command before starting SQLCI: 4> PARAM ONUM 400410, CUSTNUM 7654 5> SQLCI Before executing the INSORD OBEY ? le, you change the value of the order number parameter and set values for the other parameters of the INSERT command: >> SET PARAM ? ONUM 600480, ? TODAY CURRENT_TIMESTAMP, +> ? DDATE COMPUTE_TIMESTAMP (7/5/1988), ?

REP 221; >> OBEY INSORD; NonStop SQL/MP Reference Manual—142115 S-38 SET SESSION Command The following values are inserted in ORDERS: ORDERNUM ORDER_DAT E DELIV_DATE SALESREP CUSTNUM 600480 (timestamp for current date and time) (timestamp for 7/5/88) 221 7654 After setting a different order number and customer number, you execute the OBEY ? le again: >> SET PARAM ? ONUM 600481, ? CUSTNUM 123; >> OBEY INSORD; The following values are inserted into ORDERS: ORDERNUM ORDER_DAT E DELIV_DATE SALESREP CUSTNUM 600481 (timestamp for current date and time) (timestamp for 7/5/88) 221 123 When you exit SQLCI, the values of ?

ONUM and ? CUSTNUM are 400410 and 7654, respectively, as set initially by the PARAM command. SET SESSION Command SET SESSION is an SQLCI command that sets session options for your SQLCI session. SET [ SESSION ] option [ , option ] … ; option is: { { { { { { { { { { AUTOWORK BREAK_KEY DISPLAY_ERROR ERROR_ABORT ERROR_TEXT LIST_COUNT MANDATORY_REPORT STATISTICS WARNINGS WRAP [ { { { { { { { { { ON [ AUDITONLY ] | OFF ] } OFF | ON } } MAIN | ALL } } OFF | ON } } DETAIL | BRIEF } } number| ALL } } OFF | ON } } OFF | ON } } OFF | ON } } OFF | ON } } You cannot specify the same option more than once in a single SET SESSION command.

NonStop SQL/MP Reference Manual—142115 S-39 SET SESSION Command AUTOWORK [ ON [ AUDITONLY ] | OFF ] speci? es whether SQLCI should automatically initiate a TMF transaction when you enter a DML command and how locking works in relation to nonaudited objects locked during the transaction. ON [ AUDITONLY ] directs SQLCI to start a TMF transaction when you enter a DML command, to commit the transaction if the command terminates successfully or to roll back the transaction if the command does not complete successfully, and to release locks on objects after execution of the command.

If you specify AUDITONLY, SQLCI does not release locks on nonaudited objects when the transaction is committed or rolled back. The default when you start SQLCI is AUTOWORK ON (without AUDITONLY). OFF directs SQLCI not to start transactions automatically. BREAK_KEY { OFF | ON } speci? es the SQLCI action when you press the Break key while executing an SQL command or an OBEY command. OFF ON Return control to the previous Break key owner (type PAUSE to resume SQLCI later) Retain control (the IN ? le must be a terminal) Use OFF if you want to prevent someone from interrupting commands at your terminal.

The default when you start SQLCI is BREAK_KEY ON. DISPLAY_ERROR { MAIN } { ALL } controls which errors SQLCI displays when you enter an ERROR command. The setting you specify stays in effect until you set or reset it, or until you end your SQLCI session. MAIN ALL Display ? rst error from most recent command only Display all errors and warnings from the command The default when you start SQLCI is DISPLAY_ERROR ALL. NonStop SQL/MP Reference Manual—142115 S-40 SET SESSION Command ERROR_ABORT { OFF | ON } speci? es the SQLCI action when an error occurs in a noninteractive SQLCI session. (Has no effect on an interactive SQLCI session. OFF ON Continue after error Terminate immediately when an error is encountered The default when you start SQLCI is ERROR_ABORT OFF. ERROR_ABORT affects the process completion code returned to the process creator in the process termination message sent when the SQLCI session ends, as follows: Session Type Interactive Session Noninteractiv e Session ERROR_ABORT OFF OK=0 Warning=0 Error=0 OK=0 Warning=1 Error=2 ERROR_TEXT { DETAIL | BRIEF } ERROR_ABORT ON OK=0 Warning=0 OK=0 OK=0 Warning=1 Error = abend = 5 speci? es the default information SQLCI displays when you enter an ERROR command.

You can override the ERROR_TEXT option with the ERROR command. DETAIL BRIEF Display error text, cause, effect, and suggestions for recovery Display only the error text The default when you start SQLCI is ERROR_TEXT DETAIL. LIST_COUNT { number | ALL } speci? es how many rows of data from a SELECT command to display before a pause. number ALL Display number rows Display all the rows of the result table The default when you start SQLCI is LIST_COUNT ALL. NonStop SQL/MP Reference Manual—142115 S-41 SET SESSION Command MANDATORY_REPORT { OFF | ON } speci? es whether SQLCI should print a report even if the associated query returns zero rows.

This option has no effect when the associated query returns one or more rows. OFF ON Do not print a report when the query does not return any rows. (This is the default. ) Print a report even if the query does not return any rows. The report format uses current report settings. In addition, the report contains the message “— No rows selected. ” when the query does not return any rows. The default when you start SQLCI is MANDATORY_REPORT OFF. STATISTICS { OFF | ON } speci? es whether to display statistics after each DDL, DML, or DCL command executes. OFF ON Do not display statistics Display statistics

The default when you start SQLCI is STATISTICS OFF. See DISPLAY STATISTICS Command on page D-49 for a description of the statistics displayed. WARNINGS { OFF | ON } speci? es whether to display warning messages. (SQLCI always displays error messages. ) OFF ON Do not display warning messages Display warning messages The default when you start SQLCI is WARNINGS ON. WRAP { OFF | ON } speci? es whether SQLCI output lines that exceed the output device width are truncated or continued on the next line. OFF ON Continue (wrap around) to the next line Truncate the line if it does not ? t

The default when you start SQLCI is WRAP ON. NonStop SQL/MP Reference Manual—142115 S-42 Considerations—SET SESSION Output device widths are as follows: Terminal Disk File Unstructured or EDIT Structured Process (for example, Spooler or background) Printer 80 bytes 80 bytes Record length of ? le 132 bytes; 255 if RIGHT_MARGIN > 132) 132 bytes; 255 if RIGHT_MARGIN > 132 Considerations—SET SESSION • You can set up your SQLCI session environment by including the sqlcicommand option when you start SQLCI. For example, you might create an OBEY command ? le named PROFILE that contains these commands: VOLUME $VOL1.

SALES; CATALOG SALES; SET SESSION AUTOWORK OFF, BREAK_KEY OFF; LOG SQLCILOG; Then you can prepare your environment quickly by entering the following command: SQLCI OBEY PROFILE; This starts an SQLCI session, sets the current default volume and subvolume to $VOL1. SALES, sets the current catalog to SALES, turns off the AUTOWORK and BREAK_KEY session options, and begins logging session input and output to the ? le SQLCILOG. • Break key SQLCI responds to the Break key based on the setting of the BREAK_KEY session option as follows: Executing Command SQL commands BREAK_KEY ON Command rolls back; SQLCI prompts.

BREAK_KEY OFF Control returns to previous owner. BREAK_KEY ON or OFF NonStop SQL/MP Reference Manual—142115 S-43 Examples—SET SESSION Executing Command OBEY Command BREAK_KEY ON Command terminates; SQLCI prompts. BREAK_KEY OFF Control returns to previous owner. BREAK_KEY ON or OFF FC command Command terminates and SQLCI prompts; BREAK_KEY has no effect. Command terminates; control returns to previous owner; BREAK_KEY has no effect. Other SQLCI commands (whether or not within user-de? ned transaction) The previous owner is usually the process from which you started the SQLCI session. You can resume execution of the SQL or OBEY command.

An SQL command is a DDL, DML, DCL, PREPARE or EXECUTE command. If one of these commands is rolled back, SQLCI uses ROLLBACK WORK to terminate the current transaction and displays a message. The compiled form of the DDL or DML command is discarded. The next time you execute the command, SQLCI prepares it again and consequently causes a delay. A DDL command that is not executing in a user-de? ned TMF transaction might complete before you press the Break key. To determine whether the command completed, you must examine the database. For example, you can use the FILES command to determine whether an object ou were dropping when you pressed the Break key still exists. If you enter STOP sqlci-process-id at the command interpreter prompt, TMF rolls back the current TMF transaction. Changes to audited objects are undone, but changes to nonaudited objects are not undone. Examples—SET SESSION • To set the AUTOWORK option to OFF, enter: >> SET AUTOWORK OFF; To set AUTOWORK to ON and specify the AUDITONLY option, enter: >> SET AUTOWORK ON AUDITONLY; • To protect an update operation, you can disable the Break key before running the OBEY ? le ORDUPDT. The commands in this ? le update the ORDERS table.

If the NonStop SQL/MP Reference Manual—142115 S-44 Examples—SET SESSION Break key is pressed, SQLCI returns control to the command interpreter until you type PAUSE to resume your operation. >> SET BREAK_KEY OFF; >> OBEY ORDUPDT; (Break key is pressed. ) 5> PAUSE • Suppose the BREAK_KEY option is ON. During a TMF transaction that updates the price of each part in the PARTS table by 5 percent, you press the Break key. SQLCI rolls back the transaction. >> SET BREAK_KEY ON; >> BEGIN WORK; >> UPDATE INVENT. PARTS SET PRICE = PRICE * 1. O5; (Break key is pressed. *** ERROR [10088] Command terminated by BREAK • • To set the DISPLAY_ERROR option to MAIN, enter: >> SET DISPLAY_ERROR MAIN; To set the ERROR_TEXT option to BRIEF, enter: >> SET ERROR_TEXT BRIEF; To direct SQLCI to display ten rows at a time, enter: >> SET LIST_COUNT 10; >> To enable the display of statistics and disable continuation of text to the next line, enter: >> SET STATISTICS ON, WRAP OFF; NonStop SQL/MP Reference Manual—142115 S-45 SET STYLE Command SET STYLE Command SET STYLE is an SQLCI report writer command that sets style options. Style options affect the appearance of speci? report items, such as underlines, headings, and date and time formats. SET [ STYLE ] option [ , option ] … ; option is: { { { { { { { { { { { DATE_FORMAT DECIMAL_POINT HEADINGS NEWLINE_CHAR NULL_DISPLAY OVERFLOW_CHAR ROWCOUNT SUBTOTAL_LABEL TIME_FORMAT UNDERLINE_CHAR VARCHAR_WIDTH date-format } { “. ” | “,” }} { ON | OFF } } “character” } “character” } “character” } { ON | OFF } } “label” } time-format } “character” } number } The default settings are: DATE_FORMAT DECIMAL_POINT HEADINGS NEWLINE_CHAR NULL_DISPLAY OVERFLOW_CHAR ROWCOUNT SUBTOTAL_LABEL TIME_FORMAT UNDERLINE_CHAR VARCHAR_WIDTH M2/D2/Y2 period (. ON slash (/) question mark (? ) asterisk (*) ON * HP2:M2:S2 hyphen (-) 80 * resets all style options to their default settings. For information about a speci? c option, see the entry for that option. Considerations—SET STYLE You cannot specify the same option more than once in a single SET STYLE command. NonStop SQL/MP Reference Manual—142115 S-46 Examples—SET STYLE Examples—SET STYLE • The following command activates report headings and speci? es an asterisk as the character to display for null values: >> SET STYLE HEADINGS ON, NULL_DISPLAY “*”; SETSCALE Function SETSCALE is a function that speci? s the scale of a host variable to SQL. You can use SETSCALE in SQL statements in C, Pascal, or TAL programs. You use SETSCALE to store scaled values (such as prices) in a database, retrieve database values into host variables in the program, or reference values in the database for comparison operations. The scale information is valid only in the context of the SQL statement; the program must handle scaling for host language statements. SETSCALE ( :host-var [[INDICATOR] :ind-var], scale ) :host-var is an integer variable in a host language program. [INDICATOR] :ind-var speci? es an indicator variable associated with the host variable. cale is an integer that speci? es the scale of host-var. The values allowed depend on the size of host-var, as follows: 2-byte integers 4-byte integers 8-byte integers 0 through 5 decimal digits 0 through 10 decimal digits 0 through 18 decimal digits Considerations—SETSCALE SETSCALE directs SQL to use host-var in the context of SQL statements as if host-var were declared with a scale of scale. If the value in host-var is entered into the database through an INSERT or UPDATE, the host language program must assign a value that allows for the scale to host-var. For example, if the program is representing a price of $123. 5, then the program should assign 12345 to host-var and use SETSCALE to specify a scale of two. If the value is being retrieved from the database through a SELECT operation, SQL assigns a value that allows for the scale to host-var. For example, if SQL is storing 123. 45, then the value 12345 is returned to host-var when the program speci? es SETSCALE with a scale of two in the SELECT statement. NonStop SQL/MP Reference Manual—142115 S-47 Examples—SETSCALE To use SETSCALE in an expression, you must apply SETSCALE to each operand individually rather than to the result of the expression.

For example, the following expression adds two prices with a scale of two decimal places: SETSCALE (:PRICE1, 2) + SETSCALE (:PRICE2, 2) Examples—SETSCALE • The following C program fragment uses SETSCALE with an INSERT to create a new row with the value 98. 34 in the PARTS. PRICE column after storing the value in host variable :HV1. The value is multiplied by 100 for storing as a whole number. HV1 = 9834; EXEC SQL INSERT INTO =PARTS (PRICE) VALUES ( SETSCALE (:hv1, 2) ); • The following C program fragment uses SETSCALE with UPDATE to change a value in the PARTS. PRICE column to $158. 34.

The value is multiplied by 100 and stored in host variable :HV2. HV2 = 15834; EXEC SQL UPDATE PARTS SET PARTS. PRICE = SETSCALE (:hv2, 2) WHERE PARTS. PARTDESC = “DISK CONTROLLER”; • The following C program fragment uses SETSCALE with SELECT to retrieve the value for a disk controller from the PARTS. PRICE column and stores the value in host variable :HV3. The value has a scale of two. EXEC SQL SELECT PARTS. PRICE INTO SETSCALE ( :HV3, 2 ) FROM =PARTS WHERE PARTS. PARTDESC = “DISK CONTROLLER”; • The following C program fragment uses SETSCALE with SELECT to retrieve the part description for the part with a price of $999. 0. The price value is stored in host variable :HV4 and supplied to SQL in the search condition. The retrieved value is stored in host variable :HVSTORE. HV4 = 99950; EXEC SQL SELECT PARTS. PARTDESC INTO :HVSTORE FROM =PARTS WHERE PARTS. PRICE = SETSCALE ( :hv4, 2 ); Shorthand View A shorthand view is a view derived from one or more tables or views by joining tables or views, by projecting columns, by restricting rows, or by a combination of these actions. Shorthand views can be read, but cannot be updated or secured. NonStop SQL/MP Reference Manual—142115 S-48 SHOW CONTROL Command SHOW CONTROL Command

SHOW CONTROL is an SQLCI command that displays the current values of options set by the CONTROL EXECUTOR, CONTROL QUERY, and CONTROL TABLE statements. SHOW CONTROL ; Examples—SHOW CONTROL >> SHOW CONTROL; ——————————————–Current Environment ——————————————–CONTROL TABLE ORDERS TABLELOCK OFF CONTROL TABLE ORDERS TIMEOUT 10. 00 SECONDS CONTROL TABLE ORDERS SEQUENTIAL UPDATE ON CONTROL TABLE ORDERS SYNCDEPTH 1 CONTROL TABLE ORDERS MDAM ON CONTROL TABLE ORDERS MDAM USE 3 KEY COLUMNS CONTROL TABLE ORDERS MDAM ACCESS DENSE SHOW DEFINE Command

SHOW DEFINE is an SQLCI command that displays all or part of the working attributes set. (SHOW DEFINE is similar to the TACL command SHOW DEFINE and the OSS command show_de? ne. ) SHOW DEFINE [ attr ] ; [ * ] attr displays the attribute you specify and its value. You can specify any attribute that is legal for the current class. (If you do not know the current class, specify CLASS. ) * displays all attributes in the working attribute set and their values. Optional attributes that have no current value are not displayed. NonStop SQL/MP Reference Manual—142115 S-49 Considerations—SHOW DEFINE

Considerations—SHOW DEFINE • If you do not specify an attribute or an asterisk, SHOW DEFINE displays all attributes with a current value and warns you to specify values for required attributes without current values. Attributes whose values violate consistency rules are ? agged with an asterisk (*). Examples—SHOW DEFINE • The following command displays the CLASS attribute from the working attributes set: >>SHOW DEFINE CLASS; CLASS MAP • The following command displays the current set of working attributes. In the example, no value is currently assigned to the FILE attribute.

You must supply a value for the FILE attribute to add a DEFINE because the working attribute set does not provide a default value. >>SHOW DEFINE *; CLASS FILE MAP ?? Current attribute set is incomplete SHOW DEFMODE Command SHOW DEFMODE is an SQLCI command that displays the current DEFMODE setting. (SHOW DEFMODE is similar to the TACL command SHOW DEFMODE. ) DEFMODE is an attribute of a process that controls whether you can create DEFINEs from the process and whether DEFINEs are propagated when the process starts another process. See DEFINEs on page D-26 for more information. SHOW DEFMODE ;

Examples—SHOW DEFMODE • The following command displays the current DEFMODE: >>SHOW DEFMODE ; Defmode ON NonStop SQL/MP Reference Manual—142115 S-50 SHOW LAYOUT Command SHOW LAYOUT Command SHOW LAYOUT is an SQLCI report writer command that displays the values of the current layout options. Layout options affect the way a report appears on a terminal screen or printed page. SHOW { [ LAYOUT ] option [, option] … } ; { LAYOUT * } option is: { { { { { { { { CENTER_REPORT LEFT_MARGIN LOGICAL_FOLDING PAGE_COUNT PAGE_LENGTH RIGHT_MARGIN SPACE WINDOW } } } } } } } } * displays all layout options.

For information about a speci? c option, see the entry for that option. Examples—SHOW LAYOUT • The following command displays the current margin and line-spacing values: >> SHOW LAYOUT LEFT_MARGIN, RIGHT_MARGIN, LINE_SPACING; LEFT_MARGIN RIGHT_MARGIN LINE_SPACING 5 80 2 SHOW PARAM Command SHOW PARAM is an SQLCI command that displays the current parameter values. SHOW { [ PARAM ] name [ , name ] … } ; { PARAM * } name identi? es the parameter you want to display. * displays the values of all parameters. NonStop SQL/MP Reference Manual—142115 S-51 Examples—SHOW PARAM Examples—SHOW PARAM •

The following command displays two parameter values: >> SHOW PARAM ? PNUM, ? ST; ? PNUM ? ST 4103 TEXAS • The following command displays the value of every currently de? ned parameter: >> SHOW PARAM *; SHOW PREPARED Command SHOW PREPARED is an SQLCI command that displays prepared commands. SHOW PREPARED { command-name [, command-name ] … } ; { * } command-name is the name you speci? ed for the command when you prepared it. * displays all the currently prepared commands. Examples—SHOW PREPARED • The following command displays a prepared command named SELALLCU: >> SHOW PREPARED SELALLCU; select * from sales. ustomer >> NonStop SQL/MP Reference Manual—142115 S-52 SHOW REPORT Command SHOW REPORT Command SHOW REPORT is an SQLCI report writer command that displays the current SELECT statement and the current report formatting commands. SHOW REPORT { { command } [, command ] … } ; { * } command is: { { { { { { { { { { { { BREAK BREAK FOOTING BREAK TITLE DETAIL NAME PAGE FOOTING PAGE TITLE REPORT FOOTING REPORT TITLE SELECT SUBTOTAL TOTAL } } } } } } } } } } } } * displays the SELECT and all report formatting commands. For information about a speci? command, see the entry for that command. (The command listed as BREAK refers to the BREAK ON command. ) Examples—SHOW REPORT • The following command displays the current report title and page footer: >> SHOW REPORT TITLE, PAGE FOOTING; REPORT TITLE “ORDER DETAILS” CENTER; PAGE FOOTING TAB 50, “Page “, PAGE_NUMBER AS I3; NonStop SQL/MP Reference Manual—142115 S-53 SHOW SESSION Command SHOW SESSION Command SHOW SESSION is an SQLCI command that displays the values of the current session options. SHOW [ SESSION ] { option [, option ] … } ; { * } option is: { { { { { { { { { AUTOWORK BREAK_KEY DISPLAY_ERROR ERROR_ABORT ERROR_TEXT LIST_COUNT MANDATORY_REPORT STATISTICS WARNINGS WRAP } } } } } } } } } } * displays all session options. See SET SESSION Command on page S-39 for an explanation of each session option. Examples—SHOW SESSION • The following command displays the current values of all options: >> SHOW SESSION *; —————————-Current SESSION Option Values —————————-AUTOWORK BREAK_KEY DISPLAY_ERROR ERROR_ABORT ERROR_TEXT LIST_COUNT ON OFF MAIN OFF DETAIL 10 MANDATORY_REPORT ON STATISTICS WARNINGS WRAP OFF ON OFF

NonStop SQL/MP Reference Manual—142115 S-54 SHOW STYLE Command SHOW STYLE Command SHOW STYLE is an SQLCI report writer command that displays the current style options. Style options affect the appearance of speci? c report items, such as underlines, headings, and date and time formats. SHOW { [ STYLE ] option [, option ] … } ; { STYLE * } option is: { { { { { { { { { { { DATE_FORMAT DECIMAL_POINT HEADINGS NEWLINE_CHAR NULL_DISPLAY OVERFLOW_CHAR ROWCOUNT SUBTOTAL_LABEL TIME_FORMAT UNDERLINE_CHAR VARCHAR_WIDTH } } } } } } } } } } } * displays all style options. For information about a speci? option, see the entry for that option. Examples—SHOW STYLE • The following command displays the current setting for three style options: >> SHOW STYLE NEWLINE_CHAR, OVERFLOW_CHAR, UNDERLINE_CHAR; NEWLINE_CHAR OVERFLOW_CHAR UNDERLINE_CHAR @ * _ Similarity Checks A similarity check is a comparison made by SQL to determine whether two objects (or the compile-time and execution time version of the same object) are suf? ciently similar that a serial execution plan compiled for one is also an operable plan for the other. (SQL does not perform similarity checks for objects referenced in parallel execution plans. You can reduce recompilation time for an application by directing the SQL compiler to recompile only plans that are actually inoperable, not merely invalid. If you do so, the SQL compiler uses similarity checks to determine whether certain invalid plans (those that are invalid because objects they reference have been changed or rede? ned) are actually operable plans. NonStop SQL/MP Reference Manual—142115 S-55 General Rules for Similarity Similarity checks work by comparing information stored in a program ? le at explicit compilation time with information current at recompilation time.

During an explicit recompilation, the SQL compiler uses similarity checks to differentiate between invalid and inoperable plans if you specify COMPILE INOPERABLE PLANS. During an automatic recompilation, the SQL executor uses similarity checks to differentiate between invalid and inoperable plans if you speci? ed CHECK INOPERABLE PLANS when you last explicitly compiled the program. General Rules for Similarity Only tables, protection views, and collations can be similar. A table can be similar to another table, a protection view can be similar to another protection view, and a collation can be similar to another collation.

No object other than a table, protection view, or collation is ever considered similar to another object (even an object of the same type) for the purposes of a similarity check. A table or protection view compared to a compile-time table or protection view in a similarity check must have its SIMILARITY CHECK option set to ENABLE. (When you create a table or view, the default is SIMILARITY CHECK DISABLE, but you can specify SIMILARITY CHECK ENABLE with the CREATE TABLE, CREATE VIEW, ALTER TABLE, or ALTER VIEW statements. ) The value of the SIMILARITY CHECK option for a table or protection view affects only that table or view, not views de? ed on that table or view. Similarity Between Protection Views A protection view referenced at execution time (or recompilation time) is similar to the protection view used at compilation time if the protection view referenced at execution time has the SIMILARITY CHECK option enabled and if both protection views • • • • Have a similar underlying table Project the same columns from the underlying table Have the same column names Have the same selection expression (determined by a binary comparison of the parsed, internal representation of the two selection expressions)

Similarity Between Tables A table referenced at execution time (or recompilation time) is similar to the table used at compilation time if the table referenced at execution time has the SIMILARITY CHECK option enabled and if the tables are the same, except in the following aspects: • All of the following characteristics can differ: ° Table names and table statistics ° Data within the tables NonStop SQL/MP Reference Manual—142115 S-56 Similarity Between Tables • ° ° ° ° ° °

Column headings and help text Catalog where the table is registered Comments on columns, constraints, indexes, or tables Number of partitions and partitioning key ranges Key tags (or values) for indexes Creation and rede? nition timestamps LOCKLENGTH MAXEXTENTS NOPURGEUNTIL OWNER SECURE SERIALWRITES TABLECODE VERIFIEDWRITES The following ? le attributes can differ: ALLOCATE AUDITCOMPRESS BUFFERED CLEARONPURGE EXTENT (primary and secondary) The AUDIT ? le attribute can also differ unless the program includes a statement that performs a DELETE or UPDATE set operation on a nonaudited table with a SYNCDEPTH of 1. • The number of indexes can differ, but all indexes used in the execution plan must exist for both tables. The table referenced at execution time can have more columns than the table referenced at compile time if the common columns have identical attributes and if the statement that references the table is not one of the following: ° An INSERT statement that does not explicitly specify names of columns into which to insert values. ° A statement that uses unquali? ed column names in a way that becomes ambiguous because of the additional columns. of the form COUNT (*). A statement that uses a SELECT list containing an asterisk, unless the asterisk is Tables referenced in statements such as the following can be similar: SELECT COUNT (*) FROM table; SELECT columnx FROM tablea WHERE columny relational-op (SELECT COUNT(*) FROM tableb); NonStop SQL/MP Reference Manual—142115 S-57 Similarity Between Collations Tables referenced in the position of tablea in the following statements can be similar, but tables referenced in the position of tableb in the following statements can never be similar: SELECT columnx FROM tablea WHERE EXISTS SELECT [DISTINCT] * FROM tableb; SELECT tableb. *, tablea. FROM tableb, tablea; Similarity Between Collations Collations that are explicitly referenced in an SQL query are similar only if they are equal. SQL uses the procedure CPRL_COMPAREOBJECTS_ to determine equivalence of collations. SLACK File Attribute SLACK is a ? le attribute that speci? es the minimum percentage of space to leave for future insertions when loading data and index blocks. SLACK applies only to keysequenced ? les and to indexes. SLACK percent percent is an integer from 0 to 99 that speci? es the percent of empty space to leave in each data and index block during loading. The default is 15 percent. Purpose of SLACK • • SLACK speci? cations are usually between 15 and 25 percent. Specifying a larger than normal SLACK value when a ? le is initially loaded and many more inserts are expected can improve performance by reducing the number of block splits required when inserts occur. For a ? le expected to have little activity, you can save disk space by specifying a smaller than normal SLACK value. SPACE Option SPACE is an option of the SQLCI report writer SET LAYOUT command that speci? es the default number of spaces between columns of report print items. Each space occupies a single byte position, regardless of the character set in use.

SPACE number NonStop SQL/MP Reference Manual—142115 S-58 Considerations—SPACE number is an integer in the range 0 through 255 that speci? es the default number of spaces between print items. The default is 2. Considerations—SPACE • • If you specify the SPACE clause on the DETAIL command, the value you specify overrides the value of the SPACE layout option for that detail line. SQL does not print spaces before or after a string literal in a report unless you specify a heading for the report column that includes the string literal. If you specify a heading, SQL prints the default number of spaces before and after the string literal.

Examples—SPACE • The following example demonstrates that SQL does not print spaces before or after a string literal unless the report column that includes the string literal has a heading: >> SET LIST_COUNT 0; >> SELECT * FROM PERSNL. JOB; S> DETAIL JOBCODE, “***”, JOBDESC; S> LIST NEXT 4; JOBCODE ——JOBDESC —————— 100***MANAGER 200***PRODUCTION SUPV 250***ASSEMBLER 300***SALESREP S> DETAIL JOBCODE, “***” HEADING ” S> LIST FIRST 4; JOBCODE ——100 200 250 300 –*** *** *** *** JOBDESC —————–MANAGER PRODUCTION SUPV ASSEMBLER SALESREP “, JOBDESC;

NonStop SQL/MP Reference Manual—142115 S-59 SQL Directive • The following example uses the SPACE layout option to change the default number of spaces between columns. The content of the report (not the spacing) is the same as in the previous example. >> SET LAYOUT SPACE 5; >> SET LIST_COUNT 0; >> SELECT * FROM PERSNL. JOB; S> LIST NEXT 4; JOBCODE ——100 200 250 300 JOBDESC —————–MANAGER PRODUCTION SUPV ASSEMBLER SALESREP SQL Directive The SQL directive indicates to a host language compiler that a program contains SQL statements.

For more information about the SQL directive, see the NonStop SQL/MP programming manual for your host language. SQL Identi? ers An SQL identi? er can contain up to 30 letters (A through Z or a through z), digits (0 through 9), or underscore (_) characters. The ? rst character must be a letter. SQL ignores case in SQL identi? ers; for example, employee and EMPLOYEE are equivalent. SQL identi? ers are used as column, constraint, correlation, cursor, parameter, and statement names and cannot be the same as any keyword reserved for SQL statements.

See Reserved Words on page R-13 for a list of reserved SQL keywords. All of the following are valid SQL identi? ers: • • • • • • ColumName MONTH3ORDERS second_low_value EMPLOYEE ORD_DATE THE_END_ NonStop SQL/MP Reference Manual—142115 S-60 SQLCI SQLCI SQLCI, the NonStop SQL/MP conversational interface, executes commands and SQL statements entered interactively at a terminal or through a command ? le. SQLCI is useful for ad hoc queries and reports, for testing SQL statements before adding them to programs, for comparing the relative ef? iency of different versions of a query, and for database administration tasks such as de? ning and modifying the structure of the database. SQLCI provides many commands that are not available in embedded SQL programs but also accepts almost any statement that you can include in an embedded SQL program. A few SQL statements have options that are not allowed in SQLCI (or options that are allowed only in SQLCI); these are noted in the description of the statement. See SQLCI Commands on page S-64 and Report Writer on page R-9 for

Cite this Sample Database to Help Users of Nonstop Sql/Mp

Sample Database to Help Users of Nonstop Sql/Mp. (2016, Oct 30). Retrieved from https://graduateway.com/sample-database-to-help-users-of-nonstop-sqlmp/

Show less
  • Use multiple resourses when assembling your essay
  • Get help form professional writers when not sure you can do it yourself
  • Use Plagiarism Checker to double check your essay
  • Do not copy and paste free to download essays
Get plagiarism free essay

Search for essay samples now

Haven't found the Essay You Want?

Get my paper now

For Only $13.90/page