IBM Support

ERROR: Query History User is 'ADMIN'

Troubleshooting


Problem

During an upgrade of NPS, the following error occurs: ERROR: Query History User is 'ADMIN'. This is not supported during upgrade/downgrade. You must disable Query History, or change the configuration such that USER is not 'ADMIN'.

Cause

The error occurs because of a product change to tighten security. Although the initial release of Query History supported the database being owned by the ADMIN user, this is no longer supported.

Resolving The Problem


To resolve this issue, do the following:

1. Determine the name of the existing query history configuration using one of the following commands:
nzsql -c "show configuration"
or
nzsql -c "show history configuration"
Note the CONFIG_NAME and CONFIG_DBNAME from this output. For this example we will use ALL_QHIST as the CONFIG_DBNAME and QHIST as the CONFIG_NAME.

2. Create a configuration in which query history is disabled.
nzsql -c "CREATE HISTORY CONFIGURATION hist_disabled HISTTYPE NONE;"

3. Create a database user which has the necessary permissions to own the new configuration. For this example, we will create a user called qhist_user.
nzsql -c "create user qhist_user with password 'qhistpw';
nzsql -c "grant list on database to qhist_user"
nzsql -c "grant all admin to qhist_user"

4. Update the configuration to use the disabled configuration.
nzsql -c "Set history configuration hist_disabled"

5. Stop and restart the database so that NPS loads the disabled query history.
nzstop
nzstart

6. Change the owner of the existing query history database to the new user, qhist_user.
nzsql -c "alter database all_qhist owner to qhist_user"

7. Change the user of the existing query history from ADMIN to the new user, qhist_user.
nzsql -c "alter history configuration qhist user qhist_user password 'qhistpw';

8. Revert the configuration to use the initial configuration which now has a non-ADMIN user, qhist_user.
nzsql -c "set history configuration qhist"

9. Stop and restart the database to re-enable query history with the new user.
nzstop
nzstart

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

Historical Number

NZ844206

Document Information

Modified date:
17 October 2019

UID

swg21515668

Page Feedback