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

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

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.

Solution

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.