Creating DB2 SQL Procedures in DB2 version 6 on OS/390 (June 2000 PUT)

Creating DB2 SQL Procedures in DB2 version 6 on OS/390 (June 2000 PUT)

Post by Dan » Wed, 20 Mar 2002 06:26:36



According to the "IBM DB2 for OS/390 V6 Application Programming and
SQL Guide" (Second Edition, April 2000) page 573, SPUFI or DSNTEP2 can
be used to CREATE a SQL Procedure. Excerpts from page 573 is shown
below:

Define the stored procedure to DB2.

To do this,execute the CREATE PROCEDURE statement for the SQL
procedure.You can embed the CREATE PROCEDURE statement in an
application program or execute the statement dynamically,using an
application
such as SPUFI or DSNTEP2.Executing the CREATE PROCEDURE statement
puts the stored procedure definition in the DB2 catalog.

However, when I try to create the following DB2 SQL procedure:

CREATE PROCEDURE DBPDS01.ds00test (  )
    RESULT SETS 1
    LANGUAGE SQL
    MODIFIES SQL DATA
    COLLID DS91LGW
    NO WLM ENVIRONMENT
    ASUTIME NO LIMIT
    RUN OPTIONS 'TEST(ALL,*,,VADTCPIP&166.76.174.159:*)'
P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
        SELECT SCHEMA, NAME FROM SYSIBM.SYSROUTINES;
    -- Cursor left open for client application
    OPEN cursor1;
END P1                                  

I get the following SPUFI error messages:

CREATE PROCEDURE DBPDS01.DS00TEST (  )
    RESULT SETS 1
    LANGUAGE SQL
    MODIFIES SQL DATA
    COLLID DS91LGW
    NO WLM ENVIRONMENT
    ASUTIME NO LIMIT
    RUN OPTIONS 'TEST(ALL,*,,VADTCPIP&166.76.174.159:*)'
P1: BEGIN
    -- DECLARE CURSOR
    DECLARE CURSOR1 CURSOR WITH RETURN FOR
        SELECT SCHEMA, NAME FROM SYSIBM.SYSROUTINES;
---------+---------+---------+---------+---------+---------+---------+---
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "<END-OF-STATEMENT>".
SOM
         SYMBOLS THAT MIGHT BE LEGAL ARE: ;
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = 0  0  0  -1  833  0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'
X'FFFFFFFF'
         X'00000341'  X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0

If I remove everything after the RUN OPTIONS line, I get the following
error:

CREATE PROCEDURE DBPDS01.DS00TEST (  )
    RESULT SETS 1
    LANGUAGE SQL
    MODIFIES SQL DATA
    COLLID DS91LGW
    NO WLM ENVIRONMENT
    ASUTIME NO LIMIT
    RUN OPTIONS 'TEST(ALL,*,,VADTCPIP&166.76.174.159:*)'
---------+---------+---------+---------+---------+---------+---------+-----
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "<END-OF-STATEMENT>".
SOME
         SYMBOLS THAT MIGHT BE LEGAL ARE: <PROCEDURE-BODY>
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNHSMP2 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = 0  0  0  -1  1  0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'
X'FFFFFFFF'
         X'00000001'  X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-----
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0

This CREATE SQL PROCEDURE statment works correctly when processed
through the DB2 Stored Procedure Builder going through DB2-Connect to
the DB2 V6 subsystem on OS/390.

Any ideas on what I need to get the CREATE SQL PROCEDURE to run
through SPUFI?

 
 
 

Creating DB2 SQL Procedures in DB2 version 6 on OS/390 (June 2000 PUT)

Post by Serge Riela » Wed, 20 Mar 2002 07:55:33


It's very likley the ';' delimiter that is goofing things up.

In DB2 UWO's CLP you would use (e.g.)
db2 -td%
and then use a '%' at the end of the procedure.

Take a look if SPUFI has a setting for "default" or "statement" "delimiter"

Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada

 
 
 

Creating DB2 SQL Procedures in DB2 version 6 on OS/390 (June 2000 PUT)

Post by vaap » Thu, 21 Mar 2002 01:48:25


Hi Dan,

You might need to work closely with system people and DBA's to create an
environment for SP creation on mainframe using SQL language. This is a long
journey (atleast in our case) before we could set up things properly.

There is a very good Redbook "Cross-Platform DB2 Stored Procedures: Building
and Debugging" SG24-5485-01, which you can download from IBM Redbook site.
This will help you to go forward.

I am not sure if you could use SPUFI to create SP using SQL language.

There are three methods that you can use.

1. Using SPB (stored procedure builder) from NT workstation - for this your
DB2 DBA and system people will need to configure DSNTPSMP as this is the
pre-processor for SPB on mainframe side.
2. Use DSNTPSMP itself at mainframe without using SPB.
3. Use JCL - see Redbook for details.

Before you could even use DSNTPSMP, there is a requirement that your LE is
set up for C compile as SQL language SP first go through pre-processor and
it generates C code and your mainframe should be set for that work.

As I said before, it was a long journey for us before we could use it and if
your shop does not use WLM then your system people will need to configure
WLM and make it available for SP. I do not think that SPB or DSNTPSMP can
work without WLM setup on mainframe.

There are following steps in this process and unless someone has done this
exercise from end to end, there is no quick way.

1. Apply all PTF's as per Redbook.
2. Setup WLM and for WLM to work properly for SP, your system people need to
setup RRSAF properly.
3. Place all C run time libraries in WLM JCL concatenation for pre-processor
and C compiler to work.
4. First write a simple SP in C to make sure that WLM and RRSAF is setup
properly. For a simple C SP to work properly, you need to have proper JCL's
with proper parameters and unless you have someone who is really good in C
JCL, it may take time to come up with a working C stored procedure.
5. Your system people install DSNTPSMP and first test if DSNTPSMP works as t
his itself is a stored procedure on mainframe and its job is to pre-process
SPB input to something which then could be used by mainframe LE.
6. Configure DB2 connect for SPB to work. This itself is a topic if your
shop does not have this up and running. This will involve setting up TCPIP
on mainframe.
7. Test DB2 connect to make a connection from NT.
6. After all above, you can then test a SP from SPB.

Good luck.

Vikram


Quote:> According to the "IBM DB2 for OS/390 V6 Application Programming and
> SQL Guide" (Second Edition, April 2000) page 573, SPUFI or DSNTEP2 can
> be used to CREATE a SQL Procedure. Excerpts from page 573 is shown
> below:

> Define the stored procedure to DB2.

> To do this,execute the CREATE PROCEDURE statement for the SQL
> procedure.You can embed the CREATE PROCEDURE statement in an
> application program or execute the statement dynamically,using an
> application
> such as SPUFI or DSNTEP2.Executing the CREATE PROCEDURE statement
> puts the stored procedure definition in the DB2 catalog.

> However, when I try to create the following DB2 SQL procedure:

> CREATE PROCEDURE DBPDS01.ds00test (  )
>     RESULT SETS 1
>     LANGUAGE SQL
>     MODIFIES SQL DATA
>     COLLID DS91LGW
>     NO WLM ENVIRONMENT
>     ASUTIME NO LIMIT
>     RUN OPTIONS 'TEST(ALL,*,,VADTCPIP&166.76.174.159:*)'
> P1: BEGIN
>     -- Declare cursor
>     DECLARE cursor1 CURSOR WITH RETURN FOR
>         SELECT SCHEMA, NAME FROM SYSIBM.SYSROUTINES;
>     -- Cursor left open for client application
>     OPEN cursor1;
> END P1

> I get the following SPUFI error messages:

> CREATE PROCEDURE DBPDS01.DS00TEST (  )
>     RESULT SETS 1
>     LANGUAGE SQL
>     MODIFIES SQL DATA
>     COLLID DS91LGW
>     NO WLM ENVIRONMENT
>     ASUTIME NO LIMIT
>     RUN OPTIONS 'TEST(ALL,*,,VADTCPIP&166.76.174.159:*)'
> P1: BEGIN
>     -- DECLARE CURSOR
>     DECLARE CURSOR1 CURSOR WITH RETURN FOR
>         SELECT SCHEMA, NAME FROM SYSIBM.SYSROUTINES;
> ---------+---------+---------+---------+---------+---------+---------+---
> DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "<END-OF-STATEMENT>".
> SOM
>          SYMBOLS THAT MIGHT BE LEGAL ARE: ;
> DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE
> DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR
> DSNT416I SQLERRD    = 0  0  0  -1  833  0 SQL DIAGNOSTIC INFORMATION
> DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'
> X'FFFFFFFF'
>          X'00000341'  X'00000000' SQL DIAGNOSTIC INFORMATION
> ---------+---------+---------+---------+---------+---------+---------+---
> DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0

> If I remove everything after the RUN OPTIONS line, I get the following
> error:

> CREATE PROCEDURE DBPDS01.DS00TEST (  )
>     RESULT SETS 1
>     LANGUAGE SQL
>     MODIFIES SQL DATA
>     COLLID DS91LGW
>     NO WLM ENVIRONMENT
>     ASUTIME NO LIMIT
>     RUN OPTIONS 'TEST(ALL,*,,VADTCPIP&166.76.174.159:*)'
> ---------+---------+---------+---------+---------+---------+---------+----
-
> DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "<END-OF-STATEMENT>".
> SOME
>          SYMBOLS THAT MIGHT BE LEGAL ARE: <PROCEDURE-BODY>
> DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE
> DSNT415I SQLERRP    = DSNHSMP2 SQL PROCEDURE DETECTING ERROR
> DSNT416I SQLERRD    = 0  0  0  -1  1  0 SQL DIAGNOSTIC INFORMATION
> DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'
> X'FFFFFFFF'
>          X'00000001'  X'00000000' SQL DIAGNOSTIC INFORMATION
> ---------+---------+---------+---------+---------+---------+---------+----
-
> DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0

> This CREATE SQL PROCEDURE statment works correctly when processed
> through the DB2 Stored Procedure Builder going through DB2-Connect to
> the DB2 V6 subsystem on OS/390.

> Any ideas on what I need to get the CREATE SQL PROCEDURE to run
> through SPUFI?

 
 
 

Creating DB2 SQL Procedures in DB2 version 6 on OS/390 (June 2000 PUT)

Post by Dan » Fri, 22 Mar 2002 00:10:15



> It's very likley the ';' delimiter that is goofing things up.

> In DB2 UWO's CLP you would use (e.g.)
> db2 -td%
> and then use a '%' at the end of the procedure.

> Take a look if SPUFI has a setting for "default" or "statement" "delimiter"

> Cheers
> Serge

You are correct. Changing the SQL-Statement-Terminator from ';' to '~'
in the SPUFI defaults panel solved the CREATE PROCEDURE problem for
SQL procedures. I can now move a SQL procedure from one DB2 subsystem
that supports the stored procedure builder to another DB2 subsystem
that does not support the stored procedure builder by moving the
generated load module, DBRM and CREATE PROCEDURE (SQL).

Thanks for your help.