DB2 10.1 Client throws SQL0552N running a script

db2

I am trying to run a sql script on a remote database using DB2 10.1 on AIX 6.1. I have granted my admin user (Adm101) on the database server SECADM. But when i run my script on the remote server with the db2 client (Client101) I get:

SQL0552N "Client101" does not have the privilege to perform operation "CREATE TABLE". SQLSTATE=42502

Adm101 is in listed in the database but Client101 is not. How can I create a table on the remote client?

Best Answer

You will need to grant Client101 CONNECT and CREATETAB on database in order to be able to create a table. It may also help to grant IMPLICIT_SCHEMA as well. And depending on the version of your client, you may either need to connect with an admin ID first, or grant BINDADD to your ID so it can bind version specific system packages.

db2 connect to mydb as admin101 using "password"
db2 grant connect,createtab,implicit_schema,bindadd on database to user client101
db2 connect reset

then

db2 connect to mydb as client101 using "other_password"
db2 create table .....

Related Solutions

Db2 – Adding User access to DB2

My gut feeling is that you did not update user "john's" profile to invoke the DB2 profile setup. If you do not do this, then you won't have "access" to DB2 per say, even though the binaries are there.

So what you want to do is write a script that checks for your DB2 instance "home" profile script in your case it would be /home/db2inst1/sqllib/db2profile and if it is there, to invoke it.

if [ -f /home/db2inst1/sqllib/db2profile ] ; then
     . /home/db2inst1/sqllib/db2profile
     if [ ! $? = 0 ] ; then
          echo "Error setting up DB2 instance access"
     else
          echo "DB2 instance access set up"
     fi
fi

Db2 – Sending a mail using DB2’s Proceude UTL_MAIL

Based on what you are trying to do you would want to set up the STMP server at the database level rather than the DAS level. You set it up at the DAS level if you want DB2 to email alerts to you. See the Information Center here.

You set it up at the database level if you want to use the UTL_MAIL module as you are wishing to use.

You would need to do the following:

db2 connect to mydb -- or whatever your database name is.
db2 update db cfg using smtp_server '<server or ip>:<port>' 
-- you can have a list of smtp servers if you want, just use a comma between them.
db2 connect reset

This setting takes affect immediately. Please see Information Center for more details.

Best Answer

Related Solutions

Db2 – Adding User access to DB2

Db2 – Sending a mail using DB2’s Proceude UTL_MAIL

Related Question