Loading Shifts Using Shift2sql

Overview

Purpose

The utility — shift2sql.exe and its renamed copies — replaces the regular data unloading in SQL. In a normal mode, the data is uploaded by the reference or report server.
Under the settings described below, the data unload will start automatically using the utility. It will help to reduce the load of the server and speed up the process.
The utility is launched manually only for experiments and/or mass upload of shift files into another database. That is, all other mechanisms are the same: the files are processed from the Incoming folder, the system uses the same SQL database, which is used by the report server, etc. Only the mechanism of transmitting data to the SQL database is changed. All you need to do is to make changes to the INI file and restart the server. Further, the operation will be launched automatically.

Utility's Advantages

• It does not overload the server's *.exe (refsrv.exe or rkReportsServer.exe)
• You can modify the unloading algorithm without affecting the server
• You can simultaneously launch several data loading processes
• You can use the memory without shortchanging the server
• Memory leaks become less critical.

Utility Types

In update RK7 versions there are three copies of the described app files that have been renamed:

• refsToSQL.exe for uploading references
• shift2sql.exe for uploading cumulative data
• logsToSQL.exe for uploading the log.

There is a utility version with a graphical user interface.

Operation Algorithm

The app makes copies of the input data sets.
Then, it transmits the data, subject to different checkings, into memory data sets.
After that, the data is transmitted from the memory to MS SQL.

Conditions

The utility can be used in the reference or report server since version 7.4.21.265 and higher. If you have an older version, it is necessary to upgrade the server which unloads the data into an external database — consequently, there may be a need to upgrade the upstream servers.

Since version 7.5.4.0, shift2sql is installed by default, i.e. no additional settings are required for the INI file.

Utility Usage

Automatic

The shift2sql.exe file should be present in the directory alongside the *.exe file of the server unloading the data into an external database. 

The UseShift2SQL option should be added in the CONFIG section in the INI file of the report or reference server, depending on the server used (for versions lower than 7.5.4.xxx):

[CONFIG]
...
UseShift2SQL = "1"
...

Manual

To launch the utility manually you need to enter a text into the command line or an executable file according to the following template:
shift2sql.exe "ConnectionString" "shiftsFileName" "C:\SomePath\chckconv.xml"

There are five parameters to be used:

1. The first parameter is
   1. ConnectionString  — a connection string of the following type: Provider=SQLNCLI10.1;Persist Security Info=True;Initial Catalog=RK7xBASEUPGx20;Data Source=310-RK7-ALAN\SQLEXPRESS;User ID=SA;Password=SomeStrongPAssword — the full ADO connection string, includes the username and password
   2. 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 flag, and with bInheritHandle=true  in the _SECURITY_ATTRIBUTES structure
2. The second parameter is
   1. shiftsFileName — the name of the unloaded shift's name (shift.udb). Use with the full path, if necessary.
      
   2. or shiftsDataFileName — the data source file name 
3. The third parameter is
   1. dbExportConfigFile — the full path to the chckconv.xml structure description file. dbExportConfigFile contains the data conversion rules and is created by default in the /base/ folder (if the reference server is used for reports) after the data on the first shift is loaded using the shift2sql.exe utility
   2. or LOGToSQL.xml in the \base\refs2sql\ subfolder
4. The fourth parameter is a numeric one — parentProcIDToWait — an identifier of the OS parent process. In case of the parent process death, shift2sql.exe stops operating as well. An optional parameter, not obligatory to be specified.
5. The fifth parameter (optional, not obligatory) — sqlDataLoadingMode —  is the SQL data loading mode that regulates the overwriting. It can have the following value: 
   • dataover — uploads the entire file deleting the data in MS SQL. Thus, carries out data overwriting: the data is deleted from all tables and then added from the selected file
   • datasync — checks the entry keys and stamps (RECSTAMP) and adds, deletes, or updates only the parts which have been changed
   • schmover — creates a structure: all the tables will be created again, all the data is deleted — schmmake
   • dataload — adds the data: the data is added to that available in the tables. The values with intersecting keys will be updated.
   • schmsync — creates a database structure: the existing tables are modified (update mode for a cumulative database). 

If needed, it is possible to enable 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

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