Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    48

    Wink Unanswered: Any Tools you know to compare data in tables on OS390

    Hello,

    We have a process where we move data from 1 environment to another like UNIT test to SYSTEM and then to QUALITY and PROD etc. Here the tables structures are same but we need to move only the changed data from 1 system to other.

    Have you guys have any suggestion for any tools available for OS390 ?

    Have you used any tools to compare data on same tables ( in diff environment) ? We are using DB2 Ver 7 on OS390.

    Thanks

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Can you use DB2 Connect in your environment ?

    DB2 LUW has a powerful set operator, EXCEPT ..

    (
    select 1,2 from sysibm.sysdummy1
    union
    select 3,4 from sysibm.sysdummy1
    )
    except
    select 1,2 from sysibm.sysdummy1

    will give you the result as (3,4)

    You can create federated connections to the tables and then issue the queries from your workstation ... Of course, this may underperfrom for large tables

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Apr 2004
    Posts
    48
    we do have DB2 connect and we use it only to access data from mainframe to MS ACCESS. What is DB2 LUW ? and how can I use it on Mainframe ? Also what is federated connections ? How can I do a federated connection to DB2 OS390 ?



    Thanks

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    DB2 LUW is DB2 on Distributed Platforms(LUW - Linux, Unix and Windows) ...

    You can create a local database at the (test) Connect Gateway and create federated connection to DB2 on host ...

    The mechanism that allows a user connected to one database (LUW database) to access data on another database (Host DB2 subsytem) is called Federation .

    To set up federated connection you will have to define a wrapper, server and nickname(this is the local alias name of the remote table) at the minumum and usermapping, most likely ...

    Say, for example, you have TEST.TABLE1 and PROD.TABLE1 on host. Define the nicknames as TESTDB.TABLE1 and PRODDB.TABLE1 on your LUW database, then you can do
    select * from testdb.table1 except select * from proddb.table1

    This will select all rows in table1 not present in table2 ...

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Apr 2004
    Posts
    48
    Thanks for the discription Sathyaram,

    We have 4 test instances and 1 prod all having arround 300 tables each. The instances are :
    TDB2PLDU
    TDB2PLDD
    TDB2PLDS
    TDB2PLDQ
    PDB2PLDP

    ON MAINFRAME
    and data moves in the same order i.e U 2 D 2 S 2 Q 2 P

    We also can access data from mainframe to MS ACCESS thru DB2 Connect. Have defined different databases on Access and when we connect we connect to proper databases.

    Once I invoke access and connect it to one database say U instance, To access data from some other instance say Q then I need to invoke a separate copy of ACCESS. What I mean to say is that I cannot access data from 2 different instances in the same invokation of access.

    So I cannot run the select you have suggested i.e "Say, for example, you have TEST.TABLE1 and PROD.TABLE1 on host. Define the nicknames as TESTDB.TABLE1 and PRODDB.TABLE1 on your LUW database, then you can do
    select * from testdb.table1 except select * from proddb.table1 "


    Can we call the above set up as federated connection ? What else can I do to get the desired result ?


    Thanks

Posting Permissions

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