We have quite a users list in Portal Admin... there are hundreds of users that are no longer users on the IBM i. How can we delete these other than one by one?
Announcement
Collapse
No announcement yet.
Delete Valence Users
Collapse
X
-
You could do a mass cleanup of Valence user profiles by using a series of SQL statements to delete the records from various VV files corresponding to no-longer-existing IBM i users. But you must remove the records in a certain sequence due to foreign key constraints.
So I would suggest using the following steps, replacing VALENCE6 with whatever library is appropriate:- DSPUSRPRF USRPRF(*ALL) OUTPUT(*OUTFILE) OUTFILE(QTEMP/USERS)
- STRSQL
- UPDATE valence6/vvusers
SET vvactiv='D'
WHERE vvibmiuser<>' ' AND vvibmiuser NOT IN
(SELECT upuprf FROM qtemp/users) - DELETE FROM valence6/vvdevs
WHERE vvusrid IN
(SELECT vvusrid FROM valence6/vvusers WHERE vvactiv='D') - DELETE FROM valence6/vvusrapps
WHERE vvusrid IN
(SELECT vvusrid FROM valence6/vvusers WHERE vvactiv='D') - DELETE FROM valence6/vvusrauth
WHERE vvusrid IN
(SELECT vvusrid FROM valence6/vvusers WHERE vvactiv='D') - DELETE FROM valence6/vvusrdflts
WHERE vvusrid IN
(SELECT vvusrid FROM valence6/vvusers WHERE vvactiv='D') - DELETE FROM valence6/vvusrenx
WHERE vvusrid IN
(SELECT vvusrid FROM valence6/vvusers WHERE vvactiv='D') - DELETE FROM valence6/vvwebs200
WHERE vvusrid IN
(SELECT vvusrid FROM valence6/vvusers WHERE vvactiv='D') - DELETE FROM valence6/vvusers
WHERE vvactiv='D'
Step 3 marks all Valence user records to be deleted with a "D";
Steps 4-9 remove records from ancillary files that hold the user ID (many may not have any records);
Finally, step 10 removes the Valence user recordsLast edited by robert.swanson; 11-03-2021, 09:15 AM.
- 1 like
-
I don't have an object "users" in VALENCE6 or VALENCE6P libraries?
Instead of creating a temporary file with a list of IBM i users, could you simply use qsys2.user_info? This would allow all the statements to run from Run SQL Scripts.
I was able to do this:
select * from valence6p.vvusers
WHERE vvibmiuser<>' ' AND vvibmiuser NOT IN
(SELECT user_name from qsys2.user_info);
Result was 86 users.
But here vvactiv = '1' or '0'. So you're making it 'D' for purposes of the other SQL statements, then deleting from vvusers?
Just trying to understand before I do this delete and potentially screw things up.
How about this (saved in a PC file for Run SQL Scripts)
Code:UPDATE valence6p.vvusers SET vvactiv='D' WHERE vvibmiuser<>' ' AND vvibmiuser NOT IN (SELECT user_name from qsys2.user_info); DELETE FROM valence6p.vvdevs WHERE vvusrid IN (SELECT vvusrid FROM valence6p.vvusers WHERE vvactiv='D'); DELETE FROM valence6p.vvusrapps WHERE vvusrid IN (SELECT vvusrid FROM valence6p.vvusers WHERE vvactiv='D'); DELETE FROM valence6p.vvusrauth WHERE vvusrid IN (SELECT vvusrid FROM valence6p.vvusers WHERE vvactiv='D'); DELETE FROM valence6p.vvusrdflts WHERE vvusrid IN (SELECT vvusrid FROM valence6p.vvusers WHERE vvactiv='D'); DELETE FROM valence6p.vvusrenx WHERE vvusrid IN (SELECT vvusrid FROM valence6p.vvusers WHERE vvactiv='D'); DELETE FROM valence6p.vvwebs200 WHERE vvusrid IN (SELECT vvusrid FROM valence6p.vvusers WHERE vvactiv='D'); DELETE FROM valence6p.vvusers WHERE vvactiv='D';
Comment
Comment