Do you want to reduce the time taken to export and import a folder from the Safe X3 Console ? If yes, then read on!
From Version 2.46 of the X3 Console you can utilise a native SQL Bulk Copy Program (BCP) feature. This allows faster download of data from SQL Server table data to a file or vice-versa meaning you can greatly reduce the time taken manipulating X3 Folder data.
On testing with SEED folder data,I found that a "normal" export took 13 minutes, but only took 5 minutes using the BCP option! This is a significant time saving, and should be a bigger saving the more user data you have.
Note that even though you are using the BCP option, this still has the same issue as non-BCP export with regards to their being no referential integrity checks and simply dumps out each table in alphabetic order. i.e this process still needs to be run with no activity on the selected folder
Performing an export using the BCP feature
From within the Safe X3 Console , select the desired Folder
Click : Export
Check the option : Export Data to Native Dump Files
Below is an example trace from a SEED folder export comparing both options. The highlighted actions demonstrate the main time saving during the operation.
"Normal" SVG export
21/04/2021 15:32:24 : INFO - ---------------------------------------------------------------------
21/04/2021 15:32:24 : INFO - Folder configuration
21/04/2021 15:32:24 : INFO - ---------------------------------------------------------------------
21/04/2021 15:32:24 : INFO - Starting configuration 21/04/2021 15:32:24
21/04/2021 15:32:24 : INFO - Starting creating the SVG directory
21/04/2021 15:32:25 : INFO - Delete directory 'D:\Sage\X3ERPV12\Folders\SEED\SVG'
21/04/2021 15:32:25 : INFO - End of creation of the backup directories
21/04/2021 15:32:25 : INFO - Starting generating the folder table list
21/04/2021 15:32:25 : INFO - Launching the generation script of the folder table list - D:\Sage\X3ERPV12\Folders\SEED
21/04/2021 15:32:25 : INFO - Recovering the folder table list
21/04/2021 15:32:25 : INFO - Launching the generation script of the folder view list - D:\Sage\X3ERPV12\Folders\SEED
21/04/2021 15:32:26 : INFO - Recovering the view table list
21/04/2021 15:32:26 : INFO - 1901 tables found
21/04/2021 15:32:26 : INFO - End of generation of the table list (00:00:01.5499882)
21/04/2021 15:32:26 : INFO - Starting exporting the folder tables
21/04/2021 15:32:26 : INFO - Extracting the folder tables - SEED
21/04/2021 15:32:26 : INFO - Starting exporting the folder tables - Windows
21/04/2021 15:32:30 : INFO - Script 'D:\Sage\X3ERPV12\Folders\SEED\SVG\_valfilbat.bat' generated, host x3erpv12vm, 1625 table(s) - 276 view(s)
21/04/2021 15:32:30 : INFO - Executing '_valfilbat.bat' to extract tables data - (LibAdm.ExtractTablesWin32)
21/04/2021 15:44:39 : INFO - End of extraction of the tables - Windows
21/04/2021 15:44:39 : INFO - End of extraction of the tables (00:12:12.8367966)
21/04/2021 15:44:39 : INFO - Starting extracting the sequences
21/04/2021 15:44:39 : INFO - Extracting the sequences - SEED
21/04/2021 15:44:39 : INFO - Starting extracting the sequences - SQL Server
21/04/2021 15:44:40 : INFO - Sequence file stream generated, 1625 tables - (LibAdm.ExtractSequenceWin32Sql)
21/04/2021 15:45:40 : INFO - End of extraction of the sequences - SQL Server (00:01:01.2924798)
21/04/2021 15:45:40 : INFO - End of extraction of the tables
21/04/2021 15:45:40 : INFO - Starting generating the srf files
21/04/2021 15:45:40 : INFO - Generating the folder srf files - D:\Sage\X3ERPV12\Folders\SEED\SVG
21/04/2021 15:45:57 : INFO - End of generation of the srf files (00:00:16.3368199)
21/04/2021 15:45:57 : INFO - End of configuration (00:13:32.8091226)
21/04/2021 15:45:57 : INFO - ---------------------------------------------------------------------
21/04/2021 15:45:57 : INFO - End of configuration
21/04/2021 15:45:57 : INFO - ---------------------------------------------------------------------
Bulk copy option Export
21/04/2021 16:20:48 : INFO - ---------------------------------------------------------------------
21/04/2021 16:20:48 : INFO - Folder configuration
21/04/2021 16:20:48 : INFO - ---------------------------------------------------------------------
21/04/2021 16:20:48 : INFO - Starting configuration 21/04/2021 16:20:48
21/04/2021 16:20:48 : INFO - Starting creating the SVG directory
21/04/2021 16:20:48 : INFO - Delete directory 'D:\Sage\X3ERPV12\Folders\SEED\SVG'
21/04/2021 16:20:48 : INFO - End of creation of the backup directories
21/04/2021 16:20:48 : INFO - Starting generating the folder table list
21/04/2021 16:20:48 : INFO - Launching the generation script of the folder table list - D:\Sage\X3ERPV12\Folders\SEED
21/04/2021 16:20:49 : INFO - Recovering the folder table list
21/04/2021 16:20:49 : INFO - Launching the generation script of the folder view list - D:\Sage\X3ERPV12\Folders\SEED
21/04/2021 16:20:50 : INFO - Recovering the view table list
21/04/2021 16:20:50 : INFO - 1901 tables found
21/04/2021 16:20:50 : INFO - End of generation of the table list (00:00:01.5479738)
21/04/2021 16:20:50 : INFO - Starting exporting the folder tables
21/04/2021 16:20:50 : INFO - Extracting the folder tables - SEED
21/04/2021 16:20:50 : INFO - Starting exporting the folder tables - Windows
21/04/2021 16:20:54 : INFO - Script 'D:\Sage\X3ERPV12\Folders\SEED\SVG\_valfilbat.bat' generated, host x3erpv12vm, 1625 table(s) - 276 view(s)
21/04/2021 16:20:54 : INFO - Executing '_valfilbat.bat' to extract tables data - (LibAdm.ExtractTablesWin32)
21/04/2021 16:21:03 : INFO - End of extraction of the tables - Windows
21/04/2021 16:21:03 : INFO - End of extraction of the tables (00:00:12.7208907)
21/04/2021 16:21:03 : INFO - Starting extracting the sequences
21/04/2021 16:21:03 : INFO - Extracting the sequences - SEED
21/04/2021 16:21:03 : INFO - Starting extracting the sequences - SQL Server
21/04/2021 16:21:03 : INFO - Sequence file stream generated, 1625 tables - (LibAdm.ExtractSequenceWin32Sql)
21/04/2021 16:22:01 : INFO - End of extraction of the sequences - SQL Server (00:00:58.2734937)
21/04/2021 16:22:01 : INFO - End of extraction of the tables
21/04/2021 16:22:01 : INFO - Starting generating the srf files
21/04/2021 16:22:01 : INFO - Generating the folder srf files - D:\Sage\X3ERPV12\Folders\SEED\SVG
21/04/2021 16:22:41 : INFO - End of generation of the srf files (00:00:39.7506513)
21/04/2021 16:22:41 : INFO - Starting exporting the database
21/04/2021 16:22:41 : INFO - Exporting the database - SEED
21/04/2021 16:22:41 : INFO - Starting exporting the database - SQL Server
21/04/2021 16:22:41 : INFO - Bcp path built from key 'database.software.sqlodbctools': c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools
21/04/2021 16:25:14 : INFO - End of database export - SQL Server (00:02:32.9937131)
21/04/2021 16:25:14 : INFO - End of configuration (00:04:26.1297438)
21/04/2021 16:25:14 : INFO - ---------------------------------------------------------------------
21/04/2021 16:25:14 : INFO - End of configuration
21/04/2021 16:25:14 : INFO - ---------------------------------------------------------------------
Additional notes
1.Files created
Within the Folders directory the file types present with normal SVG are :-
.cfg .dat .fde .seq .srf
With native BCP export there are 2 additional file types:-
.dmp .fmt
2. There is a slight overhead in additional disk space requirement.
3. You could schedule these exports/imports using the X3 console tools. This will be subject of a future blog article
IN SUMMARY
If you are performing regular folder maintenance tasks, this time saving technique can significantly improve your productivity and leave you more time to focus on other X3 tasks !
