1 / 50

Informix Stored Procedure Tips and Tricks

Informix Stored Procedure Tips and Tricks. Stored Procedure Tips and Tricks. Peter Wages EDS peter.wages@eds.com. Bob Carts Senior Data Engineer, SAIC robert.carts@saic.com. Warren Donovan Senior DBA, SAIC warren.donovan@saic.com. Introduction. Certified Informix DBAs

maire
Download Presentation

Informix Stored Procedure Tips and Tricks

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Informix Stored Procedure Tips and Tricks

  2. Stored Procedure Tips and Tricks Peter Wages EDS peter.wages@eds.com Bob Carts Senior Data Engineer, SAIC robert.carts@saic.com Warren Donovan Senior DBA, SAIC warren.donovan@saic.com

  3. Introduction • Certified Informix DBAs • WAIUG Board of Directors • Work for EDS and SAIC • NT and UNIX (Solaris, IBM AIX, HP-UX) • IDS 7.31, 9.21, 9.3 and XPS 8.3 • Data Warehouse and OLTP Applications

  4. Introduction • General Comments • Stored Procedure Basics • Beyond the Basics • Summary

  5. When to use stored procedures • Use them for Systems Administration functions, such as changing passwords • Use them for common database code that will be executed often, making development and tuning easier • Use them for security to hide the code • Use them to place processing on the database server versus a client machine • Use them with Java, 4GL, ESQL/c or other tools

  6. When NOT to use stored procedures • Stored procedures for Informix, Oracle and Sybase are proprietary and cannot be migrated easily • Does anyone know about DB2 procedure and Informix procedure compatibility/migratability? • With queries that are frequently changed and updated

  7. Stored Procedure Basics • What is a Stored Procedure? • Creating a Stored Procedure using DBAccess • Parts of the Stored Procedure • Compile Time Errors and Warnings • Running a procedure in DBAccess • Returning multiple sets of values • Permissions on a Stored Procedure • Other SPL commands

  8. What is a Stored Procedure? • Pre-parsed • Stored within the System tables • Cached in Memory after initial execution • Importance of update statistics • When is a procedure re-parsed? • Update Statistics flags it to be re-parsed. • The procedure is also parsed at first execution if a table in SQL is not available at compile time.

  9. Parts of a Simple Stored Procedure • Procedure Definition: CREATE PROCEDURE warren_proc ( id_num CHAR(4)) --accept one argument RETURNING CHAR(4), INTEGER, DECIMAL(9,2); -- 3 items • Define Variables -- you can define any # of variables DEFINE p_dmis_id CHAR(4); -- of any data type, DEFINE p_fy INTEGER; -- including system level variables! DEFINE p_cap_rate DECIMAL(9,2); • SQL Statement - Note the INTO / RETURN clauses! SELECT dmis_id, fy, AVG(cap_rate) INTO p_dmis_id, p_fy, p_cap_rate FROM cap_rate WHERE dmis_id=id_num GROUP BY 1,2 ; RETURN p_dmis_id, p_fy, p_cap_rate; • Don’t Forget to end the Procedure! END PROCEDURE

  10. Running a Simple Stored Procedure • To Run in DBAccess: • EXECUTE PROCEDURE warren_proc (’0252'); • This returns the following line: (expression) (expression) (expression) 0252 1998 1899.21 • But what if there are multiple Store IDs? • ERROR: 284: A subquery has returned not exactly one row.

  11. FOREACH: Returning Multiple Rows • Use the FOREACH clause to return multiple values: CREATE PROCEDURE warren_proc ( id_num CHAR(4)) --accept one argument RETURNING CHAR(4), INTEGER, DECIMAL(9,2); -- 3 items DEFINE p_dmis_id CHAR(4); DEFINE p_fy INTEGER; DEFINE p_cap_rate DECIMAL(9,2); FOREACH SELECT dmis_id, fy, AVG(cap_rate) INTO p_dmis_id, p_fy, p_cap_rate FROM cap_rate WHERE dmis_id=id_num GROUP BY 1,2 ; RETURN p_dmis_id, p_fy, p_cap_rate WITH RESUME; --or it will return only the first row END FOREACH -- without this, the procedure will fail to compile with a syntax error END PROCEDURE

  12. FOREACH: Returning Multiple Rows • Now Run the query: • EXECUTE PROCEDURE warren_proc ('0001'); • Which will return: 0001 2002 1656.84 0001 1999 2157.99 0001 2000 1157.51 0001 2001 1263.12 0001 1998 2623.42

  13. Permissions and Stored Procedures • To Create a Stored Procedure: • To create a DBA-privileged procedure, must have DBA • To create an owner-privileged procedure, must have Resource • To Execute a Stored Procedure: • A user must have been granted execute on the procedure. • A user does not necessarily have to be granted access to all the tables in the stored procedure. • Run-Time Stored Procedure Privileges • The privileges depend on the type of procedure being run • 2 Types: Owner-Privileged and DBA-Priviliged

  14. Owner-Privileged Stored Procedures • When an Owner-Privileged Stored Procedure is executed, the server checks the existence of any referenced objects, and that the user has access to all the tables • If the user has all necessary privileges, no problem • If the user does not have all the necessary privileges, but the OWNER of the stored procedure does, no problem • If neither has the necessary privileges, the query will fail

  15. DBA-Privileged Stored Procedures • When a DBA-Privileged SP is executed, the user assumes DBA privileges for the duration of the procedure. • When a DBA-Privileged SP calls a User-Privileged SP, the called User-Privileged procedure does NOT act like a DBA-Privileged Stored Procedure. • Likewise, if a DBA-Privileged SP is called by a User-Privileged SP, it retains its privileges for the duration of its run, returns to the calling SP, which maintains its privileges.

  16. Other Stored Procedure Language Commands • LOOPS • The FOREACH loop- used to select and manipulate more than one row of data • The FOR Loop - uses expressions or range operators to establish a finite number of iterations for a loop • The WHILE Loop - indefinite loop while a given criterion holds true. Terminates when not true (beware of nulls!) • CALL - use a call to execute a procedure from within a procedure • IF, ELSE, ELIF - used to create branches within a procedure • LET- used to assign values to variables • CONTINUE - starts the next iteration of the innermost loop • EXIT- stops the execution of a loop

  17. Stored Procedures:Beyond the Basics • Raising Exceptions • Returning diagnostic/error information • Stored Procedure Maintenance • DBINFO function • Calling a Stored Procedure from a Stored Procedure • Create Your Own Exceptions • Tracing and Debugging • The Value of Current (current date-time) • Commands You Can’t Use • Stored Procedure in a Select Statement • Stored Procedures in Java • Web Reporting • Stored Procedures in 4GL • Blocking Statements • A Very Special XPS Stored Procedure • Using the “system” command

  18. On Exception • ON EXCEPTION set sql_code,isam_code,sql_message • return null, null, null, null,sql_code, isam_code, sql_message; • END EXCEPTION • If the stored procedure gets an exception , (e.g –206 table does not exist ) • What will be returned is • (expression) -206 • (expression) -111 • (expression) The specified table does not exist

  19. Returning diagnostics and Error Information CREATE PROCEDURE "informix".sp_state_lookup( p_state_name char(20) ) RETURNING char(2),int,int,char(70); DEFINE v_state_code char(2); DEFINE nrows int; DEFINE sql_code int; DEFINE isam_error int; DEFINE sql_message char(70); ON EXCEPTION set sql_code,isam_error,sql_message RETURN " ",sql_code,isam_error,sql_message; END EXCEPTION SET LOCK MODE TO WAIT 20; LET v_state_code = " "; SELECT state_code INTO v_state_code FROM state_codes WHERE state_name = upper(p_state_name); LET nrows = dbinfo("sqlca.sqlerrd2"); IF nrows = 0 then LET v_state_code = null; END IF RETURN v_state_code,10,0,"State Code Retrieved"; END PROCEDURE;

  20. Stored Procedure Maintenance • Since stored procedures are cached in memory you may be executing the old version of the procedure after you drop and recreate the procedure. This is true for older releases (i.e. 5.0) • If a table structure that a stored procedure uses is altered, you are NOT notified that the procedure may fail. You will only find out when it is executed! • When update statistics is run, procedures are flagged to be re-parsed. They are re-parsed when first executed after that point.

  21. DBINFO Function • Informix functions can be used in stored procedures • Use DBINFO(“sqlca.sqlerrd2”) to determine if any rows were selected. You can’t access SQLCODE unless there is an exception./error condition. • When you insert a record with a serial field , you can get the value of the serial with the following: • DBINFO("sqlca.sqlerrd1"); • DBINFO(”sessionid”) produces the informix session

  22. Calling a stored procedure from a stored procedure and Raising Exceptions. • Create procedure ------ • ON EXCEPTION etc... • END EXCEPTION • . • .EXECUTE PROCEDURE sp_state_lookup ( p_state ) INTO v_state_code,sscode,sisamcode,smsg; • IF sscode < 0 THEN • RAISE EXCEPTION sscode,sisamcode,smsg; • END IF • . • End procedure

  23. Create Your Own Exceptions • You can create your own exception conditions • IF user = “WARREN” THEN • RAISE EXCEPTION -746, 0, ”Deny Warren access” • END IF • Informix’s Special SQLCODE -746 • > finderr -746 • -746 message-string • You supply message-string for this message. You can apply this message • to error conditions that you specify in a stored procedure. The corrective • action for this error depends on the condition that caused it. You, the user, • define both the condition and the message text.

  24. TRACE and SET DEBUG FILE TO • The trace command and “set debug file to “ • Use the session_id to get independent simultaneous traces let v_session_id = dbinfo('sessionid');set debug file to "/tmp/wfsp"||v_session_id||".log";trace on; trace variable_name;

  25. Conditionally setting debug • create procedure get_orders (pfname char(20),plname char(30),p_debug_flag char(1) default "N") : • if p_debug_flag = "Y" then • set "debug file to ”/tmp/debuglog"; • set trace on; • else • set trace off; • end if

  26. Trace file • Sample Trace File • trace expression :21:45 START TIME • iteration of cursory procedure sp_wf_cam_b_t1 • select cursor iteration. • select cursor returns G94440 , 52 , WASHINGTON TECH • procedure sp_wf_cam_b_t1 returns G94440 , 52 , WASHINGTON TECH • , 2 , NULL. , Success • trace expression :21:45 END TIME

  27. Why is the start time the same as the end time? • Why does CURRENT always return the same value in SPL? • It is a requirement of ANSI SQL that a stored procedure return a constant value for CURRENT throughout its lifetime. This is the way that Informix implemented CURRENT in SPL to meet the standard

  28. CLOSE CLOSE DATABASE CONNECT CREATE DATABASE CREATE PROCEDURE CREATE PROCEDURE FROM DATABASE DECLARE DESCRIBE EXECUTE EXECUTE IMMEDIATE FETCH FLUSH FREE GET DESCRIPTOR INFO LOAD/UNLOAD OPEN OUTPUT PREPARE PUT SET DESCRIPTOR WHENEVER Commands You Can’t Use

  29. Using a Stored Procedure in a Select • Select Customer_no, Cust_code_proc(customer_no) from Customer • Restricts what commands you can use in the procedure

  30. ALTER FRAGMENT ALTER INDEX ALTER TABLE BEGIN WORK COMMIT WORK CREATE TRIGGER DELETE DROP DATABASE DROP INDEX DROP SYNONYM DROP TABLE DROP TRIGGER DROP VIEW INSERT RENAME COLUMN RENAME TABLE ROLLBACK WORK UPDATE Manipulation Restricted Commands

  31. JAVA try { callableStatement = oConnection.prepareCall ("{call sp_get_insert_sbs()}"); rs = callableStatement.executeQuery(); if (rs.next()) { setBatchSerialId((rs.getString(1)),session_id); sqlcode = rs.getInt(2); isam_code = rs.getInt(3); if ( sqlcode < 0 ) { } } } catch (Exception e) { }

  32. Using Stored ProceduresFor Web Reports • At EDS, we had to produce a web report of statistical data using IBI’s Web Focus report utility • The report had to run “On_DEMAND” and produce “REAL-TIME” data • The report had to get data from some major tables in the database including a table with 20 million records • What we did was write a stored procedure

  33. Using Stored ProceduresFor Web Reports • Calling a Stored Procedure From Web Focus • Case Statement • Setting PDQPRIORITY • Using Temp Tables • Optimizer Directives • Returning multiple rows for reporting

  34. Calling a stored procedure from WEBFOCUS • EX sp_wf_cam_b_sum "&PROGYR", "&REGION", "&SCH_CODE" ,"&CAM", "&STATE", "&DTE_TODAY", "&END_PREVMO", "&LST_FRIDAY", "&DTE_30PREV", "&DTE_90PREV", "&DTE_365", "&DTE_30PR_365", "&DTE_90PR_365";

  35. Using Stored Procedures For Web Reports - The CASE Statement • SELECT • school_no, • activity_code, • CASE • WHEN (booked_date > "12/31/1989" and booked_date <= v_end_date ) THEN "B" • WHEN (booked_date is null or booked_date > v_end_date ) THEN "U" • WHEN booked_date = "12/31/1989” THEN "A" • ELSE "B" • END booked_ind, • sum(actual_gross_adj - actual_fee_adj + nvl(actual_rebate_adj,0)) net_amt • FROM disburse_activity • WHERE school_no in ( select school_no from school_sum ) • AND prog_year_codes_id = p_py_code • AND action_status_date <= v_end_date • GROUP by 1,2,3 • INTO TEMP temp_disb_cnts with no log;

  36. Using Stored Procedures For Web Reports - PDQPRIORITY • Set pdqpriority 40;

  37. Using Stored Procedures For Web Reports

  38. Using Stored Procedures in 4GL • If only one set of values are returned: • PREPARE p_exec FROM "EXECUTE PROCEDURE procname(?,?,?)" • EXECUTE p_exec USING value01, value02, value03

  39. Using Stored Procedures in 4GL • If multiple sets of values are returned: • PREPARE p_exec FROM "EXECUTE PROCEDURE procname(?,?,?)" • DECLARE c_exec CURSOR FOR p_exec • OPEN c_exec USING value01, value02, value03 • WHILE STATUS = 0 • FETCH c_exec INTO return01, return02, return03, return04 • IF STATUS != 0 THEN • EXIT WHILE • END IF • ... • END WHILE • CLOSE c_exec

  40. Blocking Statements • create procedure block3 ( ) returning int; • define error_num int; • define c int; • define cmd char(30); • BEGIN • on exception in ( -206,-217 ) set error_num • if error_num = -206 then • create table t ( c int ) ; • insert into t values ( 10 ); • elif error_num = -217 then • alter table t add (d int ); • let c = ( select d from t ); • else • return error_num; • END IF • END EXCEPTION WITH RESUME • insert into t values (10); • let c = ( select d from t ); • END • BEGIN • let c = ( select e from t ); • END • return c; • end procedure

  41. Blocking Statements -Scope • Scope of Control of an ON EXCEPTION Statement • An ON EXCEPTION statement is valid for the statement block that follows the ON EXCEPTION statement, all the statement blocks nested within that following statement block, and all the statement blocks that follow the ON EXCEPTION statement. It is not valid in the statement block that contains the ON EXCEPTION statement. • Example does not agree with statement above • It is not valid in the statement block that DOES NOT contain the ON EXCEPTION statement

  42. Blocking Statements - The Informix Example • CREATE PROCEDURE scope() • DEFINE i INT; • BEGIN -- begin statement block A • . • ON EXCEPTION IN (201) • -- do action a201 • END EXCEPTION • BEGIN -- statement block aa • -- do action, a201 valid here • END • BEGIN -- statement block bb • -- do action, a201 valid here • END • WHILE i < 10 • -- do something, a201 is valid here • END WHILE • END • BEGIN -- begin statement block B • -- do something • -- a201 is NOT valid here • END • END PROCEDURE

  43. Using Resume with Exception • create procedure pop_me(_in_proc_id char(6))returning int; • begin work; • foreach j_cursor for select... into... from holding_table • ON EXCEPTION... • set sqlerr, isamerr, etc ..... • insert into error_table ... • let _result = 1; • if _in_proc_id <> 'FULL' then • return _result; • end if; • END EXCEPTION WITH RESUME • if data is bad then raise exception -746 • update holding_table set status = _status • where current of j_cursor • end foreach • commit work;

  44. Using Resume with Exception • put a commit work in the exception block ON EXCEPTION... • set sqlerr, isamerr, etc ..... • insert into error_table ... • let _result = 1; • if _in_proc_id <> 'FULL' then • commit work; • return _result; • end if; • END EXCEPTION WITH RESUME • exception block was being executed both for “unexpected” errors and bad records. • A stored procedure may not be the best tool to migrate data from a legacy system.

  45. XPS - A Stored Procedure You Need to Know This procedure sets IMPLICIT PDQ on. It allows the engine to estimate how much PDQ memory your query will need, and allocate on that much, up the maximum allowable set by MAX_PDQPRIORITY: CREATE PROCEDURE public.sysdbopen () SET ENVIRONMENT IMPLICIT_PDQ ON; SET ENVIRONMENT BOUND_IMPL_PDQ ON; END PROCEDURE;

  46. Using the “system” Command • The system command permits calling operating system commands and scripts from a stored Procedure • There is no easy way to determine if the system call completed successfully • There can be a major performance impact when using many system calls. Informix explained that this is due to re-authentication each time a system command is used

  47. Using the “System” Command • create PROCEDURE "informix".sp_chg_pwd(newpwd CHAR(100)) • RETURNING INT; • DEFINE retval INT; • LET retval = 0; • SYSTEM newpwd; • LET retval = 1; • RETURN retval; • END PROCEDURE;

  48. Stored Procedure Resources • Informix Guide to SQL Tutorial • Informix Guide to SQL Syntax • Informix Stored Procedure Programming – Michael L. Gonzales (Prentice-Hall Publisher ) 1996 ISBN 0-13-206723-4

  49. INFORMIX FAQ • www.iiug.org/ver1/resources/informix_faq.html

  50. Suggestions • Submit your tips (peter.wages@eds.com, robert.m.carts@saic.com, warren.donovan@saic.com ) • Will be placed on WAIUG site

More Related