Urgent!! Cross database select in IBM DB2

Posted on 2005-04-13
Last Modified: 2012-08-13
I have to database d1 and d2 both have a table called USR, it stored difference user informations and have a common id, how can I use one select statement to show all user informations?

I have tried the following but failed:

select UT1.ID from USER1.D1.USR UT1, USER2.D2.USR UT2 where UT1.ID = UT2.ID

Thank you very much.
Question by:sicyon
    LVL 45

    Accepted Solution


    You're trying to use SQL-SERVER syntax.  Remove the userid from the table name and you should be fine.

    select UT1.ID from D1.USR UT1, D2.USR UT2 where UT1.ID = UT2.ID

    LVL 13

    Assisted Solution

    in order to reference tables from different databases, you have to set up a link between the two databases.
    You do this by creating a wrapper and a nickname, the procedures vary depending on the type of platform (OS) you are on. If your wrapper and nickname is already created, then you just have an SQL syntax problem as Kent suggests, or post the error message that you are getting.
    LVL 13

    Expert Comment

    I do think that the answers given were quite valid, therefoire the points should be split among ghp7000 and kdo

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now