#344 JDBC DB2400 stored procedure issues

Core (461)

We're not doing a lot of SQL here for new database
stuff, but we are building a lot of stored procedures
for client queries and database updates from the web.
So my interest in this product was more specific to
stored procedures, however I'm having some
difficulties. I tried a JDBC connection to my 400
which works fine. The object list shows all my
procedures in my selected schema, when I click on a
procedure object the work panel shows an Info and
Column tabs, the Column tab however, doesn't show any
procedure parameter information. When I use an ODBC
connection instead, then I get all the parameter info
in the Column tab. I believe I'm using a current
version of the JT400.jar. I'm not an expert in any of
this stuff, but I believe I have everything configured
correctly. I was wondering if you've experienced this
limitation with the JDBC connection.
What are the advantages or disadvantages of
using a JDBC connection over the ODBC connection?

My second biggest problem is the SQL editor doesn't
like running our stored procedure scripts. These
scripts compile just fine using IBM's OPSNAV
(Operations Navigator) RUNSQLSTM processor in program
CWBUNDBS.EXE. Squirrel has difficulty parsing the
multiple SQL statements that are typical of a stored
procedure created via OPSNAV. Even if I try to
highlight and run the specific create procedure
statement portion, it fails on invalid
END_OF_STATEMENT errors . I've tried toggling on and
off the ; delimiter but still can't get the procedure
created through the Squirrel editor.
What are your experiences with this? Any workarounds
or special techniques I should be using? Also does
Squirrel provide a way to test stored procedures with
INOUT or OUT parameters? I don't have the client
language skills to develop something quick to test
these kinds of stored procedures before I hand them
off to our Java developers for use. Thanks for you
time. Attached is a sample of a typical DB2400 stored
procedure script file.


  • WBois

    WBois - 2006-02-21

    sample DB2400 stored procedure script

  • Gerd Wagner

    Gerd Wagner - 2006-02-26

    Logged In: YES

    None of us has access to DB 400 to reproduce your problem.
    But I would really be interested in it. So here is my
    proposal: If you could give me access to DB 400 via VPN
    1. I would take care of your problem(s)
    2. I would try to write a DB 400 plugin for SQuirreL that
    would allow you to script views and stored procedures from
    within SQuirreL's object tree. I know how to do that on DB2
    for Linux and hope it wouldn't be much harder on DB400.

    Im closing this bug. In case you could give me access to
    DB400 please feel free to reopen it and contact me at

  • Gerd Wagner

    Gerd Wagner - 2006-02-26
    • status: open --> closed
  • Rob Manning

    Rob Manning - 2006-02-26

    Logged In: YES

    While you are mulling Gerd's proposal, I noticed something
    in your attached stored proc script that you might like
    to fix. You see, SQuirreL interprets two consecutive eol
    chars as a statement separator as well as the semi-colon.
    In your script proc called "AMSGEN.SP_RTV_LOCATIONS_LIST"
    you seem to have two end-of-lines between your proc args and
    the BEGIN keyword. My guess is that if you are placing your
    cursor on the line of code that contains the "CREATE"
    keyword, then the following is all that is being sent to the
    server by SQuirreL:


    And since this isn't a valid procedure def, the server
    responds with invalid end-of-statement errors. Try removing
    the extra eol chars from your procedure def and see if that
    doesn't help.


  • WBois

    WBois - 2006-02-27

    Logged In: YES

    Thank you Gerd and Rob for your responses. Unfortunately,
    the option of a VPN connection to one of our ISeries for
    you to test out your plugins is not going to be possible.
    If it were up to me I would try to arrange something but, I
    already know the answer without even asking. Rob, per your
    suggestion, I will try to remove the double EOL characters
    from my script and try again to compile the stored
    procedure. I hope you can find someone that could allow you
    access to their DB2400 so that you could enhance the
    product. I know there is a community of ISeries/AS400
    programmers out there looking for a better SQL tool than
    the one that IBM provides with Operations Navigator. Again
    thanks for your time.

  • Daniel_Linyu

    Daniel_Linyu - 2006-03-28

    Logged In: YES

    The problem is the terminator-";" at the end of each
    statement in the procedure.

    Actually, the statement being sent to the server by
    SQuirreL is :


    ORDER BY Location_Code

    I found SQuirreL just using ";" as the sql statement
    separator, and I guess it will meets the same problem in
    oracle env(also contains ";" terminator in procedure), but
    in MS SQLserver env, it may work fine because it doesn't
    contains ";" in the procedure.


  • Daniel_Linyu

    Daniel_Linyu - 2006-03-29

    Logged In: YES

    I solved this problem by adding some codes in


  • WBois

    WBois - 2006-03-29

    Logged In: YES

    Hi Daniel, could you clarify your comment plaese? Are you
    saying you have modified Squirrel so that it will parse and
    compile my DB2 400 Stored Procedure scripts correctly? If
    so,what do I need to do to get this update? Thanks.

  • Daniel_Linyu

    Daniel_Linyu - 2006-05-17

    Logged In: YES


    Yes,I modified some Squirrel code to make your procedure
    scripts can be parsed and compiled.

    Because I am not a member of Squirrel-Developers, so I
    don't know how to add the code to Squirrel's offical
    release, but I can give you my code personally.

    You can request the code by sending a Email to me



Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

No, thanks