Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
Delete Records Command
has no ratings.
Published: 05 Feb 2013
Revised: 12 Feb 2013 - 1533 days ago
Last viewed on: 24 Apr 2017 (3412 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Delete Records Command Published by: Bob Cozzi on 05 Feb 2013 view comments(13)

I'm working on a new command for COZTOOLS, called "Delete Database Records".

This command will allow you to delete records from a database file using either of the following two options:

  1. Specify any SQL WHERE clause on the WHERE parameter of the CL command.
  2. Specify a DLTREL (Delete Relationship) parameter where you specify the record position and the value in that record position of records that are candidates to be deleted.

Any records matching the selection criteria are deleted from the file.

Background: I have clients that moved from S/36 to our current operating system over the decades but left in legacy routines. Most have been updated, but some old CL was converted from S/36 OCL and still has that primitive look to it.

For example, one client has a CPYF command that copies a master file to a temp file with the following:

CPYF A TOFILE(B) CRTFILE(*YES) INCCHAR(*RCD 1 *EQ 'D')
CPYF B TOFILE(A) CRTFILE(*NO) MBROPT(*REPLACE)

Often they'll throw in a DLTF A in the middle of these things. Which of course is creating havoc on our Journalling (Deleting a file that's journalled screws up a lot of things.)

Therefore I'm creating this new Delete Database Records" command that will delete using the INCCHAR style parameter or an SQL style WHERE clause.

Here's my question and I welcome any other comments:

I currently have a 3rd option for deleting records. I allow you to specify a starting Record number and record count. So you could say DLTRCD( 36 ) and it will delete record number 36. Or you could say DLTRCD( 36 10) and it would delete the 10 records starting with record 36.

The difficulty I'm having with this option is that a SETLL to the record number will cause it to position to the net available relative record number. So perhaps the user really did mean the 36th record, but what if they mean relative record number 36 and its isn't there? I could reverse the logic and use a CHAIN to the record number and if not found, do nothing, but then I have the reverse issue. If someone uses (for example) DSPPFM to view the data in the file and sees a record number of 36, then they issue the command, they may want the 36th record in the file deleted, not relative record 36.

Which gives me this vexing issue to resolve. My current thinking is to simply (A) omit the parameter and not provide deleting by record number or (B) make the parameter a hidden parm that only appears when F10 is pressed on the prompter, and tell people to use with caution.

So what do you think? Should I drop the DLTRCD (delete record range) parameter, keep it, or something else?

 

 

 

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: Paulster
Premium member *
Sweden and The Netherlands
Comment on: Delete Records Command
Posted: 4 years 2 months 19 days 3 hours 14 minutes ago

And then there's the issue of the user seeing a block of 10 records to delete but missing the deleted record in the middle of this block. So effectively, the user should delete a block of 11 records rather than 10. How will the command treat the already deleted records?

Of course a function like you propose cannot be handled to any user that is not aware of such things so maybe it is not an issue.

 

All and all I reckon a FROMRN and a TORN would be more suitable than the FROMRRN and NBROFRCDS proposal. Prior to executing the delete, the command would check if these records numbers actually exist and issue a warning if any does not.

 

Anyway, my 2 cents worth...

Posted by: Ringer
Premium member *
Comment on: Delete Records Command
Posted: 4 years 2 months 18 days 19 hours 25 minutes ago
Edited: Wed, 06 Feb, 2013 at 08:14:34 (1539 days ago)

I'd retrieve the RRN from the INFDS of the file of the record just read. If it's in the range, delete it, and read another record by RRN. I/O blocking is not an issue with ILE RPG when it assigns the RRN (was an issue in RPG 3).

I'd also disable this option if the file is REUSEDLT(*YES) so a deleted record does not get filled in suddenly and then inadvertently deleted again.

Chris Ringer

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Delete Records Command
Posted: 4 years 2 months 18 days 18 hours 14 minutes ago

While I've had to do this kind of thing before, my gut feeling is to not include the Relative Record Number option at all, and just go with the SQL-style WHERE and the *RCD position = "D" options.

Posted by: Paulster
Premium member *
Sweden and The Netherlands
Comment on: Delete Records Command
Posted: 4 years 2 months 18 days 3 hours 41 minutes ago

So basically, you're gonna invent the SQL delete...   :o)

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Delete Records Command
Posted: 4 years 2 months 17 days 17 hours 17 minutes ago

No, just enable it in CL. 

Posted by: KevinLandWaster
Premium member *
Sydney, NSW, Australia
Comment on: Delete Records Command
Posted: 4 years 2 months 17 days 11 hours 48 minutes ago

RUNSQL anyone?

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Delete Records Command
Posted: 4 years 2 months 17 days 11 hours 9 minutes ago
Edited: Thu, 07 Feb, 2013 at 16:28:11 (1538 days ago)

I created RUNSQL in 1988 and have been selling it ever since. We had "words" with IBM about offering it in 2013, nearly 25 years after I first introduced it. But yes, if you are on V7R1 TR5 you can use IBM's RUNSQL command to do similar functions. But it does not include a search of the *RCD as a DLTRCD command would nor will it work on V5R4 an later (V7R1 is required). But both DLTRCD and RUNSQL are now "free" so who cares which one is used, really? Cool

Posted by: Paulster
Premium member *
Sweden and The Netherlands
Comment on: Delete Records Command
Posted: 4 years 2 months 17 days 3 hours 34 minutes ago

So what we need is this:

CHGSYSVAL SYSVAL(QALLOWSQLINCL) VALUE(*YES)

And then you would do your SQL thing as you would in RPG.

 

Serious now. I have not had any need of running SQL in a CL program in recent years, nor the urge to undelete rows nor delete blocks of rows by RRN.

The basic policy is: you deleted it, you fix it! And otherwise there's always the backup to resort to. You restore the table in another lib and copy the deleted block back from the restored table.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Delete Records Command
Posted: 4 years 2 months 16 days 20 hours 26 minutes ago

Guess I'm in Paulster's camp, except I was wondering why you're not using the journal, possibly RMVJRNCHG to do the un-delete (no programming required!).

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Delete Records Command
Posted: 4 years 2 months 16 days 16 hours 44 minutes ago

Dale, did you reply the the wrong thread? The other one is talking about the UNDELETE function. I'm discussing a DLTRCD command in CL that provides both SQL or *RCD comparison to purge out records and as I said in my de!--script--ion, I have clients who have CL that was converted from S/36 OCL. That CL contains CPYF A to B INCCHAR(*RCD 1 NE 'D') and similar code, then they do a CPYF back to the orignal. Since they are not doing remote journalling, the journals are being shut off (because most of the time they also do a CPYF B to A after issuing a DLTF A. Its a nightmare end of month procedure.

The DLTRCD command allows us to go in an replace that 30 year old code with a single command that accomplishes the same task with a lot less overhead, and without interrupting the journalling procedures.

Remember, not every shop has a $10 billion IT budget--they get by, by asking "that won't cost anything, will it?"

 

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Delete Records Command
Posted: 4 years 2 months 13 days 19 hours 10 minutes ago

Nah; I was triggered off Paulster's comment, "you deleted it, you fix it!" But, you're right, my comment might make more sense in the other thread.

Posted by: Paulster
Premium member *
Sweden and The Netherlands
Comment on: Delete Records Command
Posted: 4 years 2 months 13 days 14 hours 11 minutes ago

Right Dale, I sorta mixed them up too. 

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Delete Records Command
Posted: 4 years 2 months 12 days 20 hours 47 minutes ago

fwiw, DLTRCD with INCCHAR would be more like the original CPYF's (which probably originally were COPYDATA's), so replacing them would be more intuitive.

If you were to do anything with FROMRCD TORCD NBRRCDS, record selection should work the same way as CPYF.