Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  DDL SPLs?

    Posted 11/08/21 06:19 PM
    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


  • 2.  RE: DDL SPLs?

    Posted 11/08/21 06:29 PM
    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





  • 3.  RE: DDL SPLs?

    Posted 11/08/21 06:34 PM
    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
    ------------------------------

    Original Message


  • 4.  RE: DDL SPLs?

    Posted 11/09/21 12:23 AM
    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
    ------------------------------

    Original Message


  • 5.  RE: DDL SPLs?

    Posted 11/09/21 07:04 AM
    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
    ------------------------------

    Original Message


  • 6.  RE: DDL SPLs?

    Posted 11/09/21 12:42 PM
    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
    ------------------------------

    Original Message


  • 7.  RE: DDL SPLs?

    Posted 11/09/21 12:58 PM
    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
    ------------------------------

    Original Message


  • 8.  RE: DDL SPLs?

    Posted 11/09/21 12:20 PM
    Thank you, all, for your comprehensive suggestions.

    Perfect.

    DG

    ------------------------------
    David Grove
    ------------------------------

    Original Message


  • 9.  RE: DDL SPLs?

    Posted 11/10/21 07:07 AM
    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".dummy2


    Note this is a "PoC" and not anything more than that :)

    JJ

    ------------------------------
    Jon Ritson
    ------------------------------

    Original Message


Related Content