Db2

 View Only

  • 1.  How can I export DB2 schema from AS400 and import into Windows?

    Posted Thu January 16, 2020 11:17 PM
    I tried IBM Data Movement Tool and it always stuck at unload.cmd
    [2020-01-16 18.03.57.703] Driver com.ibm.as400.access.AS400JDBCDriver loaded
    [2020-01-16 18.03.58.328] Database Product Name :DB2 UDB for AS/400
    [2020-01-16 18.03.58.328] Database Product Version :07.01.0000 V7R1m0
    [2020-01-16 18.03.58.328] JDBC driver AS/400 Toolbox for Java JDBC Driver Version = 12.1
    [2020-01-16 18.03.58.328] Database Major Version :7
    [2020-01-16 18.03.58.328] Database Minor Version :1
    [2020-01-16 18.03.58.328] Unknown Version for idb2
    [2020-01-16 18.03.58.328] Starting Blades
    [2020-01-16 18.03.58.343] Starting Blade_1 and building memory map.
    [2020-01-16 18.03.58.343] Starting Blade_3 and building memory map.
    [2020-01-16 18.03.58.343] Starting Blade_0 and building memory map.
    [2020-01-16 18.03.58.343] Starting Blade_2 and building memory map.
    [2020-01-16 18.03.58.343] Starting Blade_4 and building memory map.
    [2020-01-16 18.04.00.484] 0 values cached in checkConstraintMap
    [2020-01-16 18.04.09.466] 36407 default values cached in defaultValuesMap

    I have barely no knowledge about iSeries and have connected to the data source using DBVisualizer as well.
    How to generate DDL , please shed some light on this. Thanks!

    ------------------------------
    zikang cao
    ------------------------------

    #Db2


  • 2.  RE: How can I export DB2 schema from AS400 and import into Windows?

    Posted Fri January 17, 2020 03:30 AM
    you only want the dabase schema (and not the data) ?
    Have you tried "db2look"  ?   https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002051.html

    see this page (for Db2 v11.1) "Mimicking databases using db2look"
    https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.trb.doc/doc/c0020703.html

    db2look can export the database schema objects to ddl file(s) "as create commands" 
    Notice, with some older Db2 versions, it sometimes does not extract the objects in the correct order, so you might have to edit/correct that manually (f.i. view definitions could be mentioned before the table definition(s) it relies on)

    ------------------------------
    Erwin Hattingh
    Systems Engineer / Db2 DBA
    Triodos Bank
    ------------------------------

    Original Message


  • 3.  RE: How can I export DB2 schema from AS400 and import into Windows?

    IBM Champion
    Posted Fri January 17, 2020 05:38 AM
    unfortunatelly I don't think Erwins approach will work as Db2 on iSeries is completly different than any other Db2!
    There is a QSYS2.TABLES containing all information about all tables (like SYSCAT.TABLES on LUW). Connecting that with the QSYS2.COLUMNS should at least give you all the needed info.
    You may have to write yourself some little program creating DDL out of that as I don't think db2look will work against these!

    ------------------------------
    Sebastian Zok
    ------------------------------

    Original Message


  • 4.  RE: How can I export DB2 schema from AS400 and import into Windows?

    Posted Fri January 17, 2020 08:59 AM
    If you have IBM i Access Client Solutions installed on a PC click on the "Schemas" section. You will see a list of available schemas in the database. You can right click on the schema name and choose "Generate SQL". It will generate the SQL for the schema and all the tables, views, etc... inside. 

    I think the old Client Access will do the same thing, but getting to the schema list might be a little different.

    ------------------------------
    Jeremy Bowling
    ------------------------------

    Original Message