Record Integrity

The following table describes the audit queries and resolutions for this area:

Query Description Resolution

RECORD-1

Record Definition Field count does not match the number of records in Record Field table.

Run the following SQL:

SELECT COUNT(*)
FROM PSRECFIELD
 WHERE RECNAME = 'X';
UPDATE PSRECDEFN
SET FIELDCOUNT =
 COUNT
 WHERE RECNAME = 'X';

RECORD-2

Record Definition Fields do not exist in Record Field table.

Run the following SQL:

UPDATE PSRECDEFN
SET FIELDCOUNT = 0
WHERE RECNAME = 'X';

Or

DELETE FROM PSRECDEFN
WHERE RECNAME = 'X';

RECORD-3

Record Definition Parent Record does not exist in Record Definition table.

Use Application Designer to open the definition.

Select File, Object Properties, Use and specify a valid parent record.

RECORD-4

Record Definition SubRecord does not exist in Record Definition table.

Use Application Designer to open the definition.

Select File, Object Properties, Type and specify a valid subrecord.

RECORD-5

Record Definition Query Security Record does not exist in Record Definition table

Use Application Designer to open the definition.

Select File, Object Properties, Use and specify a valid query security record.

RECORD-6

Record Field definitions contain record names that do not exist in the Record Definition table.

Run the following SQL:

DELETE FROM PSRECFIELD
WHERE RECNAME = 'X'

RECORD-7

DBField records do not exist for the following RecField table Fields.

Use Application Designer to open the definition and fix the invalid fields.

RECORD-8

Record definitions do not exist for the following RecField table SubRecords.

Use Application Designer to open the definition and fix the invalid fields.

RECORD-9

Invalid record definitions in record group definitions.

Run the following SQL:

DELETE FROM PS_REC_GROUP_REC
WHERE RECNAME NOT IN
(SELECT DISTINCT RECNAME
 FROM PSRECDEFN)

RECORD-11

Record definitions that contain more than two Long character field types.

Note: The use of LONG data types can introduce performance and storage overhead on database platforms. The judicious use of multiple LOB data types in a single record definition is recommended.

Although PeopleTools does not prevent the use of multiple LONG fields in a single record definition, assigning multiple LONGs to a single record definition is discouraged.

For Db2 z/OS, LONGs are implemented as LONG VARCHAR / VARGRAPHIC data types. DB2 determines the actual length of a LONG VARCHAR / VARGRAPHIC data type when the table is created by summing the internal lengths of all of the non-LONG VARCHAR / VARGRAPHIC columns in the table, and subtracting that summed length value from the page size (PeopleTools assigns tables that contain LONGs to a 32KB page size). DB2 then reserves the remainder of the row space in the table for the LONG VARCHAR / LONG VARGRAPHIC column. If there are multiple LONG VARCHAR / VARGRAPHIC columns in the table, each must equally divide this remaining space in the row. This is why PeopleTools always accomplishes alters to tables that contain LONGs through the Alter By Table Rename method rather than the Alter In Place method. At alter time, if new columns are subsequently added to the table, or if existing column definitions are changed such that the sum of the lengths of the non-LONG columns increases, the amount of row space for the LONG VARCHAR / LONG VARGRAPHIC columns is reduced. For this reason, please use the PeopleTools LONG data type judiciously for Db2 z/OS and note that placing multiple LONGs in a single record definition is highly discouraged.

For the Oracle platform, the judicious use of multiple LOB data types in a single record definition is recommended. The use of multiple LONG fields in a single record definition is supported. Multiple LONG support is made possible with the adoption of the CLOB (Character Large Object) and BLOB (Binary Large Object) Oracle LOB data types. While technically possible, you should exercise caution when using multiple LOBs in a single record definition because of potential performance overhead which might occur based on the placement and actual size of the LOB.

RECORD-12

Record definitions with a blank or null record name value.

Run the following SQL:

DELETE FROM PSRECDEFN
 WHERE RECNAME = ''

RECORD-13

Temp records that specify a non-standard SQL table name.

Run the following SQL:

UPDATE PSRECDEFN
SET SQLTABLENAME = ' '
WHERE RECTYPE = 7 AND
 SQLTABLENAME <> ' '

RECORD-14

The Field Number field in the RecordField table has an invalid value.

If the reported record was delivered by PeopleSoft or generated as part of an upgrade, contact My Oracle Support. Run the following SQL to determine which fields need to be updated:

SELECT FIELDCOUNT
FROM PSRECDEFN
WHERE RECNAME='X';

SELECT FIELDNUM
FROM PSRECFIELD
WHERE RECNAME='X'
ORDER BY FIELDNUM;

Note: The PSRECFIELD.FIELDNUM values should be numbered sequentially 1 through PSRECDEFN.FIELDCOUNT. If any value is skipped then renumber the FIELDNUM values accordingly.