f



Reg: Copy records from one file to another based on contents of a third file.

Hi,

Suppose I have a simple flat-file, say File-C which contains just one
field: Name of a person.

File-A contains many records one of which is Name. Now I wish to copy
all records from File-A to a new File-B only if the Name present in
the record of File-A is availble in the list of Names in File-C.

Can we use File Aid JCL (or Insync) to do this or any form of JCL to
achieve this..??

I wish to avoid writing a cobol program which can of course achieve
this.

Thanks...
0
mail_ssb (18)
8/29/2003 1:30:58 PM
comp.lang.cobol 4278 articles. 1 followers. Post Follow

6 Replies
783 Views

Similar Articles

[PageSpeed] 23

mail_ssb@yahoo.com (ssb) wrote:


>Suppose I have a simple flat-file, say File-C which contains just one
>field: Name of a person.
>
>File-A contains many records one of which is Name. Now I wish to copy
>all records from File-A to a new File-B only if the Name present in
>the record of File-A is availble in the list of Names in File-C.
>
>Can we use File Aid JCL (or Insync) to do this or any form of JCL to
>achieve this..??

You can do it easily with SQL. Load A and C into DB2 temp tables, extract the
matches (B) with a SELECT * FROM A WHERE A.Name IN (SELECT Name FROM C);
0
robert3028 (111)
8/29/2003 10:55:28 PM
"ssb" <mail_ssb@yahoo.com> wrote in message
news:de8e098d.0308290530.3c4665a0@posting.google.com...
> Hi,
>
> Suppose I have a simple flat-file, say File-C which contains just one
> field: Name of a person.
>
> File-A contains many records one of which is Name. Now I wish to copy
> all records from File-A to a new File-B only if the Name present in
> the record of File-A is availble in the list of Names in File-C.
>
> Can we use File Aid JCL (or Insync) to do this or any form of JCL to
> achieve this..??

Yes.

FD Work-File
   RecordType     X(01)
   Name           X(30)
  Original Record-A contents

Step one:
 Using file A as input, create work file-A using the OUTREC/OUTFIL option of
SyncSort/DFSORT
 Fill record type at position 1 with "Z".
Step two
 Using file B AS input, create workfile-B. Fill record type at position one
with "A" Or anything which will sort prior to "Z"). Fill original record
type with spaces, nulls or anything (you won't be using it)

Step three
Concatenate workfile-A and workfile-B as input. Sort on name+ RecordType
(2,30,A,1,1,A). This will get you a file like:
Aname   <filler>
Zname   Original Record
Aname   <filler>
Zname   Original Record
Aname   <filler>
Zname   Original Record

Step four
Sort output of step three by name only; specify preservation of original
order and direct duplicates to a file.  The duplicates will be all the type
"Z" records where a type "A" record exists. The duplicates file will look
like:
Zname   Original Record
Zname   Original Record
Zname   Original Record

Step five.
Using the duplicates file created in step 4 input, strip the sort key from
the record. Output will be original File-A records where the name existed in
file-c.

Detailed syntax left to OP as exercise.

If your original file-A contains duplicate names this method fails. Of
course, you did not specify this possibility in your summary of your
'challenge.'   But there are variations of this you could manufacture to
cover this case.

MCM





0
8/30/2003 12:11:12 PM
Hi Pete and Rob,

This is the most efficient way of resolving the
problem....Unfortunately, I need to avoid using any DB2 Tables... :-(

Best regards...




"Peter E.C. Dashwood" <dashwood@enternet.co.nz> wrote in message news:<3f4ffe9b$1_1@news.athenanews.com>...
> Snap!
> 
> That would be my solution too <G>.
> 
> Pete.
> 
> "Robert Wagner" <robert@wagner.net> wrote in message
> news:3f4fd917.505424931@news.central.cox.net...
> > mail_ssb@yahoo.com (ssb) wrote:
> >
> >
> > >Suppose I have a simple flat-file, say File-C which contains just one
> > >field: Name of a person.
> > >
> > >File-A contains many records one of which is Name. Now I wish to copy
> > >all records from File-A to a new File-B only if the Name present in
> > >the record of File-A is availble in the list of Names in File-C.
> > >
> > >Can we use File Aid JCL (or Insync) to do this or any form of JCL to
> > >achieve this..??
> >
> > You can do it easily with SQL. Load A and C into DB2 temp tables, extract
>  the
> > matches (B) with a SELECT * FROM A WHERE A.Name IN (SELECT Name FROM C);
0
mail_ssb (18)
8/31/2003 2:39:54 AM
"ssb" <mail_ssb@yahoo.com> wrote in message
news:de8e098d.0308301839.6273b2e4@posting.google.com...
> Hi Pete and Rob,
>
> This is the most efficient way of resolving the
> problem....Unfortunately, I need to avoid using any DB2 Tables... :-(
>
Because you don't have it? You can create them dynamically  - they don't
need to be predefined by DBAs.

TWO options.

Use DFSORT or FILE-AID to get distinct name from file A into TEMP1.  (there
is a FIRST instance operator)
Use DFSORT or FILE-AID to get distinct name from file B into TEMP2.
Use DFSORT or FILE-AID to get rows from  TEMP1 & TEMP2 where count > 2

If the last step doesn't have this operation (i think it does) then:
Use DFSORT or FILE-AID to get distinct name from file A into TEMP1 add a
value 1 in column x.  (there is a FIRST instance operator)
Use DFSORT or FILE-AID to get distinct name from file B into TEMP2 add a
value 1 in column x.
Use DFSORT or FILE-AID to get rows from  TEMP1 & TEMP2 adding values in
column x  into TEMP3
Use DFSORT or FILE-AID to get rows from  TEMP3 where value in column x > 1

If you go to the DFSORT site - they have many examples - the documentation
is pretty decent.

A program would probably be easier.

Use DFSORT to sort A into TEMP1.
Use DFSORT to sort B into TEMP2.

Write short program...read TEMP1 read TEMP2

PERFORM READ-TEMP1
PERFORM READ-TEMP2

PERFORM UNTIL EOF
IF TEMP1 <  TEMP2
    PERFORM READ-TEMP1
IF TEMP1 >  TEMP2
    PERFORM READ-TEMP2
IF TEMP1 =  TEMP2
    PERFORM WRITE-TEMP1-IN-BOTH
    PERFORM READ-TEMP1
END-PERFORM.

I wrote something similar once that did this one keeping those in FILE A and
not in B, in B and not in A and in Both....shouldn't take more than an hour.
I'd give you the source but I'd have to go find it on my old machine sitting
in parts in the corner.  If you have a hard time - post it here and get some
help :-)

JCE

> Best regards...
> "Peter E.C. Dashwood" <dashwood@enternet.co.nz> wrote in message
news:<3f4ffe9b$1_1@news.athenanews.com>...
> > Snap!
> >
> > That would be my solution too <G>.
> >
> > Pete.
> >
> > "Robert Wagner" <robert@wagner.net> wrote in message
> > news:3f4fd917.505424931@news.central.cox.net...
> > > mail_ssb@yahoo.com (ssb) wrote:
> > >
> > >
> > > >Suppose I have a simple flat-file, say File-C which contains just one
> > > >field: Name of a person.
> > > >
> > > >File-A contains many records one of which is Name. Now I wish to copy
> > > >all records from File-A to a new File-B only if the Name present in
> > > >the record of File-A is availble in the list of Names in File-C.
> > > >
> > > >Can we use File Aid JCL (or Insync) to do this or any form of JCL to
> > > >achieve this..??
> > >
> > > You can do it easily with SQL. Load A and C into DB2 temp tables,
extract
> >  the
> > > matches (B) with a SELECT * FROM A WHERE A.Name IN (SELECT Name FROM
C);
>


0
defaultuser (532)
9/1/2003 6:41:59 AM
mail_ssb@yahoo.com (ssb) wrote:

>This is the most efficient way of resolving the
>problem....Unfortunately, I need to avoid using any DB2 Tables... :-(

Why? EVERYone is authorized to create temp tables. They exist in memory, not on
disk, and disappear when the job ends. 

>"Peter E.C. Dashwood" <dashwood@enternet.co.nz> wrote in message
news:<3f4ffe9b$1_1@news.athenanews.com>...
>> Snap!
>> 
>> That would be my solution too <G>.
>> 
>> Pete.
>> 
>> "Robert Wagner" <robert@wagner.net> wrote in message
>> news:3f4fd917.505424931@news.central.cox.net...
>> > mail_ssb@yahoo.com (ssb) wrote:
>> >
>> >
>> > >Suppose I have a simple flat-file, say File-C which contains just one
>> > >field: Name of a person.
>> > >
>> > >File-A contains many records one of which is Name. Now I wish to copy
>> > >all records from File-A to a new File-B only if the Name present in
>> > >the record of File-A is availble in the list of Names in File-C.
>> > >
>> > >Can we use File Aid JCL (or Insync) to do this or any form of JCL to
>> > >achieve this..??
>> >
>> > You can do it easily with SQL. Load A and C into DB2 temp tables, extract
>>  the
>> > matches (B) with a SELECT * FROM A WHERE A.Name IN (SELECT Name FROM C);

0
robert3028 (111)
9/1/2003 6:48:48 AM

Check into you sort program, DFSORT will allow you to merge the two
files together. 

with running DFSORT twice this you should be able to do what you want.

1) Take file-c and make the name field match in postion in file-a
adding a fill character to be used latter to remove just the name
records.  

OUTFIL FNAMES=FLR1,OUTREC=(5,14,32,8,2Z,22,6),
    VLFILL=C'*'

2) Then merge the two file together 


MERGE
 Merge is generally used to add records from a dataset to another
dataset. Merge allows you merge up to 16 files (This was raised to a
limit of 100 in release 14 of DFSort) . The files being merged must be
the same format and have been sorted.

Examples:
MERGE FIELDS=(5,10,CH,A)
MERGE FIELDS=(10,5,ZD,D,34,4,PD,A)
MERGE FIELDS=(3,5,A,17,3,A),FORMAT=CH

May need to add a outfil statment to exlcude the records that just
contains the name only.


OUTFIL INCLUDE=(1,10,CH,EQ,C'**********'),FNAMES=GP1


On 29 Aug 2003 06:30:58 -0700, mail_ssb@yahoo.com (ssb) wrote:

>Hi,
>
>Suppose I have a simple flat-file, say File-C which contains just one
>field: Name of a person.
>
>File-A contains many records one of which is Name. Now I wish to copy
>all records from File-A to a new File-B only if the Name present in
>the record of File-A is availble in the list of Names in File-C.
>
>Can we use File Aid JCL (or Insync) to do this or any form of JCL to
>achieve this..??
>
>I wish to avoid writing a cobol program which can of course achieve
>this.
>
>Thanks...

0
kittingr (2)
9/16/2003 10:53:51 PM
Reply: