{"id":1270,"date":"2011-11-03T04:25:27","date_gmt":"2011-11-02T22:25:27","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=1270"},"modified":"2016-01-07T11:31:05","modified_gmt":"2016-01-07T05:31:05","slug":"loading-db2-tables-from-another-database","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=1270","title":{"rendered":"Loading db2 tables from another database"},"content":{"rendered":"<p>Data movement is a common task that app dev and dbas perform often. DB2 has multiple options to mass add data to tables : IMPORT, LOAD and DB2MOVE.\u00c2\u00a0 Each of them have its own pros and cons.\u00c2\u00a0 If you are loading lots of data and you need it done fast, but can compromise on availability then LOAD is your best bet. The <a href=\"http:\/\/publib.boulder.ibm.com\/infocenter\/db2luw\/v9r7\/topic\/com.ibm.db2.luw.admin.cmd.doc\/doc\/r0008305.html\" target=\"_blank\">load<\/a> command in DB2 LUW is loaded with different options. I am sure you have loaded ixf and del files to db2 tables.\u00c2\u00a0 What if you want to make a copy of a table on the same database ?\u00c2\u00a0 The traditional method would be to create a new table like the source table, then export the data in del or ixf format and then load the data using the LOAD command. A newer approach would be to use the cursor option in the load command . Create table, declare a cursor for the source table and load the target table using the cursor. Eg :<\/p>\n<p><em><span style=\"color: #ff9900;\">declare c1 cursor select * from ds1.sales;<\/span><\/em><br \/>\n<em><span style=\"color: #ff9900;\">load from c1 of cursor messages load_sales.msg insert into ds1.sales_stage;<\/span><\/em><\/p>\n<p>So what if you have to load data from another database (same server or different server) ?\u00c2\u00a0 First you need to catalog the remote database on the server and then you can declare a cursor for a remote database and then load using that cursor. Eg:<br \/>\nAssuming you have cataloged the source database on the target server as srcdb<\/p>\n<p><em><span style=\"color: #ff9900;\">connect to targetdb;<\/span><\/em><br \/>\n<em><span style=\"color: #ff9900;\">declare c1 cursor database srcdb user user1 using secretpass for select * from ps1.sales;<\/span><\/em><br \/>\n<em><span style=\"color: #ff9900;\">load from c1 of cursor messages load_sales.msg insert into ds1.sales_stage nonrecoverable;<\/span><\/em><\/p>\n<p>This will load the data on table ps1.sales from the srcdb to ds1.sales_stage on the targetdb eliminating a file extract and then a load from the file.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Data movement is a common task that app dev and dbas perform often. DB2 has multiple options to mass add data to tables : IMPORT, LOAD and DB2MOVE.\u00c2\u00a0 Each of them have its own pros and cons.\u00c2\u00a0 If you are loading lots of data and you need it done fast, but can compromise on availability [&hellip;]<\/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,358],"tags":[724,943,722,717,720,34,723,719,716,718,721],"class_list":["post-1270","post","type-post","status-publish","format-standard","hentry","category-db2","category-how-to","tag-data-movement","tag-db2","tag-db2-import","tag-db2-load","tag-db2-load-from-cursor","tag-db2move","tag-export","tag-fast-and-easy-load","tag-load","tag-load-from-cursor","tag-loading-data-from-another-table"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1270","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=1270"}],"version-history":[{"count":3,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1270\/revisions"}],"predecessor-version":[{"id":1495,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1270\/revisions\/1495"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1270"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1270"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1270"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}