[-9205]: System error: AK Catalog information not found / CONSTRAINT

[Former Member]

Hello all,

We get the following error (in MaxDB Version 7.7.06.09):

[-9205]: System error: AK Catalog information not found:00000000000037BD000A0011

... when reading the constraint definitions for a specific table like this:

select * from constraints where tablename='TABNAME'

This error happens since we made some meta data changes as follows:

rename column TABNAME.A to B

alter table TABNAME drop B

(also some other columns were added and some removed from table TABNAME)

I guess that this error happens because the statement "alter table TABNAME drop B" did not properly drop a constraint definition that should be removed because column 'B' was part of the constraint definition. It seems that the check for depending constraint definitions that usually removes these when a column is removed contained in the constraint definition, does not apply in some specific cases, probably because of a specific rename/add/remove-sequence.

I guess a workaround is to remove all depending constraint definitions manually, BEFORE the column is removed.

Unfortunately it seems impossible to do so afterwards.

Thanks for any comment on this.

Gabriel

[Former Member]

Hello,

does there meanwhile exist a solution for this problem?

We have the same problem in a MaxDB 7.9.08.32

Trying:

select * from constraints where tablename='TABLENAME'

results an error:

Error Executing 'select * from constraints where tablename='TABLENAME'' SAP DBTech JDBC: [-9205]: System error: AK Catalog information not found:000000000003F5F5000A000F

We get this error for every table with a primary key.

It is also not possible to use the loader to export a table. this leads to the following messages in loader.log:

// *

// M    Import    PACKAGE x'100000006366350C180F00007C4500008347E6710B700E9A'

// *

// M    Execute   PACKAGE  to transform  unlocked CATALOG

// *

// M    Number of TABLES   to transform: 398

// *

// M    Number of VIEWS    to transform: 1

// *

// M    Number of SYNONYMS to transform: 32

// *

SELECT CONSTRAINTNAME, DEFINITION, CONSTRAINTTYPE FROM DOMAIN.CONSTRAINTS WHERE  SCHEMANAME = 'PERSONALADM' AND TABLENAME = 'TABLENAME'

// *

// E -25392:    '[SAP AG][SDBODBC DLL][SAP MaxDB] General error;-9205 POS(1) System error: AK Catalog information not found:　　　　　'

// M    Number of TABLES   transformed : 393

// *

// M    Releasing user connection (USER: 'DBA')

Any help is highly appreciated!

Bernhard

[thorsten_zielke]

Hello Bernhard,

maybe the database catalog has corrupt/invalid entries.

I would suggest that you verify the catalog and in case it returns an error let me know the result list:

1. Connect as SYSDBA user e.g. 'dba'
2. util execute check catalog
3. select * from domain.parameters

Important: If step (2) returns the error '300', you need to run a select on the domain.parameters to get the detailed results. But you must use the same SQL session for that query  or that information would be lost. Therefore, you might want to use an SQL editor for this like e.g. Database Studio or if you use dbmcli, then keep the SQL session connected.

Regards,
Thorsten

[Former Member]

Hallo Thorsten,

thank you for your quick answer.

I tried in dbmcli:

util_execute check catalog

Result:

-24988,ERR_SQL: SQL error

300,I

In Database Studio as dba:

check catalog

Result:

Error Executing 'check catalog' [300]: INCONSISTENCIES FOUND : 1

In Database Studio as dba - same session:

select * from domain.parameters

Result:

ACTION;OBJECTTYPE;PARAM1;PARAM2;PARAM3;PARAM4;PARAM5

USAGE;LONELY TABLE REF;user;S_RKA_KOSTENSTELLE;000000000003CD88;;

The reported object S_RKA_KOSTENSTELLE was a sequence that was deleted some time ago.

Do you know, how we can repair the catalog?

Regards,

Bernhard

[thorsten_zielke]

Hello Bernhard,

hmm... the 'select * from parameters' includes the tableid of the identified object, in this case it is '000000000003CD88', which unfortunately is different from the tableid you have reported the error for ('000000000003F5F5').

This very likely means that the 'check catalog' has identified a minor inconsistency on S_RKA_KOSTENSTELLE, but not related to your current problem.

The error on 'TABLENAME' has not been detected by the 'check catalog' check, which means it is an error we have not yet encountered and therefore not included in our checks.

How to proceed:

1.
run a 'check catalog with update' as dba user. This should drop the reference to the already deleted sequence S_RKA_KOSTENSTELLE.
Afterwards, a simple 'check catalog' should show no further errors.

2.
a)
Regarding your initial error, verify that indeed table 'TABLENAME' is affected by using the following SQL:
select * from domain.tables where tableid = x'000000000003F5F5'
The result should include the tablename of that object (which is expected to be TABLENAME).

b)
I would assume that you cannot access any constraints set on that table anymore (because of the catalog error). The question is: Do you know, what the current state is supposed to be? Have you set constraints which are now incorrectly missing or have there been valid constraints in the past, which were recently deleted and there should not be any constraints left on that table?

c)
Am I correctly assuming that you are or were using 'domains' for your constraints on that table (with 'domains' you can refer to a predefined constraint and use it on different tables...)?
 
Regards,
Thorsten

[Former Member]

Hello Thorsten,

as you already supposed:

1.

> check catalog with update

Statement 'check catalog with update' successfully executed in 398 ms

> check catalog

Statement 'check catalog' successfully executed in 378 ms

But the loader still reports the same error.

2.

a)

select * from domain.tables where tableid = x'000000000003F5F5'

Returns the table that is shown in the loader.log

b)

I'm afraid I cannot access any primary or foreign key contraints on any table.

If I try to open "Definition" in Database Studio for any table with a primary key,

I get an error. To verify this I created a demo table with primary key and on without.

The table with the key cannot be displayed. The definition of the other one is shown

without problems.

Additionally I wrote a small jdbc-Programm that tries to execute

metadata.getPrimaryKeys(null, schema, table);

and

metadata.getImportedKeys(null, schema, table)

They both lead to SQL-Errors for every table:

com.sap.dbtech.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [-9205]: System error: AK Catalog information not found:000000000003F5F5000A000F

    at ...

    at com.sap.dbtech.jdbc.DatabaseMetaDataMaxDB.getPrimaryKeys(DatabaseMetaDataMaxDB.java:298)

    at com.sap.dbtech.jdbc.trace.DatabaseMetaData.getPrimaryKeys(DatabaseMetaData.java:582)

All of the messages contain the same number - what seems to be a tableid.

So all the problems seem to have the same source.

Domains were not used in the table definition, but lots of check-constraints, one foreign key

and of course the primary key. A number of fields were added and 2 deleted a few weeks ago.

I now decided to export all the data of that table.

Dropped the table and recreated it.

I just started the test program and everything seems fine now!

Thanks a lot.

Regards,

Bernhard

[thorsten_zielke]

Hello Bernhard,

yes, recreating the table solves the problem, however it does not fix the bug which led the catalog become corrupt...
But you mentioned that the error occured on any table with a primary key. Do you have 'named primary keys' defined? I am asking, because these named primary keys are handled like a constraint in MaxDB, so maybe your problem was not with constraints in general, but the named primary keys?
That info would help us in trying to recreate the bug.

Regards,
Thorsten

[Former Member]

Hello Thorsten,

I'm also curious, what the source of our problem was.

Sad, that the catalog check did not recognize the problem.

I'm not aware of any tables with named primary keys.

There was none on the table that led to the problem.

I tried:

select * from constraints

where constrainttype = 'KEY' and  constraintname <> 'SYSPRIMARYKEY'

It returned nothing.

Regards,

Bernhard

[thorsten_zielke]

Hello Bernhard,

we might get closer in finding the bug if you could upload your complete database catalog for that user (includes all database object definitions, but not the data).

Doing so would mean to extract the database catalog via the 'loadercli' command line tool:

1. loadercli -d <yourdatabase> -u schemauser,password
2. export user catalog outstream file '<someFileName>'

And here is a valid upload link:

https://mdocs.sap.com/mcm/public/v1/open?shr=I0BR_ij2xY7tDHqU6KShrP9eCdVSMwiBLK-Kxflo-fY

Regards,
Thorsten

[Former Member]

I just uploaded the catalog.

Regards,

Bernhard

[thorsten_zielke]

Thank you - but now I am a bit confused. The error 'Catalog information not found' occurred when accessing the object 'TABLENAME'. Yet when I check the catalog file you have uploaded, it does not include any 'TABLENAME' table. Have you rebuild that table with a new name or have you permanently deleted it?

Regards,
Thorsten

[Former Member]

Sorry, I replaced the real name in the first posting.

The real name of the table is 'RKA_ANTRAGBEARBEITUNG'

Regards,

Bernhard

[thorsten_zielke]

Hi Bernhard,

thank you for uploading the catalog file. Unfortunately it did not give us any new insights about why the catalog should have become corrupted since the affected table 'RKA_ANTRAGBEARBEITUNG' did not have any unusual constraints set.

We might have found the bug with a database restart followed by a 'default' kernel trace plus 'select' option enabled while the catalog was still corrupt, but now it is too late for that since you had that table already dropped and rebuild.

We have added this incident to our bug tracking tool per PTS1254412, although at this point we most likely do not have enough information to fix it:

"Error -9205 "System error: AK Catalog information not found: 000000000003F5F5000A000F" at

SELECT CONSTRAINTNAME, DEFINITION, CONSTRAINTTYPE  FROM DOMAIN.CONSTRAINTS  WHERE SCHEMANAME = <schemaname> AND TABLENAME = <tablename>

Check catalog does not show any problem.

Problem is probably a lost catalog record for some constraint information (see cak_eviewdesc in a44constraint_into_moveobj)."

Regards,
Thorsten

[Former Member]

Hello Thorsten,

that`s no problem. If it helps I can set up a local instance and import a backup.

Regards,

Bernhard

[thorsten_zielke]

Hello Bernhard,

yes, that would help indeed.

Here is what you would need to to:
1. Restart the database (to ensure that the command is not already cached)
2. Activate the kernel trace with options 'DEFAULT' and 'SELECT'
-> e.g. 'dbmcli ... trace_on TRACE_DEFAULT TRACE_SELECT'

3. Trigger the error

4. Flush the trace (so that the trace buffer gets written from memory to a file)

-> 'dbmcli ... trace_flush'

5. convert the flushed binary trace file to text

-> 'dbmcli ... trace_prot akbmnx'
6. Trace will stay active until disabled (even if database was restarted), so do not forget to disable

-> 'dbmcli ... trace_off all'

Regards,
Thorsten

PS:
the kernel trace can also be activated via database studio GUI, if you prefer the graphical frontend

PPS:
If you are unsure about the current trace state, use 'trace_show' to display a trace status list

[Former Member]

Hallo,

do you remember the exact sequence of DDL done for this table?

And could you provide it to the forum or to me directly, if you prefer this because of security reasons?

Was the renamed column part of a constraint which contained other columns as well?

With some further info I hope to find the reason, maybe the bug can be fixed and perhaps some

workaround found.

Thank you in advance

Elke

[Former Member]

Hello Elke

Unfortunately I didn't have the exact DDL statements, but I had the same or a similar problem several times again now and I can describe as best as possible when it produced that error [-9205]:

When I drop a column without dropping a constraint that refers to that column before, it happened several times: The error -9205 is caused then afterward when trying to read the constraint definitions for this table (after the column was dropped, with SELECT * FROM CONSTRAINTS WHERE TABLENAME='TABNAME').

The same happened for columns that were used in foreign keys: When dropping the column without dropping the foreign key definition that uses this column before, error -9205 is caused when trying to read the foreign key columns for that table (SELECT * FROM FOREIGNKEYCOLUMNS WHERE TABLENAME='TABNAME').

AFAIK the DB usually drops constraints itself implicitly when a column is dropped which is used within a constraint definition. And when it is used in a foreign key definition, the DB usually refuses to drop that column. Obviously, in our example, this was not the case (neither for foreignkeycolumns nor for columns contained in constraint-definitions): dropping the column was possible.

A workaround was to check first if there are any constraints and/or foreign keys for the column that should be dropped and first remove them manually.

Best regards,

Gabriel