1.1. Purpose
1.1.1. The utility (shift2sql.exe and its renamed copies) will replace the regular data unloading in SQL (the directory/report sever unloads the data in a normal mode).
1.1.2. Under the settings described below the data unload will start automatically using the utility which will help unburden the server and speed up the process.
1.1.3. The utility is launched manually only for experiments and/or mass upload of shift files into another database. I.e. all other mechanisms remain the same (the files are processed from incoming, the same SQL database which is used by the report server is used, etc.). Only the mechanism of transmitting data to the SQL database is changed. All you need to do is to make changes into the .ini file and restart the server. The operation will be launched automatically from there on.
1.2. Utility's Advantages
1.2.1. Does not put an extra burden on the server's *.exe (refsrv.exe or rkReportsServer.exe).
1.2.2. You can modify the unloading algorithm without affecting the server.
1.2.3. You can simultaneously launch several data loading processes.
1.2.4. You can use the memory without shortchanging the server.
1.2.5. Memory leaks become less critical.
1.3. Utility Types
1.3.1. In relevant RK7 versions there are three copies of the described app files that have been renamed:
refsToSQL.exe for unloading directories;
shift2sql.exe for unloading cumulative data;
logsToSQL.exe for unloading the log.
1.3.2. There is a utility variety with a graphic user interface.
1.4. Operation Algorithm
1.4.1. The app makes copies of the input data sets.
1.4.2. Transmits the data which is subject to different checks into memory data sets.
1.4.3. It then relocates the data from the memory to MS SQL.
1.5. Conditions
1.5.1. The utility can be used in directory/report server starting version 7.4.21.265. If it is an older version you need to upgrade the server which unloads the data into an external database (consequently, there may be a need to upgrade the upstream servers).
1.5.2. Starting version 7.5.4.0 shift2sql is installed by default, i.e. no additional settings are required for the .ini file.
2.1. Automatically
2.1.1. The (shift2sql.exe) file should be listed in the catalog alongside the *.exe file of the server unloading the data into an external database.
Note: for versions older than 7.4.21.289 the file is delivered as part of a standard package, there is no need for manual updating (unless you are testing a new version).
2.1.2. The UseShift2SQL option should be added in the CONFIG section in the .ini file of the report/directory server (depending on the server used) (for versions older than 7.5.4.xxx):
[CONFIG]
...
UseShift2SQL = "1"
...
2.2. Manual
2.2.1. To launch it manually you need to enter a text into the command line according to the following template:
shift2sql.exe "ConnectionString" "shiftsFileName" "C:\SomePath\chckconv.xml"
2.2.2. A total of five parameters are used:
2.2.2.1. The "ConnectionString" is the first parameter which is a connection string of the following types: Provider=SQLNCLI10.1;Persist Security Info=True;Initial Catalog=RK7xBASEUPGx20;Data Source=310-RK7-ALAN\SQLEXPRESS;User ID=SA;Password=SomeStrongPAssword. The full ADO Includes username and password.
Or, dataBaseConnection , of the "ByHandle=2888" type
where the value of the ByHandle parameter is an indicator of the open temporary file. The temporary file is created with the FILE_FLAG_DELETE_ON_CLOSE,with bInheritHandle=true flag in the _SECURITY_ATTRIBUTES structure
2.2.2.2. The second parameter is "shiftsFileName" which is the name of the unloaded shift's name (shift.udb)
Use the full path if necessary
, or the data source file name shiftsDataFileName
2.2.2.3. The third parameter is the full path to the chckconv.xml structure description file. This file, dbExportConfigFile containing the data conversion rules, is generated by default in the /base/ file (if the directory server is used for reports) after the data on the first shift is loaded using the (shift2sql.exe) utility,
or also LOGToSQL.xml in the \base\refs2sql\ subfolder
2.2.2.4. The fourth parameter is parentProcIDToWait, is an identifier of the OS parent process. It is a number. As soon as the parent process dies, shift2sql.exe stops operaing . Optional, it is not obligatory to specify this.
2.2.2.5. The fifth parameter is the SQL data loading mode sqlDataLoadingMode regulating the rewriting. It can have the following value:
2.2.2.5.1. "dataover" (unload the entire file deleting the data in MS SQL). Data rewriting: the data will firstly be deleted from all tables and then added from the selected file
2.2.2.5.2. "datasync" checks the entry keys and stamps (RECSTAMP) and adds/deletes/updates only the parts which have been changed, synchronize
2.2.2.5.3. "schmover" Setting up a structure: all the tables will be created anew, all the data will be deleted, schmmake
2.2.2.5.4. "dataload"Adding data: the data will be added to that available in the tables. The values with intersecting keys will be updated.
2.2.2.5.5. "schmsync" Creating a database structure: the existing tables will be modified (update mode for a cumulative dabase).
Optional: not obligatory.
2.2.3. You can include logging into the file by specifying the log file as the location for relocating the command line output:
shift2sql.exe "ConnectionString" "shiftsFileName" "C:\SomePath\chckconv.xml" > logs.txt
2.4. To enable batch processing of several shifts you can use the bat file containing the following:
@echo start
for %%i in (shift*.udb) do (
echo %%i
call "full_path_to_shift2sql.exe" "ConnectionString" "%%i" "full_path_to_chckconv.xml" >logs.txt
)
pause
The bat file should be kept in the same folder as the shift data!