how to granr reorg privilege to any db2 user

[ikhtear]

I'm very new at db2 i'd like to grant reorg privilege to an user i tried with the following command
db2 => grant reorg on database DB_NAME to USR_NAME;

but it shows following error. can anyone help me on this

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "reorg" was found following "grant ". Expected
tokens may include: "READ". SQLSTATE=42601

[vijay2082]

Hi,

FYI

There is no reorg privilege as such. The error shows it all.

Reorganizing tables online
An online or inplace table reorganization allows users to access the table while it is being reorganized.

You must have SYSADM, SYSCTRL, SYSMAINT, or DBADM authority, or you must have CONTROL privilege on the table to reorganize a table. You must have a database connection to reorganize a table.
You can perform an inplace table reorganization using a CLP command, using an SQL call statement, or through a DB2 API.
To reorganize a table online using the CLP, issue the REORG TABLE command using the INPLACE option :
db2 reorg table test.employee inplace

** After reorganizing a table, you should collect statistics on the table so that the optimizer has the most accurate data for evaluating query access plans

Cheers, Vijay

[ikhtear]

Hi vijay,

Thanks for information

i have give privilege as below to the group like below and restart db

update dbm cfg using SYSMON_GROUP user_group

and put the user on that group

But it shows the following error
SQL2214N The user does not have the authority to run the REORG utility on
table "TEMP".

Can you please help me on this regard

Regards,
Ikhtear

[vijay2082]

Hi,

Are you using domain user groups ? Still the particular user doesn't have the privilege to perform reorg for the table.

What I will suggest you to give a privilege like one below to the particular user and paste the exact command and their output if you further get this error.

Step 1 ) db2 "connect to <DB_NAME>"
Step 2) db2 "grant DBADM on database to user <USER_NAME>"

Step 3) now connect to that database with user in step 2

db2 connect to <DB_NAME> USER <USER_NAME> password <USER_PWD>

Step 4) Once you connect to the database with the user successfuly, execute your reorg command.

Paste the output for each step if you have further issues.

Cheers, Vijay

[ikhtear]

Thanks vijay for your reply. i got it. i dont like to give dbadm to other than inst user.

[vijay2082]

Hi,

If you don't want the DBADM then you can grant the control privilege on the table to individual user.

CONTROL
Grants:
All of the appropriate privileges in the list, that is:
ALTER, CONTROL, DELETE, INSERT, INDEX, REFERENCES, SELECT, and UPDATE to base tables
CONTROL, DELETE, INSERT, SELECT, and UPDATE to views
ALTER, CONTROL, INDEX, and REFERENCES to nicknames
The ability to grant the above privileges (except for CONTROL) to others.
The ability to drop the base table, view, or nickname.
This ability cannot be extended to others on the basis of holding CONTROL privilege. The only way that it can be extended is by granting the CONTROL privilege itself and that can only be done by someone with SYSADM or DBADM authority.

The ability to execute the RUNSTATS utility on the table and indexes.
The ability to execute the REORG utility on the table.
The ability to issue the SET INTEGRITY statement against a base table, materialized query table, or staging table.
The definer of a base table, materialized query table, staging table, or nickname automatically receives the CONTROL privilege.

The definer of a view automatically receives the CONTROL privilege if the definer holds the CONTROL privilege on all tables, views, and nicknames identified in the fullselect.


Cheers, Vijay