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
 

 

Setting up a new trigger

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

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Thu Dec 24, 2015 10:33 pm    Post subject: Setting up a new trigger
Reply with quote

Hi, I am attempting to setup a new trigger and been hitting some issues. Any help/suggestion on resolving the same would be greatly appreciated.

Code:
CREATE TRIGGER CTRC_FNCL_TACN_EXT_INS       
  AFTER INSERT ON SYSXDB2.CTRC_FNCL_TACN_EXT 
  REFERENCING NEW AS NEWREC                   
  FOR EACH ROW MODE DB2SQL                   
  WHEN(NEWREC.TLC_PROD_CD = 'MNT'             
  AND EXISTS(SELECT PMT_TYP_CD, CLM_PD_AMT   
  FROM SYSXDB2.TLC_PMT_T                     
  WHERE CTRCT_NBR = NEWREC.CTRCT_NBR         
  AND (PMT_TYP_CD = 'C'                       
  OR PMT_TYP_CD = 'P'                         
  OR PMT_TYP_CD = 'S')                       
  AND TLC_PROD_CD = NEWREC.TLC_PROD_CD))     
  UPDATE SYSXDB2.CTRC_FNCL_TACN_EXT                                           
  SET NEWREC.TOT_CLMS_PD_AMT=SUM(CLM_PD_AMT)                                 
  WHERE NEWREC.CTRCT_NBR = CTRCT_NBR                                         
  AND NEWREC.TLC_PROD_CD = TLC_PROD_CD;                                     
---------+---------+---------+---------+---------+---------+---------+--------
DSNT408I SQLCODE = -20100, ERROR:  AN ERROR OCCURRED WHEN BINDING A TRIGGERED
         SQL STATEMENT.  INFORMATION RETURNED: SECTION NUMBER : 2 SQLCODE   
         -206, SQLSTATE 42703, AND MESSAGE TOKENS NEWREC.TOT_CLMS_PD_AMT     
DSNT418I SQLSTATE   = 56059 SQLSTATE RETURN CODE 
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Thu Dec 24, 2015 10:46 pm    Post subject:
Reply with quote

There is no such column TOT_CLMS_PD_AMT. It should be NEWREC.CLMS_PD_AMT
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Mon Dec 28, 2015 8:41 pm    Post subject:
Reply with quote

Hi Rohit, thanks for the response. I did first suspect that the column as the issue. However, the column is available in the table. below is the screen shot:

Code:
DB2 Admin -- TDB2 Columns in Table SYSXDB2.CTRC_FNC >  ---- Row 82 to 91 of 91
Command ===>                                                  Scroll ===> CSR 
                                                                               
Line commands:                                                                 
 T - Tables  X - Indexes  A - Auth  GR - Grant  H - Homonyms  I - Interpret   
 UR - Update runstats  LAB - Label  COM - Comment  DI - Distribution stats     
 ? - Show all line commands                                                   
                                                                               
Select Column Name        Col No Col Type Length Scale  Null Def FP    Col Card
       *                       * *             *      * *    *   *            *
------ ------------------ ------ -------- ------ ------ ---- --- -- -----------
       VEH_STTG_MLG_NB        82 DECIMAL       6      0 Y    Y   N           -1
       CNCN_MLG_NB            83 DECIMAL       6      0 Y    Y   N           -1
       RNSM_ADJM_TYPE_CD      84 CHAR          1      0 Y    Y   N           -1
       SLS_INVL_CD            85 CHAR          1      0 Y    Y   N           -1
       DLR_TYPE_CD            86 CHAR          1      0 Y    Y   N           -1
       ZCBK_FLG               87 CHAR          1      0 N    Y   N           -1
       JMA_INCV               88 DECIMAL       4      0 N    Y   N           -1
       NEW_PRC_IN             89 CHAR          1      0 N    Y   N           -1
       LOCN_CD                90 CHAR          4      0 Y    Y   N           -1
       TOT_CLMS_PD_AMT        91 DECIMAL       9      2 N    Y   N           -1
******************************* END OF DB2 DATA *******************************
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Dec 28, 2015 10:04 pm    Post subject:
Reply with quote

I don't think you can do SET NEWREC.TOT_CLMS_PD_ANT because the table you update has no column NEWREC.

Code:
UPDATE MyTable
SET some_other_table.MyColumn = MyColumn
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Tue Dec 29, 2015 3:23 am    Post subject:
Reply with quote

Guyc, thanks for input. Based on your last input, I modified it to
Code:
CTRC_FNCL_TACN_EXT.TOT_CLMS_PD_AMT
and it worked. Thanks All!
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 XFACILIT class setting for Fault Anal... egrove IBM Tools 2 Thu Aug 11, 2016 8:02 pm
No new posts How to track incoming input files tha... shreya19 JCL & VSAM 6 Thu Feb 25, 2016 6:15 pm
No new posts Issue in trigger vickey_dw DB2 3 Wed Feb 10, 2016 5:41 pm
No new posts CALL MACRO EXECUTE FORM - Setting the... sudarshan.srivathsav PL/I & Assembler 7 Tue Feb 09, 2016 10:32 pm
No new posts Trigger a mainframe job by mail Praveen Thenraj IBM Tools 8 Wed Jan 13, 2016 12:05 pm


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