Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
System error when executing delete query with subselect
From: Johan Hallgren (jhallgrenapiworldwide.com)
Date: Thu Mar 30 2006 - 20:57:44 CST
I am working with the developers of the ActiveMQ message broker (www.activemq.org) to support MaxDB for its message persistence layer. So far, everything seems to be operating normally, with the exception of one statement. Among others, the persistence layer uses these two tables:
CREATE TABLE ACTIVEMQ_MSGS(ID INTEGER NOT NULL, CONTAINER VARCHAR(250) ASCII, MSGID_PROD VARCHAR(250), MSGID_SEQ INTEGER, EXPIRATION INTEGER, MSG LONG BYTE, PRIMARY KEY ( ID ) )
CREATE TABLE ACTIVEMQ_ACKS(CONTAINER VARCHAR(250) ASCII NOT NULL, CLIENT_ID VARCHAR(250) ASCII NOT NULL, SUB_NAME VARCHAR(250) ASCII NOT NULL, SELECTOR VARCHAR(250) ASCII, LAST_ACKED_ID INTEGER, PRIMARY KEY ( CONTAINER, CLIENT_ID, SUB_NAME))
Further, one of them has these three indexes:
CREATE INDEX ACTIVEMQ_MSGS_MIDX ON ACTIVEMQ_MSGS (MSGID_PROD,MSGID_SEQ)
CREATE INDEX ACTIVEMQ_MSGS_CIDX ON ACTIVEMQ_MSGS (CONTAINER)
CREATE INDEX ACTIVEMQ_MSGS_EIDX ON ACTIVEMQ_MSGS (EXPIRATION)
The offending statement is this:
DELETE FROM ACTIVEMQ_MSGS WHERE ( EXPIRATION<>0 AND EXPIRATION<100) OR ID <= ( SELECT min(ACTIVEMQ_ACKS.LAST_ACKED_ID) FROM ACTIVEMQ_ACKS WHERE ACTIVEMQ_ACKS.CONTAINER=ACTIVEMQ_MSGS.CONTAINER)
Regardless of any data in the tables, it fails with this message:
[SAP AG][LIBSQLOD SO][SAP DB] General error;-9404 POS(1) System error: AK System error: VAK724 1
Dropping the ACTIVEMQ_MSGS_EIDX index makes the statement run without error; dropping any of the other indexes has no effect.
Does anyone have any idea of what may cause this problem? I am running it in a UNICODE database instance running in a 7.5.00.24 kernel on Linux (specifically, I'm running the Debian Sarge package for MaxDB).
Thanks in advance for any insight!