|
View previous topic :: View next topic
|
| Author |
Message |
nartcr
New User
Joined: 06 Jun 2007
Posts: 83
Location: anada
|
|
|
|
Folks,
I need suggestions on how better to handle this, or if there is a smarter way to approach this problem.
I have a key record and its getting replaced with a new key. Each of these keys are stored in separate database and holds relevant information. Ultimate goal is to see what the true new key would be.
Lets say KEY1 is superseded by KEY2 and KEY2 is superseded by KEY3, I need to be able to map the KEY1 to KEY3 efficiently.
Lets say input is:
| Code: |
----+----1----+----2----+----3
OLD KEY NEW KEY
11111
22222 AAAAA
33333 EEEEE
44444 22222
55555
66666 DDDDD
AAAAA BBBBB
BBBBB CCCCC
CCCCC DDDDD
DDDDD
EEEEE
|
The output need to show as follows:
| Code: |
OUTPUT:
----+----1----+----2----+----3
KEY MAPS TO
11111
22222 DDDDD
33333 EEEEE
44444 DDDDD
55555
66666 DDDDD
AAAAA DDDDD
BBBBB DDDDD
CCCCC DDDDD
DDDDD
|
I have been thinking of join or splice, but it looks like I do not know upfront how many times I need to splice the file. What is the best way to find what the true new key would be?
Any ideas are welcome.
Thanks,
Nara |
|
| Back to top |
|
 |
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1356
Location: Israel
|
|
|
|
1. Write a COBOL program.
2. Load the data in a temporary DB2 table and use recursive select (depends on your DB2 version). |
|
| Back to top |
|
|
nartcr
New User
Joined: 06 Jun 2007
Posts: 83
Location: anada
|
|
|
|
| Marso wrote: |
1. Write a COBOL program.
2. Load the data in a temporary DB2 table and use recursive select (depends on your DB2 version). |
1) Not an option since our shops uses only PL1. Since the records span to little above million, PL1 would be slower.
2) Again not an option, hand tied. For security reasons, we cannot load into DB2 tables. Input file is a flat file which was extracted by a vendor from DB2 tables and these are industry standard files.
Any way you could think about on SORT/ or some in-house products. I am also looking for less number of iterations. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| Quote: |
| shops uses only PL1 |
Then use that. If you cannot do anything else then speed is immaterial. |
|
| Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
| nartcr wrote: |
| Any way you could think about on SORT/ or some in-house products. I am also looking for less number of iterations. |
Only You will know which "In House" products you have, we don't.
The first step is to figure out the logic or process flow for doing what you want and then attempting to apply that to your sort product, but as others have said a program will almost certainly be the best solution.
The number of iterations will also depend on your programming skills as much as the product being used. |
|
| Back to top |
|
|
nartcr
New User
Joined: 06 Jun 2007
Posts: 83
Location: anada
|
|
|
|
Nic, agreed. Before I go program route, I wanted to make sure with icetool I cannot do something like this ?...
I will see if i can dynamic split files along with reformating each of these files and splice option to build a key. |
|
| Back to top |
|
|
nartcr
New User
Joined: 06 Jun 2007
Posts: 83
Location: anada
|
|
|
|
| expat wrote: |
| nartcr wrote: |
| Any way you could think about on SORT/ or some in-house products. I am also looking for less number of iterations. |
Only You will know which "In House" products you have, we don't.
The first step is to figure out the logic or process flow for doing what you want and then attempting to apply that to your sort product, but as others have said a program will almost certainly be the best solution.
The number of iterations will also depend on your programming skills as much as the product being used. |
Thanks Expat. I didnt mean in house products, was a typo :). No problem, i will see what i can do. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2288
Location: USA
|
|
|
|
If maximum level of key substitution is limited to a reasonable value, this can be done with only JCL+SORT utility. (It stops automatically when no more keys are substituted, up to 10 levels in this example)
| Code: |
//*====================================================================
//COPYFILE EXEC PGM=IEBGENER
//*
//SYSPRINT DD DUMMY
//SYSIN DD DUMMY
//*
//SYSUT1 DD *
*---+----1----+----2----+----3
* OLD KEY NEW KEY
11111
22222 AAAAA
33333 EEEEE
44444 22222
55555
66666 DDDDD
AAAAA BBBBB
BBBBB CCCCC
CCCCC DDDDD
DDDDD
EEEEE
//SYSUT2 DD DISP=(NEW,PASS),
// SPACE=(TRK,(10,10),RLSE),
// DSN=&SYSUID..KEYTAB
//*
//*====================================================================
//REPLKEY PROC DATA=NULLFILE,
// LOG='SYSOUT=*',
// TRACE='SYSOUT=*'
//*
//MATCH EXEC PGM=SORT,COND=(4,LE)
//*
//SYNOUT DD &LOG
//*
//MASTER DD DISP=(OLD,PASS),DSN=&DATA
//DETAILS DD DISP=(OLD,PASS),DSN=&DATA
//*
//SORTOUT DD DISP=(OLD,PASS),DSN=&DATA
//LISTING DD &TRACE
//COUNT DD DUMMY
//*
//SYSIN DD *
JOINKEYS F1=MASTER,
INCLUDE=(1,1,CH,NE,C'*',
AND,2,5,CH,NE,17,2,CH,
AND,2,5,CH,NE,C' '),
FIELDS=(17,5,A)
JOINKEYS F2=DETAILS,
INCLUDE=(1,1,CH,NE,C'*'),
FIELDS=(2,5,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,80,
F2:1,80,
?)
SORT FIELDS=COPY
OUTFIL FNAMES=COUNT,
INCLUDE=(161,1,CH,EQ,C'B',
AND,97,5,CH,NE,C' '),
NULLOFL=RC4
OUTFIL FNAMES=(LISTING,SORTOUT),
IFTHEN=(WHEN=(161,1,CH,EQ,C'1',
OR,97,5,CH,EQ,C' '),
BUILD=(1,80)),
IFTHEN=(WHEN=NONE,
BUILD=(1,16,
97,05,
22,59))
END
//*
// PEND
//*====================================================================
//KEY#1 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#2 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#3 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#4 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#5 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#6 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#7 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#8 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#9 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#10 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//*
//*====================================================================
//PRINT EXEC PGM=IEBGENER
//*
//SYSPRINT DD DUMMY
//SYSIN DD DUMMY
//*
//SYSUT2 DD SYSOUT=*
//SYSUT1 DD DISP=(OLD,PASS),DSN=&SYSUID..KEYTAB
//*
//*====================================================================
|
If the replacement level is unpredictable (that is unlikely?), then the same approach can be done, let's say, by calling SORT in the same manner from REXX, from some DO WHILE RC=0 loop. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
Should it be this ?
| Code: |
//SYSIN DD *
JOINKEYS F1=MASTER,
INCLUDE=(1,1,CH,NE,C'*',
AND,2,5,CH,NE,17,5,CH,
AND,2,5,CH,NE,C' '),
FIELDS=(17,5,A) |
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2288
Location: USA
|
|
|
|
| Rohit Umarjikar wrote: |
Should it be this ?
| Code: |
//SYSIN DD *
JOINKEYS F1=MASTER,
INCLUDE=(1,1,CH,NE,C'*',
AND,2,5,CH,NE,17,5,CH,
AND,2,5,CH,NE,C' '),
FIELDS=(17,5,A) |
|
The first line added to bypass comments in data lines; this is my own habit, to simplify debugging in many cases.
Line 2 is "fool protection", to prevent confusion with stupid translations like AAAAA --> AAAAA
Line 3 is critical. Try to remove them to find out what the result will be... |
|
| Back to top |
|
|
nartcr
New User
Joined: 06 Jun 2007
Posts: 83
Location: anada
|
|
|
|
| sergeyken wrote: |
If maximum level of key substitution is limited to a reasonable value, this can be done with only JCL+SORT utility. (It stops automatically when no more keys are substituted, up to 10 levels in this example)
| Code: |
//*====================================================================
//COPYFILE EXEC PGM=IEBGENER
//*
//SYSPRINT DD DUMMY
//SYSIN DD DUMMY
//*
//SYSUT1 DD *
*---+----1----+----2----+----3
* OLD KEY NEW KEY
11111
22222 AAAAA
33333 EEEEE
44444 22222
55555
66666 DDDDD
AAAAA BBBBB
BBBBB CCCCC
CCCCC DDDDD
DDDDD
EEEEE
//SYSUT2 DD DISP=(NEW,PASS),
// SPACE=(TRK,(10,10),RLSE),
// DSN=&SYSUID..KEYTAB
//*
//*====================================================================
//REPLKEY PROC DATA=NULLFILE,
// LOG='SYSOUT=*',
// TRACE='SYSOUT=*'
//*
//MATCH EXEC PGM=SORT,COND=(4,LE)
//*
//SYNOUT DD &LOG
//*
//MASTER DD DISP=(OLD,PASS),DSN=&DATA
//DETAILS DD DISP=(OLD,PASS),DSN=&DATA
//*
//SORTOUT DD DISP=(OLD,PASS),DSN=&DATA
//LISTING DD &TRACE
//COUNT DD DUMMY
//*
//SYSIN DD *
JOINKEYS F1=MASTER,
INCLUDE=(1,1,CH,NE,C'*',
AND,2,5,CH,NE,17,2,CH,
AND,2,5,CH,NE,C' '),
FIELDS=(17,5,A)
JOINKEYS F2=DETAILS,
INCLUDE=(1,1,CH,NE,C'*'),
FIELDS=(2,5,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,80,
F2:1,80,
?)
SORT FIELDS=COPY
OUTFIL FNAMES=COUNT,
INCLUDE=(161,1,CH,EQ,C'B',
AND,97,5,CH,NE,C' '),
NULLOFL=RC4
OUTFIL FNAMES=(LISTING,SORTOUT),
IFTHEN=(WHEN=(161,1,CH,EQ,C'1',
OR,97,5,CH,EQ,C' '),
BUILD=(1,80)),
IFTHEN=(WHEN=NONE,
BUILD=(1,16,
97,05,
22,59))
END
//*
// PEND
//*====================================================================
//KEY#1 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#2 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#3 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#4 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#5 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#6 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#7 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#8 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#9 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//KEY#10 EXEC REPLKEY,DATA=&SYSUID..KEYTAB,TRACE=DUMMY,LOG=DUMMY
//*
//*====================================================================
//PRINT EXEC PGM=IEBGENER
//*
//SYSPRINT DD DUMMY
//SYSIN DD DUMMY
//*
//SYSUT2 DD SYSOUT=*
//SYSUT1 DD DISP=(OLD,PASS),DSN=&SYSUID..KEYTAB
//*
//*====================================================================
|
If the replacement level is unpredictable (that is unlikely?), then the same approach can be done, let's say, by calling SORT in the same manner from REXX, from some DO WHILE RC=0 loop. |
Thanks a Bunch. Very smart. I was able to use it for getting my desired results. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
