Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

Modifying SQL Stored Procedure

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Modifying SQL Stored Procedure

    There are various options available to modify stored procedures on the AS/400 depending on what OS release you are at. Whether you execute the CREATE PROCEDURE via STRSQL, RUNSQLSTM or "Run SQL Scripts" in Ops Navigator and the Client Access driver, the AS/400 creates two things, an ILE C program object and a corresponding row in QSYS2.SYSPROCS. This is where re-creation gets tricky. Unlike traditional "compiles" on the AS/400 where the compiled object simply gets "replaced" upon successful compilation, one must execute a DROP PROCEDURE lib/procname via one of the three methods above(STRSQL, RUNSQLSTM or "Run SQL Scripts" in Ops Navigator and the Client Access driver). I have encountered issues where the "C" program got deleted manually and the row must be CAREFULLY found and deleted from QSYS2.SYSPROCS before the system stops "thinking" that the procedure already exists and are allowed to re-create it. Via Ops Navigator and the and the Client Access driver, you are able to "view" and highlight then copy/paste the SQL source for the stored procedure for later modification or whatever by highlighting the stored procedure and doing a "right mouse", "properties". Also, there is a column in QSYS2.SYSPROCS () which contains the SQL procedure source up to 32K. There is no way to "retrieve" the source from the ILE C object per se (that I know of). The above is true for V5R1 and should be true for V4R5 as well. Stored Procedure support got much more robust in V5R1 particularly in the area of saving and restoring between systems. It was a little less trivial in V4R5. I hope this helps a little.

  • #2
    Modifying SQL Stored Procedure

    After creating a Stored Procedure... what objects would you promote to production when using Turnovers migration tool? Would you promote the C program and QSYS2.SYSPROCS?

    Comment


    • #3
      Modifying SQL Stored Procedure

      This message may not be the answer to the above question, but we avoided this particular problem like this... We write all the "CREATE PROCEDURE" statements inside a SQLRPGLE program and compile as SQLRPGLE program and call that once to create the actual stored procedure. If you want to change the stored procedure at a later time, we simply drop the procedure, edit the sqlrpgle program which has procedure definition, recompile and call to recreate.

      Comment


      • #4
        Modifying SQL Stored Procedure

        I need to modify a stored procedure that a previous co-worker developed, and I'm not sure how to do this. If you do a WRKOBJ on the AS400 for object GETAR, this is what you will see (see code example). I have created stored procedures on the AS400 before, by going into STRSQL, and using a CREATE PROCEDURE, but this seems a little different. When you do a CREATE PROCEDURE in STRSQL, the stored procedure doesn't show up when you do a WRKOBJ. The stored procedure GETAR looks like it was somehow bound into an ILE object. I think this was somehow done using Client Access, but I don't know how. If you go into Client Access, and look at databases in WORKLIB, you can see this object, and display properties to view the SQL statements, but you can't modify it. Does anybody have any ideas or suggestions on how to modify this?
        Code

        Comment


        • #5
          Modifying SQL Stored Procedure

          You might want to check whether a source program is existing for this stored procedure. I normally code stored procedures as a TXT member in a source physical file like QSQLSRC, then execute RUNSQLSTM to compile/create the procedure. GOOD LUCK!

          Comment

          Working...
          X