How to drop user on IBM PureData System for Analytics

Troubleshooting

Problem

How to drop user on IBM PureData System for Analytics?

Symptom

When customers try to delete the user, they are getting the error "could not acquire lock for"

DEV.ADMIN(ADMIN)=> drop user test;
ERROR: DROP USER: could not acquire lock for "TEST"

Cause

This behavior occurred when the user has sessions that are running on the NPS system.

Environment

Let's give an example in how we get on this situation:

1- In one putty session connect on the database as the test user
nzsql -c DEV -u test -pw test

2- Run a query against one table
DEV.ADMIN(TEST)=> select * from r1;

3- Open a new session and execute the alter table command as follows:
DEV.ADMIN(TEST)=> alter table r1 add column col2 int;

4- Open a new session and try to delete the user:
DEV.ADMIN(ADMIN)=> drop user test;
ERROR: DROP USER: could not acquire lock for "TEST"

Diagnosing The Problem

To verify whether there is any session open as with the user TEST as nz user run the command "nzsession":

Command "nzsession" shows all the sessions that are active or idle in your NPS system.

nzsession ID Type User Start Time PID Database Schema State Priority Name Client IP Client PID Command ------ ---- ------- ----------------------- ---- -------- ------ ------ ------------- --------- ---------- ------------------------
179302 sql TEST 06-Jan-15, 13:05:33 EST 2474 RM ADMIN active normal 127.0.0.1 2473 select * from r1
179308 sql TEST 06-Jan-15, 13:06:14 EST 2615 RM ADMIN active normal 127.0.0.1 2614 alter table r1 add colum 179410 sql ADMIN 06-Jan-15, 13:16:25 EST 3815 SYSTEM ADMIN active normal 127.0.0.1 3814 SELECT session_id, clien

Resolving The Problem

In order to delete the user TEST, you have to kill all the sessions used by user TEST.

To kill the session from the user, run the following commands:
nzsession abort -force -id 179308
nzsession abort -force -id 179302

After aborting the sessions from user TEST, verify whether there is any other session in use by the user running command nzsession. If there is no session in use by TEST user, then you can drop user TEST with command below:

nzsql -c "drop user test;" DROP USER

[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"Database","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Product Synonym

pda netezza

Was this topic helpful?

Document Information

Modified date:
03 January 2024

UID

swg21694055

Tips