Retain indent formatting when compiling a stored procedure without Data Studio

Question

When I compile a stored procedure using CLPplus console (or even in dBeaver), all indentation is stripped out. Only when I compile in Data Studio is the formatting retained, but I would like to deploy projects with a script.

I expect there is something simple I am missing, but have not been able to find a solution or even any other posts with the same issue.

Using CLPplus I ran this script file @c:\temp\proc1.spsql

create or replace procedure proc1()
begin
    declare vId int default 0;
    call dbms_output.put_line(vId+1);

/*
    Testing
*/
end

The SELECT TEXT FROM SYSCAT.PROCEDURES WHERE PROCNAME = 'PROC1'; produced the the following:

create or replace procedure proc1()
begin
declare vId int default 0;
call dbms_output.put_line(vId+1);

/*
Testing
*/
end

Answer 1

It works fine for me in clpplus 1.6

$ clpplus -h | head -1
CLPPlus: Version 1.6
$ cat sql
create or replace procedure proc1()
begin
    declare vId int default 0;
    call dbms_output.put_line(vId+1);

/*
    Testing
*/
end
/
SELECT TEXT FROM SYSCAT.PROCEDURES WHERE PROCNAME = 'PROC1'
/
$ clpplus -nw $user/"$password"@$host:$port/$database @"$PWD"/sql
CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION.  All rights reserved.


Database Connection Information :
---------------------------------
Hostname = localhost 
Database server = DB2/LINUXX8664  SQL11054 
SQL authorization ID = paul 
Local database alias = BP 
Port = 50003 


DB250000I: The command completed successfully.

create or replace procedure proc1()
begin
    declare vId int default 0;
    call dbms_output.put_line(vId+1);
/*
    Testing
*/
end                                                                                
SQL> 

Answer 2

I'm not aware that either the CLP or Dbeaver strip formatting from Stored Procs (views in the CLP are a different matter unfortunately ).

For example, this is from Dbeaver

CREATE PROCEDURE P()
BEGIN
    RETURN 1;
END

then

SELECT TEXT FROM SYSCAT.PROCEDURES WHERE PROCNAME = 'P'

returns

CREATE PROCEDURE P()
BEGIN
    RETURN 1;
END

and from the command line, running Db2 11.5.4 , I can't reproduce your issue

$ db2 -td@ -vf test.sql 
create or replace procedure proc1()
begin
    declare vId int default 0;
    call dbms_output.put_line(vId+1);

/*
    Testing
*/
end

DB20000I  The SQL command completed successfully.

$ db2 -x "SELECT TEXT FROM SYSCAT.PROCEDURES WHERE PROCNAME = 'PROC1'" | head --bytes 200
create or replace procedure proc1()
begin
    declare vId int default 0;
    call dbms_output.put_line(vId+1);

/*
    Testing
*/
end

If you don’t like what the CLP does to view DDL, but still want to create them from a command line tool, then you could use CLPPlus as that does not strip formatting I believe.

Answer 3

Use the SHOW command to check to make sure CLPPlus has the preservewhitespace option and make sure it is ON.

If the option is not there you may have on old clpplus.jar file.

My problem was that the clpplus.jar file had date stamp 2011-‎04-‎01 ‏‎6:54 AM even though it shows CLPPlus: Version 1.6 when it launches.