describe_record

describe_record returns what the LLM needs to understand a record and write correct SQL against it: the record type and database table name, the fields in order with their types and flags, the keys and indexes, and the pages that use the record.

Use it for

“Describe the JOB record: its keys, required fields, and every page that uses it.”
“Write SQL that lists active employees by department, with correct effective-dated logic.” The LLM describes JOB first, to learn which keys to correlate.
“Which fields on ZZ_TRAIN_REQ are required?”
“Which indexes exist on PS_JOB?”

Input

Argument	Type	Required	Description
`record`	string	Yes	The record name, such as `JOB`. Up to 15 characters.
`include_pages`	boolean	No	Also list the pages that use the record. Default: `true`.
`expand_subrecords`	boolean	No	Replace each subrecord with its fields. Default: `true`.

{
  "type": "object",
  "properties": {
    "record": {
      "type": "string",
      "pattern": "^[A-Za-z][A-Za-z0-9_]{0,14}$",
      "description": "Record name, for example JOB or VOUCHER_LINE"
    },
    "include_pages": {
      "type": "boolean",
      "default": true,
      "description": "Also list the pages that use the record"
    },
    "expand_subrecords": {
      "type": "boolean",
      "default": true,
      "description": "Replace each subrecord with its fields"
    }
  },
  "required": ["record"],
  "additionalProperties": false
}

Output

Field	Description
`record`	The record name
`description`	The record’s description
`recordType`	`SQL Table`, `SQL View`, `Derived/Work`, `SubRecord`, `Dynamic View`, `Query View`, or `Temporary Table`
`sqlTable`	The database table or view, such as `PS_JOB`. `null` for derived/work records, subrecords, and dynamic views, which aren’t in the database.
`relatedLanguageRecord`	The related language record, if there is one
`auditRecord`	The audit record, if there is one
`keys`	The key fields, in order
`fields`	The fields, in order (see below)
`indexes`	The indexes, each with `id`, `name`, `type`, `unique`, and `fields`
`pages`	The pages that use the record, when `include_pages` is `true`
`lastUpdated`, `lastUpdatedBy`	When the record definition last changed, and who changed it

Each entry in fields has a name, a type such as Character or Date, and a length, plus decimals for numbers. These flags appear when they apply:

Flag	Meaning
`key`	The field is a key
`required`	A value is required
`translate`	Values are edited against the field’s translate values
`promptTable`	The prompt table that values are edited against
`fromSubrecord`	The subrecord the field came from, when subrecords are expanded

An index’s type is key for the key index, alternate for an alternate search key index, and user for any other index.

{
  "record": "JOB",
  "recordType": "SQL Table",
  "sqlTable": "PS_JOB",
  "keys": ["EMPLID", "EMPL_RCD", "EFFDT", "EFFSEQ"],
  "fields": [
    {
      "name": "EMPLID",
      "type": "Character",
      "length": 11,
      "key": true
    },
    { "name": "EMPL_RCD", "type": "Number", "length": 3, "key": true },
    { "name": "EFFDT", "type": "Date", "key": true },
    { "name": "EFFSEQ", "type": "Number", "length": 3, "key": true },
    { "name": "DEPTID", "type": "Character", "length": 10 },
    {
      "name": "EMPL_STATUS",
      "type": "Character",
      "length": 1,
      "translate": true
    }
  ],
  "indexes": [
    {
      "id": "_",
      "name": "PS_JOB",
      "type": "key",
      "unique": true,
      "fields": ["EMPLID", "EMPL_RCD", "EFFDT", "EFFSEQ"]
    }
  ],
  "pages": ["JOB_DATA1"]
}

How it works

The tool reads PSRECDEFN, PSRECFIELD, PSDBFIELD, PSINDEXDEFN, PSKEYDEFN, and PSPNLFIELD, always with the record name as a bind variable.

1. The record header, from PSRECDEFN:

SELECT RECNAME, RECTYPE, SQLTABLENAME, RECDESCR,
       RELLANGRECNAME, AUDITRECNAME,
       LASTUPDDTTM, LASTUPDOPRID
  FROM PSRECDEFN
 WHERE RECNAME = :record

RECTYPE becomes recordType, using the codes in the PeopleTools metadata primer. The table name is SQLTABLENAME when it’s filled in, and otherwise PS_ plus the record name.

2. The fields, from PSRECFIELD joined to PSDBFIELD. The flags come from bits in USEEDIT: 1 is key, 256 is required, and 512 is translate.

Oracle Database
Microsoft SQL Server

SELECT F.FIELDNUM, F.FIELDNAME, F.SUBRECORD,
       D.FIELDTYPE, D.LENGTH, D.DECIMALPOS,
       BITAND(F.USEEDIT, 1)   AS KEY_FLAG,
       BITAND(F.USEEDIT, 256) AS REQUIRED_FLAG,
       BITAND(F.USEEDIT, 512) AS TRANSLATE_FLAG,
       F.EDITTABLE
  FROM PSRECFIELD F
  LEFT JOIN PSDBFIELD D
    ON D.FIELDNAME = F.FIELDNAME
 WHERE F.RECNAME = :record
 ORDER BY F.FIELDNUM

SELECT F.FIELDNUM, F.FIELDNAME, F.SUBRECORD,
       D.FIELDTYPE, D.LENGTH, D.DECIMALPOS,
       F.USEEDIT & 1   AS KEY_FLAG,
       F.USEEDIT & 256 AS REQUIRED_FLAG,
       F.USEEDIT & 512 AS TRANSLATE_FLAG,
       F.EDITTABLE
  FROM PSRECFIELD F
  LEFT JOIN PSDBFIELD D
    ON D.FIELDNAME = F.FIELDNAME
 WHERE F.RECNAME = @record
 ORDER BY F.FIELDNUM

A row with SUBRECORD set to Y is a subrecord, not a field. When expand_subrecords is true, the server runs the same query for the subrecord and puts its fields in its place, up to five levels deep. EDITTABLE holds the prompt table.

3. The indexes, from PSINDEXDEFN and PSKEYDEFN:

SELECT I.INDEXID, I.INDEXTYPE, I.UNIQUEFLAG,
       K.KEYPOSN, K.FIELDNAME
  FROM PSINDEXDEFN I
  JOIN PSKEYDEFN K
    ON K.RECNAME = I.RECNAME
   AND K.INDEXID = I.INDEXID
 WHERE I.RECNAME = :record
 ORDER BY I.INDEXID, K.KEYPOSN

The key index always has the ID _ and INDEXTYPE 1, and is named PS_ plus the record name. Other indexes are named PS plus their one-character ID plus the record name, such as PSAJOB.

4. The pages, from PSPNLFIELD:

SELECT DISTINCT PNLNAME
  FROM PSPNLFIELD
 WHERE RECNAME = :record
 ORDER BY PNLNAME

Limits and errors

Field lists are never truncated. The page list stops at 200 pages, with truncated set to true.
A record that doesn’t exist returns a tool execution error that lists the closest record names.
Subrecords nested more than five levels deep are reported but not expanded further.

get_translate_values lists the valid values of a field flagged translate.
search_definitions finds a record when you don’t know its name.
find_references finds the other definitions that refer to the record, such as the components that use it as their search record.
run_select queries the record’s data, if your administrator allows it.