CODEWITHSUNDEEP
db2mainframejcl
Sowmiya V
Sowmiya V

Reputation: 1

Formatting captured spufi results using JCL

How to format the input file as expected output file?

Input file :

BROWSE-- userid.RESULT

COLUMNS 001 072 COMMAND INPUT ===> SCROLL ===> PAGE

--------+---------+---------+---------+---------+---------+---------+---------+ SELECT LASTNAME, FIRSTNME, PHONENO 00010000 FROM DSN8C10.EMP 00020000 WHERE WORKDEPT = 'D11' 00030000 ORDER BY LASTNAME; 00040000
---------+---------+---------+---------+---------+---------+---------+---------+

LASTNAME FIRSTNME PHONENO

ADAMSON BRUCE 4510

BROWN DAVID 4501

JOHN REBA 0672

JONES WILLIAM 0942

LUTZ JENNIFER 0672

PIANKA ELIZABETH 3782

SCOUTTEN MARILYN 1682
---------+---------+---------+---------+---------+---------+---------+---------+

LASTNAME FIRSTNME PHONENO

STERN IRVING 6423

WALKER JAMES 2986

YAMAMOTO KIYOSHI 2890

YOSHIMURA MASATOSHI 2890

DSNE610I NUMBER OF ROWS DISPLAYED IS 11 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---- ---------+---------+---------+---------+---------+---------+----
DSNE617I COMMIT PERFORMED, SQLCODE IS 0 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72 DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1 DSNE621I NUMBER OF INPUT RECORDS READ IS 4 DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 30

Expected Output file :

ADAMSON BRUCE 4510

BROWN DAVID 4501

JOHN REBA 0672

JONES WILLIAM 0942

LUTZ JENNIFER 0672

PIANKA ELIZABETH 3782

SCOUTTEN MARILYN 1682

STERN IRVING 6423

WALKER JAMES 2986

YAMAMOTO KIYOSHI 2890

YOSHIMURA MASATOSHI 2890

Upvotes: 0

Views: 677

Answers (1)

cschneid
cschneid

Reputation: 10765

There are a number of issues here. You appear to be running SPUFI interactively, you'd have to change that to a batch execution otherwise you'll be overwriting your userid.RESULTS dataset every time you use SPUFI for a different purpose. DSNTEP2 and DSNTEP4 are documented in the IBM DB2 documentation, be advised their output is limited to 133 bytes in width. This looks like it will work for your situation, but not necessarily in the general case.

After doing that, you have many options for reformatting your results, your shop's SORT utility, awk, sed, custom code you write yourself in Rexx, Java, PL/I, COBOL, C, C++, Python, and so forth. Choosing one of these is dependent on a number of factors including what products and languages are currently installed in your shop, your own skill set, size of the result set returned from DB2, which products and/or languages are currently being phased out of your shop, and so forth.

If you're going to go the custom code route, you might want to code your SELECT statement in that language (if it's supported) and do your data retrieval and reformatting all in one program.

Perhaps a better solution to your problem is to use Syncsort, if your shop has that product. Syncsort has the ability to execute a SELECT statement against DB2 and then process the result, reformatting as necessary. Bear in mind that while the ability is there, your shop may have elected not to use it.

As is often the case, you are well served by asking your peers and support staff how this type of challenge is normally met in your shop. Shop standards exist for many reasons, following them is in your best interest.

Upvotes: 2

Related Questions