You can try experimenting with this approach. Create a generated column
for the new timestamp. The old timestamp will be use to automatically
fill in the new timestamp. Set integrity to fill in the empty values,
and like magic your work is done. :-)) Please experiment in test
first.
CREATE TABLE DHSDB2.MYTABLE
(ADR_ID DECIMAL(15, 0) NOT NULL,
STR_ADR CHARACTER(40) NOT NULL,
CITY CHARACTER(20) NOT NULL,
ST CHARACTER(2) NOT NULL,
ZIP DECIMAL(5, 0) NOT NULL,
ZIP_EXT DECIMAL(4, 0) NOT NULL,
POST_TS TIMESTAMP NOT NULL,
NEW_TS TIMESTAMP NOT NULL GENERATED ALWAYS AS (POST_TS)
) IN TABLESPACE1
Thanks,
John P. Kruger
John P. Kruger
johnDOTkruger(AT)insightbb(DOT)com
Database Administrator
Management Information Systems
IL Department of Human Services
(217) 557-9720
FAX (217) 782-6089
>>> "hsteiner"
<db2udbdba-ezmlmshield-x28286564.[Email address protected] 11/23/05
2:54 PM >>>
I am looking for a creative solution to a problem which concerns us.
We have a fairly large table -- 210 million rows in production. What we
want to do sounds simple. But so far our approaches produce extremely
slow run times.
We need to add a new column at the end of the table which will be a
timestamp. The rowsize is approx 200 bytes already. We wish to
initialize that new column with another timestamp that exists elsewhere
in the row. In the future the new column will have the "current"
timestamp for inserted rows. This is the way they wish to populate the
column for existing rows. So basically we need to write a program which
will update every one of 210 million rows in an application that has to
stay up 24 by 7 (I don't have a day of downtime, sorry).
One technique we used took 30 minutes to update 10,000 rows. At that
rate, the update process would take almost a year. I am looking for a
more streamlined approach, needless to say.
A few more details: There are multiple indexes on this table (but not
on the new column). The table is replicated across two machines.
Appreciation any suggestions at all.
Hal
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page