Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2012
    Posts
    16

    Unanswered: Stored Procedure Help

    Hello. I'm new to DB2.
    Im running DB2 v9.5.700.579 on AIX 6.1 SP4

    My application is Java based. I use a JDBC connection to connect to the database. I'm in the process of trying to replace some dynamic SQL in the Java application to calls to DB2 application objects. I have an application process that runs in "batch" and in online. Both have the same business requirements, but the batch version doesn't know "object ID" (it will iterate over all IDs to try to apply the business logic) , where as with the "online" version the user is already on the "objectID".

    I am trying to write 2 stored procedures. SPROC#1 will contain the business rule. I envision the "online" java code will call this directly. SPROC# 2 will be a "batch" wrapper to basically get all the accounts in a current state...and then execute SPROC#1 to filter with this logic...ultimately returning the entire result set back to the Java code for evaluation.

    Here are some simplified examples of what I am trying to do:

    SPROC#1

    Code:
    CREATE PROCEDURE SCHEMA.SPROC1
    (IN zipcode CHAR(9),
     IN businessName CHAR(80)) 
     LANGUAGE SQL
    DYNAMIC RESULT SETS 1
     
    
    P2: BEGIN   
          -- Declare cursor  
    	DECLARE cursor1 CURSOR WITH RETURN FOR
    	SELECT COUNT(tb.tableID)
    	FROM table tb  
    	WHERE tb.zip_cd = zipcode
    	AND tb.primary_business_name = primaryBusinessName;
    
    	-- Cursor left open for client application
        OPEN cursor1;
    
    END P2
    SPROC#2

    Code:
    CREATE PROCEDURE SCHEMA.SPROC2
    (IN yearParm INT) 
     LANGUAGE SQL
     DYNAMIC RESULT SETS 1
     
    P1: BEGIN
     -- Declare cursor  
    	DECLARE cursor1 CURSOR WITH RETURN FOR     
    SELECT tb.tableID 
    FROM table tb
    WHERE tb.year = yearParm
    AND CALL (SCHEMA.SPROC1(tb.zipcode, tb.business_name)) > 1; 
    
    -- Cursor left open for client application
        OPEN cursor1;
    
    END P1

    Syntactically, I know this is wrong:

    Code:
    AND CALL (SCHEMA.SPROC1(tb.zipcode, tb.business_name)) > 1;
    But...logically, this is what I am trying to do. I don't know the syntax to make it happen, I think I need to do some type of temp table in SPROC2 and then call SPROC1...but I can't seem to get it.

    I am really looking for examples (I have scoured the internet for about 2 weeks now trying to do this myself...DB2 seems to be an example wasteland) instead of the theory. I've read a lot of posts that end up saying you need to do this, and then not providing an example of how to do it.

    In addition, since I began trying this...I've built a "batch" SPROC, "online" SPROC, and a UDF containing the "business rules", and accessing it as I've done above...however, this only returns one row back to the Java code...when executing the SPROC using the Command Editor tool returns 19 rows.

    I could write 2 SPROCs with the business code...but I'm trying not to do this..as I want to have reusable code...so I don't have to make the changes twice.

    Thank you so much for taking the time to read my (long) post, and thanks in advance for any assistance you may be able to offer me.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think if you use UDFs instead of Stored Procedures, it will work easier. Remember, you can have a UDF that can return a row or table as well as a scalar.

    Andy

  3. #3
    Join Date
    Jan 2012
    Posts
    16
    Hi ARWinner,

    Thanks for the quick response.

    I can't call a UDF from Java code directly, right, only a SP?

    I tried doing this with a UDF. I basically converted my SPROC1 to a UDF, then created an "online" wrapper (SPROC#3). I created it as a scalar UDF because it only returns a count.

    Callling either procedure (SP2 or SP3) this from the Command Editor (CE) works fine...

    When I call this from Java code...my SP2 only returns one row in the result set. On the CE it returns 19 to the output window.

    Is there an issue using the scalar UDF when passing this back...even though its not directly called by Java?

    Thanks for any assistance you can provide.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You do not "call" a UDF, you invoke it just like a built in function.

    Can you post the java code? Calling the Stored procedure the same way each time, regardless of how it is called, should return the same result.

    Andy

  5. #5
    Join Date
    Jan 2012
    Posts
    16
    Quote Originally Posted by ARWinner View Post
    You do not "call" a UDF, you invoke it just like a built in function.

    Can you post the java code? Calling the Stored procedure the same way each time, regardless of how it is called, should return the same result.

    Andy
    I am under the impression you cannot "invoke" a UDF from Java application code?

    The only access to compiled database objects from Java application code is a SPROC?

    If so, can you provide an example of how this is done, or the Java statement to look up?

    My Java access to the current stored procedure (ive rewritten SP1 as UDF1, and SP2 invokes

    UDF1 on the DB...basically exactly how it was written above except..no "CALL" statement) is

    essentially like this:

    Code:
    CallableStatement cs = null;
    	ResultSet rs = null;
    	Integer year = new Integer(2008);
    		
    	try{
    	cs = conn.prepareCall("{ CALL SCHEMA.SPROC2(?)}";
                     cs.setInt(1, taxyear);	   
    	    rs = cs.executeQuery();
    
                     rs.next();
                     while (rs != null){
                       do some great stuff....
                      }
    Thanks again

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought VIEWs might be better than UDFs(Of cause, UDFs will better than Stored Procedures.)
    and it would be better to combine two such small procedures into one by using functionalities of SQL.

    Although you might say
    "SPROC#1 will contain the business rule. ... SPROC# 2 will be a "batch" wrapper to basically get all the accounts in a current state...",
    what other procedures would be expected to use the SPROC#1?

    If you create UDFs/Procedures for such small logic, you should create and maintain many objects.
    And developers(including you, in the future) need to look for objects which was implemented a logic within many objects.
    It would harm the productivities of the developers.
    And some(or many) experts might want to create an SQL statement easily and straightforwardly by themself rathe than to look for such objects.

    Example 1:
    Code:
    CREATE VIEW view2 AS
    SELECT tb.*
     FROM  table tb
     WHERE (SELECT COUNT(tx.table_id)
             FROM  table tx
             WHERE tx.zipcode       = tb.zipcode
               AND tx.business_name = tb.business_name
           ) > 1
    ;
    Example 1-e: use/invoke the view.
    Code:
    SELECT table_id FROM view2 WHERE year = ?;
    It is easy
    (a) to add some more conditions for the query
    (b) or to add some other columns for the result
    (c) or to use the view with different conditions
    ...
    without modifying the view.
    Last edited by tonkuma; 01-18-12 at 19:42.

  7. #7
    Join Date
    Jan 2012
    Posts
    16
    Hi tonkuma, thank you for your reply.

    I don't know of the performance difference between using UDF/SP/or a View, but essentially any will get me the same thing, the (original) difficulty I'm having is mainly with nesting of 2 Sprocs, and not knowing the syntax to pull this off with DB2.

    Right now...i have working (on the Command Editor) where I have 2 different SPs that call 1 UDF. These SPs are different, one gets all accounts (SP2...for my thread continuity) to then get zip and name associations, the other already has this information (SP3). The difficulty I'm having with this is that its only returning one account when I access the SP2 from Java.

    I don't forsee these to be used by any other entities...but I am trying to design it so that if the logic changes for the BR, i can change the lower item only...and it will apply it to both processes...rather than (have to remember) to change 1 proc for one process and another proc for another. Right now, I have to do this to the embedded SQL in the Java app, and the statements here are actually coded differently, although they get the same result. I really want to get away from that.

    I haven't coded it, but I should be able to just code 2 SPs with their own (copy and paste) code which will do my job, but I was just trying to reuse functionality and all.

    Thanks

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that most business logic could be put in one view, if it was directly related with data in tables.
    The needs of UDF/Procedure might be rare.
    Then put presentation logic/layer into queries using the views.

    By using views, you can add/combine more informations/logic by joining with other tables/views.
    Last edited by tonkuma; 01-18-12 at 21:44. Reason: Add ", if it was directly related with data in tables"

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by achdumeingute View Post
    I am under the impression you cannot "invoke" a UDF from Java application code?

    The only access to compiled database objects from Java application code is a SPROC?

    If so, can you provide an example of how this is done, or the Java statement to look up?

    My Java access to the current stored procedure (ive rewritten SP1 as UDF1, and SP2 invokes

    UDF1 on the DB...basically exactly how it was written above except..no "CALL" statement) is

    essentially like this:

    Code:
    CallableStatement cs = null;
    	ResultSet rs = null;
    	Integer year = new Integer(2008);
    		
    	try{
    	cs = conn.prepareCall("{ CALL SCHEMA.SPROC2(?)}";
                     cs.setInt(1, taxyear);	   
    	    rs = cs.executeQuery();
    
                     rs.next();
                     while (rs != null){
                       do some great stuff....
                      }
    Thanks again
    You can invoke UDF from java using JDBC. It is just another function and you invoke it just like any other. You use Statement and PreparedStatement.

    Stored Procedures are the only objects that are "compiled" (static) in a database, so yes the only access to compiled objects is call Stored Procedures. But all BI does not reside in Stored Procedures. Views and UDFS also are part of BI and those can be called with dynamic SQL.

    Your use of java code is incorrect, which is why you are only getting one row. IT should be like this:

    Code:
    CallableStatement cs = null;
    	ResultSet rs = null;
    	Integer year = new Integer(2008);
    		
    	try{
    	cs = conn.prepareCall("{ CALL SCHEMA.SPROC2(?)}";
                     cs.setInt(1, taxyear);	   
    	    rs = cs.executeQuery();
    
                     while (rs.next()) 
                     {
                       do some great stuff....
                      }
    Andy

  10. #10
    Join Date
    Jan 2012
    Posts
    16
    Quote Originally Posted by ARWinner View Post
    You can invoke UDF from java using JDBC. It is just another function and you invoke it just like any other. You use Statement and PreparedStatement.

    Stored Procedures are the only objects that are "compiled" (static) in a database, so yes the only access to compiled objects is call Stored Procedures. But all BI does not reside in Stored Procedures. Views and UDFS also are part of BI and those can be called with dynamic SQL.

    Your use of java code is incorrect, which is why you are only getting one row. IT should be like this:

    Code:
    CallableStatement cs = null;
    	ResultSet rs = null;
    	Integer year = new Integer(2008);
    		
    	try{
    	cs = conn.prepareCall("{ CALL SCHEMA.SPROC2(?)}";
                     cs.setInt(1, taxyear);	   
    	    rs = cs.executeQuery();
    
                     while (rs.next()) 
                     {
                       do some great stuff....
                      }
    Andy
    Thank you for the information, and for pointing out my Java error.

    What is meant by the abbreviation BI?

    So you are using a UDF (or a View) as essentially a dynamic SQL statement then? I'm under the impression that dynamic SQL is much slower, correct?

    The process I am working with can take up to 3 days to run right now...its currently all dynamic SQL. I'm trying to shorten that up considerably.

    Thank you for your continued replies.

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    BI = Business Intelligence (the code you stick in the database).

    Dynamic is not necessarily slower than static. It does have prep time, but if using prepared statements, this can almost be eliminated.

    Andy

  12. #12
    Join Date
    Jan 2012
    Posts
    16
    Quote Originally Posted by ARWinner View Post
    BI = Business Intelligence (the code you stick in the database).

    Dynamic is not necessarily slower than static. It does have prep time, but if using prepared statements, this can almost be eliminated.

    Andy
    Thats what I thought you meant by BI, but i just wanted to be sure...sometimes the acronyms mean something else.

    Do you possibly have a link to a discussion or "white paper" or something about this, so I can better understand how to optimize performance in this matter.

    And...my actual problem wasn't with "if (rs != null)". I've been getting basically an index out of bounds type error. I was thinking my ResultSet object was more like a DataTable/DataSet in .NET...but its not. With the .NET classes they are more like collections, one is able to see in Debug, and access multiple dataset values at a time.

    I was trying to access "the second index" of data...which doesn't exist on a ResultSet...as you iterate through with rs.next()....so "rs.getString(2)" would throw this exception. My lack of knowledge with Java .
    Last edited by achdumeingute; 01-19-12 at 13:15. Reason: edited for (what I think is) more clarity

  13. #13
    Join Date
    Jan 2012
    Posts
    16
    Quote Originally Posted by tonkuma View Post
    I think that most business logic could be put in one view, if it was directly related with data in tables.
    The needs of UDF/Procedure might be rare.
    Then put presentation logic/layer into queries using the views.

    By using views, you can add/combine more informations/logic by joining with other tables/views.
    Hi tonkuma,

    I'm still not understanding the benefit of using a view over other DB objects here.

    If I am trying to build all of the logic on the DB server (which is what I'm trying to do because others are telling me its better performance, which is somewhat contrary to what ARWinner is saying) I'm not sure how a view would be advantageous.

    If i were accessing this with dynamic SQL in code, then yes, I could modify my "Select" each time and add the view in as appropriate...one DB object.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    By using views, you can add/combine more informations/logic by joining with other tables/views.
    For example...
    Assumptions:
    Proc2/Udf2 returns table_id.
    View returns all columns.

    I'll show by psheudo code(not actual SQL code)
    to demonstrate the differences for additional requirements.

    (1) Add additional conditions.
    (1-1) Procedure:
    Code:
    Loop until not found;
       CALL proc2(year_arg);
       If <additional conditions> Then
          proceed by using table_id;
       Else
          ignore the table_id;
       End if;
    End loop;
    (1-2) UDF(table):
    Code:
    Declare cursor c1 for
    SELECT table_id
     FROM  TABLE( udf2(year_arg) ) AS tf
     WHERE <additional conditions>;
    
    Loop until not found;
       Fetch c1;
       proceed by using table_id;
    End loop;
    (1-3) VIEW:
    Code:
    Declare cursor c1 for
    SELECT table_id
     FROM  view2
     WHERE year = year_arg
       AND <additional conditions>;
    
    Loop until not found;
       Fetch c1;
       proceed by using table_id;
    End loop;

    (2) Return additional columns.
    (2-1) Procedure:
    Recreate the procedure to return additional columns.
    If you have included additional columns for the first time of creating the procedure,
    you should fetch all columns if some columns were not necessary.

    (2-2) UDF(table):
    Recreate the procedure to return additional columns,
    Or, included additional columns for the first time of creating the view,

    (2-3) VIEW:
    Code:
    Declare cursor c1 for
    SELECT table_id , <additional-column>
     FROM  view2
     WHERE year = year_arg;
    
    Loop until not found;
       Fetch c1;
       proceed by using table_id , <additional-column>;
    End loop;

    (3) Combine(join) with another table.
    (3-1) Procedure:
    Recreate the procedure
    Or
    Code:
    Declare dynamic cursor c2 for
    SELECT <column-y>
     FROM  <another table>
     WHERE <column-x> = ?;
    
    Loop1 until not found;
       CALL proc2(year_arg);
       Open c2 cursor by using table_id;
       Loop2 until not found c2;
          Fetch c2;
          proceed by using table_id and <column-y>;
       End loop2;
    End loop1;
    (3-2) UDF(table):
    Code:
    Declare cursor c1 for
    SELECT table_id , <column-y>
     FROM  TABLE( udf2(year_arg) ) AS tf
     JOIN  <another table>
       ON  <column-x> = table_id;
    
    Loop until not found;
       Fetch c1;
       proceed by using table_id and <column-y>;
    End loop;
    (3-3) VIEW:
    Code:
    Declare cursor c1 for
    SELECT table_id , <column-y>
     FROM  view2
     JOIN  <another table>
       ON  <column-x> = table_id
     WHERE year = year_arg;
    
    Loop until not found;
       Fetch c1;
       proceed by using table_id and <column-y>;
    End loop;
    Last edited by tonkuma; 01-19-12 at 17:07.

Posting Permissions

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