Authorization error running db2 command line with db2inst1

Question

I need to enable the COLUMNAR setting in DB2 Express Edition on Docker. For that to work I need to set INTRA_PARALLEL ON at the instance or database level.

I connect to the db2 command with db2inst1 that is the instance owner, but I'm getting an error saying that the user is root. How to fix this problem?

db2 => connect to bank0002 user db2inst1 using xxxxx
 Database Connection Information
 Database server = DB2/LINUXX8664 11.5.4.0
 SQL authorization ID = DB2INST1
 Local database alias = BANK0002
db2 => UPDATE DBM CFG USING INTRA_PARALLEL ON
SQL5001N "ROOT" does not have the authority to change the database manager 
configuration file.

Answer 1

update dbm cfg is run by your user in the sh, not the user you connected as. You can attach to the instance as a particular user:

db2 attach to instance db2inst1 user db2inst1 

or:

db2 attach to instance db2inst1 user db2inst1 using <password>

and then:

db2 update dbm cfg using INTRA_PARALLEL YES

Documentation can be founds at ATTACH

In addition to that, I find it much easier to run db2 directly from the sh (as I did above) instead of starting db2 clp separately. You can use the sh as a host language and for example do loops over a resultset:

#> db2 connect to mydb
#> for t in $(db2 -x "select ..."); do
 echo "The table is ${t}"
 done 

Of course just a silly example, but it is a powerful mechanism for running db2 from a sh script

• ps0604: Thanks, how do I change the sheapthres configuration parameter as explained here? I tried unsuccessfully db2 UPDATE DATABASE MANAGER CONFIGURATION sheapthres 0
• Lennart - Slava Ukraini: You do that exactly the same, do you encounter a problem with that?
• ps0604: I'm getting: SQL0104N An unexpected token "sheapthres" was found following "CONFIGURATION". Expected tokens may include: "MEMBER". SQLSTATE=42601
• Lennart - Slava Ukraini: Oh, try CFG istead of CONFIGURATION, or the shorter version db2 update dbm cfg using .... You can also stack several changes after each other
• ps0604: Same problem. I'm getting an error when trying to run a SELECT statement on a columnar table: [57011]: Sort memory cannot be allocated to process the statement. Reason code = "3".. SQLCODE=-955, SQLSTATE=57011, that configuration seems to be the problem