DDL SPLs?
David Grove on 12.10 needed a list of all SPLs containing DDL. Paul Watson suggested grepping for create and alter statements. SangGyu Jeong noted SPL text can be fetched via SQL or dbschema. Art Kagel gave an SQL query against sysprocedures and sysprocbody using MATCHES, and a myschema and awk method, noting duplicate and overload handling. Jon Ritson added a rough awk proof of concept. David was satisfied.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Solaris 10 1/13
Informix 12.10.FCI4
I received a request from a developer who wants me to send him a text file for every SPL in our system that contains any DDL.
I am not aware of any automated way to extract such SPLs (such as some system catalog property that would reduce it to an SQL SELECT statement).
I'd like to avoid manually reviewing every SPL.
I suppose one could do a sort of brute force approach, by creating a file of the names of all DDL commands, then use dbschema to dump all the SPLs to text files, and then create a script to ripple through the list of DDL commands and grep every SPL for every DDL command.
I'm on the prowl for some magic I of which I am unaware.
Thank you for any comments.
DG
------------------------------
David Grove
------------------------------
#Informix
Grep for create and alter statements ? A simple script will give you the spl names as well Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC
That's basically what I was thinking. I would do a quick review to make sure that "CREATE" and "ALTER" are comprehensive. DG ------------------------------ David Grove ------------------------------
As shown in the document below, you can also get the text of spl with sql.
https://www.ibm.com/docs/en/informix-servers/14.10?topic=routine-generate-text
I think it is more convenient to modify the dbschema output using a script.
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
Here, something like this using SQL:
select procname
from sysprocedures as sp, sysprocbody as spb
where datakey = "T" and sp.procid = spb.procid
and mode != 'o' -- internal procedures/functions
and (data matches "*[Cc][Rr][Ee][Aa][Tt][Ee]*"
or data matches "*[Aa][Ll][Tt][Ee][Rr]*"
) and (
data not matches "*[Cc][Rr][Ee][Aa][Tt][Ee]*[Pp][Rr][Oo][Cc][Ee][Dd][Uu][Rr][Ee]*"
and data not matches "*[Cc][Rr][Ee][Aa][Tt][Ee]*[Ff][Uu][Nn][Cc][Tt][Ii][Oo][Nn]*"
);
Or using myschema (better than using dbschema because you can ask it for only the SPL routines and eliminate tables etc.) and awk:
myschema -d mydatabase --procedure-file=procedures.sql >/dev/null
awk '
/CREATE PROCEDURE/{procname=$3; next}
/CREATE FUNCTION/{procname=$3; next;}
/CREATE DBA PROCEDURE/{procname=$4; next;}
/CREATE DBA FUNCTION/{procname=$4; next}
/CREATE/{ print procname; }
/ALTER/{ print procname;}
' procedures.sql
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
Thank you, all, for your comprehensive suggestions. Perfect. DG ------------------------------ David Grove ------------------------------
Nice. It would have hit me in the face momentarily, but you pre-emptively remembered to (helpfully) eliminate from consideration the "CREATE PROCEDURE" statements. DG ------------------------------ David Grove ------------------------------
David: Forgot to filter for dups (or sort -u for the myschema version) when a routine contains multiple DDL. Also in the query version you can catch overloaded functions (if you have any) by including the procid in the projection clause. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
A bit late to the game :)
A "rough" initial attempt and SEVERAL "holes" ... (functions, DDL clauses not at the beginning of the line,$0=tolower($0), etc. etc., ) ... but hopefully interesting (and I have run out of steam :)
$ cat awkit
/^create procedure/,/^end procedure/ {
if (($1=="create") && ($2 == "procedure"))
{fl=0; sub("[(].*[)]","",$3); procname=$3}else
if (($1=="drop") && ($2=="table"))
fl=1;
else
if (($1=="create") && ($2=="table"))
fl=1;
else
if (($1=="alter") && ($2=="table"))
fl=1;
else
if (($1=="create") && ($2=="temp") && ($3=="table"))
fl=1;
else
if (($1=="end") && ($2 == "procedure;") && (fl==1))
printf "dbschema -d %s -f %s\\n", dbname, procname;
}
$ dbschema -d stores_demo | awk -f awkit dbname=stores_demo
dbschema -d stores_demo -f "informix".dummy2Note this is a "PoC" and not anything more than that :)
JJ
------------------------------
Jon Ritson
------------------------------