Bug?

[Bob Stearns]

I saw this from a developer of a third party I really like and wondered
if it was true and if there was any known wy around it?

DB2 and/or the DB2 JDBC driver has a bug/limitation. Through the JDBC
driver you can't execute an SQL statement with a carriage return or line
feed, unless it is in a CREATE TRIGGER, PROCEDURE or FUNCTION. This
doesn't really matter to the user, except for a CREATE VIEW ... when you
try to retrieve the text. So, before any query is executed on DB2
through ADS, ADS will check to see if it is a CREATE TRIGGER, FUNCTION
or PROCEDURE, and if it isn't then it will remove all of the carriage
returns and line feeds. Thus, when you retrieve the CREATE VIEW
statement it will not have them.

This bug has been around for years and is well known. I am not sure why
IBM hasn't fixed it. Although I am not 100% sure if it is the driver or
the server, I believe it is a bug in the DB2 server.

[Serge Rielau]

Bob Stearns wrote:

I saw this from a developer of a third party I really like and wondered
if it was true and if there was any known wy around it?

DB2 and/or the DB2 JDBC driver has a bug/limitation. Through the JDBC
driver you can't execute an SQL statement with a carriage return or line
feed, unless it is in a CREATE TRIGGER, PROCEDURE or FUNCTION. This
doesn't really matter to the user, except for a CREATE VIEW ... when you
try to retrieve the text. So, before any query is executed on DB2
through ADS, ADS will check to see if it is a CREATE TRIGGER, FUNCTION
or PROCEDURE, and if it isn't then it will remove all of the carriage
returns and line feeds. Thus, when you retrieve the CREATE VIEW
statement it will not have them.

This bug has been around for years and is well known. I am not sure why
IBM hasn't fixed it. Although I am not 100% sure if it is the driver or
the server, I believe it is a bug in the DB2 server.

Actually this looks like it's working as designed.
Teh original thinking was that network traffic is costly, so it makes
sense to squeeze out all unnecessary whitespace.
Starting in DB2 V7 with SQL Procedures DB2 started preserving the
formatting of SQL Procedures, SQL Functions and Triggers. (because of
the logic and the embedded -- comment support)
Note that the stripping is doen by the clients, not the server.
I just checked and create view is still stripped in FP10 in CLP at least.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab