Skip to content

Netezza – Max object id

Maximum number of objectid’s accepted by Netezza is around 2.14 Billiion in V7 (around 1 Billion in V6). If your system start using OBJID greater than 1 Billion (in V7) then you will never be able to downgrade to NPS v6. Point to note here is that when ever you create any new object the object id increased and can never be reused even if you drop any objects. So object id allocation is always increasing.
Also note that objid count will increase by 10 when ever you create a table because internally 10 oids were reserved by NPS for table creation (look little strange but its true).

How to find the max object id (objid) for your system?
Run below query:
select now(), max(objid) from _t_object;

NOW MAX
— —
2013-01-17 12:49:33 180782574

You may se that the MAX value may some time give value lower than what you got some time back or before NPS restart. This is because the above query will give you the max object id for any object that still exists. If any object was created after this and then dropped, that id value will not be shown.

So, to avoid above issue, always create one temporary table and immediately after that run this query and then delete the temp table create. This will give you a most accurate objid that is being used by NPS.