error message DB2 UDB 8.1.5 LINUX

[Bob Stearns]

When trying an alter I am getting:

DB2 SQL error: SQLCODE: -20054, SQLSTATE: 55019, SQLERRMC:
IS3.SERVICE_OBS;22
Message: The table "IS3.SERVICE_OBS" is in an invalid state for the
operation. Reason code="22".

I thought 'db2 ? 55019' would give me an explanation, but it only
repeats 'The table is in an invalid state for the operation.' without
further explanation. I need either an explanation, or better, a hint of
where to look this up.

[Gert van der Kooij]

In article <MF*******************@fe05.lga>, Bob Stearns
(rs**********@charter.net) says...

When trying an alter I am getting:

DB2 SQL error: SQLCODE: -20054, SQLSTATE: 55019, SQLERRMC:
IS3.SERVICE_OBS;22
Message: The table "IS3.SERVICE_OBS" is in an invalid state for the
operation. Reason code="22".

I thought 'db2 ? 55019' would give me an explanation, but it only
repeats 'The table is in an invalid state for the operation.' without
further explanation. I need either an explanation, or better, a hint of
where to look this up.

db2 ? sql20054

[Bob Stearns]

Gert van der Kooij wrote:

In article <MF*******************@fe05.lga>, Bob Stearns
(rs**********@charter.net) says...

When trying an alter I am getting:

DB2 SQL error: SQLCODE: -20054, SQLSTATE: 55019, SQLERRMC:
IS3.SERVICE_OBS;22
Message: The table "IS3.SERVICE_OBS" is in an invalid state for the
operation. Reason code="22".

I thought 'db2 ? 55019' would give me an explanation, but it only
repeats 'The table is in an invalid state for the operation.' without
further explanation. I need either an explanation, or better, a hint of
where to look this up.

db2 ? sql20054

Thanks. I tried db2 ? -20054, but forgot about the sql20054 format.

The question now occurs why? I don't see any function call in my statement:

alter table is3.service_obs
add column due_date date
generated always as (datex + 283 days)
go

I have also tried variants and they all give the same message.

[Gert van der Kooij]

In article <Bd*****************@fe06.lga>, Bob Stearns (rstearns1241
@charter.net) says...

The question now occurs why? I don't see any function call in my statement:

alter table is3.service_obs
add column due_date date
generated always as (datex + 283 days)
go

I have also tried variants and they all give the same message.

DId you try the suggested action:

22 Use SET INTEGRITY FOR <table-name> OFF before altering the
table. Then alter the table and use SET INTEGRITY FOR
<table-name> IMMEDIATE CHECKED FORCE GENERATED to generate the
values for the new or altered column.

[Serge Rielau]

Gert van der Kooij wrote:

In article <Bd*****************@fe06.lga>, Bob Stearns (rstearns1241
@charter.net) says...

The question now occurs why? I don't see any function call in my statement:

alter table is3.service_obs
add column due_date date
generated always as (datex + 283 days)
go

I have also tried variants and they all give the same message.

DId you try the suggested action:

22 Use SET INTEGRITY FOR <table-name> OFF before altering the
table. Then alter the table and use SET INTEGRITY FOR
<table-name> IMMEDIATE CHECKED FORCE GENERATED to generate the
values for the new or altered column.

Background: Adding/altering a generated column is the only table action
which physically updates the table. To be more precise it will likely
update all zillion rows of it (which can be a lot in Viper with range
partitioning).
So rather than quitely filling up peoples logspace (or stressing the
auto-archival option) we decided it would be appropriate to perform such
heavy operations while the table is in check pending.
All this is clearly documented by the way:

ALTER TABLE Statement:
....
AS (generation-expression)
Specifies that the definition of the column is based on an
expression. Requires that the table be put in check pending state, using
the SET INTEGRITY statement. After the ALTER TABLE statement, the SET
INTEGRITY statement with FORCE GENERATED must be used to update and
check all the values in that column against the new expression. For
details on specifying a column with a generation-expression, see "CREATE
TABLE".

Granted I was young.. but did I write it that badly?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bob Stearns]

Serge Rielau wrote:

Gert van der Kooij wrote:

In article <Bd*****************@fe06.lga>, Bob Stearns (rstearns1241
@charter.net) says...

The question now occurs why? I don't see any function call in my
statement:

alter table is3.service_obs
add column due_date date
generated always as (datex + 283 days)
go

I have also tried variants and they all give the same message.

DId you try the suggested action:

22 Use SET INTEGRITY FOR <table-name> OFF before altering the
table. Then alter the table and use SET INTEGRITY FOR
<table-name> IMMEDIATE CHECKED FORCE GENERATED to generate the
values for the new or altered column.

Background: Adding/altering a generated column is the only table action
which physically updates the table. To be more precise it will likely
update all zillion rows of it (which can be a lot in Viper with range
partitioning).
So rather than quitely filling up peoples logspace (or stressing the
auto-archival option) we decided it would be appropriate to perform such
heavy operations while the table is in check pending.
All this is clearly documented by the way:

ALTER TABLE Statement:
...
AS (generation-expression)
Specifies that the definition of the column is based on an
expression. Requires that the table be put in check pending state, using
the SET INTEGRITY statement. After the ALTER TABLE statement, the SET
INTEGRITY statement with FORCE GENERATED must be used to update and
check all the values in that column against the new expression. For
details on specifying a column with a generation-expression, see "CREATE
TABLE".

Granted I was young.. but did I write it that badly?

Cheers
Serge

I understand the problem now, but I think the error message could be
made much clearer. There should be no reference to a function in the
message. ( 22 The function cannot be used in a generated column.)

Also, this actually creates a physical column at ALTER time. What I was
looking for was a way to specify a "virtual" column which would be
instantiated at SELECT time, as needed. It would not be allowed in
UPDATE or INSERT statements, and its value would be based on the current
values in the row when selected, much the way an expression is evaluated
in a SELECT. Is there such a method, or must I create a VIEW with the
express in the SELECT part?

[Serge Rielau]

Bob Stearns wrote:

Serge Rielau wrote:

Gert van der Kooij wrote:

In article <Bd*****************@fe06.lga>, Bob Stearns (rstearns1241
@charter.net) says...
The question now occurs why? I don't see any function call in my
statement:

alter table is3.service_obs
add column due_date date
generated always as (datex + 283 days)
go

I have also tried variants and they all give the same message.

DId you try the suggested action:

22 Use SET INTEGRITY FOR <table-name> OFF before altering the
table. Then alter the table and use SET INTEGRITY FOR
<table-name> IMMEDIATE CHECKED FORCE GENERATED to generate the
values for the new or altered column.

Background: Adding/altering a generated column is the only table
action which physically updates the table. To be more precise it will
likely update all zillion rows of it (which can be a lot in Viper with
range partitioning).
So rather than quitely filling up peoples logspace (or stressing the
auto-archival option) we decided it would be appropriate to perform
such heavy operations while the table is in check pending.
All this is clearly documented by the way:

ALTER TABLE Statement:
...
AS (generation-expression)
Specifies that the definition of the column is based on an
expression. Requires that the table be put in check pending state,
using the SET INTEGRITY statement. After the ALTER TABLE statement,
the SET INTEGRITY statement with FORCE GENERATED must be used to
update and check all the values in that column against the new
expression. For details on specifying a column with a
generation-expression, see "CREATE TABLE".

Granted I was young.. but did I write it that badly?

Cheers
Serge

I understand the problem now, but I think the error message could be
made much clearer. There should be no reference to a function in the
message. ( 22 The function cannot be used in a generated column.)

Also, this actually creates a physical column at ALTER time. What I was
looking for was a way to specify a "virtual" column which would be
instantiated at SELECT time, as needed. It would not be allowed in
UPDATE or INSERT statements, and its value would be based on the current
values in the row when selected, much the way an expression is evaluated
in a SELECT. Is there such a method, or must I create a VIEW with the
express in the SELECT part?

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Bob Stearns wrote:

I understand the problem now, but I think the error message could be
made much clearer. There should be no reference to a function in the
message. ( 22 The function cannot be used in a generated column.) Granted, I think with function ADD column was meant and not a function
foo(). Clumsy wording indeed.
Also, this actually creates a physical column at ALTER time. What I was
looking for was a way to specify a "virtual" column which would be
instantiated at SELECT time, as needed. It would not be allowed in
UPDATE or INSERT statements, and its value would be based on the current
values in the row when selected, much the way an expression is evaluated
in a SELECT. Is there such a method, or must I create a VIEW with the
express in the SELECT part?

That would be the non existent GENERATED BY REFERENCE. Incidentally the
idea was to make this the default since it's what SQL Server 2000
introduced using <colname> AS <expression>. (DB2 tried to play nice here
....)
However we were never able to figure out which purpose such a feature
has because a VIEW provides the same effect, so we never implemented it.
Later on I realized that SQL Server doesn't support INSERT into a VIEW
if any column is non updatable.. So may be that is the reason.
DB2 doesn't have the restriction. So.. why is it that a view is bad?
Note that DB2 is rtaher view-friendly there are few if any drawbacks to
using views.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bob Stearns]

Serge Rielau wrote:

Bob Stearns wrote:

I understand the problem now, but I think the error message could be
made much clearer. There should be no reference to a function in the
message. ( 22 The function cannot be used in a generated column.)

Granted, I think with function ADD column was meant and not a function
foo(). Clumsy wording indeed.

Also, this actually creates a physical column at ALTER time. What I
was looking for was a way to specify a "virtual" column which would be
instantiated at SELECT time, as needed. It would not be allowed in
UPDATE or INSERT statements, and its value would be based on the
current values in the row when selected, much the way an expression is
evaluated in a SELECT. Is there such a method, or must I create a VIEW
with the express in the SELECT part?

That would be the non existent GENERATED BY REFERENCE. Incidentally the
idea was to make this the default since it's what SQL Server 2000
introduced using <colname> AS <expression>. (DB2 tried to play nice here
...)
However we were never able to figure out which purpose such a feature
has because a VIEW provides the same effect, so we never implemented it.
Later on I realized that SQL Server doesn't support INSERT into a VIEW
if any column is non updatable.. So may be that is the reason.
DB2 doesn't have the restriction. So.. why is it that a view is bad?
Note that DB2 is rtaher view-friendly there are few if any drawbacks to
using views.

Cheers
Serge

I am a little leery of views, probably because of lack of experience. Is
there some automatic way to have a view re-enabled when the underlying
table is modified? That is the basic reason I stay away from them.

[Serge Rielau]

Bob Stearns wrote:

I am a little leery of views, probably because of lack of experience. Is
there some automatic way to have a view re-enabled when the underlying
table is modified? That is the basic reason I stay away from them.

I see. Automated, no, not at this time. Maybe in the future ;-)
DB2 V8.2 provides both a wizard in control center as well a procedure to
modify tables without loosing track of views.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab