Adaptive Server 15.0 deprecates the system functions that it used to report space usage in previous versions and has replaced them with partition-aware versions. Table 5-1 lists these functions, along with the syntax changes.
Function and syntax in 12.5 |
Function in 15.0 |
|---|---|
data_pgs(object_id, {doampg | ioampg}) |
data_pages(dbid, object_id [, indid [, ptn_id]]) |
used_pgs(object_id, doampg, ioampg) |
used_pages(dbid, object_id [, indid [, ptn_id]]) |
reserved_pgs(object_id,{doampg | ioampg}) |
reserved_pages(dbid, object_id [, indid [, ptn_id]]) |
rowcnt(sysindexes.doampg) |
row_count(dbid, object_id [, ptn_id]) |
ptn_data_pgs(object_id, partition_id) |
(data_pages()) |
Adaptive Server 15.0 replaces the OAM page parameters doampg and ioampg with the more user-friendly indid (index ID) and ptn_id (partition ID) parameters. Adaptive Server also changes how these functions are used.
In Adaptive Server 12.5, these functions were used in queries involving the sysindexes table because space allocations were tracked with sysindexes and consequently, the sysindexes doampg and ioampg columns provided the OAM page parameters. For example, a common query used in Adaptive Server 12.5 to calculate total space used for each of the nonclustered indexes on a particular table was similar to:
-- ASE 12.5 logic to report the spaced used by nonclustered indices
select name, indid, used_pgs(id, doampg, ioampg)
from sysindexes
where id=object_id('salesdetail')
and indid > 1
In Adaptive Server 15.0, space is linked to syspartitions instead of sysindexes, so the query above is rewritten for Adaptive Server 15.0 as:
-- ASE 15.0 logic to report the spaced used by nonclustered indices
select i.name, p.indid, sum(used_pages(db_id(), p.id ,p.indid))
from sysindexes i, syspartitions p
where i.id=object_id('salesdetail')
and p.id=object_id('salesdetail')
and i.indid > 1
and p.indid > 1
and p.id=i.id
and p.indid=i.indid
group by i.name, p.indid
order by p.indid
The deprecated Adaptive Server 12.x functions still execute, but return a value of 0; they rely on sysindexes.doampg and sysindexes.ioampg, which Adaptive Server no longer maintains. syspartitions has similar structures in the columns datoampage and indoampage, but the values for these columns are on a partition basis, so you must aggregate index space usage for partitioned tables.
Table 5-1 lists the changed functions, along with the old and new syntax. These changes do not affect end-user application, but may impact some or your scripts and utilities.
The change that may have the biggest effect on your scripts is the replacement of sysindexes.doampg or ioampg with sysindexes.indid and partition_id. In earlier versions, the functions used scans of sysindexes, but in Adaptive Server 15.0, they use scans of syspartitions (often joined with sysindexes). For example, in Adaptive Server 12.5, this is used to report the space used by nonclustered indices:
select name, indid, used_pgs(id, doampg, ioampg)
from sysindexes
where id=object_id('authors')
and indid > 1
In Adaptive Server 15.0, this is the syntax to report the space used by nonclustered indices:
select i.name, p.indid, used_pages(dbid(), p.id ,p.indid)
from sysindexes I, syspartitions p
where i.id=object_id('authors')
and i.indid > 1
and p.indid > 1
and p.id=i.id
and p.id=object_id('authors')
and p.indid=i.indid
order by indid
The script is different because storage in Adaptive Server 15.0 is linked to syspartitions instead of sysindexes in earlier versions.
This example reports the spaced used by nonclustered indexes by partition in Adaptive Server 15.0:
select p.name, i.name, p.indid, used_pages(dbid(), p.id, p.indid, p.partitionid)
from sysindexes I, syspartitions p
where i.id=object_id('authors')
and i.indid > 1
and p.indid > 1
and p.id=i.id
and p.id=object_id('authors')
and p.indid=i.indid
order by p.partitionid, p.indid
