How to replace FileAID with SYNCSORT to produce file deltas?

dchristensen · Posted: Mon Sep 20, 2010 6:02 pm

We currently utilize batch FileAID to produce deltas for a large number of data sets, which we then process to create delimited files. The delimited files are sent via FTP to a staging server and are then loaded into SQL Server using SSIS. FileAID produces three files that separate INSERTS, DELETES and CHANGES.

While this process works well, my problem resides in the fact that we're doing most of our development on the workstation using Micro Focus Mainframe Express. FileAID isn't available through Mainframe Express which eliminates using Mainframe Express to fully test either new development or modifications to existing jobs.

I would like to look at replacing use of FileAID with using SYNCSORT as a means to create delta files. Could anyone suggest a place to look for examples of how I might be able to do this?

Dave

dick scherrer · Posted: Mon Sep 20, 2010 8:06 pm

Hello,

If you post the FileAid control statements, someone may have a suggestion.

If the tests are being run on the mainframe, why is there an issue?

If the tests are being run on the workstation, is the current release of Syncsort available on the workstation?

Possibly there is something i misunderstand. . .

dchristensen · Posted: Mon Sep 20, 2010 8:22 pm

For example, here is the JCL used to invoke FileAID to perform the delta compare:

//**********************************************************************00010004
//* COMPARE FILES & CREATE SEPARATE DELTA FILES FOR CHG,DEL & INS 00020004
//* RECORDS. 00030004
//* (STEP GENERATED BY FILE-AID)00040004
//**********************************************************************00050004
//* 00060004
//FAIDCOMP PROC 00061012
//* 00069511
//JSFACOMP EXEC PGM=FILEAID,REGION=6M 00070012
//STEPLIB DD DISP=SHR,DSN=SYS2.FILEAID.V910.LOAD 00080016
//* /* WITH ZAP DA05015 */ 00090004
//SYSPRINT DD SYSOUT=(,),OUTPUT=(*.PRINT,*.SAVRS) 00100004
//SYSLIST DD SYSOUT=(,),OUTPUT=(*.PRINT,*.SAVRS) 00110004
//DD01 DD DSN=&PREVFILE., 00120015
// DISP=SHR 00130004
//DD01C DD DSN=&CURRFILE., 00140015
// DISP=SHR 00150004
//DD01CO1 DD DSN=&DELFILE., 00160015
// DISP=(NEW,CATLG,DELETE), 00170004
// UNIT=SYSDA,SPACE=&FILESPAC, 00180014
// DCB=&FILEDCB 00190004
//DD01CO2 DD DSN=&INSFILE., 00200015
// DISP=(NEW,CATLG,DELETE), 00210004
// UNIT=SYSDA,SPACE=&FILESPAC, 00220014
// DCB=&FILEDCB 00230004
//DD01CO3 DD DSN=&CHGFILE., 00240015
// DISP=(NEW,CATLG,DELETE), 00250004
// UNIT=SYSDA,SPACE=&FILESPAC, 00260014
// DCB=&FILEDCB 00270004
//DD01CP DD DSN=X476.T400.PARMLIB(&COMPSPEC), 00280013
// DISP=SHR 00290004
//SYSIN DD DSN=X476.T415.FACOMP.CMDS,DISP=SHR 00300005
//* 00330004

Here are the FILEAID batch commands:

* 00010000
* COMPARE OPTIONS 00020000
0000 COMPARE_MODE=UNFORMATTED 00030000
0000 COMPARE_TYPE=READ_AHEAD 00040000
0000 READ_AHEAD_COUNT=250 00050001
0000 READ_AHEAD_SEQUENCE=ENFORCE 00060000
0000 RECORDS_TO_COMPARE=ALL 00070000
0000 DIFFERENCES_TO_COMPARE=ALL 00080000
* 00090000
* PRINT OPTIONS 00100000
0000 PRINT_FORMAT=CHAR 00110000
0000 MAX_DIFFERENCES_TO_REPORT=ALL 00120000
* 0000 RECORD_TYPES_TO_PRINT=CHANGED,DELETED,INSERTED 00130001
0000 RECORD_TYPES_TO_PRINT=NONE 00131001
0000 UNFORMATTED_REPORT_STYLE=CONDENSED 00140000
0000 UNFORMATTED_PRINT_SEQUENCE=GROUP 00150000
0000 CHANGED_RECORD_PRINT_CONTENT=RECORD 00160000
0000 INSERTED_RECORD_PRINT_CONTENT=RECORD 00170000
0000 DELETED_RECORD_PRINT_CONTENT=RECORD 00180000
0000 MATCHED_RECORD_PRINT_CONTENT=RECORD 00190000
0000 CONDENSED_REPORT_PRINT_ONLY_CHANGED_DATA=NO 00200000
0000 CONDENSED_REPORT_UNDERLINE_CHANGES=BOTH 00210000
0000 CONDENSED_REPORT_UNDERLINE_SYNC/KEY=BOTH 00220000
0000 CONDENSED_REPORT_SUPPRESS_PRINT_WITHOUT_CHANGES=YES 00230000
0000 CONDENSED_REPORT_PRINT_RULER=TOP 00240000
0000 CONDENSED_REPORT_CHANGED_DATA_UNDERLINE_CHARACTER=_ 00250000
0000 CONDENSED_REPORT_SYNC/KEY_UNDERLINE_CHARACTER= 00260000
* 00270000
* OUTPUT OPTIONS 00280000
0000 WRITE_TO_FILE_1=OLD/DELETED 00290000
0000 WRITE_TO_FILE_2=NEW/INSERTED 00300000
0000 WRITE_TO_FILE_3=NEW/CHANGED 00310000
* 00320000
* SYNC/KEY OPTIONS 00330000
0000 SYNC/KEY001:OLD_POSITION=00001,OLD_LENGTH=00006,OLD_DATA_TYPE=C, 00340000
SORTED=NO, 00350000
NEW_POSITION=00001,NEW_LENGTH=00006,NEW_DATA_TYPE=C 00360000
* 00370000
* COMPARE FIELDS SET 0001 00380000
0001 FIELD0002:OLD_POSITION=00001,OLD_LENGTH=00200,OLD_DATA_TYPE=C, 00390000
NEW_POSITION=00001,NEW_LENGTH=00200,NEW_DATA_TYPE=C 00400000

dchristensen · Posted: Mon Sep 20, 2010 8:27 pm

Mainframe Express from Micro Focus provides the DFSORT/SYNCSORT utility.

I'm doing testing and development on the workstation as much as I can to reduce mainframe costs. It is also convenient for files to end up on the workstation when I move to development/testing of the SSIS packages we use to load the files to the data warehouse.

We're also looking at rehosting some of the data/application systems and we have to figure how how to replace FileAID for that testing and development as well.

dick scherrer · Posted: Mon Sep 20, 2010 9:27 pm

Hello,

Alissa Margulies · Posted: Tue Sep 21, 2010 2:44 am

Hello David,

SyncSort's JOIN facility can produce separate output files containing the INSERTED, DELETED, and CHANGED records. Example 7 in the SyncSort for z/OS Exploiting JOIN Guide details exactly that. If you do not have that document readily available to you, please feel free to contact me offline at alissa.margulies@syncsort.com and I would be happy to further assist you with acquiring it.

Regards,