Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
To update the selected columns in the table

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

Active User


Joined: 18 Jun 2007
Posts: 213
Location: Cochin/Kerala/India

PostPosted: Tue Sep 10, 2013 8:08 pm    Post subject: To update the selected columns in the table
Reply with quote

Hi All,

I want to update the data in to table TAB1 from the input file.The column details as below,

C1 --> Key field
C2
C3
C4

Input data is IN-C1, IN-C2, IN-C3, IN-C4

But I have to update the columns in the table if I have valid data in the input, otherwise it should retain the data in the table.I got two options for this.

Option 1
SELECT C2,C3,C4
INTO :HC2,:HC3,:HC4
FROM TAB1
WHERE C1 = :IN-C1

IF IN-C2 <> LOW-VALUES
MOVE IN-C2 TO :HC2.

IF IN-C3 <> LOW-VALUES
MOVE IN-C3 TO :HC3.
IF IN-C4 <> LOW-VALUES
MOVE IN-C4 TO :HC4.

UPDATE TAB1
SET C2 = :HC2
C3 = :HC3
C4 = :HC4
WHERE C1 = :IN-C1

Option 2
UPDATE TAB1
SET C2 = CASE WHEN :IN-C2 = X'00' THEN C2
ELSE :IN-C2
END
SET C3 = CASE WHEN :IN-C3 = X'00' THEN C3
ELSE :IN-C3
END
SET C4 = CASE WHEN :IN-C4 = X'00' THEN C4
ELSE :IN-C4
END
WHERE C1 = :IN-C1

I want to know that which one is best option in peformance wise.Also kindly suggest that Is any other better options availabe to achieve this requirement.
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1894
Location: UK

PostPosted: Tue Sep 10, 2013 9:19 pm    Post subject:
Reply with quote

Actually you have one option:
validate input
If valid then update table
else issue error messages
get next input.
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: Wed Sep 11, 2013 1:40 am    Post subject:
Reply with quote

Hello,

If you have a high volume of data to be processed, suggest you validate the data in one process and bulk load the data into the table(s) en masse, not via individual inserts.
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us