IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

IMS DB Unload


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sudib19

New User


Joined: 06 Feb 2007
Posts: 29
Location: Noida

PostPosted: Thu Jan 27, 2011 12:03 am
Reply with quote

Hi,

I have a IMS DB unload file which is a VB file of 163 bytes, depending upon the 51st byte which should not be P and the 79th byte which should not be T I am trying to extract the 11bytes out of the 29th position out of the file into a FB file of 80 bytes. I am using this control card but am not able to get the result
Code:

//TOOLIN DD *                                         
  COPY FROM(IN1) TO(OUT) USING(CTL1)                 
/*                                                   
//CTL1CNTL DD *                                       
  OMIT COND=(51,1,CH,NE,C'P',AND,79,1,CH,NE,C'T')     
  OUTREC FIELDS=(1:29,11)                             
/*                                                   


Can somebody please help me in this.

Also in the next step I would like to compare with another file of 80 bytes which again is a flat file and has the first 11 bytes. So I was thinking of merging the files and extracting the duplicates. Can all these be possible in one Icetool step?

Thanks
Sudip
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Thu Jan 27, 2011 1:50 am
Reply with quote

Are you considering the VB file RDW in your position calculation? If not, your byte 51 will be byte 55 and your byte 79 will be byte 81 -- and byte 11 would be byte 15.
Back to top
View user's profile Send private message
sudib19

New User


Joined: 06 Feb 2007
Posts: 29
Location: Noida

PostPosted: Thu Jan 27, 2011 2:02 am
Reply with quote

yes I am robert considering the RDW positions
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Thu Jan 27, 2011 2:39 am
Reply with quote

To convert from VB to FB, you must use an OUTFIL statement with VTOF and BUILD, for example:

Code:

//TOOLIN DD *
  COPY FROM(IN1) TO(OUT) USING(CTL1)
/*
//CTL1CNTL DD *
  OMIT COND=(51,1,CH,NE,C'P',AND,79,1,CH,NE,C'T')
  OUTFIL FNAMES=OUT,VTOF,BUILD=(29,11)
/*     


Quote:
Also in the next step I would like to compare with another file of 80 bytes which again is a flat file and has the first 11 bytes. So I was thinking of merging the files and extracting the duplicates. Can all these be possible in one Icetool step?


Probably, but first you have to explain more clearly what you're trying to do.

Please show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.
Back to top
View user's profile Send private message
sudib19

New User


Joined: 06 Feb 2007
Posts: 29
Location: Noida

PostPosted: Fri Jan 28, 2011 10:20 pm
Reply with quote

Hi Frank,

I have an IMS database extracted to a flat file of record format VB and record length 163. From this I need to extract out records depending on the following criterion
a) 55th position should not 'P'
b) 83rd position should not be 'T'
When this criterion is met I have to extract the account number of length 11 and at position 34 to a FB file of record length 80. This database unload will not have any duplicates (as in the account numbers).
The database unload file looks like this
Code:

 BROWSE    XX.XXXXX.UNLOAD                    Line 00000000 Col 001 080
 Command ===>                                                  Scroll ===> CSR 
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
********************************* Top of Data **********************************
....IXP07.00.F3YPREFIX LENGTH=  39 MAX ROOT KEY LENGTH= 11                     
........XXXXXX  ..XXXXXXX ..1003804001910038040019P2113710230203020399       CA2
........XXXXXX  ..XXXXXXX ..1008308001910083080019P21138443401220604669      CA2
........XXXXXX  ..XXXXXXX ..1009145004810091450048D2113705459839230          CA2
........XXXXXX  ..XXXXXXX ..1009550001210095500012D01130261610035767         CT2
........XXXXXX  ..XXXXXXX ..1009712001710097120017D21139182510799195         CA1


Once I get the account numbers in the FB file I would want to compare it with another file of which is again a FB file of 80 bytes. This file would have duplicates, I need to remove the duplicates and then compare and get the matching records from the two files into another file.
The second file looks like this
Code:

 BROWSE    XXX.XXX Line 00000000 Col 001 080
 Command ===>                                                  Scroll ===> CSR 
********************************* Top of Data **********************************
10146970123                                                                     
10158680065                                                                     
10160760038                                             
                       


Can these be done in one step? Please let me know

Thanks for your help

Sudip
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Jan 28, 2011 10:57 pm
Reply with quote

sudib19,


It is not clear as to what the final output is. Your description does NOT match the control cards you have shown.


Anyway here is DFSORT JCL which will give you the desired results and I assumed the following.

1. You want to eliminate any records which has 'P' in pos 55 AND 'T" in pos 83.

2. Your Key to match in FB file starts in position 1 for a length of 11 bytes and the key in IMS unload file starts in position 33.

3. Once matched you want the IMS unload record as is.

Code:

//STEP0100 EXEC PGM=SORT                         
//SYSOUT   DD SYSOUT=*                           
//INA      DD DSN=Your input VB ims Unload file,DISP=SHR
//INB      DD DSN=Your input FB file with key in pos 1 file,DISP=SHR
//SORTOUT  DD SYSOUT=*                           
//SYSIN    DD *                                 
  OPTION COPY                                   
  JOINKEYS F1=INA,FIELDS=(33,11,A)               
  JOINKEYS F2=INB,FIELDS=(01,11,A)               
  REFORMAT FIELDS=(F1:1,4,5)                     
//JNF1CNTL DD *                                 
  OMIT COND=(55,1,CH,EQ,C'P',AND,83,1,CH,EQ,C'T')
//JNF2CNTL DD *                                 
  SUM FIELDS=NONE                               
//*
Back to top
View user's profile Send private message
sudib19

New User


Joined: 06 Feb 2007
Posts: 29
Location: Noida

PostPosted: Sat Jan 29, 2011 12:09 am
Reply with quote

Hi Skolusu,

I am trying this

Code:

//S1    EXEC  PGM=ICETOOL                                           
//TOOLMSG DD SYSOUT=*                                               
//DFSMSG  DD SYSOUT=*                                               
//IN1 DD DSN=XX.XX.UNLOAD,DISP=SHR (vb file with lrecl 163)                       
//IN2 DD DSN=XX.XX.XX,DISP=SHR       (fb lrecl=80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)       
//OUT DD DSN=CS.CI#A32.APAY.OUTPUT,                                 
//             UNIT=(SYSDA),                                       
//             SPACE=(CYL,(10,10),RLSE),                           
//             DCB=(LRECL=80,RECFM=FB),                             
//             DISP=(NEW,CATLG,CATLG)                               
//SORTOUT DD DSN=&T2,                   
//             UNIT=(SYSDA),                                       
//             SPACE=(CYL,(10,10),RLSE),                           
//             DCB=(RECFM=FB,BLKSIZE=0,LRECL=80),                   
//             DISP=(NEW,CATLG,DELETE)                             
//TOOLIN DD *                                                       
  COPY FROM(IN1) TO(SORTOUT) USING(CTL1)                           
  SELECT FROM(SORTOUT) TO(T1) ON(1,11,CH) NODUPS                   
  SELECT FROM(IN2) TO(T1) ON(1,11,CH) NODUPS                       
  SELECT FROM(T1) TO(OUT) ON(1,11,CH) FIRSTDUP                     
/*                                                         
//CTL1CNTL DD *                                             
  INCLUDE COND=(55,1,CH,NE,C'P',AND,83,1,CH,NE,C'T')
  OUTFIL FNAMES=SORTOUT,VTOF,                               
  OUTREC=(1:33,11,12:69X)                                   
/*     

                                                 


However this gives an error
Code:

                                                                           
   
            COPY FROM(IN1) TO(SORTOUT) USING(CTL1)                             
ICE606I 0 DFSORT CALL 0001 FOR COPY  FROM IN1      TO SORTOUT  USING CTL1CNTL T
ICE602I 0 OPERATION RETURN CODE:  16                                               


I hope this is clear.

Thanks
Sudip [/code]
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Sat Jan 29, 2011 12:19 am
Reply with quote

Sudip,

Kolusu gave you an efficient JOINKEYS solution that works.

You came back with an inefficient ICETOOL job that doesn't work. Why would you expect him to waste his time figuring out why your inefficient solution doesn't work?

Did you try Kolusu's solution? If so, and it didn't meet your requirements, then explain what you wanted and what you got. Then he can help you.
Back to top
View user's profile Send private message
sudib19

New User


Joined: 06 Feb 2007
Posts: 29
Location: Noida

PostPosted: Mon Jan 31, 2011 8:16 am
Reply with quote

Thanks Frank and Skolusu,

I was able to do that using ICETOOl.

Code:

//S1    EXEC  PGM=ICETOOL                                             
//TOOLMSG DD SYSOUT=*                                                 
//DFSMSG  DD SYSOUT=*                                                 
//IN1 DD DSN=VB File 163 Bytes, DiSP=SHR                         
//IN2 DD DSN=FB File of 80 bytes
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)         
//OUT DD DSN=&&T2,                                   
//             UNIT=(SYSDA),                                         
//             SPACE=(CYL,(10,10),RLSE),                             
//             DCB=(LRECL=80,RECFM=FB),                               
//             DISP=(NEW,CATLG,DELETE)                                 
//SORTOUT DD DSN=Output File of 80 bytes,                     
//             UNIT=(SYSDA),                                         
//             SPACE=(CYL,(10,10),RLSE),                             
//             DCB=(RECFM=FB,BLKSIZE=0,LRECL=80),                     
//             DISP=(NEW,CATLG,DELETE)                               
//TOOLIN DD *                                                         
  COPY FROM(IN1) TO(SORTOUT) USING(CTL1)                     
  SELECT FROM(SORTOUT) TO(T1) ON(1,11,CH) NODUPS             
  SELECT FROM(IN2) TO(T1) ON(1,11,CH) NODUPS                 
  SELECT FROM(T1) TO(OUT) ON(1,11,CH) FIRSTDUP               
/*                                                           
//CTL1CNTL DD *                                               
  INCLUDE COND=(55,1,CH,NE,C'P',AND,83,1,CH,NE,C'T')         
  OUTFIL FNAMES=SORTOUT,VTOF,                                 
  OUTREC=(1:33,11,12:69X)                                     
/*                                                           


Thanks
Sudip
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Jan 31, 2011 8:42 am
Reply with quote

Hello,

Which part of "your solution is inefficient" did you not understand?

Why do you want to use a solution that wastes resources when you have been given a better solution?

Do you or someone in your group get paid more to waste system resources? Perhaps own the hardware and can charge for the usage?

icon_confused.gif

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

DFSORT Developer


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

PostPosted: Tue Feb 01, 2011 2:16 am
Reply with quote

Sudip,

Please don't waste our time with this kind of thing in the future.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Remote Unload of CLOB Columns DB2 6
No new posts Multiple table unload using INZUTILB DB2 2
No new posts changing defaults in db2 admin - Unlo... DB2 0
No new posts DB2 Table - Image copy unload IBM Tools 2
Search our Forums:

Back to Top