Using UniData SQL - Rocket Documentation - Rocket Software [PDF]

C:\Program Files\Adobe\FrameMaker8\Uni; Last Name First Name Majo Mino Advisor Term Crs # GD --------- ---------- ---- ---- ------- ---- ----- -Jenral Hions CS PY Otis SP86 PY100 A CS101 B FA86 PY200 B CS201 A CS300 C

1 records listed

Note: When unnesting, Uni; COMMIT complete. 1 record(s) deleted or updated.

5-17

Deleting a Set of Records Use a search condition in the WHERE clause to specify a set of records to delete. The result set might be one, two, or 1,000 records, depending on the search condition. For a description of search conditions, see Chapter 6, “The SELECT Statement.” Since you cannot recover or sql> NUM_RENTALS < 3 INTO TEMP_CUST;

2.

Next, delete the customer ; Page 1 Last Name Term Crs # GD --------------- ---- ----- --Jenral SP86 PY100 A CS101 B FA86 PY200 B CS201 A CS300 C 1 records listed

2.

Next, issue the DELETE statement to remove CS201 in FA86 from Jenral’s entry in the STUDENT table. Notice that you only delete one line (the line containing CS201) from the original record:

sql> DELETE FROM STUDENT WHERE LNAME = "Jenral" and sql> COURSE_NBR = "CS201"; COMMIT complete. 1 record(s) deleted or updated.

3.

Now, display the record for Jenral again. Notice that CS201 no longer appears:

sql> SELECT LNAME,SEMESTER,COURSE_NBR,COURSE_GRD FROM STUDENT sql> WHERE LNAME="Jenral"; Page 1 Last Name Term Crs # GD --------------- ---- ----- --Jenral SP86 PY100 A CS101 B FA86 PY200 B CS300 C 1 records listed

4.

Next, delete all Jenral’s information for the SP86 semester. Remember, if you specify a condition satisfied by a value in a multivalued attribute, then the multivalue and all its associated subvalues are deleted.

sql> DELETE FROM STUDENT WHERE LNAME = "Jenral" sql> AND SEMESTER = "SP86"; COMMIT complete. 1 record(s) deleted or updated.

5-20 Using Uni; Page 1 Last Name Term Crs # GD --------------- ---- ----- --Jenral FA86 PY200 B CS300 C 1 records listed

Note: If conditional checking is performed on multi-subvalued attributes, and the association contains both multivalued and multi-subvalued attributes, only values in the multi-subvalued attribute are deleted. However, if all the values in a multisubvalued attribute satisfy the condition, the corresponding values of the associated multivalued attributes are also deleted.

5-21

Restoring the ; COMMIT complete. 2 record(s) updated.

The next example restores the COURSES OR CITY = "Boulder" OR CITY ="Chicago"; Page 1 Customer Name City --------------- ---------Frobisher, Denver Kamal Steven Spender Denver Wagner, Pat Denver Doodle, Polly Denver W. Belafonte, Boulder Harry Smith, Harry Boulder Fellini, J. Chicago Hoboken Jones, Freddie Chicago LaRue, Karma Denver Smith, Leif Denver Boorman, Rick Chicago 11 records listed

Combining the AND and OR Operators Uni; Page 1 Last Nam Dept -------- ---Otis FA 1 records listed

6-64 Using Uni; Page 1 Last Nam Title Salary Dept -------- ------- ------- ---Eades Prof 42,000 FA Fried Dean 98,000 BUS 2 records listed

6-66 Using Uni; C_DENVER created.

Note: Only the query_spec portion of the SELECT statement can be included in the CREATE VIEW statement.

7-5 Using Uni; Page 1 STAFF Last Nam Title Salary ---------- -------- ------- ------8 Fried Dean 98,000 1 records listed

Optimizing View Queries and the UNNEST Clause Even though a view definition meets the criteria for optimizing view processing, in some cases, Uni

Values Retrieved attr1=1 attr1=2

INSERT INTO T(id,attr1,attr2) values(1,3,"a") COMMIT SELECT attr1 FROM T WHERE attr2="a"

attr1=1 attr1=2 attr1=3 Phantom Example

UniData SQL Isolation Levels For RFS files, UniData SQL supports all four SQL isolation levels. For non-RFS files, UniData SQL supports only Repeatable Read (RR) and Serializable (S). Isolation level is specified in the UniData SQL SET TRANSACTION command. Regardless of the isolation level, exclusive locks are placed on all records being modified. These locks are maintained until the transaction is comitted or aborted.

10-10 Using UniData SQL

Summary of Isolation Levels The table below summarizes the application of isolation levels in UniData SQL.

Isolation Level

Notes

Access Modes

Shared Locks Set by SELECT

Read Uncommitted

Allows dirty reads; may allow the database to remain inconsistent.

read-only

no

Read Committed

Supported for RFS files only; repeated queries may return different results.

read-only read/write (default)

no

Repeatable Read

Supported for RFS and nonRFS files; repeated queries return the same results; allows phantoms.

read-only read-write (default)

yes

Serializable

Supported for RFS and nonRFS files; repeated queries return the same results; phantoms are not permitted.

read-only read/write (default)

yes

Summary of Isolation Levels

Read Uncommitted (RU) Transactions executed with an isolation level of RU may read dirty data, and may allow the database to remain inconsistent when the transaction that “dirties” the data rolls back; therefore, transactions that run at RU level are read-only. UniData SQL places no locks on records read at isolation level RU.

Read Committed (RC) With the isolation level RC, you can specify either read-only or read/write access mode. If none is specified, UniData SQL assumes read/write. For RFS files, since the local buffer is used, and no dirty data will be perceived by other transaction, UniData SQL does not place any locks on records that are being read. This implies that if a record is reread within the same transaction, its values may not be the same, or the repeatability of the read record is violated. 10-11

Note: If a transaction has the read-write access mode, which is the default, then a user can enter SELECT, UPDATE, INSERT, and DELETE statements. Because SQL SELECT is a read-only operation, it does not place any locks on records that are being read; but exclusive record locks are held until the end of the transaction. This prevents other users from updating the same record, and transactions with higher isolation levels from retrieving the record that is being updated.

Repeatable Read (RR) When this isolation level is specified in the SET TRANSACTION statement, UniData SQL ensures, that all read operations within this transaction are repeatable. You can specify a read-only or read-write access mode. If none is specified, the system assumes read-write. UniData SQL places shared locks on all records that are being read and keeps them until the end of the transaction. It ensures, that if a record is reread within the same transaction, then its values are guaranteed to be the same, since no other transaction can update or delete a record that is locked in shared mode. If a transaction has read/write access mode, a user can enter SELECT, UPDATE, INSERT and DELETE statements. Since SQL SELECT is a read-only operation, it places shared locks on records that are being read until the end of the transaction, as was explained above. Specifying this isolation level may lead to a reduced level of concurrency. Because shared locks are held for the duration of the transaction, no other transaction can update or delete any record retrieved by this transaction until this transaction terminates. Use this isolation level only if your application requires the repeatability of retrieved values. Also, keep such transactions short. As with other isolation levels, exclusive record locks are placed on all records that are being inserted, updated or deleted and these locks are held until the end of the transaction.

Serializable (SR) When SR isolation level is specified, UniData SQL ensures that all read operations within this transaction are repeatable and that no phantoms are possible. You can specify read-only or read/write access mode. If none is specified, read/write is assumed. UniData SQL uses a mixture of file and record-level locks depending on the way the data is being accessed: 10-12 Using UniData SQL

„

If UniData SQL can access a record directly, as in a case where the target record @ID is supplied, UniData SQL places shared locks on records being read, and exclusive locks on records being updated.

„

If UniData SQL needs to read an entire table to find records that satisfy the search condition, it places a file-level shared lock on the table being read, and record-level exclusive locks on records being updated. This ensures that no phantoms are possible, since file-level locks prevent other users from inserting data into locked files.

The following fragment presents an example where record-level locks are used by UniData SQL. Note the use of the ID in the WHERE clause. This transaction locks only two records, since UniData SQL performs query optimization to directly access the two records. The first statement places shared locks and the second statement places exclusive locks. For further information on optimizing queries, see Chapter 7, “Creating and Querying Views.” sql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; sql> SELECT * FROM ORDERS WHERE ID='912' OR ID='913'; Transaction started. Page 1 Order Order Order Client Number Date Time Number Product Nu Color Qty Price ---------- ---------- ------- ---------- ---------- ---------- ----- ---------912 01/13/1996 12:30PM 9984 53000 N/A 6 $129.95 913 01/13/1996 03:36PM 10010 13005 Gray 5 $500.00 2 records listed sql> DELETE FROM ORDERS '913'; 1 record(s) deleted or updated. sql> COMMIT; Transaction committed.

10-13

When you use standard SQL statements without specifying specific records (using the ID LIST clause or, as in the example above, ID=‘912’), UniData SQL locks the whole table, as in the following example: sql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; sql> SELECT * FROM ORDERS WHERE CLIENT_NO='9984'; Transaction started. Page 1 Order Order Order Client Number Date Time Number Product Nu Color Price Qty ---------- ---------- ------- ---------- ---------- ---------- ----- ---------912 01/13/1996 12:30PM 9984 53000 N/A 6 $129.95 1 records listed

Note that, even if this query is processed using an index (that is, an index exists on CLIENT_NO), UniData SQL still locks the entire data file, because if allowed, other transactions might add new records that would result in new index values being added. Tip: Serializable transactions may have negative effects on the concurrency level of the database. Use this isolation level only when you must prevent any possibility of phantoms being created.

10-14 Using UniData SQL

UniData SQL TP Commands TP Commands UniData SQL TP consists of the following commands: „

SET DISPLAY

„

SET DISPLAY TPMESSAGE

„

COMMIT

„

ROLLBACK

„

AUTO COMMIT

„

LOCK TABLE

Transaction-Initiating Commands The following commands initiate transactions: „

SELECT

„

INSERT

„

UPDATE

„

DELETE

„

LOCK TABLE

Unsupported Commands An error occurs and the transaction terminates when one of the following commands is encountered in a transaction: „

CREATE [TABLE | VIEW | SUBTABLE | INDEX]

„

DROP [TABLE | VIEW | SUBTABLE | INDEX]

„

ALTER TABLE

„

GRANT

„

REVOKE 10-15

„

! (shell escape)

„

SET TRANSACTION

„

AUTO COMMIT {ON | OFF}

10-16 Using UniData SQL

SET DISPLAY TPMESSAGE Syntax SET DISPLAY TPMESSAGE [ON | OFF]

Description The SET command option DISPLAY TPMESSAGE turns on or off display of transaction processing notification messages for all transactions. Note: UniData SQL always displays “Transaction aborted” when a transaction is rolled back, regardless of the TPMESSAGE setting. Unless SET DISPLAY TPMESSAGE is ON, UniData SQL transaction processing displays no message when a transaction starts or commits. After the execution of the above command, UniData SQL displays: “Transaction started.” when a UniData SQL transaction is started, and displays: “Transaction committed.” when a transaction is committed. The SET DISPLAY statement spans transaction boundaries. After it is turned on, it remains on until turned off. To display current setting of transaction message option, enter: SET DISPLAY TPMESSAGE

Examples The following example demonstrates turning on display of transaction start and transaction commit messages: sql> SET DISPLAY TPMESSAGE ON;

10-17

After the preceding SET DISPLAY TMESSAGE ON statement is executed, any statement that creates, updates, or deletes a table generates Transaction started and Transaction committed messages, as in the following example: sql> INSERT INTO INVENTORY (ID, FEATURES, COLOR, QTY, PRICE, REORDER) sql> VALUES (99999, "133 MHz CPU, 25-in. SVGA Monitor, Keyboard, Mouse, sql> Microsoft Office", "Black", 50, 1000.00, 10); Transaction started. 1 record(s) created. Transaction committed. sql> SELECT * FROM INVENTORY WHERE ID = "99999"; Transaction started. Page 1 INVENTORY 99999 Inventory Date Inventory Time Product Name Features 133 MHz CPU, 25-in. SVGA Monitor, Keyboard, Mouse,Microsoft Office Color Quanti Price Reorde ---------- ------ ---------- -----Black 50 $1,000.00 10 1 records listed Transaction committed.

10-18 Using UniData SQL

SET TRANSACTION Syntax SET TRANSACTION [READ ONLY | READ WRITE] ISOLATION LEVEL{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

Description The SET command option TRANSACTION specifies the access mode and isolation level of the next transaction to be initiated. It does not start a new transaction, and it cannot be executed within an active transaction. If you do not specify both access mode and isolation level, default UniData SQL settings are applied: READ WRITE ISOLATION LEVEL READ COMMITTED. This statement applies to the next transaction only. After that default settings are returned. You can change the level of isolation for your environment by changing the environment variable DEFAULT_ISO_LEVEL. For further information, see Administering UniData. Note: SET TRANSACTION is included in the SET section of the UniData SQL Commands Reference.

10-19

Parameters The following table describes each parameter of the syntax. Parameter

Description

READ ONLY

Specifies that the next transaction is read-only.

READ WRITE

Specifies that the next transaction is read-write.

ISOLATION LEVEL

Select one of the following four isolation levels: READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE For details on isolation levels, see “UniData SQL Isolation Levels” on page 10-10. SET TRANSACTION Parameters

Example The transaction in the following example is read-only and has the isolation level of read uncommitted: sql>SET TRANSACTION READ ONLY ISOLATION LEVEL READ UNCOMMITTED; SELECT * FROM STUDENT; EMP_NAME='Dow, John' WHERE EMPID='1001'; COMMIT;

10-20 Using UniData SQL

COMMIT Syntax COMMIT [WORK]

Description The UniData SQL COMMIT command concludes an active transaction. The optional keyword WORK is provided for backward compatibility. If a COMMIT command executes without an active transaction, UniData SQL displays the following error message: Invalid transaction termination: no active transaction.

The system performs the following steps during a transaction commit: „

Disables the interrupt key.

„

Writes all updates.

„

Releases all locks acquired within the transaction.

„

Enables the break key.

If the transaction cannot commit, the system performs the following steps: „

Aborts the transaction.

„

Releases all locks acquired within the transaction.

10-21

ROLLBACK Syntax ROLLBACK [WORK]

Description The UniData SQL ROLLBACK command cancels an active transaction. UniData SQL discards pending writes and releases all locks acquired during the transaction. The optional keyword WORK is provided for backward compatibility with legacy systems. All commands issued from the beginning of the transaction are undone; therefore, no updates associated with the transaction take place. A transaction can be rolled back due to any of several conditions in addition to execution of a ROLLBACK command, including: „

File-access errors (such as no privilege, violation of operation system level security, and so forth).

„

Locking errors, such as deadlocks.

„

Invalid command or invalid command syntax.

„

If SB_FLAG is OFF, executing the ROLLBACK command produces a system message indicating that the transaction has been aborted when it has not.

10-22 Using UniData SQL

AUTO COMMIT Syntax AUTO COMMIT [ ON | OFF ]

Description AUTO COMMIT ON causes each statement to immediately commit.

Parameters The following table describes each parameter of the syntax. Parameter

Description

ON

Each UniData SQL statement is immediately committed, releasing all locks. The default is interactive mode

OFF

Several statements can be combined into one transaction. Set AUTO COMMIT OFF to be able to commit or roll back a set of statements as a single unit.

no option

Displays the current setting of AUTO COMMIT. AUTO COMMIT Parameters

Note: Unlike SET TRANSACTION, which sets options that apply to the next transaction only, AUTO COMMIT affects all subsequent transactions until UniData SQL encounters another AUTO COMMIT statement.

10-23

Example The following example demonstrates the use of AUTO COMMIT to display or change its status: sql> AUTO COMMIT; Transaction auto commit option is ON. sql> AUTO COMMIT OFF; sql> AUTO COMMIT; Transaction auto commit option is OFF.

10-24 Using UniData SQL

LOCK TABLE Syntax LOCK TABLE table_name [,table_name...] IN {SHARE | EXCLUSIVE} MODE [NOWAIT]

Description LOCK TABLE overrides UniData’s automatic locking system, thus providing a means of controlling the level of isolation for tables. LOCK TABLE is the only UniData SQL command that explicitly locks tables. See Administering UniData for an explanation of the UniData locking scheme. Examples of situations in which you should use LOCK TABLE: „

A transaction accesses many files, but you need to isolate only one key file from other transactions. LOCK TABLE enables you to specify a lower-level isolation (such as RC) for a transaction, while locking a file at a higher level.

„

An application may eventually lock almost all records in a file. UniData SQL locks records as they are needed, then converts to a file lock when a majority of the records are locked. This can result in a deadlock. To avoid this, use LOCK TABLE to lock the file at the beginning of the application.

„

A batch process that updates records.

When LOCK TABLE is issued and no transaction is active, UniData SQL starts a new transaction. Upon termination of the transaction, all locks are released. When an application requests a file lock against records that are already locked by another user, the application waits until the locks are released. This can appear to be a system or terminal hang to the user. Include the NOWAIT keyword to avoid this. UniData SQL allows a maximum of 128 files to be locked within a transaction — including system-imposed locks as well as locks set by the user.

10-25

Parameters The following table describes each parameter of the syntax. Parameter

Description

table_name

Specifies the table to be locked.

IN SHARE MODE

Indicates that a shared lock is to be placed on the table.

IN EXCLUSIVE MODE

Indicates that an exclusive lock is to be placed on the table.

NOWAIT

Aborts the transaction rather than wait for a lock to be released. If you do specify NOWAIT and include several files in single LOCK TABLE command, and any one of the files is already locked, the command fails. LOCK TABLE Parameters

10-26 Using UniData SQL

Examples of UniData SQL TP The following examples illustrate how different locking schemes affect isolation levels with the ORDERS database file. ID

F1

F2

2

4

a

8

3

a

1

3

b

3

2

c

5

3

d

6

1

b

7

4

a

Attributes in ORDERS Database

Example 1 – Read Committed vs. Repeatable Read This example demonstrates a nonrepeatable read: different results are obtained from the same SELECT statement in steps 1 and 4. Step

Transaction 1 (RC level)

1

SELECT F2 FROM ORDERS WHERE ID = 2;(result ‘a’)

Transaction 2 (any level)

2

UPDATE ORDERS SET F2=’b’ WHERE ID=2;

3

COMMIT;

4

SELECT F2 FROM ORDERS WHERE ID = 2;(result ‘b’) Read Committed Isolation Level

10-27

In the next example, transaction 1 is executing with the isolation level RR (Repeatable Read), thus preventing nonrepeatable reads by either transaction. Step

Transaction 1 (RR Level)

1

SELECT F2 FROM ORDERS WHERE ID = 2; (result ‘a’; record 2 locked)

2

3

Transaction 2 (any level)

UPDATE ORDERS SET F2=’b’ WHERE ID=2; (exclusive lock on 2 not granted; wait) SELECT F2 FROM ORDERS WHERE ID= 2; (result ‘a’)

4

COMMIT; (release S lock)

5

(x lock granted) (change ‘a’ to ‘b’) COMMIT; Repeatable Read Isolation Level

Example 2 – Repeatable Read vs. Serializable The following example demonstrates the phantom of two transactions when the first is executed in the Repeatable Read isolation level. Step

Transaction 1 (RR level)

1

SELECT F1 FROM ORDERS WHERE F1=4;(result records 2,7 locked)

Transaction 2 (any level)

Repeatable Read Isolation Level

10-28 Using UniData SQL

Step

Transaction 1 (RR level)

Transaction 2 (any level)

2

INSERT INTO ORDERS VALUES (4,4,’b’)

3

COMMIT;

4

SELECT F1 FROM ORDERS WHERE F1= ‘4’ (result records 2,4,7) Repeatable Read Isolation Level (continued)

Default Transaction Settings Example 1 – Interactive UniData SQL In the following example, UniData SQL performs an implicit commit after the first SELECT statement and after the subsequent UPDATE statement. This operation is the same regardless of whether you invoke UniData SQL from ECL or from the operating system because the default AUTO COMMIT setting for interactive UniData SQL is ON. sql> SET DISPLAY TPMESSAGE ON; sql> SELECT * FROM ORDERS WHERE ID='801'; Transaction started. Page 1 Order Order Order Client Number Date Time Number Product Nu Color Qty Price ---------- ---------- ------- ---------- ---------- ---------- ----- ---------801 09/28/1995 04:34PM 10018 11000 Gray 1 $1,799.00 1 records listed Transaction committed sql> UPDATE ORDERS SET QTY='5' WHERE ID='801'; Transaction started. 1 record(s) updated. Transaction committed.

Example 2 – Manual Control of Isolation Level The following example illustrates several points: 10-29

„

You can override the default AUTO COMMIT option default (ON). In this example, AUTO COMMIT is OFF for all subsequent UniData SQL transactions until the end of the UniData SQL session or until another AUTO COMMIT command executes.

„

With AUTO COMMIT OFF, you are in full control of each transaction’s isolation level and its termination. For example, with AUTO COMMIT ON, you have no reason to set isolation level, because each statement commits immediately. On the other hand, with AUTO COMMIT OFF, you can set isolation level, as is done in this example. Here, the user sets the isolation level to repeatable read, ensuring that no other user modifies the record being read.

After you turn AUTO COMMIT OFF, you must execute the COMMIT command to terminate the transaction. :SQL sql> SET DISPLAY TPMESSAGE ON; sql> AUTO COMMIT OFF; sql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; sql> SELECT * FROM ORDERS WHERE ID='970'; Transaction started. Page 1 Order Order Order Client Number Date Time Number Product Nu Color Qty Price ---------- ---------- ------- ---------- ---------- ---------- ----- ---------970 01/15/1996 10:00AM 9988 13003 Gray 5 $250.00 1 records listed sql> UPDATE ORDERS SET QTY='10'WHERE ID='970'; 1 record(s) updated. sql> COMMIT; Transaction committed. sql> QUIT :

10-30 Using UniData SQL

Example 3 – Quitting UniData SQL with AUTO COMMIT OFF This example illustrates quitting from UniData SQL without explicitly terminating a transaction while AUTO COMMIT option is OFF. UniData SQL performs an implicit commit before exiting. The fact that an implicit commit is performed on quit when the AUTO COMMIT option is ON is a trivial consequence of the rule that every statement is immediately committed. %sql> AUTO COMMIT OFF; sql> SELECT * FROM ORDERS; Page 1 Order Order Order Client Number Date Time Number Product Nu Color Qty Price ---------- ---------- ------- ---------- ---------- ---------- ----- ---------912 01/13/1996 12:30PM 9984 53000 N/A 6 $129.95 801 09/28/1995 04:34PM 10018 11000 Gray 1 $1,799.00 941 01/14/1996 03:00PM 10009 50000 Gray 10 $1,399.99 . . . sql> DELETE FROM ORDERS WHERE ID='970'; 1 record(s) deleted or updated. Transaction committed. sql> QUIT :

Example 4 – Immediately Commiting a Transaction When a UniData SQL statement is executed from the ECL prompt or from a paragraph, it is always immediately committed. In this example, two UniData SQL statements are executed and immediately committed: :SQL SELECT * FROM ORDERS; Page 1 Order Order Order Client Number Date Time Number Product Nu Color Qty Price ---------- ---------- ------- ---------- ---------- ---------- ----- ---------912 01/13/1996 12:30PM 9984 53000 N/A 6 $129.95 801 09/28/1995 04:34PM 10018 11000 Gray 1

10-31

$1,799.00 . . . :SQL UPDATE ORDERS SET QTY='1' WHERE ID='970'; 1 record(s) updated.

10-32 Using UniData SQL

Other UniData SQL TP Considerations This section outlines various parts of the UniData system impacted by UniData SQL TP. These areas are: „

System Administration

„

Performance

„

Interaction of UniData SQL with Other Transactional Environments

System Administration UniData SQL TP and the System Buffer UniData SQL TP is the default behavior The only way to disable UniData SQL TP is by turning off the system buffer. You can do this within the udtconfig file located in /usr/ud72/include by changing the SB_FLAG parameter. The default parameter is: SB_FLAG=1 You can turn the system buffer off by making the following change: SB_FLAG=0 Note: By turning off the system buffer, you also disable UniBasic TP and RFS, as well as UniData SQL TP. Check to make sure that this functionality is not used by your application before turning it off.

Setting the Default Isolation Level An environment variable specifies the default isolation level for this installation (the default is READ COMMITTED if this variable is not specified). DEFAULT_ISO_LEVEL

10-33

Setting Maximum Number of Locks Another environment variable specifies the maximum number of record locks allowed per file at a given time. This environment variable is: MAX_RECORD_LOCKS You can set this environment variable in the .login or .profile scripts for each user, as appropriate for your environment. When the number of record locks on a specific file exceeds the defined value, UniData SQL attempts a lock escalation by converting record locks to a single file lock.

Performance Since UniData SQL TP uses existing UniData TP facilities, the performance of UniData SQL TP largely depends on the underlying facilities. However, record locking, which is not enforced by UniBasic, may have an adverse affect on performance. To reduce the number of lock requests, IBM recommends using Read Committed as the default isolation level, as opposed to ANSI’92 SQL–stipulated Serializable. UniData SQL also supports a LOCK TABLE command, giving applications a certain degree of control to reduce system-enforced record locks. UniData also has a lock escalation scheme — when a large number of record locks are set, they are replaced by a single file lock.

Avoiding System Deadlocks In a deadlock, two users hold locks and request the lock the other holds; both would wait forever unless one is aborted. When UniData detects a deadlock, the transaction whose request causes the deadlock is terminated and automatically rolled back.

10-34 Using UniData SQL

The example below demonstrates a deadlock resulting when transaction1 waits for Transaction2 to release the lock on record 2, while Transaction 2 waits for transaction1 to release the lock on record1. Step

Transaction 1

1

lock record1 update record1

2 3 4

Transaction 2

lock record2 update record2 lock record2 lock record1 Deadlock Example

Interaction of UniData SQL with Other Transactional Environments Although nested transactions are not supported in UniData and UniData SQL transaction processing, a single transaction can span more than one execution environment. ANSI’92 SQL standards dictate that a transaction started in a particular environment must terminate in the same environment. The following explains how this rule is applied to UniData transaction environments.

UniBasic Subroutines Executed from UniData SQL The following rules apply to transactions executed from UniData SQL: „

If a UniBasic subroutine is executed from within UniData SQL while a transaction is active (such as a SUBR in a virtual attribute definition), all rules that apply to active transactions apply to the execution of the UniBasic subroutine. For example, the UniBasic TRANSACTION START command is not allowed, and no transaction terminating commands, such as TRANSACTION COMMIT or TRANSACTION ABORT, are allowed within the subroutine.

„

Locking within UniBasic subroutines operate the same way regardless of the environment the subroutine is called from: records are not automatically locked on a read, regardless of the isolation level specified in UniData SQL.

10-35

„

The outcome of the execution of a subroutine is determined by the outcome of the execution of the encompassing UniData SQL transaction. That is, if the encompassing UniData SQL transaction terminates with COMMIT, the updates made by the subroutine are committed; if the encompassing UniData SQL transaction terminates with rollback, any updates made in the subroutine are aborted as well.

„

If a UniBasic subroutine is aborted due to a fatal system error or a deadlock, the encompassing UniData SQL transaction is rolled back as well.

„

UniData SQL transactions are always rolled back if any statement fails.

„

If a UniBasic subroutine is executed with no UniData SQL transaction active, the subroutine controls the transactional environment. An example of this situation is when a CREATE.INDEX command is executed to build a virtual field index that invokes a UniBasic subroutine that contains TRANSACTION START and/or TRANSACTION COMMIT/ABORT. This execution may invoke hundreds, even thousands, of transactions, each subroutine execution being one of them.

„

Because a UniData SQL environment cannot be reentered recursively, a UniBasic subroutine executed from UniData SQL cannot contain EXECUTESQL or EXECUTE statements that execute UniData SQL statements.

UniData SQL Executed in UniBasic EXECUTE “SQL” and EXECUTESQL If UniData encounters an EXECUTE “SQL ...;” or EXECUTESQL statement while no UniBasic transaction is active, the following two possibilities exist: „

If this UniData SQL statement is not a transaction-initiating command, no transactional semantics apply.

„

If this UniData SQL statement is one of the transaction-initiating commands, a transaction is initiated and committed immediately.

Within a UniBasic program in which a transaction is active, EXECUTE “SQL ...;” or EXECUTESQL statements: „

10-36 Using UniData SQL

Do not initiate new transactions, even if they are transaction-initiating statements.

„

Transaction-terminating statements (COMMIT or ABORT) and UniData SQL statements not allowed within a transaction result in an error.

„

The active UniBasic transaction does not terminate when the processing of the EXECUTE or EXECUTESQL statement completes unless a fatal system error is encountered. Therefore, many UniData SQL statements may be executed within the same UniBasic transaction. The outcome of the encompassing UniBasic transaction determines the outcome of all UniData SQL statements within this transaction.

„

The UniBasic STATUS function returns a value greater than zero to indicate failure of a UniData SQL statement.

A UniBasic EXECUTE statement can invoke the execution of a paragraph that can contain one or more UniData SQL statements. If a paragraph containing UniData SQL statements is executed while no UniBasic transaction is active, the following rules apply: „

Transactional semantics do not apply to UniData SQL statements that do not initiate transactions.

„

Each transaction-initiating UniData SQL statement within this paragraph is immediately committed.

If a paragraph containing UniData SQL statements is executed while a UniBasic transaction is active, the following rules apply: „

No new transactions are initiated by the UniData SQL statements.

„

No transaction-terminating statements are allowed in the paragraph.

„

UniData SQL statements disallowed in transactions are illegal in the paragraph.

„

The encompassing UniBasic transaction remains active when UniData SQL statements completes unless a fatal system error is encountered. Therefore, multiple UniData SQL statements may be executed within the paragraph.

„

The outcome of the surrounding UniBasic transaction determines the outcome of all of the UniData SQL statements within this paragraph.

10-37

UniData SQL TP and READNEXTTUPLE The UniBasic READNEXTTUPLE statement does not interface with the UniData SQL environment. In order to step through a result set using a READNEXTTUPLE statement, the result set must have already been placed in a temporary file using the TO option of the SELECT statement which had been executed using the EXECUTESQL statement.

UniData SQL and ODBC Transactional Semantics ODBC transactional semantics are similar to UniData SQL transactional semantics with the following two exceptions: „

The ODBC transaction access modes stay in effect until changed by a subsequent ODBC SetConnectOption command or until the client session ends.

„

ODBC isolation level also remains in effect until changed by another ODBC SetConnectOption command or until the client session ends.

Five isolation levels exist in ODBC: four of them correspond to the four ANSI’92 SQL isolation levels, and the fifth, SQL_TXN_VERSIONING, is designed for nonlocking protocols such as Oracle’s Read Consistency. This fifth ODBC isolation level is not supported in UniData SQL.

10-38 Using UniData SQL

Tips on Transactions Optimizing Performance The following tips make for better transaction processing and improve system performance: „

Do not use a higher level of isolation than necessary. Higher-level isolations (Repeatable Read and Serializable) tend to lock large numbers of records or entire files.

„

To reduce record lock contention, avoid creating “hot spots,” where a great deal of file access is concentrated. For example, a file of summary records that must be updated by every transaction may contribute to the creation of a hot spot if the file modulo is small; resize the file so that records are evenly distributed to ease contention on the summary records. A file that constantly gets extended because of new record additions may also create a hot spot.

„

Do not run long transactions during heavy system loads. The longer the transaction, the longer locks are held; this blocks other transactions and results in increased incidence of deadlocks.

„

Avoid transactions that involve user interaction, such as keyboard input.

„

Although you can’t completely avoid deadlocks, if you follow certain principles in all applications, you can reduce the occurrence of deadlocks:

„

Always request locks in the same order.

„

If multiple files are to be modified by different applications, always update them in a fixed, predetermined order.

Limitations TP Semantics and RFS UniData SQL TP semantics are enforced only on RFS files, except for repeatable read and serializable isolation, which is enforced for non-RFS files. Warning: If users are mixing RFS and non-RFS files in a single transaction, full ACID properties are not guaranteed. 10-39

Statements Excluded from Transaction Processing Currently, UniData SQL transaction processing does not support SQL DDL statements. Specifically, the following statements cannot appear in a transaction: „

CREATE [TABLE | VIEW | SUBTABLE | INDEX]

„

DROP [TABLE | VIEW | SUBTABLE | INDEX]

„

ALTER TABLE

Additionally, the following statements are prohibited within transactions: „

GRANT

„

REVOKE

„

SET TRANSACTION

„

AUTO COMMIT

„

! (shell escape symbol)

When such statements are encountered within a transaction, one of the following two actions takes place: „

If the transaction is initiated from UniData SQL, the transaction is aborted.

„

If the transaction is initiated from another environment (such as UniBasic), an error condition is returned and control is returned to the encompassing environment.

UniData SQL TP does not support nested transactions, either directly or indirectly (such as through a subroutine call).

10-40 Using UniData SQL

Chapter

UniData SQL and 1NF Mapping Terminology . . . . . . . . . . . . . . . . . . 1NF Mapping . . . . . . . . . . . . . . . . . . NF2 Data Model vs. 1NF Data Model . . . . . . . . . What Is a 1NF Mapping View? . . . . . . . . . . . Subtables . . . . . . . . . . . . . . . . . . . Rules for Updating Subtables . . . . . . . . . . . Primary and Foreign Keys . . . . . . . . . . . . Creating Subtables . . . . . . . . . . . . . . . Creating Keys . . . . . . . . . . . . . . . . Examples of Read-Only Mapping . . . . . . . . . . . Creating a View for Singlevalued Attributes . . . . . . . Creating a View for the Multivalued Attribute . . . . . . Creating a View for Multivalued and Multi-Subvalued Attributes Examples of Updatable Mapping. . . . . . . . . . . . Create a Nested Level 0 Subtable . . . . . . . . . . Create a Nested Level 1 Subtable . . . . . . . . . . Create a Nested Level 2 Subtable . . . . . . . . . . Updatable Mapping Commands . . . . . . . . . . . . CREATE SUBTABLE . . . . . . . . . . . . . NLn_KEY . . . . . . . . . . . . . . . . . DROP SUBTABLE . . . . . . . . . . . . . .

11 . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . .

11-3 11-5 11-5 11-5 11-7 11-7 11-8 11-10 11-12 11-15 11-16 11-16 11-17 11-19 11-19 11-20 11-21 11-23 11-23 11-28 11-30

This chapter introduces updatable mapping. Note: Once you upgrade an account from a release earlier than 4.0, you will not be able to access the account with the earlier version of UniData SQL. IBM recommends that you perform a backup of your system before upgrading. This chapter includes the following sections: „

Terminology

„

1NF Mapping

„

Subtables

„

Examples of Read-Only Mapping

„

Examples of Updatable Mapping

„

Updatable Mapping Commands

11-2 Using UniData SQL

Terminology It is important to familiarize yourself with the following terms to help you understand the changes to UniData SQL: „

SQL – Structured Query Language. An industry standard for data management, data definition, data manipulation, access protections, and transaction control.

„

UniData SQL – The UniData implementation of SQL. UniData SQL handles tables both in the NF2 (nested relational) structure and the 1NF model.

„

Database – In general terms, a database is a collection of data that: „

Conforms to a common structure with a format defined by the metadata.

„

Is operated on (stored, modified, retrieved) by a database management system.

„

Can be concurrently accessed by multiple users under the protection of data sharing and concurrency rules provided by the database management system.

„

ODBC – Microsoft’s Open Database Connectivity (ODBC) interface that enables a variety of applications to access data in databases using SQL. The ODBC components consist of an application, driver manager, driver, and a data source.

„

1NF Database – A database that conforms to the first normal form type of data storage. Most databases are 1NF databases; as such, they have only singlevalued attributes. DB2 is a 1NF database.

„

NF2 Database – A database that conforms to the nested relational model. This model enables you to store data in a variety of attributes: singlevalued, multivalued, and multi-subvalued. This model avoids data redundancy. UniData is a NF2 database.

„

1NF Mapping – A mechanism that enables data stored in UniData relational tables to be viewed (read-only mapping) or updated (updatable mapping) by applications that operate on 1NF data, with the assumption that the data conforms to the 1NF model in which all attribute values are atomic.

„

Base Table – The place where the data resides. It includes the VOC pointer to a dictionary and a physical file. 11-3

„

Record ID – Commonly known in UniData terms as the @ID. This ID has the location of 0 LOC(0) and is the primary key of a table.

„

Subtable – This new type of UniData SQL view is designed for updatable 1NF mapping from an NF2 database. The subtable translates data stored in singlevalued, multivalued, or multi-subvalued formats into three distinct nested levels: 0, 1, and 2.

„

Nested Levels – In a UniData SQL table, singlevalued attributes are not nested (they correspond to the top level of nesting). Associations that combine related multivalued and multisubvalued attributes are nested subrelations. Within each association, multivalued and multi-subvalued attributes correspond to the first and second level of nesting, respectively. A subtable in UniData SQL contains related attributes that belong to the same nesting level. „

A subtable containing the singlevalued attributes of a UniData SQL table is a nested level 0 subtable.

„

A subtable containing either a unassociated multivalued attribute or the multivalued attributes of one association is a nested level 1 subtable.

„

A subtable containing either a unassociated multisubvalued attribute, or the multi-subvalued attributes of one association, is the nested level 2 subtable.

„

Schema – In SQL, a collection of database objects, such as tables, views, indexes, and so forth. sharing the same namespace. Each UniData account is a separate namespace and therefore fits the definition of a schema. In UniData, the term schema means the metadata that describes the UniData data. A schema, in UniData terms, is a set of catalog tables that contain names of 1NF views and subtables that map UniData nested data into 1NF format A UniData schema also contains the names and data types of its attributes.

„

Schema Generation – This process translates UniData dictionary information into a standard SQL form to store it in the database schema and creates UniData SQL views and subtables that serve as the 1NF mapping mechanism.

„

Schema Generator – A set of tools that guide a user through the translation process and acts on 1NF views and their descriptions to complete the translation.

11-4 Using UniData SQL

1NF Mapping 1NF mapping enables ODBC-compliant applications to access data stored in UniData (1NF) tables. The following definitions will help you understand the differences between the 1NF format and the UniData Nested Relational (NF2) data structure.

NF2 Data Model vs. 1NF Data Model Before you perform updatable mapping, you must understand the current state of your data. Is it in 1NF or NF2 format? „

NF2 Data – Data that conforms to the nested relational model. This model enables you to store data in a variety of attributes: singlevalued, multivalued, and multi-subvalued. This model avoids data redundancy. UniData is a NF2 database.

„

1NF Data – Data that conforms to the first normal form type of data storage. Most databases are 1NF databases; they have only singlevalued attributes. DB2 is a 1NF database.

Because UniData stores data in NF2 format, that data must be unnested before being passed to a 1NF application. If the application requires only read access, you can use 1NF mapping views to unnest it. If the application updates UniData tables, you must unnest the data by creating subtables.

What Is a 1NF Mapping View? A 1NF mapping view in UniData SQL presents data in such a way that it can be viewed by using a an SQL SELECT statement.

11-5

Creating 1NF Mapping Views Singlevalued Attribute Views – Any singlevalued attribute can be presented to 1NF applications without any mapping performed on it. All you need to do to access data in this form, is to include the attribute in a view. Because the record ID attribute uniquely identifies each record in a UniData table, you must also include this attribute to identify the nested relational record the view record comes from. The following statement creates a 1NF mapping view: sql> CREATE VIEW ORDER_INFO sql> AS SELECT ID, CLIENT_NO, ORD_DATE, ORD_TIME sql> FROM ORDERS; View ORDER_INFO created.

Multivalued and Multi-Subvalued Attribute Views – You must create a separate view for each association, for each unassociated multivalued, and for each unassociated multi-subvalued attribute. The following statements create 1NF mapping views for these types of attributes. This first example creates a view that unnests attributes in the association LINE_ITEMS: sql> sql> sql> sql>

CREATE VIEW ORDER_LINE_ITEMS AS SELECT ID, PRODUCT_NO, COLOR, QTY, PRICE FROM ORDERS UNNEST PRODUCT_NO, COLOR, QTY, PRICE;

View ORDER_LINE_ITEMS created

This example creates a view that unnests the unassociated multivalued attribute ADDRESS: sql> sql> sql> sql>

CREATE VIEW ORDER_ADDRESS AS SELECT ID, ADDRESS FROM ORDERS UNNEST ADDRESS;

View ORDER_ADDRESS created

11-6 Using UniData SQL

Subtables A subtable is a UniData SQL view that presents data stored in a UniData SQL table in such a way that it can be viewed and updated using 1NF SQL data manipulation commands. As a result, ODBC-compliant and other 1NF applications can select, insert, update, and delete data stored in UniData nested relational tables. In a UniData SQL table, singlevalued attributes are not nested (they correspond to the 0th level of nesting). Associations that combine related multivalued and multisubvalued attributes can be viewed as nested subrelations. Within each association, multivalued and multi-subvalued attributes correspond to the 1st and 2nd level of nesting, respectively. A subtable in UniData SQL contains related attributes that belong to the same nesting level. The nested levels are referred to as NL0, NL1, and NL2. „

NL0 subtable represents singlevalued attributes.

„

NL1 subtable represents associated multivalued attributes or unassociated multivalued attributes. All multivalued attributes must be in the same association.

„

NL2 subtable represents associated multi-subvalued attributes or unassociated multi-subvalued attributes.

These subtables are linked through primary and foreign keys.

Rules for Updating Subtables The following general rules apply to the update of a subtable: „

If you need to insert a record with a new record ID, you must first insert it into the nested level 0 subtable. This will result in an automatic insertion of a row with this ID into each subtable defined on the specified table. These new rows will have the specified value in their record ID attribute while all other attributes will receive no values (remain empty).

„

If you need to delete a record ID, you must delete it from a nested level 0 subtable. This will result in an automatic deletion of all the rows with this ID from all subtables defined on the specified table.

11-7

Primary and Foreign Keys To preserve the integrity of NF2 data, 1NF applications that update these data must understand the real data relationships in NF2 tables. UniData uses standard ANSI SQL and ODBC concepts to describe such relationships to 1NF applications. The NL0 subtable defines the primary key. The purpose of a primary key is to specify one or more attributes whose data values are unique among all data values of the same attributes in that subtable. UniData SQL does not allow a record to be added to a table with a duplicate key value. The primary key of the NL0 subtable becomes the foreign key in the NL1 subtable. So that UniData SQL can locate the primary key, you name the NL0 subtable in the REFERENCES clause of the CREATE SUBTABLE statement that creates the NL1 subtable. To create an NL2 subtable, you use the primary key of the NL1 subtable as the NL2 table’s foreign key, and list the NL1 subtable name in the REFERENCES clause. No primary key is required when you create an NL2 subtable, because no lower-level subtable will reference this (NL2) subtable; however, you may want to define a primary key for NL2 subtables to ensure the uniqueness of the data. The primary and foreign keys accomplish the same data relationships in subtables as associations do in UniData SQL base tables. The following table summarizes the number and value type of attributes required to create primary and foreign keys in subtables. Key

Subtable

Type

NL0

NL1

NL2

Primary

1S

1 S, 1 MV

1 S, 1 MV, 1 MS

Foreign

n/a

1S

1 S, 1 MV

Primary and Foreign Keys for Subtables

Note: If you create an NL1 subtable without a primary key, and then want to create an NL2 key, you have to drop and recreate an NL1 subtable that names a primary key. Then you can create an NL2 key that references the NL1 subtable.

11-8 Using UniData SQL

Warning: If two subtables map to the same association, but specify different primary keys, UniData SQL has no way of ensuring that primary keys are unique. This could result in duplicate values being added through UPDATE or INSERT, or by more than one row being updated by UPDATE.

Types of Primary and Foreign Keys Keys can be unique or generated. The concept of unique and generated keys applies to both primary and foreign keys, because the primary key of one subtable is the foreign key of another.

Unique Keys In a unique key, the uniqueness of the key value depends on the data itself. In this case, actual attribute names are used in the PRIMARY KEY or FOREIGN KEY clauses.

Generated Keys When you specify generated keys, UniData SQL assigns a number to be part of each key value, so that they key value is unique even when the data itself is not. In this case, you use NL1_KEY or NL2_KEY keyword in the SELECT statement, rename the selected values, and use these new names in the PRIMARY KEY or FOREIGN KEY clause. In addition to serving as a unique record identifier, a generated key provides information about the order in which rows are stored. 1NF databases assume that the order of rows in a table is not important: every row is identified with the values of its attributes, not the order in which they are stored. In a UniData table though, the order in which values of a multivalued or a multi-subvalued attribute are stored within a record may be important. It corresponds to the concept of a list structure which is not supported by 1NF databases. Generated keys, on the other hand, provide support for the list structures that exist in UniData tables.

Points to Remember about Subtables Remember the following when creating and using subtables:

11-9

„

Provide a primary key definition in a level 0 subtable. Provide a primary key definition in a level 1 subtable if you will later need to create a nested level 2 subtable referencing it.

„

A record ID value is required in all insert statements. It must be an existing value when inserting into a level 1 or 2 subtable.

„

To unambiguously identify rows of a subtable for update or delete, you must uniquely identify each row by its primary key. If a primary key is defined using the unique key technique, then: „

UniData SQL ensures its uniqueness on insert and update.

„

Inserted rows are appended after the last existing row.

„

If a primary key is defined using the generated key technique and you want to insert a row in a particular order, then specify the position using the generated key value; you can also leave the generated key value empty, and UniData SQL will append it to the end of the list.

„

All attributes of a nested level 1 or 2 subtable can be updated except for the record ID and the generated key attribute.

„

If you delete the last row of a nested level 1 or 2 subtable with the specified record ID, the row does not disappear, but all the attribute values (except record ID) become empty.

Creating Subtables Nested Level 0 Subtables The record ID attribute uniquely identifies each record in a UniData table. By including this attribute in a nested level 0 subtable, each row of this subtable is uniquely identified as well. Using standard SQL terminology, the record ID attribute is the primary key of a nested level 0 subtable. Therefore, the definition of a nested level 0 subtable must include the primary key specification. For example: sql> sql> sql> sql>

11-10 Using UniData SQL

CREATE SUBTABLE ORDER_NL0 AS SELECT ID, CLIENT_NO, ORD_DATE, ORD_TIME FROM ORDERS PRIMARY KEY ID;

Nested Level 1 Subtables For a nested level 1 subtable, the record ID values do not uniquely identify each row since these values are replicated due to unnesting of the multivalued attributes. On the other hand, by including the record ID attribute in a nested level 1 subtable, it becomes possible to identify which NF2 record each row of the nested level 1 subtable belongs to. Therefore, the record ID attribute is always included in a nested level 1 subtable. Every value of this record ID attribute in the nested level 1 subtable matches the corresponding record ID value of the nested level 0 subtable. Such a subtable relationship can be described using the standard SQL concept of a foreign key. Therefore, the definition of a nested level 1 subtable includes the foreign key specification. For example: sql> sql> sql> sql> sql> sql>

CREATE SUBTABLE ORDER_NL1 AS SELECT ID, PRODUCT_NO, PROD_NAME FROM ORDERS UNNEST PRODUCT_NO, PROD_NAME PRIMARY KEY ID, PRODUCT_NO FOREIGN KEY ID REFERENCES ORDER_NL0;

The implied referential constraint of this foreign key is DELETE CASCADE. The corresponding nested level 0 subtable name follows the keyword REFERENCES. The following referential integrity rules apply: „

By the foreign key definition, any row inserted into a nested level 1 subtable must have its record ID value matching one of the existing record ID values in the corresponding nested level 0 subtable.

„

By the rule of delete cascade, when a row is deleted from a nested level 0 subtable, all the rows of the corresponding nested level 1 subtable with the matching record ID are automatically deleted.

11-11

Nested Level 2 Subtables For a nested level 2 subtable, the primary key values of the corresponding nested level 1 subtable do not uniquely identify each row because these values are replicated when the multisubvalued attributes are unnested. By including the primary key attributes of the nested level 1 subtable in a nested level 2 subtable, we can identify the multivalued attribute values within the NF2 record that correspond to each row of the nested level 2 subtable. Therefore, the primary key attributes of the corresponding nested level 1 subtable are always included in a nested level 2 subtable. We specify the relationship between these attributes of the nested level 1 subtable and the subordinate nested level 2 subtable using the primary and foreign key concepts similar to the primary key/foreign key relationship between the primary key of the nested level 0 subtable and the foreign key of its subordinate nested level 1 subtable. sql>CREATE SUBTABLE ORDER_NL2 sql>AS SELECT ID, PRODUCT_NO, COLOR, QTY, PRICE sql>FROM ORDERS sql>UNNEST PRODUCT_NO, COLOR, QTY, PRICE sql>FOREIGN KEY ID, PRODUCT_NO REFERENCES ORDER_NL1;

Note: A nested level 2 subtable may or may not need to have its own primary key. In order to unambiguously identify rows of such a subtable for update or delete, it is necessary to identify its primary key. Both the unique key and the generated key techniques are available for nested level 2 subtables. The following is an example of a generated key NL2 subtable: sql>CREATE SUBTABLE ORDER_GEN2(ID, PRODUCT_NO, DETAIL, COLOR, QTY, PRICE) sql>AS SELECT ID, PRODUCT_NO, NL2_KEY(COLOR, QTY, PRICE), COLOR, QTY, PRICE sql>FROM ORDERS sql>UNNEST PRODUCT_NO, COLOR, QTY, PRICE sql>PRIMARY KEY ID, PRODUCT_NO, DETAIL sql>FOREIGN KEY ID, PRODUCT_NO REFERENCES ORDER_NL1;

Creating Keys While the values of generated key attributes can be manipulated with standard SQL insert and update commands, the semantics of these operations on generated key attributes are slightly different from those on other attributes.

11-12 Using UniData SQL

When inserting, you can omit the value of a generated key, in which case the row will be stored after the last existing row. If you specify the value of a generated key that is within the range of existing key values, the new row is stored at the position specified by this value. Other rows are “shifted down” to make space for the new row. If the specified value of a generated key is larger than the largest existing value, the row is appended. If you want to append to the end of a list, but do not know what the largest value of a generated key is, specify 0 to tell UniData SQL to store the new row at the end of the list.

Unique Keys Specifying the unique key technique for a nested level 1 subtable consists of identifying a single multivalued attribute in the specified association whose values are unique (distinct) within each NF2 record. Then, together with the record ID, this attribute comprises the primary key of this nested level 1 subtable, as demonstrated in the following example (PRIMARY KEY ID, PRODUCT_NO). sql> sql> sql> sql> sql> sql>

CREATE SUBTABLE ORDER_NL1 AS SELECT ID, PRODUCT_NO, PROD_NAME FROM ORDERS UNNEST PRODUCT_NO, PROD_NAME PRIMARY KEY ID, PRODUCT_NO FOREIGN KEY ID REFERENCES ORDER_NL0;

In this subtable, PRODUCT_NO has unique values within each NF2 record; therefore, this attribute can serve as the unique key. When no such a single multivalued attribute exists that can serve as a unique key, the generated key technique must be used.

Generated Keys A generated key in a nested level 1 subtable is a calculated attribute whose values represent the position number of each row of this subtable. The numbering of rows starts with 1 and is incremented by 1 for each next row. The generated key attribute is defined with the NL1_KEY function within the CREATE SUBTABLE statement. Together with the record ID, this attribute comprises the primary key of this nested level 1 subtable. sql>CREATE SUBTABLE ORDER_GEN(ID, PROD_ID, PRODUCT_NO) sql>AS SELECT ID, NL1_KEY(PRODUCT_NO), PRODUCT_NO sql>FROM ORDERS sql>UNNEST PRODUCT_NO sql>PRIMARY KEY ID, PROD_ID sql>FOREIGN KEY ID REFERENCES ORDER_NL0;

11-13

Examples of Read-Only Mapping This section uses the ORDERS table to demonstrate read-only mapping. Notice that associated multivalued attributes and multi-subvalued attributes are represented in this table: LISTDICT ORDERS; Page 1 @ID TYP LOC CONV ASSOC --------------- --- ------------- -----------@ID D 0 ID D 0 ORD_DATE D 1 D4/ ORD_TIME D 2 MTH CLIENT_NO D 3 PRODUCT_NO D 4 LINE_ITEMS COLOR D 5 LINE_ITEMS QTY D 6 LINE_ITEMS PRICE D 7 MD2, LINE_ITEMS ... LINE_ITEMS PH PRODUCT_NO PR ... 28 records listed

11-14 Using UniData SQL

NAME

FORMAT SM

--------------- ------ -- ORDERS Order Number Order Date Order Time Client Number Product Number

10R 10R 10R 7R 10R 10R

S S S S S MV

Color

10T

MS

Qty

6R

MS

Price

10R

MS

Creating a View for Singlevalued Attributes This first view combines the record ID and all selected singlevalued attributes in a view: CREATE VIEW ORDERS_VIEW AS SELECT ID, ORD_DATE, ORD_TIME, CLIENT_NO FROM ORDERS; View ORDERS_VIEW created. SELECT * FROM ORDERS_VIEW; Page 1 Order Order Order Client Number Date Time Number ---------- ---------- ------- ---------912 10/24/2000 12:30PM 9984 801 10/13/2003 04:34PM 10018 941 10/24/2001 03:00PM 10009 805 10/14/2000 11:11AM 9987 830 10/25/2001 03:09PM 10013 863 10/20/2000 09:45AM 10006 834 10/25/2001 03:27PM 10004 861 10/20/2009 12:51PM 10020 890 10/25/2000 07:00PM 10009 914 10/24/2000 02:00PM 9975 ... Page 11 Order Order Order Client Number Date Time Number ---------- ---------- ------- ---------922 10/24/2000 09:39AM 10021 811 10/13/2007 10:10PM 10023 192 records listed

Creating a View for the Multivalued Attribute This example creates and uses a view for the table’s multivalued attribute. It includes: „

ID

„

Multivalued attribute

CREATE VIEW ORDERS_MV1_VIEW AS SELECT ID, PRODUCT_NO FROM ORDERS UNNEST PRODUCT_NO WHERE ID BETWEEN 810 AND 815; View ORDERS_MV1_VIEW created.

11-15

SELECT * FROM ORDERS_MV1_VIEW; Page 1 Order Number Product Nu ---------- ---------813 30000 813 34000 813 11020 810 40008 810 40013 810 13005 812 10001 812 14001 812 12002 812 33000 812 10008 811 13001 811 13002 811 15001 14 records listed

Creating a View for Multivalued and MultiSubvalued Attributes This view selects all multivalued and multi-subvalued attributes. All of these attributes belong to the same association. This view contains: „

11-16 Using UniData SQL

Record ID

„

Unnested multivalued and multi-subvalued attributes

CREATE VIEW ORDERS_SMV_VIEW AS SELECT ID, PRODUCT_NO, COLOR, QTY, PRICE FROM ORDERS UNNEST PRODUCT_NO, COLOR, QTY, PRICE WHERE ID BETWEEN 810 AND 815; View ORDERS_SMV_VIEW created. SELECT * FROM ORDERS_SMV_VIEW; Page 1 Order Number Product Nu Color Qty Price ---------- ---------- ---------- ------ ---------813 30000 Black 1 $99.96 813 34000 Oak grain 1 $199.87 813 11020 Black 1 $69.94 810 40008 White 2 $69.92 810 40013 Silver 1 $47.72 810 13005 Gray 2 $500.00 812 10001 Gray 1 $1,995.00 812 14001 Black 3 $49.95 812 12002 N/A 1 $1,999.00 812 33000 Cherry 1 $599.96 812 10008 Black 1 $400.00 811 13001 N/A 6 $94.00 811 13002 N/A 1 $150.00 811 15001 N/A 6 $119.00 14 records listed

11-17

Examples of Updatable Mapping This section demonstrates creating and using subtables through which you can update the base table. The three levels (NL0, NL1, and NL2) are linked by primary and foreign keys. They represent the three levels of nested data contained in UniData NF2 base tables: „

NL0 – Represents singlevalued attributes.

„

NL1 – Represents multivalued attributes.

„

NL2 – Represents multi-subvalued attributes.

Note: You cannot create a single subtable that contains attributes from different associations or that combine more than one value type — such as combining singlevalued and multi-vlaued attributes. If you do, UniData returns an error message indicating the type of error, such as: a subtable cannot contain both ‘MV’ and ‘MS’ attributes. (Note that the foreign key, which is the primary key of the subtable or table at the next-higher level, is the only exception to this rule.)

Create a Nested Level 0 Subtable This first subtable combines the Record ID and selected singlevalued attributes into a updatable subtable. It contains: „

Record ID.

„

Singlevalued attributes.

11-18 Using UniData SQL

„

Primary key to enable referencing by the NL1 subtable.

CREATE SUBTABLE ORDER_NL0 AS SELECT ID, CLIENT_NO, ORD_DATE, ORD_TIME FROM ORDERS PRIMARY KEY ID; Subtable ORDER_NL0 created. SELECT * FROM ORDER_NL0 WHERE ID BETWEEN 810 AND 815; Page 1 Order Client Order Number Number Date ---------- ---------- ---------813 10024 10/10/2007 810 9975 10/10/2006 812 10008 10/11/2004 811 10023 10/13/2007 4 records listed

Order Time ------05:00PM 08:55AM 04:00PM 10:10PM

Create a Nested Level 1 Subtable This next subtable represents the base table’s multivalued attribute. It contains: „

Record ID.

„

Unnested multivalued attribute.

„

Foreign key that references the NL0 subtable.

11-19

„

Primary key to enable referencing by the NL2 subtable.

CREATE SUBTABLE ORDER_NL1 AS SELECT ID, PRODUCT_NO FROM ORDERS UNNEST PRODUCT_NO, PROD_NAME PRIMARY KEY ID, PRODUCT_NO FOREIGN KEY ID REFERENCES ORDER_NL0; Subtable ORDER_NL1 created. SELECT * FROM ORDER_NL1 WHERE ID BETWEEN 810 AND 815; Page 1 Order Number Product Nu Product Na ---------- ---------- ---------813 30000 CD System 1 813 34000 Speaker 813 11020 TV 810 40008 Telephone 810 40013 Telephone 810 13005 Hard Drive 812 10001 Computer 812 14001 Memory 812 12002 Monitor 812 33000 CD System 3 812 10008 Printer 811 13001 Adapter 811 13002 Adapter 811 15001 Modem 14 records listed

Create a Nested Level 2 Subtable The final subtable represents the base table’s multi-subvalued attribute. It contains: „

Record ID.

„

Unnested multi-subvalued attributes.

„

The primary key from the multivalued attributes subtable.

„

Foreign key that references the NL1 subtable.

11-20 Using UniData SQL

„

A unique key to uniquely identify rows to be updated or deleted.

CREATE SUBTABLE ORDER_NL2 AS SELECT ID, PRODUCT_NO, COLOR, QTY, PRICE FROM ORDERS UNNEST PRODUCT_NO, COLOR, QTY, PRICE FOREIGN KEY ID, PRODUCT_NO REFERENCES ORDER_NL1; Subtable ORDER_NL2 created. SELECT * FROM ORDER_NL2 WHERE ID BETWEEN 810 AND 815; Page 1 Order Number Product Nu Color Qty Price ---------- ---------- ---------- ------ ---------813 30000 Black 1 $99.96 813 34000 Oak grain 1 $199.87 813 11020 Black 1 $69.94 810 40008 White 2 $69.92 810 40013 Silver 1 $47.72 810 13005 Gray 2 $500.00 812 10001 Gray 1 $1,995.00 812 14001 Black 3 $49.95 812 12002 N/A 1 $1,999.00 812 33000 Cherry 1 $599.96 812 10008 Black 1 $400.00 811 13001 N/A 6 $94.00 811 13002 N/A 1 $150.00 811 15001 N/A 6 $119.00 14 records listed

11-21

Updatable Mapping Commands CREATE SUBTABLE Syntax CREATE SUBTABLE subtable_name [(attribute_subtable_name [,attribute_subtable_name]...)] AS SELECT query_spec [PRIMARY KEY attribute_name[,attribute_name]...] [FOREIGN KEY attribute_name[,attribute_name]... REFERENCES subtable_name]

Description CREATE SUBTABLE creates a UniData SQL view that presents values from the base table in such a way that those values can be viewed and updated using 1NF ANSI-standard SQL Data Manipulation Language (DML) commands. This enables you to update data in UniData SQL base tables using desktop tools that access these subtables through UniData ODBC or UniOLEDB. Note: UniData SQL updatable mapping does not duplicate, replicate, modify, normalize, or replace the nested relational data. Data values are stored only once — in native nested relational form. Subtables are simply views of the data in its native (NF2) form. Tools that generate ANSI-standard SQL DML commands manipulate data in the subtables as if it were stored in standard 1NF tables.

11-22 Using UniData SQL

Parameters The following table describes each parameter of the syntax. Parameter

Description

subtable_name

Specifies the name of the new subtable. Subtable names can contain alphabetic characters, numbers, or the underscore character. ODBC prohibits the use of other special characters. The following restrictions apply to subtable names: The first character of the name cannot be a number. The subtable name must be unique among tables, subtables, and views in the database. The maximum length of the table name is 30 characters.

attribute_subtable_name

The name to be used for the attribute in this subtable. If you rename one attribute, you must list all attributes, even if you use the attribute name from the base table. You must rename attributes when a generated key is used.

AS SELECT query_spec

Selects data to include in the subtable. query_spec is defined in the SELECT statement section of this manual. You must UNNEST nested attributes to create a subtable through which you can update the base table. The SELECT clause cannot contain more than 255 virtual attributes. query_spec cannot contain aggregation function or expressions (except for NL1_key or NL2_key).

PRIMARY KEY attribute_name

One or more attributes to use as the primary key for this subtable. If you rename an attribute (see attribute_subtable_name, above), you must use the new name here.

FOREIGN KEY attribute_name

One or more attributes to use as the foreign key for this subtable. The foreign key refers to the primary key of a subtable one nested level higher. If you rename an attribute (see renamed_attribute, above), you must use the new name here.

REFERENCES subtable_name

The corresponding subtable name, one nested level higher, that contains, as a primary key, the attribute_names listed in the FOREIGN KEY clause of this statement. CREATE SUBTABLE Parameters

11-23

Examples This series of examples demonstrates creating NL0, NL1, and NL2 subtables that are linked by primary and foreign keys. This first statement creates the NL0 subtable. Notice that this statement does not rename the attributes, but uses the attribute names from the CLIENTS table dictionary. The new subtable contains unique primary keys; no foreign key is allowed because this is the top (NL0) level subtable. sql> sql> sql> sql>

CREATE SUBTABLE ORDER_NL0 AS SELECT ID, CLIENT_NO, ORD_DATE, ORD_TIME FROM ORDERS PRIMARY KEY ID;

Subtable ORDER_NL0 created.

And the following SELECT statement lists the contents of this new subtable: sql> SELECT * FROM ORDER_NL0; Page 1 Order Client Order Number Number Date ---------- ---------- ---------912 9984 01/13/1996 801 10018 09/28/1995 941 10009 01/14/1996 805 9987 10/05/1995 830 10013 01/24/1996 970 9988 01/15/1996 863 10006 12/04/1995 ... 834 10004 01/24/1996 922 10021 01/13/1996 811 10023 10/02/1995 193 records listed

Order Time ------12:30PM 04:34PM 03:00PM 11:11AM 03:09PM 10:00AM 09:45AM 03:27PM 09:39AM 10:10PM

This example creates the NL1 subtable. Notice that multivalued attributes are selected and unnested in the subtable. Note: Because PROD_NAME is a virtual field, it cannot be included in an INSERT statement and its value cannot be updated with UPDATE. sql> sql> sql> sql> sql> sql>

11-24 Using UniData SQL

CREATE SUBTABLE ORDER_NL1 AS SELECT ID, PRODUCT_NO, PROD_NAME FROM ORDERS UNNEST PRODUCT_NO, PROD_NAME PRIMARY KEY ID, PRODUCT_NO FOREIGN KEY ID REFERENCES ORDER_NL0;

The following SELECT statement displays the (unnested) multivalued table, ORDER_NL1: sql> SELECT * FROM ORDER_NL1; Page 1 Order Number Product Nu Product Na ---------- ---------- ---------912 53000 Printer Cartridge 801 11000 Computer 941 50000 Computer ... 818 14001 Memory 818 14002 Memory 818 14003 Memory 818 13004 Hard Drive 818 13005 Hard Drive 818 13006 Hard Drive 818 54030 Hard Drive 818 54060 Hard Drive 818 54080 Disk Drive ... Page 32 Order Number Product Nu Product Na ---------- ---------- ---------811 13002 Adapter 811 15001 Modem 487 records listed

The next example creates the NL2 subtable, which unnests the multi-subvalued attributes. Because EPRICE is a virtual field, it cannot be included in an INSERT statement and its value cannot be updated with UPDATE. sql> CREATE SUBTABLE ORDER_NL2 AS SELECT ID, PRODUCT_NO, COLOR, QTY, PRICE, EPRICE FROM ORDERS UNNEST PRODUCT_NO, COLOR, QTY, PRICE FOREIGN KEY ID, PRODUCT_NO REFERENCES ORDER_NL1; Subtable ORDER_NL2 created.

11-25

This final example displays a portion of the NL2 subtable: sql> SELECT * FROM ORDER_NL2; Page 1 Order Number Product Nu Color Qty Price Extended Pri ---------- ---------- ---------- ------ ---------- -----------912 53000 N/A 6 $129.95 $779.70 801 11000 Gray 1 $1,799.00 $1,799.00 941 50000 Gray 10 $1,399.99 $13,999.90 805 11140 Gray 5 $149.97 $749.85 805 10120 Standard 25 $429.94 $10,748.50 805 10050 Yellow 13 $29.97 $389.61 805 10150 Silver 15 $48.82 $732.30 805 10070 Silver 999 $34.97 $34,935.03 ... 505 records listed

NLn_KEY Syntax NL1_KEY(mvattribute | msattribute... | association_name) NL2_KEY(msattribute... | association_name)

Description The NL1_KEY expression creates a counter for the number of values in one or more associated multivalued and multi-subvalued attributes. The NL2_KEY expression creates a counter for the number of subvalues in one or more associated multi-subvalued attributes. Points to remember about NLn_KEY: „

At least one attribute must be included in the NLn_KEY expression.

„

All attributes specified in a NLn_KEY must belong to the same association.

„

Only D-type (data type) attributes can be used.

„

If an association name is specified, it is replaced by all D-type attributes in the association.

11-26 Using UniData SQL

„

When more than one multivalued or multi-subvalued attributes are used, the counter is incremented even when a value is contained in only one of the attributes. For example, in the following example, the counter is incremented for the COLOR attribute value Green, even though no value is present in the QTY attribute:

sql> SELECT * FROM ORDERS; Page 1 ORDER_NUM CLIENT PRODUCT_NO COLOR QTY CNT ---------- ---------- ---------- ---------- ------ -------949 9990 56090 Red 3 1 Blue 3 2 Gray 3 3 Green 4

Examples NL1_KEY In the following example, the SELECT statement displays the value of the counter for each value in PRODUCT_NO. Notice that the attributes must be renamed in this view because NL1_KEY is an expression: sql> SELECT ID, ORD_DATE, ORD_TIME, CLIENT_NO, NL1_KEY(PRODUCT_NO) sql> FROM ORDERS sql> UNNEST PRODUCT_NO; Page 1 ORDER_NUM ODATE ---------- ---------912 01/13/1996 801 09/28/1995 941 01/14/1996 805 10/05/1995 805 10/05/1995 805 10/05/1995 805 10/05/1995 805 10/05/1995 830 01/24/1996 830 01/24/1996 830 01/24/1996 830 01/24/1996 830 01/24/1996 ... 811 10/02/1995 487 records listed

TIME CLIENT CNT ------- ---------- -------12:30PM 9984 1 04:34PM 10018 1 03:00PM 10009 1 11:11AM 9987 1 11:11AM 9987 2 11:11AM 9987 3 11:11AM 9987 4 11:11AM 9987 5 03:09PM 10013 1 03:09PM 10013 2 03:09PM 10013 3 03:09PM 10013 4 03:09PM 10013 5 10:10PM

10023

3

11-27

NL2_KEY The following SELECT statement includes a counter for the multi-subvalued attributes COLOR and QTY: sql> SELECT ID, CLIENT_NO, PRODUCT_NO, sql> COLOR, QTY, NL2_KEY(COLOR, QTY) sql> FROM ORDERS; Page 1 ORDER_NUM CLIENT PRODUCT_NO COLOR QTY CNT ---------- ---------- ---------- ---------- ------ -------912 9984 53000 N/A 6 1 801 10018 11000 Gray 1 1 941 10009 50000 Gray 10 1 ... 833 9974 40004 Almond 100 1 Gray 99 2 40005 White 60 1 Almond 60 2 40007 Gray 60 1 ... 969 9988 56080 Black 50 1 Red 50 2 Blue 50 3 ... 949 9990 56090 Red 3 1 Blue 3 2 Gray 3 3 56070 Red 3 1 Blue 3 2 ... 193 records listed

DROP SUBTABLE Syntax DROP SUBTABLE subtable_name;

Description The DROP SUBTABLE command drops a subtable that was created by a CREATE SUBTABLE statement. It also drops all views created on the subtable, but it does not affect the base table.

11-28 Using UniData SQL

If a higher-level subtable is dropped, it renders invalid all subtables referencing it. Before dropping a higher-level subtable, first drop all of subtables that reference it.

Example The following example demonstrates creating and dropping a subtable: sql> sql> sql> sql>

CREATE SUBTABLE ORDERS_TST AS SELECT ID, ORD_DATE, ORD_TIME, CLIENT_NO FROM ORDERS PRIMARY KEY ID;

Subtable ORDERS_TST created.

sql> DROP SUBTABLE ORDERS_TST; Subtable ORDERS_TST has been dropped.

11-29

Chapter

Null and Missing Values

What Are Missing Values? . . . . . . . What Are Null Values? . . . . . . . . Effects of Null and Missing Values in UniData SQL. Null and Missing Values in ODBC . . . . . . Working with Null Values . . . . . . . . . Points to Remember . . . . . . . . . Examples . . . . . . . . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

12 . 12-2 . 12-3 . 12-5 . 12-7 . 12-9 . 12-9 . 12-10

The behavior of missing values in UniData is different from the behavior of ANSI SQL nulls. To make UniData SQL ANSI compliant, UniData uses true null value handling. However, because many existing UniBasic programs are not able to process nulls, we have also provided users with a configuration parameter to turn on or off null value handling. Tip: After turning null value handling on, IBM recommends that you not turn it off, as the null character may have been introduced into your data. If you must turn null value handling off, be sure to check your data and convert the null value to another string (using a UniBasic program or virtual attribute) before attempting to execute queries, virtual attributes, or UniBasic programs. Regardless of whether null value handling is on or off, UniData products handle missing values consistently. With null value handling turned on, UniData products handle null values consistently, and UniData SQL is compliant with ANSI standards.

What Are Missing Values? A missing value is one that is not present. Missing values also have the following characteristics: „

In display and print, missing values take up no space.

„

If a numeric value is used in an expression, missing values are considered to be 0.

„

If a missing value is used in an expression in which a character is expected, the missing value is considered to be an empty string.

„

A missing value is smaller than any other character value.

Here are some examples of missing values: „

In a singlevalued attribute: no value is entered for a particular singlevalued attribute

„

In a multivalued attribute: two consecutive value marks are entered; the value between them is missing; also, if you enter the literal "}}", UniData inserts three missing values: one before the value marks, a second between the two, and a third after the value marks.

„

Two associated multivalued attributes have different numbers of values. When the association is unnested, both attributes are considered to have the same number of values. To accomplish this, missing values are added to the attribute that originally had fewer values. 12-2

Example The following example demonstrates the results obtained by unnesting associated multivalued attributes. Attributes A and B are associated. Multivalued attribute A: 1 23 4 5 6 7 8

Multivalued attribute B: A BC D

When unnested, the result table has the following values: 1 23 4 5 6 7 8

A BC D (missing (missing (missing (missing

value) value) value) value)

What Are Null Values? Null values have the following characteristics in UniData SQL when null value handling is on: „

When used in any expression, the result of the expression is null.

„

When compared to any other value, including itself, the result is false. The only way to compare a null value is to use the keywords IS [NOT] NULL.

„

The null value is the lowest value.

12-3 Using UniData SQL

Similarities and Differences in Null and Missing Values The only similarity between null and missing values is that both types of values are not known when data was entered into the database. Differences between null and missing values include the following: „

In an expression, such as X = Y, if X and Y are missing, the expression evaluates to true. If X and Y are null values, the expression evaluates to false.

„

In a numeric expression, such as X + 1, if X is missing, the result is 1, but if X is null, the result is null.

„

In a character expression, such as "ABC"||X. If X is missing, the result is ABC. If X is the null value, the result is the null value.

„

The null value is lower than any other value including missing values.

12-4

Effects of Null and Missing Values in UniData SQL The following table summarizes the effect of UniData’s handling of null and missing values. Notice that UniData SQL handles missing values consistently regardless of data type. UniData SQL Operation

Null Value Handling Off

On

WHERE (retrieves rows based on values in a particular attribute)

IS NULL selects rows containing missing values.

Aggregation: MIN, MAX, SUM, AVG, COUNT

Missing values are included in calculations.

Missing values are included in calculations. Null values are excluded from calculations.

COUNT(*)

Missing values are included in count.

Null and missing values are included in count.

ORDER BY

Missing string values are smaller than any other string value.

Missing values are sorted the same way as with null flag off.

IS NOT NULL excludes rows containing missing values.

Missing numeric values: If index is not used: -1 < missing < 0

IS NULL retrieves rows containing null values. IS NOT NULL excludes rows containing null values.

Null values are the lowest. They are sorted first in an ascending sort.

If index is used: missing is smaller than any other number. Null and Missing Values in UniData SQL

12-5 Using UniData SQL

UniData SQL Operation

Null Value Handling

UPDATE TABLE ORDERS SET X = NULL

Changes X to a missing value.

Changes X to the null value.

INSERT INTO TABLE ORDERS (ID, ATTR1, ATTR2) VALUES(1, NULL, "X")

Inserts a missing value into ATTR1.

Inserts the null value into ATTR1.

INSERT INTO TABLE ORDERS (ID,ATTR2) VALUES(1,"X")

Inserts a missing value into ATTR1.

Inserts a missing value into ATTR1.

Null and Missing Values in UniData SQL (continued)

12-6

Null and Missing Values in ODBC UniData missing values are not supported by ODBC and ANSI SQL. In these products, the null value represents both unknown and missing data. Therefore, missing values are translated into nulls by UniData SQL and the client product never sees missing values. What the client sees as null values may be either null or missing values. Warning: Desktop products handle null and missing values in various ways. Some third-party desktop (client) products are capable of handling zero-length strings. UniData uses the VCHAREMPTY configuration parameter in the uci.config file to determine how to treat an empty, or missing, value. If VCHAREMPTY = ON, UniData does not convert the empty (missing) value to the null value. If VCHAREMPTY=OFF, or is not defined, UniData converts the empty (missing) value to the null value. The following table summarizes the effects of UniData’s handling of null and missing values through ODBC. Remember that, with null value handing on, you can emulate this processing in UniData SQL by executing SET MISSING NULL. ODBC Operation

Null Value Handling Off

WHERE (retrieves rows based on values in a particular attribute)

IS NULL retrieves rows containing missing values. IS NOT NULL excludes rows containing missing values.

Aggregation: MIN, MAX, SUM, AVG, COUNT

Missing values are included in calculations.

COUNT(*)

Missing values are included in count.

On IS NULL retrieves rows containing null and missing values. IS NOT NULL excludes rows containing null and missing values. Null and missing values are excluded from calculations.

This does not conform to ANSI standards. Null and missing values are included in count.

Null and Missing Values through ODBC

12-7 Using UniData SQL

ODBC Operation ORDER BY

Null Value Handling Although the client, through ODBC, sees null values, those values are sorted as UniData missing values.

Null and missing values are all sorted as nulls: the lowest. They are sorted first in an ascending sort.

Missing numeric values: If index is not used: -1 < missing < 0 If index is used: missing is smaller than any other number. This does not fully comply with ANSI standards. UPDATE TABLE ORDERS SET X = NULL

Changes X to a missing value.

Changes X to the null value.

INSERT INTO TABLE ORDERS (ID, ATTR1, ATTR2) VALUES(1, NULL, "X")

Inserts a missing value into ATTR1.

Inserts the null value into ATTR1.

INSERT INTO TABLE ORDERS (ID,ATTR2) VALUES(1,"X")

Inserts a missing value into ATTR1.

Inserts a missing value into ATTR1. This does not fully comply with ANSI standards.

Null and Missing Values through ODBC (continued)

12-8

Working with Null Values With the udtconfig parameter NULL_FLAG on, the null value represents an unknown value, and empty string represents missing values: “”. The remainder of this section assumes that null value handling is on. The ASCII character that represents the null character is determined by the language group. See Administering UniData for instructions on setting the UniData configuration parameter NULL_FLAG. See UniData International for information on setting the language group. Note: The UniData SQL statement SET MISSING NULL causes UniData SQL to treat missing values as null values, as does ODBC. See SET in the UniData SQL Commands Reference for an example.

Points to Remember Remember the following points when writing UniData SQL statements that manipulate data containing the null value: „

IS NULL returns all null values. IS NOT NULL returns all non-null values.

„

The null value is ignored by the aggregate functions: AVG, COUNT, MAX, MIN, NUMBER, STD, and VARIANCE. Hint: COUNT(*) counts rows that contain the null value.

„

You can convert the null value to another string with the NVL function.

„

The null value affects expressions and functions in the following ways:

12-9 Using UniData SQL

„

When used in arithmetic expressions, the null value is considered to be 0.

„

In comparisons, the null value is considered to be smaller than any character, string, or number. In ORDER BY, the null value is sorted as the lowest value.

„

The result of a Boolean expression that compares any value with the null value returns a result of false. This includes comparisons in which the null value is compared with itself.

Examples This section provides examples of null value handling in UniData SQL with null value handling ON (utdconfig parameter NULL_FLAG = 1).

Selecting Null Values This example demonstrates selecting rows from the table STAFF for which the LNAME attribute contains the null value: sql> SELECT ID, NVL(LNAME, "NULL"), DEPT, TITLE FROM STAFF sql> WHERE LNAME IS NULL; Page 1 STAFF NVL(LNAME,"NULL") Dept Title ---------- ----------------- ---- ------4 NULL PSY AscProf 1 records listed

12-10

Aggregate Functions and the Null Value The following example demonstrates the effect of the null value on group functions. Because the null value represents an unknown value, nulls are ignored. sql> COMPUTE SUM OF PRICE ON PROD_NAME; sql> COMPUTE COUNT OF PRICE ON PROD_NAME; sql> COMPUTE MIN OF PRICE ON PROD_NAME; sql> COMPUTE MAX OF PRICE ON PROD_NAME; sql> COMPUTE STD OF PRICE ON PROD_NAME; sql> COMPUTE VARIANCE OF PRICE ON PROD_NAME; sql> BREAK ON PROD_NAME SKIP 1; sql> SELECT PROD_NAME, PRODUCT_NO, PRICE FROM ORDERS sql> UNNEST PRODUCT_NO sql> WHERE PRODUCT_NO BETWEEN 10010 AND 10020 ORDER BY PRODUCT_NO; Page 1 Product Na Product Nu Price ---------- ---------- ---------Camcorder 10010 N 10010 N 10010 N 10010 N 10020 $200.00 10020 $200.00 10020 $200.00 10020 $200.00 ********** ---------cnt 4 max $200.00 min $200.00 std $0.00 sum $800.00 var $0.00 8 records listed

Counting Rows Containing Null Values Use COUNT(*) to include in count rows that may contain the null value. COUNT(attribute_name) excludes from the count rows that contain the null value in attribute_name. This next two examples contrasts the COUNT(attribute_name) and COUNT(*).

12-11 Using UniData SQL

Before executing this example, LNAME “James” was changed to the null value. Here are the records to be counted: sql> SELECT ID, LNAME FROM STAFF WHERE SAL > 30000; Page 1 STAFF Last Nam ---------- -------3 Eades 6 Masters 4 8 Fried 4 records listed

As you can see in the following example, COUNT does not count rows for which the attribute being counted is the null value. Four rows are read, but only three are included in the count: SELECT COUNT(LNAME) FROM STAFF WHERE SAL > 30000; Page 1 CNT(Last -------3 4 record(s) calculated

COUNT(*), on the other hand, does include rows containing the null value (unless the attribute in the WHERE condition is the null value): SELECT COUNT(*) FROM STAFF WHERE SAL > 30000; Page 1 COUNT(*) -------4 4 record(s) calculated

12-12

Converting the Null Value for Display or Print The following example demonstrates the NVL function, which converts the null value to another string for print or display purposes: SELECT ID, Page 1 STAFF ---------3 6 9

NVL(LNAME, "Unknown"), DEPT, TITLE FROM STAFF; NVL(LNAME,"Unknown") -------------------Eades Masters Aaron

1 Fisher 4 Unknown 7 Carnes 2 Otis 5 Gibson 8 Fried 9 records listed

Dept ---FA PSY PSY BUS PSY ENG FA SPA BUS

Title ------Prof Ascprof Counsel or Prof AscProf Prof AscProf Prof Dean

The Null Value in Expressions and String Functions Before executing this example, the ZIP code for customer Carrie Fischer was changed to the null value. The next example demonstrates the fact that the null value cannot be compared with any value, therefore it is not selected by the comparison “< 10050”. sql> SELECT @ID, NAME, ZIP Page 1 Cust Customer Name ---------- --------------100 Jones, Samuel 1 records listed

12-13 Using UniData SQL

FROM CUSTOMER WHERE ZIP < 10050; Zip C ----01212

Before executing this example, the salary for Fischer was changed to the null value. This example demonstrates the fact that any arithmetic operation including the null value results in the null value: sql> SET NULL “N”; sql> SHOW NULL; sql> SELECT @ID, LNAME, SAL, SAL +(SAL/20) "New Pay" sql> FROM STAFF; Page 1 STAFF Last Nam Salary New Pay ---------- -------- ------- -----------3 Eades 42,000 44100 6 Masters 35,800 37590 9 Aaron 19,900 20895 1 Fisher N N 4 James 32,500 34125 7 Carnes 29,900 31395 2 Otis 25,800 27090 5 Gibson 26,800 28140 8 Fried 98,000 102900 9 records listed

12-14

Appendix

UniData SQL Reserved Words This appendix contains a list of words that are reserved for specific usage in UniData SQL. These words should only be used in their proper syntax in UniData SQL statements. Do not use these words as names of files or variables in your UniData SQL statements. Note: If you use reserved words incorrectly in your UniData SQL statements, your statement or program could result in an error.

A

Using Reserved Words The following example shows an incorrect use of the SELECT reserved word. In this example, the SELECT reserved word is used twice; once in its proper syntax and once in place of the name of a file. sql> SELECT NAME, CITY FROM SELECT;

The following example shows the correct use of the SELECT reserved word. In this example, the SELECT reserved word is used only once, in its proper syntax. sql> SELECT NAME, CITY FROM CUSTOMER;

A-2

List of Reserved Words Reserved Words ADD

ALL

ALTER

AND

ANY

AS

ASC

AUTO

AVG

BETWEEN

BREAK

BTITLE

BY

CHAR

CLEAR

COLUMN

COMMIT

COMO

COMPUTE

COUNT

CREATE

DATA

DATE

DAY

DEFAULT

DELETE

DESC

DICT

DISTINCT

DROP

DYNAMIC

EVERY

EXISTS

EXIT

EXP

EXPDESC

FROM

GRANT

GROUP

HAVING

IN

INDEX

INSERT

INTERSECT

INTO

IS

LEFT

LIKE

LISTDICT

LOCK

LONG

MAX

MIN

MODIFY

MONTH

NOT

NOWAIT

NULL

NUMBER

OF

OFF

ON

OPTION

OR

ORDER

PH

PUBLIC

QUIT

REVOKE

RIGHT

ROLLBACK

SELECT

SET

SHOW

SQL

STDDEV

SUBTABLE

SUM

TABLE

TRANSACTION

TO

TTITLE

UNION

UNNEST

UniData SQL Reserved Words

A-3 Using UniData SQL

Reserved Words UPDATE

VALUE

VALUES

VARIANCE

VIEW

WHEN

WHERE

WITH

YEAR UniData SQL Reserved Words (continued)

A-4

Appendix

More Table Joins

This appendix provides examples of SELECT statements that perform inner and outer joins — extracting data from two or more tables, subtables, or views. The concept of joining tables is introduced in Chapter 6, “The SELECT Statement.” That chapter provides examples for writing simple joins. This appendix, however, provides examples of more complex joins, such as the following: „

Combining joins with other SELECT statement clauses, such as WHERE, ORDER BY and subqueries.

„

Stacking join clauses to include the results from a join in a second join.

The screen examples in this appendix are como files created by executing an SQL script that begins by executing the COMO ON command. Remember, the UniData SQL COMO command captures user input and system response as displayed on the terminal screen.

B

Tables for Join Examples Let’s create some simple tables we can use to try out various kinds of joins: create table TABLE_AB (attrib_num number, attrib_char char(10)); 2 attribute(s) added. Create file TABLE_AB, modulo/1,blocksize/1024 create table TABLE_CD (attrib_num number, attrib_char char(10)); 2 attribute(s) added. Create file TABLE_CD, modulo/1,blocksize/1024 insert into 1 record(s) insert into 1 record(s)

TABLE_AB values (1, 1, "aaa"); created. TABLE_AB values (2, 2, "bbb"); created.

insert into 1 record(s) insert into 1 record(s)

TABLE_CD values (1, 1, "ccc"); created. TABLE_CD values (3, 10, "ddd"); created.

Here are the tables we just created: select * from TABLE_AB; Page 1 TABLE_AB attrib_num attrib_char ---------- ---------- ---------1 1 aaa 2 2 bbb 2 records listed select * from TABLE_CD; Page 1 TABLE_CD attrib_num attrib_char ---------- ---------- ---------1 1 ccc 3 10 ddd 2 records listed

B-2

Examples of Outer Joins Full Outer Join A full outer join retrieves rows that contain data from only the left table (the first one listed in the SELECT statement) or only the right table (the second one listed in the SELECT statement), as well as rows containing data from both tables. All UniData outer joins must be equi-joins (based on an equal condition). select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB full outer join TABLE_CD on TABLE_AB.attrib_num = TABLE_CD.attrib_num; Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------1 1 1 1 3 10 2 2 3 records listed

Joining Tables on Attributes of the Same Name This example performs the same select and join as the preceding statement because the following two clauses do the same thing — fieldname (attribute name) must be the same in both tables: on TABLE_AB.attrib_num = TABLE_CD.attrib_num table1.fieldname = table2.fieldname using(attrib_num) USING(fieldname) select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB full outer join TABLE_CD using(attrib_num); Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------1 1 1 1 3 10 2 2 3 records listed

B-3 Using UniData SQL

ON

Full Outer Join Combined with WHERE Condition You can further limit data selected from the result of a join by specifying a search condition in a WHERE clause: select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB full outer join TABLE_CD on TABLE_AB.attrib_num = TABLE_CD.attrib_num where TABLE_AB.@ID > 1; Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------1 1 3 10 2 2 3 records listed

B-4

Joining on Multivalued Attributes You must unnest multivalued attributes that are included in a join statement. If you do not, the result table from which records are selected may be missing rows. Here is a sample join with unnest from the demo database: select a.@ID, a.NAME, a.TAPES_RENTED, b.RENTAL_PRICE from CUSTOMER a FULL JOIN TAPES b ON unnest a.TAPES_RENTED; Page 1 CUSTOMER Customer Name Tapes ---------- --------------- ---------200 Smith, Leif 201 Wagner, Pat 202 Grundy, Robin 203 Boorman, Rick 204 Frobisher, Kamal 205 Fellini, J. Hoboken 206 LaRue, Karma 207 Doodle, Polly W. 208 Dillon, Matt 209 Byles, Marcy 25 Valenzuela, Carmen 210 Wagner, Esther 2 11 Best, George B2297 2 Partner, Bonnie B2297 2 Partner, Bonnie B914 Page 2 CUSTOMER ---------3 190 8 100 9 15 1 90 90 100 6 8 9 1 10 12

B-5 Using UniData SQL

Customer Name --------------Fischer, Carrie Belafonte, Harry Jones, Mable Jones, Samuel Chase, Carl James, Bob Smith, Harry Steven Spender Steven Spender Jones, Samuel Jones, Bob Jones, Mable Chase, Carl Smith, Harry Faber, Harry Jamieson, Dale

a.TAPE_COST, b.@ID, a.TAPES_RENTED = b.@ID

Rental C TAPES Retail C -------- ---------- --------

$2.50 B2297 $2.50 B2297 $1.06 B914 V1077

2.50 2.50 1.06 4.50

Tapes Rental C TAPES Retail C ---------- -------- ---------- -------V110 $2.52 V110 2.52 V1234 V1249 V1254 V1254 V2001 V2001 V2123 V2232 V2343 V4341 V4499 V4951 V5004 V5151 V6670

$3.54 $3.15 $3.15 $3.75 $3.75

V1249 V1254 V1254 V2001 V2001

3.54 3.15 3.15 3.75 3.75

$4.54 $4.54 $2.50 $4.40 $3.54 $6.75

V4341 V4499 V4951 V5004 V5151 V6670

4.54 4.54 2.50 4.40 3.54 6.75

1 100 Page 3 CUSTOMER ---------9 7 14 5 4

V7456 $5.50 V8181 $6.75 V8481

Smith, Harry Jones, Samuel

V8181 V8481

Customer Name --------------Chase, Carl Jones, Freddie Darrell Corden Barrie, Dick Bowie, David

Tapes Rental C ---------- -------V8481 $6.75 V9431 $4.50 V9834 V996 $4.54 V9961 $5.05

4.04 5.50 6.75

TAPES Retail C ---------- -------V8481 6.75 V9431 4.50 V996 V9961 v1231 v4637

4.54 5.05 3.79 3.03

42 records listed

Left Outer Join A left outer join retrieves only rows that contain data from the left table (the first one listed), or both tables included in the statement. You create a left outer join by including the LEFT keyword in the JOIN clause: select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB left outer join TABLE_CD on TABLE_AB.attrib_num = TABLE_CD.attrib_num; Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------1 1 1 1 2 2 2 records listed

Left Outer Join Combined with WHERE Condition You can further limit data selected from the result of a join by specifying a search condition in a WHERE clause. This search condition selects values in attribute attrib_num from the 02 table that are equal to 3: select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB left outer join TABLE_CD on TABLE_AB.attrib_num = TABLE_CD.attrib_num where TABLE_AB.attrib_num = 2; Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------2 2 1 records listed

B-6

Right Outer Join A right outer join retrieves only rows that contain data from the right table (the last one listed), or both tables included in the statement. You create a right outer join by including the RIGHT keyword in the JOIN clause: select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB right outer join TABLE_CD on TABLE_AB.attrib_num = TABLE_CD.attrib_num; Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------1 1 1 1 3 10 2 records listed

Right Outer Join Combined with WHERE Condition You can further limit data selected from the result of a join by specifying a search condition in a WHERE clause. This search condition selects values in attribute attrib_num from the 02 table that are not equal to 3: select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB right outer join TABLE_CD on TABLE_AB.attrib_num = TABLE_CD.attrib_num where TABLE_CD.attrib_num != 3; Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------1 1 1 1 3 10 2 records listed

B-7 Using UniData SQL

Joining Three Tables You cannot join more than two tables in a single join, but you can join the result of a join of two tables with a third table. You do this by stacking JOIN clauses. Notice that rows selected are further limited by the inclusion of a search condition in the WHERE clause: select TABLE_AB.@ID, TABLE_AB.attrib_num, t2.@ID, t2.attrib_num, t3.@ID, t3.attrib_num from TABLE_AB full outer join TABLE_CD t2 on TABLE_AB.attrib_num = t2.attrib_num full outer join TABLE_CD t3 on TABLE_AB.@ID = t3.@ID where t3.@ID > 1; Page 1 TABLE_AB attrib_num TABLE_CD attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------- ---------- ---------3 10 1 1 1 1 2 2 3 10 4 records listed select TABLE_AB.@ID, TABLE_AB.attrib_num, t2.@ID, t2.attrib_num, t3.@ID, t3.attrib_num from TABLE_AB full outer join TABLE_CD t2 using (attrib_num) full outer join TABLE_CD t3 using (@ID) where t3.@ID > 1; Page 1 TABLE_AB attrib_num TABLE_CD attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------- ---------- ---------2 2 1 1 1 1 3 10 3 10 3 records listed

Joining Tables in a Subquery You can include a join in a subquery: select * from TABLE_AB where attrib_num = (select TABLE_CD.attrib_num from TABLE_AB full outer join TABLE_CD on TABLE_CD.attrib_num = TABLE_AB.attrib_num); Page 1 TABLE_AB attrib_num attrib_char ---------- ---------- ---------1 1 aaa 1 records listed

B-8

The following syntax produces the same results: select * from TABLE_AB where attrib_num = (select TABLE_CD.attrib_num from TABLE_AB full outer join TABLE_CD using (attrib_num));

Sorting Join Results You can combine a join with the ORDER BY clause to sort the results from the JOIN: select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB full outer join TABLE_CD on TABLE_AB.attrib_num = TABLE_CD.attrib_num order by TABLE_AB.attrib_num; Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------3 10 1 1 1 1 2 2 3 records listed

The following syntax produces the same results. The USING clause creates an equijoin on the attribute in parentheses: select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB full outer join TABLE_CD using (attrib_num) order by TABLE_CD.@ID; Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------2 2 1 1 1 1 3 10 3 records listed

Combining Results of Queries Containing Joins You can combine multiple queries, each of which join tables, subtables, or views by using the keyword UNION.

B-9 Using UniData SQL

Note: UNION combines the results of two query_spec statements into a single result set. The number of items in query_spec must be the same; data types of corresponding items must be the same. Duplicate records are discarded. select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB left outer join TABLE_CD using (@ID) union select TABLE_CD.@ID, TABLE_CD.attrib_num, TABLE_AB.@ID, TABLE_AB.attrib_num from TABLE_CD right outer join TABLE_AB using (attrib_num); Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------1 1 1 1 2 2 2 2 3 records listed

Creating a View from Joined Tables This example creates a view by joining tables. The attributes selected (TABLE_AB.@ID, TABLE_AB.attrib_num...) become the corresponding attributes in the view (v1, v2, v3...). create view view_1 (v1, v2, v3, v4) as select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB full outer join TABLE_CD on TABLE_AB.attrib_num = TABLE_CD.attrib_num; View view_1 created. select * from view_1; Page 1 v1 v2 v3 v4 ---------- ---------- ---------- ---------1 1 1 1 3 10 2 2 3 records listed

B-10

Examples of Inner Joins This first example joins tables based on data values in attribute attrib_num being equal. Notice that the inner join selects only records that contain equal data in the joined attributes: select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB join TABLE_CD on TABLE_AB.attrib_num = TABLE_CD.attrib_num; Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------1 1 1 1 1 records listed

These next two statements use different syntax to create the same inner join, which is based on values in attributes attrib_num in tables TABLE_AB and TABLE_CD being equal: select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB inner join TABLE_CD on TABLE_AB.attrib_num = TABLE_CD.attrib_num; Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------1 1 1 1 1 records listed select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB inner join TABLE_CD using(attrib_num); Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------1 1 1 1 1 records listed

B-11 Using UniData SQL

Stacking JOIN Clauses The first join in the following example creates a temporary table, t3, that is joined with TABLE_AB in the second join clause: select TABLE_AB.@ID, TABLE_AB.attrib_num, t2.@ID, t2.attrib_num, t3.@ID, t3.attrib_num from TABLE_AB inner join TABLE_CD t2 on TABLE_AB.attrib_num = t2.attrib_num inner join TABLE_CD t3 on TABLE_AB.@ID = t3.@ID; Page 1 TABLE_AB attrib_num TABLE_CD attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------- ---------- ---------1 1 1 1 1 1 1 records listed

Here, the same join is created with the USING clause: select TABLE_AB.@ID, TABLE_AB.attrib_num, t2.@ID, t2.attrib_num, t3.@ID, t3.attrib_num from TABLE_AB inner join TABLE_CD t2 using (attrib_num) inner join TABLE_CD t3 using (@ID); Page 1 TABLE_AB attrib_num TABLE_CD attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------- ---------- ---------1 1 1 1 1 1 1 records listed

Combining Inner Join Results with UNION The following two statements each join the same two tables: select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB inner join TABLE_CD using (@ID); Page 1 TABLE_AB attrib_num TABLE_CD attrib_num ---------- ---------- ---------- ---------1 1 1 1 1 records listed select TABLE_CD.@ID, TABLE_CD.attrib_num, TABLE_AB.@ID, TABLE_AB.attrib_num from TABLE_CD inner join TABLE_AB using (attrib_num); Page 1 TABLE_CD attrib_num TABLE_AB attrib_num ---------- ---------- ---------- ---------1 1 1 1 1 records listed

B-12

Creating a View from an Inner Join This statement creates a view based on a subquery that creates an inner join of tables TABLE_AB and TABLE_CD. The attributes in view_2 (v1, v2, and so on) correspond to the attributes in the select statement (TABLE_AB.@ID, TABLE_AB.attrib_num., and so on): create view view_2 (v1, v2, v3, v4) as select TABLE_AB.@ID, TABLE_AB.attrib_num, TABLE_CD.@ID, TABLE_CD.attrib_num from TABLE_AB inner join TABLE_CD on TABLE_AB.attrib_num = TABLE_CD.attrib_num; View view_2 created.

Here’s the view created by the preceding statement: select * from view_2; Page 1 v1 v2 v3 v4 ---------- ---------- ---------- ---------1 1 1 1 1 records listed

B-13 Using UniData SQL