DB2 400 drop column

Question

I want to drop a column called id which is an auto incrementing PK.

The SQL:

alter table "CO88GT"."XGLCTL" drop column id cascade;

And I get:

Error: [SQL0952] Processing of the SQL statement ended.  Reason code 10.

SQLState:  57014

ErrorCode: -952

I could be wrong but I think it has something to do with preventing the table from losing data. To get around this issue I need to create a new table without the column and copy the data from the old table into the new table and then replace the old table with the new table.

Squier

I suggest placing the ODBC job on the AS/400 into debug mode to see if anything shows up in the job log. To find the ODBC job, use WRKACTJOB JOB(QZDASOINIT) and find the job with your user id on it. The try STRSRVJOB JOB(your job number/user/name) and then STRDBG UPDPROD(*YES). When a job is in debug mode, you get a lot more information in the job log when running SQL statements. I don't know for sure, but you may get something useful from that. – Chattel

Answer 1

Info

AS400 is giving you a warning (inquiry message) because of possible data loss, asking you to Cancel or Ignore the requested operation. So, beacuse of this being a interactive request, over JDBC/ODBC you cannot type 'I' to ignore, and AS throws you an ErrorCode: -952 with SQLState: 57014 and Reason code 10.

In the documentation of SQL0952 says:

Message Text:   Processing of the SQL statement ended. Reason code &1.
Cause Text:     The SQL operation was ended before normal completion. The reason code is &1. Reason codes and their meanings are:

* 1 - An SQLCancel API request has been processed, for example from ODBC.
* 2 - SQL processing was ended by sending an exception.
* 3 - Abnormal termination.
* 4 - Activation group termination.
* 5 - Reclaim activation group or reclaim resources.
* 6 - Process termination.
* 7 - An EXIT function was called.
* 8 - Unhandled exception.
* 9 - A Long Jump was processed.
* 10 - A cancel reply to an inquiry message was received.
* 11 - Open Database File Exit Program (QIBM_QDB_OPEN).
* 0 - Unknown cause.

If you are using JDBC and the SQL error isn't self-explanatory, you can make a JDBC connection with parameter 'errors=full', which will give much more info on the error. For other connection parameters see this.

example connection string:

jdbc:as400://serverName;libraries=*libl;naming=system;errors=full;

With that connection the resulting error would be like this:

Error: [SQL0952] Processing of the SQL statement ended.  Reason code 10.
Cause . . . . . :   The SQL operation was ended before normal completion.
The reason code is 10.
Reason codes and their meanings are:
1 -- An SQLCancel API request has been processed, for example from ODBC.
2 -- SQL processing was ended by sending an exception.
3 -- Abnormal termination.
4 -- Activation group termination.
5 -- Reclaim activation group or reclaim resources.
6 -- Process termination.
7 -- An EXIT function was called.
8 -- Unhandled exception.
9 -- A Long Jump was processed.
10 -- A cancel reply to an inquiry message was received.
11 -- Open Database File Exit Program (QIBM_QDB_OPEN).
0 -- Unknown cause.
Recovery  . . . :   If the reason code is 1, a client request was made to cancel SQL processing.  For all other reason codes, see previous messages to determine why SQL processing was ended.

SQLState:  57014
ErrorCode: -952

---

The solution

So finally, if you cannot use STRSQL, another solution is to use iSeries Navigator, to be exact its "Run SQL scripts" (it is usually here --> "%Program Files%\IBM\Client Access\Shared\cwbundbs.exe").

But first of all you have to add a system reply parameter (only once per machine)

ADDRPYLE SEQNBR(1500) MSGID(CPA32B2) RPY('I')

This is done in "green screen". This sets a deafult answer ('I') on CPA32B2 inquiry message. The CPA32B2 is an internal massage id, which is tied to an drop column operation.

(It actually doesn't have to be done in "green screen", use it like CHGJOB command. Example :

cl: ADDRPYLE SEQNBR(1500) MSGID(CPA32B2) RPY('I');

)

Now you can start "Run SQL scripts", the first command to run is:

cl: CHGJOB INQMSGRPY(*SYSRPYL);

this changes the current job parameter INQMSGRPY, to *SYSRPYL. *SYSRPYL causes to look if exists a system reply parameter when an inquiry message should be displayed.

Now you can run your alter which drops the column.

---

Unfortunately, I don't know how to drop a column, just using JDBC. If someone knows please let me know.

References:

• Understanding What Controls the Automatic Reply Function
• Replying to Run-Time Inquiry Messages
• Error in dropping column (forum post)
• How can I avoid SQL0952 on ALTER TABLE?

Smug

Thank you for this detailed and incredibly helpful answer. – Puglia

Answer 2

Finally I found a solution:

CALL QSYS2.QCMDEXC('ADDRPYLE SEQNBR(1500) MSGID(CPA32B2) RPY(''I'')');
CALL QSYS2.QCMDEXC('CHGJOB INQMSGRPY(*SYSRPYL)');


ALTER TABLE <tablename> DROP COLUMN <column_name>;                

Explorer

Answer 3

There isn't enough information in your error message to be sure, but dropping a primary key column is generally quite risky and the database correctly makes it difficult.

You likely have a foreign key constraint involving that column.

Don't drop the constraint and delete that column unless you're sure you know what you're doing.

Cerys

I can't see why that would block it since cascade was specified. I'm just mucking around in a test schema/library I set up so it isn't production data. Safe is good but when there is nothing to wreck of any consequence this is just annoying. – Squier

@Squier - Sorry, didn't see the cascade. If there's a foreign key constraint, that indeed should have dropped it for you. So I have no idea of the real problem. – Cerys

Answer 4

According to this post: http://bytes.com/topic/db2/answers/185467-drop-column-table

It is possible to drop a column using STRSQL in the green screen environment. I have access to this and it does work, but a client with a 400 does not have the licensed program to use STRSQL. The issue is that STRSQL will prompt if this is something I really want to do.

To get at the data I'm using SQuirrel SQL client with the JT400 JDBC driver... So I guess with the system insisting on prompting (and actually no way of getting the prompt even without STRSQL) it won't let me do it.

So I guess I'm stuck doing what I'm doing... creating a new table and copying the data and then swapping the tables.

Squier

STRSQL isn't necessary. A very simple REXX procedure can accept the ALTER TABLE DROP COLUMN statement and run it interactively just as STRSQL would. The inquiry message can then be answered. – Binky

Answer 5

The only way I found to get jdbc to work is to first manually change the default for this message. Then run your update application. In our case we use Liquibase. I could get the java's CommandCall to call ADDRPYLE and CHGJOB INQMSGRPY(*SYSRPYL), but it never actually allowed the alter table * drop column * to not give the follow error:

Error:

10 -- A cancel reply to an inquiry message was received.

Working Command:

CHGMSGD MSGID(CPA32B2) MSGF(QSYS/QCPFMSG) DFT('I')

Reference:

• http://knowledgebase.progress.com/articles/Article/9678

Arlenarlena

Rather than changing the system's default for the message description, copy the MsgD into a new *MSGF and change the new MsgD default. Then you can run OVRMSGF MSGF(QCPFMSG) TOMSGF(newMsgF) and CHGJOB INQMSGRPY(*DFT). That way the system's version is unchanged and only the job with the override ever sees the new default. This should also work for JDBC/ODBC as well as iSeries Navigator. You can run DLTOVR to remove the override and CHGJOB to reset default handling back to its original state after dropping the column. – Binky

Answer 6

green screen -- STRSQL will give you error msg to answer

alter table devlibsc/trklst drop column "ST"

Change of file TRKLST may cause data to be lost. (C I)

Importunity

To complete this, responding with I on the command line will ignore the message and continue to process the command. "C" I believe is cancel, and make no changes. – Toreutic