Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2012
    Posts
    29

    Unanswered: bulk loading data from another server

    Hi all,
    I'm trying to port the function "openrowset" from SQL Server to DB2.

    This function can be used as following:

    INSERT INTO myTable(FileName, FileType, Document)
    SELECT a.*
    FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
    'SELECT FileName, FileType, Document
    FROM AdventureWorks2008R2.HumanResources.Department') AS a;


    where Seattle1 is a remote server.

    I'm looking for an equivalent in DB2, but I only found IMPORT and LOAD utilities, which just load data from a text file.

    Is there something better (above all in terms of performances) I could use in this case?

    Thanks a lot

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can use Federation to have a remote table look like it is local to the database. Look it up in the manual.

    Andy

  3. #3
    Join Date
    Jan 2012
    Posts
    29
    Thanks Andy for your reply.

    Anyway, what I'd like to do is not replicating a whole table, but just bulk load some data from a source server into another.

    For example I may select two columns from a source server table and insert those values in a target server table with a different name.

    Thanks

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You basically have two options:

    1) export the data from the remote database, transmit it to the local database, and import/load it where you want it
    2) Use federation to make it look like the remote table is local. This does not copy/replicate anything. It just allows you to reference the remote table as if it was local. You can then insert/load from the federated nickname (table).

    Andy

  5. #5
    Join Date
    Jan 2012
    Posts
    29
    I've been looking at another option which is the LOAD with CURSOR file type.

    Is there anyone who implemented or know how to use it without federating the servers?

    Thanks

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    bulk load

    export the data in del format and use load from this file
    cursor is only possible while being connected or using nickname
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    there is a way. take some time to watch the db2nightshow #73. Someone is explaining about just THAT issue. there are some "undocumented features" in the cursuor command. Exact what you need.
    You could also go to the slides of the latest IDUG in prague. Go thru the slides of the presentations by Michael Tiefenbacher. You will find the syntax there as well (and learn some more wonderfull things).
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    these "undocumented features" are not documented in the online infocenter, but look at the movement guide (pdf version of db2 books) and there they are...
    but in this case the source is sqlserver - the load will not work if federation is not active
    Last edited by przytula_guy; 03-13-12 at 10:22.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by dr_te_z View Post
    You could also go to the slides of the latest IDUG in prague.
    Are they available free of charge?

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by db2girl View Post
    Are they available free of charge?
    Not yet. I guess it will be, later in the year.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I found an example of CURSOR DATABASE here:
    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •