How is called DB2 SQL Dialect?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matthew - 24 Nov 2007 12:06 GMT

Hi everyone,

i'm really newbie in IBM DB2 but I have to know how is IBM DB2 Dialcet
called? In oracle we have PL/SQL, in SQL Server we have T-SQL. What
about DB2? Is it SQL PL, or DB2 SQL Dialect?

Thanks in advance for any reposne.
Matthew

Reply to this Message

Serge Rielau - 24 Nov 2007 13:41 GMT

> i'm really newbie in IBM DB2 but I have to know how is IBM DB2 Dialect
> called? In oracle we have PL/SQL, in SQL Server we have T-SQL. What
> about DB2? Is it SQL PL, or DB2 SQL Dialect?

*shrug* DB2 supports SQL.
DB2's procedural language is compliant with the SQL/PSM standard.
MySQL supports the same language.

Since questiosn like yours keep popping up DB2's procedural language
capabilities also go by "SQL PL".. But it's just "SQL"...

Note that in Oracle PL/SQL refers to the procedural constructs only.
"SELECT * FROM DUAL" is NOT PL/SQL. It's just SQL.
x := y IS PL/SQL

AFAIK MS SQL Server is the only product that has named there SQL
dialect: T-SQL. No wonder - it is.... unique indeed.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

deangc - 25 Nov 2007 06:01 GMT

> AFAIK MS SQL Server is the only product that has named there SQL
> dialect: T-SQL. No wonder - it is.... unique indeed.

I believe that T-SQL comes from Transact-SQL, which was more-or-less
shared with Sybase way back. The last time I checked, Sybase still
calls their dialect Transact-SQL.

Nobody supports all of the ANSI standard, and everyone offers
extensions. SQL Server is no more unusual in this regard than anyone
else.

Reply to this Message

Serge Rielau - 25 Nov 2007 12:16 GMT

> Nobody supports all of the ANSI standard, and everyone offers
> extensions. SQL Server is no more unusual in this regard than anyone
> else.

[hello].[world] is rather unusual in my book....
and then there is:
'hello' + 'world'

There is a difference between extensions and plain incompatibilities at
the core of the very language.

Anyway the point I was making is that "TSQL" is the only SQL Dialect.
All the others are names for the procedural language aspect only:
PL/SQL, SPL, SQL PL, pgPL/SQL (did I get this right??).

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

DA Morgan - 25 Nov 2007 14:35 GMT

>> Nobody supports all of the ANSI standard, and everyone offers
>> extensions. SQL Server is no more unusual in this regard than anyone
[quoted text clipped - 12 lines]
> Cheers
> Serge

Except for that unnamed abomination in MS Access.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

Reply to this Message

deangc - 26 Nov 2007 18:35 GMT

> Except for that unnamed abomination in MS Access.

The less said about that the better.

Reply to this Message

deangc - 26 Nov 2007 18:25 GMT

> [hello].[world] is rather unusual in my book....

It's an alias for "hello"."world", not so unusual.

> and then there is:
> 'hello' + 'world'

Yes, that is a bit unusual, I agree.

> There is a difference between extensions and plain incompatibilities at
> the core of the very language.

Neither of the above are incompatibilities, though. Is 'II' defined as
the concatenation operator in SQL3? As far as I know (and my knowledge
doesn't match yours, I know) it is not.

> Anyway the point I was making is that "TSQL" is the only SQL Dialect.
> All the others are names for the procedural language aspect only:
> PL/SQL, SPL, SQL PL, pgPL/SQL (did I get this right??).

PL/SQL has, as you know, many extensions that have little to do with
the procedural part of the language. I would argue that it is even
more a 'dialect' than T-SQL. But you are right: the only ones that I
know of that explicitly name themselves a dialect are T-SQL and
Transact-SQL.

I have used SQL Server 6.5, 7.x, 2000, DB2 7.x, 8.x, 9.x, Oracle 8.x,
9.x (that experience is getting old now) and PROGRESS 8.x, 9.x, and I
think that of those products DB2 is the most ideologically 'pure'.
Until 9.5, that is... :)

Reply to this Message

Serge Rielau - 26 Nov 2007 18:53 GMT

> Until 9.5, that is... :)

It was one of those days...too much cranberry juice

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

DA Morgan - 27 Nov 2007 00:22 GMT

>> Until 9.5, that is... :)
> It was one of those days...too much cranberry juice

A little vodka, a few ice cubes, a slice of lime, and it will all get
better.

Signature

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Reply to this Message

Dave Hughes - 26 Nov 2007 23:58 GMT

>> [hello].[world] is rather unusual in my book....
>
> It's an alias for "hello"."world", not so unusual.

It is rather unusual - especially seeing as some databases use brackets
for array indexing (and appears to be what the SQL standard specifies for
array indexing).

>> and then there is:
>> 'hello' + 'world'
[quoted text clipped - 5 lines]
>
> Neither of the above are incompatibilities, though.

How do you conclude they're not incompatibilities? They're not compatible
with the SQL standard, and (unsurprisingly) aren't compatible with several
rather commonly used database implementations. I suppose one could
classify them as "extensions" if the standard identifier quoting ("") and

|| concatenation are supported too, although from a brief glance at the

SQL Server 2005 reference that only appears to be the case for quoted
identifiers [1] (there doesn't appear to be any support for the standard

|| concatenation operator [2]).

[1] http://technet.microsoft.com/en-us/library/ms174393.aspx
[2] http://technet.microsoft.com/en-us/library/ms177561.aspx

Speaking of which...

> Is 'II' defined as
> the concatenation operator in SQL3? As far as I know (and my knowledge
> doesn't match yours, I know) it is not.

Assuming by SQL3 you mean SQL2003, and by 'II' you mean ||, it certainly
is defined as the concatenation operator. Always has been and therefore
probably always will be (at least in the interests of backward
compatibility). I don't see any support in the standards for + as a
concatenation operator.

>> Anyway the point I was making is that "TSQL" is the only SQL Dialect.
>> All the others are names for the procedural language aspect only:
>> PL/SQL, SPL, SQL PL, pgPL/SQL (did I get this right??).

Apparently it's PL/pgSQL [3], but close enough :)

[3] http://www.postgresql.org/docs/8.2/static/plpgsql.html

> PL/SQL has, as you know, many extensions that have little to do with
> the procedural part of the language.

I'm splitting hairs here but still ... It does seem to be common practice
to refer to Oracle's entire SQL dialect as "PL/SQL", although strictly
speaking that's wrong (note the separate reference manuals at [4]). Hence
if these extensions aren't defined in the procedural part of the language,
then they're nothing to do with PL/SQL (which simply refers to the
procedural part of Oracle's dialect).

[4]
http://www.oracle.com/pls/db111/portal.portal_db?selected=5&frame=#sql_and_pl_sq
l_languages

> I would argue that it is even
> more a 'dialect' than T-SQL. But you are right: the only ones that I
[quoted text clipped - 5 lines]
> think that of those products DB2 is the most ideologically 'pure'.
> Until 9.5, that is... :)

I haven't used 9.5 yet, but from what little I've read about the
compatibility enhancements it would appear that most which alter
fundamental behaviours in the dialect aren't activated by default (i.e.
they have to be explicitly enabled). So, one could easily classify them
"extensions" rather than "incompatibilities" (to refer to the earlier
point). Is a database still "ideologically pure" if it's got "extensions"
rather than "incompatibilities"? (although perhaps a more pertinent
question would be "does anyone care about the ideological purity of
databases?" ;-)

Pedantically,

Dave.

Reply to this Message

Will Honea - 28 Nov 2007 03:47 GMT

> I haven't used 9.5 yet, but from what little I've read about the
> compatibility enhancements it would appear that most which alter
[quoted text clipped - 5 lines]
> question would be "does anyone care about the ideological purity of
> databases?" ;-)

I don't know about purity, but at least Express-C 9.5 (LUW) will install on
openSUSE 10.3 - all earlier versions crap out trying to start the java
installer or (in the case of an update install) the CC. Even the 9.5 code
complains about not being able to unlock something because it isn't locked,
then complains that the same resource can't be locked because it's still
locked. The rest of the error message blames the problem on the
application coding <g> so it's likely a java thing. At least everything
runs. It may be my inagination, but the CC seems to somewhat faster than
previous versions.

Signature

Will Honea

--
Posted via a free Usenet account from http://www.teranews.com

Reply to this Message

Lennart - 27 Nov 2007 06:26 GMT

[...]

> I have used SQL Server 6.5, 7.x, 2000, DB2 7.x, 8.x, 9.x, Oracle 8.x,
> 9.x (that experience is getting old now) and PROGRESS 8.x, 9.x, and I
> think that of those products DB2 is the most ideologically 'pure'.
> Until 9.5, that is... :)

Havent tried 9.5 myself, so I'm curious on what in 9.5 you concider
ideologically 'unpure'?

/Lennart

Reply to this Message

Serge Rielau - 27 Nov 2007 11:33 GMT

> [...]
>> I have used SQL Server 6.5, 7.x, 2000, DB2 7.x, 8.x, 9.x, Oracle 8.x,
[quoted text clipped - 4 lines]
> Havent tried 9.5 myself, so I'm curious on what in 9.5 you concider
> ideologically 'unpure'?

I think Dean is referring to this:
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0707rielau/

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

deangc - 27 Nov 2007 18:27 GMT

> I think Dean is referring to this:http://www.ibm.com/developerworks/db2/library/techarticle/dm-0707rielau/

Yes. I was hoping it would be seen as a gentle joke. I should know by
now that people are sensitive about their RDBMS of choice.

Reply to this Message

deangc - 27 Nov 2007 18:53 GMT

> Havent tried 9.5 myself, so I'm curious on what in 9.5 you concider
> ideologically 'unpure'?

It was a joke. 9.5 adds support for a number of Oracle extensions,
including the dreaded (+) outer join syntax.

I understand why it was done, but it is impure.

Reply to this Message

Serge Rielau - 27 Nov 2007 19:33 GMT

>> Havent tried 9.5 myself, so I'm curious on what in 9.5 you concider
>> ideologically 'unpure'?
[quoted text clipped - 3 lines]
>
> I understand why it was done, but it is impure.

I think those who knew about these features got that it was a joke. :-)

But, yes, people tend to be sensitive on such matter... All is relative
however. In some other groups this thread would have long turned abusive :-(

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Lennart - 28 Nov 2007 06:07 GMT

> > Havent tried 9.5 myself, so I'm curious on what in 9.5 you concider
> > ideologically 'unpure'?
>
> It was a joke. 9.5 adds support for a number of Oracle extensions,
> including the dreaded (+) outer join syntax.

My guess was either that or something related to xml. Anyhow, as
mentioned I was just curious

/Lennart

[...]

Reply to this Message