0

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.
Improve this question

1 Answer 1

Reset to default
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

Improve this answer
9
  • Thanks, how do I change the sheapthres configuration parameter as explained here? I tried unsuccessfully db2 UPDATE DATABASE MANAGER CONFIGURATION sheapthres 0
    – ps0604
    Jan 2, 2021 at 15:04
  • You do that exactly the same, do you encounter a problem with that?
    – Lennart - Slava Ukraini
    Jan 2, 2021 at 15:09
  • I'm getting: SQL0104N An unexpected token "sheapthres" was found following "CONFIGURATION". Expected tokens may include: "MEMBER". SQLSTATE=42601
    – ps0604
    Jan 2, 2021 at 15:10
  • Oh, try CFG istead of CONFIGURATION, or the shorter version db2 update dbm cfg using .... You can also stack several changes after each other
    – Lennart - Slava Ukraini
    Jan 2, 2021 at 15:14
  • 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
    – ps0604
    Jan 2, 2021 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.