Data manipulation with SORT utility

Question

There is one DB2 table which has 4byte Interger as Primary key. Now i have to double the rows of this table. One way i have is, manipulate the key value by unloading the table to a dataset and keep all the columns detail as is. This way i will be able to double the rows. I am planning to multiply each primary key with value '-1' such that i will get another row with same details except key with negative value. I haven't worked much on data manipulation. Can i use SORT utility for this? If yes, then how? Are there any references available?

Answer 1

Here is how I would do it. Maybe someone else on here has a better way to accomplish your task, but I would do this:

STEP 1 COPY

using IEBGENER, copy the original data to a temp file. Keep in mind I had to guess that the record length and space required

//COPY1    EXEC PGM=IEBGENER                       
//SYSUT1   DD DSN=XX.FILE.ORIGINAL,          
//            DISP=SHR                             
//SYSUT2   DD DSN=&&TEMPFILE,         
//            DISP=(KEEP,PASS),UNIT=(SYSDA,1), 
//            LRECL=50,RECFM=FB,
//            SPACE=(CYL,(25,10),RLSE)             
//SYSPRINT DD SYSOUT=*                             
//SYSIN    DD DUMMY  

After that, write a DFSORT step that will change all of the key values. I assume that this key will appear first in you file and that it is 4 characters.

STEP 2 THE MATH

Here we will take the temp file and write out a new file where the key = key * -1

//MULTI    EXEC PGM=DFSORT                       
//SORTIN   DD DSN=&&TEMPFILE,          
//            DISP=SHR                             
//SORTOUT  DD DSN=XX.FILE.MULTI,         
//            DISP=(,CATLG,DELETE),UNIT=(SYSDA,1), 
//            LRECL=50,RECFM=FB,
//            SPACE=(CYL,(25,10),RLSE)             
//SYSPRINT DD SYSOUT=*                             
//SYSIN    DD *
  SORT FIELDS=COPY
  OUTREC FIELDS=(-1,MUL,1,4,ZD,EDIT=(STTT),SIGNS=('+','-',,))
/*

Once that step is complete, you can use the DFSORT tool to sort the two files into one

STEP 3 SORT

//SORT    EXEC PGM=DFSORT                       
//SORTIN   DD DSN=XX.FILE.ORIGINAL,          
//            DISP=SHR   
//         DD DSN=XX.FILE.MULTI,
//            DISP=SHR                          
//SORTOUT  DD DSN=XX.FILE.FINAL,         
//            DISP=(,CATLG,DELETE),UNIT=(SYSDA,1), 
//            LRECL=50,RECFM=FB,
//            SPACE=(CYL,(25,10),RLSE)             
//SYSPRINT DD SYSOUT=*                             
//SYSIN    DD *
  SORT FIELDS=(1,4,ZD,A)
/*