Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Mar 2009
    Posts
    73

    Unanswered: Migrate db2 express to iseries

    Hi,
    We are currently running an application developed on db2 express c database but now my boss wants to transfer it to the newly purchase Power6/Iseries machine . Is there a way to import all the tables along with their data tables, sequences, views etc from express c to the new machine ?

    I tried the IBM Migration tool but it don't have any option for import from db2 express c to iseries.

    Any help is appreciated, Mike.

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Google for:
    db2look
    db2move

  3. #3
    Join Date
    Mar 2009
    Posts
    4
    Search for create ddl in your db2 luw management tool.
    I know, in iseries this function is located in operations navigator which is not
    available for luw.

    If you don't find that, you can make a reverseengineering with hibernate
    and apply the mapping documents against iseries.

  4. #4
    Join Date
    Mar 2009
    Posts
    73
    Quote Originally Posted by dr_te_z
    Google for:
    db2look
    db2move
    Hi, thanks for the reply !
    I used db2look to generate the ddl and db2move to generate all the files successfully but now I'm facing another problem is how to import them inside the iSeries server.

    We dont have db2connect license. Although my tables are created there but how do I import the data using

    db2move import ? This command didnt worked inside the query command window inside the navigator.

    Guys please advise as necessary.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Very few people know much about DB2 iSeries, and as you now know it is quite a bit different than either DB2 for Linux/UNIX/Windows, or DB2 for z/OS.

    I would recommend that you try and convince your boss to convert that box to Linux or AIX (both run on the same hardware you have). Otherwise you will be in for some rough times ahead (not just with the migration).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Mar 2009
    Posts
    73
    Can you advise on how to import through csv's inside all the tables?

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Marcus_A
    I would recommend that you try and convince your boss to convert that box to Linux or AIX
    Not yet, not yet!
    Do not throw away you beauty AS/400 because of your first minor setback...
    Have you tried:
    to EXPORT a table into a .IXF file, copy that file to the AS/400 and load it again, or, even better
    connect the express database to your AS/400, create nicknames from your express-tables (google for "federation") and then do a load-from-cursor

  8. #8
    Join Date
    Mar 2009
    Posts
    73
    Hi, Thanks for keeping the spirits high !
    I have managed to export all the tables to ixf files successfully, but I have about 85 ixf files now. How to ftp all these in one go ? The transfer to Iseries tool is just for single transfers ? I tried ftping through a 3rd part programme(filezilla) for batch transfer but it wont show any directories inside QSYS.

    Is there a way to batch upload along with a batch restore for all the tables in the schema ?




    Quote Originally Posted by dr_te_z
    Not yet, not yet!
    Do not throw away you beauty AS/400 because of your first minor setback...
    Have you tried:
    to EXPORT a table into a .IXF file, copy that file to the AS/400 and load it again, or, even better
    connect the express database to your AS/400, create nicknames from your express-tables (google for "federation") and then do a load-from-cursor

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dr_te_z
    Not yet, not yet!
    Do not throw away you beauty AS/400 because of your first minor setback...
    Don't have to throw it away. Red Hat Linux or AIX runs on the same hardware.
    Last edited by Marcus_A; 06-03-09 at 08:12.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Mar 2009
    Posts
    4
    Hello,

    i would suggest to use the i5 Database.
    It is the more professional.
    If you buy a i5 to run luw on linux is like
    throwing money out of the window.

    i5 Know How is enough available at the Freelancer Market.

    Now some Ideas to import:

    Have a look at kettle - the easiest way to copy your schemas.

    2. possibliity - Use Hibernate
    1. Make a reverse Engineering from your existing database
    2. Make a source sesion and a destination session and copy your
    data into - finish.

    best regards

  11. #11
    Join Date
    Mar 2009
    Posts
    73
    Thanks for the reply,

    How to proceed about the source and destination copy sessions ?

    Regards, Mike.

    Quote Originally Posted by sagi7
    Hello,

    i would suggest to use the i5 Database.
    It is the more professional.
    If you buy a i5 to run luw on linux is like
    throwing money out of the window.

    i5 Know How is enough available at the Freelancer Market.

    Now some Ideas to import:

    Have a look at kettle - the easiest way to copy your schemas.

    2. possibliity - Use Hibernate
    1. Make a reverse Engineering from your existing database
    2. Make a source sesion and a destination session and copy your
    data into - finish.

    best regards

  12. #12
    Join Date
    Mar 2009
    Posts
    4
    first create DDL, what you have done so far.

    Source Session:

    HQL: from xyz

    targesSession.replicate(...)

  13. #13
    Join Date
    Mar 2009
    Posts
    73
    Hi thanks for the reply, here's what I did.

    1) Generated ddl through db2look on express C.
    2) All tables were successfully created on iSeries eliminating the entry "LOGGED NOT COMPACT" on lobs from the ddl.
    3) Exported all Tables from express c into delimited format.
    4) Ftped all tables to iSeries successfully.
    5) Errors faced in restoring tables through cypfrmimpf command on iseries on tables with clob data.

    When I exported the tables in delimited format which have lobs in them
    it made them like into separate files

    uqreqnote ,uqreqnote.001.lob

    Now when I try to import these through cpyimpf command I get a prompt

    **The selected table contains lob columns, which require the use of secondary stream files.**

    Now when I execute the command I get an error like
    Unable to access a file that is referred to by a file reference variable.

    It is also to note that when I try to ftp the files to iSeries, although they do successfully but it says like

    200 PORT subcommand request successful.
    150 Sending file to member UWRN in file UWRN in library CSV.
    226 File transfer completed successfully.
    ftp: 151779 bytes sent in 0.00Seconds 151779000.00Kbytes/sec.
    200 PORT subcommand request successful.
    150-Characters that are not recognized in file specification are changed to #.
    150 Sending file to member #01.LOB in file UWRN in library CSV.
    426-Records written to file UWRN in library CSV have been truncated. Data in fil
    e may not be valid.
    426 Data transfer ended.
    ftp: 6131 bytes sent in 0.00Seconds 6131000.00Kbytes/sec.

    Is it renaming the lob file hence it is not found ? How can I prevent this ? Any clue guys ?

    Regards, Mike.

  14. #14
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Dunno much about iSeries. What I do know about the FTP protocol that you can choose ascii or binary mode. It looks like you are set to ascii mode while copy-ing LOB's.
    I recall that .IXF files also contain header-info about the code-set. So it might be usefull to transfer everthing in 'binary' mode. The translation can be done by DB2.

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dr_te_z
    Dunno much about iSeries. What I do know about the FTP protocol that you can choose ascii or binary mode. It looks like you are set to ascii mode while copy-ing LOB's.
    I recall that .IXF files also contain header-info about the code-set. So it might be usefull to transfer everthing in 'binary' mode. The translation can be done by DB2.
    For someone who doesn't much iSeries you are giving a lot of advice. For most file transfers between Linux and iSeries, you will need to do an ASCII to EBCDIC translation unless it is truly a binary file.

    I don't know for sure about iSeries, but the IXF format in DB2 z/OS (actually it is in QMF for z/OS) is completely incompatible with the IXF for in DB2 for Linux, UNIX, and Windows. Since both iSeries and z/OS are EBCDIC character sets, I would be careful that.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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