Sorting and Paging in the Maximo REST API

The rules here come from IBM’s documentation and from our own work with the REST API, on Manage in the MAS 9.2 series. We ran every request on our test system, against IBM’s demo data, as a user without administrator rights. The examples read the object structure MXAPIASSET at the site BEDFORD.

Sorting with oslc.orderBy

#

oslc.orderBy takes a list of attributes, separated by commas. Every attribute carries a sign in front: + sorts ascending, - descending.

oslc.orderBy=-priority,+assetnum

This returns the assets with the highest priority first, and within one priority by asset number. Three details decide whether the request works:

• The sign is required. oslc.orderBy=assetnum is refused with BMXAA8744E, and the message says it expected a sort order sign.
• The + must be encoded as %2B. In a URL, a raw + stands for a space. Manage then reads " assetnum" and refuses it with the same error. --data-urlencode in curl and URLSearchParams in JavaScript encode it for you.
• The attribute must belong to the main object. An unknown name is refused with BMXAA9105E, “Invalid OSLC order by identifier”.

Without oslc.orderBy, the API sets no order of its own. The database returns the rows in whatever order it likes, and that order can change between two requests. Any request that pages needs a sort key.

A dot in a sort key is a default value

#

In oslc.where and oslc.select, a dot leads to a related record. In oslc.orderBy it means something else: the text after the dot is the value used for records where the attribute is empty.

oslc.orderBy=+location.description

This looks like “sort by the description of the location”. It sorts by location, and an asset without a location sorts as if its location were the word description. The request returns 200 and a plausible list, so the mistake is easy to miss. Sorting by an attribute of a related record is not possible in oslc.orderBy.

The default value has a real use. On our test system, empty values came first in an ascending sort. A default value moves them to the end:

oslc.orderBy=+serialnum.ZZZZ

Pages

#

oslc.pageSize sets how many records one answer holds. Every answer then carries the links to its neighbours in responseInfo:

"responseInfo": {
  "href": "…/api/os/mxapiasset?…",
  "nextPage": { "href": "…/api/os/mxapiasset?pageno=3&…" },
  "previousPage": { "href": "…/api/os/mxapiasset?pageno=1&…" },
  "pagenum": 2,
  "totalPages": "…",
  "totalCount": "…"
}

pageno picks a page directly. A page past the end is refused with a 400, and the message names the cause: the page number was derived and not taken from nextPage. So the reliable way through a list is to follow nextPage until it is missing.

A few page sizes behave in their own way:

• No oslc.pageSize: Manage returns every matching record in one answer. On a large table that is slow and heavy, and an administrator can cap it with the system property mxe.oslc.maxpagesize. A request that passes the cap then fails.
• oslc.paging=true without a page size: Manage pages with a default size of 100.
• oslc.pageSize=0: refused with BMXAA8731E. The page size must be more than 0.

Why basic paging can skip a record

#

Basic paging, the kind above, runs the query again for every page. Page 3 with a page size of 50 means “run the query, skip the first 100 rows, return the next 50”. Nothing connects page 3 to the page 2 you read before.

The same happens in the other direction. A new record that sorts before the current position pushes every row down by one, and the next page repeats a record. A changed attribute that is part of the sort moves a record from one page to another. For a screen that shows one page at a time this rarely matters. For an export, a sync or a migration it does.

Stable paging

#

IBM’s answer is stable paging. With stablepaging=1, Manage keeps the result of the first request in memory and serves the following pages from it. The nextPage link then carries a stableId. The rules, as IBM describes them:

• Paging goes forward only. There is no previousPage, and a page cannot be read twice.
• The kept result expires after five minutes without a request, or when the last page is read. The system property mxe.oslc.idleexpiry sets the time in seconds.

On our test system, stable paging never got past the first page. Page 1 came back with its nextPage link, and following that link was refused with 404 BMXAA9510E, “The stable page resource was not found”. It failed every time: as a user and as the administrator, and with the session cookies of the first answer sent back. Basic paging and keyset paging worked on the same system in the same minutes. We have not found the cause yet, so we do not build on stable paging, and we test it first on any system where we would want it.

Keyset paging: every record exactly once

#

Keyset paging sorts by the unique id of the main object and asks for the records after the last id it has seen. Each page is a new query with its own where clause, and the position comes from the data itself:

page 1: oslc.where=siteid="BEDFORD"
        oslc.orderBy=+assetuid
        oslc.pageSize=500

page 2: oslc.where=siteid="BEDFORD" and assetuid>1629
        oslc.orderBy=+assetuid
        oslc.pageSize=500

1629 is the assetuid of the last record on page 1. The loop ends when a page holds fewer records than the page size. In JavaScript:

async function readAll(base, where, select, apikey) {
  const all = [];
  let last = 0;
  while (true) {
    const q = new URLSearchParams({
      lean: "1",
      "oslc.select": `assetuid,${select}`,
      "oslc.where": `${where} and assetuid>${last}`,
      "oslc.orderBy": "+assetuid",
      "oslc.pageSize": "500",
    });
    const res = await fetch(`${base}/api/os/mxapiasset?${q}`, {
      headers: { apikey },
    });
    const page = (await res.json()).member ?? [];
    all.push(...page);
    if (page.length < 500) return all;
    last = page.at(-1).assetuid;
  }
}

Why it holds up:

• A deleted record leaves a gap in the ids, and the next page starts after the last id all the same.
• A record changed between pages stays at its id. It is neither skipped nor repeated, as long as it still matches the where clause.
• A new record gets a higher id and turns up on a later page.

Every main object has such an id: ASSETUID for the asset, WORKORDERID for the work order, LOCATIONSID for the location. Select it, sort by it alone, and compare with >. Our product Fiedler Data Export reads an object structure this way.

Which paging for which job

#

Errors at a glance

#

Examples

#

Each request below returned data on our test system. Each line is one query parameter.

The most urgent assets first, ten per page:

oslc.where=siteid="BEDFORD"
oslc.select=assetnum,description,priority
oslc.orderBy=-priority,+assetnum
oslc.pageSize=10

Assets by serial number, the ones without one at the end:

oslc.where=siteid="BEDFORD"
oslc.select=assetnum,serialnum
oslc.orderBy=+serialnum.ZZZZ
oslc.pageSize=20

The first page of a keyset run, with the totals:

oslc.where=siteid="BEDFORD"
oslc.select=assetuid,assetnum,status
oslc.orderBy=+assetuid
oslc.pageSize=500
collectioncount=1

What comes next

#

IBM’s own pages on sorting and paging are the reference to keep next to this article:

• Sorting and paging in the IBM Maximo Manage documentation.
• Query (GET) / Sort and paging in IBM’s Maximo REST API guide on GitHub.

The next article of the series covers counts, aggregates and distinct values. The series overview lists all parts. If you want help with the REST API in your own Manage, our services page shows how we work, or get in touch.

IBM and Maximo are trademarks of International Business Machines Corporation, registered in many jurisdictions worldwide.