469,341 Members | 7,095 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,341 developers. It's quick & easy.

how to granr reorg privilege to any db2 user

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
Mar 6 '11 #1
5 9003
vijay2082
112 100+
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
Mar 6 '11 #2
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
Mar 7 '11 #3
vijay2082
112 100+
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
Mar 7 '11 #4
Thanks vijay for your reply. i got it. i dont like to give dbadm to other than inst user.
Mar 7 '11 #5
vijay2082
112 100+
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
Mar 7 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

27 posts views Thread by Raquel | last post: by
2 posts views Thread by TomHorner | last post: by
16 posts views Thread by andy.standley | last post: by
9 posts views Thread by mike_dba | last post: by
9 posts views Thread by Mikael | last post: by
12 posts views Thread by wheels619 | last post: by
33 posts views Thread by JamesB | last post: by
reply views Thread by bwmiller16 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.