Data Refresh Of An Environment

Jeff George

Jeff George

Well Known Member
What is the current consensus on best practice methodology for doing this? Is it by using the JDE UBE? I want to make sure that only data is copied between the tables and that the tables themselves are not replaced. My CNC team is currently doing a DB2 library restore of the tables from PROD to PY/DV, which sometimes replaces tables that have been modified in DV or removes tables that don't exist in the PROD environment. The justification for not using the UBE is that it takes longer.
 
The justification for not using the UBE is that it takes longer.

That justification still holds true. Your iSeries administrator can/should modify the CL program to not remove tables that are not in DV. For existing tables, there is no easy way other than to change the CL program to skip those tables (if your iSeries admin has the expertise, she/he can compare table schema and skip those tables). It is common practice to hold off on replacing certain tables like UDC, system constants etc. especially when the business team is testing / implementing new functionality in the lower environments.
 
Once your data gets past a certain size then the UBE approach isn't really an option anymore.
We're Oracle DB and have scripted a Import process to copy PRODDTA/PRODCTL into CRP or DEV.

Part of the script process is to run SQL to change things such as Address Book email addresses so that testing in CRP/DEV doesn't go out to Customers, Suppliers, etc.

We also find this SQL to be critical
DELETE CRPDTA.F00165
WHERE GDQUNAM = 'OLEQUE';
in order to prevent users from messing with Production Media Object files pointed to by F00165.
 
I though that the R98403 (or whatever that job is called) allowed you to copy Business Data without recreating the tables in the target environment. Am I misremembering?
 
We do refreshes using database tools instead of the UBE as well. The way we handle your concerns is to identify tables that don't exist in PD or have changed is with backup scripts. We are SQL Server, so I don't know the details of DB2, but we use a script that looks at the create and modify dates on the tables in PD and PY. You can usually tell tables that have changed since the majority of the PY tables should have the date from the last refresh which means any dates after that will have changed. We use scripts to copy those tables to a backup/utility/generic database and restore the tables from that database after the refresh.

The biggest hassle with using database refresh is the UDO architectures in 9.1. I haven't tackled that issue since it hasn't been a big complaint for us, but I am concerned about it as we are upgrading to 9.2 where UDO is greatly expanded. It would be great if Oracle provided some type of "admin sweep" in OMW to be able to identify all the UDO objects in PD and demote them down to PY which could be used after a database refresh. Better yet, someone would create a third party tool to do the required moves using database tools.

I just read Hari's response and it sounds to me like I should clarify. When I say database tools I mean restoring backups, not running scripts, to move the data. I take a full backup from the Production database and restore it as the CRP database. I then use scripts to change owners, logins and update stored procedures and functions. It takes some prep time to refresh the scripts to catch new objects that need changed, but the benefits are tremendous. I can do it during the day with no impact on the production system at all and it is very fast.
 
Last edited:
Jeff, like you see from the comments above every customer has some unique needs and the CNC team works around those needs by customizing the scripts at the DB level. I doubt you will find any customer who uses the JDE UBEs to copy data .. yes it could be used in certain specific scenarios in a limited way but still you would find the job of refresh is handled better at the DB level regardless of the platform. Hope that answers your question.
 
Back
Top