DELETE of all records in a file

[NeilD]

We utilize SQL within a program (SQLRPGLE) to delete all records in a QTEMP file prior to reloading the file.

c/exec sql
c+ DELETE FROM qtemp/AFILE WITH NC
c/end-exec

However, we experienced a strange event where the program seemed to process an array of records which shouldn't exist together and the possible cause is that the DELETE failed prior to rebuilding the file.
Could this delete statement fail?
For example; could the first record in the file be locked and cause the statement to fail?

Thanks for any help you can offer.

[DeadManWalks]

Re: DELETE of all records in a file

any record could be locked and present an issue.
It is quicker to issue a ClrPgm, or Delete the table and create it again instead of deleting record by record. Issuing a delete will also let you know if something has a record locked.

[TedHolt]

Re: DELETE of all records in a file

It is quicker to issue a ClrPgm

The last time I tested this, and it has been a few years, the system would sometimes clear a physical file when DELETE had no WHERE clause.

I have read that it clears if there are no locks, but I have seen instances where it did not do so.

[TedHolt]

Re: DELETE of all records in a file

Could this delete statement fail?

Yes, which is why I recommend always checking the SQL status (or SQL code) after every operation.

[MichaelCatalani]

Re: DELETE of all records in a file

The last time I tested this, and it has been a few years, the system would sometimes clear a physical file when DELETE had no WHERE clause.

I have read that it clears if there are no locks, but I have seen instances where it did not do so.

I ran some tests on this a couple of years back, comparing what occurred in the journal. I cant remember what the threshold was, but if the file contained more than a certain number of records, a CLRPFM was issued. Under the threshhold, and individual record deletes were issued.

[B.Hauser]

Re: DELETE of all records in a file

A CLRPFM is executed when performing an SQL Delete Statement, if all rows have to be deleted and there is no lock on the table/rows.
If there is any lock, the records are only deleted but not a CLRPFM (needs *EXCL access) performed. CLRPFM includes an RGZPFM, so the delete records are physically removed. The SQL DELETE only marks the records as being deleted but does not remove them physically from the table.

Birgitta

[NeilD]

Re: DELETE of all records in a file

Many thanks for the helpful hints and tips.
Problem solved.
SQLSTT was not being monitored correctly. (01504 "Delete statement does not include a WHERE clause").

[TedHolt]

Re: DELETE of all records in a file

Many thanks for the helpful hints and tips.
Problem solved.
SQLSTT was not being monitored correctly. (01504 "Delete statement does not include a WHERE clause").

SQL state 01504 is a warning, not an error. It would not cause the DELETE to fail.

[TedHolt]

Re: DELETE of all records in a file

A CLRPFM is executed when performing an SQL Delete Statement, if all rows have to be deleted and there is no lock on the table/rows.

That's what I thought, but it doesn't appear to be true in all cases. I did the following test on a 7.1 machine with recent PTF's.

CRTDUPOBJ OBJ(QCUSTCDT) FROMLIB(QIWS) OBJTYPE(*FILE)
          TOLIB(QTEMP) NEWOBJ(CUST) DATA(*YES)    
          CST(*NO) TRG(*NO)

DSPFD shows me 12 active rows, zero deleted.

delete from qtemp/cust where state = 'TX'
2 rows deleted from CUST in QTEMP.

DSPFD shows 10 active rows, 2 deleted

delete from qtemp/cust             
10 rows deleted from CUST in QTEMP.

DSPFD shows zero active rows, 12 deleted.

I know some folks at IBM who should be able to explain why the system did not clear the file. One of these days I'll get around to asking them.

[NeilD]

Re: DELETE of all records in a file

SQL state 01504 is a warning, not an error. It would not cause the DELETE to fail.

Hi Ted,
Correct, our problem was that we hadn't anticipated a warning. It actually did delete the records, but because we weren't monitoring for the 01504 warning it affected the next process.
Neil

[TedHolt]

Re: DELETE of all records in a file

because we weren't monitoring for the 01504 warning it affected the next process.

Forgive me for being a little thick-headed, Neil, but I don't understand why you would need to monitor for the message.

[NeilD]

Re: DELETE of all records in a file

Forgive me for being a little thick-headed, Neil, but I don't understand why you would need to monitor for the message.

Our "IF" statement only proceeded if the SQLSTT = '00000'. We needed to include other "Warning" messages too.

[MichaelCatalani]

Re: DELETE of all records in a file

That's what I thought, but it doesn't appear to be true in all cases. I did the following test on a 7.1 machine with recent PTF's.

CRTDUPOBJ OBJ(QCUSTCDT) FROMLIB(QIWS) OBJTYPE(*FILE)
          TOLIB(QTEMP) NEWOBJ(CUST) DATA(*YES)    
          CST(*NO) TRG(*NO)

DSPFD shows me 12 active rows, zero deleted.

delete from qtemp/cust where state = 'TX'
2 rows deleted from CUST in QTEMP.

DSPFD shows 10 active rows, 2 deleted

delete from qtemp/cust             
10 rows deleted from CUST in QTEMP.

DSPFD shows zero active rows, 12 deleted.

I know some folks at IBM who should be able to explain why the system did not clear the file. One of these days I'll get around to asking them.

Ted,

Increase the number of rows in your file to a few hundred, then run the last delete statement again. See if a CLRPFM is executed instead.

Back when I did tested this (a couple of years ago), I believe the threshhold, for whether a CLRPFM or individual deletes was used, was around 50 rows on my machine. (i515 6.1). I was checking the journal, so I could tell if individual deletes were being executed, or a clrpfm. I believe the threshold was 50 rows, but it may have been more than that.

I remember thinking that this could potentially impact's someone's DR efforts, because if a CLRPFM is executed, you can not un-do the changes with a RMVJRNCHG command. You have to restore the file, and apply all journal changes back to that point. It's probably not a big deal (until you need to do it), but I did think it was odd that the delete command would float between record deletion and a CLRPFM. It would almost make sense to always do a CLRPFM.

[TedHolt]

Re: DELETE of all records in a file

I asked Kent Milligan about the system using CLRPFM when the SQL DELETE has no WHERE clause. He pointed me to the SQL_FAST_DELETE_ROW_COUNT value in QAQQINI. He said that the default for fast delete is 1000 rows.

I googled and found an article of Scott Forstie's with more information:

http://iprodeveloper.com/database/ge...t-delete-db2-i

[TedHolt]

Re: DELETE of all records in a file

Our "IF" statement only proceeded if the SQLSTT = '00000'. We needed to include other "Warning" messages too.

I've seen a lot of programs that looped until SQL code came back with a value of 100. They run fine day after day, until one day the system sends a warning message and the program doesn't get the full result set.

I always check for SQLSTATE > '02000' or SQLSTATE >= '02000', depending on the situation. (Sometimes 02000 is an error, and sometimes it isn't.)