Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230

    Unanswered: have any tools or utilities that show me the text of procedure or funtion or triggers

    I am using db2 v8.2.2 and aix 5.2

    I used db2 syscat.routine and select BODY columns but did not show me anything and it was empty, please help

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Select text from syscat.routines should give you the text for SQL routines.
    If theay are not SQL routines, there is no way to query the DB to get this that I know of.

    Andy

  3. #3
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    Quote Originally Posted by ARWinner
    Select text from syscat.routines should give you the text for SQL routines.
    If theay are not SQL routines, there is no way to query the DB to get this that I know of.

    Andy
    As you know TEXT is clob column and I know I have UDF like:

    create function xxxxxx(4000) for bit data)
    returns varchar(4000)
    specific cbchr
    external name 'yyyyyy!bbbb'
    language C
    parameter style DB2SQL
    not variant
    fenced
    not null call
    no sql
    no external action
    no scratchpad
    no final call;


    I expect to see this body, how can I see that?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Here is an example using the CLP:

    LCAD-->select text from syscat.routines where routinename = 'UDF_NAME'

    TEXT








    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    CREATE FUNCTION PRODUCTION.UDF_NAME(LAST VARCHAR(25), FIRST VARCHAR(20), MIDDLE VARCHAR(15), SUFFIX VARCHAR(10))
    RETURNS VARCHAR(75) LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION CALLED ON NULL INPUT
    BEGIN ATOMIC
    DECLARE OUTTEXT VARCHAR(75);

    SET OUTTEXT = '';

    IF (LAST IS NOT NULL)
    THEN SET OUTTEXT = RTRIM(LAST) || ', ';
    END IF;

    IF (FIRST IS NOT NULL)
    THEN SET OUTTEXT = OUTTEXT || RTRIM(FIRST) || ' ';
    END IF;

    IF (MIDDLE IS NOT NULL)
    THEN SET OUTTEXT = OUTTEXT || RTRIM(MIDDLE) || ' ';
    END IF;

    IF (SUFFIX IS NOT NULL)
    THEN SET OUTTEXT = OUTTEXT || RTRIM(SUFFIX);
    END IF;

    RETURN OUTTEXT;
    END








    1 record(s) selected.

    LCAD-->

    Andy

  5. #5
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    Quote Originally Posted by ARWinner
    Here is an example using the CLP:

    LCAD-->select text from syscat.routines where routinename = 'UDF_NAME'

    TEXT








    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------
    CREATE FUNCTION PRODUCTION.UDF_NAME(LAST VARCHAR(25), FIRST VARCHAR(20), MIDDLE VARCHAR(15), SUFFIX VARCHAR(10))
    RETURNS VARCHAR(75) LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION CALLED ON NULL INPUT
    BEGIN ATOMIC
    DECLARE OUTTEXT VARCHAR(75);

    SET OUTTEXT = '';

    IF (LAST IS NOT NULL)
    THEN SET OUTTEXT = RTRIM(LAST) || ', ';
    END IF;

    IF (FIRST IS NOT NULL)
    THEN SET OUTTEXT = OUTTEXT || RTRIM(FIRST) || ' ';
    END IF;

    IF (MIDDLE IS NOT NULL)
    THEN SET OUTTEXT = OUTTEXT || RTRIM(MIDDLE) || ' ';
    END IF;

    IF (SUFFIX IS NOT NULL)
    THEN SET OUTTEXT = OUTTEXT || RTRIM(SUFFIX);
    END IF;

    RETURN OUTTEXT;
    END








    1 record(s) selected.

    LCAD-->

    Andy
    it does not show me the body of my UFD because the language is c but yours is SQL, am I right ? is there any way I can see the bodies of my UDF or SP.

    Thank you so much for your help.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    As I stated in my first reply, you cannot get the text of non-SQL routines from syscat. You maybe able to derive the "CREATE FUNCTION" statement from syscat.routines.

    Andy

  7. #7
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    Quote Originally Posted by ARWinner
    As I stated in my first reply, you cannot get the text of non-SQL routines from syscat. You maybe able to derive the "CREATE FUNCTION" statement from syscat.routines.

    Andy
    Thank you so much Andy for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •