Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 9 z/OS trigger question

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
David Joyce

New User


Joined: 08 Aug 2008
Posts: 17
Location: Boulder Colorado

PostPosted: Thu Aug 25, 2011 11:24 pm    Post subject: DB2 9 z/OS trigger question
Reply with quote

The trigger I am attempting to write checks a WHEN condition, if true I want to perform a SELECT statement and then interrogate the SQLSTATE of the SELECT statement performing either an INSERT or UPDATE based upon the SQLSTATE. Here is error I receive when I try to create the trigger in SPUFI:
Code:
DSNT408I SQLCODE = -20100, ERROR:  AN ERROR OCCURRED WHEN BINDING A TRIGGERED
         SQL STATEMENT.  INFORMATION RETURNED: SECTION NUMBER : 3 SQLCODE     
         -104, SQLSTATE 42601, AND MESSAGE TOKENS CMSDBT01,ELSE CONCAT || /   
         MICROSECONDS MICROSECOND SECONDS SECOND 


Here is the trigger DDL:
Code:
--#SET TERMINATOR ?                                   
  SET CURRENT SQLID = 'DB2DBA'?                       
  CREATE TRIGGER CMSDBT01.TESTTBL1_DELETE             
    AFTER DELETE ON CMSDBT01.TESTTBL1                 
  REFERENCING OLD AS O                               
  FOR EACH ROW                                       
  MODE DB2SQL                                         
  WHEN (O.COL3 > CURRENT TIMESTAMP - 1 MONTH)         
    BEGIN ATOMIC                                     
      SELECT 1                                       
        FROM CMSDBT01.TESTTBL2                       
      WHERE COL1 = O.COL1;                           
      VALUES CASE                                     
        WHEN SQLSTATE = '00000' THEN                 
          UPDATE CMSDBT01.TESTTBL2                   
            SET TRIGGER_TS = CURRENT TIMESTAMP       
          WHERE COL1 = O.COL1                         
        WHEN SQLSTATE = '02000' THEN                 
          INSERT INTO CMSDBT01.TESTTBL2               
           VALUES (O.COL1, CURRENT TIMESTAMP)         
        ELSE                                         
          SIGNAL SQLSTATE 'Z0001'                     
            SET MESSAGE_TEXT = 'PROBLEM WITH CMSTB467'
      END;                                           
    END?                     
Code'd

Any help is greatly appreciated. Thanks.
Back to top
View user's profile Send private message

David Joyce

New User


Joined: 08 Aug 2008
Posts: 17
Location: Boulder Colorado

PostPosted: Fri Aug 26, 2011 12:55 am    Post subject: Reply to: DB2 9 z/OS trigger question
Reply with quote

Could not figure out how to interrogate SQLSTATE from previous SQL SELECT statement so I changed the trigger to:

Code:
--#SET TERMINATOR ?                           
  SET CURRENT SQLID = 'DB2DBA'?               
--DROP TRIGGER CMSDBT01.TESTTBL1_DELETE;     
--COMMIT;                                     
  CREATE TRIGGER CMSDBT01.TESTTBL1_DELETE     
    AFTER DELETE ON CMSDBT01.TESTTBL1         
  REFERENCING OLD AS O                       
  FOR EACH ROW                               
  MODE DB2SQL                                 
  WHEN (O.COL3 > CURRENT TIMESTAMP - 1 MONTH)
    BEGIN ATOMIC                             
      DELETE                                 
        FROM CMSDBT01.TESTTBL2               
      WHERE COL1 = O.COL1;                   
      INSERT INTO CMSDBT01.TESTTBL2           
        VALUES (O.COL1, CURRENT TIMESTAMP);   
    END?                                     
--#SET TERMINATOR ;
Code'd
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Aug 26, 2011 11:57 am    Post subject:
Reply with quote

I think you can not do this in Values clause.... You can call a stored procedure from the trigger and there you can do all the checks.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Aug 26, 2011 7:49 pm    Post subject:
Reply with quote

Hello,

You need to use the Code tag when posting code, jcl, data, etc. Doing a copy/paste and applung the Code tag will preserve alignment and improve readability.

There is a Preview function so you can wee your post as it will appear to the forum. When the post appeara as you wish, Submit.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fault Analyzer - listings question. egrove IBM Tools 4 Thu Aug 11, 2016 5:31 pm
No new posts dataset copy question - REPRO or some... atulxp TSO/ISPF 2 Wed Aug 03, 2016 10:56 pm
No new posts SQL Order By related question Joseph K Thomas DB2 8 Fri Mar 18, 2016 12:53 pm
No new posts DFSort Question krrp DFSORT/ICETOOL 9 Mon Mar 07, 2016 7:11 pm
No new posts How to track incoming input files tha... shreya19 JCL & VSAM 6 Thu Feb 25, 2016 6:15 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us