× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Why do you not want to use SQL's DELETE?

At v5r3 or higher using DELETE to remove all rows doesn't (necessarily)
have to hit every row.  In other words, it works similar to CLRPFM.

From the manual:
DELETE Performance: An SQL DELETE statement that does not contain a
WHERE clause will delete all rows of a table. In this case, the rows may
be deleted using either a clear operation (if not running under
commitment control) or a change file operation (if running under
commitment control). If running under commitment control, the deletes
can still be committed or rolled back. This implementation will be much
faster than individually deleting each row, but individual journal
entries for each row will not be recorded in the journal. This technique
will only be used if all the following are true:         The target
table is not a view. 
         A significant number of rows are being deleted. 
         The job issuing the DELETE statement does not have an open
cursor on the file (not including pseudo-closed SQL
cursors). 
         No other job has a lock on the table. 
         The table does not have an active delete trigger. 
         The table is not the parent in a referential constraint with a
CASCADE, SET NULL, or SET DEFAULT delete rule.
         The user issuing the DELETE statement has *OBJMGT or *OBJALTER
system authority on the table in addition to the
DELETE privilege.

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Monday, October 23, 2006 12:00 PM
To: Midrange Systems Technical Discussion
Subject: "call qcmdexc parm('CLRPFM ABC' 10.00000)" in an SQL script?

I am attempting to clear several files at the top of an SQL 
script that
later populates those files.  (I would prefer to not use 
SQL's DELETE to
"clear" the file.)

I know that SQL supports calling programs with no parameters. 
 In a crude
test, I was able to prove that it is possible to pass 
parameters, as long as
they are specified in hexidecimal format, i.e., CALL ABC 
(x'C1').  Is there
a way to do this without mucking up the SQL script?  The SQL 
Reference and
Programming Guide don't offer much, although the programming 
guide gave an
obscure example of calling a program from a C program.

TIA,
Dan
-- 
This is the Midrange Systems Technical Discussion 
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.