Issue while performing People Tools 8.51 Upgrade May 24, 2011
Posted by Nilotpal Mrinal in PeopleSoft, Uncategorized.Tags: People Tools Upgrade, PeopleSoft, PeopleSoft Upgrade
trackback
Currently I am doing an application Upgrade from FSCM 8.9 to 9.1. As part of that we are upgrading Tools from 8.48 to 8.51.
While running DBTSFIX.sqr, we faced this error -
SQR 1500: (SQR 1500) Array element out of range (32767) for array ‘a_dbts_list’ on line 125.
We tweaked the sqr as below -
Locate the create-array name=a_DBTS_list statement in sqr.
Update the size of the array to 131070 (double the delivered size of 65535).
This help us run the sqr successfully. Oracle validated the fix later.
I hope this will help folks who are upgrading to Tools 8.51.
I am also facing one issue in HRMS application upgrade:
HCM 8.9 to 9.1
Tools 8.47 to 8.51
During my upgrade HCM8.9 to 9.1 from tools 8.47 to 8.51, I am facing issue with
Time and Labour tables:
GROUP_ID column from old release is replaced by TL_GROUP_ID column (key columns in the tables).
When this column is added by an alter statement(CA Step),it did so by inserting NULL value to this column.At later stage in CA when obsolete columns got deleted, GROUP_ID column got deleted from these tables which being key column and having data on it.
Now on step of creating Indexes again CA step is giving error as duplicate keys found for these tables because of NULL values in key column TL_GROUP_ID.
Hi Kailash,
This is a known issue and I have reported it to Oracle Support and in future you will get a patch for it.
TL_GROUP_ID (9.1) is mapped to GROUP_ID(8.8) and as per correct conversion TL_GROUP_ID field should be populated by GROUP_ID. To fix this problem you have to identify all the tables referenced by TL_GROUP_ID and see which all are getting altered. Then search for those alter script and modify null by GROUP_ID.
This resolves the issue.
INSERT INTO Z1HRSS6$.PSYTL_GROUP_DTL (
TL_GROUP_ID,
EMPLID,
EMPL_RCD,
GROUP_TYPE_IND,
TL_APPR_PRIORITY,
GROUP_ID)
SELECT
GROUP_ID, **** in original script it will be NULL *****
EMPLID,
EMPL_RCD,
GROUP_TYPE_IND,
1,
GROUP_ID
FROM Z1HRSS6$.PS_TL_GROUP_DTL
;
Thanks,
Nilotpal Mrinal
Thanks Nilotpal.
So this will be a kind of pushing back data (from COP) corresponding to GROUP_ID columns to TL_GROUP_ID just before the alter delete step in CA (as I did in a test MTP run to overcome this issue).
What I did just before the Alter step in CA to drop old column was…
For tables having issue with TL_GROUP_ID cloumn:
Create a temporary table from the backup tables with data.
Copy GROUP_ID column data to TL_GROUP_ID.Drop the GROUP_ID column from temporary table.
Count the number of rows in Temporary table, backup and original table to make sure all are in sync.
Truncate the original table.
Insert the Data from Temporary table to Original.
Execute the Create unique index command to the table in issue.
Drop the temporary table.
This help me resolve the issue.
You don’t need to carry out is as a pre or post AWOD. You can tweak the alter script itself next move onwards. As data will be there, indexes will get created.
Hi Neil,
In my upgrade, I am also facing issue while running Running Alter Timestamps Scripts:
These AlterTimestamp1-8, scripts were generated during Initial pass and are used in MTP as per step documentation.
When CA ran those scripts in TestMTP, found following issues again:
“Columns missing from record: PS_UPG_HC_AET.LASTUPDDTTM (Initial pass issue and in Test MTP also)
Tables not found in the database : PS_UPG_RCM_INT_AET (Issue occured during Test MTP run)”
“Tables not found in the database : PS_UPG_HRS_QST_AET,PS_UPG_JOB_TMP (Issue occured during Test MTP run)”
“Tables not found in the database : PS_UPG_PM_ST3_AET,PS_UPG_BUDACTS_AET (Issue occured during Test MTP run)”
“Columns missing from record : PS_UPG_POSNDTA_AET.LASTUPDDTTM (Initial pass issue and in Test MTP also);
Tables not found in the database : PS_UPG_INT_SCH_AET (Issue occured during Test MTP run)”
I do remember facing issues in these steps, though I don’t recollect if it was the same issue.
I will suggest generate these scripts once again in next MTP and run these scripts sequential ( not by CA). CA runs these scripts in parallel.I found that running scripts in parallel using CA was causing issues and above approach helped me.
Thanks Neil for replying.
I am hesitating to regenerate and run the scripts as the document say’s :
Task 3-24-4: Generating Timestamp Conversion Scripts
Understanding Timestamp Conversion Scripts
If you are performing your initial upgrade pass, complete all sections in this step to generate timestamp conversion scripts.
Important! During Move to Production passes, copy the DROPINDEXESn.SQL,
ALTERTIMESTAMPSn.SQL, and REBUILDINDEXESn.SQL scripts from your initial upgrade pass
output directory and place them in the output directory for your Move to Production pass. Edit the
REBUILDINDEXESn.SQL scripts and replace the database name in the create index statement with the Move
to Production database name, if needed. These scripts can only be generated correctly during the initial pass.
You can skip the remaining sections of this step, which only apply to the initial upgrade pass.
I have generated the scripts but found some differences in the new ones so didn’t executed it.
Will it be good if I execute the newly generated altertimestamp sql scripts in MTP pass.
Please suggest.
Hi ,
In my another upgrade , in Initial pass:
Task 2-14-1: Running the RNHCUPK03 Script:
CA is erroring out throwing following error:
Rename GROUP_ID -> TL_GROUP_ID
Message Set Number: 200
Message Number: 0
Message Reason: SQL error. Stmt #: 6815 Error Position: 0 Return: 805 – ORA-00001: unique constraint (SYSADM.PS_PSOBJCHNG) violated (200,0)
Message Set Number: 200
Message Number: 0
Message Reason: INSERT INTO PSOBJCHNG (ENTTYPE, OLDNAME, NEWNAME, OBJCHG_STAMP, OLDNAME2) VALUES (:1,:2,:3,SYSDATE,:4) (200,0)
Error: Unable to rename: GROUP_ID -> TL_GROUP_ID
Please suggest what could be the reason, as i have no clue whats going on. And I have got stucked on this.
sorry for half information:
its HCM8.9 to 9.1 upgrade and on tools 8.47 to 8.51
Hi ,
Sorry for being late in response.
1. Task 3-24-4: Generating Timestamp Conversion Scripts
For this issue , I will recommend you to generate new set of scripts.
There will be some difference in scripts, but will be ok, as it will be based on your current source.
This will help you prevent any issue during go-live.
2. Rename Group ID->TL Group ID ,
I have not encountered this issue, but seems like PSOBJCHNG table is either having duplicate data or some other issues. Renames happen based on data in this table.