|
Problem IC76686
|
Status: Closed |
ALTER PROCEDURE statement doesn't keep LF at the end of each line as CREATE PROCEDURE statement when sent to server via CLP. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
The DDL of ALTER PROCEDURE statement is sent to server in single
line if -- style of comment is not used. It is against the
behavior of CREATE PROCEDURE which keeps all LFs and DDL are in
multiple lines as given to CLP.
So, CLP should add Line Feed (0x0A) at the end of each line for
the ALTER PROCEDURE statement so that the behavior is consistent
with CREATE PROCEDURE command. The linefeed will preserve any
new lines used in the ALTER PROCEDURE statement so that when the
DDL is produced for an ALTER PROCEDURE statement, its output
will not be in a single line.
At present, when we write following alter procedure command in a
CLP script and execute it using "db2 -td@ -vf filename" :
alter procedure majason.sp1 replace version v1 ()
DISABLE DEBUG MODE
ISOLATION LEVEL CS
LANGUAGE SQL
P1: BEGIN
-- Test Insert
INSERT INTO T1 VALUES( /* need to find why
this multiline comment fails */
-- here it should work.
11, -- see what happens here.
55); /* if there is a ' or " in th */
END P1@
The output at present comes as below in single line. Yes, there
is an LF at the end of -- style comment.
alter procedure majason.sp1 replace version v1 () DISABLE DEBUG
MODE ISOLATION LEVEL CS LANGUAGE SQL P1: BEGIN INSERT INTO T1
VALUES( /* need to find why this multiline comment fails */ 11,
-- see what happens here.
55); /* if there is a ' or " in th */ END P1
DB20000I The SQL command completed successfully.
The above output should come as below:
alter procedure majason.sp1 replace version v1 ()
DISABLE DEBUG MODE
ISOLATION LEVEL CS
LANGUAGE SQL
P1: BEGIN
-- Test Insert
INSERT INTO T1 VALUES( /* need to find why
this multiline comment fails */
-- here it should work.
11, -- see what happens here.
55); /* if there is a ' or " in th */
END P1
DB20000I The SQL command completed successfully. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* The DDL of ALTER PROCEDURE statement is sent to server in *
* single *
* line if -- style of comment is not used. It is against the *
* *
* behavior of CREATE PROCEDURE which keeps all LFs and DDL are *
* in *
* multiple lines as given to CLP. *
* *
* *
* *
* So, CLP should add Line Feed (0x0A) at the end of each line *
* for *
* the ALTER PROCEDURE statement so that the behavior is *
* consistent *
* with CREATE PROCEDURE command. The linefeed will preserve *
* any *
* new lines used in the ALTER PROCEDURE statement so that when *
* the *
* DDL is produced for an ALTER PROCEDURE statement, its *
* output *
* will not be in a single line. *
* *
* *
* *
* At present, when we write following alter procedure command *
* in a *
* CLP script and execute it using "db2 -td@ -vf filename" : *
* *
* *
* *
* alter procedure majason.sp1 replace version v1 () *
* *
* DISABLE DEBUG MODE *
* *
* ISOLATION LEVEL CS *
* *
* LANGUAGE SQL *
* *
* P1: BEGIN *
* *
* -- Test Insert *
* *
* INSERT INTO T1 VALUES( /* need to find why *
* *
* this multiline comment fails *
* */ *
* -- here it should work. *
* *
* 11, -- see what happens here. *
* *
* 55); /* if there is a ' or " in th */ *
* *
* END P1@ *
* *
* *
* *
* *
* *
* The output at present comes as below in single line. Yes, *
* there *
* is an LF at the end of -- style comment. *
* *
* *
* *
* alter procedure majason.sp1 replace version v1 () DISABLE *
* DEBUG MODE ISOLATION LEVEL CS LANGUAGE SQL P1: BEGIN INSERT *
* INTO T1 VALUES( /* need to find why this multiline comment *
* fails */ 11, -- see what happens here. *
* 55); /* if there is a ' or " in th */ END P1 *
* DB20000I The SQL command completed successfully. *
* *
* *
* The above output should come as below: *
* *
* *
* *
* alter procedure majason.sp1 replace version v1 () *
* *
* DISABLE DEBUG MODE *
* *
* ISOLATION LEVEL CS *
* *
* LANGUAGE SQL *
* *
* P1: BEGIN *
* *
* -- Test Insert *
* *
* INSERT INTO T1 VALUES( /* need to find why *
* *
* this multiline comment fails *
* */ *
* -- here it should work. *
* *
* 11, -- see what happens here. *
* *
* 55); /* if there is a ' or " in th */ *
* *
* END P1 *
* *
* DB20000I The SQL command completed successfully. *
****************************************************************
* RECOMMENDATION: *
* Apply v9.7 Fixpack 5. *
**************************************************************** |
| Local Fix: |
Use old style comments ( -- ) at the end of each line, which
keeps LF at the end. The format of old style comment is
-- <some text> \n |
| available fix packs: |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in v9.7 Fixpack 5. This fix should be
applied to the system where the DB2 CLP is being executed. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 30.05.2011
02.01.2012
02.01.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
