 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / March 2007Newbie - Function calls a stored procedure with results (integer)
Working on a function that calls a stored procedure. iResult = Call GetEmployee (5, indate) The stored procedure, GetEmployee has 3 parameters. The first two parameters are an integer and a date (IN parameters). The third and final parameter is an integer (OUT). I would simply like to call the stored procedure and use the returned integer in my function. Also tried Call GetEmployee (5, indate, iResult) Error: SQL075iN Routine "GetEmployee" ... attempted to execute a statement that is not allowed. > Working on a function that calls a stored procedure. > [quoted text clipped - 3 lines] > parameters are an integer and a date (IN parameters). The third and > final parameter is an integer (OUT). You only provide two parameters above. So DB2 won't use the correct procedure or complain with an error stating that no matching procedure was found. Maybe you want to do this: CALL getEmployee(5, indate, iResult); > I would simply like to call the stored procedure and use the returned > integer in my function. Do you want to return the integer as result of the stored procedure (set by the RETURN statement) or do you want to pass it back via an OUT parameter? > Also tried Call GetEmployee (5, indate, iResult) > > Error: SQL075iN Routine "GetEmployee" ... attempted to execute a > statement that is not allowed. Have a look at the explanation for this error message: ---------------------------- $ db2 "? sql751" SQL0751N Routine "<routine-name>" (specific name "<specific-name>") attempted to execute a statement that is not allowed. Explanation: The program used to implement the body of a routine is not allowed to issue any connection statements. If the routine is a function or method, COMMIT and ROLLBACK (without the SAVEPOINT option) are also not allowed. If the routine is a procedure and is called within a trigger, function, method, or dynamic compound statement, a COMMIT or ROLLBACK statement is not allowed in the procedure. ---------------------------- So what's the code of your procedure? I think that needs fixing according to the above explanation.  SignatureKnut Stolze DB2 z/OS Admin Enablement IBM Germany Knut, > Do you want to return the integer as result of the stored procedure (set by > the RETURN statement) or do you want to pass it back via an OUT parameter? Either method would be great. Would slightly prefer a return statement. The procedure does call a commit. I can remove that from the code and try again. Thanks for pointing that out. > > Working on a function that calls a stored procedure. > [quoted text clipped - 50 lines] > DB2 z/OS Admin Enablement > IBM Germany > Knut, > [quoted text clipped - 4 lines] > Either method would be great. Would slightly prefer a return > statement. In that case, treat the OUT parameter simply like any other variable in the procedure, i.e. set it to an appropriate value before you exit the procedure. > The procedure does call a commit. I can remove that from the code and > try again. Thanks for pointing that out. You can have transactional logic (COMMIT/ROLLBACK) inside a stored procedure. However, you may not be able to call the procedure in certain contexts because the COMMIT/ROLLBACK impacts the client's transaction. For example, consider this: INSERT INTO tableA ... CALL proc() ROLLBACK If PROC issues a COMMIT statement, DB2 will also commit the INSERT operation. Therefore, the idea is usually to let the client manage transactions themselves and to _not_ use COMMIT/ROLLBACK in an SP. As usual, there are exceptions. A COMMIT/ROLLBACK implies another network roundtrip. And if that is a performance-critical point, it may be better sometimes to perform the EOT inside the SP. SignatureKnut Stolze DB2 z/OS Admin Enablement IBM Germany There are 2 things I am unsure of: 1) The UDF returns a table. So, within the UDF, I have a sql statement: select column_a from table_a a where a.result_id = result_org_id ; The result_org_id is a declared variable. How is the syntax handled. 2) When executing the function, it results in an error: SQL0746N routine ... violated nested sql statement rules when attempting to perform operation "MODIFY" on table ... SQLState=57053. It is unusual because there should not be a conflict. Both write to an application log table. According to help docs, it indicates that the table is already being read from or written by the application. Why does DB2 not create a lock waiting situation? > > Knut, > [quoted text clipped - 34 lines] > DB2 z/OS Admin Enablement > IBM Germany > There are 2 things I am unsure of: > > 1) The UDF returns a table. So, within the UDF, I have a sql > statement: > select column_a from table_a a where a.result_id = result_org_id ; > The result_org_id is a declared variable. How is the syntax handled. You're mixing things here again. UDFs do not have OUT parameters - only stored procedures do. So again: you should explain what you want to do. I assume you are talking about stored procedures now. Given that we have no idea how you implemented your stored procedure (which programming language), it's hard to be more specific. Basically, you just set the OUT parameters like any other variable. With a LANGUAGE SQL procedure, it would be something like this: CREATE PROCEDURE p(OUT x INT) LANGUAGE SQL CONTAINS SQL BEGIN SET x = 5; END@ The return statement would be this: CREATE PROCEDURE r() LANGUAGE SQL CONTAINS SQL BEGIN RETURN 12; END@ As for the execution of dynamic SQL, here is a short example: CREATE PROCEDURE d(OUT x INT) LANGUAGE SQL READS SQL DATA BEGIN DECLARE stmt VARCHAR(100); SET stmt = 'SELECT COUNT(*) FROM syscat.tables'; PREPARE s FROM stmt; BEGIN DECLARE c CURSOR FOR s; OPEN c; FETCH c INTO x; CLOSE c; END; END@ CALL p(?) Value of output parameters -------------------------- Parameter Name : X Parameter Value : 253 Return Status = 0 > 2) When executing the function, it results in an error: SQL0746N > routine ... violated nested sql statement rules when attempting to [quoted text clipped - 3 lines] > already being read from or written by the application. Why does DB2 > not create a lock waiting situation? As I mentioned in the other thread: apparently you call the procedure or function (or whatever) in a context where data modifications are simply forbidden. Therefore, you have to adjust your procedure that it is (a) declared as CONTAINS SQL or READS SQL DATA (or NO SQL for external routines), and (b) does not contain data modification statements. SignatureKnut Stolze DB2 z/OS Admin Enablement IBM Germany >> ... UDFs do not have OUT parameters - only stored procedures do. ... I am not suggesting the UDF has an out parameter. It returns a table. In the event a UDF calls a stored procedure, I would like to use the returned value from the SP within the select clause that builds the table returned from the UDF. The programming language for the SP is SQL. The SP procedure works fine. Returns the correct integer. The issue comes with working with that integer. For example: Create Function XYZ Returns table (N int) Call proc1 (in_int, in_date, out_int) Return select col1 from table1 where value1 = outint > > There are 2 things I am unsure of: > [quoted text clipped - 72 lines] > DB2 z/OS Admin Enablement > IBM Germany >>> ... UDFs do not have OUT parameters - only stored procedures do. ... > [quoted text clipped - 18 lines] > Return > select col1 from table1 where value1 = outint Here is a working example: CREATE PROCEDURE p(OUT x INT) READS SQL DATA BEGIN SET x = 123; END@ CREATE FUNCTION f() RETURNS TABLE ( name VARCHAR(128) ) READS SQL DATA BEGIN ATOMIC DECLARE rows INT; CALL p(rows); RETURN SELECT tabname FROM syscat.tables WHERE card > rows; END@ SELECT * FROM TABLE ( f() ) AS t@ NAME -------------------------------------------------------------------------------------------------------------------------------- 0 record(s) selected. RUNSTATS ON TABLE sysibm.systables@ RUNSTATS ON TABLE sysibm.syscolumns@ RUNSTATS ON TABLE sysibm.sysviews@ RUNSTATS ON TABLE sysibm.sysdatatypes@ SELECT * FROM TABLE ( f() ) as t@ NAME -------------------------------------------------------------------------------------------------------------------------------- SYSTABLES SYSCOLUMNS SYSVIEWS 3 record(s) selected. As you can see, the table function calls the procedure to determine a value that is later used in the SELECT statement that produces the result set of the table function. The RUNSTATS are necessary in my simple scenario to correctly populate the CARD values in SYSCAT.TABLES. I'm also looking at the UDF you posted the other day. Since you don't have any dynamic portions in there (table names are constant), you could use a regular SQL statement. Things would only be different if the user of your table function can supply an arbitrary condition, which includes subselects. In that case, you would need dynamic SQL. So what's the situation? SignatureKnut Stolze DB2 z/OS Utilities Development IBM Germany Knut, Thanks for the examples. They would work well in a text book for pre- med students. The issue comes about in that the SP is set as "modifies sql data". As it builds a wicked insert statement that writes data to the db. And since the stored procedure is "modifies sql data" - the function fails and also wants to be set to "modifies sql data". Kind of a catch 22 situation. Maybe DB2 (ibm) should consider removing these limitations. > Things would only be different if the user of your table function can supply an arbitrary condition, which includes > subselects. In that case, you would need dynamic SQL. So what's the situation? The user of the table function uses it as this... select * from table (getemployee(3, date('10/02/2006')) ) AS t; So - no the user of the function will call the function as a table passing it an integer and a date. There are no arbitrary conditions or subselects. > >>> ... UDFs do not have OUT parameters - only stored procedures do. ... > [quoted text clipped - 77 lines] > > - Show quoted text - > Knut, > [quoted text clipped - 7 lines] > catch 22 situation. Maybe DB2 (ibm) should consider removing these > limitations. Not to speak for Knut, but he has been very, very helpful and patient. I doubt he appreciates condescending remarks like these. Perhaps you should consider fixing your application instead of complaining about a design decision that was made for good reason. It's not as though you haven't been presented with options that will support your current design. > And since the stored procedure is "modifies sql data" - the function > fails and also wants to be set to "modifies sql data". Naturally... > Kind of a > catch 22 situation. Maybe DB2 (ibm) should consider removing these > limitations. They are there for a very good reason. A READS SQL DATA function can be used in a SELECT statement, whereas a MODIFIES SQL DATA (usually) can't. It just has side effects, which are generally frowned upon - not only in SQL. >> Things would only be different if the user of your table function can >> supply an arbitrary condition, which includes [quoted text clipped - 6 lines] > passing it an integer and a date. There are no arbitrary conditions > or subselects. That would bring us back to the semantics of your table function (which we don't know) if you would want to do things w/o dynamic SQL. Your code doesn't have clear semantics because of the problem I pointed out. Hence, you should provide a description (which you need for documentation purposes anyway). SignatureKnut Stolze DB2 z/OS Utilities Development IBM Germany > Knut, > [quoted text clipped - 16 lines] > passing it an integer and a date. There are no arbitrary conditions > or subselects. If I wrote the things beside the point, please allow me. It may be came from my poor English capability. Although, Following example produces no meaningful results. You can see how to call MODIFIES SQL DATA Procedure from MODIFIES SQL DATA Table Function. Example 1) ------------------------------------------------------------ CREATE TABLE Justin_log (in_Int INTEGER NOT NULL ,in_Date DATE NOT NULL ,out_Int INTEGER NOT NULL ,Isrt_TS TIMESTAMP DEFAULT CURRENT TIMESTAMP ,q_user VARCHAR(128) DEFAULT CURRENT USER ); CREATE PROCEDURE GetEmployee (IN in_int INTEGER, IN in_date DATE, OUT out_int INTEGER) LANGUAGE SQL MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION BEGIN ATOMIC SELECT COUNT(*) INTO out_int FROM Employee WHERE hiredate <= in_date; INSERT INTO Justin_log (in_int, in_date, out_int) VALUES (in_int, in_date, out_int); RETURN 0; END@ CREATE FUNCTION GetEmployee (in_int INTEGER, in_date VARCHAR(10)) RETURNS TABLE (N int) LANGUAGE SQL MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION BEGIN ATOMIC DECLARE out_int INTEGER; CALL GetEmployee (in_int, DATE(in_date), out_int); RETURN SELECT INT(empno) FROM Employee WHERE edlevel = out_int; END@ But, if objective to call procedure is to use INSERT statement in that, you can INSERT in the Table Function. Like following Example 2). Note: If you want to do everything in Table Function, you should take care of the fact that statements which can be used in Functions are limited. For example "SELECT INTO" can't be used in Functions. So, I used "SET out_int = (scalar subselect)" in the following example. Example 2) CREATE FUNCTION GetEmployee_log (in_int INTEGER, in_date VARCHAR(10)) RETURNS TABLE (N int) LANGUAGE SQL MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION BEGIN ATOMIC DECLARE out_int INTEGER; SET out_int = (SELECT COUNT(*) FROM Employee WHERE hiredate <= DATE(in_date) ); INSERT INTO Justin_log (in_int, in_date, out_int) VALUES (in_int, in_date, out_int); RETURN SELECT INT(empno) FROM Employee WHERE edlevel = out_int; END@ Here are sample results of execution. ------------------------- Commands Entered ------------------------- SELECT * FROM TABLE( GetEmployee(1, '1970-12-31') ) AS t@ -------------------------------------------------------------------- N ----------- 290 310 2 record(s) selected. ------------------------- Commands Entered ------------------------- SELECT * FROM TABLE( GetEmployee_log(101, '1970-12-31') ) AS t@ -------------------------------------------------------------------- N ----------- 290 310 2 record(s) selected. ------------------------- Commands Entered ------------------------- SELECT * FROM Justin_log@ -------------------------------------------------------------------- IN_INT IN_DATE OUT_INT ISRT_TS Q_USER ----------- ---------- ----------- -------------------------- -------- 1 1970-12-31 12 2007-03-23-23.09.14.560000 DB2ADMIN 101 1970-12-31 12 2007-03-23-23.18.26.463000 DB2ADMIN 2 record(s) selected. Thank you Tonkuma for your example. The one difference that I have with my SP is that I build the insert statement in a string and then call the string with prepare statement from query and execute query. When call the UDF => select * from table (getemployee(3, '10/02/2006') ) AS t; It returns a failure: SQL0746N Routine "USERID.INS_GETEmployee" (specific name "INS_GETEmployee") violated nested SQL statement rules when attempting to perform operation "MODIFY" on table "USERID.RESULT_SETS". SQLSTATE=57053 I am assuming it does not like the prepare and execute... > > Knut, > [quoted text clipped - 131 lines] > > - Show quoted text - >> It returns a failure: SQL0746N Routine "USERID.INS_GETEmployee" (specific name "INS_GETEmployee") violated nested SQL statement rules when attempting to perform operation "MODIFY" on table "USERID.RESULT_SETS". SQLSTATE=57053 << Then, you had better to show the source code of "USERID.INS_GETEmployee" and UDF GetEmployee. Without them, no one couldn't help you. Because, from description of SQL0746N in Manual, this message suggests the error is closely dependent on application code itself. I suspect conflict of "table access context" that is written in the manual "Application Development Guide: Programming Server Applications" Chapter 2. Developing routines ---> Data conflicts when procedures read from or write to tables Attached is the code as requested... To give you a summary of what the code does... GetEmployee is a function that calls INS_GetEmployee. For the most part, that is all that GETEmployee does and returns data based on a result_id from INS_GETEmployee. INS_GETEmployee executes a complex query - inserts data into a "result" table. The result table id is passed back to the function. SET SCHEMA DB2USR; SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","DB2USR"; CREATE FUNCTION DB2USR.GETEMPLOYEE (IN_INT INTEGER, IN_DATE VARCHAR(10) ) RETURNS TABLE (EMPID INTEGER ) SPECIFIC DB2USR.GETEMPLOYEE LANGUAGE SQL DETERMINISTIC MODIFIES SQL DATA STATIC DISPATCH CALLED ON NULL INPUT NO EXTERNAL ACTION INHERIT SPECIAL REGISTERS BEGIN ATOMIC DECLARE out_int INTEGER; CALL GetEmployee (in_int, DATE(in_date), out_int); RETURN SELECT value1 FROM result_sets WHERE result_id = out_int; END; --------------------------------------------------------------- SET SCHEMA ; SET CURRENT PATH = ; CREATE PROCEDURE DB2USR.INS_GETEMPLOYEE (IN P_EMPID INTEGER, IN P_PERIOD DATE, OUT P_RESULT_ID INTEGER ) SPECIFIC DB2USR.INS_GETEMPLOYEE LANGUAGE SQL DETERMINISTIC CALLED ON NULL INPUT MODIFIES SQL DATA INHERIT SPECIAL REGISTERS -- begin BEGIN ATOMIC --DECLARE sqlstate CHAR(5) DEFAULT '00000';-- --DECLARE sqlcode int default 0; -- -- DECLARE SQL variables so database will fill them. -- DECLARE SQLCODE INTEGER DEFAULT 0; -- -- DECLARE SQLSTATE CHAR(0005) DEFAULT '00000'; -- DECLARE iGroup integer default -1; DECLARE db_empid integer; DECLARE db_sysemp varCHAR(1); DECLARE db_edlevel integer; DECLARE db_conjunction integer; DECLARE db_test_type integer; DECLARE db_grouping_id integer;-- DECLARE db_value integer; -- DECLARE i integer default 1;-- DECLARE squery VARCHAR(3800);-- DECLARE field varCHAR(9);-- DECLARE table varchar(16); -- DECLARE extable varchar(22);-- DECLARE notsql varchar(3);-- DECLARE compval varchar(4);-- DECLARE conj varchar(4) default ''; DECLARE groupflag integer default 0;-- DECLARE itemp integer; DECLARE pperiod date;-- DECLARE result_id integer;-- DECLARE stmt1 STATEMENT;-- FOR_LOOP: FOR EACH_ROW AS select a.empid, a.sysemp, b.edlevel, b.conjunction, b.test_type, b.grouping_id, b.value from db2usr.empdefn a left join db2usr.empproperties_defn b on a.empid = b.empid where a.empid = P_EMPID order by a.empid, b.prop_order DO SET db_empid = EACH_ROW.empid; SET db_sysemp = EACH_ROW.sysemp;-- SET db_edlevel = EACH_ROW.edlevel;-- SET db_conjunction = EACH_ROW.conjunction;-- SET db_test_type = EACH_ROW.test_type;-- SET db_grouping_id = EACH_ROW.grouping_id;-- SET db_value = EACH_ROW.value;-- SET pperiod = P_PERIOD; if db_sysemp = 'Y' then -- check for system set field = 'system_id';-- set table = 'system_demo_data';-- set extable = 'empexclude_system_defn';-- else -- site_demo set field = 'css_id';-- set table = 'site_demo_data' ;-- set extable = 'empexclude_defn';-- end if; -- -- Check test_type if db_test_type = 6 then set notsql = 'NOT';-- else set notsql = '';-- set notsql = left (notsql, 1);-- end if; -- if db_test_type = 1 then set compval = '>'; -- elseif db_test_type = 2 then set compval = '<';-- elseif db_test_type = 3 then set compval = '>=';-- elseif db_test_type = 4 then set compval = '<=';-- elseif db_test_type = 5 then set compval = 'like'; -- else set compval = '=';-- end if; -- if i = 1 then -- first record set iGroup = db_grouping_id;-- set squery = rtrim(char(field)) || ' from db2usr.'|| rtrim(char(table)) || ' where ' || rtrim(char(field)) || ' ' || rtrim(char(notsql)) || ' in (select ' || rtrim(char(field)) || ' from db2usr.' || rtrim(char(table)) || ' where ( '; -- else -- not the first record -- set query = query || ') ' if iGroup <> db_grouping_id then -- new group set squery = rtrim(squery) || ') '; -- end if; -- set squery = rtrim(squery) || rtrim(char(conj));-- set squery = rtrim(squery) || ' ' || rtrim(char(field)) || ' ' || rtrim(char(notsql)) || ' in (select ' || rtrim(char(field)) || ' from db2usr.' || rtrim(char(table)) || ' where '; -- if iGroup <> db_grouping_id then -- new group set squery = rtrim(squery) || '( ';-- set groupflag = 1; -- end if; -- set iGroup = db_grouping_id; -- end if; -- --Insert into applog (Description) values ('** Mid Query ** = '|| rtrim(char(query)) ); -- set squery = rtrim(squery) || ' (demo_id = ' || rtrim(char(db_edlevel)) || ' AND demo_value ' || rtrim(char(compval)) || ' ' || rtrim(char(db_value)) || '))';-- -- Insert into applog (Description) values ('** Length ** = '|| length(rtrim(char(query))) );-- set i = i + 1;-- set itemp = length(squery); -- --Insert into applog (Description) values ('** length ** = '|| char(itemp) ); -- -- Check conjuction if db_conjunction = 1 then set conj = ' AND';-- elseif db_conjunction = 2 then set conj = ' OR';-- else set conj = '';-- end if;-- --Insert into applog (Description) values ('** Query ** = '|| rtrim(char(squery)) ); -- END FOR; -- set squery = squery || ') ';-- -- if groupflag = 1 then -- set squery = rtrim(squery) || ') ';-- -- end if; -- -- Excludes if length(squery) > 0 then -- query exists set squery = rtrim(squery) || ' AND ' || rtrim(char(field)) || ' not in (select ' || rtrim(char(field)) || ' from db2usr.' || rtrim(char(extable)) || ' where ( period = ''' || char(pperiod) || ''' or all_period_flag = ''Y'' ) and (empid = ' || char(P_EMPID) || ' or all_empflag = ''Y'' ) UNION select a.' || rtrim(char(field)) || ' FROM db2usr.' || rtrim(char(table)) || ' a, db2usr.var_defn b, db2usr.empdefn c, db2usr.demo_label_defn d where b.service = c.service and b.var_name = ''EMPEXCL'' and b.edlevel = a.demo_id and d.label_id = a.demo_value and locate(rtrim(d.rpt_label), exclude) > 0 and empid = ' || char(P_EMPID) || ' )'; -- end if; -- set itemp = length(squery); -- Insert into applog (Description) values ('** End length ** = '|| char(itemp) ); -- -- Insert into db2usr.result_set_defn values (timestamp, username) Insert into db2usr.result_set_defn values (default, current timestamp, user);-- -- this will only be one row ; loop is not necessary FOR_LOOP2: FOR EACH_ROW AS SELECT IDENTITY_VAL_LOCAL() as idvalue FROM SYSIBM.SYSDUMMY1 DO SET result_id = EACH_ROW.idvalue;-- END FOR;-- Insert into applog (Description) values ('** identity value ** = '|| char(result_id) ); -- -- insert into DB2USR.RESULT_SETS (result_id, value1) select distinct result_id, set squery = 'insert into db2usr.result_sets (result_id, value1) Select distinct ' || char(result_id) || ', ' || squery; -- -- call squery;-- PREPARE stmt1 from squery; -- EXECUTE stmt1;-- set P_RESULT_ID = result_id;-- RETURN result_id; end; > >> It returns a failure: SQL0746N Routine > [quoted text clipped - 13 lines] > Chapter 2. Developing routines ---> Data conflicts when procedures > read from or write to tables |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.