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
 

 

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: 1825
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 Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm


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