Extracting SPLs From An Informix Database

At least with the tools that we have here (and from what I gather from reading requests for this around the web), there is no terribly easy way to extract the text of Stored Program Language procedures (SPL) from the Informix database.  This is actually pretty important for reasons that I had best not discuss, but the following code might be useful to others stuck in Informix land.

There are three SPLs required; sel_spl_app actually extracts the information into a table like this:

create table 'ccramer'.spl_for_diff (
    procid INT not null,
    spl_name CHAR(80) not null,
    spl_text CHAR(32000) not null
)
extent size 32 next size 32
lock mode page;

The sel_spl_name and sel_spl_text SPLs are used by sel_spl_app.  You then run an UNLOAD command on your host to take the information from spl_for_diff table.  As the comments at the start of sel_spl_app explain, these all have to go into a single file, but there is a bash script shown there as well that shows how to extract the SPLs into individual files.  Unfortunately, this does not handle situations where you have multiple SPLs with the same name, but different numbers of types of parameters.

DROP PROCEDURE sel_spl_app();

-- This procedure retrieves the procid for all the SPLs defined in sysprocbody that are not DBA specific,
-- then finds the name and the text and puts them into the spl_for_diff table, which can then be unloaded to the PC.
-- Unfortunately, the UNLOAD command cannot be included in an SPL, because it has to run on the host, not on the
-- server.  Here is a typical UNLOAD command to run after this is complete:
--   UNLOAD TO "d:\Users\ccramer\My Documents\all" SELECT spl_name,spl_text FROM spl_for_diff WHERE LOWER(spl_name) NOT LIKE "%copyright";
-- Note that we exclude rows in spl_for_diff LIKE "%copyright" to skip the weird debugging SPLs that are used by the Server Studio
-- SPL debugger.  You may have additional items that you want to leave out.
-- I wish that there had been some way to have either sel_spl_app or the UNLOAD command separate each SPL into its own file,
-- but I could not figure out how to do that (and there was additional processing required anyway).  These are handled by a
-- bash script instead.
-- Here is a sample bash script that separates all the SPLs in "all" into individual files.
-- #!/bin/bash
-- rm -f ~/tmp/spls/*
-- IFS=$'\n'
-- # remove all the closing |\r stuff, because these mark the end of an SPL.
-- # Also replace all the \ at the end of lines with a marker that will later
-- # let us return these to newlines, after we have written a single long line
-- # to a file.
-- sed -e "s/|\r$//g" <$1 | sed ':a;N;$!ba;s/\\\n/[EOL]/g' >~/tmp/all
-- # Now /tmp/all consists of nothing but spl_name|spl_text -- time to
-- # write these all into individual files.
-- for line in `cat ~/tmp/all`
-- do
--   splName=`echo $line | awk -F"|" '{print $1}' | tr -d '\t'`
--   splText=`echo $line | awk -F"|" '{print $2}'`
--   echo "splName="$splName >>~/tmp/spls/splList
--   echo $splText | sed -e "s/\[EOL\]/\n/g" >~/tmp/spls/$splName.sql
-- done

create procedure "ccramer".sel_spl_app();
-- The procid that uniquely defines each SPL.
DEFINE l_procid INTEGER;
-- The SPL's name, retrieved from the text (which may or may not work)
DEFINE l_spl_name CHAR(256);
-- The SPL's text, retrieved using the l_procid from sysprocbody
DEFINE l_spl_text CHAR(32700);


DEFINE GLOBAL g_sqlcode     INTEGER DEFAULT 0;
DEFINE GLOBAL g_isamcode    INTEGER DEFAULT 0;
DEFINE GLOBAL g_errval      CHAR(80) DEFAULT "";

BEGIN
  LET l_spl_name = TRIM("");
  -- Remove all rows in the spl_for_diff table
  DELETE FROM spl_for_diff;
  -- Fill in this table with the required data
  FOREACH procid_cursor FOR
    SELECT spb.procid INTO l_procid FROM sysprocbody spb
    WHERE spb.datakey='T'
      AND (LOWER(spb.data) LIKE "%create procedure%" OR LOWER(spb.data) LIKE "%create function%")
      AND LOWER(spb.data) NOT LIKE "%create dba%" AND LOWER(spb.data) NOT LIKE "%create procedure --%"
          AND spb.procid != 116
      -- Retrieve the SPL name
      EXECUTE PROCEDURE sel_spl_name(l_procid) INTO l_spl_name, g_sqlcode, g_isamcode, g_errval;
      -- Retrieve the text of the SPL
      LET l_spl_text = sel_spl_text(l_procid);
      -- Store this information for this SPL into spl_for_diff table
      INSERT INTO spl_for_diff (procid, spl_name, spl_text)
     VALUES (l_procid, l_spl_name, l_spl_text);
   END FOREACH;
END;
END PROCEDURE;

DROP FUNCTION sel_spl_name(integer);

-- For a particular procid, retrieve the SPL name -- which involves finding the name in the SPL text, and
-- parsing out just the name (not the owner, and not the parameters).
create procedure "ccramer".sel_spl_name(i_procid INTEGER)
    RETURNING CHAR(256) AS spl_name, INTEGER AS sqlcode, INTEGER AS isamcode, CHAR(80) AS errval;

-- start of SPL name
DEFINE l_spl_name_start INTEGER;
-- end of SPL name (actually, start of opening brace)
DEFINE l_spl_name_end INTEGER;
-- start of period inside SPL name (such as userid.splname)
DEFINE l_period_pos INTEGER;
-- text of the SPL that we need to search
DEFINE l_spl_text CHAR(32000);
DEFINE l_spl_text_row CHAR(256);
-- name of the SPL after removing prefix userid and period
DEFINE o_spl_name CHAR(256);

DEFINE GLOBAL g_sqlcode     INTEGER DEFAULT 0;
DEFINE GLOBAL g_isamcode    INTEGER DEFAULT 0;
DEFINE GLOBAL g_errval      CHAR(80) DEFAULT "";

ON EXCEPTION SET g_sqlcode, g_isamcode, g_errval
RETURN NULL, g_sqlcode, g_isamcode, g_errval;
END EXCEPTION;

BEGIN
  LET l_spl_text = TRIM("");
  EXECUTE PROCEDURE sel_spl_text(i_procid) INTO l_spl_text;
  LET l_spl_text = LOWER(l_spl_text);
  LET l_spl_name_start = instr(l_spl_text, "create procedure");
  IF l_spl_name_start = 0 THEN
    LET l_spl_name_start = instr(l_spl_text, "create function");
    -- need to skip "create function"
    IF l_spl_name_start > 0 THEN
      LET l_spl_name_start = l_spl_name_start + LENGTH("create function");
    END IF
  ELSE
    -- need to skip "create procedure"
    LET l_spl_name_start = l_spl_name_start + LENGTH("create procedure");
  END IF
   -- find the opening brace to isolate name
  LET l_spl_name_end = instr(SUBSTR(l_spl_text, l_spl_name_start), "(") - 1;
  -- there could be an informix. or userid. before the name.  If so, find it
  -- and skip it.
  LET l_period_pos = instr(substr(l_spl_text, l_spl_name_start, l_spl_name_end), ".");
  IF l_period_pos > 0 THEN
    LET l_spl_name_start = l_spl_name_start + l_period_pos;
    LET l_spl_name_end = instr(substr(l_spl_text, l_spl_name_start), "(") - 1;
  END IF
  -- extract the SPL name, and remove newlines
  LET o_spl_name = REPLACE(SUBSTR(l_spl_text, l_spl_name_start, l_spl_name_end), CHR(10), '');
  LET o_spl_name = TRIM(REPLACE(o_spl_name, CHR(11), ''));
  LET o_spl_name = TRIM(REPLACE(o_spl_name, CHR(11), ''));
  RETURN o_spl_name, NULL, NULL, NULL;
END;
END PROCEDURE;

DROP FUNCTION sel_spl_text(integer);

-- This procedure extracts the SPL's text based on the procid, and concencates them all together into a single
-- string.  Unfortunately, it assumes that no SPL will exceed 32700 characters (and any that does is so badly conceived
-- that it should be rewritten).
create procedure "ccramer".sel_spl_text(i_procid INTEGER) RETURNING CHAR(32700) AS spl_text;
DEFINE o_spl_text CHAR(32700);
DEFINE l_spl_text_row CHAR(256);
BEGIN
  LET o_spl_text = "";
  FOREACH seq_cursor FOR
    SELECT data INTO l_spl_text_row FROM sysprocbody spb
      where spb.datakey='T' and spb.procid = i_procid
    LET o_spl_text = TRIM(o_spl_text) || l_spl_text_row;
  END FOREACH
  RETURN o_spl_text;
END
END PROCEDURE;