• If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Announcement

Collapse
No announcement yet.

Delete Valence Users

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Delete Valence Users

    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?

  • #2
    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:
    1. DSPUSRPRF USRPRF(*ALL) OUTPUT(*OUTFILE) OUTFILE(QTEMP/USERS)
    2. STRSQL
    3. UPDATE valence6/vvusers
      SET vvactiv='D'
      WHERE vvibmiuser<>' ' AND vvibmiuser NOT IN
      (SELECT upuprf FROM qtemp/users)
    4. DELETE FROM valence6/vvdevs
      WHERE vvusrid IN
      (SELECT vvusrid FROM valence6/vvusers WHERE vvactiv='D')
    5. DELETE FROM valence6/vvusrapps
      WHERE vvusrid IN
      (SELECT vvusrid FROM valence6/vvusers WHERE vvactiv='D')
    6. DELETE FROM valence6/vvusrauth
      WHERE vvusrid IN
      (SELECT vvusrid FROM valence6/vvusers WHERE vvactiv='D')
    7. DELETE FROM valence6/vvusrdflts
      WHERE vvusrid IN
      (SELECT vvusrid FROM valence6/vvusers WHERE vvactiv='D')
    8. DELETE FROM valence6/vvusrenx
      WHERE vvusrid IN
      (SELECT vvusrid FROM valence6/vvusers WHERE vvactiv='D')
    9. DELETE FROM valence6/vvwebs200
      WHERE vvusrid IN
      (SELECT vvusrid FROM valence6/vvusers WHERE vvactiv='D')
    10. DELETE FROM valence6/vvusers
      WHERE vvactiv='D'
    Step 1 creates a temporary QTEMP file of all your IBM i user profiles;
    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 records
    Last edited by robert.swanson; 11-03-2021, 09:15 AM.

    Comment


    • #3
      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';
      Last edited by gwilburn; 11-03-2021, 09:01 AM. Reason: Suggested Solution

      Comment


      • #4
        USERS is just a QTEMP outfile created in step 1, but QSYS2.USER_INFO would work just the same. And yes, we're using "D" as a marker for deleting records from the ancillary files before finally removing them from VVUSERS.

        Comment


        • #5
          OK... I was referring to your step #3. I think that's a typo.

          instead of valence6/users I'm thinking you mean valence6/vvusers

          Comment


          • #6
            Whoops, yes, that should be vvusers. I'll update the post.

            Comment


            • #7
              This worked perfectly! (I used qsys2.user_info)

              Thanks!

              Comment

              Working...
              X