Yes I have but it is log intensive. I think I had to double the
primary logs (ours is set too low anyway)
and it worked very well.
Thanks,
Happy Thanksgiving!!!
John P. Kruger
Database Administrator
Management Information Systems
IL Department of Human Services
(217) 557-9720
FAX (217) 782-6089
>>> "DANIEL CALLAHAN "
<db2udbdba-ezmlmshield-x57796952.[Email address protected] 11/23/05
3:29 PM >>>
Nice!!
So an alter and then set integrity. Have you tried that with a large
table
?
"JOHN KRUGER "
<db2udbdba-ezmlms
hield-x45143880.x
To
[Email Address Removed] "LazyDBA Discussion"
a.com> <[Email address protected]
cc
11/23/2005 04:14
PM
Subject
Re: What is best way to do a
massive update?
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
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
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