Db2 Lite plan on the IBM Cloud table not accessible after ALTER COLUMN performed

Question

I had to perform an ALTER on a table in Db2 on IBM Cloud (DashDb). Thereafter, I tried to

ALTER TABLE REFT_BRAND_DIM ALTER COLUMN BRAND_CD SET NOT NULL;

Now that table is inaccessible. For example:

SELECT * FROM RQG40283.REFT_BRAND_DIM

Error message
Operation not allowed for reason code "7" on table "RQG40283.REFT_BRAND_DIM".. SQLCODE=-668, SQLSTATE=57016, DRIVER=4.26.14

This error is The table is in the reorg pending state. This can occur after an ALTER TABLE statement containing a REORG-recommended operation. However, neither REORG TABLE RQG40283.REFT_BRAND_DIM or REORG TABLESPACE rqg40283space1 seems to be recognized.

I would appreciate whatever help someone could provide.

Answer 1

Dropping and recreating the table is an obvious workaround. You can preserve data by either using a new table-name, and copying data from the old table, or by by using a temporary table.

On my Db2-cloud lite plan, it lets me REORG the table after making table alterations.

Example:

call sysproc.admin_cmd('REORG TABLE REFT_BRAND_DIM' );

Also, if have have a local Db2 runtime client, or the Db2 fat-client, whose version+fixpack exactly matches the version+fixpack of your Db2-lite instance, then the CLP reorg also works with the Db2-lite plan currently like this:

db2 connect to bludb user .... using ...

db2 reorg table reft_brand_dim

The command line only works when the CLI packages do not need to be bound or rebound on the Db2-lite plan, as such rights are not granted with the Db2-lite plan.

Answer 2

Seems to me like you need to drop the table. You said you are on the Db2 Lite plan. In that case you do not have any administration privileges and the service is only for some light testing. Thus, it should be easy to recreate the table.