DB2 V9.x introduced one of the long awaited feature that other RDBMS could easily do…the DROP COLUMN . Since DB2 V9.1 , db2 is able to drop a column from a table without the dba have to drop and recreate the table.\u00c2\u00a0 The traditional method of , unloading the data,\u00c2\u00a0 dropping and recreating the table, loading the data, recreating the constraints and objects is not necessary.\u00c2\u00a0 You can now issue ALTER TABLE… DROP COLUMN … to remove a column. If you have depending objects, db2 is also able to take care of it using the CASCADE option.\u00c2\u00a0\u00c2\u00a0 Now is there a catch !\u00c2\u00a0 Yes..there is…db2 recommends a reorg of the table after dropping a column and after\u00c2\u00a0 three drop columns on a table, the table goes into DRP state and any operations on the table gets a SQLCODE 2005N RC 23 .\u00c2\u00a0 This means all the three strikes have been taken.\u00c2\u00a0<\/p>\n
In my case today,\u00c2\u00a0 I had to drop 6 columns, so I had to do two reorgs, if you have many columns to drop, it might be better to take the older route, but if its couple of columns and a small table that can take a small downtime, then drop column route is easier.\u00c2\u00a0 Here is what I did :<\/p>\n
ALTER TABLE\u00c2\u00a0\u00c2\u00a0TECH.SITE\u00c2\u00a0 DROP COLUMN COSTOMER_NO CASCADE; DB2 V9.x introduced one of the long awaited feature that other RDBMS could easily do…the DROP COLUMN . Since DB2 V9.1 , db2 is able to drop a column from a table without the dba have to drop and recreate the table.\u00c2\u00a0 The traditional method of , unloading the data,\u00c2\u00a0 dropping and recreating the table, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[3],"tags":[],"class_list":["post-434","post","type-post","status-publish","format-standard","hentry","category-db2"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/434","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=434"}],"version-history":[{"count":1,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/434\/revisions"}],"predecessor-version":[{"id":435,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/434\/revisions\/435"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=434"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=434"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=434"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nALTER TABLE\u00c2\u00a0\u00c2\u00a0TECH.SITE\u00c2\u00a0 DROP COLUMN COSTOMER_NAME CASCADE;
\nALTER TABLE\u00c2\u00a0\u00c2\u00a0TECH.SITE\u00c2\u00a0 DROP COLUMN COSTOMER_PHNO CASCADE;
\nREORG TABLE TECH.SITE;
\nALTER TABLE\u00c2\u00a0\u00c2\u00a0TECH.SITE\u00c2\u00a0 DROP COLUMN COSTOMER_FAXNO CASCADE;
\nALTER TABLE\u00c2\u00a0\u00c2\u00a0TECH.SITE\u00c2\u00a0 DROP COLUMN COSTOMER_CELLNO CASCADE;
\nALTER TABLE\u00c2\u00a0\u00c2\u00a0TECH.SITE\u00c2\u00a0 DROP COLUMN COSTOMER_EMAIL CASCADE;
\nREORG TABLE TECH.SITE;<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"