IBM Support

IC76686: ALTER PROCEDURE statement doesn't keep LF at the end of each line as CREATE PROCEDURE statement when sent to server via CLP.

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

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

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
    

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

Problem conclusion

  • Problem was first fixed in v9.7 Fixpack 5.  This fix should be
    applied to the system where the DB2 CLP is being executed.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC76686

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-05-30

  • Closed date

    2012-01-02

  • Last modified date

    2012-01-02

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R910 PSN

       UP

  • R950 PSN

       UP

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC76686

Modified date: 02 January 2012