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 Triggers - cant put NOT NULL constraint

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

New User


Joined: 06 May 2008
Posts: 33
Location: Chennai

PostPosted: Thu Apr 30, 2009 12:08 am    Post subject: DB2 Triggers - cant put NOT NULL constraint
Reply with quote

HI,

I need help in DB2 triggers:

Issue:
Table A has many fields out of which 2 fields are getting inserted with NULL values.
I have to stop insertion of NULL values on these 2 fields.

I cant put NOT NULL constraint as it will result in Job abends and online application will throw error.

I am trying using Triggers.

One possible solution is to let table update the fields with NULL values and then let the trigger delete them afterwards ( using After Trigger) ie delete the complete row.

But I am looking at a possibility of using Before trigger.
Let the trigger check if the field have NULL values(to be inserted) and if yes bypass the insert . But is it possible ? This all so that the jobs do not abend .

Kindly advise
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10278
Location: italy

PostPosted: Thu Apr 30, 2009 12:13 am    Post subject: Reply to: DB2 Triggers
Reply with quote

the requirement looks like a major database/application change
so the approach should be to fix the programs

going with Your approach You might face issues about data consistency

for example
the end user enters a transaction which updates the table in question
if the transaction is successful he/she might get a message that the insert
was done and maybe produce an hardcopy of the event

but then a triggered event clears up the update icon_eek.gif icon_eek.gif icon_eek.gif
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: Thu Apr 30, 2009 1:12 am    Post subject:
Reply with quote

Hello,

Quote:
I cant put NOT NULL constraint as it will result in Job abends and online application will throw error.
It should not cause any problems if the code is properly implemented. . .

As inconvenient as it may be, it might be a good idea to re-consider the table/column definitions.

IMHO, this approach may cause long-term support "opportunities". To me is is somewhat scary to have null be valid only some of the time. . . If i write code that encounters a null, how do i know if this null is valid or an error? Again, only MHO. . .
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Apr 30, 2009 1:23 am    Post subject:
Reply with quote

Quote:
Issue:
Table A has many fields out of which 2 fields are getting inserted with NULL values.
I have to stop insertion of NULL values on these 2 fields.


Just what NULL values are you inserting in these 2 field? Are theses 2 columns defined as nullable?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Apr 30, 2009 1:27 am    Post subject:
Reply with quote

I recently came off a site where they had the same problem.
they figured that there are not that many programs
that are doing a row insert -
so fix all those programs. Still had NULLs being INSERTed.

so I put a trigger, with a SIGNAL and an error message.
Found them,
required some quick compiles/binds/links/promotion to production,
but that was the lesser of two evils
  • an hour or so down time
  • crap in the database that made balancing the books impossible


the jerkoff (manager) that allowed the garbage code to go to production,
had to stand-up, admit, and then require the trigger to be implemented.

actually, the only hard part was making the manager fess-up.
that's the problem that you have -
forcing management to admit to the problem
fix the problem
and probably endure some embarrassment.
Back to top
View user's profile Send private message
ashishsr123

New User


Joined: 06 May 2008
Posts: 33
Location: Chennai

PostPosted: Thu Apr 30, 2009 1:39 am    Post subject:
Reply with quote

Quote:
Just what NULL values are you inserting in these 2 field? Are theses 2 columns defined as nullable?


Yes, these columns can have NULL value( guess this answers you?).

Thanks everyone for the quick response.
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: Thu Apr 30, 2009 1:47 am    Post subject:
Reply with quote

Hello,

Quote:
Yes, these columns can have NULL value( guess this answers you?).
There is no such thing as a NULL value. . . The NULL indicator might be set, but there is no actual value that represents NULL. All of the values from x'00' thru x'FF' are valid values and none of them is NULL.
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 Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


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