Hy!
I have some probelms with the Modify (delete)...Can someone help me?
I must cancel a chart with 15 million lines.
The problem is that the program writes in the spool a lot of lines and at the end it goes to abend.

During the execution of the program the following message is visualized:

(FOC415)TRANS 2 REJECTED NOMATCH GZ_VALOR
2896 361120090101
(FOC415)TRANS 3 REJECTED NOMATCH GZ_VALOR
2896 361120090101
(FOC415)TRANS 4 REJECTED NOMATCH GZ_VALOR
2896 361120090101
(FOC415)TRANS 5 REJECTED NOMATCH GZ_VALOR


But I would want to visualize the following message:


REFERENCE...AT TRANS 1000
REFERENCE...AT TRANS 2000
REFERENCE...AT TRANS 3000
REFERENCE...AT TRANS 4000
REFERENCE...AT TRANS 5000



Thank you for any help!

This message has been edited. Last edited by: Kerry,

Well, first off, you need to post your MODIFY code. Second, examine your input transactions against your data base to figure out why you are getting the NOMATCH. Thirdly, the REFERENCE lines indicate that a checkpoint has been met and a commit done. If you want fewer, you can add a CHECK nnnn just before the END statement. CHECK 1000 is the default

But, finally, if you need to delete that many rows, a REBUILD/REORG might be a better and quicker option for you.

Alas, you did not post your modify code.
Now we'll have to do some guess work.
My guess is that you would like to delete or update existing records. And that you do not need to do anything for non-existing records.
And you probably have in your code something like
MATCH KEYFIELD(S)
ON NOMATCH REJECT
or something to that same effect.
It is the ON NOMATCH REJECT that produces these messages.
In stead of ON NOMATCH REJECT you could also code ON NOMATCH GOTO ENDCASE or ON NOMATCH GOTO TOP, these will not produce messages.
If this is not reflecting your situation, please post your code...

Hi,

this is my source code:
***************************************************

MODIFY FILE GZVALORE
FIXFORM ZC_GARANZ/9 ZC_CAUSALE/9 ZD_RIFERIM/8
MATCH ZC_GARANZ ZC_CAUSALE ZD_RIFERIM
ON NOMATCH REJECT
ON MATCH DELETE
ON MATCH GOTO CANCELLA
CASE CANCELLA
NEXT ZC_GARANZ ZC_CAUSALE ZD_RIFERIM
ON NEXT DELETE
ON NEXT GOTO CANCELLA
ON NONEXT GOTO ENDCASE
ENDCASE
DATA ON KEY2DEL -----> this is the file that contains all the record keys that should be deleted
END
***************************************************

The strange thing is that when I execute this command on a table, log lines are written each 1000 transactions, while when I execute it on another table, log lines are written for each record. This behaviour causes an abnormal termination of the program (abend).
I wouldn't like that it writes logs for each record.
I think the problem can be found in the table settings, but I don't know which setting can help me to manage deletion for each 1000 transactions.

Thank you again for your collaboration.

Again, change ON NOMATCH REJECT to ON NOMATCH GOTO TOP. All nomatch (reject) messages will then vanish.

You can also stop the NOMATCH messages with

LOG NOMATCH MSG OFF

However, it seems to me that your MODIFY logic may delete more than you want. Essentially, you are deleting ever record in the table after you find your first match - which would explain why subsequent transactions find no match. Is that your intention?

If you wish to delete only those records that match your input transactions, then you don't need case CANCELLA:

MODIFY FILE GZVALORE
 FIXFORM ZC_GARANZ/9 ZC_CAUSALE/9 ZD_RIFERIM/8 
 MATCH ZC_GARANZ ZC_CAUSALE ZD_RIFERIM 
    ON NOMATCH REJECT 
    ON MATCH DELETE 
 DATA ON KEY2DEL
END

This message has been edited. Last edited by: Dan Satchell,

For the handling of Check Point processing, you should check the MODIFY manual. This has been asked before in this forum.

One other thing, is the keys you supply in the file KEY2DEL, are they unique in the file ?

If they are, then you do not need the case CANCELLA, If they are not, then you probably need to change your NEXT to a MATCH.

Perhaps posting your master GZVALORE may help as well.

Thank you all for your suggestions. I tried to modify my program with your suggestions but the result is negative.

I think to not have described clearly which is my problem so I would like to tell you once again which it is.
I succeed to execute correctly this program for one table (let's call it with letter A) but not for another table (let's call it with letter B). So for table A this program delete correctly all records printing one line of log each 1000 transactions, while instead on table B this programs prints one line of log for each record. The result is that in the second case the printing spool finishes and bring the program to abend.
Is there a property that can set how the program should work (by record or transaction)? Is it a property that should be set inside table (of a DB2 database) properties? Is there a way to set when it should write a log line, that is after n transactions?

Thank you.
Regards.
Rob

Rob, first off, you need to download a mainframe FOCUS manual and read up on MODIFY. It is not a tool to treat lightly if you have no experience.

As for Table B, let's go over this again. The REFERENCE .. AT TRANS lines are commit points governed by the CHECK command. The default number is 1000. If you want to see fewer of those, then you increase the check amount, for instance CHECK 5000. You put this line near the bottom of your MODIFY code.

Now your NOMATCH messages which are undoubtedly the ones filling up your spool are under your control. Several suggestions have been given as to how to re-tool your MODIFY so that it doesn't get those. If they really are an error, then you need to look into why they are happening. You could turn the messages off via LOG NOMATCH MSG OFF but I submit to you that this is not a good idea. Generally when you get these messages, something is wrong and you need to pay attention.

You have been asked to post the master file description of the file you are trying to modify. Please do so as well as the most current version of your code as well as a few of the keys in your input transaction file.

Hello Ginny,

at the moment I’m working with Focus because I need to do it for job reason, but I’m a young programmer and I don’t know perfectly this programming language.
I’m trying to perform a delete activity on DB2 database on tables that contain 15 millions of lines. Here you can read the complete program that should perform this activity:
**************************************************************************

SET CDN=ON
SET ASNAME=ON
SET ALL =PASS
-SET &&OGGI = &YYMD ;
-SET &&DTRIF1 = DATECVT(&&OGGI,'I8YYMD','YYMD');
-SET &&INI1 = DATEMOV(&&DTRIF1,'BOY');
-SET &&INIZIOA = DATECVT(&&INI1,'YYMD','I8YYMD');
-SET &&MESE = EDIT(&&OGGI,$$$$99$$);
-SET &&INI2 = DATEADD(&&INI1, 'D', -1);
-SET &&INI2B = DATEMOV(&&INI2,'BOY');
-SET &&INIZIOB = DATECVT(&&INI2B,'YYMD','I8YYMD');
-SET &&DATA_STO = IF &&MESE LE 01 THEN &&INIZIOB ELSE &&INIZIOA ;
DEFINE FILE SATEMPO
D5/YYMD = &&DATA_STO;
END
-*
TABLE FILE SATEMPO
PRINT TD_DATA
TF_FINEMES
BY TD_DATA
WHERE TD_DATA LE D5
ON TABLE HOLD AS 'TMP1TE'
END
DEFINE FILE GZVALORE
D5/YYMD = &&DATA_STO;
END
-*
TABLE FILE GZVALORE
PRINT ZC_GARANZ
ZC_CAUSALE
ZD_RIFERIM
BY ZD_RIFERIM NOPRINT
WHERE ZD_RIFERIM LT D5
WHERE ZC_CAUSALE IN FILE LISTCAUS
IF RECORDLIMIT EQ 1500
ON TABLE HOLD AS 'TMPGZV1'
END
-*
JOIN CLEAR *
JOIN ZD_RIFERIM IN TMPGZV1 TO TD_DATA IN TMP1TE AS J1
-*
DEFINE FILE TMPGZV1
D5/YYMD = &&DATA_STO;
END
-*
TABLE FILE TMPGZV1
PRINT ZC_GARANZ
ZC_CAUSALE
ZD_RIFERIM
BY ZC_GARANZ NOPRINT
BY ZC_CAUSALE NOPRINT
BY ZD_RIFERIM NOPRINT
WHERE TF_FINEMES EQ 'N'
ON TABLE SAVE AS 'KEY2DEL'
END
-*
MODIFY FILE GZVALORE
FIXFORM ZC_GARANZ/9 ZC_CAUSALE/9 ZD_RIFERIM/8
MATCH ZC_GARANZ ZC_CAUSALE ZD_RIFERIM
ON NOMATCH REJECT
ON MATCH DELETE
ON MATCH GOTO CANCELLA
CASE CANCELLA
NEXT ZC_GARANZ ZC_CAUSALE ZD_RIFERIM
ON NEXT DELETE
ON NEXT GOTO CANCELLA
ON NONEXT GOTO ENDCASE
ENDCASE
DATA ON KEY2DEL
END
-*
*************************************************************************
This is the master file description:
**************************************************************************
FILENAME=GZVALORE,SUFFIX=SQLDS,$

SEGNAME='GZ_VALOR',SEGTYPE=S0,$
FIELD=ZC_GARANZ ,ZC_GARANZ ,I9 ,I4 ,MISSING=OFF,
FIELD=ZC_DIVISA ,ZC_DIVISA ,I6 ,I2 ,MISSING=OFF,
FIELD=ZC_CAUSALE ,ZC_CAUSALE ,I9 ,I4 ,MISSING=OFF,
FIELD=ZD_RIFERIM ,ZD_RIFERIM ,YYMD ,DATE ,MISSING=OFF,
FIELD=ZI_VALOREQ ,ZI_VALOREQ ,P17.2 ,P8 ,MISSING=OFF,
FIELD=ZI_VALORET ,ZI_VALORET ,P17.2 ,P8 ,MISSING=OFF,
FIELD=ZC_CODCOMP ,ZC_CODCOMP ,A2 ,A2 ,MISSING=OFF,
FIELD=ZC_CHIAVE1 ,ZC_CHIAVE1 ,I9 ,I4 ,MISSING=OFF,
FIELD=ZC_CHIAVE2 ,ZC_CHIAVE2 ,I6 ,I2 ,MISSING=OFF,
FIELD=ZC_CHIAVE3 ,ZC_CHIAVE3 ,I9 ,I4 ,MISSING=OFF,
FIELD=ZC_CHIAVE4 ,ZC_CHIAVE4 ,I6 ,I2 ,MISSING=OFF,
FIELD=ZC_CHIAVE5 ,ZC_CHIAVE5 ,A1 ,A1 ,MISSING=OFF,
FIELD=ZP_VALORE ,ZP_VALORE ,P13.8 ,P6 ,MISSING=OFF,
FIELD=ZD_VALORE ,ZD_VALORE ,YYMD ,DATE ,MISSING=OFF,
FIELD=ZC_CUTENTE ,ZC_CUTENTE ,A8 ,A8 ,MISSING=OFF,
FIELD=ZC_CVALIDI ,ZC_CVALIDI ,A1 ,A1 ,MISSING=OFF,
FIELD=ZD_DTRANS ,ZD_DTRANS ,HYYMDm ,HYYMDm,MISSING=OFF
**************************************************************************
I don’t know if the algorithm is correct. I would like to delete all records of DB2 table using a searching key, made of three different values. These values are taken from a file, generated from this program (you can look at it in the source code above). When the values of the file are matched with the values of DB2 table the record should deleted. The values of the file are a secondary index of DB2 table, so it’s possible to find different records on DB2 table that matches one key of the file.
DB2 table is indexed with a non-clustered index.
Do you think that this algorithm generates errors when values are not matched? Because the spool is filled of those logs produced when values are not matched.

Regards.
Roby

Roby

The logic you are using seems to be incorrect.

Look at what you are doing. You are matching on field1, field3 and field4, that is the values supplied in the data KEYDEL2 are matching ONE record on the table. That record is then deleted (ON MATCH DELETE). After that you are going to CASE CANCELLA. Here you are issuing a NEXT. A NEXT statement literally moves to the next record, it DOES NOT match any key field values. You then DELETE that record, and repeat this until the END OF FILE. That has happened for transaction number 1, you have deleted nearly all of your data file, so transaction 2 cannot find the record as you have already deleted it.

How many keys has the table? Create a transaction file with ALL the key values and then use MATCH only to delete, do not use NEXT.

Alan is correct, do not use NEXT. You should match all keys in table GZVALORE. You didn't include the access file for GZVALORE in your post, but I assume the first four fields in the table are keys. I assume that table SATEMPO is also DB2. If that is true, your code should look something like that below.

You should use TABLEF to extract the data because only DB2 should do the sorting, not WebFOCUS. You should use your filter date, &&DATA_STO, directly in the WHERE clauses to avoid having to create a D5 DEFINE for every record in the extracts. These changes should improve performance. You should use all four keys from table GZVALORE to obtain a single match for every record you want to delete. Then your MODIFY becomes a simple set of steps: read a transaction, match on all keys, and delete the record. I changed your ON TABLE SAVE AS KEY2DEL to ON TABLE HOLD AS KEY2DEL so a master is created for file KEY2DEL. Then your FIXFORM statement becomes FIXFORM FROM KEY2DEL because MODIFY can read the structure for the input transactions directly from the master description for KEY2DEL.

SET CDN=ON 
SET ASNAME=ON 
SET ALL =PASS 
-SET &&OGGI = &YYMD ; 
-SET &&DTRIF1 = DATECVT(&&OGGI,'I8YYMD','YYMD'); 
-SET &&INI1 = DATEMOV(&&DTRIF1,'BOY'); 
-SET &&INIZIOA = DATECVT(&&INI1,'YYMD','I8YYMD'); 
-SET &&MESE = EDIT(&&OGGI,$$$$99$$); 
-SET &&INI2 = DATEADD(&&INI1, 'D', -1); 
-SET &&INI2B = DATEMOV(&&INI2,'BOY'); 
-SET &&INIZIOB = DATECVT(&&INI2B,'YYMD','I8YYMD'); 
-SET &&DATA_STO = IF &&MESE LE 01 THEN &&INIZIOB ELSE &&INIZIOA ;
-*
TABLEF FILE SATEMPO 
 PRINT TF_FINEMES 
 BY TD_DATA 
 WHERE TD_DATA LE '&&DATA_STO' 
 ON TABLE HOLD AS 'TMP1TE' 
END
-*
TABLEF FILE GZVALORE 
 PRINT ZC_GARANZ
       ZC_DIVISA 
       ZC_CAUSALE 
 BY ZD_RIFERIM
 WHERE ZD_RIFERIM LT '&&DATA_STO' 
 WHERE ZC_CAUSALE IN FILE LISTCAUS 
 IF RECORDLIMIT EQ 1500 
 ON TABLE HOLD AS 'TMPGZV1' 
END 
-* 
JOIN CLEAR * 
JOIN ZD_RIFERIM IN TMPGZV1 TO TD_DATA IN TMP1TE AS J1 
-* 
TABLE FILE TMPGZV1 
 BY ZC_GARANZ
 BY ZC_DIVISA
 BY ZC_CAUSALE
 BY ZD_RIFERIM
 WHERE TF_FINEMES EQ 'N' 
 ON TABLE HOLD AS 'KEY2DEL' 
END 
-* 
MODIFY FILE GZVALORE 
 FIXFORM FROM KEY2DEL
 MATCH ZC_GARANZ ZC_DIVISA ZC_CAUSALE ZD_RIFERIM 
   ON NOMATCH REJECT 
   ON MATCH DELETE 
 DATA ON KEY2DEL 
END 

This message has been edited. Last edited by: Dan Satchell,

Also, I don't think you want to use SET ALL = PASS. I believe this statement will cause the WHERE clause WHERE TF_FINEMES EQ 'N' to be ignored if a matching record is not found in JOINed file TMP1TE.

Hello all,

thanks to everybody for your precious suggestions.
I solved this problem following Dan source code.
The problem was due to file content: different keys appear more than one time and I wasn't aware about this.
So now my code has been correct and you can look at it just below:

DEFINE FILE TMPGZV1
D5/YYMD = &&DATA_STO;
END
-*
TABLE FILE TMPGZV1
SUM MAX.ZC_GARANZ
MAX.ZC_CAUSALE
MAX.ZD_RIFERIM
BY ZC_GARANZ NOPRINT
BY ZC_CAUSALE NOPRINT
BY ZD_RIFERIM NOPRINT
WHERE TF_FINEMES EQ 'N'
ON TABLE SAVE AS 'KEY2DEL'
END
-*
MODIFY FILE GZVALORE
FIXFORM ZC_GARANZ/9 ZC_CAUSALE/9 ZD_RIFERIM/8
MATCH ZC_GARANZ ZC_CAUSALE ZD_RIFERIM
ON NOMATCH REJECT
ON MATCH DELETE
DATA ON KEY2DEL

Thank you again.
Regards.
Roby