PeopleTools Platform Flags on Indexes

I have noticed on databases that have been upgraded to PT8.45, that most of the indexes say they will build on some database platforms when they will actually build on either all of them, or none of them.

This is because PSINDEXDEFN still contains flags for platforms that are no longer supported by PeopleSoft/Oracle and can no longer be set individually in Application Designer.

• PLATFORM_SBS: SQL Base
• PLATFORM_ALB: Allbase
• PLATFORM_DB4: DB2/AS400

However, Application Designer still checks the values of these flags and if any flags have different values, then the SOME radio button is selected. However, all six platform flags have the same value.

This is a nuisance because PeopleSoft releases indexes for specific platforms, and some indexes are suppressed on certain platforms. It is not easy to see from the indexes view of Application Designer whether this index will be built. It would be easier if it said ALL or NONE when it means that.

I have fixed this by updating PeopleTools tables as follows. The first query reports on the indexes where the supported platform flags all have the same value and one of the unsupported platform flags are different.

column RECNAME format a15
column INDEXID format a5 heading 'IndexID'
column DDLCOUNT format 999 heading 'DDLCnt'
column CUSTKEYORDER format 9999 heading 'CustKeyOrder'
column KEYCOUNT format 999 heading 'KeyCnt'
column PLATFORM_SBS format 999 heading 'SBS'
column PLATFORM_DB2 format 999 heading 'DB2'
column PLATFORM_ORA format 999 heading 'ORA'
column PLATFORM_INF format 999 heading 'INF'
column PLATFORM_DBX format 999 heading 'DBx'
column PLATFORM_ALB format 999 heading 'ALB'
column PLATFORM_SYB format 999 heading 'SYB'
column PLATFORM_MSS format 999 heading 'MSS'
column PLATFORM_DB4 format 999 heading 'DB4'
column ACTIVEFLAG Format 999 heading 'Active'
column CLUSTERFLAG format 999 heading 'ClustFlag'
column UNIQUEFLAG format 999 heading 'UniqueFlag'
column INDEXTYPE format 999 heading 'IndexType'
column IDXCOMMENTS format a60

spool fixindexdefn
SELECT *
FROM PSINDEXDEFN
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND (PLATFORM_ORA!=PLATFORM_SBS
  OR PLATFORM_ORA!=PLATFORM_ALB
  OR PLATFORM_ORA!=PLATFORM_DB4)
;

                                                 Cust
                Index Index Unique Clust          Key  Key  DDL
RECNAME         ID     Type   Flag  Flag Active Order  Cnt  Cnt  SBS  DB2  ORA  INF  DBx  ALB  SYB  MSS  DB4
--------------- ----- ----- ------ ----- ------ ----- ---- ---- ---- ---- ---- ---- ---- ---- ---- -
IDXCOMMENTS
------------------------------------------------------------
PSGATEWAYDEL    A         4      0     0      1     1    1    0    1    1    1    1    1    1    1    1    0
for clearlisting

PSLOCALEOPTNDFN _         1      1     1      1     0    3    0    1    1    1    1    1    1    1    1    0


PSLOCK          _         1      1     1      1     0    1    0    1    1    1    1    1    1    1    1    0

The following SQL commands set the SQL flags for the unsupported platforms to the value for the supported platforms. The version number on the record definition is updated so that PeopleSoft reaches the object.

UPDATE PSVERSION
SET VERSION = VERSION + 1
WHERE OBJECTTYPENAME IN('SYS','RDM');

UPDATE PSLOCK
SET VERSION = VERSION + 1
WHERE OBJECTTYPENAME IN('SYS','RDM');

UPDATE PSRECDEFN
SET VERSION = (
  SELECT VERSION
  FROM PSVERSION
  WHERE OBJECTTYPENAME = 'RDM')
WHERE RECNAME IN (
  SELECT RECNAME
  FROM PSINDEXDEFN
  WHERE PLATFORM_DB2=PLATFORM_DBX
  AND PLATFORM_DBX=PLATFORM_INF
  AND PLATFORM_INF=PLATFORM_ORA
  AND PLATFORM_ORA=PLATFORM_SYB
  AND ( PLATFORM_ORA!=PLATFORM_SBS
    OR PLATFORM_ORA!=PLATFORM_ALB
    OR PLATFORM_ORA!=PLATFORM_DB4)
);

UPDATE psindexdefn
SET PLATFORM_DB4=PLATFORM_ORA
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND PLATFORM_ORA!=PLATFORM_DB4;

UPDATE psindexdefn
SET PLATFORM_ALB=PLATFORM_ORA
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND PLATFORM_ORA!=PLATFORM_ALB;

UPDATE psindexdefn
SET PLATFORM_SBS=PLATFORM_ORA
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND PLATFORM_ORA!=PLATFORM_SBS;

COMMIT;
spool off

The platform flags now say 'ALL'. Not a tremendous change, but at least I can immediately see that these indexes do build on all platforms without having to open each one.