• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

Urgent!! Cross database select in IBM DB2

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.
  • 2
2 Solutions
Kent OlsenData Warehouse Architect / DBACommented:

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

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.
I do think that the answers given were quite valid, therefoire the points should be split among ghp7000 and kdo

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now