#DB2 #performance #optimization #sql #sql/pl

Experiences with DB2
DB2, SQL tuning, database optimization and occasionally other database related information that interests me

Recent comments

  • May 11, 2012 8:36 am

    Oracle : Foreign key and validation

    Illustrating how the foreign key could be disabled and using the NOVALIDATE - the rows in the child table could be preserved even after enabling the foreign key. The newer rows will be checked for presence in the ‘parent’ table.

    SQL> create table test1(a number(8,2) primary key)
      2  /
                                                                               
    Table created.

    SQL> create table test2(a number(8,2) primary key, constraint fkey1 foreign key(a) references test1(a))
      2  /

    Table created.

    SQL> insert into test1 values(1)
      2  /

    1 row created.

    SQL> c/1/2
      1* insert into test2 values(1)
    SQL> /

    1 row created.


    SQL>insert into test1 values(2)
    SQL> /

    1 row created.

    SQL> c/(2)/(3)
      1* insert into test1 values(3)
    SQL> /

    1 row created.

    SQL> c/1/2
      1* insert into test2 values(3)
    SQL> /

    1 row created.

    SQL> alter table test2 disable constraint fkey1
    SQL> /

    Table altered.

    SQL> truncate table test1 
      2  /

    Table truncated.

    SQL> alter table test2 enable novalidate constraint fkey1
    SQL> /

    Table altered.

    SQL> insert into test2 values(2)
      2  /
    insert into test2 values(2)
    *
    ERROR at line 1:
    ORA-02291: integrity constraint (SYS.FKEY1) violated - parent key not found


  • September 16, 2009 9:45 am

    First post

    This new blogging community seems to be interesting, going with my ideology - sticking to just the point. I am planning to write about my everyday work life involving databases and programming involving the same.

    DB2 is my favourite database and so the examples will be targeted to DB2 developers majorly, but the posts should be meaningful to you regardless of what database you use, provided you are a DBA.

    To kick things off - I’ve always wanted db2 to have wider array of scalar functions.

    Some like first day of month, last day of month are missing, but the most glaring one that many programmers use is the

    max(val1,val2) = (val1 > val2, val1, val2),

    and that is missing in UDB version 9.5 as of now. Not very sure about the latest 9.7 that boasts to save the world and solve the hunger problem though.

    So I created one myself, and am heavily using it in my code so does other developers.

    create function DB2ADMIN.MAXTWO(x date, y date)

    returns date

    begin atomic

    if y is null or x >= y then return x;

    else return y;

    end if;

    end;

    Overload this for handling other datatypes and use them with descretion.

Apture