Detailed descriptions of error codes for incremental update of DB2 MQT

The specific query table definition contains specific rules related to the full query content.

The specified full query for the specific query table "<Table Name>" is invalid. Cause code = "<cause code> ".

Note:
The specific query table definition contains specific rules related to the full query content. Some rules are based on the specific query table option (refresh deferred or refresh immediate), while others are based on whether the table is replicated. The full query in the create table statement that returns this status violates at least one of the rules described in the SQL Reference.
This error may occur when creating a stage table. In this case, this error applies to queries used in the definition of the specific query table associated with the stage table.
The statement cannot be processed because it violates the restrictions indicated by the following cause code:

1
Each selection list element must have a name.

2
Full query cannot reference any of the following object types:
Specific query table
Stage table
Declared global temporary table
Type table
System Directory table
View that violates any specific query table restrictions
Protected table
The NICKNAME created using the disallow caching clause in the create nickname or alter nickname statement directly or indirectly depends on the view of the protected table.

3
A full query cannot contain any column reference or expression of the following data types:
LOB, LONG, DATALINK, XML, reference, user-defined structured type, or any single value type based on these data types

4
A full query cannot contain any column reference, expression, or function that meets the following conditions:
Depends on the physical characteristics of the data, such as DBPARTITIONNUM, HASHEDVALUE, RID_BIT, and RID.
Depends on the changes made to the data, such as the row change expression or ROW CHANGE
TIMESTAMP Column
Is defined as EXTERNAL ACTION
Is defined as language SQL, contains SQL, reads SQL DATA or
MODIFIES SQL DATA

5
When REPLICATED is specified, the following restrictions apply:
Aggregate functions and group by clauses are not allowed.
A specific query table can only reference a single table. That is, it cannot include join, union, or subquery
The partitioning key clause cannot be specified.

6
When refresh immediate is specified, the full query cannot contain:
References to nicknames
SELECT DISTINCT
Reference of dedicated registers
References to global variables
Non-deterministic Functions
OLAP functions, sampling functions, and text functions
Any expression that uses the result of the aggregate function
No aggregate function for the full query that contains the group by clause
Recursive common table expression
Subquery

7
When refresh immediate is specified:
The specified query table cannot contain duplicate rows.
When a group by clause is specified, all group by recommendations must be included in the selection list.
When a group by clause containing grouping sets, CUBE, or ROLLUP is specified, no GROUP set can be repeated. If C appears in grouping sets,
The group by item can be empty in CUBE or ROLLUP, so GROUPING (C) must appear in the selection list
If there is no group by clause, each base table must have at least one unique key, and all columns of these keys must appear in the selection list of the specific query table definition.

8
If refresh immediate is specified, the following restrictions apply when the group by clause is included in the full query:
The selection list must contain COUNT (*) or COUNT_BIG (*)
For each empty column C, if the selection list contains SUM (C), you also need COUNT (C)
At least one of the following Aggregate functions must appear (and there are no other Aggregate functions ):
SUM (), COUNT (), COUNT_BIG (), or GROUPING ()
HAVING clause cannot be specified
In the partitioned database environment, the group by column must contain the partition key of the specific query table.
Nesting of Aggregate functions is not allowed.

9
If refresh immediate is specified, the full query must be a subquery, but the exception is that union all is supported in the group by input table expression.

10
If refresh immediate is specified and the FROM clause references multiple tables, only internal JOIN is supported (explicit inner join syntax is not used ).

11
If refresh immediate is specified, the input table expressions of union all or JOIN cannot contain aggregate functions.

12
The Incremental backup maintenance of this specific query table requires a system temporary table. The row width or number of columns of this table exceeds the limit that the maximum system temporary tablespace currently available on the database can accommodate.

 

 

User response:
Change the full query in the create table statement to conform to the specific query TABLE options and whether the specific query TABLE is a replication rule.

The operation corresponding to the cause code is:

1
Correct the create table statement to ensure that all elements have names (you can use the AS clause to name expressions or explicitly name all columns in the column list defined by the specific query TABLE ).

2
Correct the create table statement to ensure that only supported objects are referenced.

3
Correct the create table statement to ensure that only supported columns or expression types are referenced.

4
Correct the create table statement to ensure that only supported Columns, expressions, or functions are referenced.

5
CREATE an unreplicated materialized query TABLE or correct the create table statement to make sure that the query references a single TABLE and does not contain subqueries, clustering, or PARTITIONING clauses.

6
Create a specific query table as refresh deferred, or
Remove nickname reference
Except DISTINCT
Remove dedicated registers
Remove non-deterministic functions or replace them with deterministic functions.
Remove all OLAP, sampling, and text functions
Remove the aggregate function from the expression or change the expression to a simple reference to the aggregate function
Remove Aggregate functions or add group by clauses
Correct the create table statement to ensure that recursive common TABLE expressions are not referenced.
Remove subqueries

7
Create a specific query table as refresh deferred, or
Correct the create table statement to ensure that all group by items are in the selection list.
Correct the group by clause to ensure that no duplicate GROUP set exists.
Remove the empty column C from the selection list or add GROUPING (C)
Correct the create table statement to ensure that at least one of the tables referenced in the query
The unique key appears in the selection list.

8
Create a specific query table as refresh deferred, or
Add COUNT (*) or COUNT_BIG (*) to the selection list or remove the group by clause
Add COUNT (*) to the selection list, remove SUM (C), or change column C to non-empty.
Remove unsupported Aggregate functions or replace them with supported functions.
Remove HAVING clause
Correct the create table statement to ensure that the group by clause contains all partition key columns.

9
Create a specific query table as refresh deferred, or change the table definition to union all in the input table expression of subquery or group.

10
Create a specific query table as refresh deferred or use inner join (no explicit inner join syntax is used ).

11
Create a specific query table as refresh deferred, or remove the aggregate function from the input table of union all or JOIN.

12
Create a specific query table as refresh deferred or reduce the width or number of columns of the total row, or create a system temporary tablespace with an appropriate page size.