Upgrading just the Data Schema from Pega 6 to Pega 7
We are upgrading from Pega V6.2 to Pega V7.1.7. In production once we upgrade the system from 6.2 to 7.1.7 there would be a gap of 5 to 7 days where smoke testing and other app server setup needs to be done before users start using the Pega 7 system. As users use the 6.2 system during this period we may need to update the Data Schema to incorporate the changes in the assignment tables etc.. updated during these 5 days. is there a easy way to achieve this instead of executing all the upgrade steps again ?
***Updated by moderator: Lochan to close post***
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Accepted Solution
Pegasystems Inc.
US
Both pr_data_products and pr_data_restore.
JP Morgan Chase
US
Thanks Vipin.
The link do not highlight the properties that need to be set in setupdatabase.properties file.
Does the rules schema name should be 6.2 schema and the data schema should be 7.1 data schema here ?
#Rules schema name : Used for all databases.
#The user name is used for default schema name
rules.schema.name=<6.2 SCHEMA>
# Data schema name : Used for systems running on a Split Schema
# The value of rules.schema.name is the default value for data.schema.name
data.schema.name=<7.1 DATA SCHEMA>
Pegasystems Inc.
US
If you look in the upgrade guide you can see the values you need for these properties. As long as there are not any rule changes after you start the upgrade process the gap between your rules and data upgrade will not matter.
Copy your production database with at least some subset of the data to another environment. As outlined in the upgrade guide perform the upgrade of this environment to a split schema. Do your smoke testing. When you are ready to move to production you need to run the upgrade steps which will migrate your upgraded rules schema to production and create the rules objects (this is where you are creating triggers/stored procedures and permissions needed between the rules and the real data schema) and then run the data only upgrade.
JP Morgan Chase
US
you mean i can run the upgrade step and data only upgrade on the latest 6.2 schema ? and delete all the rule tables in the 6.2 schema manually after the upgrade.
Pegasystems Inc.
US
There is a post upgrade step documented in the guide which talks about using the optimize schema wizard to cleanup the unused rules tables from the data schema. You can either have the wizard do the cleanup or just make a list of the tables the wizard identified and manually delete them.
Pegasystems Inc.
US
The 6.2 schema name is what is going to become the data schema in the 7.1 split schema environment. When you get to the dataonly upgrade you would set the data.schema.name=your6.2schemaname. Don't forget to do the migrate step before you do the dataonly upgrade. The 7.1 Upgrade guide documents how to populate the properties for each of these steps.
JP Morgan Chase
US
Thanks Celeste. This is much better and this 'When you get to the dataonly upgrade you would set the data.schema.name=your6.2schemaname" clarifies my doubt.
Just to confirm , the migrate step before the dataonly upgrade would still be PEGA7 RULES and Pega7 DATA Schema correct ?,
pega.source.rules.schema=[PEGA7 RULES]
#Set the following property if the source system already contains a split schema.
pega.source.data.schema=[PEGA7 DATA]
pega.target.rules.schema=[PEGA7 RULES]
#Used to correctly schema qualify tables in stored procedures, views and triggers.
#This property is not required if migrating before performing an upgrade.
pega.target.data.schema=[PEGA7 DATA]
Pegasystems Inc.
US
I cannot think of any scenario where pega.source.data.schema and pega.target.data.schema would be the same in the migrate script. I would expect for this step in your situation pega.source.data.schema is left blank and pega.target.data.schema=your6.2schemaname. Maybe it would be clearer if you outlined all of the steps and schema names you are expecting so we can see where the disconnect is. I am assuming that you are going to have application specific changes to go along with the upgrade. Are you doing this out of place upgrade in lower environment and then just migrating rules to higher environments or are you importing RAP files as part of the higher level upgrade process?
JP Morgan Chase
US
Hi Celeste
We are doing a out of place upgrade. .
6.2 Schema Name : CAPRPCMGR
7.1 RULES SCHEMA : CAPRPCMGR7 (new blank schema)
7.1 DATA SCHEMA : PRPCDATA (new blank schema)
here are the steps i followed..
1) Copy CAPRPCMGR to PRPCDATA ( Now Upgrade is done on the PRPCDATA )
2) Migrate script
pega.source.rules.schema=PRPCDATA
#Set the following property if the source system already contains a split schema.
pega.source.data.schema=
pega.target.rules.schema=CAPRPCMGR7
#Used to correctly schema qualify tables in stored procedures, views and triggers.
#This property is not required if migrating before performing an upgrade.
pega.target.data.schema=
3) Upgrade script
#Rules schema name : Used for all databases.
#The user name is used for default schema name
rules.schema.name=CAPRPCMGR7
# Data schema name : Used for systems running on a Split Schema
# The value of rules.schema.name is the default value for data.schema.name
data.schema.name=
4) Second run of Migrate script to generate triggers,procedures etc...
pega.source.rules.schema=CAPRPCMGR7
#Set the following property if the source system already contains a split schema.
pega.source.data.schema=PRPCDATA
pega.target.rules.schema=CAPRPCMGR7
Hi Celeste
We are doing a out of place upgrade. .
6.2 Schema Name : CAPRPCMGR
7.1 RULES SCHEMA : CAPRPCMGR7 (new blank schema)
7.1 DATA SCHEMA : PRPCDATA (new blank schema)
here are the steps i followed..
1) Copy CAPRPCMGR to PRPCDATA ( Now Upgrade is done on the PRPCDATA )
2) Migrate script
pega.source.rules.schema=PRPCDATA
#Set the following property if the source system already contains a split schema.
pega.source.data.schema=
pega.target.rules.schema=CAPRPCMGR7
#Used to correctly schema qualify tables in stored procedures, views and triggers.
#This property is not required if migrating before performing an upgrade.
pega.target.data.schema=
3) Upgrade script
#Rules schema name : Used for all databases.
#The user name is used for default schema name
rules.schema.name=CAPRPCMGR7
# Data schema name : Used for systems running on a Split Schema
# The value of rules.schema.name is the default value for data.schema.name
data.schema.name=
4) Second run of Migrate script to generate triggers,procedures etc...
pega.source.rules.schema=CAPRPCMGR7
#Set the following property if the source system already contains a split schema.
pega.source.data.schema=PRPCDATA
pega.target.rules.schema=CAPRPCMGR7
#Used to correctly schema qualify tables in stored procedures, views and triggers.
#This property is not required if migrating before performing an upgrade.
pega.target.data.schema=PRPCDATA
5) Second run of Upgrade script to upgrade data schema ( by passing dataOnly -- true)
rules.schema.name=CAPRPCMGR7
# Data schema name : Used for systems running on a Split Schema
# The value of rules.schema.name is the default value for data.schema.name
data.schema.name=PRPCDATA
These sequence of steps has been tested on the lower environments and working fine.
Once these steps are performed on my PROD Schema's and smoke testing is done on using my 7.1 Schemas CAPRPCMGR7 and PRPCDATA it takes a week to get the approvals etc... until then users work on the 6.2 Schema (CAPRPCMGR). Now the goal is to get the subset of data from 6.2 Schema ( from CAPRPCMGR) to 7.1 DATA Schema ( PRPCDATA ) before going live.
Pegasystems Inc.
US
For step #4 if you are not using 2 databases and you are actually not migrating the upgraded rules schema back (ie, the CAPRPCMGR7 schema is not just a temporary upgrade schema but will become your new rules schema) the settings for source rules and data schema are not relevant as they are not being used. These steps through the smoke testing are fine but I don't believe your idea of just taking some pieces of your 6.2 schema and moving them to your updated data schema will work. I don't know how you would identify what needs to be moved and then what if it is not in the format that is needed for 7.1. I was expecting that you were going to do as Gopi suggested earlier and repeat steps 4 and 5 with your real data schema of CAPRPCMGR and the PRPCDATA schema can then be removed.
JP Morgan Chase
US
Hi Celeste
Thanks for the update. If i follow the approach suggested by Gopi , As rule schema objects are already generated, is it required to run step 4 Second run of Migrate script to generate triggers,procedures etc again ?
Pegasystems Inc.
US
Yes you need to do both step #4 and step #5. If you didn't do step #4 then all of your database triggers, grants, etc. will not be setup correctly between the rules schema and then real data schema. You don't need to actually migrate the rules tables back (assuming that CAPRPCMGR7 is going to be your production rules schema) but you do need to do the creation of the rules schema objects and then the dataOnly upgrade that Gopi suggests.
JP Morgan Chase
US
Thank you. One final question here, As the RULES Schema do not change from QA to PROD. Can we export the Schema from QA to PROD and just perform the steps #4 (generating rule objects ) , #5 (data only upgrade) with real data schema CAPRPCMGR (6.2 Schema). Do you see any issues in exporting RULES Schema from QA to PROD ?
JP Morgan Chase
US
I tried this approach today on our QA environment where RULES schema exists. Step#4 generating rule schema objects was successful. but there were some unique constraint issue while running the data only upgrade (Step #5) on my real data schema. It is trying to insert the product records again resulting in below error.
[java] com.pega.pegarules.pub.database.DuplicateKeyException: Database-General Problem saving instance of class Data-Admin-Products-Restore-Rule 1 23000 ORA-00001: unique constraint (CAPRPCMGR7.PR_DATA_RESTORE_PK) violated
Pegasystems Inc.
US
If you use the migrate script in 7.1.8 to move your upgraded rules schema to higher environments you wouldn't see this issue as it will not migrate the pr_data_restore and pr_data_products tables to the higher environments. If you are using a DB tool to move the rules schema to higher environments or even if you use migrate in versions before 7.1.7 then you need to either not include those tables or just truncate them before you run the dataOnly upgrade.
In 7.1.8 the migrate script will actually fail if you have uncommitted hot fixes in the rules schema you are trying to migrate.
S&P Global
IN
Hi Pavan,
In the Pre Week where you want to upgrade the production take the copy of Data schema and perform your upgrade on the copied schema, where you will get two new Data and Rules Schema. On the day of production do only a data upgrade on the production schema which will generally take couple of hours. You can use the same Rules Schema which is generated on the pre week upgrade. In this approach you will perform only Data Upgrade.
Thanks,
Gopi
Updated: 22 Jul 2015 12:00 EDT
USDA
US
You can't perform this kind of approach when you are upgrading from 6.X to 7.X. But you can perform this when you are upgrading from 7.X to a latest version of 7.X. Because 6.X and 7.X schema structures and it's methods are completely different. So you can't manually dump 6.X data into 7.X data after the upgrade. It is not a recommended approach. You can take a look at the notes in the below screen shot.
In your post you mentioned that the outage window for 7.X upgrade is going to take 5 to 7 business days which is a very large window. I don't know the reasons why is it being so long? but I would like to suggest you don't go with UI upgrade which would take 2 to 3 business days but rather than that use any Linux machine to perform the upgrade which should take not more than 6 hours. I followed the same approach many times. In that way, you can reduce the outage window timeline. Best of luck !!!...
