HP Neoview SQL Reference Manual

HP Part Number: 544582–001 Published: August 2007 Edition: HP Neoview Release 2.2

Legal Notice

Confidential computersoftware. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial

Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under

vendor’s standard commercial license.

The informationcontained hereinis subject to change without notice. Theonly warranties forHP productsand services are set forth in the express

warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. HP

shall not be liable for technical or editorial errors or omissions contained herein.

Export of the information contained in this publication may require authorization from the U.S. Department of Commerce.

Microsoft, Windows, and Windows NT are U.S. registered trademarks of Microsoft Corporation.

Intel, Pentium, and Celeron are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other

countries.

Java is a U.S. trademark of Sun Microsystems, Inc.

Motif, OSF/1, UNIX, X/Open, and the "X" device are registered trademarks, and IT DialTone and The Open Group are trademarks of The Open

Group in the U.S. and other countries.

Open Software Foundation, OSF, the OSF logo, OSF/1, OSF/Motif, and Motif are trademarks of the Open Software Foundation, Inc. OSF MAKES

NO WARRANTY OF ANY KIND WITH REGARD TO THE OSF MATERIAL PROVIDED HEREIN, INCLUDING, BUT NOT LIMITED TO, THE

IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESSFOR A PARTICULAR PURPOSE. OSF shall not be liable for errors contained

herein or for incidental consequential damages in connection with the furnishing, performance, or use of this material.

and documentationare basedin parton the Fourth Berkeley SoftwareDistribution underlicense from The Regents of the Universityof California.

OSF acknowledgesthe followingindividuals and institutions for their role in its development:Kenneth C.R.C. Arnold, Gregory S. Couch, Conrad

of California.

Table of Contents

About This Document.......................................................................................................23

Supported Release Version Updates (RVUs)........................................................................................23

Intended Audience................................................................................................................................23

New and Changed Information in This Edition...................................................................................23

Document Organization.......................................................................................................................24

Notation Conventions...........................................................................................................................24

General Syntax Notation.................................................................................................................24

Related Documentation........................................................................................................................26

Neoview Customer Library.............................................................................................................26

Publishing History................................................................................................................................27

HP Encourages Your Comments..........................................................................................................27

1 Introduction...................................................................................................................29

SQL Language.......................................................................................................................................29

Security.................................................................................................................................................29

User IDs and Role............................................................................................................................29

Authorization ID.............................................................................................................................29

Data Consistency and Access Options..................................................................................................30

READ UNCOMMITTED ................................................................................................................30

READ COMMITTED ......................................................................................................................30

SERIALIZABLE or REPEATABLE READ.......................................................................................31

SKIP CONFLICT..............................................................................................................................31

Database Integrity and Locking............................................................................................................31

Lock Duration..................................................................................................................................31

Lock Granularity..............................................................................................................................31

Lock Mode.......................................................................................................................................31

Lock Holder.....................................................................................................................................32

Transaction Management......................................................................................................................32

Statement Atomicity........................................................................................................................32

User-Defined and System-Defined Transactions............................................................................33

User-Defined Transactions.........................................................................................................33

System-Defined Transactions.....................................................................................................33

Rules for DML Statements...............................................................................................................33

Effect of AUTOCOMMIT Option....................................................................................................33

Concurrency....................................................................................................................................33

Transaction Access Modes...............................................................................................................35

READ ONLY...............................................................................................................................35

READ WRITE.............................................................................................................................36

Transaction Isolation Levels............................................................................................................36

READ UNCOMMITTED............................................................................................................36

READ COMMITTED..................................................................................................................36

SERIALIZABLE or REPEATABLE READ .................................................................................36

Transaction Rollback Mode.............................................................................................................37

ANSI Compliance and Neoview SQL Extensions................................................................................37

ANSI-Compliant Statements...........................................................................................................37

Statements That Are Neoview SQL Extensions..............................................................................38

ANSI-Compliant Functions.............................................................................................................38

Neoview SQL Error Messages..............................................................................................................39

Table of Contents 3

2 SQL Statements.............................................................................................................41

Categories.............................................................................................................................................41

Data Definition Language (DDL) Statements.................................................................................41

Data Manipulation Language (DML) Statements...........................................................................42

Transaction Control Statements.......................................................................................................42

Resource Control and Optimization Statements.............................................................................42

Control Statements..........................................................................................................................43

Object Naming Statements..............................................................................................................43

Trigger Statements...........................................................................................................................43

Stored Procedure Statements...........................................................................................................43

ALTER MATERIALIZED VIEW Statement..........................................................................................44

Syntax Description of ALTER MATERIALIZED VIEW..................................................................44

Considerations for ALTER MATERIALIZED VIEW.......................................................................44

ALTER MVGROUP Statement..............................................................................................................45

Considerations for ALTER MVGROUP..........................................................................................45

Example of ALTER MVGROUP......................................................................................................45

ALTER SYNONYM Statement..............................................................................................................46

Syntax Description of ALTER SYNONYM .....................................................................................46

Considerations.................................................................................................................................46

Versioning Considerations..............................................................................................................46

Example of ALTER SYNONYM......................................................................................................46

ALTER TABLE Statement.....................................................................................................................47

Syntax Description of ALTER TABLE.............................................................................................47

Considerations for ALTER TABLE..................................................................................................49

Effect of Adding a Column on View Definitions.......................................................................49

Authorization and Availability Requirements...........................................................................49

Example of ALTER TABLE..............................................................................................................49

ALTER TRIGGER Statement.................................................................................................................50

Syntax Description of ALTER TRIGGER.........................................................................................50

Considerations for ALTER TRIGGER.............................................................................................50

Authorization and Availability Requirements...........................................................................50

ALTER VIEW Statement.......................................................................................................................51

Syntax Description of ALTER VIEW...............................................................................................51

Example of ALTER VIEW................................................................................................................51

BEGIN WORK Statement.....................................................................................................................52

Examples of BEGIN WORK............................................................................................................52

CALL Statement....................................................................................................................................53

Syntax Description of CALL............................................................................................................53

Considerations for CALL................................................................................................................53

Usage Restrictions......................................................................................................................53

Required Privileges....................................................................................................................53

Input Parameter Arguments......................................................................................................54

Output Parameter Arguments...................................................................................................54

Data Conversion of Parameter Arguments................................................................................54

Null Input and Output...............................................................................................................54

Transaction Semantics................................................................................................................54

Examples of CALL...........................................................................................................................54

COMMIT WORK Statement.................................................................................................................56

Considerations for COMMIT WORK..............................................................................................56

Begin and End a Transaction......................................................................................................56

Effect of Constraints...................................................................................................................56

Example of COMMIT WORK..........................................................................................................56

CREATE INDEX Statement...................................................................................................................58

Syntax Description of CREATE INDEX..........................................................................................58

4 Table of Contents

Considerations for CREATE INDEX...............................................................................................59

Authorization and Availability Requirements...........................................................................59

Limits on Indexes.......................................................................................................................59

Example of CREATE INDEX...........................................................................................................59

CREATE MATERIALIZED VIEW Statement........................................................................................60

Syntax Description of CREATE MATERIALIZED VIEW................................................................60

Considerations for CREATE MATERIALIZED VIEW....................................................................62

Types of Materialized Views...........................................................................................................62

Authorization..................................................................................................................................62

Materialized Views Clustering........................................................................................................63

Materialized Views Using Other Materialized Views.....................................................................63

Single Delta Materialized Views.....................................................................................................63

DML Operations on Materialized Views........................................................................................63

Indexes and Materialized Views.....................................................................................................63

Joins.................................................................................................................................................64

Restrictions for CREATE MATERIALIZED VIEW..........................................................................64

MJV (Materialized Join View)....................................................................................................64

MAV (Materialized Aggregate View) on a Single Table............................................................64

DML Operations on Materialized Views...................................................................................64

Example of CREATE MATERIALIZED VIEW................................................................................64

CREATE MVGROUP Statement...........................................................................................................66

Example of CREATE MVGROUP....................................................................................................66

CREATE SCHEMA Statement..............................................................................................................67

Syntax Description of CREATE SCHEMA......................................................................................67

Considerations for CREATE SCHEMA...........................................................................................67

Reserved Schema Names...........................................................................................................67

Example of CREATE SCHEMA.......................................................................................................67

CREATE SYNONYM Statement...........................................................................................................68

Syntax Description of CREATE SYNONYM ..................................................................................68

Considerations.................................................................................................................................68

Versioning Considerations..............................................................................................................68

Example of CREATE SYNONYM....................................................................................................68

CREATE TABLE Statement...................................................................................................................69

Syntax Description of CREATE TABLE...........................................................................................71

Considerations for CREATE TABLE................................................................................................73

Considerations for CREATE VOLATILE TABLE............................................................................74

Considerations for CREATE TABLE LIKE......................................................................................74

Reserved Table Names.....................................................................................................................74

Authorization and Availability Requirements................................................................................74

Limits for Tables..............................................................................................................................74

Calculating Row Size.......................................................................................................................75

Creating Partitions Automatically...................................................................................................75

Record Format.................................................................................................................................75

Generating Unique Values For a Column.......................................................................................75

Neoview SQL Extensions to CREATE TABLE................................................................................77

DISK POOL......................................................................................................................................77

Considerations for DISK POOL.................................................................................................77

Restrictions for DISK POOL.......................................................................................................78

Examples of CREATE TABLE..........................................................................................................78

Examples of CREATE TABLE AS....................................................................................................79

CREATE TRIGGER Statement..............................................................................................................81

Syntax Description of CREATE TRIGGER......................................................................................81

Considerations for CREATE TRIGGER...........................................................................................82

Authorization and Availability Requirements...........................................................................82

Trigger Types..............................................................................................................................82

Table of Contents 5

Restrictions on Triggers..............................................................................................................84

Recompilation and Triggers.......................................................................................................84

Triggers and Primary Keys........................................................................................................84

Examples of CREATE TRIGGER.....................................................................................................85

Before and After Triggers...........................................................................................................85

CREATE VIEW Statement.....................................................................................................................87

Syntax Description of CREATE VIEW.............................................................................................87

Considerations for CREATE VIEW.................................................................................................88

Reserved View Names...............................................................................................................88

Effect of Adding a Column on View Definitions.......................................................................88

Authorization and Availability Requirements...........................................................................88

Updatable and Non-Updatable Views.......................................................................................88

Examples of CREATE VIEW............................................................................................................89

DELETE Statement................................................................................................................................90

Syntax Description of DELETE........................................................................................................90

Considerations for DELETE............................................................................................................92

Authorization Requirements......................................................................................................92

Transaction Initiation and Termination......................................................................................92

Isolation Levels of Transactions and Access Options of Statements..........................................92

SET ON ROLLBACK Considerations........................................................................................92

SET ON ROLLBACK Restrictions..............................................................................................92

Examples of DELETE.......................................................................................................................92

DROP INDEX Statement.......................................................................................................................93

Syntax Description of DROP INDEX..............................................................................................93

Considerations for DROP INDEX...................................................................................................93

Authorization and Availability Requirements...........................................................................93

Indexes That Support Constraints.............................................................................................93

Example of DROP INDEX...............................................................................................................93

DROP MATERIALIZED VIEW Statement............................................................................................94

Syntax Description of DROP MATERIALIZED VIEW....................................................................94

Example of DROP MATERIALIZED VIEW....................................................................................94

DROP MVGROUP Statement...............................................................................................................95

Considerations for DROP MVGROUP............................................................................................95

Example of DROP MVGROUP........................................................................................................95

DROP SCHEMA Statement..................................................................................................................96

Syntax Description of DROP SCHEMA..........................................................................................96

Considerations for DROP SCHEMA...............................................................................................96

Authorization and Availability Requirements...........................................................................96

Example of DROP SCHEMA...........................................................................................................96

DROP SYNONYM Statement...............................................................................................................97

Syntax Description of DROP SYNONYM ......................................................................................97

Considerations.................................................................................................................................97

Versioning Considerations..............................................................................................................97

Example of DROP SYNONYM........................................................................................................97

DROP TABLE Statement.......................................................................................................................98

Syntax Description of DROP TABLE...............................................................................................98

Considerations for DROP TABLE...................................................................................................98

Authorization and Availability Requirements...........................................................................98

Example of DROP TABLE...............................................................................................................98

DROP TRIGGER Statement..................................................................................................................99

Syntax Description of DROP TRIGGER..........................................................................................99

Considerations for DROP TRIGGER...............................................................................................99

Authorization and Availability Requirements...........................................................................99

Examples of DROP TRIGGER.........................................................................................................99

DROP VIEW Statement.......................................................................................................................100

6 Table of Contents

Syntax Description of DROP VIEW...............................................................................................100

Considerations for DROP VIEW...................................................................................................100

Authorization and Availability Requirements.........................................................................100

Example of DROP VIEW...............................................................................................................100

EXECUTE Statement...........................................................................................................................101

Syntax Description of EXECUTE...................................................................................................101

Considerations for EXECUTE........................................................................................................102

Scope of EXECUTE...................................................................................................................102

Examples of EXECUTE..................................................................................................................102

EXPLAIN Statement............................................................................................................................104

Syntax.............................................................................................................................................104

Considerations...............................................................................................................................105

Case Considerations.................................................................................................................105

Number Considerations...........................................................................................................105

Formatted [OPTIONS 'f'] Considerations................................................................................105

Normal User [OPTIONS 'n'] Considerations...........................................................................106

Expert User [OPTIONS 'e'] Considerations..............................................................................108

Machine-Readable [OPTIONS 'm'] Considerations.................................................................110

Example of EXPLAIN Statement Using ODBC........................................................................112

Example of EXPLAIN Statement Using JDBC.........................................................................113

GRANT Statement..............................................................................................................................115

Syntax Description of GRANT......................................................................................................115

Considerations for GRANT...........................................................................................................116

Authorization and Availability Requirements.........................................................................116

Privileges on Views..................................................................................................................116

Examples of GRANT.....................................................................................................................116

GRANT EXECUTE Statement.............................................................................................................117

Syntax Description of GRANT EXECUTE.....................................................................................117

Considerations for GRANT EXECUTE.........................................................................................117

Authorization and Availability Requirements.........................................................................117

Examples of GRANT EXECUTE....................................................................................................118

GRANT SCHEMA Statement.............................................................................................................119

Syntax Description of GRANT SCHEMA.....................................................................................119

Considerations for GRANT SCHEMA..........................................................................................120

Authorization and Availability Requirements.........................................................................120

Schema Privileges and Synonyms............................................................................................120

Examples of GRANT SCHEMA....................................................................................................120

INSERT Statement...............................................................................................................................121

Syntax Description of INSERT.......................................................................................................121

Considerations for INSERT...........................................................................................................123

Authorization Requirements....................................................................................................123

Transaction Initiation and Termination....................................................................................123

Self-Referencing INSERT and BEGIN WORK or AUTOCOMMIT OFF..................................123

Isolation Levels of Transactions and Access Options of Statements........................................124

Use of a VALUES Clause for the Source Query Expression....................................................124

Requirements for Inserted Rows..............................................................................................124

Using Compatible Data Types..................................................................................................124

Examples of INSERT......................................................................................................................125

LOCK TABLE Statement.....................................................................................................................127

Syntax Description of LOCK TABLE.............................................................................................127

Considerations for LOCK TABLE..................................................................................................127

Authorization Requirements....................................................................................................127

Modifying Default Locking......................................................................................................127

Effect of AUTOCOMMIT Option.............................................................................................127

Indexes......................................................................................................................................128

Table of Contents 7

Examples of LOCK TABLE............................................................................................................128

MERGE INTO Statement....................................................................................................................129

Syntax Description of MERGE INTO............................................................................................129

Considerations for MERGE INTO.................................................................................................129

Upsert Using Single Row.........................................................................................................129

Restrictions..........................................................................................................................130

Upsert Using Rowsets..............................................................................................................130

Example...............................................................................................................................130

MERGE From One Table Into Another....................................................................................130

Example...............................................................................................................................131

Reflexive Updates...............................................................................................................131

PREPARE Statement...........................................................................................................................132

Syntax Description of PREPARE...................................................................................................132

Considerations for PREPARE........................................................................................................132

Availability of a Prepared Statement........................................................................................132

Examples of PREPARE..................................................................................................................132

REVOKE Statement.............................................................................................................................134

Syntax Description of REVOKE.....................................................................................................134

Considerations for REVOKE.........................................................................................................135

Authorization and Availability Requirements.........................................................................135

Examples of REVOKE....................................................................................................................135

REVOKE EXECUTE Statement...........................................................................................................136

Syntax Description of REVOKE EXECUTE...................................................................................136

Considerations for REVOKE EXECUTE........................................................................................137

Authorization and Availability Requirements.........................................................................137

Examples of REVOKE EXECUTE..................................................................................................137

REVOKE SCHEMA Statement............................................................................................................138

Syntax Description of REVOKE SCHEMA....................................................................................138

Considerations for REVOKE SCHEMA........................................................................................139

Authorization and Availability Requirements.........................................................................139

Examples of REVOKE SCHEMA...................................................................................................139

ROLLBACK WORK Statement...........................................................................................................140

Considerations for ROLLBACK WORK........................................................................................140

Begin and End a Transaction....................................................................................................140

Examples of ROLLBACK WORK..................................................................................................140

SELECT Statement..............................................................................................................................141

Syntax Description of SELECT......................................................................................................144

Considerations for SELECT...........................................................................................................151

Authorization Requirements....................................................................................................151

Locking Modes.........................................................................................................................151

Use of Views With SELECT......................................................................................................151

Join Limits.................................................................................................................................152

Object Names in SELECT.........................................................................................................152

AS and ORDER BY Conflicts....................................................................................................152

Stream Access Restrictions.......................................................................................................152

Restrictions on Embedded Inserts............................................................................................152

DISTINCT Aggregate Functions..............................................................................................152

Limitations of DISTINCT Aggregates......................................................................................153

Examples of Multiple Distinct Aggregates..............................................................................153

Considerations for Select List........................................................................................................153

Considerations for SEQUENCE BY...............................................................................................153

Considerations for GROUP BY......................................................................................................153

Considerations for SORT BY.........................................................................................................154

Considerations for ORDER BY......................................................................................................154

Considerations for DISTINCT.......................................................................................................154

8 Table of Contents

Considerations for UNION...........................................................................................................154

Characteristics of the UNION Columns..................................................................................154

ORDER BY Clause and the UNION Operator.........................................................................155

GROUP BY Clause, HAVING Clause, and the UNION Operator ..........................................155

UNION ALL and Associativity................................................................................................155

Access Modes and the UNION Operator.................................................................................155

Examples of SELECT.....................................................................................................................156

Examples for Embedded INSERT..................................................................................................161

SET SCHEMA Statement....................................................................................................................163

Syntax Description of SET SCHEMA............................................................................................163

Consideration for SET SCHEMA...................................................................................................163

Examples of SET SCHEMA...........................................................................................................163

SET TABLE TIMEOUT Statement.......................................................................................................164

Syntax Description of SET TABLE TIMEOUT...............................................................................164

Considerations for SET TABLE TIMEOUT....................................................................................165

Examples of SET TABLE TIMEOUT..............................................................................................165

SET TRANSACTION Statement.........................................................................................................166

Syntax Description of SET TRANSACTION.................................................................................166

Considerations for SET TRANSACTION......................................................................................167

Implicit Transactions................................................................................................................167

Explicit Transactions.................................................................................................................168

Degree of Concurrency.............................................................................................................168

Effect on Utilities......................................................................................................................168

Example of SET TRANSACTION..................................................................................................168

UPDATE Statement.............................................................................................................................169

Syntax Description of UPDATE.....................................................................................................169

Considerations for UPDATE..........................................................................................................171

Authorization Requirements....................................................................................................171

Transaction Initiation and Termination....................................................................................171

Isolation Levels of Transactions and Access Options of Statements........................................171

Conflicting Updates in Concurrent Applications....................................................................171

Requirements for Data in Row.................................................................................................172

Reporting of Updates...............................................................................................................172

Updating Character Values......................................................................................................172

SET Clause Restrictions and Error Cases.................................................................................172

SET ON ROLLBACK Considerations......................................................................................173

SET ON ROLLBACK Restrictions............................................................................................173

Examples of UPDATE....................................................................................................................173

UPDATE STATISTICS Statement........................................................................................................175

Syntax Description of UPDATE STATISTICS................................................................................175

Considerations for UPDATE STATISTICS.....................................................................................177

Physical Statistics......................................................................................................................177

Using Statistics.........................................................................................................................177

Authorization and Locking......................................................................................................177

Transactions..............................................................................................................................177

Generating and Clearing Statistics for Columns......................................................................178

Column Lists and Access Plans................................................................................................178

Examples of UPDATE STATISTICS...............................................................................................178

3 SQL Utilities................................................................................................................181

Privileges Required to Execute Utilities .............................................................................................181

MAINTAIN Command.......................................................................................................................182

Syntax Description of MAINTAIN................................................................................................182

........................................................................................................................................................182

Table of Contents 9

MAINTAIN MVGROUP................................................................................................................184

Examples of MAINTAIN...............................................................................................................184

POPULATE INDEX Utility.................................................................................................................186

Syntax Description of POPULATE INDEX....................................................................................186

Considerations for POPULATE INDEX........................................................................................186

Examples of POPULATE INDEX...................................................................................................186

PURGEDATA Utility...........................................................................................................................187

Syntax Description of PURGEDATA.............................................................................................187

Considerations for PURGEDATA..................................................................................................187

Examples of PURGEDATA............................................................................................................187

4 SQL Language Elements...........................................................................................189

Character Sets......................................................................................................................................190

Columns..............................................................................................................................................191

Column References........................................................................................................................191

Derived Column Names................................................................................................................191

Column Default Settings................................................................................................................191

Examples of Derived Column Names...........................................................................................191

Constraints..........................................................................................................................................193

Creating, Adding, and Dropping Constraints on SQL Tables.......................................................193

Constraint Names.....................................................................................................................193

Correlation Names..............................................................................................................................194

Explicit Correlation Names...........................................................................................................194

Implicit Correlation Names...........................................................................................................194

Examples of Correlation Names....................................................................................................194

Database Objects.................................................................................................................................195

Ownership.....................................................................................................................................195

Database Object Names......................................................................................................................196

Logical Names for SQL Objects.....................................................................................................196

SQL Object Namespaces................................................................................................................196

Data Types...........................................................................................................................................197

Comparable and Compatible Data Types.....................................................................................199

Character Data Types...............................................................................................................199

Datetime Data Types................................................................................................................199

Interval Data Types...................................................................................................................200

Numeric Data Types.................................................................................................................200

Character String Data Types..........................................................................................................200

Considerations for Character String Data Types.....................................................................201

Datetime Data Types......................................................................................................................202

Considerations for Datetime Data Types.................................................................................202

Interval Data Types.............................................................................................................................204

Considerations for Interval Data Types.........................................................................................204

Numeric Data Types ...........................................................................................................................205

Expressions..........................................................................................................................................208

Character Value Expressions.........................................................................................................208

Examples of Character Value Expressions...............................................................................208

Datetime Value Expressions..........................................................................................................210

Considerations for Datetime Value Expressions......................................................................211

Examples of Datetime Value Expressions................................................................................211

Interval Value Expressions............................................................................................................213

Considerations for Interval Value Expressions........................................................................214

Examples of Interval Value Expressions..................................................................................215

Numeric Value Expressions...........................................................................................................217

Considerations for Numeric Value Expressions......................................................................217

10 Table of Contents

Examples of Numeric Value Expressions.................................................................................219

Identifiers............................................................................................................................................220

Regular Identifiers.........................................................................................................................220

Delimited Identifiers......................................................................................................................220

Examples of Identifiers..................................................................................................................220

Indexes................................................................................................................................................221

SQL Indexes...................................................................................................................................221

Keys.....................................................................................................................................................222

Clustering Keys..............................................................................................................................222

Index Keys.....................................................................................................................................222

SQL Index Keys........................................................................................................................222

Primary Keys.................................................................................................................................222

Literals.................................................................................................................................................223

Character String Literals................................................................................................................223

Considerations for Character String Literals............................................................................224

Examples of Character String Literals......................................................................................224

Datetime Literals............................................................................................................................225

Examples of Datetime Literals.................................................................................................225

Interval Literals..............................................................................................................................226

Considerations for Interval Literals.........................................................................................227

Examples of Interval Literals....................................................................................................228

Numeric Literals............................................................................................................................228

Examples of Numeric Literals..................................................................................................229

Null......................................................................................................................................................230

Using Null Versus Default Values.................................................................................................230

Defining Columns That Allow or Prohibit Null............................................................................230

Null in DISTINCT, GROUP BY, and ORDER BY Clauses........................................................230

Null and Expression Evaluation Comparison .........................................................................231

SQL Tables.....................................................................................................................................232

Automatically Creating Partitions............................................................................................232

Predicates............................................................................................................................................233

BETWEEN Predicate......................................................................................................................233

Considerations for BETWEEN.................................................................................................234

Examples of BETWEEN............................................................................................................234

Comparison Predicates..................................................................................................................234

Considerations for Comparison Predicates..............................................................................235

Examples of Comparison Predicates........................................................................................236

EXISTS Predicate............................................................................................................................238

Examples of EXISTS.................................................................................................................238

IN Predicate...................................................................................................................................239

Considerations for IN...............................................................................................................239

Examples of IN.........................................................................................................................240

NULL Predicate.............................................................................................................................241

Considerations for NULL.........................................................................................................241

Examples of NULL...................................................................................................................242

Quantified Comparison Predicates...............................................................................................242

Considerations for ALL, ANY, SOME......................................................................................243

Examples of ALL, ANY, SOME................................................................................................243

Privileges.............................................................................................................................................245

Considerations...............................................................................................................................245

General Rules for object-privilege and schema-privilege........................................................245

Privilege Descriptions for object-privilege...............................................................................245

General Rules for column-list...................................................................................................246

Schemas...............................................................................................................................................247

Search Condition.................................................................................................................................248

Table of Contents 11

Considerations for Search Condition............................................................................................248

Order of Evaluation..................................................................................................................248

Column References...................................................................................................................248

Subqueries................................................................................................................................248

Examples of Search Condition.......................................................................................................249

Subquery.............................................................................................................................................250

SELECT Form of a Subquery.........................................................................................................250

Using Subqueries to Provide Comparison Values.........................................................................250

Nested Subqueries When Providing Comparison Values.............................................................250

Correlated Subqueries When Providing Comparison Values.......................................................251

Tables...................................................................................................................................................252

Base Tables and Views........................................................................................................................252

Example of a Base Table......................................................................................................................252

Triggers................................................................................................................................................253

Views...................................................................................................................................................254

SQL Views......................................................................................................................................254

Example of a View....................................................................................................................254

MVGROUPs ..................................................................................................................................254

5 SQL Clauses...............................................................................................................257

DEFAULT Clause................................................................................................................................258

Examples of DEFAULT..................................................................................................................259

SAMPLE Clause..................................................................................................................................260

Considerations for SAMPLE..........................................................................................................261

Sample Rows............................................................................................................................261

Cluster Sampling......................................................................................................................261

Examples of SAMPLE....................................................................................................................262

SEQUENCE BY Clause.......................................................................................................................268

Considerations for SEQUENCE BY...............................................................................................268

Examples of SEQUENCE BY.........................................................................................................269

TRANSPOSE Clause...........................................................................................................................271

Considerations for TRANSPOSE...................................................................................................272

Multiple TRANSPOSE Clauses and Sets..................................................................................272

Degree and Column Order of the TRANSPOSE Result...........................................................272

Data Type of the TRANSPOSE Result......................................................................................272

Cardinality of the TRANSPOSE Result....................................................................................273

Examples of TRANSPOSE.............................................................................................................273

6 SQL Functions and Expressions................................................................................279

Categories............................................................................................................................................279

Standard Normalization................................................................................................................279

Aggregate (Set) Functions..............................................................................................................279

Character String Functions............................................................................................................280

Datetime Functions........................................................................................................................281

Mathematical Functions................................................................................................................282

Sequence Functions.......................................................................................................................283

Other Functions and Expressions..................................................................................................285

ABS Function.......................................................................................................................................286

Example of ABS.............................................................................................................................286

ACOS Function...................................................................................................................................287

Examples of ACOS........................................................................................................................287

ADD_MONTHS Function...................................................................................................................288

Examples of ADD_MONTHS........................................................................................................288

12 Table of Contents

ASCII Function....................................................................................................................................289

Example of ASCII..........................................................................................................................289

ASIN Function.....................................................................................................................................290

Examples of ASIN..........................................................................................................................290

ATAN Function...................................................................................................................................291

Examples of ATAN........................................................................................................................291

ATAN2 Function.................................................................................................................................292

Example of ATAN2........................................................................................................................292

AVG Function......................................................................................................................................293

Considerations for AVG.................................................................................................................293

Data Type of the Result............................................................................................................293

Operands of the Expression.....................................................................................................293

Nulls.........................................................................................................................................293

Examples of AVG...........................................................................................................................293

CASE (Conditional) Expression..........................................................................................................295

Considerations for CASE...............................................................................................................296

Data Type of the CASE Expression..........................................................................................296

Character Data Type.................................................................................................................296

Numeric Data Type..................................................................................................................296

Datetime Data Type..................................................................................................................296

Interval Data Type....................................................................................................................296

Examples of CASE.........................................................................................................................296

CAST Expression.................................................................................................................................298

Considerations for CAST...............................................................................................................298

Valid Conversions for CAST .........................................................................................................298

Examples of CAST.........................................................................................................................299

CEILING Function..............................................................................................................................300

Example of CEILING.....................................................................................................................300

CHAR Function...................................................................................................................................301

Example of CHAR.........................................................................................................................301

CHAR_LENGTH Function.................................................................................................................302

Considerations for CHAR_LENGTH............................................................................................302

CHAR and VARCHAR Operands............................................................................................302

Examples of CHAR_LENGTH......................................................................................................302

COALESCE Function..........................................................................................................................303

Example of COALESCE.................................................................................................................303

CODE_VALUE Function.....................................................................................................................304

Example of CODE_VALUE Function............................................................................................304

CONCAT Function..............................................................................................................................305

Concatenation Operator (||).........................................................................................................305

Considerations for CONCAT.........................................................................................................305

Operands..................................................................................................................................305

SQL Parameters........................................................................................................................305

Examples of CONCAT...................................................................................................................305

CONVERTTIMESTAMP Function......................................................................................................307

Considerations for CONVERTTIMESTAMP.................................................................................307

Relationship to the JULIANTIMESTAMP Function................................................................307

Use of CONVERTTIMESTAMP...............................................................................................307

Examples of CONVERTTIMESTAMP...........................................................................................307

COS Function......................................................................................................................................308

Example of COS.............................................................................................................................308

COSH Function...................................................................................................................................309

Example of COSH..........................................................................................................................309

COUNT Function................................................................................................................................310

Considerations for COUNT...........................................................................................................310

Table of Contents 13

Operands of the Expression.....................................................................................................310

Nulls.........................................................................................................................................310

Examples of COUNT.....................................................................................................................310

CURRENT Function............................................................................................................................312

Example of CURRENT..................................................................................................................312

CURRENT_DATE Function................................................................................................................313

Examples of CURRENT_DATE.....................................................................................................313

CURRENT_ROLE Function................................................................................................................314

Example of CURRENT_ROLE.......................................................................................................314

CURRENT_TIME Function.................................................................................................................315

Example of CURRENT_TIME.......................................................................................................315

CURRENT_TIMESTAMP Function....................................................................................................316

Example of CURRENT_TIMESTAMP...........................................................................................316

CURRENT_USER Function................................................................................................................317

Example of CURRENT_USER.......................................................................................................317

DATE_ADD Function.........................................................................................................................318

Examples of DATE_ADD...............................................................................................................318

DATE_SUB Function...........................................................................................................................319

Examples of DATE_SUB................................................................................................................319

DATEADD Function...........................................................................................................................319

Examples of DATEADD................................................................................................................320

DATEDIFF Function............................................................................................................................321

Examples of DATEDIFF.................................................................................................................321

DATEFORMAT Function....................................................................................................................322

Examples of DATEFORMAT.........................................................................................................322

DATE_PART Function (of an interval)................................................................................................322

Examples of DATE_PART..............................................................................................................322

DATE_PART Function (of a timestamp).............................................................................................323

Examples of DATE_PART..............................................................................................................323

DATE_TRUNC Function.....................................................................................................................324

Examples of DATE_TRUNC..........................................................................................................324

DAY Function......................................................................................................................................325

Example of DAY.............................................................................................................................325

DAYNAME Function..........................................................................................................................326

Example of DAYNAME.................................................................................................................326

DAYOFMONTH Function..................................................................................................................327

Examples of DAYOFMONTH.......................................................................................................327

DAYOFWEEK Function......................................................................................................................328

Example of DAYOFWEEK.............................................................................................................328

DAYOFYEAR Function.......................................................................................................................329

Example of DAYOFYEAR..............................................................................................................329

DECODE Function..............................................................................................................................330

Considerations for DECODE.........................................................................................................330

Examples of DECODE...................................................................................................................331

DEGREES Function.............................................................................................................................333

Examples of DEGREES..................................................................................................................333

DIFF1 Function....................................................................................................................................334

Considerations for DIFF1...............................................................................................................334

Equivalent Result......................................................................................................................334

Datetime Arguments................................................................................................................334

Examples of DIFF1.........................................................................................................................334

DIFF2 Function....................................................................................................................................336

Considerations for DIFF2...............................................................................................................336

Equivalent Result......................................................................................................................336

Datetime Arguments................................................................................................................336

14 Table of Contents

Examples of DIFF2.........................................................................................................................336

EXP Function.......................................................................................................................................338

Examples of EXP............................................................................................................................338

EXPLAIN Function.............................................................................................................................339

Considerations for EXPLAIN Function.........................................................................................339

Using a Statement Pattern........................................................................................................339

Result of the EXPLAIN Function.............................................................................................339

Examples of EXPLAIN Function...................................................................................................341

EXTRACT Function.............................................................................................................................342

Examples of EXTRACT..................................................................................................................342

FLOOR Function.................................................................................................................................343

Examples of FLOOR......................................................................................................................343

HOUR Function..................................................................................................................................344

Example of HOUR.........................................................................................................................344

INSERT Function................................................................................................................................345

Examples of INSERT......................................................................................................................345

ISNULL Function................................................................................................................................346

Examples of ISNULL.....................................................................................................................346

JULIANTIMESTAMP Function...........................................................................................................347

Examples of JULIANTIMESTAMP................................................................................................347

LASTNOTNULL Function..................................................................................................................347

Example of LASTNOTNULL.........................................................................................................347

LCASE Function..................................................................................................................................349

Example of LCASE........................................................................................................................349

LEFT Function.....................................................................................................................................350

Examples of LEFT..........................................................................................................................350

LOCATE Function...............................................................................................................................351

Considerations for LOCATE..........................................................................................................351

Result of LOCATE....................................................................................................................351

Using UCASE...........................................................................................................................351

Examples of LOCATE....................................................................................................................351

LOG Function......................................................................................................................................352

Example of LOG............................................................................................................................352

LOG10 Function..................................................................................................................................353

Example of LOG10.........................................................................................................................353

LOWER Function................................................................................................................................354

Considerations for LOWER...........................................................................................................354

Example of LOWER.......................................................................................................................358

LPAD Function....................................................................................................................................359

Examples of LPAD.........................................................................................................................359

LTRIM Function..................................................................................................................................360

Considerations for LTRIM.............................................................................................................360

Result of LTRIM........................................................................................................................360

Example of LTRIM.........................................................................................................................360

MAX/MAXIMUM Function................................................................................................................361

Considerations for MAX................................................................................................................361

Operands of the Expression.....................................................................................................361

Example of MAX............................................................................................................................361

MIN Function......................................................................................................................................362

Considerations for MIN.................................................................................................................362

Operands of the Expression.....................................................................................................362

Example of MIN.............................................................................................................................362

MINUTE Function...............................................................................................................................363

Example of MINUTE.....................................................................................................................363

MOD Function....................................................................................................................................364

Table of Contents 15

Example of MOD...........................................................................................................................364

MONTH Function...............................................................................................................................365

Example of MONTH......................................................................................................................365

MONTHNAME Function...................................................................................................................366

Example of MONTHNAME..........................................................................................................366

MOVINGAVG Function......................................................................................................................367

Example of MOVINGAVG............................................................................................................367

MOVINGCOUNT Function................................................................................................................368

Considerations for MOVINGCOUNT...........................................................................................368

No DISTINCT Clause...............................................................................................................368

Example of MOVINGCOUNT.......................................................................................................368

MOVINGMAX Function.....................................................................................................................370

Example of MOVINGMAX...........................................................................................................370

MOVINGMIN Function......................................................................................................................371

Example of MOVINGMIN............................................................................................................371

MOVINGSTDDEV Function...............................................................................................................372

Example of MOVINGSTDDEV......................................................................................................372

MOVINGSUM Function.....................................................................................................................374

Example of MOVINGSUM............................................................................................................374

MOVINGVARIANCE Function..........................................................................................................375

Example of MOVINGVARIANCE.................................................................................................375

NULLIF Function................................................................................................................................377

Example of NULLIF.......................................................................................................................377

NULLIFZERO Function......................................................................................................................378

Examples of NULLIFZERO...........................................................................................................378

NVL Function......................................................................................................................................379

Examples of NVL...........................................................................................................................379

OCTET_LENGTH Function................................................................................................................380

Considerations for OCTET_LENGTH...........................................................................................380

CHAR and VARCHAR Operands............................................................................................380

Similarity to CHAR_LENGTH Function..................................................................................380

Example of OCTET_LENGTH.......................................................................................................380

OFFSET Function................................................................................................................................381

Example of OFFSET.......................................................................................................................381

PI Function..........................................................................................................................................382

Example of PI.................................................................................................................................382

POSITION Function............................................................................................................................383

Considerations for POSITION.......................................................................................................383

Result of POSITION.................................................................................................................383

Using the UPSHIFT Function...................................................................................................383

Examples of POSITION.................................................................................................................383

POWER Function................................................................................................................................384

Examples of POWER.....................................................................................................................384

QUARTER Function............................................................................................................................385

Example of QUARTER..................................................................................................................385

RADIANS Function............................................................................................................................386

Examples of RADIANS..................................................................................................................386

RANK/RUNNINGRANK Function....................................................................................................387

Considerations for RUNNINGRANK...........................................................................................387

Depends of the Sequence order................................................................................................387

NULL Values............................................................................................................................387

Examples of RUNNINGRANK.....................................................................................................387

RUNNINGSTDDEV Function............................................................................................................389

Considerations for RUNNINGSTDDEV.......................................................................................389

Equivalent Result......................................................................................................................389

16 Table of Contents

Examples of RUNNINGSTDDEV..................................................................................................389

REPEAT Function................................................................................................................................390

Example of REPEAT......................................................................................................................390

REPLACE Function.............................................................................................................................390

Example of REPLACE...................................................................................................................391

RIGHT Function..................................................................................................................................391

Examples of RIGHT.......................................................................................................................391

ROUND Function................................................................................................................................392

Examples of ROUND.....................................................................................................................392

ROWS SINCE Function.......................................................................................................................392

Considerations for ROWS SINCE..................................................................................................393

Counting the Rows...................................................................................................................393

Examples of ROWS SINCE............................................................................................................393

ROWS SINCE CHANGED Function..................................................................................................394

Considerations for ROWS SINCE CHANGED.............................................................................394

Counting the Rows...................................................................................................................394

Examples of ROWS SINCE CHANGED........................................................................................394

RPAD Function...................................................................................................................................394

RTRIM Function..................................................................................................................................395

Considerations for RTRIM.............................................................................................................395

Result of RTRIM.......................................................................................................................395

Example of RTRIM........................................................................................................................395

RUNNINGAVG Function...................................................................................................................395

Considerations for RUNNINGAVG..............................................................................................396

Equivalent Result......................................................................................................................396

Example of RUNNINGAVG..........................................................................................................396

RUNNINGCOUNT Function..............................................................................................................396

Considerations for RUNNINGCOUNT........................................................................................396

No DISTINCT Clause...............................................................................................................396

Example of RUNNINGCOUNT....................................................................................................396

RUNNINGMAX Function..................................................................................................................397

Example of RUNNINGMAX.........................................................................................................397

RUNNINGMIN Function...................................................................................................................397

Example of RUNNINGMIN..........................................................................................................397

RUNNINGRANK Function................................................................................................................399

RUNNINGSTDDEV Function............................................................................................................400

Considerations for RUNNINGSTDDEV.......................................................................................400

Equivalent Result......................................................................................................................400

Examples of RUNNINGSTDDEV..................................................................................................400

RUNNINGSUM Function...................................................................................................................401

Example of RUNNINGSUM..........................................................................................................401

RUNNINGVARIANCE Function........................................................................................................402

Examples of RUNNINGVARIANCE.............................................................................................402

SECOND Function..............................................................................................................................403

Example of SECOND.....................................................................................................................403

SIGN Function.....................................................................................................................................404

Examples of SIGN..........................................................................................................................404

SIN Function.......................................................................................................................................405

Example of SIN..............................................................................................................................405

SINH Function....................................................................................................................................406

Example of SINH...........................................................................................................................406

SPACE Function..................................................................................................................................407

Example of SPACE.........................................................................................................................407

SQRT Function....................................................................................................................................408

Example of SQRT...........................................................................................................................408

Table of Contents 17

STDDEV Function...............................................................................................................................409

Considerations for STDDEV..........................................................................................................409

Definition of STDDEV..............................................................................................................409

Data Type of the Result............................................................................................................409

Operands of the Expression.....................................................................................................409

Nulls.........................................................................................................................................409

FLOAT(54) and DOUBLE PRECISION Data............................................................................409

Examples of STDDEV....................................................................................................................410

SUBSTRING/SUBSTR Function..........................................................................................................411

Alternative Forms..........................................................................................................................411

Considerations for SUBSTRING/SUBSTR.....................................................................................411

Requirements for the Expression, Length, and Start Position.................................................411

Examples of SUBSTRING/SUBSTR...............................................................................................411

SUM Function.....................................................................................................................................413

Considerations for SUM................................................................................................................413

Data Type and Scale of the Result............................................................................................413

Operands of the Expression.....................................................................................................413

Example of SUM............................................................................................................................413

TAN Function......................................................................................................................................414

Example of TAN............................................................................................................................414

TANH Function...................................................................................................................................415

Example of TANH.........................................................................................................................415

THIS Function.....................................................................................................................................416

Considerations for THIS................................................................................................................416

Counting the Rows...................................................................................................................416

Example of THIS............................................................................................................................416

TRANSLATE Function........................................................................................................................417

TRIM Function....................................................................................................................................418

Considerations for TRIM...............................................................................................................418

Result of TRIM..........................................................................................................................418

Examples of TRIM.........................................................................................................................418

UCASE Function.................................................................................................................................419

Considerations for UCASE............................................................................................................419

Examples of UCASE......................................................................................................................425

UPPER Function..................................................................................................................................427

Example of UPPER........................................................................................................................427

UPSHIFT Function..............................................................................................................................428

Examples of UPSHIFT...................................................................................................................428

USER Function....................................................................................................................................429

Example of USER...........................................................................................................................429

VARIANCE Function..........................................................................................................................430

Considerations for VARIANCE.....................................................................................................430

Definition of VARIANCE.........................................................................................................430

Data Type of the Result............................................................................................................431

Operands of the Expression.....................................................................................................431

Nulls.........................................................................................................................................431

FLOAT(54) and DOUBLE PRECISION Data............................................................................431

Examples of VARIANCE...............................................................................................................431

WEEK Function...................................................................................................................................432

Example of WEEK.........................................................................................................................432

YEAR Function....................................................................................................................................433

Example of YEAR..........................................................................................................................433

ZEROIFNULL Function......................................................................................................................434

Example of ZEROIFNULL.............................................................................................................434

18 Table of Contents

A Quick Reference........................................................................................................435

B Reserved Words.........................................................................................................437

Reserved Neoview SQL Identifiers ....................................................................................................437

C Limits...........................................................................................................................441

D Standard SQL and Neoview SQL............................................................................443

ANSI SQL Standards...........................................................................................................................443

ISO Standards......................................................................................................................................443

Neoview SQL Compliance..................................................................................................................444

Neoview SQL Extensions to Standard SQL........................................................................................447

Character Set Support.........................................................................................................................448

Index...............................................................................................................................449

Table of Contents 19

List of Tables

1-1 Concurrent DDL/Utility Operation and File Access Modes.........................................................33

1-2 Concurrent DDL/Utility and DML Operations.............................................................................34

1-3 Concurrent DML and DDL Operations .......................................................................................34

1-4 Operations Effect on Table Timestamps .......................................................................................35

2-1 Maximum Row Sizes Available.....................................................................................................74

2-2 EXPLAIN Statement Options......................................................................................................104

2-3 Plan Summary Information.........................................................................................................106

2-4 Node Listing Information ...........................................................................................................106

2-5 Cost Factors of DETAIL_COST column......................................................................................111

6-1 One-to-One Uppercase and Titlecase to Lowercase Mappings .................................................354

6-2 One-to-One UCS2 Mappings ......................................................................................................419

6-3 Two-Character UCS2 Mapping ..................................................................................................422

6-4 Three-Character UCS2 Mapping ................................................................................................425

B-1 Reserved SQL Identifiers ............................................................................................................437

About This Document

This manual describes reference information about the syntax of SQL statements, functions, and other SQL language elements supported by the Neoview database software.

The Neoview SQL statements and utilities are entered interactively or from script files using the client-based utility, Neoview Script. For information on Neoview Script, see the Neoview Script Guide.

NOTE: In this manual, SQL language elements, statements, and clauses within statements are

based on the ANSI SQL:1999 standard.

Supported Release Version Updates (RVUs)

HP Neoview Release 2.2.

Intended Audience

This manual is intended for database administrators and application programmers who are using SQL to manage a database on an HP Neoview data warehousing platform.

The reader should be familiar with structured query language (SQL) and with the American National Standard Database Language SQL:1999.

New and Changed Information in This Edition

New or Changed InformationSection

Updated considerations.“ALTER MATERIALIZED VIEW

Statement” (page 44)

Added Considerations subsection for ALTER MVGROUP.“ALTER MVGROUP Statement” (page 45)

New statement.“CALL Statement” (page 53)

Updated syntax.“CREATE MATERIALIZED VIEW

Statement” (page 60)

Added DISK POOL and ALIGNED | PACKED format to the list of attributes.

“CREATE TABLE Statement” (page 69)

New function.“CURRENT_ROLE Function” (page 314)

New function.“CURRENT_USER Function” (page 317)

The option NOLOG is changed to NOMVLOG.“DELETE Statement” (page 90)

Added Considerations subsection for DROP MVGROUP.“DROP MVGROUP Statement” (page 95)

New function.“EXPLAIN Function” (page 339)

New statement.“EXPLAIN Statement” (page 104)

Corrected syntax.“GRANT Statement” (page 115)

New statement.“GRANT EXECUTE Statement” (page 117)

New statement.“GRANT SCHEMA Statement” (page 119)

The option NOLOG is changed to NOMVLOG. Deleted some considerations.

Added a consideration on self-referencing INSERT.

“INSERT Statement” (page 121)

The keyword LOG is changed to MVLOG.“MAINTAIN MVGROUP” (page 184)

New statement.“MERGE INTO Statement” (page 129)

Updated to include information on schemas.“Privileges” (page 245)

Supported Release Version Updates (RVUs) 23

New or Changed InformationSection

Updated to include ONLINE | OFFLINE options.“POPULATE INDEX Utility” (page 186)

Updated to include schemas.“REVOKE Statement” (page 134)

New statement.“REVOKE EXECUTEStatement” (page136)

New statement.“REVOKE SCHEMAStatement” (page138)

New consideration added.“Considerations for SELECT” (page 151)

GROUP BY clause enhanced to allow expressions. joined-table syntax is extended to support full outer join.

“SELECT Statement” (page 141)

Added table that summarizes the Neoview SQL data types.“Data Types” (page 197)

Extended to allow multiple columns to be specified. The option NOLOG is changed to NOMVLOG.

“UPDATE Statement” (page 169)

New function.“USER Function” (page 429)

Document Organization

• Chapter 1 (page 29), introduces Neoview SQL and covers topics such as database security,

data consistency and integrity, transaction management, and ANSI compliance.

• Chapter 2 (page 41), describes the SQL statements supported by Neoview SQL.

• Chapter 3 (page 181), describes utilities that perform tasks such as maintenance, populating

indexes, purging data from tables and indexes, and reorganizing and reloading data in a table or index.

• Chapter 4 (page 189), describes parts of the language, such as database objects, data types,

expressions, identifiers, literals, and predicates, which occur within the syntax of Neoview SQL statements.

• Chapter 5 (page 257), describes clauses used by Neoview SQL statements.

• Chapter 6 (page 279), describes specific functions andexpressions that you can use in Neoview

SQL statements.

• Appendix A (page 435), is a quick reference to commands, statements, and utilities.

• Appendix B (page 437), lists the words that are reserved in Neoview SQL.

• Appendix C (page 441), describes limits in Neoview SQL.

• Appendix D (page 443), describes how Neoview SQL conforms to the ANSI standard.

Notation Conventions

General Syntax Notation

This list summarizes the notation conventions for syntax presentation in this manual.

UPPERCASE LETTERS

Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example:

SELECT

Italic Letters

Italic letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required. For example:

file-name

Computer Type

Computer type letters within text indicate case-sensitive keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example:

24 About This Document

myfile.sh

Bold Text

Bold text in an example indicates user input typed at the terminal. For example:

ENTER RUN CODE

?123 CODE RECEIVED: 123.00

The user must press the Return key after typing the input.

[ ] Brackets

Brackets enclose optional syntax items. For example:

DATETIME [start-field TO] end-field

A group of items enclosed in brackets is a list from which you can choose one item or none. The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example:

DROP SCHEMA schema [CASCADE] [RESTRICT]

DROP SCHEMA schema [ CASCADE | RESTRICT ]

{ } Braces

Braces enclose required syntax items. For example:

FROM { grantee[, grantee]...}

A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines. For example:

INTERVAL { start-field TO end-field } { single-field }

INTERVAL { start-field TO end-field | single-field }

| Vertical Line

A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. For example:

{expression | NULL}

… Ellipsis

An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. For example:

ATTRIBUTE[S] attribute [, attribute]...

{, sql-expression}...

An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example:

expression-n…

Notation Conventions 25

Punctuation

Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown. For example:

DAY (datetime-expression)

@script-file

Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. For example:

"{" module-name [, module-name]... "}"

Item Spacing

Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example:

DAY (datetime-expression)

DAY(datetime-expression)

If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items:

myfile.sh

Line Spacing

If the syntax of a command is too long to fit on a singleline, each continuation line is indented three spaces and is separated from the preceding line by a blank line. This spacing distinguishes items in a continuation line from itemsin a vertical list of selections. For example:

match-value [NOT] LIKE pattern

[ESCAPE esc-char-expression]

Categories

The statements are categorized according to their functionality:

• “Data Definition Language (DDL) Statements”

• “Data Manipulation Language (DML) Statements”

• “Transaction Control Statements”

• “Resource Control and Optimization Statements”

• “Control Statements”

• “Object Naming Statements”

• “Trigger Statements”

• “Stored Procedure Statements”

Data Definition Language (DDL) Statements

Use these DDL statements to define, delete, or modify the definition of a Neoview SQL schema, or object, or the authorization to use an object.

Changes materialized views.“ALTER MATERIALIZED VIEW Statement” (page 44)

Adds or removes member materialized views from a materialized view group (MVGROUP).

“ALTER MVGROUP Statement” (page 45)

Changes synonyms or aliases for tables.“ALTER SYNONYM Statement” (page 46)

Adds a constraint or column to a table or view, drops existing constraints, or changes file attributes of a table or view.

“ALTER VIEW Statement” (page 51)

Changes table.“ALTER TABLE Statement” (page 47)

Alters trigger status.“ALTER TRIGGER Statement” (page 50)

Creates an index on a table.“CREATE INDEX Statement” (page 58)

Creates a materialized view.“CREATE MATERIALIZED VIEW Statement” (page 60)

Creates a logical collection of materialized views.“CREATE MVGROUP Statement” (page 66)

Creates a schema.“CREATE SCHEMA Statement” (page 67)

Creates synonym or alias for tables.“CREATE SYNONYM Statement” (page 68)

Creates a table.“CREATE TABLE Statement” (page 69)

Creates a trigger.“CREATE TRIGGER Statement” (page 81)

Creates a view.“CREATE VIEW Statement” (page 87)

Drops an index.“DROP INDEX Statement” (page 93)

Drops a materialized view.“DROP MATERIALIZED VIEW Statement” (page 94)

Drops a materialized group.“DROP MVGROUP Statement” (page 95)

Drops a schema.“DROP SCHEMA Statement” (page 96)

Drops a synonym.“DROP SYNONYM Statement” (page 97)

Drops a table.“DROP TABLE Statement” (page 98)

Drops a trigger.“DROP TRIGGER Statement” (page 99)

Categories 41

Drops a view.“DROP VIEW Statement” (page 100)

Grants access privileges for a table or view to specified roles.

“GRANT Statement” (page 115)

Grants privileges for executing a stored procedure in Java (SPJ) to specified roles.

“GRANT EXECUTE Statement” (page 117)

Grants access privileges for a schema to specified roles.“GRANT SCHEMA Statement” (page 119)

Revokes accessprivileges for a table or view fromspecified roles.

“REVOKE Statement” (page 134)

Revokes privileges for executing a stored procedure from specified roles.

“REVOKE EXECUTE Statement” (page 136)

Data Manipulation Language (DML) Statements

Use these DMLto delete, insert, select, or update rows in one or more tables:

Deletes rows from a table or view.“DELETE Statement” (page 90)

Inserts data into tables and views.“INSERT Statement” (page 121)

Retrieves data from tables and views.“SELECT Statement” (page 141)

Updates values in columns of a table or view.“UPDATE Statement” (page 169)

For information about DELETE, INSERT, SELECT, and UPDATE, see individual entries for these statements.

Transaction Control Statements

Use these statements to specify user-defined transactions and to set attributes for the next transaction:

Starts a transaction.“BEGIN WORK Statement” (page 52)

Commits changesmade during a transaction and ends the transaction.

“COMMIT WORK Statement” (page 56)

Performs all table maintenance operations.“MERGE INTO Statement” (page 129)

Undoes changes made during a transaction and ends the transaction.

“ROLLBACK WORK Statement” (page 140)

Sets attributes for the next SQL transaction — the isolation level, access mode, size of the diagnostics area, and whether to automatically commit database changes.

“SET TRANSACTION Statement” (page 166)

Resource Control and Optimization Statements

Use these statements to control access to a Neoview SQL table and its indexes on remote segments:

Executes and SQL statement previously compiled by a PREPARE statement.

“EXECUTE Statement” (page 101)

Locks the specified table (or the underlying tables of a view) and its associated indexes for the duration of the active transaction.

“LOCK TABLE Statement” (page 127)

Compiles an SQL statement for later use with the EXECUTE statement.

“PREPARE Statement” (page 132)

Updates statistics about the contents of a table and its indexes.

“UPDATE STATISTICS Statement” (page 175)

42 SQL Statements

Control Statements

Use these statements to control the execution default options, plans, and performance of DML statements:

Specifies a dynamic timeout value in the run-time environment of the current session.

“SET TABLE TIMEOUT Statement” (page 164)

Object Naming Statements

Use these statements to specify default ANSI names for the schema:

Sets the default ANSI schema for unqualified object names for the current session.

“SET SCHEMA Statement” (page 163)

Trigger Statements

Use these statements to create and manipulate triggers on SQL tables:

Alters a trigger.“ALTER TRIGGER Statement” (page 50)

Creates a trigger.“CREATE TRIGGER Statement” (page 81)

Drops a trigger.“DROP TRIGGER Statement” (page 99)

Stored Procedure Statements

Use these statements to execute stored procedures in Java (SPJs) and to modify authorization to execute SPJs:

Initiates the execution of an SPJ in a Neoview database.“CALL Statement” (page 53)

Grants privileges for executing an SPJ to specified roles.“GRANT EXECUTE Statement” (page 117)

Revokes privileges for executing an SPJ from specified roles.

“REVOKE EXECUTE Statement” (page 136)

Categories 43

ALTER MATERIALIZED VIEW Statement

• “Syntax Description of ALTER MATERIALIZED VIEW” (page 44)

• “Considerations for ALTER MATERIALIZED VIEW” (page 44)

The ALTER MATERIALIZED VIEW statement changes a materialized view.. See “Database

Object Names” (page 196).

ALTER {MATERIALIZED VIEW | MV} name mv-alter-action

mv-alter-action is:

MVATTRIBUTES[S] mv-attribute | {ADD | REMOVE} IGNORE CHANGES ON simple-table [,simple-table]..] | RENAME TO new-name [CASCADE]

mv-attribute is: COMMIT REFRESH EACH n-rows

Syntax Description of ALTER MATERIALIZED VIEW

name

specifies the current name of the object. See “Database Object Names” (page 196).

RENAME new-name [CASCADE]

changes the logical name of the object within the same schema.

new-name

specifies the new name of the object after the RENAME operation occurs. CASCADE

specifies that indexes and constraints on the renamed object will be renamed.

COMMIT REFRESH EACH n-rows

n-rows refers to the number of rows that refresh processes from the log before committing a transaction and starting another one. There is no recommended value for n-rows, but the number must be less than 5000.

Considerations for ALTER MATERIALIZED VIEW

As in regular tables, materialized view tables have attributes. Most of the attributes that are valid for regular tables are also valid for materialized views. In addition, materialized view tables have specific attributes, which are MVATTRIBUTES and IGNORE CHANGES.

MVATTRIBUTES is used by refresh to determine the number of rows that should be refreshed per transaction. This attribute is not valid for MJVs. IGNORE CHANGES is used by refresh to ignore the updates performed to the base tables that are specified in the IGNORE CHANGES list.

A base table of the materialized view can be added to the IGNORE CHANGES list by altering the materialized view using the ADD IGNORE CHANGES ON clause. Using ALTER MATERIALIZED VIEW to add ignore changes on a base table will be successful only if all the updates to the base table up to that point are propagated to the materialized view by using REFRESH. If there are updated rows on a base table that are not used by REFRESH to update rows in the materialized view, an error will occur that indicates a REFRESH needs to be performed before altering the materialized view to add IGNORE CHANGES on the base table. Only a base table that is in the FROM clause of the query expression of the materialized view can be added to the IGNORE CHANGES list. A base table of the materialized view that is already in the IGNORE CHANGES list can be removed from the IGNORE CHANGES list by altering the materialized view using the REMOVE IGNORE CHANGES ON clause.

44 SQL Statements

ALTER MVGROUP Statement

• “Syntax Description of ALTER MATERIALIZED VIEW”

• “Considerations for ALTER MATERIALIZED VIEW”

The ALTER MVGROUP statement allows you to add or remove a member from the materialized view group (MVGROUP). The ADD clause should be used when adding one or more MVs to the MVGROUP. The REMOVE clause should be used when removing one or more MVs from an MVGROUP.

For information on MVGROUPS, see “MVGROUPs ” (page 254).

ALTER MVGROUP mv-group-name {ADD mv-name [, mv-name] ... | REMOVE mv-name [, mv-name]...}

mv-group-name

specifies the materialized view group name to alter.

mv-name

specifies the ANSI logical name for the materialized view.

Considerations for ALTER MVGROUP

• Only ON REQUEST and RECOMPUTE MV can be added to a materialized view group. ON

STATEMENT MV is not allowed to participate in a group.

• A materialized view is allowed to participate in a group only if:

— all materialized views directly or indirectly used by the materialized view are in the

group, or

— the materializedview is a single-delta materialized view. See “CREATE MATERIALIZED

VIEW Statement” (page 60) for information on single-delta materialized views.

Example of ALTER MVGROUP

• This example adds a member to the materialized view group:

ALTER MVGROUP pgroup ADD pgroups

• This example removes a member from the materialized view group:

ALTER MVGROUP pgroup REMOVE pgroups

ALTER MVGROUP Statement 45

ALTER SYNONYM Statement

• “Syntax Description of ALTER SYNONYM ”

• “Considerations”

• “Versioning Considerations”

• “Example of ALTER SYNONYM”

The ALTER SYNONYM statement allows you to change the table, view, or materialized view the synonym is referencing to a different object of the same structure. You can write queries that reference the synonym and use the ALTER SYNONYM command to point to a different object with the same structure. The advantage of this is to change the object being referenced without changing the query.

When a synonym is assigned to an object, the object can be referenced either with the synonym name or the actual name. The resultant query results will be the same.

Syntax Description of ALTER SYNONYM

ALTER SYNONYM alternate-name TO object;

alternate-name

specifies the name of the synonym. See “Database Object Names” (page 196).

object

specifies Neoview SQL tables, views, and materialized views. See “Database Object Names”

(page 196).

Considerations

• When the object being referenced by the synonym is dropped, then the synonym is also

dropped.

• Only the owner of the schemaor the services ID can create,alter, or drop the alternate names

for a table.

• You can create an unlimited number of synonyms for a single object.

• Grant and Revoke commands are allowed on synonyms. The command will be applied to

the actual reference object.

• View, constraints, and trigger text cannot use synonym names in their DML text.

• Synonyms cannot be renamed. The RENAME operation is allowed on actual table names

only.

Versioning Considerations

Synonyms are only available in schema version 2000. If you need to downgrade your schema to version 1200, all synonyms must be dropped.

Example of ALTER SYNONYM

• This example changes a synonym:

ALTER SYNONYM aname TO s-table

46 SQL Statements

ALTER TABLE Statement

• “Syntax Description of ALTER TABLE”

• “Considerations for ALTER TABLE”

• “Example of ALTER TABLE”\

The ALTER TABLE statement adds a column to a Neoview SQL table. See “Database Object

Names” (page 196).

ALTER TABLE name alter-action ATTRIBUTE {NO INSERTLOG | INSERTLOG}

alter-action is: ADD [COLUMN] column-definition | ADD [CONSTRAINT constraint] table-constraint

| DROP CONSTRAINT constraint [RESTRICT | CASCADE] | RENAME TO new name [CASCADE]

column-definition is:

column data-type

([DEFAULT default | NO DEFAULT] [[CONSTRAINT constraint-name] column-constraint]...)

data-type is: CHAR[ACTER] [(length)[CHARACTERS]] [CHARACTER SET char-set-name] [UPSHIFT] | PIC[TURE] X [(length)][CHARACTER SET char-set-name] [DISPLAY] [UPSHIFT] | CHAR[ACTER] VARYING (length) [CHARACTER SET char-set-name] [UPSHIFT] | VARCHAR (length) [CHARACTER SET char-set-name] [UPSHIFT] | NUMERIC [(precision [,scale])] [SIGNED|UNSIGNED] | NCHAR [(length) [CHARACTER SET char-set-name] [UPSHIFT] | NCHAR VARYING(length) [CHARACTER SET char-set-name] [UPSHIFT] | SMALLINT [SIGNED|UNSIGNED] | INT[EGER] [SIGNED|UNSIGNED] | LARGEINT | DEC[IMAL] [(precision [,scale])] [SIGNED|UNSIGNED] | PIC[TURE] [S]{ 9(integer) [V[9(scale)]] | V9(scale) } [DISPLAY [SIGN IS LEADING] | COMP] | FLOAT [(precision)] | REAL | DOUBLE PRECISION | DATE | TIME [(time-precision)] | TIMESTAMP [(timestamp-precision)] | INTERVAL { start-field TO end-field | single-field }

default is:

literal

| NULL | CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP}

Syntax Description of ALTER TABLE

name

specifies the current name of the object. See “Database Object Names” (page 196).

ALTER TABLE Statement 47

NO INSERTLOG | INSERTLOG

specifies whether update and delete operations are recorded in the table’s IUD log.

When a table is defined with the INSERTLOG attribute, logging operations ignore delete and update operations and log inserts only.

ADD [COLUMN] column-definition

adds a column to table. This option is valid only for the ALTER TABLE statement.

The clauses for the column-definition are:

column-name

specifies the name for the new column in the table. column-name is an SQL identifier. column-name must be unique among column names in the table. If the column name

is a Neoview SQL reserved word, you must delimit it by enclosing it in double quotes. For example: "sql".myview. See “Identifiers” (page 220).

data-type

specifies is the data type of the values that can be stored in column. See “Data Types”

(page 197).

ADD [CONSTRAINT constraint] table-constraint

adds a constraint to the table and optionally specifies constraint as the name for the constraint. The new constraint must be consistent with any data already present in the table.

CONSTRAINT constraint

specifies a name for the column or table constraint. constraint must have the same schema as table and must be unique among constraint names in its schema. If you omit the schema portions of the name you specify in constraint, Neoview SQL expands the constraintname by using theschema for table. See“Database Object Names” (page 196).

If you do not specify a constraint name, Neoview SQL constructs an SQL identifier as the name for the constraint in the schema for table. The identifier consists of the fully qualified table name concatenated with a system-generated unique identifier. For example, a

constraint on table A.B.C might be assigned a name such as A.B.C_123..._01... .

DROP CONSTRAINT constraint [RESTRICT | CASCADE]

drops a constraint from the table. The new constraint must be specified.

If you drop a constraint, Neoview SQL drops its dependent index if Neoview SQL originally created the same index. If the constraint uses an existing index, the index is not dropped.

The default is RESTRICT.

CONSTRAINT constraint

constraint on table A.B.C might be assigned a name such as A.B.C_123..._01... .

RENAME new-name[CASCADE]

changes the logical name of the object within the same schema.

new-name

specifies the new name of the object after the RENAME operation occurs. CASCADE

specifies that indexes and constraints on the renamed object will be renamed.

48 SQL Statements

column data-type

specifies the name and data type for a column in the table.

column is an SQL identifier. column must be unique among column names in the table. If the name is a Neoview SQL reserved word, you must delimit it by enclosing it in double quotes. Such delimited parts are case-sensitive. For example: "join".

data-type is the data type of the values that can be stored in column. A default value must be of the same type as the column, including the character set for a character column. See

“Data Types” (page 197).

DEFAULT default | NO DEFAULT

specifies a default value for the column or specifies that the column does not have a default value. You can declare the default value explicitly by using the DEFAULT clause, or you can enable null to be used as the default by omitting both the DEFAULT and NOT NULL clauses. If you omit the DEFAULT clause and specify NOT NULL, Neoview SQL returns an error. For existing rows of the table, the added column takes on its default value.

If you set the default to the datetime value CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP, Neoview SQL uses January 1, 1 A.D. 12:00:00.000000 as the default date and time for the existing rows.

For any row that you add after the column is added, if no value is specified for the column as part of the add row operation, the column receives a default value based on the current timestamp at the time the row is added.

CONSTRAINT constraint

specifies a name for the column or table constraint. constraint must have the same schema as table and must be unique among constraint names in its schema. If you omit the schema portions of the name you specify in constraint, Neoview SQL expands the constraint name by using the schema for table. See “Database Object Names” (page 196).

If you do not specify a constraint name, Neoview SQL constructs an SQL identifier as the name for the constraint in the schema for table.The identifier consists of the fully qualified table name concatenated with a system-generated unique identifier. For example, a constraint

on table A.B.C might be assigned a name such as A.B.C_123..._01... .

Considerations for ALTER TABLE

Effect of Adding a Column on View Definitions

The addition of a column to a table has no effect on existing view definitions. Implicit column references specified by SELECT * in view definitions are replaced by explicit column references when the definition clauses are originally evaluated.

Authorization and Availability Requirements

To alter a table, you must own its schema or be the super id. You must also have access to all indexes of the table itself.

ALTER TABLE works only on user-created tables.

Example of ALTER TABLE

• This example adds a column:

ALTER TABLE persnl.project ADD COLUMN projlead NUMERIC (4) UNSIGNED

ALTER TABLE Statement 49

ALTER TRIGGER Statement

• “Syntax Description of ALTER TRIGGER”

• “Considerations for ALTER TRIGGER”

The ALTER TRIGGER statement is used to enable or disable triggers, individually or by SQL tables.

ALTER TRIGGER { ENABLE trigger-name | ENABLE ALL OF table-name | DISABLE trigger-name | DISABLE ALL OF table-name};

Syntax Description of ALTER TRIGGER

trigger-name

specifies the name of the trigger to alter. See “Database Object Names” (page 196).

table-name

specifies the name of the table that this trigger is defined on. See “Database Object Names”

(page 196).

Considerations for ALTER TRIGGER

ENABLE ALL enables all triggers defined on table-name.

DISABLE ALL disables all triggers defined on table-name.

Authorization and Availability Requirements

To alter a trigger, you must own its schema or be the services ID. Only the table owner can use ALTER TRIGGER DISABLE ALL or ALTER TRIGGER ENABLE ALL

50 SQL Statements

ALTER VIEW Statement

• “Syntax Description of ALTER VIEW”

• “Example of ALTER VIEW”

The ALTER VIEW statement adds a column to a Neoview SQL table. See “Database Object

Names” (page 196).

ALTER VIEW name alter-action

alter-action is: RENAME TO new name

Syntax Description of ALTER VIEW

name

specifies the current name of the object. See “Database Object Names” (page 196).

RENAME new-name

changes the logical name of the object within the same schema.

new-name

specifies the new name of the object after the RENAME operation occurs.

Example of ALTER VIEW

• This example adds a column:

ALTER VIEW persnl.project RENAME TO persnl.project2

ALTER VIEW Statement 51

BEGIN WORK Statement

The BEGIN WORKstatement enables you to start a transaction explicitly—where thetransaction consists ofthe set of operations defined by the sequence of SQL statements that begins immediately after BEGIN WORK and ends with the next COMMIT or ROLLBACK statement. See “Transaction

Management” (page 32).

BEGIN WORK is a Neoview SQL extension.

BEGIN WORK

Examples of BEGIN WORK

• Group three separate statements—two INSERT statements and an UPDATE statement—that

update the database within a single transaction:

--- This statement initiates a transaction. BEGIN WORK;

--- SQL operation complete.

INSERT INTO sales.orders VALUES (125, DATE '1998-03-23', DATE '1998-03-30', 75, 7654);

--- 1 row(s) inserted.

INSERT INTO sales.odetail VALUES (125, 4102, 25000, 2);

--- 1 row(s) inserted.

UPDATE invent.partloc SET qty_on_hand = qty_on_hand - 2 WHERE partnum = 4102 AND loc_code = 'G45';

--- 1 row(s) updated.

--- This statement ends a transaction. COMMIT WORK;

--- SQL operation complete.

52 SQL Statements

CALL Statement

• “Syntax Description of CALL”

• “Considerations for CALL”

• “Examples of CALL”

The CALL statement invokes a storedprocedure inJava (SPJ) ina Neoviewdatabase. To develop, deploy, and manage SPJs, see the Neoview Guide to Stored Procedures in Java.

CALL procedure-ref ([argument-list])

procedure-ref is: [schema-name.]procedure-name

argument-list is:

SQL-expression[{, SQL-expression}...]

Syntax Description of CALL

procedure-ref

specifies an ANSI logical name of the form:

[schema-name.]procedure-name

where each part of the name is a valid SQL identifier with a maximum of 128 characters. For more information, see “Identifiers” (page 220).

If you do not fully qualify the procedure name, Neoview SQL qualifies it according to the schema of the current session.

argument-list

accepts arguments for IN, INOUT, or OUT parameters. The arguments consist of SQL expressions, including dynamic parameters, separated by commas:

SQL-expression[{, SQL-expression}...]

Each expression must evaluate to a value of one of these data types:

• Character value

• Date-time value

• Numeric value

Interval value expressions are disallowed in SPJs. For more information, see “Input Parameter

Arguments” (page 54) and “Output Parameter Arguments” (page 54).

Do not specify result sets in the argument list. For information about how to use result sets, see the Neoview Guide to Stored Procedures in Java.

Considerations for CALL

Usage Restrictions

You can use a CALL statement as a stand-alone SQL statement in applications or command-line interfaces, such as Neoview Script. You can also use a CALL statement in a trigger but not inside a compound statement or with rowsets. If you use a CALL statement in a trigger, the CALL statement must execute a stored procedure that does not have any OUT or INOUT parameters.

Required Privileges

To execute a CALL statement, you must have EXECUTE privilege on the procedure. For more information, see the “GRANT EXECUTE Statement” (page 117).

CALL Statement 53

Input Parameter Arguments

You pass data to an SPJ by using IN or INOUT parameters. For an IN parameter argument, use one of these SQL expressions:

• Literal

• SQL function (including CASE and CAST expressions)

• Arithmetic or concatenation operation

• Scalar subquery

• Dynamic parameter (for example, ?) in an application

• Named (for example, ?param) or unnamed (for example, ?) parameter in Neoview Script

For an INOUT parameter argument, you can use only a dynamic, named, or unnamed parameter. For more information, see “Expressions” (page 208).

Output Parameter Arguments

An SPJ returns values in OUT and INOUT parameters. Output parameter arguments must be dynamic parameters in an application (for example, ?) or named or unnamed parameters in Neoview Script (for example, ?param or ?). Each calling application defines the semantics of the OUT and INOUT parameters in its environment. For more information, see the Neoview Guide to Stored Procedures in Java.

Data Conversion of Parameter Arguments

Neoview SQL performs an implicit data conversion when the data type of a parameter argument is compatible with but does not match the formal data type of the stored procedure. For stored procedure input values, the conversion is from the actual argument value to the formal parameter type. For stored procedure output values, the conversion is from the actual output value, which has the data type of the formal parameter, to the declared type of the dynamic parameter.

Null Input and Output

You can pass a null value as input to or output from an SPJ, provided that the corresponding Java data type of the parameter supports nulls. If a null is input or output for a parameter that does not support nulls, Neoview SQL returns an error. For more information about handling null input and output, see the Neoview Guide to Stored Procedures in Java.

Transaction Semantics

The CALL statement automatically initiates a transaction if there is no active transaction. However, the failure of a CALL statement does not always automatically abort the transaction. For more information, see the Neoview Guide to Stored Procedures in Java.

Examples of CALL

• In the Neoview Script interface, execute an SPJ named MONTHLYORDERS, which has one

IN parameter represented by a literal and one OUT parameter represented by a unnamed parameter, ?:

CALL sales.monthlyorders(3,?);

• This CALL statement executes a stored procedure, which accepts one IN parameter (a date

literal), returns one OUT parameter (a row from the column, NUM_ORDERS), and returns two result sets:

CALL sales.order_summary('01/01/2007', ?);

NUM_ORDERS

------------------- 13

ORDERNUM NUM_PARTS AMOUNT Order/Date Last Name

54 SQL Statements

---------- -------------- --------------- ---------- ------------------

100210 4 19020.00 2006-04-10 HUGHES 100250 4 22625.00 2006-01-23 HUGHES 101220 4 45525.00 2006-07-21 SCHNABL 200300 3 52000.00 2006-02-06 SCHAEFFER 200320 4 9195.00 2006-02-17 KARAJAN 200490 2 1065.00 2006-03-19 WEIGL . . .

--- 13 row(s) selected.

Order/Num Part/Num Unit/Price Qty/Ord Part Description

---------- -------- ------------ ---------- ------------------

100210 2001 1100.00 3 GRAPHIC PRINTER,M1 100210 2403 620.00 6 DAISY PRINTER,T2 100210 244 3500.00 3 PC GOLD, 30 MB 100210 5100 150.00 10 MONITOR BW, TYPE 1 100250 6500 95.00 10 DISK CONTROLLER 100250 6301 245.00 15 GRAPHIC CARD, HR . . .

--- 70 row(s) selected.

--- SQL operation complete.

For more information about using SPJ result sets, see the Neoview Guide to Stored Procedures in Java.

• This trigger contains a CALL statement, which executes an SPJ named LOWERPRICE. An

SPJ in a trigger must not have any OUT or INOUT parameters.

CREATE TRIGGER sales.setsalesprice AFTER UPDATE OF qty_on_hand ON invent.partloc FOR EACH STATEMENT REFERENCING NEW as newqty WHEN ( SUM(newqty.qty_on_hand) > 500 ) CALL sales.lowerprice();

For more examples, see the Neoview Guide to Stored Procedures in Java.

CALL Statement 55

COMMIT WORK Statement

• “Considerations for COMMIT WORK”

• “Example of COMMIT WORK”

The COMMIT WORK statement commits any changes to objects made during the current transaction, releases all locks on objects held by the transaction, and ends the transaction. See

“Transaction Management” (page 32).

COMMIT [WORK]

WORK is an optional keyword that has no effect.

COMMIT WORK has no effect outside of an active transaction.

Considerations for COMMIT WORK

Begin and End a Transaction

BEGIN WORK starts a transaction. COMMIT WORK or ROLLBACK WORK ends a transaction.

Effect of Constraints

When COMMIT WORK is executed, all active constraints are checked, and if any constraint is not satisfied, changes made to the database by the current transaction are canceled—that is, work done by the current transaction is rolled back. If all constraints are satisfied, all changes made by the current transaction become permanent.

Example of COMMIT WORK

• Suppose that your application adds information to the inventory. You have received 24

terminals from a new supplier and want to add the supplier and update the quantity on hand. The part number for the terminals is 5100, and the supplier is assigned supplier number

17. The cost of each terminal is $800.

The transaction must add the order for terminals to PARTSUPP, add the supplier to the SUPPLIER table, andupdate QTY_ON_HAND in PARTLOC. After the INSERT and UPDATE statements execute successfully, you commit the transaction, as shown:

-- This statement initiates a transaction. BEGIN WORK;

--- SQL operation complete.

-- This statement inserts a new entry into PARTSUPP. INSERT INTO invent.partsupp VALUES (5100, 17, 800.00, 24);

--- 1 row(s) inserted.

-- This statement inserts a new entry into SUPPLIER. INSERT INTO invent.supplier VALUES (17, 'Super Peripherals','751 Sanborn Way', 'Santa Rosa', 'California', '95405');

--- 1 row(s) inserted.

-- This statement updates the quantity in PARTLOC. UPDATE invent.partloc SET qty_on_hand = qty_on_hand + 24 WHERE partnum = 5100 AND loc_code = 'G43';

--- 1 row(s) updated.

-- This statement ends a transaction.

56 SQL Statements

COMMIT WORK;

--- SQL operation complete.

COMMIT WORK Statement 57

CREATE INDEX Statement

• “Syntax Description of CREATE INDEX”

• “Considerations for CREATE INDEX”

• “Example of CREATE INDEX”

The CREATE INDEX statement creates a Neoview SQL index based on one or more columns of a table. The CREATE VOLATILE INDEX statement creates a Neoview SQL index with a lifespan that is limited to the SQL session that the index is created. Volatile indexes are dropped automatically when the session ends. See “Database Object Names” (page 196).

CREATE INDEX is a Neoview SQL extension.

CREATE [VOLATILE] [UNIQUE] INDEX index ON table (column-name [ASC[ENDING] | DESC[ENDING]] [,column-name [ASC[ENDING] | DESC[ENDING]]]...) [populate-option] [file-option]...

populate-option is: POPULATE | NO POPULATE

file-option is: | partn-file-option

partn-file-option is: {HASH PARTITION BY (partitioning-column,

partitioning-column...)}

Syntax Description of CREATE INDEX

UNIQUE

specifies that the values (including NULL values) in the column or set of columns that make up the index cannot contain more than one occurrence of the same value or set of values. For indexes with multiple columns, the values of the columns as a group determine uniqueness, not the values of the individual columns.

index

is an SQL identifier that specifies the simple name for the new index. You cannot qualify index with its schema names. Indexes have their own namespace within a schema, so an index name might be the same as a table or constraint name. However, no two indexes in a schema can have the same name.

table

is the name of the table for which to create the index. See “Database Object Names” (page 196).

column-name [ASC[ENDING] | DESC[ENDING]] [,column-name [ASC[ENDING] | DESC[ENDING]]]...

specifies the columns in table to include in the index. The order of the columns in the index need not correspond to the order of the columns in the table.

ASCENDING or DESCENDING specifies the storage and retrieval order for rows in the index. The default is ASCENDING.

Rows are ordered by values in the first column specified for the index. If multiple index rows share the same value for the first column, the values in the second column are used to order the rows, and so forth. If duplicate index rows occur in a nonunique index, their order is based on the sequence specified for the columns of the key of the underlying table. For ordering (but not for other purposes), nulls are greater than other values.

populate-option

When you create an index and do not specify POPULATE or NO POPULATE, POPULATE is assumed.If you create an index using NO POPULATE, you must later request a POPULATE INDEX command to actually load the data. See “POPULATE INDEX Utility” (page 186).

58 SQL Statements

NO POPULATE

specifies that the index is not to be populated when it is created. The indexes are created, but no data is written to the index, and it is marked offline. You can drop an offline index with the DROP INDEX statement. The DROP TABLE statement also drops offline indexes of the specified table. DML statements have no effect on offline indexes. If an index is created with the intention of using it for a constraint, you must populate it before creating the constraint. By using the POPULATE INDEX utility, you can populate an offline index and remove its offline designation.

POPULATE

Specifies thatthe index is to be created and populated. If you omit the populate-option, the default is POPULATE.

partn-file-option is:

{HASH PARTITION BY (partitioning-column, partitioning-column...)}

specifies the partitioning columns. If you do not specify the partitioning columns, the default is the same partitioning column or columns as the base table for a non-unique index, and all the columns in the index for a unique index.

Considerations for CREATE INDEX

When you create an index and do not specify POPULATE or NO POPULATE, POPULATE is assumed. If you create an index using NO POPULATE, you must later request a POPULATE INDEX command to actually load the data. See “POPULATE INDEX Utility” (page 186).

Authorization and Availability Requirements

To create a Neoview SQL index, you must be the owner of the underlying table or be the services ID.

When the POPULATE option is specified, CREATE INDEX locks out INSERT, DELETE, and UPDATE operations on the table being indexed. If other processes have rows in the table locked when the operation begins, CREATE INDEX waits until its lock request is granted or timeout occurs.

An index always has the same security as the table it indexes, so roles authorized to access the table can also access the index. You cannot access an index directly.

Limits on Indexes

For nonunique indexes, the sum of the lengths of the columns in the index plus the sum of the length of the clustering key of the underlying table cannot exceed 2048 bytes. For unique indexes, the sum of the lengths of the columns in the index cannot exceed 2048 bytes.

There is no restriction on the number of indexes per table.

Example of CREATE INDEX

• This example creates an index on two columns of a table:

CREATE INDEX xempname ON persnl.employee (last_name, first_name);

CREATE INDEX Statement 59

CREATE MATERIALIZED VIEW Statement

• “Syntax Description of CREATE MATERIALIZED VIEW”

• “Considerations for CREATE MATERIALIZED VIEW”

• “Example of CREATE MATERIALIZED VIEW”

The CREATE MATERIALIZED VIEW statement creates a materialized view.

CREATE {MATERIALIZED VIEW | MV} mv-name [column-name-list] { RECOMPUTE | REFRESH refresh-type } { INITIALIZE ON REFRESH | INITIALIZE ON CREATE }

file-options AS query-expr

column-name-list is: (column-name [, column-name]...)

refresh-type is:

ON STATEMENT | {ON REQUEST [ignore-changes]}

ignore-changes is: IGNORE CHANGES ON simple-table [, simple-table]..

file-options is: [clustering-def] [partition-definition] [mv-attributes]

clustering-def is: STORE BY (key-column-list)

partition-definition is: HASH PARTITION [BY key-column-list]

mv-attributes is: MVATTRIBUTE[S] COMMIT REFRESH EACH n-rows

query-expr is: SELECT column-expr [, column-expr]... FROM table-ref [,table-ref] ... [WHERE search-condition] [GROUP BY {colname | colnum}[, {colname | colnum}]...]

table-ref is: simple-table | joined-table

simple-table is:

{base-table-name | materialized-view-name} [[AS] corr-name [(col-expr-list)]]

joined-table is:table-ref [NATURAL] [INNER] join table-ref [join-spec]

join-spec is: ON search-condition

column-expr is: {non-aggregate-column-expr [[AS] derived-name] | aggregate-name(expr) [AS] derived-name}

aggregate-name is:

AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE

Syntax Description of CREATE MATERIALIZED VIEW

mv-name

specifies the ANSI logical name for the materialized view to create.

60 SQL Statements

column-name-list

specifies names for the columns in the materialized view, as well as headings for the columns. Column names in the list correspond directly to columns in the query-expr. If you omit this clause, columns in the materialized view will have the same names as the corresponding columns in the query-expr. If a column in query-expr does not have an implicit name (for example, a function) and an explicit name is not specified using the AS syntax, the column-name-list must be specified or an error is returned. You must specify this clause if any two columns in the tables specified by query-expr have the same name. column-name-list is optional.

You can specify GROUP BY using ordinals to refer to the relative positionwithin the SELECT list. For example, GROUP BY 3, 2, 1.

INITIALIZE ON CREATE

implies that the materialized view gets its initial content upon its creation. A materialized view that is based on another materialized view can be initialized on create only if the query expression does not include joins and if the underlying materialized view is already initialized. This restriction is required in order to guarantee the consistency of the materialized view.

INITIALIZE ON REFRESH

implies that the materialized view gets its initial content on its first refresh, assuming the underlying tables have been updated.

refresh-type

specifies the method that will be used to update the materialized view. ON REQUEST

this method implies the incremental maintenance of the materialized view. The materialized view has to be explicitly refreshed through the MAINTAIN command.

ON STATEMENT

a materialized view that is refreshed ON STATEMENT is often called immediate. An ON STATEMENT materialized view ismaintained automatically as part of the statement that updated any of the base tables that are part of the materialized view, just like indexes. Therefore, immediate materialized views are always fully consistent with the database base tablesbut are expected toslow down the update transactions. Only ON STATEMENT materialized joined views are supported. ON STATEMENT materialized aggregate views and ON STATEMENT MAV on Explicit Join (MAJV) are not supported.

ignore-changes

The IGNORE CHANGES ON clause instructs the refresh operation of a materialized view over several base tables to ignore the changes to the listed base tables. This clause is applicable only to ON REQUEST MAJVs and ON REQUEST MJVs. At least one table from the FROM clause of the MV should not appear in the IGNORE CHANGES ON clause. Also, only base tables from the FROM clause of the MV can appear in the IGNORE CHANGES ON clause. The IGNORE CHANGES list cannot include MVs.

file-options

is a subset of the table file option (see the“CREATE TABLE Statement” (page 69).

The STORE BY and PARTITION BY clauses are only required for RECOMPUTE materialized views. ON REQUEST and ON STATEMENT materialized views are automatically clustered and partitioned by Neoview.

STORE BY (key-column-list)

The STORE BY clause specifies the order of rows within the physical file that holds the table, determines the physical organization of the table, and the ways you can partition the table. The storage key is referred to as the clustering index. You define the clustering index for the materialized view table using the column list. The key columns in the key-column-list must be NOT NULL columns from the materialized view query expression.

HASH PARTITION BY (key-column-list)

CREATE MATERIALIZED VIEW Statement 61

Hash partitioning is the only partitioning scheme supported for materialized views. This clause defines the partitioning keys of the materialized view.

non-aggregate-column-expr

col-expr is a single column name or a derived column. A derived column(derived-name)

is an SQL value expression; its operands can be numeric, string, datetime, or interval literals, columns, functions defined on columns, scalar subqueries, CASE expressions, or CAST expressions. Any single column name in col-expr must be from tables or views specified in the FROM clause.

aggregate-name

Specifies the aggregate name: AVG, COUNT, MAX, MIN, STDDEV, SUM, or VARIANCE.

query-expr

is a subset of the query expression supported in a regular view definition. Only expressions that start with SELECT are supported.

mv-attributes

The COMMIT REFRESH EACH attribute is only allowed for ON REQUEST MVs that are defined on single delta MVs. A single delta MV is an MV that can be refreshed based on changes to a single table. So, a MAV or a single delta MAJV (an MV that ignores updates for all the tables in the FROM clause except one table) can specify the COMMIT REFRESH EACH.

Considerations for CREATE MATERIALIZED VIEW

• Materialized aggregate views do not support the DISTINCT clause as an argument of the

aggregate function. Additionally, aggregate functions as subqueries in an aggregate function expression are not allowed.

• If optional column names for the materialized view are not specified, they default to the

column names (or derived names) specified in the materialized view query expression.

• You can specify GROUP BY using ordinals to refer to the relative position within the SELECT

list. For example, GROUP BY 3, 2, 1.

• Materialized views cannot be created on views.

Types of Materialized Views

Neoview SQL defines the following three types of materialized views:

• Materialized Join View (MJV): A materialized join view that holds the results of a join query

with inner equi-joins of several tables. Outer joins and cross joins are not supported. These tables can be base tables or other MVs. The query does not include aggregations.

• Materialized Aggregate View (MAV): A materialized aggregate view that holds the result

of a GROUP BY query with any of these aggregate functions: SUM, AVG, VARIANCE, STDDEV, MIN, MAX, COUNT(*), and COUNT(x). The aggregate function can include expression on the columns, such as SUM(a+b). Aggregate MVs can be of two types:

— MAV on Single Table: the MAV is defined on a single base table or MV — MAV on Explicit Join (MAJV): the MAV is defined on an inner equi-join of several base

tables, MVs, or both.

• RECOMPUTE materialized views: A materialized view that is initialized every time the

materialized view needs to be updated with changes to its base tables.

Authorization

Materialized views have the same security policies as regular views, which are independent of those of its underlying tables.

The creator of a materialized view must have SELECT privileges on all the objects underlying the materialized view. To grant a SELECT privilege on the materialized view to another user, the creator of the materialized view must have the grant option on the underlying objects.

62 SQL Statements

The INSERT privilege is used to authorize the incremental REFRESH. You must have INSERT privileges on a materialized view to be able to perform an incremental REFRESH on it.

You must have full access (SELECT, INSERT, and DELETE privileges) to the materialized view to perform either initialize or recompute on the materialized view.

The INSERT and DELETE privileges on a materialized view can be granted by the creator of the materialized view or any user that has a grant option. INSERT and UPDATE commands are blocked on materialized views. The DELETE command on RECOMPUTE and ON STATEMENT materialized views is blocked. The DELETE privilege on ON REQUEST MV enables the user to perform deletes.

Materialized Views Clustering

Materialized views are automatically clustered as follows:

• MAVs and MAJVs — The clustering key defaults to the set of all not null GROUP BY columns.

• MJVs — The clustering key defaults to the MV SYSKEY and the clustering columns of one

of the base tables.

• RECOMPUTE — RECOMPUTE materialized views are not automatically clustered.

Materialized Views Using Other Materialized Views

When one materialized view uses other materialized views, their refresh types must be compatible. You cannot create anON STATEMENT materialized view that uses an ON REQUEST materialized view or any RECOMPUTE materialized views. An ON REQUEST materialized view can use ON STATEMENT materialized views but not RECOMPUTE materialized views. RECOMPUTE materialized views can be defined on any other type of materialized view.

Single Delta Materialized Views

A materialized view is single delta if it is based on a single table (base table or another materialized view) or on a join where all tables but one are in the IGNORE CHANGES list.

DML Operations on Materialized Views

All update operations (INSERT, UPDATE,DELETE) areblocked onall materialized views except for the DELETE operation on ON REQUEST materialized views.

Indexes and Materialized Views

Secondary indexes may be created automatically by the system for incremental MVs. These indexes are designed to enhance the performance of the REFRESH operation.

For MJVs, the system-added secondary indexesare based on the underlying base tables’ clustering index columns. These indexes are not created for underlying tables with INSERTLOG attribute or those tables that are included in the MV's IGNORE CHANGES clause. Neoview SQL tries to minimize the number of secondary indexes created by the system based on the equal predicates in the query expression.

For MAVs, the clustering index includes all the NOT NULL group-by columns. If some of the group-by columns are nullable, the system generates a secondary index based on all the group-by columns.

Secondary indexes created by the system for incremental REFRESH of MV might be expensive to maintain. Neoview SQL allows users to remove indexes that were created by the system and to create alternative indexes. If you remove system-added indexes and do not replace them with alternative indexes, the performance of the REFRESH operation may be greatly hurt.

You can create additional secondary indexes, which cannot be unique.

CREATE MATERIALIZED VIEW Statement 63

Joins

• An inner equi-join query is a query where rows from each table are matched to specific rows

in other tables using equal predicates.

• Outer joins include LEFT OUTER JOIN or FULL OUTER JOIN.

• In a cross join, not all predicates are given so each row of a table is matched with all the rows

of the other table.

Restrictions for CREATE MATERIALIZED VIEW

The following restrictions specify what materialized view types can be defined as incremental:

MJV (Materialized Join View)

• Only inner equi-joins are supported; outer joins and cross joins are not supported.

• Must be a single block SELECT-FROM-WHERE query (for example, UNION, ORDER BY

are not allowed).

MAV (Materialized Aggregate View) on a Single Table

• The MAV can also be defined on a previously defined MV.

• The HAVING clause is not allowed.

• The DISTINCT function is not supported.

• Aggregates must be top-most functions and they cannot contain nested aggregates (for

example, no AVG(X)+1).

• Under certain conditions when the MAX or MIN function is used in a MAV, the incremental

refresh of the MV returns an error and the MV needs to be recomputed. This is because if a row that contains the MAX or MIN value of its group is deleted from the base table, a new value cannot be incrementally computed. The failure of an incremental refresh operation of a MAV, because of the deletion of the MIN or MAX value, can be avoided:

— if an even greater value was inserted, it is the new MAX or MIN value — if the base table is in insert-only table, mark it as INSERTLOG — add an index on the base table on the columns that are used as the MVs group by

columns. When such a supporting index exists, the new MAX or MIN value can be computed without a full scan on the base table, keeping the REFRESH incremental.

If none of these options is used, the MV has to be computed.

• The columns in the GROUP BY clause cannot exceed the maximum key length limit.

• Must be a single block SELECT-FROM-WHERE-GROUPBY query (for example, ORDER

BY is not allowed). There is one exception to this rule; nested blocks are allowed when the inner block’s purpose is to extract a group by column (using a built-in function like substring or extract).

• Non-repeatable expressions (for example, current_time) are not supported for all types

of aggregate materialized views.

DML Operations on Materialized Views

All update operations (INSERT, UPDATE,DELETE) areblocked onall materialized views except for the DELETE operation on ON REQUEST materialized views.

Example of CREATE MATERIALIZED VIEW

• This example creates a materialized view:

CREATE MATERIALIZED VIEW PUBSCH.DETAIL_MV REFRESH ON REQUEST INITIALIZE ON REFRESH AS SELECT PUBSCH.DETAIL_TABLE.ORDERNUM, SUM(PUBSCH.DETAIL_TABLE.QUANTITY) AS

64 SQL Statements

TOTAL_AVAIL FROM PUBSCH.DETAIL_TABLE WHERE PUBSCH.DETAIL_TABLE.ORDERNUM > 1 GROUP BY PUBSCH.DETAIL_TABLE.ORDERNUM;

• This is an example of an MJV:

CREATE MATERIALIZED VIEW sales_store REFRESH ON REQUEST INITIALIZE ON REFRESH AS SELECT price, partkey, timekey, store.nam FROM sales, store WHERE sales.storekey = store.storekey;

• This is an example of a MAV on and explicit join:

CREATE MATERIALIZED VIEW sales_by_day_category REFRESH ON REQUEST INITIALIZE ON REFRESH AS SELECT part.category, time.day, SUM(price) sum_price FROM sales, part, time WHERE sales.timekey = time.timekey AND sales.partkey = part.partkey GROUP BY part.category, time.day;

CREATE MATERIALIZED VIEW Statement 65

CREATE MVGROUP Statement

The CREATE MVGROUP statement groups together materialized views that are defined on a common table to preserve database consistency.

For information on MVGROUPS, see “MVGROUPs ” (page 254)and “MAINTAIN MVGROUP”

(page 184).

CREATE MVGROUP mv-group-name

mv-group-name

specifies the materialized view group name to create.

Example of CREATE MVGROUP

• This example creates a materialized view group:

CREATE MVGROUP pgroup

66 SQL Statements

CREATE SCHEMA Statement

• “Syntax Description of CREATE SCHEMA”

• “Considerations for CREATE SCHEMA”

• “Example of CREATE SCHEMA”

The CREATE SCHEMA statement creates a Neoview SQL schema. See “Schemas” (page 247).

CREATE SCHEMA schema-clause [schema-element [, schema-element] ...]]

schema-clause is: schema

schema-element is:

table-definition

| view-definition | grant-statement | index-definition

Syntax Description of CREATE SCHEMA

schema

is a name for the new schema. A simple schema name is an SQL identifier.

schema-element

specifies the objects to be defined in the schema. The element in the statement must be in the same schema as the schema you are creating. Schema elements must appear in sequence—a schema element that depends on another schema element must be listed after that schema element.

table-definition

is a CREATE TABLE statement.

view-definition

is a CREATE VIEW statement.

grant-statement

is a GRANT statement.

index-definition

is a CREATE INDEX statement. index-definition cannot be the only schema-element.

Considerations for CREATE SCHEMA

Reserved Schema Names

Schema names that begin with DEFINITION_SCHEMA_VERSION_ and the schema named PUBLIC_ACCESS_SCHEMA are reserved for SQL. You cannot create schemas with these names. The schema PUBLIC_ACCESS_SCHEMA is created during system initialization and cannot be dropped.

These names are not reserved (you can create schema with these names): SYSTEM_SCHEMA, SYSTEM_DEFAULTS_SCHEMA, MXCS_SCHEMA.

Example of CREATE SCHEMA

• This example creates a schema:

CREATE SCHEMA myschema;

CREATE SCHEMA Statement 67

CREATE SYNONYM Statement

• “Syntax Description of CREATE SYNONYM ”

• “Considerations”

• “Versioning Considerations”

• “Example of CREATE SYNONYM”

The CREATE SYNONYM statement allows you to create synonyms (use an alternate name) for tables, views, or materialized views that can be used in queries. You can write queries that reference the synonym and later alter the synonym to point to a different object with the same structure. The advantage of this is to change the object being referenced without changing the query.

When a synonym is assigned to an object, the object can be referenced either with the synonym name or the actual name. The resultant query results will be the same.

Syntax Description of CREATE SYNONYM

CREATE SYNONYM alternate-name FOR object;

alternate-name

specifies the name of the synonym. See “Database Object Names” (page 196).

object

specifies Neoview SQL tables, views, and materialized views can be specified. See “Database

Object Names” (page 196).

Considerations

• When the object being referenced by the synonym is dropped, the synonym is also dropped.

• Only the owner of the schemaor the services ID can create,alter, or drop the alternate names

for a table.

• You can create an unlimited number of synonyms for a single object.

• Grant and Revoke commands are allowed on synonyms. The command will be applied to

the actual reference object.

• A synonym is silently mapped to the underlying referenced object for DML operations and

UPDATE STATISTICS command. This mapping does not occur for DDL and utility requests.

• When a synonym for an object is changed, any currently executing plans fail and required

name resolution and similarity check. If the similarity check fails, existing plans referencing the new object associated with the synonym name require compilation or recompilation.

• View, constraints, and trigger text cannot use synonym names in their DML text.

• Synonyms cannot be renamed. The RENAME operation is allowed on actual table names

only.

Versioning Considerations

Synonyms are only available in schema version 2000. If you need to downgrade your schema to version 1200, all synonyms must be dropped.

Example of CREATE SYNONYM

• This example creates a synonym:

CREATE SYNONYM aname FOR s-table

68 SQL Statements

CREATE TABLE Statement

• “Syntax Description of CREATE TABLE”

• “Considerations for CREATE TABLE”

• “Examples of CREATE TABLE”

The CREATE TABLE statement creates a Neoview SQL table. The CREATE VOLATILE TABLE statement creates a Neoview SQL table in a SQLsession. Volatile tables are dropped automatically when the session ends. The CREATE TABLE AS statement creates a table based on the data

CREATE TABLE Statement 69

attributes of a SELECT query and populates the table using the data returned by the SELECT query. See “Database Object Names” (page 196).

CREATE [VOLATILE] TABLE table (table-element [,table-element]...) | table-spec | like-spec }

[NO PARTITION | HASH PARTITION BY (partitioning-column, partitioning-column...)]

[MAX TABLE SIZE megabytes] [DISK POOL number] [ATTRIBUTE {NO INSERTLOG | INSERTLOG}] [{ALIGNED | PACKED}] FORMAT AS select-query

table-spec is: (table-element [,table-element]...

table-element is:

column-definition

| [CONSTRAINT constraint-name] table-constraint

column-definition is:

column data-type

[DEFAULT default | NO DEFAULT |

identity-column-specification]

[[CONSTRAINT constraint-name] column-constraint]...

identity-column-specification is:

GENERATED BY DEFAULT AS IDENTITY

data-type is: CHAR[ACTER] [(length [CHARACTERS])] [CHARACTER SET char-set-name] [UPSHIFT] | PIC[TURE] X [(length)] [CHARACTER SET char-set-name] [DISPLAY] [UPSHIFT] | CHAR[ACTER] VARYING (length) [CHARACTER SET char-set-name] [UPSHIFT] | VARCHAR (length) [CHARACTER SET char-set-name] [UPSHIFT] | PIC[TURE] [S]{ 9(integer) [V[9(scale)]] | V9(scale) } [DISPLAY [SIGN IS LEADING] | COMP]

| NCHAR [(length) [UPSHIFT] | NCHAR VARYING(length) [UPSHIFT] | NUMERIC [(precision [,scale])] [SIGNED|UNSIGNED] | SMALLINT [SIGNED|UNSIGNED] | INT[EGER] [SINGED|UNSIGNED] | LARGEINT | DEC[IMAL] [(precision [,scale])] [SIGNED|UNSIGNED | FLOAT [(precision)] | REAL | DOUBLE PRECISION | DATE | TIME [(time-precision)] | TIMESTAMP [(timestamp-precision)] | INTERVAL { start-field TO end-field | single-field }

70 SQL Statements

default is:

literal

| NULL | CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP

column-constraint is: NOT NULL | PRIMARY KEY [ASC[ENDING] | DESC[ENDING]] | CHECK (condition)

column-list is:

column-name [,column-name]...

table-constraint is:

PRIMARY KEY (key-column-list) |CHECK (condition) [NOT CASESPECIFIC]

key-column-list is:

column-name [ASC[ENDING] | DESC[ENDING]]

[,column-name [ASC[ENDING] | DESC[ENDING]]]...

like-spec is: LIKE source-table [include-option]...

include-option is:

WITH CONSTRAINTS | WITH PARTITIONS

Syntax Description of CREATE TABLE

table

is the ANSI logical name for the new table and must be unique among names of tables, views, and procedures within its schema.

NO INSERTLOG | INSERTLOG

specifies whether update and delete operations are recorded in the table’s IUD log, if one exists.

When a table is defined with the INSERTLOG attribute, logging operations ignore delete and update operations and log inserts only.

This logging is needed to maintain ON REQUEST materialized views. If no ON REQUEST materialized views are defined on the table, this attribute has no affect.

NO PARTITION

creates a non-partitioned table. To create a non-partitioned table, specify the NO PARTITION option in the CREATE TABLE statement.

If the NO PARTITION option is not specified, a partitioned table is created. When a partitioned table is created, the table is automatically partitioned across all the disk volumes within a disk pool.

MAX TABLE SIZE megabytes

specifies the table size as a number of megabytes.

If you do not specify MAX TABLE SIZE, a table is created with the following default characteristics:

• For a partitioned table, the space allocated after you enter the first row is 100 MB times number of partitions. The maximum size a partitioned table can reach is the size of the

CREATE TABLE Statement 71

disk at the time of table creation. When a partitioned table is created, the table is automatically partitioned across all the disk volumes on the system.

• For a non-partitioned table, the space allocated after you enter the first row is 100 MB.

DISK POOLpool_number

causes the table to be created in the specified pool number. The value of pool_number is an unsigned integer. Zero (0) is not a valid pool number. If pool_number is greater than the number of pools or if pool_number is an invalid number, an error is generated.

ALIGNED | PACKED FORMAT

specifies if the table should be created in the new or old record format.

ALIGNED FORMAT is the default format if no keyword is supplied. In this format, the columns are all properly aligned and records are aligned with data blocks.

PACKED FORMAT is the format where all columns are packed together and records are packed together within data blocks.

Indexes and materialized views do not have ALIGNEDand PACKED format but they inherit the format of the underlying table.

If the row format phrase appears more than once within the ATTRIBUTES clause, an error is issued.

AS select-query

specifies a select query which is used to populate the created table. A select query can be any SQL select statement.

column data-type

specifies the name and data type for a column in the table. At least one column definition is required in a CREATE TABLE statement.

data-type is the data type of the values that can be stored in column. A default value must be of the same type as the column, including the character set for a character column. “Data

Types” (page 197).

DEFAULT default | NO DEFAULT

specifies a default value for the column or specifies that the column does not have a default value. “DEFAULT Clause” (page 258).

identity-column-specification

indicates that a particular column is an IDENTITY column. The GENERATED BY DEFAULT indicates that the system will generate values for this column by default. See “Generating

Unique Values For a Column” (page 75).

CONSTRAINT constraint

specifies a name for the column or table constraint. constraint must have the same schema as table and must be unique among constraint names in its schema. If you omit the schema portions of the name you specify in constraint, Neoview SQL expands the constraint name by using the schema for table. See “Database Object Names” (page 196).

NOT NULL

is a column constraint that specifies that the column cannot contain nulls. If you omit NOT NULL, nulls are allowed in the column. If you specify both NOT NULL and NO DEFAULT, each row inserted in the table must include a value for the column. See “Null” (page 230).

PRIMARY KEY [ASC[ENDING] | DESC[ENDING]] or PRIMARY KEY (key-column-list)

is a column that specifies a column or set of columns as the primary key for the table. key-column-list cannot include more than one occurrence of the same column.

ASCENDING and DESCENDING specify the direction for entries in one column within the key. The default is ASCENDING.

72 SQL Statements

The PRIMARY KEY value in each row of the table must be unique within the table. Columns within a PRIMARY KEY cannot contain nulls. A PRIMARY KEY defined for a set of columns implies that the column values are unique and not null. You can specify PRIMARY KEY only once on any CREATE TABLE statement.

Neoview SQL uses the primary key as the clustering key of the table in order to avoid creating a separate, unique index to implement the primary key constraint.

If you do not specify a PRIMARY KEY constraint, Neoview SQL cannot implement the primary key as the clustering key.

CHECK (condition)

is a constraint that specifies a condition that must be satisfied for each row in the table. See

“Search Condition” (page 248).

Neoview SQL checks the condition whenever an operation occurs that might affect its value. The operation is allowed if the predicate in the condition evaluates to TRUE or null but prohibited if the predicate evaluates to FALSE.

You cannot refer to the CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP function in a CHECK constraint, and you cannot use subqueries in a CHECK constraint. CHECK constraints cannot contain non-ISO88591 string literals.

NOT CASESPECIFIC

is a column constraint that specifies that the column contains strings that are not case specific. The default is CASESPECIFIC. Comparison between two values is done in a case insensitive way only if both are case insensitive. This applies to comparison in a binary predicate, LIKE predicate, and POSITION/REPLACE string function searches. See “Examples of CREATE

TABLE” (page 78).

LIKE source-table [include-option]...

directs Neoview SQL to create a table like the existing table, source-table, omitting constraints (with the exception of the NOT NULL and PRIMARY KEY constraints), and partitions unless include-option clauses are specified.

source-table

is the ANSI logical name for the existing table and must be unique among names of tables, views, and procedures within its schema.

The include-option clauses are specified as:

WITH CONSTRAINTS

directs Neoview SQL to use constraints from source-table. Constraint names for table are randomly generated unique names.

When you perform a CREATE TABLE LIKE, whether or not you include the WITH CONSTRAINTS clause, the target table will have all the NOT NULL column constraints that exist for the source table with different constraint names.

WITH PARTITIONS

directs Neoview SQL to use partition definitions from source-table. Each new table partition resides on the same volume as its original source-table counterpart. The new table partitions do not inherit partition names from the original table. Instead, Neoview SQL generates new names based on the physical file location.

If you specify the LIKE clause and the PARTITION file-option, you cannot specify WITH PARTITIONS.

Considerations for CREATE TABLE

You can create partitioned and non-partitioned tables. To create a non-partitioned table, specify the NO PARTITION option with the CREATE TABLE command.

CREATE TABLE Statement 73

Considerations for CREATE VOLATILE TABLE

• Volatile temporary tables are very similar to persistent tables, except that the life span of

the table is limited to the session that created the table. Once the session ends, the tables are automatically dropped.

• Volatile temporary table are closely linked to the session. Their namespace is unique across

multiple concurrent sessions, and therefore allow multiple sessions to use the same volatile temporary table names simultaneously without any conflicts.

• Volatile tables support creation of indexes.

• Volatile tables are partitioned by the system.

• Statistics are maintained for volatile tables much like for persistent tables.

• Volatile tables can be created and accessed using one part name or two part names. However,

you must use the same name (one part or two part) for any further DDL or DML statements on the created volatile table. See “Examples of CREATE TABLE” (page 78).

Considerations for CREATE TABLE LIKE

The CREATE TABLE LIKE statement does not create views, owner information, or privileges for the new table based on the source table. Privileges associated with a new table created by using the LIKE specification are defined as if the new table is created explicitly by the current user. If the source table has any unique or droppable primary key constraints, Neoview SQL creates indexes for them on the target table. Other indexes on the source table are not created on the target table.

Reserved Table Names

Table names prefixed by the name of a user metadata table are reserved. You cannot create tables with such names. For example, you cannot create tables that are prefixed by these names:

• HISTOGRAMS

• HISTOGRAM_INTERVALS

• MVS_TABLE_INFO_UMD

• MVS_UMD

• MVS_USED_UMD

Authorization and Availability Requirements

To create a table, you must own its schema.

Limits for Tables

You can create tables and indexes with rows that are 32,708 bytes in size and blocks that are 32K bytes in size. The default block size for all create table and index operations is 32768 (32K) byte block size (in all cases).

There are no tools provided for explicitly migrating an existing 4KB block size table to a 32KB block size table. If you want a larger block size, a new table must be created and data copied from the old table to the new table.

Table 2-1 describes the block size, the size available to Neoview SQL, and the size available to

the user.

Table 2-1 Maximum Row Sizes Available

Max row size available to usersMax row size available to SQLBlock size

403640404096

327083271232768

74 SQL Statements

Tables and Triggers

If you plan to create triggers on a table, its primary key length cannot exceed 2032 bytes. A table which will not have triggers can have a primary key of 2048 bytes. For details about this limit, see “Triggers and Primary Keys” (page 84).

Calculating Row Size

The row size required for Neoview SQL tables is dependent on the number of variable length columns and number of columns that can be null.

The following table has four nullable columns and one variable length column. The calculation for the size of each of the fields is listed beside the column definition. In addition to the size of each field, there is an additional four bytes added at the beginning of each row inserted for a maximum size of 310 bytes.

Create Table CustOrder (orderKey largeint not null, -- 8 bytes itemNum smallint not null, -- 2 bytes orDate date not null, -- 4 bytes shipDate date, -- 6 bytes shipMode char(12), -- 14 bytes rtnDate date, -- 6 bytes rtnReason varchar(256), -- 266 bytes primary key(orderKey, itemNum) ;

To calculate this for your specific table, use this formula:

(num of variable fields * 8) + (num of nullable fields * 2) + (num of ‘not null droppable’ fields *

2) + 4 + (size of all fields based on type)

Columns declared as ‘not null’ have no additional size otherthen the size of the data type declared.

Creating Partitions Automatically

When creating a table users can specify that the table is not partitioned using the NO PARTITION clause. The default for the table is to be partitioned.

You may also specify the MAX TABLE SIZE clause that is used to allocate space on the disk. It is advisable that you specify a value, even if it is approximate, because it helps to allocate the appropriate amount of space for the table. If this clause is not specified, Neoview SQL will decide. If the table is partitioned then the table is automatically partitioned across all the disk volumes on the system.

Record Format

The keyword FORMAT is a non-reserved word.

If the Row Format phrase appears more than once within the ATTRIBUTES clause, Neoview SQL issues an error.

The Row Format phrase can appear in any order within the ATTRIBUTES clause.

The Row Format phrase cannot appear within an ALTER statement.

Generating Unique Values For a Column

You can use IDENTITY columns to automatically generate unique values. The values are unique across all partitions of the table for the IDENTITY column. IDENTITY columns are declared in the CREATE TABLE statement. IDENTITY columns can be used as surrogate keys. They can also be used to uniquely identify records with the same key.

CREATE TABLE Statement 75

RULES

• Only one IDENTITY column can be used in a table.

• Values for the IDENTITY column are generated by default. If you specify a value for the

IDENTITY column, the system uses that value and does not generate a unique value for that row.

• The IDENTITY column must have a NOT NULL constraint. If not specified, the system will

implicitly add the constraint.

• The IDENTITY column property is retained on the target table for CREATE TABLE LIKE...

statements.

CONSIDERATIONS

• IDENTITY columns can be the PRIMARY KEY or can be part of a compound clustering key.

You must have a unique index on the IDENTITY column. It is recommended that you assign the IDENTITY column as the clustering key to avoid an extra index on the table. This avoids index maintenance.

• The IDENTITY column can be the partitioning key or can be part of a compound partitioning

key.

• You can specify a SIGNED LARGEINT value for an IDENTITY column. The system generates

only positive values greater than 1023.

RESTRICTIONS

These restrictionsapply to a column defined as an IDENTITY column. Appropriate error messages are generated for each of these restrictions.

• You cannot add an IDENTITY column using the ALTER TABLE statement.

• INSERT...SELECT operations are supported.

• You cannot define a trigger to insert into an IDENTITY column.

• An IDENTITY column can only be defined on a LARGEINT column. You can specify a

SIGNED LARGEINT value if you want to use the negative values.

• Expressions are not allowed as IDENTITY column values. You must specify the keyword

DEFAULT or supply a LARGEINT value.

• For an IDENTITY column, the tuple list cannot have mixed user and DEFAULT values

specified. You must specify values for all in the tuple list or specify DEFAULT for all in the tuple list. For example, error 3414 is raised in the following case:

INSERT INTO t id_s values (DEFAULT,’1’,1), (DEFAULT,’2’,2), (50,’3’,3), (DEFAULT,’4’,4) (DEFAULT,’5’,5);

EXAMPLES

• This example shows how to create an IDENTITY column for a simple table. In this example,

the column surrogate_key is defined as the IDENTITY column and is the primary key (clustering key).

CREATE TABLE t_id_S (surrogate_key LARGEINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, name CHAR (5) NOT NULL, primary key(surrogate_key) ) HASH PARTITION BY(surrogate_key);

• This example shows IDENTITY column surrogate_key as part of the clustering key. The

surrogate key column must have a unique index on it.

CREATE TABLE t_id (surrogate_key LARGEINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,

76 SQL Statements

name CHAR (256) NOT NULL, order_number INT UNSIGNED NOT NULL, primary key (surrogate_key,order_number) ) HASH PARTITION BY(surrogate_key, order_number); create unique index sk_idx on t_id(surrogate_key);

• This example shows the IDENTITY column surrogate_key as the partitioning key. Note

that for this release, the surrogate key column must have a unique index on it:

NOTE: In Neoview SQL, the partitioning key must be a subset of the clustering key. In the

case of a table with a single column clustering key, the partitioning key must be the same as the clustering key.

CREATE TABLE t_id (surrogate_key LARGEINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, name CHAR (256) NOT NULL, order_number INT UNSIGNED NOT NULL, primary key (surrogate_key,order_number) ) HASH PARTITION BY(surrogate_key);

create unique index sk_idx on t_id(surrogate_key);

• This statement fails with an error stating that only LARGEINT columns can be defined as

an IDENTITY column.

CREATE TABLE T (surrogate_key CHAR(64) GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY, name CHAR (256) NOT NULL, order_number INT UNSIGNED NOT NULL) HASH PARTITION BY(surrogate_key);

• This statement fails with an error stating that a table can have only one IDENTITY column.

CREATE TABLE T (surrogate_key LARGEINT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY, name CHAR (256) NOT NULL, order_number LARGEINT GENERATED BY DEFAULT AS IDENTITY NOT NULL) HASH PARTITION BY(surrogate_key);

Neoview SQL Extensions to CREATE TABLE

This statement is supported for compliance with ANSI SQL:1999 Entry Level. Neoview SQL extensions to the CREATE TABLE statement are ASCENDING, DESCENDING, PARTITION, MAXTABLESIZE, and ATTRIBUTE clauses.

DISK POOL

The DISK POOL attribute allows disks to be divided into disk pools.

Considerations for DISK POOL

• The default number of pools for systems up to 256 disks is 1 and for systems beyond 256

disks, the default number is 2.

• Each disk pool has the same number of disks.

• The number of disks in a pool is the total disks divided by the number of pools.

• Disks within a pool are evenly distributed among segments.

• Distribution of disks to disk pools — For 2 pools with 2 disks per CPU, the first disk of every

CPU belongs to pool 1 and the second disk of every CPU belongs to pool 2.

• Tables can be allocated to disks that belong to the requested disk pool number.

CREATE TABLE Statement 77

• By default, tables are assigned to disk pools in a round robin fashion.

• The default number of partitions created are as many number of disks in a disk pool.

• A non partitioned table can be created within a disk pool using the NO PARTITION clause.

Restrictions for DISK POOL

DISK POOL cannot be used with volatile tables, materialized views, indexes, and CREATE TABLE LIKE.

Examples of CREATE TABLE

• This example creates a table. The clustering key is the primary key.

CREATE TABLE SALES.ODETAIL ( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL, partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL, unit_price NUMERIC (8,2) NO DEFAULT NOT NULL, qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL, PRIMARY KEY (ordernum, partnum) );

• This example creates a table like the JOB table with the same constraints:

CREATE TABLE SAMDBCAT.PERSNL.JOB_CORPORATE LIKE SAMDBCAT.PERSNL.JOB WITH CONSTRAINTS;

• This example creates a volatile table:

CREATE VOLATILE TABLE vtable (a int);

• This is an example of one-part name usage:

CREATE VOLATILE TABLE vtable(a int); INSERT INTO vtable values(1); SELECT * from vtable; CREATE VOLATILE INDEX vindex on vtable(a); DROP VOLATILE INDEX vindex; DROP VOLATILE TABLE vtable;

• This is an example of two-part name usage:

CREATE VOLATILE TABLE “mysch”.vtable(a int); INSERT INTO mysch.vtable values(1); SELECT * from mysch.vtable; CREATE VOLATILE INDEX vindex on mysch.vtable(a); DROP VOLATILE INDEX vindex; DROP VOLATILE TABLE mysch.vtable;

• Example of DISK POOL usage. For a system with 256 processors and 2 disks per processor,

the default number of poolsis 2. Each pool contains 256 disks. T1 is created with 256 partitions and assigned to a random disk pool.

CREATE TABLE t1 (a int not null, primary key(a));

• Example of DISK POOL usage. Example of DISK POOL usage. For a system with 256

processors and 2 disks per processor, the default number of pools is 2. Each pool contains 256 disksT2 is created with 256 partitions and allocated to the disk in disk pool 2.

CREATE TABLE t2 (a int not null, primary key(a)) DISK POOL 2;

• Example of ALIGNED format usage:

CREATE TABLE t157t02 (c int not null constraint t157t02_1 unique) no partition attribute blocksize 4096, aligned format;

• This is an example of NOT CASESPECIFIC usage:

CREATE TABLE T (a char(10) NOT CASESPECIFIC, b char(10)); INSERT INTO T values (‘a’, “a”);

78 SQL Statements

• A row is not returned in this example. Constant ‘A’ is case sensitive, whereas column ‘a’ is

insensitive.

SELECT * FROM T WHERE a = ‘A’;

• The row is returned in this example. Both sides are case sensitive.

SELECT * FROM T WHERE a = ‘A’ (not casesensitive);

• A row is not returned in this example. A case sensitive comparison is done since column ‘b’

is case sensitive.

SELECT * FROM T WHERE b = ‘A’;

• A row is not returned in this example. A case sensitive comparison is done since column ‘b’

is case sensitive.

SELECT * FROM T WHERE b = ‘A’ (not casesensitive);

Examples of CREATE TABLE AS

This section shows the column attribute rules used to generate and specify the column names and data types of the table being created.

• If column-attributes are not specified, the select list items of the select-query are used

to generate the column names and data attributes of the created table. If the select list item is a column, then it is used as the name of the created column. For example:

create table t as select a,b from t1

Table t has 2 columns named (a,b) and the same data attributes as columns from table t1.

• If the select list item is an expression, it must be renamed with an AS clause. An error is

returned if expressions are not named. For example:

create table t as select a+1 c from t1

Table t has 1 column named (c) and data attribute of (a+1)

create table t as select a+1 from t1

An error is returned, expression must be renamed.

• If column-attributes are specified and contains datatype-info, then they override

the attributes of the select items in the select query. These data attributes must be compatible with the corresponding data attributes of the select list items in the select-query.

create table t(a int) as select b from t1

Table t has one column named “a” with datatype “int”.

create table t(a char(10)) as select a+1 b from t1;

An error is returned since the data attribute of column “a”, a char, does not match the data attribute of the select list item “b” a numeric.

• If column-attributes are specified and they only contain column-name, then the

specified column-name override any name that was derived from the select query.

create table t(c,d) as select a,b from t1

Table t has 2 columns, c and d, which has the data attributes of columns a and b from table t1.

• If column-attributes are specified, then they must contain attributes corresponding to

all select list items in the select-query. An error is returned, if there is a mismatch.

create table t(a int) as select b,c from t1

An error is returned. Two items need to be specified as part of the table-attributes.

• column-attributes must specify either the column-name datatype-info pair or just

the column-name for all columns. You cannot specify some columns with just the name and others with name and datatype.

CREATE TABLE Statement 79

create table t(a int, b) as select c,d from t1

An error is returned.

This section shows the file attributes, such as partitioning information and clustering keys, which can be specified for the table being created. All the file options that can be specified during a regular CREATE statement can be specified during a CREATE TABLE AS statement.

• If table-attributes are not specified, the table is created as a single partitioned table.

create table t as select * from t1

• Any other file/table options that are allowed and supported by a regular CREATE statement,

can be specified for a CREATE TABLE AS statement and are used as follows:

create table t max table size 1000 as select * from t1

create table t(a int not null) hash partition by (a) as select * from t1

80 SQL Statements

CREATE TRIGGER Statement

• “Syntax Description of CREATE TRIGGER”

• “Considerations for CREATE TRIGGER”

• “Examples of CREATE TRIGGER”

The CREATE TRIGGER statement is used to create triggers on SQL tables. A trigger is a mechanism that sets up the database system to perform certain actions automatically in response to the occurrence of specified events.

CREATE TRIGGER trigger-name {BEFORE | AFTER} {INSERT | DELETE | UPDATE [OF (columns)]} ON table-name [REFERENCING old-new-alias-list ] [FOR EACH {ROW | STATEMENT}] [ WHEN (search-condition)]

triggered-SQL-statement;

columns is:

column-name, columns | column-name

old-new-alias-list is:

old-new-alias, old-new-alias | old-new-alias

old-new-alias is :

OLD [ROW] [AS] correlation-name | NEW [ROW] [AS] correlation-name | OLD [TABLE] [AS] table-alias | NEW [TABLE] [AS] table-alias

triggered-SQL-statement is:

atomic-compound-statement is:

BEGIN ATOMIC

{triggered-SQL-statement | IF-statement}...

END;

signal-statement is: SIGNAL SQLSTATE quoted-sqlstate (quoted-string-expr);

set-new-statement is: SET correlation-name.column-name = value-expression

Syntax Description of CREATE TRIGGER

trigger-name

specifies the name of the trigger to create. See “Database Object Names” (page 196).

column-name

specifies the nameof the new column that correlates tothe row to be modified. See “Database

Object Names” (page 196).

table-name

specifies the name of the trigger subject table. See “Database Object Names” (page 196).

FOR EACH { ROW | STATEMENT }

specifies whether the trigger is based on a row or a statement. If you do not specify this clause, the default is ROW for a BEFORE trigger and STATEMENT for an AFTER trigger.

CREATE TRIGGER Statement 81

old-new-alias

is the list of correlation name of table aliases used by a trigger.

correlation-name

is the name of the old or new row acted upon by the trigger.

table-alias

is the name of the old or new table acted upon by the trigger.

search-condition

is the condition that, when true, activates this trigger.

triggered-SQL-statement

is the SQL statement to be performed when this trigger is activated.

searched-update-statement

is an update statement to be performed when this trigger is activated.

searched-delete-statement

is a delete statement to be performed when this trigger is activated.

insert-statement

is an insert statement to be performed when this trigger is activated.

signal-statement

is a statement to be sent to the SIGNAL statement. The signal statement can be used as a trigger action to allow a trigger execution to raise an exception that causes the triggered, as well as the triggering statement, to fail.

set-new-statement

is an assignment statement that can be used as a BEFORE-trigger action to assign values to transition variables representing columns in the subject table modified by the triggering action.

quoted-sqlstate

is the four-digit SQLSTATE prefixed with an ‘s’ or “S” and delimited by single quotes to be passed to SIGNAL.

quoted-string-expr

is a string expression delimited by single quotes.

argument-list

is the list of arguments to pass to a stored procedure.

IF-statement

is a SQL IF statement. The SQL IF statement is a compound statement that provides conditional execution based on the truth value of a conditional expression.

Considerations for CREATE TRIGGER

Triggers support up to 16 levels of recursion. Triggers have their own namespace.

The LIKE option of CREATE TABLE ignores triggers.

If you are running CREATE TRIGGER from Neoview Script, you must enter a / (slash) on a newline to terminate the command. See Running SQL Statements in Chapter 4 of the Neoview Script Guide.

Authorization and Availability Requirements

To create a trigger, you must own the schema where the trigger is defined and the schema where the subject table of the schema resides and you must have REFERENCES privileges on the columns used on the referenced table. Otherwise, you must be the services ID.

Trigger Types

You can configure triggers as BEFORE or AFTER types. When a triggering statement occurs, this is the order of execution:

82 SQL Statements

1. BEFORE triggered statements

2. Triggering statement

3. AFTER triggered statements

Execution of a statement is considered to be complete only when all cascaded triggers are complete. When multiple triggers are activated by the same event (that is, a conflict set), the next trigger from the original conflict set is considered only after the execution of cascaded triggers of a specific trigger is complete (depth-first execution). Within a conflict set, the order of execution is by timestamp of creation of the corresponding trigger. Older triggers are executed first.

Statement triggers and row triggers can participate in the same conflict set and can cascade each other. Therefore, they can appear intertwined.

Triggers use transition tables or transition variables to access old and new states of the table or row. Statement triggers use transition tables. Row triggers use transition variables. This table summarizes the transition variables that different trigger types can use:

Statement Trigger Can Use:Row Trigger Can Use:Triggering Event and Activation

Time

InvalidNEW ROWBEFORE INSERT

InvalidOLD ROW, NEW ROWBEFORE UPDATE

InvalidOLD ROWBEFORE DELETE

NEW TABLENEW ROWAFTER INSERT

OLD TABLE, NEW TABLEOLD ROW, NEW ROWAFTER UPDATE

OLD TABLEOLD ROWAFTER DELETE

BEFORE Triggers

BEFORE triggers are used for one of these purposes:

• To generate an appropriate signal when an insert, update, or delete operation is applied

and a certain condition is satisfied (using the SIGNAL statement as an action.)

• To massage data prior to the insert or update operation that caused the trigger to be activated.

BEFORE-type trigger operations are exercised as tentative executions. The triggering statement is executed but assigns values to the NEW ROW transition variables rather than to the subject table. That table appears not to be affected by the tentative execution. When it is accessed by the trigger action, it shows values in place before the action of the trigger. Because BEFORE-triggers can only be row triggers, they use transition variables to access old and new states of the row.

Before-type triggers do not modify tables. However, by using a SET statement, they can assign new values only to the NEW ROW transition variables. As a result, a BEFORE-type trigger can override the effect of the original triggering statement.

The unique features of BEFORE-type triggers are:

• The triggering statement executes only after the trigger is executed.

• Only row granularity is allowed.

• Only the NEW ROW transition variable can be modified.

• BEFORE-type triggers cannot be cascading. A cascading trigger is a trigger whose action

includes trigger events.

One of the key differences between BEFORE- and AFTER-type triggers is their relationship to constraints. A BEFORE-type trigger can prevent the violation of a constraint, whereas an AFTER-type trigger cannot, because it is executed after the constraints are checked. BEFORE-type triggers are used to condition input data, while AFTER-type triggers encode actual application logic.

CREATE TRIGGER Statement 83

Restrictions on Triggers

• The trigger feature does not allow the use of:

— Positioned deletes and updates as triggered statements. — Subqueries in search-condition for AFTER triggers (but they are allowed in

search-condition for BEFORE triggers.)

• To create a trigger on a given table, the name of the table should be at least six characters

less than the maximum length of a valid table name (128 characters).

• There is a compile time limit of 256 triggers for each statement. This means if the statement

has the potential to activate more than 256 triggers (not necessarily different triggers, there could be one or more recursive triggers), an error is raised. The restriction applies regardless of whether a trigger is disabled or enabled and whether the trigger may or may not be activated.

Recompilation and Triggers

User applications that change (INSERT, UPDATE, or DELETE) information in a table require compilation when a trigger with a matching event is added or dropped. User applications that use a SELECT on the subject table do not require recompilation. User applications do not require an SQL compilation when a trigger ischanged from DISABLED to ENABLED, or from ENABLED to DISABLED, using the ALTER TRIGGER statement. User applications require SQL recompilations only when triggers are added or dropped. No source code changes or language compilations are required.

Triggers and Primary Keys

Suppose you create this table:

CREATE TABLE t1( c1 varchar(2040) NOT NULL, c2 int, c3 int, c4 char(3), c5 char(3), primary key (c1) ); CREATE TABLE t2 (c1 char(3), c2 char(3));

When you try to create a trigger on this table using these commands, you receive errors:

*** ERROR[1085] The calculated key length is greater than 2048 bytes.

*** ERROR[11041] Temporary table could not be created! Check default partitions.

This is because of the way that trigger temporary tables are created. Trigger temporary tables are auxiliary tables used by triggers to store transient data it uses during its execution. This temporary table is created with two more columns than its corresponding subject table has, whose combined length is 16 bytes. The two added columns, along with the subject table’s primary key, form the primary key of the temporary table. This primary key is too long.

If you change column c1 of table t1 from varchar(2040) to varchar(2000), the primary key length is now 2000 bytes, and the CREATE TRIGGER statement completes successfully. Keep this limit in mind when you create tables.

84 SQL Statements

Examples of CREATE TRIGGER

Before and After Triggers

Suppose that you have a database to record patients’ vital signs and drugs prescribed for them. The database consists of these tables:

• vital_signs, which records vital signs at each visit

• prescription, which records prescriptions written for each patient

• generic_drugs, which lists generic drug equivalents for brand-name drugs

The prescription table is created like this:

CREATE TABLE prescription ( id INTEGER NOT NULL pat_id INTEGER NOT NULL, issuing_phys_id INTEGER NOT NULL, date_prescribed DATE DEFAULT NULL, drug VARCHAR(80) DEFAULT NULL, record_id INTEGER NOT NULL, dosage VARCHAR(30) NOT NULL, frequency VARCHAR(30) DEFAULT NULL, refills_remaining INTEGER DEFAULT NULL, instructions VARCHAR(255) DEFAULT NULL, primary key (id));

You can create a BEFORE trigger on prescription so that when a prescription is entered, if the prescribeddrug is found ingeneric_drugs, a generic drug is substituted for the brand-name drug, and the instructions for the drugs are updated:

CREATE TRIGGER alternate_drug BEFORE INSERT ON prescription REFERENCING NEW AS newdrug FOR EACH ROW WHEN (upshift(newdrug.drug) IN (SELECT upshift(generic_drugs.drug) FROM generic_drugs)) SET newdrug.drug = (SELECT upshift(generic_drugs.alternate_drug) FROM generic_drugs WHERE upshift(newdrug.drug) = upshift(generic_drugs.drug)), newdrug.instructions = newdrug.instructions || ' Prescribed drug changes to alternative drug.';

You can create an AFTER trigger on vital_signs so that when that table is updated, SQL checks the patient’s weight and height. Based on their values, this trigger might add a record to prescription to create a new prescription for a weight-loss drug with instructions that indicate that this is a free sample:

CREATE TRIGGER free_sample AFTER INSERT ON vital_signs REFERENCING NEW AS sample FOR EACH ROW WHEN (sample.weight > 299 and sample.height < 69) INSERT INTO prescription (id, pat_id, issuing_phys_id, record_id, date_prescribed, drug, dosage, frequency, refills_remaining, instructions) VALUES ((SELECT sequence + 1 from prescription_seq), (SELECT pat_id FROM record WHERE sample.id = record.vital_id), (SELECT phys_id FROM record WHERE sample.id =

CREATE TRIGGER Statement 85

record.vital_id), (SELECT record.id FROM record WHERE sample.id = record.vital_id), CURRENT_DATE, 'POUND OFF', '200 mg', '1 pill 1 hour before each meal', 0, 'Free sample no refills' );

86 SQL Statements

CREATE VIEW Statement

• “Syntax Description of CREATE VIEW”

• “Considerations for CREATE VIEW”

• “Examples of CREATE VIEW”

The CREATE VIEW statement creates a Neoview SQL view. See “Views” (page 254).

CREATE VIEW view [(column-name ] [,column-name ...)] AS query-expr [WITH [CASCADED] CHECK OPTION]

query-expr is:

non-join-query-expr | joined-table

non-join-query-expr is:

non-join-query-primary | query-expr UNION [ALL] query-term

query-term is:

non-join-query-primary | joined-table

non-join-query-primary is:

simple-table | (non-join-query-expr)

joined-table is:

table-ref [NATURAL] [join-type] JOIN table-ref [join-spec]

join-type is:

INNER | LEFT [OUTER] | RIGHT [OUTER]

join-spec is: ON condition

simple-table is: VALUES (row-value-const) [,(row-value-const)]... | TABLE table | SELECT [ALL | DISTINCT] select-list FROM table-ref [,table-ref]... [WHERE search-condition] [SAMPLE sampling-method] [TRANSPOSE transpose-set [transpose-set]... [KEY BY key-colname]]... [SEQUENCE BY colname [ASC[ENDING] | DESC[ENDING]] [,colname [ASC[ENDING] | DESC[ENDING]]]...] [GROUP BY {colname | colnum} [,{colname | colnum}]...] [HAVING search-condition]

row-value-const is:

row-subquery | expression [,expression]...

Syntax Description of CREATE VIEW

view

specifies the name for the view to create. See “Database Object Names” (page 196).

(column-name [,column-name ]...)

specifies names for the columns of the view and, optionally, headings forthe columns. Column names in the list must match one-for-one with columns in the table specifiedby query-expr.

If you omit this clause, columns in the view have the same names as the corresponding columns in query-expr. You must specify this clause if any two columns in the table specified by query-expr have the same name or if any column of that table does not have a name. For example, this query expression SELECT MAX(salary), AVG(salary) AS average_salary FROM employee the first column does not have a name.

CREATE VIEW Statement 87

column-name

specifies the name for a column in the view. column-name is an SQL identifier. column-name must be unique among column namesin the view and cannot be a reserved

word. It can contain a reserved word if it is delimited.

If you do not specify this clause, columns in the view have the same names as the columns in the select list of query-expr.

No two columns of the view can have the same name; if a view refers to more than one table and the select list refers to columns from different tables with the same name, you must specify new names for columns that would otherwise have duplicate names.

AS query-expr

specifies the columns for the view and sets the selection criteria that determines the rows that make up the view. This query-expr cannot contain non-ISO88591 string literals. For the syntax description of query-expr, see “SELECT Statement” (page 141).

WITH [CASCADED] CHECK OPTION

specifies that no row can be inserted or updated in the database through the view unless the row satisfies the view definition—that is, the search condition in the WHERE clause of the query expression must evaluate to true for any row that is inserted or updated.

If you omit this option, a newly inserted row or an updated row need not satisfy the view definition, which means that such a row can be inserted or updated in the table but does not appear in the view. This check is performed each time a row is inserted or updated.

WITH CHECK OPTION does not affect the query expression; rows must always satisfy the view definition. CASCADED is an optional keyword; WITH CHECK OPTION has the same effect.

Considerations for CREATE VIEW

• You can specify GROUP BY using ordinals to refer to the relative position within the SELECT

list. For example, GROUP BY 3, 2, 1.

• Dynamic parameters are not allowed.

Reserved View Names

View names prefixed by the name of a UMD table are reserved. You cannot create views with such names. For example, you cannot create a view named HISTOGRAMS_MYVIEW.

Effect of Adding a Column on View Definitions

The addition of a column to a table has no effect on any existing view definitions or conditions included in constraint definitions. Any implicit column references specified by SELECT * in view or constraint definitions are replaced by explicit column references when the definition clauses are originally evaluated.

Authorization and Availability Requirements

To create a view, you must have select privileges for the objects underlying the view.

When you create a view on a single table, the owner of the view is automatically given all privileges WITH GRANT OPTION on the view. However, when you create a view that spans multiple tables, the owner of the view is given only SELECT privileges WITH GRANT OPTION. If you try to grant privileges to another user on the view other than SELECT you will receive a warning that you lack the grant option on that privilege.

Updatable and Non-Updatable Views

Single table views can be updatable. Multi-table views cannot be updatable.

88 SQL Statements

To define an updatable view, a query expression must also meet these requirements:

• It cannot contain a JOIN, UNION, or EXCEPT clause.

• It cannot contain a GROUP BY or HAVING clause.

• It cannot directly contain the keyword DISTINCT.

• The FROM clause must refer to exactly one table or one updatable view.

• It cannot contain a WHERE clause that contains a subquery.

• The select list cannot include expressions or functions or duplicate column names.

Examples of CREATE VIEW

• This example creates a view on a single table without a view column list:

CREATE VIEW SALES.MYVIEW1 AS SELECT ordernum, qty_ordered FROM SALES.ODETAIL;

• This example creates a view with a column list:

CREATE VIEW SALES.MYVIEW2 (v_ordernum, t_partnum) AS SELECT v.ordernum, t.partnum FROM SALES.MYVIEW1 v, SALES.ODETAIL t;

• This example creates a view from two tables by using an INNER JOIN:

CREATE VIEW MYVIEW4 (v_ordernum, v_partnum) AS SELECT od.ordernum, p.partnum FROM SALES.ODETAIL OD INNER JOIN SALES.PARTS P ON od.partnum = p.partnum;

CREATE VIEW Statement 89

DELETE Statement

• “Syntax Description of DELETE”

• “Considerations for DELETE”

• “Examples of DELETE”

The DELETEstatement is a DML statement that deletes a row or rows from a table or an updatable view. Deleting rows from a view deletes the rows from the table on which the view is based. DELETE does not remove a table or view, even if you delete the last row in the table or view.

The two forms of the DELETE statement are:

• Searched DELETE—deletes rows whose selection depends on a search condition

For the searched DELETE form, if there is no WHERE clause, all rows are deleted from the table or view.

Searched DELETE is:

DELETE [NOMVLOG][WITH NO ROLLBACK] FROM table

| STREAM (table) [AFTER LAST ROW]

[SET ON ROLLBACK set-roll-clause [,set-roll-clause ]...]

[WHERE search-condition ]

[[FOR] access-option ACCESS]

set-roll-clause is:

column-name = expression

access-option is:

READ COMMITTED | SERIALIZABLE | REPEATABLE READ | SKIP CONFLICT

Syntax Description of DELETE

NOMVLOG

specifies that the operation will not be recorded in the materialized view log table. Once this option is used, a materialized view will no longer be consistent with its base tables and re computing it produces a different result.

WITH NO ROLLBACK

specifies that the transaction within which the deletestatement is executing does not rollback if a transaction aborts. If you specify this option when AUTOCOMMIT is set to OFF an error code is generated. If the AUTOCOMMIT option is set to OFF, you can use the SET TRANSACTION statement to enable the NO ROLLBACK option for the transaction. See

“SET TRANSACTION Statement” (page 166).

An error message is generated if one of the following is true:

• WITH NO ROLLBACK used with STREAM clause

• WITH NO ROLLBACK used with SET ON ROLLBACK clause

table

names the user table or view from which to delete rows. table must be a base table or an updatable view. To refer to a table or view, use the ANSI logical name:

See “Database Object Names” (page 196).

90 SQL Statements

STREAM (table)

deletes a continuous data stream from the specified table. You cannot specify stream access for the DELETE statement if it is not embedded as a table reference in a SELECT statement. See “SELECT Statement” (page 141).

[AFTER LAST ROW]

causes the stream to skip all existing rows in the table and delete only rows that are published after the statement is issued.

SET ON ROLLBACK set-roll-clause [,set-roll-clause]...

causes one or more columns to be updated when the execution of the DELETE statement causes its containing transaction to be rolled back.

set-roll-clause

sets the specified column to a particular value. For each set-roll-clause, the value of the specified target column-name is replaced by the value of the update source expression. The data type of each target column must be compatible with the data type of its source value.

column-name

names a column in table to update. You cannot qualify or repeat a column name. You cannot update the value of a column that is part of the primary key.

expression

is an SQL value expression that specifies a value for the column. The expression cannot contain an aggregate function defined on a column. The data type of expression must be compatible with the data type of column-name. A scalar subquery in expression cannot refer to the table being updated.

If expression refers to columns being updated, Neoview SQL uses the original values to evaluate the expression and determine the new value.

See “Expressions” (page 208).

WHERE search-condition

specifies a search condition that selects rows to delete. Within the search condition, any columns being compared are columns in the table or view being deleted from. See “Search

Condition” (page 248).

If you do not specify a search condition, all rows in the table or view are deleted. You can also delete all the rows from a table using the PURGEDATA utility.

[FOR] access-option ACCESS

specifies the access option required for data used to evaluate the search condition. See “Data

Consistency and Access Options” (page 30).

READ COMMITTED

specifies that any data used to evaluate the search condition must come from committed rows.

SERIALIZABLE | REPEATABLE READ

specifies that the DELETE statement and any concurrent process (accessing the same data) execute as if the statement and the other process had run serially rather than concurrently.

SKIP CONFLICT

enables transactions to skip rows locked in a conflicting mode by another transaction. The rows under consideration are the result of evaluating the search condition for the DELETE statement. You cannot use the SKIP CONFLICT in a SET TRANSACTION statement.

The default access option is the isolation level of the containing transaction.

DELETE Statement 91

Considerations for DELETE

Authorization Requirements

DELETE requiresauthority to read and write to the table or view being deleted from and authority to read tables or views specified in subqueries used in the search condition.

Transaction Initiation and Termination

The DELETE statement automatically initiates a transaction if no transaction is active. Otherwise, you can explicitly initiate a transaction with the BEGIN WORK statement. When a transaction is started, the SQL statements execute within that transaction until a COMMIT or ROLLBACK is encountered or an error occurs.

Isolation Levels of Transactions and Access Options of Statements

The isolation level of an SQL transaction defines the degree to which the operations on data within that transaction are affected by operations of concurrent transactions. When you specify access options for the DML statements within a transaction, you override the isolation level of the containing transaction. Each statement then executes with its individual access option.

You can explicitly set the isolation level of a transaction with the SET TRANSACTION statement. See “SET TRANSACTION Statement” (page 166).

SET ON ROLLBACK Considerations

The SET ON ROLLBACK expression is evaluated when each row is processed during execution of the DELETE statement. The results of the evaluation are applied when and if the transaction is rolled back. Two important implications are:

• If the SET ON ROLLBACK expression generates an error (for example, a divide by zero or

overflow error), the error is returned to the application when the DELETE operation executes, regardless of whether the operation is rolled back.

• If a DELETE operation is applied to a set of rows and an error is generated while executing

the DELETE operation, and the transaction is rolled back, the actions of the SET ON ROLLBACK clause apply only to the rows that the DELETE operation processed before the error was generated.

SET ON ROLLBACK Restrictions

The columns used in the SET ON ROLLBACK clause:

• Must be declared as NOT NULL.

• Cannot use the VARCHAR data type.

• Cannot be used in the primary key or clustering key.

Examples of DELETE

• Remove all rows from the JOB table:

DELETE FROM persnl.job;

--- 10 row(s) deleted.

• Remove the row for TIM WALKER from the EMPLOYEE table:

DELETE FROM persnl.employee WHERE first_name = 'TIM' AND last_name = 'WALKER';

--- 1 row(s) deleted.

• Remove from the table ORDERS any orders placed with sales representative 220 by any

customer except customer number 1234:

92 SQL Statements

DELETE FROM sales.orders WHERE salesrep = 220 AND custnum <> 1234;

--- 2 row(s) deleted.

• Remove all suppliers not in Texas from the table PARTSUPP:

DELETE FROM invent.partsupp WHERE suppnum IN (SELECT suppnum FROM samdbcat.invent.supplier WHERE state <> 'TEXAS');

--- 41 row(s) deleted.

This statement achieves the same result:

DELETE FROM invent.partsupp WHERE suppnum NOT IN (SELECT suppnum FROM samdbcat.invent.supplier WHERE state = 'TEXAS');

--- 41 row(s) deleted.

DROP INDEX Statement

• “Syntax Description of DROP INDEX”

• “Considerations for DROP INDEX”

• “Example of DROP INDEX”

The DROP INDEX statement deletes a Neoview SQL index. See “Database Object Names”

(page 196).

DROP INDEX is a Neoview SQL extension.

DROP [VOLATILE] INDEX index

Syntax Description of DROP INDEX

index

is the index to drop.

For information, see “Identifiers” (page 220).

Considerations for DROP INDEX

Authorization and Availability Requirements

To drop an index, you must be the owner of the schema.

Indexes That Support Constraints

Neoview SQL uses indexes to implement some constraints. If you use the DROP CONSTRAINT option in an ALTER TABLE statement, Neoview SQL drops indexes that it created to implement that constraint.

Example of DROP INDEX

• This example drops an index:

DROP INDEX myindex;

• This example drops a volatile index:

DROP VOLATILE INDEX vindex;

DROP INDEX Statement 93

DROP MATERIALIZED VIEW Statement

• “Syntax Description of DROP MATERIALIZED VIEW”

• “Example of DROP MATERIALIZED VIEW”

The DROP MATERIALIZED VIEW statement drops a materialized view. You cannot drop a materialized view that is used by other materialized views or by regular views unless the CASCADE option is used. This command removes the materializedview from all the MVGROUPs it belongs to. It could cause recompilation of SQL statements that used the materialized view, as well as any of its base tables.

DROP { MATERIALIZED VIEW | MV } mv-name [CASCADE]

Syntax Description of DROP MATERIALIZED VIEW

mv-name

specifies the ANSI logical name for the materialized view to drop of the form:

[schema-name.]mv-name

where each part of the name is a valid SQL identifier with a maximum of 128 characters. mv-name must be unique among table, view, and procedure names in the schema.

CASCADE

any objects that depend on the materialized view are dropped. This includes materialized views and views.

Example of DROP MATERIALIZED VIEW

• This example drops a materialized view:

DROP MATERIALIZED VIEW PUBSCH.DETAIL_MV

94 SQL Statements

DROP MVGROUP Statement

• “Considerations for DROP MVGROUP”

• “Example of DROP MVGROUP”

The DROP MVGROUP statement allows you to drop materialized view groups.

For information on MVGROUPS, see “MVGROUPs ” (page 254).

DROP MVGROUP mv-group-name

mv-group-name

specifies the materialized view group name to drop.

Considerations for DROP MVGROUP

• Dropping an MVGROUP does not affect the MVs that were part of the group.

• You do not need to cleanup the contents of the group prior to dropping the MVGROUP.

Example of DROP MVGROUP

• This example drops a materialized view group:

DROP MVGROUP pgroup

DROP MVGROUP Statement 95

DROP SCHEMA Statement

• “Syntax Description of DROP SCHEMA”

• “Considerations for DROP SCHEMA”

• “Example of DROP SCHEMA”

The DROP SCHEMA statement deletes a Neoview SQL schema. See“Schemas” (page 247).

DROP SCHEMA schema

Syntax Description of DROP SCHEMA

schema

is the name of the schema to drop.

Considerations for DROP SCHEMA

Authorization and Availability Requirements

To drop a schema, you must own the schema.

Example of DROP SCHEMA

• This example drops an empty schema:

DROP SCHEMA sales;

96 SQL Statements

DROP SYNONYM Statement

• “Syntax Description of DROP SYNONYM ”

• “Considerations”

• “Versioning Considerations”

• “Example of DROP SYNONYM”

The DROP SYNONYM statementallows you to drop synonyms for tables, views, or materialized views that can be used in queries. You can write queries that reference the synonym and later alter the synonym to point to a different object with the same structure. The advantage of this is to change the object being referenced without changing the query.

When a synonym is assigned to an object, the object can be referenced either with the synonym name or the actual name. The resultant query results will be the same.

Syntax Description of DROP SYNONYM

DROP SYNONYM alternate-name

alternate-name

specifies the name of the synonym. See “Database Object Names” (page 196).

Considerations

• When the object being referenced by the synonym is dropped, then the synonym is also

dropped.

• Only the owner of the schema or the services ID can create, alter, or drop the alternate names.

• You can create an unlimited number of synonyms for a single object.

• Grant and Revoke commands are allowed on synonyms. The command will be applied to

the actual reference object.

• When a synonym for an object is changed, any currently executing plans fail and required

name resolution and similarity check. If the similarity check fails, existing plans referencing the new object associated with the synonym name require compilation or recompilation.

• View, constraints, and trigger text cannot use synonym names in their DML text.

• Synonyms cannot be renamed. The RENAME operation is allowed on actual table names

only.

Versioning Considerations

Synonyms are only available in schema version 2000. If you need to downgrade your schema to version 1200, all synonyms must be dropped.

Example of DROP SYNONYM

This example drops a synonym:

DROP SYNONYM aname

DROP SYNONYM Statement 97

DROP TABLE Statement

• “Syntax Description of DROP TABLE”

• “Considerations for DROP TABLE”

• “Example of DROP TABLE”

The DROP TABLE statement deletes a Neoview SQL table and its dependent objects such as indexes and constraints. See “Database Object Names” (page 196).

DROP [VOLATILE] TABLE table

Syntax Description of DROP TABLE

table

is the name of the table to delete.

Considerations for DROP TABLE

Authorization and Availability Requirements

To drop a table, you must own the schema that contains the table.

Example of DROP TABLE

• This example drops a table:

DROP TABLE mysch.mytable;

• This example drops a volatile table:

DROP VOLATILE TABLE vtable;

98 SQL Statements

DROP TRIGGER Statement

• “Syntax Description of DROP TRIGGER”

• “Considerations for DROP TRIGGER”

• “Examples of DROP TRIGGER”

The DROP TRIGGER statement is used to drop a trigger on an SQL table.

DROP TRIGGER trigger-name;

Syntax Description of DROP TRIGGER

trigger-name

specifies the name of the trigger to be dropped, of the form:

[[catalog-name.]schema-name.]trigger-name

where each part of the name is a valid SQL identifier with a maximum of 128 characters. For information, see “Identifiers” (page 220).

Considerations for DROP TRIGGER

If you use the CASCADE option to drop objects (table, view, or column) used by a trigger, the triggers using that object will also be dropped.

Authorization and Availability Requirements

To drop a trigger, you must own its schema or be the services ID.

Examples of DROP TRIGGER

• This example drops a trigger:

DROP TRIGGER my-trigger;

DROP TRIGGER Statement 99

DROP VIEW Statement

• “Syntax Description of DROP VIEW”

• “Considerations for DROP VIEW”

• “Example of DROP VIEW”

The DROP VIEW statement deletes a Neoview SQL view. See “Views” (page 254).

DROP VIEW view

Syntax Description of DROP VIEW

view

is the name of the view to drop.

Considerations for DROP VIEW

Authorization and Availability Requirements

To drop a view, you must own the schema that contains the view.

Example of DROP VIEW

• This example drops a view:

DROP VIEW mysch.myview;

100 SQL Statements

HP Neoview SQL Reference Manual

Specifications and Main Features

Frequently Asked Questions

User Manual