Sql state 57014

[abercrombieande]

I am going crazy why cant I drop a column

ALTER TABLE DWUSRLIB.DMPROVDT  DROP COLUMN DIM_PROVDR_EXP_DATE;

SQL State: 57014
Vendor Code: -952
Message: [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.

I don't see any locks on the table and the job log isnt showing anything.

Update: I was trying to run the code through iSeries Nav. When I use STRSQL on the green screen I get a message back saying data might be lost but it runs ok once the message is answered. The table is empty.

[Mercury]

Re: Sql state 57014

Pls paste the relative CREATE TABLE.

[K2r400]

Re: Sql state 57014

Do you have a line with the CPA32B2 message in your system reply list ? (WRKRPYLE)
like this

        3282    CPA32B2  I           'iSeries Navigator'                  1

What is the response ?
Do you use the RPYL in your job ? INQMSGRPY(*SYSRPYL)

[kitvb1]

Re: Sql state 57014

If you're dropping a column, you should get this message

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

Check that the has been no change to the default reply or an auto-reply installed.

[abercrombieande]

Re: Sql state 57014

CREATE TABLE DWUSRLIB.DMPROVDT (
DIM_PROVDR_DIM_PROVDR_ID_SKEY FOR COLUMN DIMPVDIDN NUMERIC(9, 0) GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1
MINVALUE 0 NO MAXVALUE
NO CYCLE NO ORDER
CACHE 20 )
,
DIM_PROVDR_LOADED_DATE FOR COLUMN LODDTE DATE NOT NULL ,
DIM_PROVDR_PROVDR_ID_SKEY FOR COLUMN PVDIDN NUMERIC(9, 0) NOT NULL ,
DIM_PROVDR_TYPE_DESC FOR COLUMN PVDTYP CHAR(10) CCSID 37 NOT NULL ,
DIM_PROVDR_CODE FOR COLUMN PVDCDE CHAR(10) CCSID 37 NOT NULL ,
DIM_PROVDR_NAME FOR COLUMN PVDNME CHAR(100) CCSID 37 NOT NULL ,
DIM_PROVDR_BATCH_JOB_NUM FOR COLUMN BCHJOBNBR CHAR(20) CCSID 37 NOT NULL ,
CONSTRAINT DWUSRLIB.Q_PACDTADEV_DMPROVDR_DIMPVDIDN_00001 PRIMARY KEY( DIM_PROVDR_DIM_PROVDR_ID_SKEY ) )

RCDFMT DMPROVDR ;

LABEL ON TABLE DWUSRLIB.DMPROVDT
IS 'Dimension PROVIDER' ;

LABEL ON COLUMN DWUSRLIB.DMPROVDT
( DIM_PROVDR_DIM_PROVDR_ID_SKEY IS 'DimProviderID' ,
DIM_PROVDR_LOADED_DATE IS 'LoadedDate' ,
DIM_PROVDR_PROVDR_ID_SKEY IS 'ProviderID' ,
DIM_PROVDR_TYPE_DESC IS 'ProviderType' ,
DIM_PROVDR_CODE IS 'ProviderCode' ,
DIM_PROVDR_NAME IS 'ProviderName' ,
DIM_PROVDR_BATCH_JOB_NUM IS 'BatchJobNumber' ) ;

LABEL ON COLUMN DWUSRLIB.DMPROVDT
( DIM_PROVDR_DIM_PROVDR_ID_SKEY TEXT IS 'DIM_PROVDR_DIM_PROVDR_ID_SKEY' ,
DIM_PROVDR_LOADED_DATE TEXT IS 'DIM_PROVDR_LOADED_DATE' ,
DIM_PROVDR_PROVDR_ID_SKEY TEXT IS 'DIM_PROVDR_PROVDR_ID_SKEY' ,
DIM_PROVDR_TYPE_DESC TEXT IS 'DIM_PROVDR_TYPE_DESC:' ,
DIM_PROVDR_CODE TEXT IS 'DIM_PROVDR_CODE' ,
DIM_PROVDR_NAME TEXT IS 'DIM_PROVDR_NAME' ,
DIM_PROVDR_BATCH_JOB_NUM TEXT IS 'DIM_PROVDR_BATCH_JOB_NUM' ) ;

[abercrombieande]

Re: Sql state 57014

Do you have a line with the CPA32B2 message in your system reply list ? (WRKRPYLE)
like this

        3282    CPA32B2  I           'iSeries Navigator'                  1

What is the response ?
Do you use the RPYL in your job ? INQMSGRPY(*SYSRPYL)

Sequence number . . . . . . . . > 3282 1-9999
Message identifier . . . . . . . CPA32B2 Character value, *SAME, *ANY
Compare data:
Comparison data . . . . . . . 'iSeries Navigator'
Message data start position . 1 1-999, *SAME, *NONE
Message reply . . . . . . . . . 'I'

Additional Parameters

Dump the sending job . . . . . . *NO *SAME, *NO, *YES
Coded character set ID . . . . . *JOB *JOB, *SAME, *HEX...

[Mercury]

Re: Sql state 57014

Are you trying to drop a column that doesn't exist in the table as I don't see column DIM_PROVDR_EXP_DATE in the CREATE TABLE ?

ALTER TABLE DWUSRLIB.DMPROVDT  DROP COLUMN [B]DIM_PROVDR_EXP_DATE[/B];

[abercrombieande]

Re: Sql state 57014

Are you trying to drop a column that doesn't exist in the table as I don't see column DIM_PROVDR_EXP_DATE in the CREATE TABLE ?

ALTER TABLE DWUSRLIB.DMPROVDT  DROP COLUMN [B]DIM_PROVDR_EXP_DATE[/B];

It has since been dropped. I did a work around by running STRSQL.