MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

DB2 unload , reformat and load

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
SRI123
Beginner


Joined: 01 Jun 2012
Posts: 18
Topics: 7

PostPosted: Fri Aug 28, 2015 4:42 am    Post subject: DB2 unload , reformat and load Reply with quote

Hi
Employee table contains - EMP-NO Integer, DOJ DATE, DATE-REL DATE, SAL DECIMAL(9,2)

I want to unload the data from the above table and reformat the data and load the data to another table.

Want to reformat the data such that new field EXP INT has to be calculated ( difference of DOJ and current date. And new field BONUS DECIMAL(9,2) has to be added based on years of EXP field. IF EXP < 2 THEN 100000 otherwise 200000
Reformatted data is like this: EMPNO, DOJ, SAL,BONUS
Load reformatted data into another table with fields:
EMPNO INTEGER, DOJ DATE, SAL DECIMAL(9,2),BONUS DECIMAL(9,2)

Question is what is the unload jcl and load jcl used so that data can be modified and loaded back ?

Thanks
Back to top
View user's profile Send private message
Nic Clouston
Advanced


Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home

PostPosted: Fri Aug 28, 2015 5:50 am    Post subject: Reply with quote

Why unload? Why not INSERT into new table values (SELECT from old table)?
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.
Back to top
View user's profile Send private message
SRI123
Beginner


Joined: 01 Jun 2012
Posts: 18
Topics: 7

PostPosted: Fri Aug 28, 2015 6:24 am    Post subject: Reply with quote

Since data needs to be reformatted;
Back to top
View user's profile Send private message
CZerfas
Intermediate


Joined: 31 Jan 2003
Posts: 211
Topics: 8

PostPosted: Fri Aug 28, 2015 6:25 am    Post subject: Reply with quote

If there are too many rows to be inserted, you could "unload" your source table with a SELECT making all the needed reformats running under DSNTIAUL and, with a propper load statement, load the stuff into the target table

regards
Christian
Back to top
View user's profile Send private message
Nic Clouston
Advanced


Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home

PostPosted: Fri Aug 28, 2015 6:38 am    Post subject: Reply with quote

Do the reformatting in the SELECT eg (not real code but shows you the concept)
Code:

SELECT cola, cola+somevalue as colz, colb FROM old table

will select 2 columns (cola and colb) from the table and create a new column (colz) based on the value of cola. You can use the CASE statement to apply a varying value as for your EXP column.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.
Back to top
View user's profile Send private message
SRI123
Beginner


Joined: 01 Jun 2012
Posts: 18
Topics: 7

PostPosted: Fri Aug 28, 2015 7:08 am    Post subject: Reply with quote

Ok thats good idea.thanks

Instead of reformatting while unloading, want to do it while loading.

So how to keep the condition in load statement. And do I need to change the data format from FILE S9(9) COMP to Integer of table field ? If yes, how to change?
Back to top
View user's profile Send private message
Nic Clouston
Advanced


Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home

PostPosted: Fri Aug 28, 2015 9:11 am    Post subject: Reply with quote

Why are you insisting on doing an unload? You can insert into table b the row that you have just selected from table a along with the derived columns. All one statement. One oass of the data. Have you started trying things out with SPUFI?
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.
Back to top
View user's profile Send private message
shekar123
Advanced


Joined: 22 Jul 2005
Posts: 525
Topics: 89
Location: Bangalore India

PostPosted: Fri Aug 28, 2015 9:33 am    Post subject: Reply with quote

Please post your columns of the new table to which you want the load of the data to happen.
_________________
Shekar
Grow Technically
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Fri Aug 28, 2015 10:08 am    Post subject: Re: DB2 unload , reformat and load Reply with quote

SRI123 wrote:
Question is what is the unload jcl and load jcl used so that data can be modified and loaded back ?

Thanks


Sri123,

Are these your home work class exercises? If so please state them. Secondly what exactly did you try so far? Show us what you have tried and then may be we can suggest an improvement or guide you in the right direction.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group