copying prod data to crp

B733.2 NT 4.0 SQL 7.0 SP2

Our users want a copy of production data copied to CRP except for the F0911
table which is huge. Can anyone give some insight for the best way to do
this?

Thanks,

Crawford Winter
Senior Technical Analyst
Transamerica Life Canada

Crawford :

R98403 would be fine. You may specify that Object Name != 'F0911'
You can also use SQL-DTS.

Sebastian

Crawford,

Official politically-correct-JDE-approved answer:

Use R98403 with data selection to exclude the F0911 to refresh CRP from PROD. This UBE allows you to select a source and target. You can either specify by environment or by datasource. I prefer datasource so that you know exactly what you are copying. If you choose to use environments to specify source and target, the UBE will use OCM mappings to decide where to get and put the tables. You will want to copy tables from both Control Tables - PROD and Business Data - PROD to Control Tables/Business Data - CRP

R98403 does work. It can be slow because it copies individual records, committing them one at a time. It has been known to skip records and tables during the copy but this is a rare occurence. If you are not comfortable with native SQL server utilities, I recommend you use the R98403.

For your platform, DTS (Data Transformation Services) would be the native tool to use. This should be used by an experienced SQL DBA.

Of course your users need to be aware that since you are not copying the F0911, there will be some referential integrity problems in your resulting CRP environment between the F0911 it and other related files. One benefit of using DTS (besides speed) is that you could limit the number of records copied for the F0911 and only bring over part of it. With the F98403 it is all or nothing.

Regards,

Justin Miller
Teamspot Oy
[email protected]

working with B7332 and XE on AS/400, NT, Solaris and AIX

Copy Business Data from Production to Prototype or any other environment
Database size considerations
Before to update databases is very important to examine the free space available on the Enterprise Server or Data Server where databases reside.

The best practice is to compare the actual size of jde_production and jde_crp.

SQLServer bulk logged option
Before to proceed is strongly recommended to set Bulk logged option on the database jde_crp and set up the option: unrestricted file growth for data and log files.
Number of Rows on critical tables
F0911 Account Ledger
F0011 Batch Control Records
F0101 Address Book Master
F03B11 Customer Ledger
F03B13 Receipts Header File
F03B14 A/R Check Detail File
F4101 Item Master
F4102 Item Branch / Plant
F4111 Item Ledger (Cardex)
F4105 Item Cost
F4106 Item Price
F41112 Item ASOF Files
F4115 Item History
F0411 Accounts Payable Ledger
F0413 Accounts Payable – Matching documents header
F0414 Accounts Payable – Matching documents detail
F4301 Purchase Order Header
F4311 Purchase Order Detail File
F43199 Purchase Order Detail Ledger File
F43121 Receipt Information
F4201 Sales Order Header File
F4211 Sales Order Detail File
F42119 Sales Order Detail Ledger File
F42199 Sales History File

For those tables than exceeds 300,000 records it’s recommended to copy it using SQL commands and exclude it from the R98403 using data selection.

Business Data

In order to copy Business Data from Production to Prototype make your own copy of the R98403 Version XJDE0021 using Batch Versions.

Processing options must be set up right otherwise you can delete the production database.
Database backup should be performed prior to run this process.

This UBE can take several hours up to 48 hours so a weekend run is recommended.
The R98403 must run Local mode.

Data selection:
In order to exclude voluminous tables as explained above, an entry on the data selection of object name (F9860) should be included.

Control Tables

This is the last step to be executed, missing or wrong execution of the R98403 version XJDE0022 will return duplicate keys messages.

§ Make your own copy of the R98403 version XJDE0022 (Refresh Prototype Control Tables From Production). This version will be used to copy control tables from Production to Prototype Control Tables - PROD data source.



§ Edit the processing options for your new version
o For target Environment (option 1), leave blank.
o For target Data Source (option 2), enter Control Tables – CRP.
o For Data Load (option 3), enter 2 – this copies data to the table.
o For source Data Source (option 4), enter Control Tables – PROD.
o For option 6: 1
o For option 7: A Recreate existing tables
o For option 8: 1
o For option 9: Blank

§ Edit the data selection for your version to include the following objects: F9000, F9001, F9002, F9005, F9005D, F9006, F9006D, F9010, F9020, F9021, F9022, F9030, F9050, F91014, F91011, F91012, F91013.

§ Run the version locally. It should copy all central objects tables from Control Tables – PROD to Control Tables – CRP.

§ Verify the results of the report and the creation of the tables. This should copy the following tables to the ESU database: F0002, F00021, F9000, F9001, F9002, F9005, F9005D, F9006, F9006D, F9010, F9020, F9021, F9022, F9030, F9050, F91014, F91011, F91012, F91013, F0004, F0004D, F0005, F0005D, F0082, F00821, F00825, F00826, F0083, F0084.

§ Exit out of Batch Versions application.



Sergio Vargas
Windows 2000 / SQL2000 / B7333 Xe / SP15.1

For those of you who prefer using native database tools, this works for me. I've got a 2GB Production database on SQL2000. Using native SQL backup and the script below I can do a full backup and refresh of Production to CRP in 30 - 40 Mins. The R98403 just takes to long and I've never had a problem using this method. Unfortunately you can't exclude tables but as long as you've got the space you could purge some of the data after the restore and shrink the database to recover some space.

*** Create a file backup device called JDE_PRODUCTION (or whatever you prefer)and backup JDE_PRODUCTION database to this device. Run the script below to restore PROD to CRP ***

RESTORE DATABASE JDE_CRP
FROM JDE_PRODUCTION
WITH RECOVERY,
REPLACE,
MOVE 'JDE_PRODUCTION_Data' TO 'H:\MSSQL\OWData\jde_CRPdata.mdf',
MOVE 'JDE_PRODUCTION_log' TO 'G:\MSSQL\OWLog\jde_CRPlog.ldf'
GO

*** Update the logical file name, this command is only valid in SQL2000, skip this step for SQL7) ***

ALTER DATABASE MODIFY FILE (NAME = JDE_PRODUCTION_Data, NEWNAME = JDE_CRP_Data)
ALTER DATABASE MODIFY FILE (NAME = JDE_PRODUCTION_Log, NEWNAME = JDE_CRP_Log)


*** When restore is complete you need to update object owners. In Query Analyzer select JDE_CRP database ***

SELECT * FROM SYSUSERS

*** Note down UIDs for CRPDTA, PRODDTA, CRPCTL, PRODCTL ***

UPDATE SYSOBJECTS SET UID = 'CRPDTAUID or CRPDTAUID' WHERE UID = 'PRODDTAUID OR CRPDTAUID'

Phil Anderson.
[email protected]
W2K/SQL2K/MSCS
Xe SP18/Update4

Panda,

very clever and I totally agree with you about R98403 - talk about slow! The only thing that has caught me out in the past when using SQL tools is that the indexes don't get recreated if you copy the tables using "insert into * Select * from" commands. But then, your method doesn't need that......

As for leaving out F0911 - don't do it. With JDE it is an all or nothing approach when it comes to Business Data tables. Control tables are another matter.


OneWorld Xe Sp16_018, W2k, W2K TSE, SQL 2K on Compaq Proliant.

Panda or any other SQL2K expert,

Will this script work on MS SQL 7.0 ?

Currently OneWorld B7332/Xe Windows NT SQL 7.0 Server SP3/ Citrix XP (Experienced on all platforms)

Yip, this works fine with SQL7. As I noted in the script, the only command that doesn't work is the ALTER DATABASE . . . command, this is new to SQL 2000. What this command does is change the logical database name (right click on your database and select properties, general tab). I haven't found a command to change this name in SQL7, so once you restore it from PROD, it will reflect JDE_PRODUCTION_Data. This has no impact on JDE as your database name is still JDE_CRP.

Phil Anderson.
[email protected]
W2K/SQL2K/MSCS
Xe SP18/Update4

There is another method that's also quick, but the drawback is it requires a bit of Production downtime.

1. Detach JDE_PRODUCTION and JDE_CRP DB's
e.g sp_detach_db 'JDE_PRODUCTION'
2. Make a copy of the JDE_PRODUCTION DB physical files (using explorer)
3. Rename your existing JDE_CRP physical files as a backup.
4. Rename the copied JDE_PRODUCTION physical files to JDE_CRP.
5. Reattach the JDE_PRODUCTION DB using the JDE_CRP physical files
e.g. sp_attach_db @dbname = 'JDE_PRODUCTION',
@filename1 = 'c:\mssql\data\jde_CRPdata.mdf',
@filename2 = 'c:\mssql\data\jde_CRPlog.ldf'
6. Rename JDE_PRODUCTION DB to JDE_CRP
e.g. sp_renamedb 'OLDNAME i.e JDE_PRODUCTION', 'NEWNAME i.e JDE_CRP'
7. Reattach JDE_PRODUCTION again using the original JDE_PRODUCTION physical files.

Not for the faint hearted or for the by the book JDE people. Make sure that you're doing this with a clear head and not after an all nighter.

Phil Anderson.
[email protected]
W2K/SQL2K/MSCS
Xe SP18/Update4

Following Panda script
Question:
Using SQLServer 7 How to update filename from sysfiles
from JDE_PRODUCTION_Data to JDE_CRP_Data
System tables are protected.????
Otherwise problems can arise having wrong filename.
Thanks

-----------------------------
*** Update the logical file name, this command is only valid in SQL2000, skip this step for SQL7) ***

ALTER DATABASE MODIFY FILE (NAME = JDE_PRODUCTION_Data, NEWNAME = JDE_CRP_Data)
ALTER DATABASE MODIFY FILE (NAME = JDE_PRODUCTION_Log, NEWNAME = JDE_CRP_Log)




Sergio Vargas
Windows 2000 / SQL2000 / B7333 Xe / SP15.1

I may be hazy on this but I think I was cautioned by JDE support to NOT run the R98403 if you have a co-existant environment, it will convert (screw up) some of the shared tables thus breaking the co-existence.

Someone let me know if this is wrong.

We just perform library copys then had to use OMW to get the menus across.


*JDE CNC, CMA
AS400@V4R5, Xe SP16.1 XU3/A73 CUME11, CoExist, NT4.0 (SP6a), NT4.0 Citrix, Win2k Citrix XPe
500 mixed clients

The one time I used R98403 to copy environments, it worked, but it took 2 days. So I now copy libraries. I wanted to warn everyone about copying libraries if you have OS/400 V5R1. I copied DEV to CRP last week, then was unable to use the package that I copied or create a new one. It turns out that V5R1 is having some issues with the BLOB files. I applied the newest DB fix, SF99501 version 6, then cleared files in the Central Objects library and used SQL insert to recopy the records (this is what R98403 would have done, but faster). I was then able to build a new package and everything is fine now.

Jean Driscoll
AS/400 Co-existent Xe SP15+17.1, Update 1/A73Cum12

AS/400 is a beutiful for this. All you have to do is make a back up of you current F0911 file from the CRPDTA. Once this is done, Back up the PRODDTA and PRODCTL to a tape. Delete CRPDTA and CRPCTL and Restore PRODDTA and PRODCTL to CRPDTA and CRPCTL. Once you have restored, then you will be able to restore you F0911 from you backup of CRP.....

We have done this numerous of times and not seen a side affect and much faster.

JAB
OW XE
AS/400 (Enterprise)
WinNT (Deployment)
SP16

Have you tried this with other files at the same time eg; F0411, F0311 and
using an 'as of' date just so you can have a subset to test with?

Just curious.

Regards

Malcolm

Co-extistence AS400 V4R5 & B7333 SP16_019