How the heck... do i delete the sap* from my sandbox client xxx?

[former_member823327]

I have a client on a sandbox where all the power users are locked (ones that can unlock sap*). I already have the sapstar logon param set to zero, so now all i have to do is delete that user, on said client, on said instance, in table USR02. How the heck do i do this on a DB2 platform where the OS is integrated with the DB? If this were MSSQL - no prob.

Regards,

Mark

[former_member823327 · Accepted solution]

Well, getting closer i was able to at least execute the last statement in the PACE shell but received execution errors and after further research found out that i can execue the db2 command in the Q shell. Unfortunately im now getting CLI conversion errors which i can't make sense out of.

• CLI ERROR *****

SQLSTATE: 57017

NATIVE ERROR CODE: -332

Character conversion between CCSID 13488 and CCSID 65535 not valid.

This is a bizarre error to my point of view, either i can execute SQL or not?

Regards,

Mark

[thomas_obermeier · Accepted solution]

Hi Mark,

I can reproduce this if I run

CHGJOB CCSID(65535)

on my current job prior to starting qsh. By default SIDOFR and SIDADM have a Job CCSID of 500. Run

CHGJOB CCSID(500)

or, even better, logon as SIDOFR/SIDADM and retry. Don't forget to end QSH with F3 (not F12) to avoid using the same qsh session with the old settings again.

If it still fails, check your Job CCSID with

DSPJOB OPTION(*DFNA)

Regards,

Thomas

[former_member823327]

Worked as advertised. A few things that needed to happen in order if there are any newbies like myself...

1) Needed to run the CHGJOB CCSID(500) command before opening the QSH

2) Needed to end the command with a ";" before the final double quotes

Thanks everyone for your help!

Best Regards,

Mark

[former_member182034]

hi Mark,

you can reset you user with this command

SQL> delete sapsr3.USR02 where bname='SAP*' and mandt='client_number';
SQL> COMMIT;

Regards,

[Former Member]

Hi Mark,

If you only need to unlock the user, you can do it following way.

For DB2, login as "db2adm" user and run db2cli or db2.cmd

db2> update sapsr3.USR02 set uflag='0' where bname='power_user' and mandt='client_number';

Regards.

Rajesh Narkhede

[thomas_obermeier]

Hi Mark,

The syntax on IBM i is a little different but quite similar to what majamil and Rajesh described. From my view you have two options:

1) start from the command line and type STRSQL if this tool is installed. From there, you can run SQL commands. In your case it is like

delete from r3<sid>data/usr02  where bname = 'SAP*' and mandt = 'client_number'

2) start from the command line and type CALL qp2term to open a PASE shell, which behaves almost like a UNIX shell. There you have access to db2 with the following syntax:

db2 "delete from r3<sid>data.usr02  where bname = 'SAP*' and mandt = 'client_number'"

HTH, Thomas

Edited by: Thomas Obermeier on Jun 29, 2011 9:04 AM

Edited by: Thomas Obermeier on Jun 29, 2011 9:07 AM