Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Operating Systems & Databases

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 19th July 2017, 15:39
Ivanus Ivanus is offline
Junior Member
 
Join Date: Sep 2014
Posts: 23
Ivanus is on a distinguished road
Baan: 5.0b - DB: MS SQL - OS: Windows Server
Sync tables between 2 databases
Baan: iBaanERP 5.0c
DB: SQL Server 2008 R2
OS: Windows 2008
C/S: None/Unknown

Hi, we are having a production system and on a separate Server our Testsystem. Both are having the same OS and the same Version of SQL Database.
To refresh our Testsystem from time to time I was thinking about to synchronize some tables we need from the production system to the Testsystem.
I don't want to copy the whole database because then I have to make a lot of adjustments on the Testsystem again.
Any idea how this could work?
Thanks
Reply With Quote
  #2  
Old 19th July 2017, 22:54
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,936
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
I would not do this for test - but I am not a DBA, but stuff gets out of sync and it makes it hard to test. But it should work assuming you get all the necessary tables and the referenced tables - I am thinking from a 4c4 perspective. What we do is we actually have another copy where we do not load certain tables (like tfgld tables) but we know for a fact those tables are not needed in the simulation company. All this really does is make the cloning faster for that company. Just my opinion.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #3  
Old 20th July 2017, 12:06
Ivanus Ivanus is offline
Junior Member
 
Join Date: Sep 2014
Posts: 23
Ivanus is on a distinguished road
Baan: 5.0b - DB: MS SQL - OS: Windows Server
Hi, we also need the finance tables in our Testsystem (it's also our Development System).
I also tried it with the common way to dump in BaaN the whole company out but this takes 15 hours and than I have to import it in the Testsystem and this will also take more than 15 hours.
Any other suggestion?
Thanks
Reply With Quote
  #4  
Old 21st July 2017, 14:23
jeffersyuan jeffersyuan is offline
Senior Member
 
Join Date: Jan 2006
Posts: 275
jeffersyuan is on a distinguished road
Baan: B4,B50C, LN FP1-FP10 - DB: SQL Server , Oracle - OS: Window , Unix , AIX
Hi,

To myself, I would like you to write a small program to do the sync job.

Indeed, I just finish one small tool which support copy company data between two different database, for example, from SQL to Oracle, from sql to sql, from oracle to sql, from oracle to oracle etc.

Jeffers

Quote:
Originally Posted by Ivanus View Post
Hi, we also need the finance tables in our Testsystem (it's also our Development System).
I also tried it with the common way to dump in BaaN the whole company out but this takes 15 hours and than I have to import it in the Testsystem and this will also take more than 15 hours.
Any other suggestion?
Thanks
__________________
The Simply is The Best
Reply With Quote
  #5  
Old 9th August 2017, 16:24
toolswizard toolswizard is offline
Senior Member
 
Join Date: Aug 2001
Location: USA
Posts: 292
toolswizard is on a distinguished road
Baan: 4c4, 50b, 50c, ERP ln - DB: Informix, Oracle, Sql - OS: Unix, NT, AIX
We have a 2tb database on Oracle. Originally we did the dump/load from oracle and it would take 5 to 7 days.

I broke the seq dump down into segments, the largest files in their own job, the smaller ones in groups. I got the job down to 30 hours. We have faster processors now on the production system and run 10 jobs at a time, getting the dump down to 2 1/2 hours from 20. Total time now is about 18 hours total. Loading is slower as the processors and memory are less, and we only run 6 to 8 jobs at a time.

If you did the same, breaking down the total tables into several jobs, you should be able to get it down in a few hours.
__________________
ToolsWizard
Reply With Quote
Sponsored Links
  #6  
Old 14th August 2017, 20:35
GaryEd GaryEd is offline
Senior Member
 
Join Date: Sep 2001
Location: CT, USA
Posts: 135
GaryEd is on a distinguished road
Baan: 4c4 sp 30 - DB: SQL 2008 R2 - OS: Windows 2008 R2
This is how we do it

Notes:
Be very careful doing this as it is very easy with one simple typo to wipe out a whole lot of data.
Before beginning make sure table sharing between test companies matches production companies.
Running the exports (step 5) will lock the table during the dump so it must be run when no users are on the system (including jobs).
1. Run session ttaad4222m000 (count number of records by table) for each production company. Output to a text file.
2. Run baan sql query “logical”. Output to a text file. (this query is specific to us but basically gets a list of logical tables to be able to filter them out of the lists of tables to dump)
3. Using MS Access (or some other method) create a text file for each company with a list of tables that are not a logical table and that contain data. Save the file as XXXseqdump.txt in the E:\baan\bin folder (where XXX = company number). The idea is to only dump tables that contain data and to not dump data that is physically in another company so that the process runs as quickly as possible.
4. From a command prompt on the server change to the E:\baan\bin folder and run the following to override the normal default settings. This will help the process run quicker (per baan support).
fillenv
Set USER=baan
set RDS_FULL=100
set SSTS_SET_ROWS=1000
set MSQL_ARRAY_FETCH=1
set MSQL_ARRAY_INSERT=1
set MSQL_MAX_ARRSZ=100
5. Run the following command for each company in turn (where XXX = company number).
bdbpre -t"|" -IXXXseqdump.txt -oE:\seq_dumps\XXX –CXXX
6. Run baan session tccom0214m000 (from file/run) in each source company. The directory must match the folder where you dumped the data (see step 5). You must specify all prod company to test company mappings. The field is not big enough to do it all at once so you need to split it in two and run the session twice. The format is prod1>test1;prod2>test2 (separator is semi-colon).



7. Run session ttaad4231m000 to delete all tables in each test company. You will get a report stating that logical tables could not be deleted, this is ok. This makes sure that there is no orphan data in tables that are not populated in the production company.
8. From command prompt run following for each company (where XXX = source/prod company and YYY = target/test company).
bdbpost -t"|" -kmnf -DE:\seq_dumps\XXX -cYYY
9. Run session ttaad4230m000 to create remaining empty tables in each test company.
10. Log in to test company and make sure all is ok.
__________________
Gary
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
View tables of two databases from one Baan instance tharden Tools Development 1 27th May 2005 23:47
How can I restore dumps of TT package tables? zyilmaz Tools Administration & Installation 12 31st January 2005 11:45
access to databases and tables out of baan lli-baan Tools Development 0 28th August 2003 10:57
Tools Tables which can be created for a company different than 000 i96nds Tools Development 11 13th March 2002 02:07


All times are GMT +2. The time now is 00:20.


©2001-2017 - Baanboard.com - Baanforums.com