IBM Db2 on Cloud script creating tables in the wrong schema

Question

On IBM Db2 on Cloud I have imported a script. I created a new schema under which I want to have the new tables created, but when I run the script, it keeps trying to create the tables in a previous schema. Not sure how to get the scripts to create the tables in the new schema.

I have tried the below script without the .SQL_GROUPING_SORTING and it tries to add the tables to a different schema. I have changed the default schema in the Run SQL window within db2 to SQL_GROUPING_SORTING and am now getting the error

""KZF72118" does not have the privilege to perform operation "IMPLICIT CREATE SCHEMA".. SQLCODE=-552, SQLSTATE=42502, DRIVER=4.26.14"

DDL statement for table 'HR' database:

CREATE TABLE EMPLOYEES.SQL_GROUPING_SORTING (
                            EMP_ID CHAR(9) NOT NULL, 
                            F_NAME VARCHAR(15) NOT NULL,
                            L_NAME VARCHAR(15) NOT NULL,
                            SSN CHAR(9),
                            B_DATE DATE,
                            SEX CHAR,
                            ADDRESS VARCHAR(30),
                            JOB_ID CHAR(9),
                            SALARY DECIMAL(10,2),
                            MANAGER_ID CHAR(9),
                            DEP_ID CHAR(9) NOT NULL,
                            PRIMARY KEY (EMP_ID));
                            
  CREATE TABLE JOB_HISTORY.SQL_GROUPING_SORTING (
                            EMPL_ID CHAR(9) NOT NULL, 
                            START_DATE DATE,
                            JOBS_ID CHAR(9) NOT NULL,
                            DEPT_ID CHAR(9),
                            PRIMARY KEY (EMPL_ID,JOBS_ID));
 
 CREATE TABLE JOBS.SQL_GROUPING_SORTING (
                            JOB_IDENT CHAR(9) NOT NULL, 
                            JOB_TITLE VARCHAR(15) ,
                            MIN_SALARY DECIMAL(10,2),
                            MAX_SALARY DECIMAL(10,2),
                            PRIMARY KEY (JOB_IDENT));

CREATE TABLE DEPARTMENTS.SQL_GROUPING_SORTING (
                            DEPT_ID_DEP CHAR(9) NOT NULL, 
                            DEP_NAME VARCHAR(15) ,
                            MANAGER_ID CHAR(9),
                            LOC_ID CHAR(9),
                            PRIMARY KEY (DEPT_ID_DEP));

CREATE TABLE LOCATIONS.SQL_GROUPING_SORTING (
                            LOCT_ID CHAR(9) NOT NULL,
                            DEP_ID_LOC CHAR(9) NOT NULL,
                            PRIMARY KEY (LOCT_ID,DEP_ID_LOC));

Paul Vernon Paul Vernon · Accepted Answer · 2020-10-12T12:30:18

With the Db2 on Cloud Lite Plan

The Lite plan uses one database schema.

So the only schema you can use is the one that matches your user name. In your case this would be KZF72118

Create your tables with out a schema name, and they will be created in schema KZF72118.

You would need to use one of the other plans to remove this restriction

IBM Db2 on Cloud script creating tables in the wrong schema

1 Answers