Informix

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

Original Message:
Sent: 11/8/2021 6:19:00 PM
From: David Grove
Subject: DDL SPLs?

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

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:
Sent: Mon November 08, 2021 06:28 PM
From: Paul Watson
Subject: DDL SPLs?

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

Original Message:
Sent: 11/8/2021 6:19:00 PM
From: David Grove
Subject: DDL SPLs?

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

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:
Sent: Mon November 08, 2021 06:34 PM
From: David Grove
Subject: DDL SPLs?

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:
Sent: Mon November 08, 2021 06:28 PM
From: Paul Watson
Subject: DDL SPLs?

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

Original Message:
Sent: 11/8/2021 6:19:00 PM
From: David Grove
Subject: DDL SPLs?

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

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:
Sent: Tue November 09, 2021 12:22 AM
From: SangGyu Jeong
Subject: DDL SPLs?

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:
Sent: Mon November 08, 2021 06:34 PM
From: David Grove
Subject: DDL SPLs?

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:
Sent: Mon November 08, 2021 06:28 PM
From: Paul Watson
Subject: DDL SPLs?

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

Original Message:
Sent: 11/8/2021 6:19:00 PM
From: David Grove
Subject: DDL SPLs?

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

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:
Sent: Tue November 09, 2021 07:03 AM
From: Art Kagel
Subject: DDL SPLs?

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:
Sent: Tue November 09, 2021 12:22 AM
From: SangGyu Jeong
Subject: DDL SPLs?

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:
Sent: Mon November 08, 2021 06:34 PM
From: David Grove
Subject: DDL SPLs?

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:
Sent: Mon November 08, 2021 06:28 PM
From: Paul Watson
Subject: DDL SPLs?

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

Original Message:
Sent: 11/8/2021 6:19:00 PM
From: David Grove
Subject: DDL SPLs?

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

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:
Sent: Tue November 09, 2021 12:41 PM
From: David Grove
Subject: DDL SPLs?

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:
Sent: Tue November 09, 2021 07:03 AM
From: Art Kagel
Subject: DDL SPLs?

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:
Sent: Tue November 09, 2021 12:22 AM
From: SangGyu Jeong
Subject: DDL SPLs?

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:
Sent: Mon November 08, 2021 06:34 PM
From: David Grove
Subject: DDL SPLs?

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:
Sent: Mon November 08, 2021 06:28 PM
From: Paul Watson
Subject: DDL SPLs?

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

Original Message:
Sent: 11/8/2021 6:19:00 PM
From: David Grove
Subject: DDL SPLs?

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

Thank you, all, for your comprehensive suggestions.

Perfect.

DG

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

Original Message:
Sent: Mon November 08, 2021 06:34 PM
From: David Grove
Subject: DDL SPLs?

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:
Sent: Mon November 08, 2021 06:28 PM
From: Paul Watson
Subject: DDL SPLs?

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

Original Message:
Sent: 11/8/2021 6:19:00 PM
From: David Grove
Subject: DDL SPLs?

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

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:
Sent: Tue November 09, 2021 12:20 PM
From: David Grove
Subject: DDL SPLs?

Thank you, all, for your comprehensive suggestions.

Perfect.

DG

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

Original Message:
Sent: Mon November 08, 2021 06:34 PM
From: David Grove
Subject: DDL SPLs?

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:
Sent: Mon November 08, 2021 06:28 PM
From: Paul Watson
Subject: DDL SPLs?

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

Original Message:
Sent: 11/8/2021 6:19:00 PM
From: David Grove
Subject: DDL SPLs?

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