0

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?

VinDesai
  • 13
  • 1
  • 1
  • 4
  • so you want to dump all of the data to file and use DFSORT or make duplicates of all of the rows with a new key? – SaggingRufus Jun 22 '17 at 17:46
  • Also, to actually help you with the sort, we will need to know the data layout – SaggingRufus Jun 22 '17 at 17:47
  • @SaggingRufus yes. Last goal is to have double row count by any means of data manipulation. I am planning to multiple key value by '-1'. Will that be feasible? – VinDesai Jun 22 '17 at 17:49
  • @SaggingRufus database has 5 columns out of which first column with length of 4(Integer) is primary key. – VinDesai Jun 22 '17 at 17:52

1 Answers1

2

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)
/*
SaggingRufus
  • 1,814
  • 16
  • 32
  • Thank you so much. This seems pretty straightforward and simple way to accomplish the task goal. Thank you so much, you have been guidance. – VinDesai Jun 22 '17 at 18:56