SQL Administration for DBAs

265 Pages·2014·1.52 MB·English

by  Rocket Software

Rocket UniVerse
SQL Administration for DBAs
Version 11.2
November 2013
UNV-112-SQLA-1
Notices
Edition
Publication date: November 2013
Book number: UNV-112-SQLA-1
Product version: Rocket UniVerse V11.2
Copyright
© Rocket Software, Inc. or its affiliates 1985-2014. All Rights Reserved.
Trademarks
Rocket is a registered trademark of Rocket Software, Inc. For a list of Rocket registered trademarks
go to: www.rocketsoftware.com/about/legal. All other products or services mentioned in this
document may be covered by the trademarks, service marks, or product names of their
respective owners.
Examples
This information might contain examples of data and reports. The examples include the names of
individuals, companies, brands, and products. All of these names are fictitious and any similarity
to the names and addresses used by an actual business enterprise is entirely coincidental.
License agreement
This software and the associated documentation are proprietary and confidential to Rocket
Software, Inc., are furnished under license, and may be used and copied only in accordance with
the terms of such license.
Note: This product may contain encryption technology. Many countries prohibit or restrict the
use, import, or export of encryption technologies, and current use, import, and export
regulations should be followed when exporting this product.
Contact information
Website: www.rocketsoftware.com
Rocket Software, Inc. Headquarters
77 4th Avenue, Suite 100
Waltham, MA 02451-1468
USA
Tel: [phone]
Fax: [phone]
2
Contacting Global Technical Support
If you have current support and maintenance agreements with Rocket Software, you can access
the Rocket Customer Portal to report and track a problem, to submit an enhancement request or
question, or to find answers in the U2 Knowledgebase. The Rocket Customer Portal is the primary
method of obtaining support.
To log in to the Rocket Customer Portal, go to:
www.rocketsoftware.com/support
If you do not already have a Rocket Customer Portal account, you can request one by clicking
Need an account? on the Rocket Customer Portal login page.
Alternatively, you can contact Global Technical Support by email or by telephone:
Email: [email protected]
Telephone:
North America [phone]
United Kingdom/France [phone] or [phone]
Europe/Africa [phone]
Australia [phone] or [phone]
New Zealand +0800 505 515
3
Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta
Table of
Contents
Table of Contents
Chapter 1 Chapter 1: The UniVerse environment
Main features . . . . . . . . . . . . . . . . . . . . 1-3
The UniVerse command processor . . . . . . . . . . . 1-3
UniVerse SQL . . . . . . . . . . . . . . . . . . 1-4
Other UniVerse utilities and processors . . . . . . . . . 1-4
The operating system and UniVerse . . . . . . . . . . . . 1-5
UniVerse tables. . . . . . . . . . . . . . . . . . 1-5
The VOC file . . . . . . . . . . . . . . . . . . 1-6
UniVerse SQL statements and commands . . . . . . . . 1-7
UniVerse and SQL databases. . . . . . . . . . . . . . . 1-8
Database concepts and structures . . . . . . . . . . . 1-8
Data models. . . . . . . . . . . . . . . . . . . 1-8
UniVerse tables and files . . . . . . . . . . . . . . 1-9
Using UniVerse help . . . . . . . . . . . . . . . . . 1-15
UniVerse online library . . . . . . . . . . . . . . . 1-15
Windows help . . . . . . . . . . . . . . . . . . 1-15
UniVerse command line help . . . . . . . . . . . . . 1-16
Review of terms . . . . . . . . . . . . . . . . . . . 1-17
The sample database . . . . . . . . . . . . . . . . . 1-19
Chapter 2 Chapter 2: Users, UniVerse accounts, and schemas
User accounts. . . . . . . . . . . . . . . . . . . . 2-3
UniVerse accounts . . . . . . . . . . . . . . . . . . 2-4
Schemas. . . . . . . . . . . . . . . . . . . . . . 2-5
Schema structure . . . . . . . . . . . . . . . . . 2-5
SQL users . . . . . . . . . . . . . . . . . . . . . 2-6
Setting the SQL environment for a session . . . . . . . . . . 2-7
Chapter 3 Chapter 3: The command processor and the VOC file
The command processor . . . . . . . . . . . . . . . . 3-3
C:\Users\awaite\Documents\U2Doc\UniVerse\11.2\Source\sqldba\SqldbaTOC.fm (bookTOC.template)
March 25, 2014 10:10 am
C:\Users\awaite\Documents\U2Doc\UniVerse\11.2\Source\sqldba\SqldbaTOC.fm
(bookTOC.template)
Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta
Special character interpretation. . . . . . . . . . . . 3-4
Called procedures . . . . . . . . . . . . . . . . 3-4
The VOC file. . . . . . . . . . . . . . . . . . . . 3-5
Some VOC entry types. . . . . . . . . . . . . . . 3-6
UniVerse sentence stack . . . . . . . . . . . . . . . . 3-9
Chapter 4 Chapter 4: Creating and dropping schemas
What is a schema? . . . . . . . . . . . . . . . . . . 4-3
The SQL catalog . . . . . . . . . . . . . . . . . 4-3
Creating a schema . . . . . . . . . . . . . . . . . . 4-5
Dropping a schema . . . . . . . . . . . . . . . . . 4-6
Chapter 5 Chapter 5: Creating, modifying, and dropping tables
Creating a table . . . . . . . . . . . . . . . . . . . 5-3
Naming a table . . . . . . . . . . . . . . . . . 5-4
Defining the file type . . . . . . . . . . . . . . . 5-5
Defining a column . . . . . . . . . . . . . . . . 5-6
Associations and multivalued columns . . . . . . . . . 5-18
Modifying a table . . . . . . . . . . . . . . . . . . 5-22
Adding columns, table constraints, and associations . . . . 5-22
Removing constraints, associations, and default values . . . 5-25
Changing a column’s default value . . . . . . . . . . 5-26
Dropping a table . . . . . . . . . . . . . . . . . . 5-27
Dropping a table with a dependent view . . . . . . . . 5-27
Dropping a referenced table . . . . . . . . . . . . . 5-27
Indexes. . . . . . . . . . . . . . . . . . . . . . 5-29
Dropping an index . . . . . . . . . . . . . . . . 5-30
Using triggers on a table. . . . . . . . . . . . . . . . 5-31
Adding a trigger . . . . . . . . . . . . . . . . . 5-31
Enabling and disabling triggers. . . . . . . . . . . . 5-31
Dropping a trigger . . . . . . . . . . . . . . . . 5-32
Listing information about a table. . . . . . . . . . . . . 5-33
Examining a table’s dictionary . . . . . . . . . . . . 5-34
Examining a table’s SICA. . . . . . . . . . . . . . 5-34
Examining a table’s SQL catalog information . . . . . . . 5-35
Modifying table dictionaries . . . . . . . . . . . . . . 5-37
Adding I-descriptors to the table definition. . . . . . . . 5-38
Changing the default set of displayed columns . . . . . . 5-39
Defining a stable unassociated multivalued column. . . . . 5-40
Loading data into a table. . . . . . . . . . . . . . . . 5-42
5 UniVerse SQL Administration for DBAs
Chapter 6 Chapter 6: UniVerse files and SQL
How file dictionaries affect SQL . . . . . . . . . . . . . 6-4
Data types of fields . . . . . . . . . . . . . . . . 6-5
Singlevalued or multivalued fields . . . . . . . . . . 6-8
Multipart record IDs . . . . . . . . . . . . . . . 6-10
Association definition . . . . . . . . . . . . . . . 6-11
Association behavior . . . . . . . . . . . . . . . 6-14
Visible fields (stored and computed) . . . . . . . . . . 6-16
Converting a UniVerse file to a table. . . . . . . . . . . . 6-21
The CONVERT.SQL command . . . . . . . . . . . . 6-21
Using CONVERT.SQL . . . . . . . . . . . . . . . 6-23
CONVERT.SQL example . . . . . . . . . . . . . . 6-27
Chapter 7 Chapter 7: Ensuring data integrity
Data integrity and UniVerse SQL. . . . . . . . . . . . . 7-3
Entity integrity . . . . . . . . . . . . . . . . . . . 7-5
Unique values and primary keys . . . . . . . . . . . 7-5
Checking for uniqueness (UNIQUE) . . . . . . . . . . 7-6
Checking for unique multivalues in each row (ROWUNIQUE) . 7-7
Semantic or domain integrity . . . . . . . . . . . . . . 7-8
Testing for NOT NULL and NOT EMPTY . . . . . . . . 7-8
Data types and domains . . . . . . . . . . . . . . 7-9
Rules (CHECK) . . . . . . . . . . . . . . . . . 7-10
Referential constraints . . . . . . . . . . . . . . . . 7-13
Referential integrity. . . . . . . . . . . . . . . . 7-14
Removing integrity constraints . . . . . . . . . . . . . 7-23
Chapter 8 Chapter 8: Maintaining database security
Controlling access to your database . . . . . . . . . . . . 8-3
Users. . . . . . . . . . . . . . . . . . . . . 8-3
Database objects . . . . . . . . . . . . . . . . . 8-4
Privileges . . . . . . . . . . . . . . . . . . . 8-4
Granting privileges . . . . . . . . . . . . . . . . . 8-6
Granting database privileges. . . . . . . . . . . . . 8-6
Granting table privileges . . . . . . . . . . . . . . 8-8
Revoking privileges . . . . . . . . . . . . . . . . . 8-14
Revoking database privileges . . . . . . . . . . . . 8-14
Revoking table privileges. . . . . . . . . . . . . . 8-14
REVOKE and WITH GRANT OPTION . . . . . . . . . 8-15
REVOKE and overlapping GRANTs . . . . . . . . . . 8-16
6 UniVerse SQL Administration for DBAs
Chapter 9 Chapter 9: Transactions, recovery, and concurrent access
Transaction processing. . . . . . . . . . . . . . . . . 9-3
Transaction processing and UniVerse SQL . . . . . . . . 9-5
Database recovery . . . . . . . . . . . . . . . . . . 9-6
File backup . . . . . . . . . . . . . . . . . . . 9-6
Transaction logging . . . . . . . . . . . . . . . . 9-7
Media recovery. . . . . . . . . . . . . . . . . . 9-8
Warmstart recovery . . . . . . . . . . . . . . . . 9-9
Concurrent access . . . . . . . . . . . . . . . . . . 9-10
Locks . . . . . . . . . . . . . . . . . . . . . 9-11
Isolation levels . . . . . . . . . . . . . . . . . . 9-13
Chapter 10 Chapter 10: Transferring tables across schemas
Preparing to export SQL tables . . . . . . . . . . . . . . 10-4
Conversion file formats . . . . . . . . . . . . . . . 10-5
Physically transferring exported SQL tables . . . . . . . . . 10-7
Resolving conflicts in the new schema . . . . . . . . . . . 10-8
Importing transferred SQL tables . . . . . . . . . . . . . 10-9
Errors in importing . . . . . . . . . . . . . . . . 10-9
Deleting exported tables from the old schema. . . . . . . . . 10-10
Chapter 11 Chapter 11: Creating an XML document with UniVerse SQL
XML for UniVerse . . . . . . . . . . . . . . . . . . 11-2
Document type definitions. . . . . . . . . . . . . . 11-2
The Document Object Model (DOM) . . . . . . . . . . 11-3
Well-formed and valid XML documents . . . . . . . . . 11-3
Creating an XML document from RetrieVe . . . . . . . . . . 11-4
Create the &XML& file . . . . . . . . . . . . . . . 11-4
Mapping modes . . . . . . . . . . . . . . . . . 11-4
Creating a mapping file . . . . . . . . . . . . . . . 11-9
How data is mapped . . . . . . . . . . . . . . . . 11-14
Mapping example. . . . . . . . . . . . . . . . . 11-15
Creating an XML document . . . . . . . . . . . . . 11-16
Examples . . . . . . . . . . . . . . . . . . . . 11-17
Creating an XML document with UniVerse SQL . . . . . . . . 11-28
Create the &XML& file . . . . . . . . . . . . . . . 11-28
Processing rules for UniVerse SQL SELECT statements . . . . 11-30
XML limitations in UniVerse SQL . . . . . . . . . . . 11-31
Examples . . . . . . . . . . . . . . . . . . . . 11-31
Table of Contents 7
Chapter 12 Chapter 12: Receiving an XML document with UniVerse SQL
Receiving an XML document through UniVerse BASIC . . . . . 12-3
Defining extraction rules . . . . . . . . . . . . . . 12-3
Defining the XPath . . . . . . . . . . . . . . . . 12-5
Extracting XML data through UniVerse BASIC . . . . . . 12-13
Displaying an XML document through RetrieVe . . . . . . 12-18
Displaying an XML document through UniVerse SQL . . . . 12-22
8 UniVerse SQL Administration for DBAs
1Administering UniData on Windows NT or Windows 2000
0
Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta
Chapter
Chapter 1: The UniVerse 1
environment
Main features . . . . . . . . . . . . . . . . . . . 1-3
The UniVerse command processor. . . . . . . . . . . 1-3
UniVerse SQL . . . . . . . . . . . . . . . . . . 1-4
Other UniVerse utilities and processors . . . . . . . . . 1-4
The operating system and UniVerse. . . . . . . . . . . . 1-5
UniVerse tables . . . . . . . . . . . . . . . . . 1-5
The VOC file . . . . . . . . . . . . . . . . . . 1-6
UniVerse SQL statements and commands . . . . . . . . 1-7
UniVerse and SQL databases . . . . . . . . . . . . . . 1-8
Database concepts and structures . . . . . . . . . . . 1-8
Data models . . . . . . . . . . . . . . . . . . 1-8
UniVerse tables and files . . . . . . . . . . . . . . 1-9
Using UniVerse help . . . . . . . . . . . . . . . . . 1-15
UniVerse online library. . . . . . . . . . . . . . . 1-15
Windows help. . . . . . . . . . . . . . . . . . 1-15
UniVerse command line help . . . . . . . . . . . . 1-16
Review of terms . . . . . . . . . . . . . . . . . . 1-17
The sample database . . . . . . . . . . . . . . . . . 1-19
C:\Users\awaite\Documents\U2Doc\UniVerse\11.2\Source\sqldba\Ch1TOC.fm
March 25, 2014 10:10 am Administering UniData on Windows NT or Windows 2000
C:\Users\awaite\Documents\U2Doc\UniVerse\11.2\Source\sqldba\Ch1.fm
3/25/14
This chapter introduces the main components that make up the UniVerse
system and reviews different kinds of UniVerse databases.
Developers who prefer SQL can use UniVerse SQL to define, query, and
control the data in the database (UniVerse files as well as tables) without
changing existing UniVerse applications. UniVerse SQL conforms to the
ANSI/ISO 1992 standard established for SQL (Entry SQL plus extensions),
enhanced to take advantage of the extended relational database structure of
UniVerse and seamlessly integrated into the UniVerse environment. It is both
a database language and a set of capabilities.
Because this book is written for SQL database administrators (DBAs), it uses
SQL terminology to discuss many UniVerse concepts. UniVerse SQL User
Guide and UniVerse SQL Reference both contain information useful for
database administrators.
UniVerse tables and files are similar in many ways. Both contain a matrix of
data and typically comprise a data file and a file dictionary. An SQL schema
is roughly equivalent to a UniVerse account.
1-2 UniVerse SQL Administration for DBAs