SQLCODE -497 THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN EXCEEDED FOR DATABASE

Manikandan Govindaraj

SQLCODE -497 THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN EXCEEDED FOR DATABASE

Hi All,

We got the below error for a QMF batch job

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

Explanation

The SQL statement cannot be executed because an internal identifier limit has been exceeded for the database. The cause of this error is due to one of the following:

  1. On a CREATE DATABASE statement, the limit of 65217 DBIDs has been exceeded.
  2. For all other statements, the limit of 32767 OBIDs has been exceeded for that database.
  3. If the database is a WORKFILE database, then one of the following reasons might apply:
    • The number of tablespaces in the WORKFILE database has exceeded the limit of 500.
    • The number of indexes defined on declared global temporary tables belonging to all agents on the local DB2®member has exceeded the limit of 10,000.
    • The number of tables and triggers belonging to a local agent exceeded the limit of 11,767. The tables in the WORKFILE database include work files, created global temporary tables, and declared global temporary tables.

We verified all the above options and deleted some obsolete tables and ran Modify recovery. It didn't work.

Has anyone come across this issue if so please advice..

 

 

 

Roy Boxwell

SQLCODE -497 THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN EXCEEDED FOR DATABASE
(in response to Manikandan Govindaraj)
Just deleting the tables is not enough... as has been often posted you must:
1) drop all old unused tables
2) reorg TS
3) modify recovery on db.ts to delete all ic’s
4) image copy again to get a recovery point

This also shrinks your DBD size.


Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

> On 26 Jul 2019, at 14:30, Manikandan Govindaraj <[login to unmask email]> wrote:
>
> Hi All,
>
> We got the below error for a QMF batch job
>
> -497 THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN EXCEEDED FOR DATABASE
>
> Explanation
>
> The SQL statement cannot be executed because an internal identifier limit has been exceeded for the database. The cause of this error is due to one of the following:
>
> On a CREATE DATABASE statement, the limit of 65217 DBIDs has been exceeded.
> For all other statements, the limit of 32767 OBIDs has been exceeded for that database.
> If the database is a WORKFILE database, then one of the following reasons might apply:
> The number of tablespaces in the WORKFILE database has exceeded the limit of 500.
> The number of indexes defined on declared global temporary tables belonging to all agents on the local DB2®member has exceeded the limit of 10,000.
> The number of tables and triggers belonging to a local agent exceeded the limit of 11,767. The tables in the WORKFILE database include work files, created global temporary tables, and declared global temporary tables.
> We verified all the above options and deleted some obsolete tables and ran Modify recovery. It didn't work.
>
> Has anyone come across this issue if so please advice..
>
>
>
>
>
>
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
> ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
Attachments

  • smime.p7s (3.9k)

Suresh Sane

SQLCODE -497 THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN EXCEEDED FOR DATABASE
(in response to Roy Boxwell)
As I recall, specifying the OBID is an option during CREATE TABLE. If all positive values have been used up, using a negative value could be a short term workaround.

I am NOT recommending doing this, but suggesting it as a possibility. Not even sure if it works today.

Thx
Suresh

________________________________
From: Boxwell, Roy <[login to unmask email]>
Sent: Friday, July 26, 2019 1:10 PM
To: [login to unmask email] <[login to unmask email]>
Subject: [DB2-L] - RE: SQLCODE -497 THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN EXCEEDED FOR DATABASE

Just deleting the tables is not enough... as has been often posted you must:
1) drop all old unused tables
2) reorg TS
3) modify recovery on db.ts to delete all ic’s
4) image copy again to get a recovery point

This also shrinks your DBD size.


Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675[cid:586e2b42-bd7c-4f94-be9b-5e114ceed9f4]
Fax +49 (0)211 96149-32[cid:acf25fe5-a62f-48e7-af44-0dfc4492c2a1]
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

On 26 Jul 2019, at 14:30, Manikandan Govindaraj <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Hi All,

We got the below error for a QMF batch job

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

Explanation

The SQL statement cannot be executed because an internal identifier limit has been exceeded for the database. The cause of this error is due to one of the following:

1. On a CREATE DATABASE statement, the limit of 65217 DBIDs has been exceeded.
2. For all other statements, the limit of 32767 OBIDs has been exceeded for that database.
3. If the database is a WORKFILE database, then one of the following reasons might apply:
* The number of tablespaces in the WORKFILE database has exceeded the limit of 500.
* The number of indexes defined on declared global temporary tables belonging to all agents on the local DB2®member has exceeded the limit of 10,000.
* The number of tables and triggers belonging to a local agent exceeded the limit of 11,767. The tables in the WORKFILE database include work files, created global temporary tables, and declared global temporary tables.

We verified all the above options and deleted some obsolete tables and ran Modify recovery. It didn't work.

Has anyone come across this issue if so please advice..







-----End Original Message-----
Attachments

  • Outlook-dxaqrayp.png (<1k)
  • Outlook-olrswnvk.png (<1k)

James Campbell

SQLCODE -497 THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN EXCEEDED FOR DATABASE
(in response to Manikandan Govindaraj)
Another possibility is to change the database? Since this is QMF, I presume the
Q.PROFILES.SPACE value is being used to store the table. Many years ago I was at a site
that every Sunday dropped the "oldest" set of databases, recreated them and their
tablespaces, and changed all the Q.PROFILES to point to the new tablespaces. So saved
tables were kept for a few weeks (current database, second newest, ..., oldest), and then
dropped.

In line with recent developments, since QMF V11, it is possible to specify DATABASE ?????,
so you get the one table per tablespace.

James Campbell

On 26 Jul 2019 at 5:30, Manikandan Govindaraj wrote:

> Hi All,
> We got the below error for a QMF batch job
> -497   THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN EXCEEDED FOR DATABASE 
> Explanation
> The SQL statement cannot be executed because an internal identifier limit has been exceeded for the database. The cause of this error is due to one of the following:
> On a CREATE DATABASE statement, the limit of 65217 DBIDs has been exceeded.For all other statements, the limit of 32767 OBIDs has been exceeded for that database.If the database is a WORKFILE database, then one of the following reasons might apply:The number of tablespaces in the WORKFILE database has exceeded the limit of 500.The number of indexes defined on declared global temporary tables belonging to all agents on the local DB2®member has exceeded the limit of 10,000.The number of tables and triggers belonging to a local agent exceeded the limit of 11,767. The tables in the WORKFILE database include work files, created global temporary tables, and declared global temporary tables.We verified all the above options and deleted some obsolete tables and ran Modify recovery. It didn't work.
> Has anyone come across this issue if so please advice..
>  


---
This email has been checked for viruses by AVG.
https://www.avg.com