Hi,

I have around 10,000 tables in my db2 database. I have to grant a user insert privilege to all the tables in the database. Please let me know the command to perform this grant operation in a single shot.

Regards,
Santhosh

You cannot do that with one command, but you can build a script that will do that:

db2 connect to XXXXX

db2 "select 'grant insert on table '||rtrim(tabschema)||'.'||rtrim(tabname)||' to authorization-name;' from syscat.tables where tabschema <> 'SYSIBM' and type = 'T'"

Pipe the output to a file. Edit the file a bit and submit it to grant the inserts.

Thanks Marcus. I am able to generate the script and grant insert privileges to the particular user to all the tables in the database.

Marucs,

you used rtrim(tabschema) for character string values. If I have to pass integer variable say "tbspaceid" in the below command I am facing the errors.

C:\>db2 "select 'list tablespace container for '||rtrim(tbspaceid)||' with detail' from syscat.table
spaces"
SQL0440N No authorized routine named "RTRIM" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

Regards,
Santhosh

You need to convert it to character:

db2 "select 'list tablespace containers for '||rtrim(cast (tbspaceid as char(3)))||' show detail' from syscat.tablespaces"

Thanks marcus. I am able to get the output.