Troubleshooting
Problem
DB2 SQL0960 / SQLSTATE=57011 returned when trying to create a table in a tablespace. But querying SYSCAT.TABLES shows the number of tables is not near the limit?
Symptom
The DB2 jcc driver might return the following when trying to create a regular or partitioned table:
com.ibm.db2.jcc.am.SqlException: The maximum number of
objects have been created in the table spaces with the following names
or identifiers "USERSPACE1".. SQLCODE=-960, SQLSTATE=57011, DRIVER=4.13.127
Or the following error might be received on the CLP command line:
C:\>db2 "create table technote (test int) in userspace1"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0960C The maximum number of objects have been created in the table spaces
with the following names or identifiers "USERSPACE1". SQLSTATE=57011
However, if we query SYSCAT.TABLES we find very few tables are actually created, certainly nowhere near the limit. See: "Related URL" section for 'SQL & XML limits':
select count(*) as number_of_objects ,case when type='A' then 'ALIAS' when type='S' then 'MQT' when type='T' then 'REGULAR TABLE' when type='V' then 'VIEW' else 'other' end as type from syscat.tables where TBSPACE='USERSPACE1' group by type order by type
;
NUMBER_OF_OBJECTS TYPE
----------------- -------------
1 MQT
14 REGULAR TABLE
2 record(s) selected.
This SQL shows only 14 regular untyped tables. See: "Related URL" section for a link to the documentation relating to SYSCAT.TABLES catalog table.
Cause
In DB2 v9.1 a number of catalog changes were made to support range partitioned tables (RPTs) which means that SYSCAT.TABLES is no longer reliable in providing a count of the total number of tables created in the database.
Furthermore, each range partitioned table contributes to the maximum number of objects allowed and that range partition data is now stored in the syscat.datapartitions catalog table:
Therefore, it is necessary to query: syscat.datapartitions to determine the actual number of tables created.
Diagnosing The Problem
To select all the relevant objects, including range partitioned tables and tables with their BLOB/CLOB data in a separate tablespace, the following SQL could be used:
1)
select distinct varchar(tsp.tbspace,60) as tbspace, varchar(dap.tabschema,60) as schema ,varchar(dap.tabname,60) as tabname from syscat.datapartitions dap inner join syscat.tablespaces tsp on dap.tbspaceid = tsp.tbspaceid UNION select distinct varchar(tsp.tbspace,60),varchar(dap.tabschema,60),varchar(dap.tabname,60) from syscat.datapartitions dap inner join syscat.tablespaces tsp on dap.long_tbspaceid = tsp.tbspaceid ;
An additional 'where' clause can be added to filter on USERSPACE1, otherwise all tables will be returned for all tablespaces.
This SQL doesn't include tables with their indexes in a separate tablespaces, to incorporate that data then you can select from: syscat.indexes
However, this SQL will return 1 row for every range partition, which while useful, might return a lot of data.
2) This SQL provides a much simpler 'count':
select count(*) as count from table (select partitionobjectid from syscat.datapartitions where tbspaceid=2 group by PARTITIONOBJECTID with ur) with ur
;
It returns:
COUNT
-----------
53299 <<==This is the 8k limit for a DMS tablespace.
1 record(s) selected.
In this case the above count is sufficient to confirm the limit has been reached.
Resolving The Problem
Unfortunately, all that can be done is to move some of the objects to a new tablespace. Once the limit has been hit it cannot be increased and the only option is to move data to another tablespace which is below the limit.
Again, see: "Related URL" section, specifically: "SQL & XML Limits"
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21987464