Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2002
    Posts
    11

    Red face Unanswered: DB2 SQL Stored Procedure creating problem - HELP!

    I`m creating stored proc but procedure doesn`t created ((
    CREATE PROCEDURE DB2ADMIN.Proc1 ( )
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    END P1
    =====================================
    console output when I try to buuild my proc
    ==============

    DB2ADMIN.PROC1 - Create stored procedure returns -7032.

    DB2ADMIN.PROC1:

    *** BIND
    C:\IBM\SQLLIB\function\routine\sqlproc\SB4A\DB2ADM IN\tmp\P4402430.sqc ***



    LINE MESSAGES FOR P4402430.sqc

    ------ --------------------------------------------------------------------

    SQL0060W The "C" precompiler is in progress.

    SQL0091W Precompilation or binding was ended with "0"

    errors and "0" warnings.



    *** COMPILE
    C:\IBM\SQLLIB\function\routine\sqlproc\SB4A\DB2ADM IN\tmp\P4402430.c ***





    @echo off

    REM set VCV6_DRIVE=c:\Microsoft Visual Studio

    REM set
    include=%include%;%VCV6_DRIVE%\VC98\atl\include;%V CV6_DRIVE%\VC98\mfc\includ
    e;%VCV6_DRIVE%\VC98\include

    REM set lib=%lib%;%VCV6_DRIVE%\VC98\mfc\lib;%VCV6_DRIVE%\V C98\lib

    REM set
    path=%path%;%VCV6_DRIVE%\Common\Tools\WinNT;%VCV6_ DRIVE%\Common\MSDev98\Bin;
    %VCV6_DRIVE%\Common\Tools;%VCV6_DRIVE%\VC98\bin;%V CV6_DRIVE%\VC98\mfc\lib;%V
    CV6_DRIVE%\VC98\lib



    @echo off

    REM set VCV5_DRIVE=c:\Program Files\devstudio

    REM set
    include=%include%;%VCV5_DRIVE%\vc\include;%VCV5_DR IVE%\vc\atl\include;%VCV5_
    DRIVE%\vc\mfc\include

    REM set lib=%lib%;%VCV5_DRIVE%\vc\lib;%VCV5_DRIVE%\vc\mfc\ lib

    REM set
    path=%path%;%VCV5_DRIVE%\sharedide\bin\ide;%VCV5_D RIVE%\sharedide\bin;%VCV5_
    DRIVE%\vc\bin





    @echo on

    set SQLROUTINE_FILENAME=P4402430

    set db2path=C:\IBM\SQLLIB



    nmake /f C:\IBM\SQLLIB\function\routine\sqlproc.mak



    *** C:\IBM\SQLLIB\function\routine\sqlproc\SB4A\DB2ADM IN\tmp\P4402430.def
    ***

    LIBRARY P4402430

    EXPORTS _pgsjmp@4

    pgsjmp=_pgsjmp@4





    C:\IBM\SQLLIB\function\routine\sqlproc\SB4A\DB2ADM IN\tmp>set
    SQLROUTINE_FILENAME=P4402430



    C:\IBM\SQLLIB\function\routine\sqlproc\SB4A\DB2ADM IN\tmp>set
    db2path=C:\IBM\SQLLIB



    C:\IBM\SQLLIB\function\routine\sqlproc\SB4A\DB2ADM IN\tmp>nmake /f
    C:\IBM\SQLLIB\function\routine\sqlproc.mak



    'nmake' is not recognized as an internal or external command,

    operable program or batch file.



    ----------------------------------------------------------------------------



    SQL7032N SQL procedure "PROC1" not created. Diagnostic file is

    "P4402430.log". SQLSTATE=42904





    DB2ADMIN.PROC1 - Build failed.

    DB2ADMIN.PROC1 - Changes rolled back.
    =====================================
    ADD INFO:
    I`m using IBM stored procedure builder - DB2 7.2
    and when i try to open >SQL Stored procedure build options< from menu
    this error occurs:
    [IBM][CLI Driver][DB2/NT] SQL1109N The specified DLL "db2psmdr" could not
    be loaded.

    SQLCODE:-1109
    .................................................. ....
    Ivan Danchev
    System Developer
    BIANOR
    5 Stratsin Str.
    1407 Sofia, Bulgaria
    Switchboard: (+359 2) 962 4524, (+359 2) 962 3937
    Fax: (+359 2) 962 4416
    mailto:ivan.danchev@bianor.com
    http://www.bianor.com
    .................................................. ....

  2. #2
    Join Date
    Mar 2002
    Posts
    17
    Hi
    Do you have application development client installed on your machine? Make sure you do. Also, check if you have Microsoft Visual C++ 5.0/6.0 or VisualAge C++ 4.2.5.0. I have it working on my NT database but I have problems on AIX.
    Good luck

  3. #3
    Join Date
    Apr 2002
    Posts
    11
    Originally posted by Musa
    Hi
    Do you have application development client installed on your machine? Make sure you do. Also, check if you have Microsoft Visual C++ 5.0/6.0 or VisualAge C++ 4.2.5.0. I have it working on my NT database but I have problems on AIX.
    Good luck
    Yes I have App. Dev client. I`ve just install MS Visual C++6.0 .... but things are still the same... ( .... any other ideas ?

  4. #4
    Join Date
    Mar 2002
    Posts
    17
    Oh! By the way, if you're using temporary tables you have to create a temporary table space for the temp tables.

  5. #5
    Join Date
    Mar 2002
    Posts
    34
    I have run into this exact same problem when I first started to use Db2.

    First make sure the DB server has the necessary compiler installed. Unlike Oracle and SQL Server, the compiler needed to build and run sql stored procs is not pakaged with the DB2 software.

    If you are a developer and are not running on the db server, you only need to install the developer client. As long as your DB server has the compiler installed you DO NOT need to install the C++ compiler on your client PC.

    Lastly, when you install the C++ compiler make sure you use the supported version. When you install the C++ compiler you need to make sure you copy the following environment variables
    Right click on My computer go to properties and then the advanced tab
    copy the following C++ user variables down to the associated system variables:
    INCLUDE
    LIB
    PATH

    Please reply if you still have some problems,

    Brian

  6. #6
    Join Date
    Mar 2002
    Posts
    34
    I forgot to include 2 other steps to get the compiler to work:

    1. you need to go to adminstrative tools->computer management->Local Users and Groups-> create a new user called "db2admin" give the user adminstrator rights.
    2. You need to go to ->Local Users and Groups-> Local Security Settings

    there are several policy's you need to add the db2admin to:
    -act as part of the operating system
    -increase quotas
    -replace a process level token
    -create a token object
    -log on locally (not sure if this one is required)

    Also once you have set up all this correctly. Just as an FYI, you can create a stored proc and then copy the DLL files to DB instance that doesn't have the compiler and with the DLL alone the stored proc will execute.

    Hope this helps,

    BK

  7. #7
    Join Date
    Apr 2002
    Posts
    11

    10x!!! and another question

    Hi!!! & 10x!!! ))
    I have OK installed DB2 ver . 7.2 Application Development Client ( with
    Storage Procedure Builder ) and Administration Client. DB2 7.2 Server is
    installed on a remote computer. Neither Server machine nor my machine have
    installation of MS VisualC++ or IBM VisualAgeC++ compiler. I'll try to install MS VisualC++ on DB2 server - am I understand you right ?

    And I have another problem. Once I could'n create SQL Stored PRoc. I try
    with Java Stored Proc. - and every sigle one looks (and works) ok. But now I try call one
    SQLJ proc form another and this doesn't works - any ideas ? - note that both are created as "Modifies SQL data" procedures!


    /**
    * JDBC Stored Procedure DB2ADMIN.Procedure2
    */
    import java.sql.*; // JDBC classes

    public class Procedure2
    {
    public static void procedure2 ( ResultSet[] rs ) throws SQLException,
    Exception
    {
    // Get connection to the database

    Connection con =
    DriverManager.getConnection("jdbc:default:connecti on");
    PreparedStatement stmt = null;
    String sql;

    sql = "call sb4a.prodecure1() ";
    stmt = con.prepareStatement( sql );
    rs[0] = stmt.executeQuery();
    if (con != null) con.close();
    }
    }

    /**
    * JDBC Stored Procedure DB2ADMIN.Procedure1
    */
    import java.sql.*; // JDBC classes

    public class Procedure1
    {
    public static void procedure1 ( ResultSet[] rs ) throws SQLException,
    Exception
    {
    // Get connection to the database
    Connection con =
    DriverManager.getConnection("jdbc:default:connecti on");
    PreparedStatement stmt = null;
    String sql;

    sql = "SELECT * FROM sb4a.outcome";
    stmt = con.prepareStatement( sql );
    rs[0] = stmt.executeQuery();
    if (con != null) con.close();
    }
    }
    .................................................. ....
    Ivan Danchev
    System Developer
    BIANOR
    5 Stratsin Str.
    1407 Sofia, Bulgaria
    Switchboard: (+359 2) 962 4524, (+359 2) 962 3937
    Fax: (+359 2) 962 4416
    mailto:ivan.danchev@bianor.com
    http://www.bianor.com
    .................................................. ....

  8. #8
    Join Date
    Mar 2002
    Posts
    34
    Yes, that's correct. The java compiler is packaged as part of DB2.

    So you can write stored procs in Java.

    You still need to install the C++ compiler if you want to write SQL stored procs.

    Please let me know if you have any more questions,

    BK

  9. #9
    Join Date
    Mar 2002
    Posts
    34
    When you get the C++ compiler attached is a sample SQL proc.

    A couple of FYI about DB2. You can't truncate table, there's no such command. They suggest for large volume deletes to use drop table or replace command. Second there is no alter table drop column command, you have to create a new table and select into and then drop the old one and rename the new one.

    Just a couple of fustrating things that isn't well documented in DB2.

    BK
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2002
    Posts
    11

    10x a lot!!! - but SQLJ help needed...

    04-08-2002 03:29 PM

    --------------------------------------------------------------------------------

    10x!!!
    Now things look much more clear...!!!

    Anyway, I start this project with SQLJ Stored procedures (its easiset way for me, as I work with JAVA mostly...)and another question ...


    Another problem. Once I could'n create SQL Stored PRoc. I try
    with Java Stored Proc. - and every sigle one looks (and works) ok. But now I try CALL one
    SQLJ proc form another and this doesn't works - any ideas ? - note that both are created as "Modifies SQL data" procedures!

    ERROR WHEN I RUN sb4a.Procedure2)
    ==================
    SB4A.PROCEDURE2 - Calling stored procedure.

    [IBM][CLI Driver][DB2/NT] SQL0751N User defined function or procedure "" (specific name "") attempted to execute a statement that is not allowed.



    SB4A.PROCEDURE2 - Changes rolled back.

    SB4A..PROCEDURE2 - Done calling stored procedure.
    /**
    * JDBC Stored Procedure SB4A..Procedure2
    */
    import java.sql.*; // JDBC classes

    public class Procedure2
    {
    public static void procedure2 ( ResultSet[] rs ) throws SQLException,
    Exception
    {
    // Get connection to the database

    Connection con =
    DriverManager.getConnection("jdbc:default:connecti on");
    PreparedStatement stmt = null;
    String sql;

    sql = "call sb4a.prodecure1() ";
    stmt = con.prepareStatement( sql );
    rs[0] = stmt.executeQuery();
    if (con != null) con.close();
    }
    }

    /**
    * JDBC Stored Procedure SB4A..Procedure1
    */
    import java.sql.*; // JDBC classes

    public class Procedure1
    {
    public static void procedure1 ( ResultSet[] rs ) throws SQLException,
    Exception
    {
    // Get connection to the database
    Connection con =
    DriverManager.getConnection("jdbc:default:connecti on");
    PreparedStatement stmt = null;
    String sql;

    sql = "SELECT * FROM sb4a.outcome";
    stmt = con.prepareStatement( sql );
    rs[0] = stmt.executeQuery();
    if (con != null) con.close();
    }
    }
    .................................................. ....
    Ivan Danchev
    System Developer
    BIANOR
    5 Stratsin Str.
    1407 Sofia, Bulgaria
    Switchboard: (+359 2) 962 4524, (+359 2) 962 3937
    Fax: (+359 2) 962 4416
    mailto:ivan.danchev@bianor.com
    http://www.bianor.com
    .................................................. ....

  11. #11
    Join Date
    Mar 2002
    Posts
    34
    sorry, I don't know java. So I never used the sqlj stored procs at all.

    If you wouldn't mind sending me an example of a java stored proc, I would greatly appreciate it.

    Good Luck,
    BK

  12. #12
    Join Date
    Apr 2002
    Posts
    11

    Smile SQLJ

    )) I've send examples with my previous post!. These are 2 very simple procedures - Procedure1 just make a simple `select * ....` statement , and Procedure2 tries to call Procedure1. Procedure1 works fine alone, but procedure2 doesnt work. note that proc 2 just execute ` call procedure1` statement.

    After all I'll attach example from my current project - this procedure searches in table for certain record and returns this single record as result. If no record found and autoCreate is set to autoCreateON - procedure creates new record and returns newly created record as result set.

    feel free to ask me anything for Java ... and other things. if I can I`ll try to anser you...


  13. #13
    Join Date
    Apr 2002
    Posts
    11

    Cool Re: SQLJ sorry forgot the attachemnt....



    NB! normal file ext. is .java ... but dbforums.com doesen`t allow .java , so cover it with .txt!
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •