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
 

 

Update all records on file1 using single record on file2 ?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
rmd3003

New User


Joined: 03 Jul 2006
Posts: 53

PostPosted: Tue Oct 05, 2010 8:25 pm    Post subject: Update all records on file1 using single record on file2 ?
Reply with quote

I need to update all records on file1 using single record on file2. No matching is needed - just sequential read all records on file1 and update every record with info from file2.

FILE1
Code:
----+----1----+
0000000000AAAAA
0000000000BBBBB
0000000000CCCCC
0000000000DDDDD


FILE2
Code:
----+----1----+-
XXXXX           


OUTPUT
Code:
----+----1----+
0000000000XXXXX
0000000000XXXXX
0000000000XXXXX
0000000000XXXXX
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2165
Location: @my desk

PostPosted: Tue Oct 05, 2010 9:01 pm    Post subject:
Reply with quote

rmd3003,

If you can pass the file2 data via a SYMNAMES dataset of FB,LRECL=80, you can try this.
Code:
//STEP1  EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN   DD DSN= Input file
//SORTOUT  DD DSN= Output file
//SYMNAMES DD *
DATA1,'XXXXX'
//SYSIN    DD *
  INREC OVERLAY=(11:DATA1)
  SORT FIELDS=COPY
Back to top
View user's profile Send private message
rmd3003

New User


Joined: 03 Jul 2006
Posts: 53

PostPosted: Tue Oct 05, 2010 9:08 pm    Post subject: Reply to: Update all records on file1 using single record on
Reply with quote

No, I'd rather use regular sort. I have an idea - use ICETOOL but still can't make it to work. icon_cry.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: Tue Oct 05, 2010 9:21 pm    Post subject:
Reply with quote

Hello,

The posted solution IS the regular sort. . .

Quote:
No, I'd rather use regular sort.
Why?

Why would using ICETOOL be "better" icon_confused.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Oct 05, 2010 9:34 pm    Post subject:
Reply with quote

actually, Arun needs to modify his post, and create a first step which creates the symname for the second step.
that would automate the process.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Tue Oct 05, 2010 10:13 pm    Post subject:
Reply with quote

rmd3003,
What is the LRECL and RECFM of both input files and the expected output?

Thanks,
Back to top
View user's profile Send private message
rmd3003

New User


Joined: 03 Jul 2006
Posts: 53

PostPosted: Tue Oct 05, 2010 10:29 pm    Post subject:
Reply with quote

sqlcode1 wrote:
rmd3003,
What is the LRECL and RECFM of both input files and the expected output?

Thanks,


Samples I posted here are just to show what I'm trying to do. Let's say file1 is 100 bytes and file2 is 80 bytes. File2 will always have one record but what's in pos 1:5 will change from run to run. File1 will have multiple records.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Oct 05, 2010 10:47 pm    Post subject:
Reply with quote

what positions in file1 are to be overlayed with the data in 1 thru 5 of file2.
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: Tue Oct 05, 2010 10:48 pm    Post subject: Reply to: Update all records on file1 using single record on
Reply with quote

To repeat:

Quote:
Hello,

The posted solution IS the regular sort. . .

Quote:
No, I'd rather use regular sort.
Why?

Why would using ICETOOL be "better" icon_confused.gif


d
Back to top
View user's profile Send private message
rmd3003

New User


Joined: 03 Jul 2006
Posts: 53

PostPosted: Tue Oct 05, 2010 11:04 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
what positions in file1 are to be overlayed with the data in 1 thru 5 of file2.


11:15
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Oct 05, 2010 11:25 pm    Post subject:
Reply with quote

in this post, by frank yeager:
http://ibmmainframes.com/viewtopic.php?p=191294&highlight=sysnames#191294
he creates a sysnames dataset from the input.
in your case you only need to build 1 record that looks like the instream data of ARUN post.
the record you would build would be:
DATA1,'XXXXX'
where DATA1 would be a constant and the XXXXX would be the value in position 1-5 of your file2, which would be the input to this first step.

as frank's example shows, generate a temporary dataset as SORTOUT
and again reference this dataset as the
//SYMNAMES DD DSN=&&S1,DISP=(OLD,PASS)
for the second sort step, which would be ARUN's example.


so for your first sort step,
you only need to develop the sort cards to build the
DATA1,' in positions 1 thru 7
in positions 8 thru 12 extract positions 1 thru 5 of file2 (SORTIN of 1st sort step)
' in position 13.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Oct 05, 2010 11:30 pm    Post subject:
Reply with quote

Here's a DFSORT job that will do what you asked for. (I have no idea what you mean by "use regular sort".)

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=... input file2 (FB/80)
//SORTOUT DD DSN=&&S1,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//SYSIN DD *
  OPTION COPY
  INREC BUILD=(C'TARG,''',1,5,C'''',80:X)
/*
//S2 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SYMNAMES DD DSN=&&S1,DISP=(OLD,PASS)
//SORTIN DD DSN=... input file1 (FB/100)
//SORTOUT DD DSN=...  output file (FB/100)
//SYSIN DD *
  OPTION COPY
  INREC OVERLAY=(11:TARG)
/*
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Oct 05, 2010 11:57 pm    Post subject:
Reply with quote

Frank Yaeger wrote:
(I have no idea what you mean by "use regular sort".)

Probably either American or Swiss icon_razz.gif

(I have a friend who used to work in a delicatessen. He was once asked for a cheese sandwich. When he asked the customer what kind of cheese he wanted, he was told, "the regular sort".)
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Oct 06, 2010 12:48 am    Post subject:
Reply with quote

Cute.

Quote:
Dinosaurs dominated the Earth's ecosystem for one hundred million years. During this time, the largest mammal was the size of, and looked very much like, a rat.


Rats rule!!! icon_biggrin.gif

Frank, who is owned by pet rats Nora, Sara, Wendy and Alice.
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am


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