Last Update: 20190611 Local Path: "C:\DAN\HTM\GoDaddy\dansher\utut\spl\README.txt" Informix Stored Procedures Language ("SPL", not to be confused with SQL) compiles text files containing Informix SPL-compliant statements. All such files end in ".sql" just like SQL files. The dbaccess binary and additional system Tables distributed with the 5.0 Standard Engine can read, compile, and execute SPL statements. Once these statements are compiled, the resulting executable is known as a Stored Procedure ("SP"). The SP becomes data within the database and the *.sql file representing the SP source-code is only read one time: during the SPL compile-phase. An SP is compiled (via running the *.sql file [with dbaccess] within which the SP is defined) ONE TIME ONLY. Upon completion, the text of the SP is stored within the database where it can be CALLed or EXECUTEd (as appropriate) by name within some other *.sql command file(s), provided dbaccess (and NOT isql) is controlling (i.e., running) the *.sql. Note that isql 4.1 (our version as of Thu Jan 21 15:31:51 CST 1993) is ignorant of all matters SP and SPL. Therefore use only dbaccess to run any *.sql file containing reference(s) to anything SPL. SPL is far more flexibile than SQL in that it offers the traditional programming constructs such as conditional branching (IF/THEN/ELIF/ELSE), looping (FOR, WHILE, FOREACH), and error handling (ON EXCEPTION). In addition, most SQL statements can be placed and executed within an SP. See Pages 7-57 and 7-58 in the Informix Guide to SQL (December 1991) for a list of "can't use in within a SP" SQL statements. One of the best jobs for SPL is where one or more Tables need to be UPDATEd based on the content of one or more other Tables. SQL requires the use of [very inefficient] sub-queries to achieve what SPL can do with a FOREACH loop. For example, pm.rc.sql took FIVE HOURS to run under SQL, but the SP pmrc_spl does the same work in UNDER A MINUTE! Note the code segment below (taken from rcup_spl.sql) as an example ("hot" is a TEMP TABLE): FOREACH imod FOR -- ratchet thru Rows in hot SELECT rec_inv_stk_no, i_bal, iu_cost, ilu_cost, iqoo INTO stk_no, on_hand, u_cst, last_cst, qoo FROM hot UPDATE inv_file SET inv_bal_on_hand = on_hand, -- value in current hot Row. inv_unit_cost = u_cst, -- value in current hot Row. inv_last_unit_cost = last_cst, -- value in current hot Row. inv_qty_on_order = qoo, -- value in current hot Row. inv_last_typ = "RC", inv_last_act = TODAY WHERE inv_stk_no = stk_no -- joins inv_file & hot ; -- required terminator of SELECT END FOREACH ; Argument values can can be passed into an SP (if it was created to accept them) and processed or SELECTed values can be RETURNed out of an SP. For more info on SPL (all of it, actually), see: Informix Guide to SQL Reference (December 1991) ------------------------------------------------------------ CREATE PROCEDURE Pages 7-52 thru 7-59 DROP PROCEDURE Page 7-130 EXECUTE PROCEDURE Pages 7-142 thru 7-143 SET DEBUG FILE TO Pages 7-279 thru 7-280 SPL Syntax, examples, etc. All of Chapter 8 Informix ESQL/C (which someday we hope to have [and know how to use]) has a slightly different way (CREATE PROCEDURE FROM and PREPARE) of compiling SPL, and more information on this is to be found on Page 7-60 and Pages 7-208 thru 7-220. Informix Guide to SQL Tutorial (December 1991) ------------------------------------------------------------- Chapter 11, "Using Stored Procedures" Pages 11-16 thru 11-21 DOING IT ======== At the moment, I infer that dbaccess can compile SPL statements into an SP three ways. The first two of these are like: dbaccess db_name spl_file.sql # from the command line, ONE TIME! # From within a shell script as in this example: dbaccess db_name - << HURL CREATE PROCEDURE itct () RETURNING integer; DEFINE howmany integer; LET howmany = (SELECT count(*) FROM items); RETURN howmany; END PROCEDURE DOCUMENT "This action shows current Row-count in Table items.", "This Stored Procedure is very simplistic.", "Usage: EXECUTE PROCEDURE itct ();"; HURL # end of shell script segment The third way is the interactive mode of dbaccess (e.g., dbaccess db_name). In this mode the "ring-menu" is operational. From this the User chooses the Query-language option, and thence what s/he wants to do in real time. Running an SP can NOT be done from the UNIX command line, but an SP CAN successfully be EXECUTEd from within a shell script "here document" as the segment below (from /u/spims2/SCREENS/TEST/spl_run.sh) examples: # begin segment dbaccess $1 << GOSPL 2>/dev/null SET DEBUG FILE TO "/tmp/debug.spl"; -- see P. 7-279 & 8-73 EXECUTE PROCEDURE $2 $ARG ; GOSPL # end segment In the example above, if the SPL "TRACE ON;" statement had beem present when the SP was compiled, then chatter from the SPL interpreter will be gathered into "/tmp/debug.spl" while the SP is EXECUTEing. Below is an example of SPL which CREATEs a Stored Procedure ("SP") called "see_doc". see_doc can display the [optional] documentation [that may be stored] within any SP [with the DOCUMENT statement]. see_doc must be invoked in one of the two ways shown inside the DOCUMENT section. "--" delimit the beginning of all programmer's comments. -- see_doc.sql by Dan Martin (DRM/CTG) Fri Jan 22 08:57:36 CST 1993 -- Contains SPL: Use ONLY with dbaccess! CREATE PROCEDURE see_doc (sp_name char(14) default "see_doc") RETURNING char(70); DEFINE doc_text char(70); FOREACH SELECT data[1,70] doc INTO doc_text FROM sysprocbody WHERE datakey = "D" AND procid = (SELECT p.procid FROM sysprocedures p WHERE p.procname = sp_name) RETURN doc_text WITH RESUME; END FOREACH ; END PROCEDURE DOCUMENT "Stored Procedure 'see_doc' will display the Stored", "Documentation for any SP named 'sp_name'.", 'Usage: EXECUTE PROCEDURE see_doc ("sp_name");', ' - OR: EXECUTE PROCEDURE see_doc ();' WITH LISTING IN "/tmp/hurl.spl" ; -- EOF see_doc.sql FYI, the SP see_doc works (i.e., can be EXECUTEd)! MANAGING STORED PROCEDURES (SP) AT ALL PLANTS ============================================= The scripts listed below have been placed within the directory /u/spims2/DB/ to manage the many Stored Procedures (SP) that will become an ever greater part of the SPIMS system. It is probably best to invoke them from within /u/spims2/DB/ (i.e., while /u/spims2/DB/ is the active directory). spl_list - lists all SP present in all Plant Databases. Since there is a lot of vertical output, suggested invocation is: spl_list | tee /tmp/xxx (then use vi or pg on /tmp/xxx) spl_comp - DROPs and then re-CREATEs ("compiles") a single SP for all Plants (if changes are desired for an existing SP, it must first be DROPed before it can be CREATEd again). This script requires that an SP named "sp_name" to be completely and correctly defined within the file "sp_name.sql" which MUST be located within the directory "/u/spims2/SPL/". The working segment from within spl_comp is reproduced below for your amusement ($1 represents the SP argument/name): dbaccess $SDB - << GOSPL # 2>/dev/null DROP PROCEDURE $1; UPDATE STATISTICS; GOSPL dbaccess $SDB $SPM/SPL/$1 dbaccess $SDB - << STOP GRANT EXECUTE ON $1 TO public; STOP spl_comp uses the argument you give it on invocation (e.g., spl_comp foo_spl) as the name of the SP (e.g., foo_spl) to DROP, and also as the "front" of the actual filename (e.g., foo_spl.sql) within which an SP of the same name is CREATEd. The SP-creating *.sql may be located ONLY within /u/spim2/SPL/ spl_drop - DROPs a single Stored Procedure from ALL Plants. Useful for cleaning out obsolete or abandoned SPs. spl_redo - DROPs and then re-CREATEs ALL SPL/*.sql Stored Procedures. Like spl_comp, it GRANTs EXECUTE to all SPs to PUBLIC. Use this script to be SURE that every Plant has (and is running the same version of) all Stored Procedures. WARNING: ANY and ALL ../SPL/*.sql MUST obey the name "rule" mentioned in the paragraph below because `basename ../SPL .sql` is used to create a list of Stored Procedures to be DROPed. This same list is used to re-CREATE each Stored Procedure. CAUTIONS: Any SP destined to be run by cron (even as root) MUST be created with the "DBA" Option (e.g., CREATE DBA PROCEDURE foo_cron). Otherwise UPDATEs and other Table changes will fail because the SP will take EXCEPTION to all instances where the Table is not owned by the cron-entity du jour. ALL /u/spims2/SPL/*.sql ARE RUN by phoenix as the means by which all Stored Procedures are CREATEd in the new database. The implication is that ALL *.sql which CREATE Stored Procedures for ALL Plants MUST be located within the directory /u/spims2/SPL/. Likewise, there MUST NOT BE ANY *.sql within /u/spims2/SPL/ which do anything other than to CREATE Stored Procedures! Scripts spl_comp and spl_redo BOTH give EXECUTE priviledge to ALL LOGINS (i.e., to the Informix "public" generic-login). Therefore all SPs should be "guarded" by some shell script that controls access to various Users, either by looking at `logname`, or by seeking matches within the textfile svnauth.$SITE or within any of the Columns ulogins.ulog, emp_pur.emp_pa_int, emp_pur.ulogin, or emp_pur.emp_ty >>>>>>>>>> FOREACH loops NEED an Index on the Target table. Example: FOREACH si_act FOR SELECT delete_me INTO stk_no FROM deaduns DELETE FROM dead_stock WHERE dead_stk = stk_no An Index MUST (really, MUST!) already exist or be explicitly CREATEd on dead_stock.dead_stk even though [in this example from SPL/delete_si.sql] dead_stock is a TEMP Table. See the shell script SHELLS/deadstk.sh for a [clever and hard-] working, and fully detailed, example. The reason for the critical need of the Index is that the (so-called) Informix Optimizer sees each DELETE in the FOREACH loop as a singular, one-time event, [fortunately] undeserving of the [potentially considerable] effort needed for auto-indexing every time around the loop. Without an Index, only a very inefficient sequential-search (thru Table dead_stock) can be used to satisfy the WHERE clause. The Informix folks should teach said [alledged] Optimizer how to analyze FOREACH loops so that it could forge a reasonable auto-indexing strategy. >>>>>>>>>> If TRACE ON is in effect, it should probably be made TRACE OFF BEFORE all FOREACH loops. Debug output from a large loop could be humongous and potentially run an HD out of free space. As with *.ace *.sql *.per and *.sh files, all SP must (MUST!) be kept the same for all Plants. Hence the existence of spl_comp and spl_redo. End of Text