36,410 pts.
POSTED: Feb 9, 2012 9:19 PM (GMT)
For a basic temporary change, just use OVRDBF to override to the desired member before running your SQL DELETE.
Tom
Can I use SQL to delete a couple record from a member of a physical file?
I have a physical file that have multiple member and I delete a couple records from one of the member. I'm not sure if I can use SQL to do it. I'm using DFU to remove records now.
Thanks!!
Software/Hardware used:
iSeries
Thanks!!
Software/Hardware used:
iSeries
ASKED:
Feb 9, 2012 6:28 PM GMT
UPDATED:
February 27, 2012 9:05:28 PM GMT
RELATED QUESTIONS
- passing variable in CL Program using QMQRY and SQL
- AS/400 Program To List Physcial Files with Deleted Records
- Reading only the active records in a flat file
- Copy Spooled File to a Source Physical File Member
- Writing a "If" & "Then" Statement in Crystal Reports 9
- Updating a SQL Server record in a large table
- How do I find out how many lines of source code are on the system?
- Working with STRSQL command in AS/400
- List all file members from a multi member AS400 File
- Direct an SQL statement
Answer Wiki:
Yes, you can
1. Use SQL to create an ALIAS file
CREATE ALIAS QTEMP/&yourFile FOR &yourLib/&yourFile (&yourMember)
2. Use your SQL statement ot update the file QTEMP/&yourFile
3. Delete QTEMP/&yourFile
NOTE: you do not need to use QTEMP. I just do the so I do not leave unneeded objects on the system since for me these are one time shots.
If you are doing many SQL statements over the same members, you may want to leave themn on the system.
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
Absolutely right but confusing, Charllie used &yourFile for both the file name and the Alias name.
1. Use SQL to create an ALIAS file
CREATE ALIAS QTEMP/yourAlias FOR yourLib/yourFile (yourMember)
2. Use SQL to
Delete QTEMP/yourALIAS where ...... rules for finding records to be deleted.
3. If you want to delete alias Use SQL
Drop Alias QTEMP/yourAlias
----------
By creating the Alais in QTEMP it goes away when your job ends. If you want the alias to be persistant then create it in a real library.
Phil
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
1. Use SQL to create an ALIAS file
CREATE ALIAS QTEMP/&yourFile FOR &yourLib/&yourFile (&yourMember)
2. Use your SQL statement ot update the file QTEMP/&yourFile
3. Delete QTEMP/&yourFile
NOTE: you do not need to use QTEMP. I just do the so I do not leave unneeded objects on the system since for me these are one time shots.
If you are doing many SQL statements over the same members, you may want to leave themn on the system.
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
Absolutely right but confusing, Charllie used &yourFile for both the file name and the Alias name.
1. Use SQL to create an ALIAS file
CREATE ALIAS QTEMP/yourAlias FOR yourLib/yourFile (yourMember)
2. Use SQL to
Delete QTEMP/yourALIAS where ...... rules for finding records to be deleted.
3. If you want to delete alias Use SQL
Drop Alias QTEMP/yourAlias
----------
By creating the Alais in QTEMP it goes away when your job ends. If you want the alias to be persistant then create it in a real library.
Phil
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
To see other answers submitted to the Answer Wiki View Answer History.
