Usage of TYPE in DB2

[Neena Paul]

Hi,

I ve a below structured object TYPE in Oracle to calculate the Simple and Compound interest for an amount..

CREATE TYPE pnr_typ AS OBJECT
   (
        principle NUMBER,
        interest NUMBER,
        year NUMBER,
        MEMBER FUNCTION si RETURN FLOAT,
        MEMBER FUNCTION ci RETURN FLOAT,
        MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER
   );
/
show err
 
CREATE TYPE BODY pnr_typ IS
 
      MEMBER FUNCTION si RETURN FLOAT IS
       BEGIN
           RETURN (principle*interest*SELF.year)/100;
       END;      MEMBER FUNCTION ci RETURN FLOAT IS
       BEGIN
           RETURN POWER(SELF.principle*(1 + SELF.interest/100), year);
       END;
 
 
      MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS
       BEGIN
           RETURN (year + invent);
       END;
      END;
/
Show err
 

With in the TYPE i define some attributes like principle, interest, year and more member functions like si, ci, prod to access and modify the TYPE attributes.

This TYPE can be used in

* the column definition while creating a column in a table,
* the procedures or Oracle DB objects as an instance

I would like to know if the TYPE (structured) in DB2 can do the same as the above Oracle TYPE.

Thanks,
Neena

[Neena Paul]

Hi ,

I ve successfully created the equivalent for the above Oracle TYPE object in DB2 with the below DB2 code snippet ..

CREATE TYPE s1.pnr_typ AS
       (
       principle INT,
       interest DECIMAL(5, 2),
       year INT
       )
     NOT FINAL
     MODE DB2SQL
       METHOD SI()
       RETURNS FLOAT
       LANGUAGE SQL
       DETERMINISTIC
       CONTAINS SQL
       NO EXTERNAL ACTION,
 
       METHOD CI()
       RETURNS FLOAT
       LANGUAGE SQL
       DETERMINISTIC
       CONTAINS SQL
       NO EXTERNAL ACTION,
 
       METHOD PROD(NUM INT)
       RETURNS INTEGER
       LANGUAGE SQL
       DETERMINISTIC
       CONTAINS SQL
       NO EXTERNAL ACTION
@
 
   CREATE METHOD SI()
     FOR s1.pnr_typ
     RETURN (SELF..principle*SELF..interest*SELF..year)/100
@
 
   CREATE METHOD CI()
     FOR s1.pnr_typ
     RETURN SELF..principle * POWER((1 + SELF..interest/100), SELF..year)
@
 
   CREATE METHOD PROD(NUM INT)
     FOR s1.pnr_typ
     RETURN  NUM+SELF..year
@

I do not have an idea how to use this TYPE in tables or SQL queries or Procedures, could any body help me with some simple eg. using this TYPE.

Thanks,
Neena

[Neena Paul]

Now I am able to create tables with the structured TYPE as a column , insertions and selections of the TYPE columns into and from the table and the method calls in a Select query is working fine now,

Could any one help me with the usage of structured TYPEs within a procedure or function...

I dont know how to initialise the TYPE in a function....

Any help on this would be highly appreciated..

Thanks
Sn

[Neena Paul]

With due respects to the experts here I wonder is this (TYPE)too complex ... i dint find any solution for my problem...

Sn :confused: