While indexing is not supported against views - it is supported against base tables, of course. And when you reference a view, "query rewrite" merges the view definition and the additional clauses and generates an optimal query against the base table which WILL take advantage of any appropriate indexes - so it's not an issue.
Thanks for the heads up on the Alter table feature added to the control center - that's quite useful.
Best,
Hal Steiner
-----Original Message-----
From: hparthasarathy <db2udbdba-ezmlmshield-x31190237.[Email address protected]
To: LazyDBA Discussion <[Email address protected]
Sent: Mon, 24 Oct 2005 10:09:14 -0600
Subject: RE: AW: drop column
Yes. the control center generates the alter-table sysntax using the
ALTOBJ procedure.
Along with those operations you specified, it takes care of dependent
objects of the table.
Export, recreate and import is simple, but extraction and recreation of
dependent objects is bit challenging.
Partha
"Ken Daulong DC-98 "
<db2udbdba-ezmlmshield-x69897230.[Email address protected]
10/24/2005 09:53 AM
To
"LazyDBA Discussion" <[Email address protected]
cc
Subject
RE: AW: drop column
Yes, but all the control center does is create a scripts to do the table
export, drop, create and load.
Ken
-----Original Message-----
From: hparthasarathy
[mailto:db2udbdba-ezmlmshield-x17974890.[Email address protected]
Sent: Monday, October 24, 2005 10:44 AM
To: LazyDBA Discussion
Subject: Re: AW: drop column
In DB2 8.2, column-dropping is supported. You can do this either in
Control Center or you can use ALTOBJ stored procedure.
Another suggestion would be to rename the current table and create a view
for this table by not considering the columns that you don't need.
But the scary part is Indexing is not supported on Views [sombody, correct
me if I am wrong!] which may be needed for such a big table.
HTH
Partha
Hassan Parthasarathy [PARTHA]
"Otfrid Altfeld "
<db2udbdba-ezmlmshield-x48741380.[Email address protected]
10/24/2005 08:59 AM
To
"LazyDBA Discussion" <[Email address protected]
cc
Subject
AW: drop column
Thank you, Ken.
This is what I was afraid of.
I thought dropping a column within the alter table statement would be
according to SQL Standard.
Exporting an re-importing data from a table with approximately 160 000 000
rows isn't that funny, is it?
Regards,
Otfrid
-----Ursprüngliche Nachricht-----
Von: Ken Daulong DC-98 [mailto:db2udbdba-ezmlmshield-x47115167.[Email
address protected]
Gesendet: Montag, 24. Oktober 2005 16:56
An: LazyDBA Discussion
Betreff: RE: drop column
No column drops in db2.
export the data less the column to drop
drop the table
create the table less the column
load the data back
-----Original Message-----
From: Otfrid Altfeld
[mailto:db2udbdba-ezmlmshield-x88849206.[Email address protected]
Sent: Monday, October 24, 2005 9:52 AM
To: LazyDBA Discussion
Subject: drop column
Hi.
When executing the following statement (db2 8.2, Windows 2000 Server):
'alter table table_name drop column column_name'
I get the the SQL-Message SQL0104N 'An unexpected token was found...'.
Does that mean DB2 doesn't allow dropping a column from a table (though
the column is empty)?
What can I do to drop a column from a table successfully?
Thank you in advance,
Otfrid
Otfrid Altfeld
Vermittlungstechnik
3U TELECOM AG
Neue Kasseler Straße 62F
D-35039 Marburg
Tel: +49/ (0) 64 21/ 999 - 24 68
Fax: +49/ (0) 64 21/ 999 - 23 45
Email: [Email address protected] <mailto:[Email address protected]
Internet: www.3u.net <http://www.3u.net/>
Jetzt neu: 3U Highspeed-DSL und 3U Internet-Telefonie
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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