Select Disabled User on Database

[demaya]

Hello Folks,

I want to optimize my user management in OnDemand. Before I delete the users after x months I want to disable them x months before delete. Setting and getting each user through arsxml is no problem.

But I have some existing scripts that use arsuser to get user information. Where can I get the disabled state from? I found out, that the field "ADMIN" is updated when disabling / enabling the user in the admin client. But I can't get an distinct value I can see the user is disabled on database level...

Anyone has an idea?

Cheers!

[jw]

Hi mayach,

If you translate the ADMIN field in bits, you will see bit number 8 (start from zero) is turned on, which means the user is disabled.

Hope this helps.

-jw

[Justin Derrick]

Yup, there are many bit (or 'vector') fields used in CMOD.  A word of warning:  Depending on the langugage you're using, and the platform you're on, the bits can be reversed.  (ie, Little-Endian vs. Big-Endian-ness).  This was one of the trickier things I had to deal with when I wrote RAPTOR4, since it runs on both little and big-endian machines.

-JD.

[demaya]

Sounds like fun ;)

Thank you anyway.

How can I select this on a db2 query? I can't manage it to work for me...

Cheers!

[Justin Derrick]

I've never managed to figure this out in SQL -- I usually resort to Perl for this sort of thing.  If anyone knows how to do this, I'd love to see it. 

-JD.

[ewirtz]

You can get results by SQL as well. You have just to use an OR to see whether a bit is on:

SELECT HEX(ADMIN),A.* FROM ARSSERVR.ARSUSER A
     WHERE USER LIKE '%'
     AND (SUBSTR(HEX(ADMIN), 2 , 1)='1'    -- 1
       OR SUBSTR(HEX(ADMIN), 2 , 1)='3'    -- 1 + 2
       OR SUBSTR(HEX(ADMIN), 2 , 1)='7'    -- 1 + 2 + 4
       OR SUBSTR(HEX(ADMIN), 2 , 1)='F'    -- 1 + 2 + 4 + 8
       OR SUBSTR(HEX(ADMIN), 2 , 1)='5'    -- 1 + 4
       OR SUBSTR(HEX(ADMIN), 2 , 1)='D'    -- 1 + 4 + 8
       OR SUBSTR(HEX(ADMIN), 2 , 1)='9');; -- 1 + 8

regards

Egon

[jw]

this may help.

select userid, bitand(admin, 256) from arsuser where bitand(admin,256) <> 0

-jw

[ewirtz]

Hi,

at least DB2 Z/OS doesn't have the bitand function.   :(

Egon