Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Column names with spaces - How could db2 allow its creation?

2,117 views
Skip to first unread message

Arun Srini

unread,
May 20, 2011, 3:53:40 PM5/20/11
to
I just want to know how db2 can allow creating columns with spaces
between them . I was able to create this in db2 udb 9.7

create table arun.test1("a b" int);

and was able to access the row when I use double quotes. Is this ok?
If there are information regarding how and why it can happen please
link them in the reply. Is this a codepage issue?

Lennart Jonsson

unread,
May 20, 2011, 5:17:52 PM5/20/11
to
On 2011-05-20 21:53, Arun Srini wrote:
> I just want to know how db2 can allow creating columns with spaces
> between them . I was able to create this in db2 udb 9.7
>
> create table arun.test1("a b" int);
>
> and was able to access the row when I use double quotes. Is this ok?

Yes, but I would advise against it. Normally db2 stores identifiers such
as tablenames in upper case, and also transforms them to uppercase when
used in queries. But if you quote an identifier with double quotes, it
is stored as is in the catalog. Example:

create table arun.test1("ab" int);
select * from arun.test1 where ab = 3
SQL0206N "AB" is not valid in the context where it is used. SQLSTATE=42703

select * from arun.test1 where "ab" = 3
ab
-----------

0 record(s) selected.

/Lennart

Serge Rielau

unread,
May 20, 2011, 7:12:06 PM5/20/11
to
Using double quotes forces DB2 to take the identifiers "as-is"
Using spaces is definitely not best practices.
If you want to pile insult ontop of injury you can escape the double quotes:
"a""b".. yummy.
Not all features that are legal are encouraged....

Cheers
Serge


--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

Will Honea

unread,
May 20, 2011, 11:32:55 PM5/20/11
to
Serge Rielau wrote:

> Not all features that are legal are encouraged....

That also applies to names with apostrophes. O'Grady drives me nuts!

--
Will Honea

--CELKO--

unread,
May 21, 2011, 9:45:24 AM5/21/11
to
Yes, double quotes are part of the ANSI/ISO standards. It is one of
our biggest mistrakes. Do not use it. Follow the ISO rules aoubt using
only alphas, digits and underscores; this will let your data element
names will port to all the X3J languages and other tools.

There are bad SQL programmers who use this feature to do display
formatting in the database. When someone does this, it is a symptom of
other bad code. They do not understand how a tiered architecture
works.

Arun Srini

unread,
May 21, 2011, 1:55:46 PM5/21/11
to

This was the explanation I was looking forward to. Thanks Joe.

0 new messages