IC88633: PROCEDURE CALLS REFERENCING A HOST VARIABLE FAILS TO REGENERATE A DROPPED TYPE

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

APAR status

  • Closed as program error.

Error description

  • After a type has been dropped, the subsequent call of a
    procedure that casts a host variable as the dropped type fails
    to regenerate the type.
    
    For example, in the following scenario, SQL901N instead of the
    proper SQL723N (SQL204N) may be returned.
    
    create table t1 (c1 int, c2 int);
    create type r1 as row (c1 int, c2 int);
    create type r2 as row (c1 int, c2 int);
    create type a2 as r1 array[10];
    
    create function foo() returns a2
    begin
     declare lv1 a2;--
     return lv1;--
    end;
    
    create or replace procedure poo()
    begin
     declare s1 varchar(200);--
     set s1 = 'delete from t1 where c1 = cardinality(cast (? as
    a2))';--
     prepare st from s1;--
     execute st using foo();--
    end;
    
    drop type r1;
    drop type r2;
    
    -- SQL901N
    call poo();
    
    Here is the error stack:
    ----------------------------------------------------------------
    ----------------
    --sqlnn_cmpl                [300]:rc(-2144272270) Error in
    parser
    --sqlnp_main                [250]:rc(-2144272270) Invoke LPG
    parser (semantic phase)
    --sqlnp_parser              [330]:rc(-2144272270) Call smactn
    
    --sqlnp_smactn              [100]:rc(-2144272270) Processing
    semantic action
    --sqlnq_sem_host            [245]:rc(-2144272270) convert host
    variable type to dat_TYPTYPE
    --sqlnq_convertHVtoDatTyp   [243]:rc(-2144272209) Could not
    fetch type packed descriptor.
    ----------------------------------------------------------------
    ----------------
    --SQL0901N  The SQL statement failed because of a non-severe
    system error.
    --Subsequent SQL statements can be processed.  (Reason
    "sqlnq_convertHVtoDatTyp
    --[243]:rc(-2144272209) Could not fetch".)  SQLSTATE=58004
    

Local fix

  • Calling a procedure that depends on a type that has been dropped
    will result in an error SQL723N (SQL204N) anyway.  Users should
    either drop the procedure or recreate the missing type.
    
    If the type is recreated, before calling the user procedure,
    call the following admin procedure to manually regenerate the
    type:
        call admin_revalidate_db_objects('type', NULL, NULL);
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update to DB2 Version 10.1 Fix Pack 3 or later.              *
    ****************************************************************
    

Problem conclusion

  • The problem was first fixed in DB2 Version 10.1 Fix Pack 3.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC88633

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-11-28

  • Closed date

    2013-10-21

  • Last modified date

    2013-10-21

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

    IC86073

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSN

       UP



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

10.1

Reference #:

IC88633

Modified date:

2013-10-21

Translate my page

Machine Translation

Content navigation