R98403 Alternative , Business Data refresh method?

CNC Guy

Well Known Member
Folks,

As we all know we use R98403 to refresh environments within JDE. What we also know is this takes a lot of time to refresh the data.

I read somewhere that we can use the backend i.e. DB tools to refresh data especially since we normally refresh only the business data. That way it will be must faster and in case of any issues the rollback is going to be proper. The reason for this process being faster is that when using R98403 it copies the data from the source env in DB to local and then to the target environment in DB which is also depepdent on the network bandwidth.

so is there an issue or concern with this method of refreshing business data? I know one issue might be Oracle will not support this method since it is outside JDE but are there are known issues with following this method? Has anybody done this anytime?

Please let me know since I'll want to be sure before following this method?

Thanks in advance.
CNC Guy
E1 8.10
UNIX,DB2
Websphere 5
 
We NEVER use R98403 or any UBE to refresh data from PROD down to QA or DEV. Our DBA's handle the refresh using database tools.
 
I used both E1 (R98403) and database utilities for business data (and occasionally for Central object) refresh on virtually all platforms (except for DB2 UDB)

Generally speaking, it's up to you to decide what’s the best way to do the refresh.

Both have pros and cons

R98403
Pros
- Fairly simple
- Doesn't require specific database knowledge.

Cons
- May take a lot of time (don't forget that it does copy on "record by record” basis) - I have seen 12+ hours copy
- Requires some accuracy when setting up Processing options and data selection for the first time.
- UBE can simply crash, especially on big volumes of data.
- No data integrity (data may change during the copy process)

Database tools (depends on the particular database)-
Pros -
- Usually Takes less time
- Data integrity can be preserved (at least with Oracle database)

Cons
- Specific DBA knowledge may be required
- More chances of breaking something
smile.gif


My personal opinion, - if database is fairly small and you are ok to take a responsibility for the refresh results (it’s a political rather then technical factor) – use R98403
If database is not so small, or you want to share some responsibility with your DBA - use Database tools.
I wouldn’t really worry about Oracle support. In most cases you are not refreshing Production data, and, second, how would they know anyway ? 

Of course, your mileage may vary.
 
I sctipt the process using Database tools for all databases and platforms. I've done this for Oracle, AS/400, SQL Server and yes the very popular DB2 UDB :)>).

For DB2 you need to use the load/unload data process. The database needs to be quiet (down) during the unload but this does take a lot less time.

It is a pain however to load the data into another a set of different tablespaces with different schema's. I actually have our DBA do most of this.

If you're interested in assistance here you can contact me offline. We likely have the only DB2 DBA that also knows how to spell JDE.

You can grab any DB2 DBA off the street but you'll have to specify exactly what you want very carefully.

Colin
 
I wrote this little piece a few weeks ago when asked by a colleague. I'm sure the list will find it beneficial :

Please don't attempt to use anything other than JDE to convert BETWEEN different platforms - otherwise you will chase your tail around the "null space" issue. Nulls and Spaces are treated differently between platforms - so if you want to attempt to reinvent the wheel, good luck. However, using JDE to perform the data conversion will ensure that each row is inserted against the data dictionary correctly.

What I usually do is use Oneworld to perform the refresh between two different platforms as follows :

1. Create a list of objects you want to copy using the database (select * from sysobjects or whatnot)
2. Open this list using Microsoft Word. Run a Search/Replace and convert the list into a Table Conversion Script :
a. TABLE(Fnnnnn) FROMDS(Business Data – PROD) TODS(Business Data – CRP) OWNERID(CRPDTA) OWNERPWD(CRPDTA) CREATE(YES) CLEAR(YES) COPY_DATA(YES)
b. Note – if you use Microsoft Word, you can do a search for “^p” to find carriage returns – then replace with “) FROMDS(Business Data – PROD) TODS(Business Data – CRP) OWNERID(CRPDTA) OWNERPWD(CRPDTA) CREATE(YES) CLEAR(YES) COPY_DATA(YES)^pTABLE (“ – this will put the table name in between the brackets.
c. Create a Table Conversion – you don’t need to check it in
d. Click “next/next/next” through Table/Copy – when it asks for the list of tables – click on “import” and import your new script
e. Run this table conversion on a client – make sure it has good connectivity to the server

Of course, because you’re using OneWorld – you never have to ever worry about data dictionary items. I have serious issues about using database tools to copy/convert data to different database types.

You could use R98403 – but the beauty of the above scripting is that you can split the script into 10 scripts, then run 10 UBE’s on the same box if necessary – and you’ll be running 10 times as fast ! How quick is this ? Well, my last customer converted 100Gb database from AS/400 to SQL Server and I converted the entire database in 8 hours using a single dual-processor intel box and a horribly slow SQL database (temporary use). If I dropped tables like the F0911 and F42199 – I could have completed the entire copy in about an hour !

BUT - what about if you're just looking to copy from, say, Production to PY - ie a Standard EnterpriseOne refresh between environments? Here is the Jon Steel class on Refreshing your Datasources !

1. DB2 - use CPYF and copy libraries and files between PRODDTA/PRODCTL and CRPDTA/CRPCTL. One thing that the As/400 is good at (and is very simple) is refreshes !

2. SQL - either use a backup/restore/rename procedure - or, if you are concerned about the BLOB's - then do what I do and set up a BCP extract script to export every table in parallel as follows. I do this as a nightly job that occurs at the same time as the backup. Note that this is just a DOS batch file :

start /B bcp JDE_PRODUCTION.PRODDTA.Fnnnnn out PRODDTA_Fnnnn /b 100000 /n /t /S{sqlserver} /UPRODDTA /P{proddtas password}

I do this because I get a lot of requests from users asking me to restore xyz into CRP because they screwed something up. The best thing about BCP is that it extracts everything out into binary files that can be compressed pretty darn well. My last customer had 100Gb database - BCP extracted out to about the same size (of course) - but then I ran a compression routine (7zip) and ended up with a 2Gb compressed file ! My nightly job ran the entire process start to finish - taking some time to compress of course - but I'd end up with a 2gb file that I kept for at least 7 days (rotating the name of the file based on the day the script ran) ! Believe me, doing this saves your bacon ! The BCP extract - 100gb - took less than 25 minutes to complete.

So how do you restore BCP files ? Easy peasy. Truncate each file using a nice script - then try the following :

start /B bcp JDE_CRP.CRPDTA.Fnnnnn in {pathtoBCPfile}PRODDTA_Fnnnn /n /t /S{sqlservername} /UCRPDTA /P{CRPDTA's password} /o{outputlogfile} -b10000 -m50 -h "TABLOCK"

Be careful about BCP importing them in parallel - too many threads can cause issues. I usually "space out" my "start /b's". Remember - 98% of the tables are going to be empty or very small - so they'll run VERY fast - leaving just the F0911's to finish up. Make sure you use the Tablelock hint - it really speeds up those really big tables.

Why do this ? why not use DTS ? Quite simply, I hate gui applications when I can create a nice convoluted batch script or command-line process ! DTS won't run anywhere near as fast as BCP - and, to be honest, its based on BCP technology.

Finally - you could always just shut down the database - copy the production database files and remount them - then use stored procedures to rename the table owners. This is how E812 does its install - and its pretty darn fast - IF you have the ability to shut everything down.

If not - and if you have a modern drive array like the Hewlett Packard EVA - then you should script a snapshot routine after performing a "freeze". The EVA can perform a "Snapclone" - again, remount and rename should be a part of your script. Don’t forget to "thaw" your database afterwards !

3. Oracle - probably has one of the easiest and plentiful methods for refreshing. Obviously backup/restore is the simplest method - you can identify a different owner as you restore. Another method is to use SQL - just truncate each table, then run inserts as follows :

INSERT INTO CRPDTA.Fnnnn select * from PRODDTA.Fnnnn

You'd be surprised how fast this method is - your only bottleneck will come on those tables with lots of rows, and lots of indexes. If you think it'd be faster, just drop those indexes as part of your script and recreate them afterwards - or run a "CREATE TABLE CRPDTA.Fnnnnn AS SELECT * FROM PRODDTA.Fnnnn"

With the same token as SQL Servers BCP - you could always use imp80 (or whatever version of oracle you're using). This is how JDE creates everything using LOADALL - but on Oracle, it’s a lot slower than SQL so be careful.

Anyway - I hope that explains all the platforms and how you should refresh. With all the above together with my JDE Tableconversion Scripting Language (documented at Quest Global last year) - you really should be able to move data with ease !

Hope that helps !
 
Jon,

just a few comments here.....

Why use Table Conversion when you can just use the R98403 with data selection and still run this in parallel?

Also instead of the R98403 you can use the R98403B "Bulk Copy Tables" which is better than the R98403 and TC which do one table, one row at a time.

****************************************************************
For the AS/400 I always prefer SAVE/RESTORE instead of CPYF. CPYF does not guarantee a consistent image since you're copying a moving target......same as the R98403.
I prefer something like this

1. CRTLIB (JDETEMP)

2. CRTSAVF (JDETEMP/PRODDTA)

3. SBMJOB CMD(SAVLIB LIB(PRODDTA) DEV(*SAVF) SAVF(JDETEMP/PRODDTA))

4. SBMJOB CMD(CLRLIB(CRPDTA))

5. SBMJOB CMD(RSTLIB SAVLIB(PRODDTA) DEV(*SAVF) SAVF(JDETEMP/PRODDTA) MBROPT(*ALL) ALWOBJDIF(*ALL) RSTLIB(CRPDTA))

You can also use the above script to grab the libraries from tape.

****************************************************************
For SQL Server I prefer to use a simple attach/detach. Since everyone backs up the database nightly it's pretty easy.

1. Detach the databases for the PY environment and rename the physical files

2. Restore the PD databases files and rename then to the PY file names. This can be done using a simple file system restore or if you have the window stop the DBMS and do a simple file copy.

3. Reattach the database files and "attach as" the PY database and specify the correct owner

4. Run a script to change the object owners from PRODDTA and PRODCTL to CRPDTA and CRPCTL (just Google it)

5. Run a script to change the internal file names on the database from 'JDE_PRODUCTION_log' and 'JDE_PRODUCTION_Data' to 'JDE_CRP_log' and 'JDE_CRP_Data'

6. In SQL Server security change the default Database on the CRPDTA and CRPCTL to the PY database

7. Run a script to update the public permissions on the database (just Google it).

8. Finally run an update stats and DBCC update usage on the database.

The entire process should take 15 minutes plus the time it takes to do the restore or database copy. I prefer this over DTS which is very inefficient. I also like this more than BCP because I don't have to truncate and can roll it back in just 15 minutes - it's also an exact image.

****************************************************************
As for Oracle.....enough said. It just shouldn't be an issue. Oracle by default loads all tables into one big bastabase which is different from all other platforms so the INSERT INTO CRPDTA.Fnnnn select * from PRODDTA.Fnnnn is very easy. Backup and restore is also very easy.

****************************************************************
For DB2 UDB on Windows the database is actually loaded as 4 different databases. This means that INSERT INTO CRPDTA.Fnnnn select * from PRODDTA.Fnnnn simply won't work. You have to go through the process of "Federation" which is annoying at best.

Backup and restore won't work because this will preserve the table space names and all of the schema owners.

To load data from one schema to another schema in the same database use something like this in a script:

:f_onetable
echo declare c_%2 cursor for select * from %1.%2; >>%_tmpfile%
echo load from c_%2 of cursor replace into %3.%2 copy yes to %_copyloc%; >>%_tmpfile%
echo commit; >>%_tmpfile%

To load data from one schema to another schema in different databases use something like this in a script:

echo connect to %_srcdb%; >>%_tmpfile%
echo export to %_copyloc%\%1.ixf of ixf lobs to %_copyloc% modified by lobsinfile select * from %_srcschema%.%1; >>%_tmpfile%
echo connect to %_tgtdb%; >>%_tmpfile%
echo load from %_copyloc%\%1.ixf of ixf lobs from %_copyloc% modified by lobsinfile replace into %_tgtschema%.%1 copy yes to %_copyloc%; >>%_tmpfile%
echo connect reset; >>%_tmpfile%

Obviously this is much more complex and requires a skilled DB2 UDB DBA (wow that's a mouth full).



Colin
 
Yes - as stated, there are a bunch of ways to refresh and I'm sure others have their own opinions behind this. Its all personal preference. Most of mine, including the SQL BCP method, can be scripted.

Its good to finally see people putting answers to JDEList rather than just comment on stuff.
 
We have literally stopped using the R98403 to do any sort of data refresh, Since Xe we have been using a SQL Job to do our data refresh and now with 8.12 we are recently modified our process to use SQL to do the data refresh, this process takes about 2 hours compared to the R98403 taking in excess of 12 hours and well if it fails you would have wasted 12 hours (never to be regained) and if you are using CFR 21 you still have to manually copy the audit tables because R98403 does not recognize them.

We tried using it this past weekend because our script was not fully ready and guess what after 12 hours the security token expired and the job failed, after resetting the token per Oracles instructions, which was incorrect it failed again, so as of today my SQL script is done and it is now taking 2 hors to do a full data refresh from PROD to PY.
 
Thanks everybody for their tips and advice. I too was a little bit skeptical with having the DBA's to do the copy but since we have huge amount of business data and I remember one time I was trying to copy a table via OMW and it failed. So I would prefer asking the DBA's to do the overall Business data DB refresh from PD.

BTW we are on UDB DB2 (over UNIX) so I belive once we tell the DBA's the tablespace : to and from we should be fine. I do not wish to get into the R98403 since I am sure for data as huge as ours we can run into more than 15 hours for the data copy.

I remember somebody quoting here that most of the DBA's would not know what JDE is. If we tell them the tablespace can there be an issue?

I am still worried on which way to take?

CNC Guy.
E1 8.10
UNIX, DB2
 
Also as I understand I would only need to have them copy the PRODCTL and PRODDTA schema tables i.e. business data and control tables from PD? Would I need to have the other schema copied as well i.e. the complete tablespace?

Once again we are on UDB DB2 (UNIX)

Please let us know.

Thanks,
CNC Guy
E1 8.10
UNIX, UDB DB2
 
[ QUOTE ]
Yes - as stated, there are a bunch of ways to refresh and I'm sure others have their own opinions behind this. Its all personal preference. Most of mine, including the SQL BCP method, can be scripted.

Its good to finally see people putting answers to JDEList rather than just comment on stuff.

[/ QUOTE ]
Heh, Tring to be a poster then a answer leech.

I guess I am just not big on writing scripts. It probably comes from my inherited gift of not being able to spell my way out of a paper bag
cool.gif


R98403 works good, I have done DTS too. R98403B sound good though I have never used it. What kind of senerious would it be better for?

DTS or detach/rename/reattach sound effective but what about when you only want to do half the DB? Like Bus Data but not the control tables?

Here's something that I thought of reading the posts, I thought it fit the conversation, hahaha

www.vintagehorn.com/switchlinux3.swf
 
I sctipt the process using Database tools for all databases and platforms. I've done this for Oracle, AS/400, SQL Server and yes the very popular DB2 UDB :)>).

For DB2 you need to use the load/unload data process. The database needs to be quiet (down) during the unload but this does take a lot less time.

It is a pain however to load the data into another a set of different tablespaces with different schema's. I actually have our DBA do most of this.

If you're interested in assistance here you can contact me offline. We likely have the only DB2 DBA that also knows how to spell JDE.

You can grab any DB2 DBA off the street but you'll have to specify exactly what you want very carefully.

Colin

Hello

I would like to know more about DB2.

As I have a requirement for refreshing PY from PD in AS400.

You can also contact me on [email protected]


Thanks,
Aniket
 
DB2 on AS400 is Different from DB2 UDB on other platforms (which is what Colin was talking about ).

The answer to your question (Refreshing a data library on AS400) has also been answered in detail (by Colin again) in this very same thread. Scroll back a few pages (or even read this entire thread from the beginning).
 
Back
Top