Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    6

    Question Unanswered: trigger calling procedure with row anchor type

    Hi!

    I am trying to create a trigger that calls a stored procedure whose the input parameter is an anchor row type of the table the trigger is defined on.

    Something like this:


    Code:
    create table mytable (
      a decimal(12) not null,
      b decimal(12) null
    );
    
    create table mytable_history (
      a decimal(12) not null,
      b decimal(12) null
    );
    
    create or replace type proc_param as row anchor data type to row of mytable
    
    create or replace procedure upon_insert_procedure(in p proc_param)
    language sql
    begin
      insert into mytable_history(a, b) values (p.a, p.b); 
    end
    
    create or replace trigger mytrigger
    after insert on mytable
    referencing new as n for each row mode db2sql
    begin
    call upon_insert_procedure(n);
    end
    to which I get:



    Error report:
    DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=N, DRIVER=4.7.85

    which means that "N" cannot be used in this context.
    Although we all know that both the type of the variable "n" and the input parameter of the procedure is a type representing a row in 'mytable'. (I.e., what you would call mytable%rowtype in Oracle lingo.)



    Any ideas?
    (Apart from the procedure having to have as many parameters as there are columns in 'mytable' -- in the real life example I'm struggling with there's many more columns than just these two.)




    Thanks,
    Agoston

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Why are you trying to do the trigger work through a Stored Procedure? You can just move the logic into the trigger.

    Also what OS and DB2 version?

    Andy

  3. #3
    Join Date
    Sep 2009
    Posts
    6
    Quote Originally Posted by ARWinner View Post
    Why are you trying to do the trigger work through a Stored Procedure? You can just move the logic into the trigger.
    Also what OS and DB2 version?
    Andy

    Hi Andy,


    first the version information:

    OS:
    Code:
    ~> cat /etc/redhat-release 
    Red Hat Enterprise Linux Server release 6.6 (Santiago)

    DB2:
    Code:
    ~> db2 connect to MYDATABASE
       Database Connection Information
     Database server        = DB2/LINUXX8664 10.5.1
     [...]

    About your other question:
    I wish I could move the logic into the trigger! The whole thing started with db2 not being able to handle the amount of columns used in the trigger and showing the error below:

    Code:
    create or replace trigger mytrigger
    after insert on mytable
    referencing new as n for each row mode db2sql
    insert into mytable_history(column1, ..., column30) values (n.column1, ..., n.column30);
    ==>

    SQL1424N Too many references to transition variables and transition table
    columns or the row length for these references is too long. Reason code="2".
    LINE NUMBER=3. SQLSTATE=54040



    The only thing I've found about this problem is this:

    http://www.dbforums.com/showthread.p...tion-variables

    So OK, I've tried creating a system temporary tablespace as suggested:
    Code:
    create system temporary tablespace temp1 pagesize 16K managed by system using ( '/data/temp1' )
    ==>
    SQL1582N The PAGESIZE of the table space "TEMP1" does not match the PAGESIZE
    of the bufferpool "IBMDEFAULTBP" associated with the table space.
    SQLSTATE=428CB


    This is because the DB was created with an 8K pagesize. If I increase the page size of the DB (something that I would like to avoid having to do with our customer DB!), then I am able to create such a temporary tablespace, but then the DB doesn't have any trouble dealing with the trigger in the first place even without it.

    So it looks like if I'm to stick with the 8K page size for the DB, my only chance is calling a stored procedure in the trigger instead of executing the actual insert statement.


    If anyone knows a solution to this problem instead of the one with the stored procedure ==> just hit me!



    Thanks,
    Agoston

  4. #4
    Join Date
    Sep 2009
    Posts
    6
    Quote Originally Posted by ARWinner View Post
    Why are you trying to do the trigger work through a Stored Procedure? You can just move the logic into the trigger.
    Also what OS and DB2 version?
    Andy

    Hi Andy,


    first the version information:

    OS:
    Code:
    ~> cat /etc/redhat-release 
    Red Hat Enterprise Linux Server release 6.6 (Santiago)

    DB2:
    Code:
    ~> db2 connect to MYDATABASE
       Database Connection Information
     Database server        = DB2/LINUXX8664 10.5.1
     [...]

    About your other question:
    I wish I could move the logic into the trigger! The whole thing started with db2 not being able to handle the amount of columns used in the trigger and showing the error below:

    Code:
    create or replace trigger mytrigger
    after insert on mytable
    referencing new as n for each row mode db2sql
    insert into mytable_history(column1, ..., column30) values (n.column1, ..., n.column30);
    ==>


    SQL1424N Too many references to transition variables and transition table
    columns or the row length for these references is too long. Reason code="2".
    LINE NUMBER=3. SQLSTATE=54040



    The only thing I've found about this problem is this:

    http://www.dbforums.com/showthread.p...tion-variables

    So OK, I've tried creating a system temporary tablespace as suggested:
    Code:
    create system temporary tablespace temp1 pagesize 16K managed by system using ( '/data/temp1' )
    ==>

    SQL1582N The PAGESIZE of the table space "TEMP1" does not match the PAGESIZE
    of the bufferpool "IBMDEFAULTBP" associated with the table space.
    SQLSTATE=428CB


    This is because the DB was created with an 8K pagesize. If I increase the page size of the DB (something that I would like to avoid having to do with our customer DB!), then I am able to create such a temporary tablespace, but then the DB doesn't have any trouble dealing with the trigger in the first place even without it.

    So it looks like if I'm to stick with the 8K page size for the DB, my only chance is calling a stored procedure in the trigger instead of executing the actual insert statement.




    Thanks,
    Agoston

  5. #5
    Join Date
    Sep 2009
    Posts
    6
    Quote Originally Posted by ARWinner View Post
    Why are you trying to do the trigger work through a Stored Procedure? You can just move the logic into the trigger.

    Also what OS and DB2 version?

    Andy




    Hi Andy,





    first the version information:



    OS:

    Code:
    ~> cat /etc/redhat-release 
    
    Red Hat Enterprise Linux Server release 6.6 (Santiago)




    DB2:

    Code:
    ~> db2 connect to MYDATABASE
    
       Database Connection Information
    
     Database server        = DB2/LINUXX8664 10.5.1
    
     [...]




    About your other question:

    I wish I could move the logic into the trigger! The whole thing started with db2 not being able to handle the amount of columns used in the trigger and showing the error below:



    Code:
    create or replace trigger mytrigger
    
    after insert on mytable
    
    referencing new as n for each row mode db2sql
    
    insert into mytable_history(column1, ..., column30) values (n.column1, ..., n.column30);


    ==>





    SQL1424N Too many references to transition variables and transition table

    columns or the row length for these references is too long. Reason code="2".

    LINE NUMBER=3. SQLSTATE=54040







    The only thing I've found about this problem is this:



    http://www.dbforums.com/showthread.p...tion-variables



    So OK, I've tried creating a system temporary tablespace as suggested:

    Code:
    create system temporary tablespace temp1 pagesize 16K managed by system using ( '/data/temp1' )


    ==>



    SQL1582N The PAGESIZE of the table space "TEMP1" does not match the PAGESIZE

    of the bufferpool "IBMDEFAULTBP" associated with the table space.

    SQLSTATE=428CB





    This is because the DB was created with an 8K pagesize. If I increase the page size of the DB (something that I would like to avoid having to do with our customer DB!), then I am able to create such a temporary tablespace, but then the DB doesn't have any trouble dealing with the trigger in the first place even without it.



    So it looks like if I'm to stick with the 8K page size for the DB, my only chance is calling a stored procedure in the trigger instead of executing the actual insert statement.





    If anyone knows a solution to this problem instead of the one with the stored procedure ==> just hit me!







    Thanks,

    Agoston

    Comment

Tags for this Thread

Posting Permissions

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