By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,683 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

Problem with a simple union

P: n/a
I am relatively new to DB2 and having a problem with a simple union
statement.

Running Db2UDB version 8.1.1 on Aix 5.1

The union and union all SQL statements I am running produce the same
results. No duplicates are being eliminated in the union.

To verify, I ran SQL with union statements against system tables.

Same result.

An example:

db2 "select tabschema from syscat.tables
union
select tabschema from sysstat.tables"

db2 "select tabschema from syscat.tables
union all
select tabschema from sysstat.tables"

Both queries produce the same results with duplicates in both.

Both tabschema columns are in the same location (1st) and both are
varchar(128).

What am I doing wrong? Would appreciate any thoughts.

Thanks
Gerry
sn********@sbcglobal.net
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Not really sure why you are seeing what you see - if I try the exact same on DB2 UDB V8.1
FP #7 (V8.2) I get the expected results:

C:\>db2 "select tabschema from syscat.tables union select tabschema from sysstat.tables"

TABSCHEMA
-------------------------------------------------------------------------------
SYSCAT
SYSIBM
SYSSTAT
SYSTOOLS

4 record(s) selected.

The UNION ALL SELECT returns 413 records with duplicates ... as you can see the above did
not return duplicates ...

Strange ... but does not seem you are doing anything wrong ...

--

Bob
Engagement Specialist - DB2 Information Management Software - IBM Software Group
IBM Toronto Lab
[My comments are solely my own and are not meant to represent an official IBM position -
ask my cat!]

"Gerry" <sn********@sbcglobal.net> wrote in message
news:70*************************@posting.google.co m...
I am relatively new to DB2 and having a problem with a simple union
statement.

Running Db2UDB version 8.1.1 on Aix 5.1

The union and union all SQL statements I am running produce the same
results. No duplicates are being eliminated in the union.

To verify, I ran SQL with union statements against system tables.

Same result.

An example:

db2 "select tabschema from syscat.tables
union
select tabschema from sysstat.tables"

db2 "select tabschema from syscat.tables
union all
select tabschema from sysstat.tables"

Both queries produce the same results with duplicates in both.

Both tabschema columns are in the same location (1st) and both are
varchar(128).

What am I doing wrong? Would appreciate any thoughts.

Thanks
Gerry
sn********@sbcglobal.net

Nov 12 '05 #2

P: n/a
Turns out it was a problem with the way the statement was being parsed
by Putty, my telnet client.

Even weirder, after 2 days of parsing it in a manner which gave union
all results no matter how it was entered, it suddenly stopped.
Thanks
Gerry

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.