0

I am working on DB2 9.7 database. I was using SquirrelSQL for GUI purpose. However ever since I applied an alter command to one of my tables , I started facing problems with the table, and any further select queries asked for "reorg" of the table. to overcome this, I renamed the old table and created new table. However the create query didn't execute properly in Squirrel ,and so downloaded DBViewer for my STS(Spring Source Tool Suite.)I executed the create table query from DBViewer, but the issue now is neither am I able to access the newly created table from my JAVA code , nor from Squirrel.

I am completely clueless as to what could be the problem. Has anyone got any idea?

Following is the Structure of my table:

CREATE TABLE DB2ADMIN.CERT
(
    CERT_ID             CHAR(36) NOT NULL,
    CERT_CD          CHAR(1) NOT NULL,
    CERT_NBR            CHAR(10) NOT NULL,
    CERT_REQ_USR_CD_1           CHAR(10),
    CERT_REQ_USR_CD_2           CHAR(10),
    CERT_REQ_USR_CD_3           CHAR(10),
    CERT_REQ_USR_CD_4           CHAR(10),
    CERT_REQ_USR_TXT_1          VARCHAR(255),
    CERT_REQ_USR_TXT_2          VARCHAR(255),
    CERT_REQ_USR_TXT_3          VARCHAR(255),
    CERT_REQ_USR_TXT_4          VARCHAR(255),
    CERT_REQ_USR_TXT_5          VARCHAR(255),
    CERT_REQ_USR_TXT_6          VARCHAR(255),
    CERT_REQ_USR_TXT_7          VARCHAR(255),
    CERT_REQ_USR_TXT_8          VARCHAR(255),
    CERT_REQ_USR_TXT_9          VARCHAR(255),
    CERT_REQ_USR_DT             DATE,
    LAST_MDF_USER_ID            CHAR(25) NOT NULL,
    LAST_MDF_ACY_TS             TIMESTAMP(26,6) NOT NULL,
    CONSTRAINT SQL111017085116710 PRIMARY KEY (CERT_ID)
);

In my alter query I changed the datatype of CERT_NBR form INTEGER to CHAR

using the command;

ALTER TABLE CERT ALTER COLUMN CERT_NBR SET DATA TYPE INTEGER
pkn1230
  • 103
  • 1
  • 3
  • 15
  • what errors did you run into? what does the table look like and what did you change with ALTER? please provide more details – data_henrik Oct 23 '15 at 07:33
  • Look into IBM Manual [link](https://www-01.ibm.com/support/knowledgecenter/#!/SSEPGG_9.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000888.html) for more info on what you can do with ALTER and what not – PK20 Oct 23 '15 at 09:20

1 Answers1

2

any further select queries asked for "reorg" of the table.

This is a common occurrence after altering tables in DB2. It should be trivially solved by calling:

reorg table table-name;

This is a command-line command, rather than an sql statement, but you can call it via SQL with the admin_cmd procedure:

call sysproc.admin_cmd('reorg table table-name');

I'm not sure why you are unable to access the new table. The error message should help you resolve this. Some possibilities:

  • You created it with a different username than the one you are trying to access it with.
  • You never committed after the create table statement.
  • Something went wrong and the table ended up in an inoperable state.
  • thanx I was able to execute the query the help of admin_cmd – pkn1230 Oct 27 '15 at 07:26
  • could you please have a look at https://stackoverflow.com/questions/31004249/reorg-command-in-db2 – pkn1230 Oct 27 '15 at 11:43
  • @pkn1230, my answer (as well as the previous answer given to your question) appears to give the exact solution you need: `sysproc.admin_cmd`. Have you tried this? If you have and are still having trouble, update your question with details or ask a new one. –  Oct 27 '15 at 12:42