IBM Support

IT00578: DB2LOOK WITH -T OPTION DOES NOT GENERATE A DEFINITION FOR A STORED PROCEDURE.

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • db2look with -t option generates all the dependent objects on
    the table specified, except for stored procedures.
    
    Take SAMPLE database for example, there are 1
    UDF,RESIGN_EMPLOYEE,  and 1 stored procedure,BONUS_INCREASE,
    created on EMPLOYEE table.
    
    When the following command is run,
    
    $ db2look -d SAMPLE -e -t EMPLOYEE
    
    it will only generate DDL for RESIGN_EMPLOYEE, not
    BONUS_INCREASE.
    
    CREATE FUNCTION resign_employee (number CHAR(6))
      RETURNS TABLE (empno  CHAR(6),
                     salary DOUBLE,
                     dept   CHAR(3))
      SPECIFIC resign_employee
      MODIFIES SQL DATA
      LANGUAGE SQL
      BEGIN ATOMIC
    --
    ----------------------------------------------------------------
    ---------------------
    -- Routine type:  SQL table function
    -- Routine name:  resign_employee
    --
    -- Purpose:  This procedure takes in an employee number, then
    removes that
    --           employee from the EMPLOYEE table.
    --           A useful extension to this function would be to
    archive the
    --           original record into an archive table.
    --
    --
    ----------------------------------------------------------------
    ----------------------
        DECLARE l_salary DOUBLE;--
        DECLARE l_job CHAR(3);--
    
        SET (l_salary, l_job) = (SELECT salary, job
                                   FROM OLD TABLE (DELETE FROM
    employee
                                                    WHERE
    employee.empno = number));--
    
        RETURN VALUES (number,l_salary, l_job);--
      END;
    
    SET CURRENT SCHEMA = "DB2INST1";
    SET CURRENT PATH =
    "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2INST1";
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All DB2 V9.7 Users                                           *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 V9.7 Fix Pack 10 or higher.                   *
    ****************************************************************
    

Problem conclusion

  • Fixed in DB2 V9.7 Fix Pack 10.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT00578

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-03-24

  • Closed date

    2015-02-17

  • Last modified date

    2015-02-17

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IT07061 IT07062

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IT00578

Modified date: 17 February 2015