A Too Common Problem, -497, The OBIDs Maxed Out | Relief is Here

[Ed_Arnold]

Every so often I see the problem...

"-497 THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN EXCEEDED FOR DATABASE database-name"

...which brings OnDemand processing to a halt.
__________

[write up no longer available]

The best write up on this that I have read is a column by IBM DB2 specialist Vikram Saraswathi:

  http://it.toolbox.com/blogs/db2-dba-diary/the-problem-with-max-internal-identifiers-obids-34817

I won't copy the article here, but if you're not monitoring OBID usage "an ounce of prevention..."

Ed Arnold

[Alessandro Perucchi]

Thanks for the tip!

Cheers,
Alessandro

[Ed_Arnold]

As we say around here, "The easy button has been pressed."

IF you're on CMOD on z/OS 8.4.1 or above

THEN if you're getting the -497, you can use the incredibly easy to use new facility to have all new tables created in a different DB2 database.


Note:  the new database has to be in the same DB2 subsystem.


Just go to the Application Group tab ---> Advanced

In the lower right corner you'll see a field, "Database Name"

Just put in the new database name, ensure that the user has permission to write to that database, and start loading.

Any new data table will be created in the new database.

Yes, it really is that easy to now get past the -497 problem.

Ed

[Ed_Arnold]

Finally got a round tuit and tried the SELECT statements in the referenced article on my personal 8.4.1 sandbox.

Note:  all of the numbers should be low ---- I don't have thousands of tables like a production shop.

1. select DBID from SYSIBM.SYSDATABASE WHERE NAME=DATABASE NAME;
2. SELECT MAX(OBID) FROM SYSIBM.SYSTABLES WHERE DBID= THE DBID WHICH YOU GOT FROM ABOVE sysdatabase QUERY
3. SELECT MAX(OBID) FROM SYSIBM.SYSTABLESPACE WHERE DBID= THE DBID WHICH YOU GOT FROM ABOVE sysdatabase QUERY
4. SELECT MAX(OBID) FROM SYSIBM.SYSINDEXES WHERE DBID= THE DBID WHICH YOU GOT FROM ABOVE sysdatabase QUERY

SELECT DBID FROM SYSIBM.SYSDATABASE WHERE NAME='ARS841DB'
 --- 276

SELECT MAX(OBID) FROM SYSIBM.SYSTABLES WHERE DBID=276
 --- 4403

SELECT MAX(OBID) FROM SYSIBM.SYSTABLESPACE WHERE DBID=276
 --- 4401

SELECT MAX(OBID) FROM SYSIBM.SYSINDEXES WHERE DBID=276
 --- 4412

Are any of the last three queries showing a total OBID count close to 32700?
 --- No, as is to be expected.


For you production shops out there, this really is an ounce of prevention.

Ed

[Jon.Bauer@bcbsks.com]

Our OBID limit is nearing.  What steps need to be taken prior to going to the Application Group tab and adding the new Database Name?

[Ed_Arnold]

Our OBID limit is nearing.  What steps need to be taken prior to going to the Application Group tab and adding the new Database Name?

Jon - it's so easy it's almost scary.

This is the biggest consideration:  "Note:  the new database has to be in the same DB2 subsystem."

Then the only hitch - if you call it that - that I had the only time I had to do this was GRANTs for permission had to be run.

Ed