2

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
2

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> 
5
  • Well, curiously it has stripped out the blank line before the comment – Paul Vernon Jan 14 at 23:39
  • Well, that is interesting. I wonder if there is some type of configuration for CLPplus, since it is the same version as mine. – Jake v1 Jan 14 at 23:50
  • Are you using tabs or spaces as the prefix? I used spaces. – Paul Vernon Jan 15 at 0:17
  • Good call, I switched to spaces... Still stripped out :( – Jake v1 Jan 15 at 14:41
  • Humm. Not sure what else might be different about our environments. I’m using Linux but client OS should not matter. Raise with IBM support if you can – Paul Vernon Jan 15 at 16:40
1

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.

10
  • Point taken about Dbeaver. Correct, it does retain the formatting as you pointed out. I used Dbeaver view procedure option which messed up the formatting, so it is best to fetch the procedure back using SQL – Jake v1 Jan 13 at 18:25
  • I updated my question to focus on script with CLP. I would still like to deploy using a script. Also, would like to retrieve the procedure using a script for that matter. – Jake v1 Jan 13 at 18:55
  • The Dbeaver view procedure might be an issue in an old version. If not raise it with the Dbeaver developers. – Paul Vernon Jan 13 at 23:24
  • I compiled/deployed the test script using CLPplus now (and edited my question accordingly). Still same result. I am a developer, not a dba, and starting to wonder if there is something else at play. At this point I am open to any ideas on alternative free options to deploy a procedure without Data Studio, for from a script using Data Studio if that were possible? – Jake v1 Jan 14 at 13:30
  • I can't reproduce your issue myself using the db2 clp. What DB2 version are you using? Are you sure it is not something else that is stripping the leading spaces? – Paul Vernon Jan 14 at 13:51
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.