A CREATE TABLE has "copy-options" that can make an effective
duplicate of the original TABLE using any of:
CREATE TABLE ... LIKE ... copy-options
CREATE TABLE ... ( LIKE ... copy-options)
CREATE TABLE ... as-result-table copy-options
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzhctabl.htm
_i CREATE TABLE i_
"The CREATE TABLE statement ..."
Similarly for DECLARE GLOBAL TEMPORARY TABLE statement.
The available copy options [shown in the syntax diagram in the above
doc link] include both EXCLUDING IMPLICITLY HIDDEN and EXCLUDING ROW
CHANGE TIMESTAMP.
So what I believe Vern is alluding... these could be of assistance if
there is value in creating an alternate version of the original TABLE,
from which to establish a DS which will not include the ROW CHANGE
TIMESTAMP column.?
However I am wondering... *if* the column were defined instead as
[IMPLICITLY HIDDEN and\or] GENERATED ALWAYS, if there might not be
another solution.
FWiW I thought GENERATED ALWAYS was implied for any column with the
as-row-change-timestamp-clause much like the data-type is optional and
implied TIMESTAMP.... but I see no such statement in the CREATE TABLE
statement documentation, so maybe that is required to be specified
explicitly to enable OVERRIDING values. But in a re-read of both the
UPDATE statement and the INSERT statement documentation, I really do
expect that the OVERRIDING USER VALUE on the INSERT should and would
resolve the issue, if the column were to be defined instead as:
ChgDate TIMESTAMP GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW
CHANGE TIMESTAMP NOT NULL /* optionally: IMPLICITLY HIDDEN */
As with an IDENTITY column, the VALUES clause of the INSERT statement
"Insert into BillAgentP Values(:ds_Insert)" would have a corresponding
variable to the row change timestamp column, and thus the user value
should be ignored when modified to request instead:
"Insert into BillAgentP OVERRIDING USER VALUE Values(:ds_Insert)"
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzbackup.htm
_i INSERT i_
"The INSERT statement ...
OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE
Specifies whether system generated values or user-specified values
for a ROWID, identity, or row change timestamp column are used. If
OVERRIDING SYSTEM VALUE is specified, the implicit or explicit list of
columns for the INSERT statement must contain a column defined as
GENERATED ALWAYS. If OVERRIDING USER VALUE is specified, the implicit or
explicit list of columns for the INSERT statement must contain a column
defined as either GENERATED ALWAYS or GENERATED BY DEFAULT.
OVERRIDING SYSTEM VALUE
Specifies that the value specified in the VALUES clause or
produced by a fullselect for a column that is defined as GENERATED
ALWAYS is used. A system-generated value is not inserted.
OVERRIDING USER VALUE
Specifies that the value specified in the VALUES clause or
produced by a fullselect for a column that is defined as either
GENERATED ALWAYS or GENERATED BY DEFAULT is ignored. Instead, a
system-generated value is inserted, overriding the user-specified value.
..."
Regards, Chuck
On 15 Mar 2013 10:13, Vernon Hamberg wrote:
Kurt - there is a way to ignore these automatic things <<SNIP>>
Look for "EXCLUDING ROW CHANGE TIMESTAMP" and "INCLUDING ROW CHANGE
TIMESTAMP" - these will likely help you out here.
Search for them in the SQL Reference.
On 3/15/2013 10:00 AM, Gary Thompson wrote:
I think so, or, use a view which omits the TIMESTAMP
Anderson, Kurt on Friday, March 15, 2013 8:55 AM wrote:
<<SNIP>>
One thing I learned about was being able to have my SQL table
automatically set the last change timestamp field w/o me doing it.
Example: ChgDate TIMESTAMP FOR EACH ROW ON UPDATE AS ROW CHANGE
TIMESTAMP NOT NULL
<<SNIP>>
I had Insert into BillAgentP Values(:ds_Insert); Where ds_Insert is a
Qualified DS defined like the file. But now that doesn't work b/c it
doesn't like that I'm updating ChgDate. Is my only recourse to list
out all the fields except for ChgDate?
<<SNIP>>
midrange.com
