Re: Quick file changes

Charles,

Your sample statement of the delete with the list is about what I
thought it would be. Nice to see I was closer than I thought.

I do like the concept of using another table, if for no other reason
than the list of functions is easier to verify.

I am going to look through the archives. I am remembering that there
was some oddity with the length of a SQL statement whe run from a file
using RUNSQLSTM. First thing I am going to do is query the file for
records to delete, then run SQL to chek the counts. Belt and
suspenders.

I was reluctant to write this as RPG. Could be done. Nowhere near as
simple or elegant. I also recall an unrelated disaster when a Synon
project design generated an IF with several hundred ANDEQ lines -
resulting in the compiler choking.

Thanks.

John McKee


On Thu, Oct 18, 2012 at 9:57 PM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

RUNSQLSTM should work if I recall correctly....otherwise you could
simply put the statement in an SQLRPGLE program

Note one statement is all you need.
DELETE FROM BSYSQSP2
WHERE QSSEC2 <> 'xxxx'
AND QSFN01 in ('FUNCTION', 'FUNCTION2','FUNCTION3')

You could also consider storing the function names in a new table
DELETE FROM BSYSQSP2
WHERE QSSEC2 <> 'xxxx'
AND QSFN01 in (select funcname from mytable)

You can always test the statement as a select....
select * FROM BSYSQSP2
WHERE QSSEC2 <> 'xxxx'
AND QSFN01 in ('FUNCTION', 'FUNCTION2','FUNCTION3')


HTH,
Charles


On Thu, Oct 18, 2012 at 10:47 PM, John McKee <jmmckee@xxxxxxxxxxxxxx> wrote:

As part of the impending phase out of the i where I work, I have been
told that access to functions currently authorized needs to be changed
at midnight on 10/31.

Two files, only second needs to be changed. First is user related
which associated with the second where a record exists for each
function to be allowed.

I can see how to write an RPG program to do this. Just work with the
second file. Read record, if authorized function is in a list, delete
the record, if the person authorized is not one specific person.
Makes for a big nested if. I think there are 20 or so functions that
have to be removed.

I could see creating a file with those functions in it, to make the IF
less of a potential issue.

I am wondering if SQL would be a better choice. And, if so, whether
the SQL could be put in a job that is subitted on hold to be released
later - or just released at a set time.

Would this be where RUNSQLSTM (not sure that is the right command - I
am away from the i right now) would be used? We have SQL installed.

My limited SQL skills would be something like this:

DELETE FROM BSYSQSP2 WHERE QSSEC2 <> 'xxxx' AND QSFN01 = 'FUNCTION'

Repeat the above for all functions.

So, is there a difference between the RPG method and SQL method? Are
separate DELETE commands the way to go in SQL?

This looks like my last programming task. Just want to do it right
and maybe learn something in the process.

It has been a fun ride. I will definitely miss working with the i,
despite the name changes.

John McKee
--
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.

--
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.