× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



We had a thread here recently - I was looking for a CREATE TABLE that'd preserve format level ID when there is one of these ROW CHANGE TIMESTAMP things - which we find very useful here. A subset of the records is copied to QTEMP, then processed with RPG RLA.

I ended up using all the INCLUDINGs there are, I think, and it worked.

My idea was to replace about 10-15 lines of code with 2-3.

So I threw the comment out, not sure if it really applied. the IMPLICITLY HIDDEN is interesting, as well. Not sure if the CREATE TABLE would include that column - or if the INCLUDING would override the HIDDEN - lots to learn again!!

Vern

On 3/15/2013 2:52 PM, CRPence wrote:
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>>


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.