Refresh data from PY to DV

We need to copy the tables from PY to DV for a full data refresh. We are on SQL 2005. We need to do it on the database level instead of the using the JDE table copy function as that would take too much time.

Any help would be greatly appreciated.

The fastest way I perform a reliable refresh on SQL is to BCP Extract all the tables, truncate the destination tables and then reimport them.

Depending on the size of your F0911 depends on how long the process takes. Here are some clues :

First of all, the extract :
[ QUOTE ]

s:
mkdir s:\backups\database\dba\PROD_DATA
Cd s:\backups\database\dba\PROD_DATA
start /B bcp JDE_PRODUCTION.PRODDTA.F0000194 out PRODDTA_F0000194 /b 100000 /n /t /Ssvowsql /UPRODDTA /P{proddta password}
start /B bcp JDE_PRODUCTION.PRODDTA.F00022 out PRODDTA_F00022 /b 100000 /n /t /Ssvowsql /UPRODDTA /P{proddta password}
start /B bcp JDE_PRODUCTION.PRODDTA.F00023 out PRODDTA_F00023 /b 100000 /n /t /Ssvowsql /UPRODDTA /P{proddta password}
.
.
start /B bcp JDE_PRODUCTION.PRODDTA.FD3N915 out PRODDTA_FD3N915 /b 100000 /n /t /Ssvowsql /UPRODDTA /P{proddta password}


[/ QUOTE ]

Note that all the extracts run in parallel. With 3,000 tables in oneworld - you'll see literally 3000 BCP processes start up - there doesn't seem to be any limitation on extracting out the data. The majority of the BCP processes will be extracting out empty or very small tables - and will start and finish in a few seconds. The final few will be the F0911 and the really big tables in your system. Depending on the speed of your system, a full 150Gb production database takes around 35 minutes to extract to disk (ie, the F0911 takes about that long to complete). I run this every night as part of my backup - since it enables me to individually restore tables upon request. I also keep a few "archive" (zip) versions of this directory around for a few days, just in case. It means I usually don't have to go back to tape if I have to restore something !

Next, truncate your database :

[ QUOTE ]

if exists (select * from dbo.sysobjects where id = object_id(N'[TESTDTA].[F0000194]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
truncate table [TESTDTA].[F0000194]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[TESTCTL].[F0002]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
truncate table [TESTCTL].[F0002]
GO
.
.
.

if exists (select * from dbo.sysobjects where id = object_id(N'[TESTDTA].[FD3N915]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
truncate table [TESTDTA].[FD3N915]
GO


[/ QUOTE ]

Obviously, this script is created simply enough from inside SQL Enterprise Manager.

Lastly, the restore from BCP file :

[ QUOTE ]


Cd PROD_DATA
start /B bcp JDE_DEVELOPMENT.TESTDTA.F0911 in PRODDTA_F0911 /n /t /Ssvowsql /UTESTDTA /P{testdta password} /o "s:\output_f0911.txt" -b10000 -m50 -h "TABLOCK"
start /B bcp JDE_DEVELOPMENT.TESTDTA.F42199 in PRODDTA_F42199 /n /t /Ssvowsql /UTESTDTA /P{testdta password} /o "s:\output_f42199.txt" -b10000 -m50 -h "TABLOCK"
bcp JDE_DEVELOPMENT.TESTDTA.F0015Z1 in PRODDTA_F0015Z1 /n /t /Ssvowsql /UTESTDTA /P{testdta password} /o "s:\output_f0015z1.txt" -b10000 -m50 -h "TABLOCK"
start /B bcp JDE_DEVELOPMENT.TESTDTA.F001651 in PRODDTA_F001651 /n /t /Ssvowsql /UTESTDTA /P{testdta password} /o "s:\output_f001651.txt" -b10000 -m50 -h "TABLOCK"
.
.
.


[/ QUOTE ]
Note that here, the F0911, F42199 and F0015Z1 will start in parallel - then the script will pause until the F0015Z1 finishes before continuing on starting F001651 in parallel. I do this to "stutter" the process somewhat - running 3000 imports in parallel causes the system to actually cause issues - ending up with some tables not actually restoring properly (a microsoft resource limitation somewhere along the line). In effect, I place my restores in order based on largest through to smallet, and delete the "start /b" wherever I want to create a "pause".

I also run the tablelock hint - so make sure that nobody is on your system in DEV when you restore, otherwise they'll get into a deadlock. It can take a few hours to perform the full restore - ie thats how long the F0911 takes to restore because of all the indexes.

Lastly, there IS indeed a way to get the JDE Table Copy function to run JUST as fast as a SQL refresh - its all about running the process in parallel. I have another customer that uses JDE to perform their refresh and the 9 Table Conversion UBE's take abut 4 hours to completely refresh their data. That way, I can even schedule it if I wish (!)

Hope that helps - email/phone if you have any questions.

By the way - I expect to be flamed over using BCP, compared to DTS - but if you want to use DTS to do this - knock yourselves out (I just like using scripts and automating most of it. I hate to babysit a GUI process !)

Both BCP and DTS approaches are fine but I find them unnecessarily complex and slow for the purpose of a mass data refresh. They are faster than R98403 but still a table level copy solution.

If your goal is to refresh business data and control tables from one database to another and you have decided to use a native approach over R98403 you might consider two other approaches: "backup/restore" and "detach/copy/attach"

(On a side note I share Jon's preference to use BCP as well as backup every night. I do this with SQL DB's and also with Oracle DB's in that I Export as well as backup. I like the flexibility to get to backups of individual tables quickly. In this case though I don't believe that BCP is the most efficient approach for a data refresh)


backup/restore
==============

If you cannot afford downtime against your source environment the backup/restore approach works well. Backup your PY database and restore over your DV database.

detach, copy, attach
====================

If you can afford a short period of downtime in your source database you may consider the "sp_detach_db, copy db files and sp_attach_db" method.

In both of the methods above you will need to execute "sp_change_object_owner" over the restored or attached database tables to change owners (in your case) from CRPDTA/CRPCTL to TESTDTA/TESTCTL. You will also want to change the logical file names on the restored or attached database so that the logical names of your JDE_DEVELOPMENT files are not JDE_CRP. This can be done via "ALTER DATABASE {dbname} MODIFY FILE (NAME = N'{Filename}.LDF',NEWNAME = N'{newFilename}').

You can eliminate the need to change object owners if you configure your databases and E1 datasources so that PRODDTA/PRODCTL is used in every database. I personally setup my E1 installations this way to facilitate data refreshes over the project cycle. With that in place you are only left changing the logical filenames and that step is only required for cosmetic reasons. The logical name doesn't have to be changed.

All of this can be scripted and scheduled.

Some considerations when deciding between the two:

+ detach/attach will be faster than backup/restore

- detach/attach requires that the source database is unavailable for a period of time (the time it takes to copy the physical datafiles after detach and the time to reattach the source database).

+ detach/attach guarantees that there are no active transactions in the source as the source is offline when detached.

+ with backup/restore the restore source can be the previous night's backup

Drop me a note directly if you need any more detail on the approaches.

Regards,

And now you don't hae to worry about the object owners...............



SELECT 'EXEC sp_changeobjectowner ''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''', CRPDTA'
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA = 'PRODDTA'

Hi,

Just wanted to add a few remarks to both DTS, bcp and
backup/restore procedures :

bcp : by far it's the fastest transfer method, but it
won't transfer stored procedures, triggers and views
you have created on the database.

I know that vanilla JDE doesn't use any of that stuff;
but many real-world companies do implement these
solutions on JDE databases to manipulate data, audit
table access, export data to 3rd party apps, accelerate
performance, etc.

2. DTS : is slower than bcp but allows you to copy
both data and objects (table definition, permissions,
triggers, stored procedures, views, users, etc).
It also quite flexible and allows you to modify
destination tables, manipulate data on the fly via
scripting, etc.

3. Backup/Restore or attach/detach, they're easy and
quite straightforward to implement via scripting.
The only problem I see is the "ownership", you'll
have PRODDTA/PRODCTL tables on both sides.
You can either change owners and reapply permissions
(which I prefer) or leave all databases with
PRODDTA/PRODCTL owners.

There's nothing wrong with leaving PRODDTA/PRODCTL owners
everywhere, however it can easily confuse those who
manipulate data via SQL Query Analyzer (specially if you
log to the wrong database!)

[ QUOTE ]

bcp : by far it's the fastest transfer method, but it
won't transfer stored procedures, triggers and views
you have created on the database.


[/ QUOTE ]
Nor will it create tables or permissions. The method I used expects the tables to already exist - I truncate the data (extremely fast) and then "refill" the data using the BCP method.

This is a personal preference. I of course have performed backup/restore and the copy process with the sp_changeowner - but the copy process requires the database to be brought down (impacting production) - and I really don't like Microsofts backup/restore process to much unless its a real emergency (under older versions, I got burnt by the lack of LONG RAW support !!!). Not only that, but I try to stay clear of the manipulation of the master objects if at all possible. Again, if you run BCP in parallel - it takes NO LONGER to backup/restore than a backup - try it, you'll be very impressed with the speed.

In comparison - a full microsoft backup of a 200Gb database at one customer took 1 hour and 4 minutes - whereas the BCP extract took 47 minutes to complete last night. Admittedly, the BCP extract was running at the same time as the backup - so the BCP probably had priority !!!!