README for HP Neoview Release 2.4 Service Pack 2

• EXPLAIN statement:
EXPLAIN [OPTIONS {'f' | 'n' | 'e' | 'm'}] FOR QID qid
The qid is a delimited identifier or regular identifier, which represents the query ID. This
example shows the query ID, which contains lowercase characters, as a delimited identifier:
QID "MXID01001011194212103659400053369000000085905admin00_2605_S1"
• EXPLAIN function:
SELECT * FROM TABLE(EXPLAIN(NULL, 'QID=qid'))
The qid is a case-sensitive identifier, which represents the query ID. For example:
'QID=MXID01001011194212103659400053369000000085905admin00_2605_S1'
The EXPLAIN function or statement returns the plan that was generated when the query was
prepared. EXPLAIN for QID retrieves all the information from the original plan of the executing
query. The plan is available until the query finishes executing and is removed or deallocated.
For more information about EXPLAIN, see the Neoview SQL Reference Manual.
Recalibrating Internal Sequence Generators
Sequence generators supply unique, sequential numeric values for a table and are typically used
to generate values for a surrogate key. Surrogate keys are useful in data warehouse environments
because they enable dimension and fact tables to be joined more efficiently, and they provide a
way to uniquely identify records that have the same or a dynamic natural key. In Neoview SQL,
you can generate unique values for a surrogate key by specifying an IDENTITY column in a
CREATE TABLE statement. The IDENTITY column uses an internal sequence generator.
After you execute a CREATE TABLE AS or CREATE TABLE LIKE AS statement, it may be
necessary to recalibrate the value of the internal sequence generator to avoid duplicate values.
The INSERT-SELECT from the table creation provides user-specified values for the target table's
IDENTITY column. The internal sequence generator associated with the target table's IDENTITY
column remains at its initial default value based on the START WITH property, which could
lead to duplicate values in the IDENTITY column during subsequent row insertions.
The current software release introduces syntax in the ALTER TABLE ALTER COLUMN statement
that enables you to recalibrate an IDENTITY column and its current value. Use the new
RECALIBRATE options in ALTER TABLE ALTER COLUMN to recalibrate the current value of
the internal sequence generator according to the current maximum value in the IDENTITY
column. This action prevents duplicate values from occurring in the IDENTITY column. After
you recalibrate the IDENTITY column, the next value that the internal sequence generator supplies
for the IDENTITY column during an INSERT statement will be greater than the current maximum
value of the IDENTITY column.
For more information about the new RECALIBRATE options, see the Neoview SQL Reference
Manual for Release 2.4 SP2.
Loading Data Into an Existing Table With the LOAD IF EXISTS Option
The CREATE TABLE AS statement now includes a new LOAD IF EXISTS option, which causes
data to be loaded into an existing table. If you do not specify the LOAD IF EXISTS option and
try to load data into an existing table, the CREATE TABLE AS statement fails to execute. Use the
LOAD IF EXISTS option with the AS clause in these scenarios:
• Running CREATE TABLE AS without re-creating the table. The table must be empty.
Otherwise, the CREATE TABLE AS statement returns an error. Delete the data in the table
by using PURGEDATA or a DELETE statement before issuing the CREATE TABLE AS
statement.
• Using CREATE TABLE AS to add data incrementally to an existing table. You must start a
user-defined transaction before issuing the CREATE TABLE AS statement. If you try to
execute the CREATE TABLE AS statement without starting a user-defined transaction, an
New Features in Release 2.4 SP2 9