Results 1 to 10 of 10
  1. #1
    Join Date
    May 2009
    Posts
    8

    Unanswered: how export database db2 to SQL Server 2000 and MySQL

    sorry I newbie in here. how export database db2 to SQL Server 2000 and MySQL? please help me

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    zero1987, You can export data in DB2 (you don't mention what version or operating system) to a CSV (comma separated variable) file. SQL Server can accept this file (I would use a DTS (Data Transformation Service) to do this). I assume MySQL can also handle CSV as it is a fairly standard format.

    There may be other ways, such as linking to the DB2 from SQL Server and going directly from table to table but I have never done this.

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    I've once tried to replicate a few tables from DB2 (z/OS) to MySQL through an application (running on Linux and connecting to both DB2 and MySQL through ODBC; it took terribly long (and consumed an unexpected amount of CPU on the mainframe).
    After that, I used UNLOAD in DB2 to produce a CSV file, then ftp-ing that file to Linux where I LOAD it into MySQL; all this takes about 100x less time than the first method.

    Here are the exact statements:

    DB2 utility command:
    UNLOAD DATA FROM TABLE mytbl DELIMITED CHARDEL X'7D' UNLDDN filename

    Ftp commands:
    ASCII
    SITE 'charset(sw88591)'
    GET filename
    BYE

    MySQL command:
    load data local infile 'filename' replace into table mytbl character set latin1 fields terminated by ',' optionally enclosed by '''';
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    May 2009
    Posts
    8
    Quote Originally Posted by Stealth_DBA
    zero1987, You can export data in DB2 (you don't mention what version or operating system) to a CSV (comma separated variable) file. SQL Server can accept this file (I would use a DTS (Data Transformation Service) to do this). I assume MySQL can also handle CSV as it is a fairly standard format.

    There may be other ways, such as linking to the DB2 from SQL Server and going directly from table to table but I have never done this.
    I use DB2 version 9 in windows xp.I don't understand how export to csv in DB2. please give me tutorial how export to csv.because I am still study DB2 so I still do not understand way export database DB2. please help me

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    zero1987, the easiest way is to use the Control Center.

    Right Click on a Table and select Export... from the pop-up menu.
    Then it is just a matter of filling in the information.

    Output file box: Type in a path\file name. (you can click on the box with ... to open an explorer type window to find the destination).

    File Format: Delimited should be selected. (if you want to change the default of the delimiters click on the Options button).

    Select statement: Enter your select statement. A SELECT * FROM table-name is already entered but you can change it to anything you like.

    Message File box: Enter a path\file name for the Messages file.

    Unless you are doing some thing real fancy, you can ignore the Columns and Schedule tabs at the top.

    PS You can click on the SHOW COMMAND button to see the actual command you will be running. If you like, you can copy it out and run it from a command line (or script file). You might want to do this if you have a LOT of tables to export.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    how about reading this
    EXPORT
    and see for del
    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
    May 2009
    Posts
    8
    thanks all

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I would suggest you use IXF format for your files. If I am not mistaken it is readable by all db's. You might save your self some hassle if you have special characters in your data.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Nov 2009
    Posts
    1
    Try using:

    MyDbExplorer

  10. #10
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Cougar8000 View Post
    I would suggest you use IXF format for your files. If I am not mistaken it is readable by all db's.
    Can anyone confirm? I'm afraid he is misaken because IXF is a db2 dedicated format. But it is Cougar8000 talking here so I am starting to doubt....

Posting Permissions

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