IBM Mainframe Forum

Hi,

I have the data like as below in the datasets.
I want to copy whenever the string "SQLCODE =" comes, I need to copy the above five lines including the SQLCODE= line.
Can any one help in this one.

Thanks in Advance,
Purush

Example data :
----------------
PAGE 1
--#SET MAXERRORS 03
***INPUT STATEMENT:
SELECT 'G10.TG10Y02_007' FROM G10.TG10Y02_007
FETCH FIRST 1 ROWS ONLY WITH UR;
SUCCESSFUL RETRIEVAL OF 0 ROW(S)
PAGE 1
***INPUT STATEMENT:
SELECT 'G10.TG10Y03_007' FROM G10.TG10Y03_007
FETCH FIRST 1 ROWS ONLY WITH UR;
SUCCESSFUL RETRIEVAL OF 0 ROW(S)
PAGE 1
***INPUT STATEMENT:
SELECT 'G13.TG13B11' FROM G13.TG13B11
FETCH FIRST 1 ROWS ONLY WITH UR;
SQLERROR ON SELECT COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -551, ERROR: S8Y3Y2 DOES NOT HAVE THE PRIVILEGE TO PERFORM
DSNT418I SQLSTATE = 42501 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOSC SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -10 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFFF6' X'00000000' X'00000000' X'FFFFFFFF' X'000
INFORMATION

What sort product do you use and why did you not post in the section relevant to that product? What is the LRECL and RECFM of your input dataset? And do you want the same for your output dataset? What have you tried?

Also, when posting code, messages, data and other things that use a fixed pitch font please use the code tags. Look around the forum and you will see what I mean.

Hi,

I am not sure which sort need to use but am using IBM Sort utility.
RECFM=FB & LRECL=80 for both input and output datasets.

I did not get idea how to do it.

Thanks in Advance,
Purush

I am not sure which sort need to use but am using IBM Sort utility

You will need to use the sort that your company uses which tou now state is IBM Sort i.e. DFSort - read the first line or two of the SYSOUT from a sort step.

I think you need to read up about GROUP, IFTHEN and WHEN in the manual. And try looking in the forum, I think a similar requirement was asked last year - but possibly not in this forum. Anothe rplace to look is the DFSort tricks document available from the 'stickies' at the top of this section of the forum.

DFSORT is working on one record at a time. You know by the time the sixth arrives, that you need the previous five.

How would you do that in COBOL? You'd store the data in WORKING-STORAGE, and if an appropriate sixth record turned up, you'd have the other five ready.

DFSORT doesn't have any WORKING-STORAGE. If something needs storing, the only place is to store it on a record (extending, temporarily, your current record).

How do you get data from one record to another? Only possible with WHEN=GROUP and PUSH. You need five records, so you'll need five WHEN=GROUP and PUSH.

The first WHEN=GROUP has to mark your actual group of records, so you use BEGIN with a value that uniquely (for this type of group) defines the start of a group, which looks to be "***INPUT STATEMENT: ". For this you need to PUSH a sequence number (SEQ). You don't need to PUSH the data, because it is a literal value, which you can include later. You are interested in groups of six records, so use RECORDS=6 on the GROUP.

Your PUSH is PUSH=(onebyteafterrecord:SEQ=1). The "=1" is a length of one, so can hold 1-9m before flipping to zero, which is enough space for you.

Your second WHEN=GROUP uses the value of the SEQ established by the first PUSH. If it has a value of two, that defines the group. It needs to PUSH the data. There are only five records left in the group, so code RECORDS=5.

Your PUSH is PUSH=(nextavailablebyte:1,lengthofrecord).

Your third WHEN=GROUP uses the value of the SEQ established by the first PUSH. If it has a value of thee, that defines the group. It needs to PUSH the data. There are only four records left in the group, so code RECORDS=4.

See the pattern? Continue it, it finishes with RECORDS=2.

You now have a long record, the sixth of which in a group contains the previous four records, and one more that you can construct from a literal. You then need to be able to write multiple records from a single input record. This can only be done in OUTFIL, with the / (Slash Operator).

So, you code OUTFIL, You need to use INCLUDE= to identify the sixth record and use BUILD, like this:



OK. That is a lot to take in. Take it a bit at a time. Try with a very simple test file:



The idea is to extract the two groups of records which start with "1" and end in "6". The data is only one byte long, for ease of testing.

Do each line of code one at a time and see how it builds on the output.

Here's a start. Put the test file under //SORTIN DD * and use //SORTOUT DD SYSOUT=*, and look at the output you get in the spool.


Then add the next line, to define the group which starts with "2". Then the next.

By the time you've got through the test data, you'll have done something pretty complex, and learned a lot.