Problem with a simple union

[Gerry]

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

[Bob [IBM]]

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

[datapro01]

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