CODEWITHSUNDEEP
sqldatabasedb2
Magnus
Magnus

Reputation: 760

Is my SQL version determined by the database I'm accessing or by my SQL client?

I have a SQL query I want to optimize, so I asked the database owner what version of SQL they were using (since ordinary methods didn't seem to have support). They answered that my version of SQL is not decided by them but by my local SQL client. They claimed to use a system called "DB2", with support for multiple SQL dialects.

I then went on to ask our IT department which version of SQL our client was using (that client being Squirrel SQL). After some fiddling around they logged on to the database, queried it and reported the version of SQL to be DB2.[sequence of decimals].

This is probably a stupid question, but would someone mind clarifying this?

Upvotes: 1

Views: 72

Answers (2)

Nathan Hughes
Nathan Hughes

Reputation: 96424

There is an industry standard called ANSI SQL that database vendors implement. Then on top of that they tack on non-standard proprietary stuff, extra commands, keywords, procedural stuff like stored procedures and triggers and cursors, that are not covered by a standard but which they expect will provide useful features that will differentiate them from the competition.

For Db2 11’s compliance with standards see https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.common.doc/doc/c0011215.html. The actual spec is behind a paywall so this is not that helpful. See https://www.whoishostingthis.com/resources/ansi-sql-standards/#sql-ansi-standards-for-database-administration for an explanation of ANSI SQL standards.

Different db2 products (Z/OS, LUW) might have different extensions. Z/OS has to do horrible mainframey stuff that LUW can do without. But you wouldn’t be given a choice, you have to use the commands implemented by the database that you are connected to. The SQL client doesn’t have a role in this.

Upvotes: 2

user330315
user330315

Reputation:

"They answered that my version of SQL is not decided by them but by my local SQL client"

That's complete and utter nonsense - those people apparently have no idea what they are talking about.

The SQL dialect that is understood by the server is only defined by that server. The client has absolutely nothing to do with that. If the database server doesn't support some specific feature, no SQL client will change that.

Upvotes: 3

Related Questions