How is called DB2 SQL Dialect?

[Matthew]

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

[Serge Rielau]

Matthew wrote:

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[deangc]

On Nov 24, 5:41 am, Serge Rielau <[e-mail]:

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.

[Serge Rielau]

deangc wrote:

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[DA Morgan]

Serge Rielau wrote:

deangc wrote:

>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

Except for that unnamed abomination in MS Access.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu (replace x with u to respond)

[deangc]

On Nov 25, 4:16 am, Serge Rielau <[e-mail]:

[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... :)

[deangc]

On Nov 25, 6:35 am, DA Morgan <[e-mail]:

>
Except for that unnamed abomination in MS Access.

The less said about that the better.

[Serge Rielau]

deangc wrote:

Until 9.5, that is... :)

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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Dave Hughes]

On Mon, 26 Nov 2007 18:25:44 -0000, deangc <de***********@gmail.comwrote:

On Nov 25, 4:16 am, Serge Rielau <[e-mail]:

>[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'

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.

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/l.../ms174393.aspx
[2] http://technet.microsoft.com/en-us/l.../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/port..._sql_languages

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... :)

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.

[DA Morgan]

Serge Rielau wrote:

deangc wrote:

>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.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
da******@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Lennart]

On Nov 26, 7:25 pm, deangc <[e-mail]:
[...]

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

[Serge Rielau]

Lennart wrote:

On Nov 26, 7:25 pm, deangc <[e-mail]:
[...]

>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'?

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

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[deangc]

I think Dean is referring to this:http://www.ibm.com/developerworks/db...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.

[deangc]

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.

[Serge Rielau]

deangc wrote:

>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.

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Will Honea]

Dave Hughes wrote:

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?" ;-)

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 <gso 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.

--
Will Honea

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

[Lennart]

On Nov 27, 7:53 pm, deangc <[e-mail]:

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

[...]