In a previous post I covered the Stratum.Server DALOAD and GISLSDRV processes. In this post, I am going to discuss a few techniques for optimizing performance of GISLSDRV on Windows. In my next post, I will cover the same for DALOAD on IBMi.
GISLSDRV on Windows
GISLSDRV is most efficient if it can process all the EXTPNDXX or temporary.txt input records in memory in a single segment. Once the size of the input records to be processed exceeds the amount of available memory, GISLSDRV automatically breaks the data into segments for sequential processing. We affectionately refer to this process as chunking.
The GISLSDRV GI.log file will identify if chunking is occurring during the GISLSDRV load process. The first step in the GISLSDRV load process is to calculate how much memory will be required to process the EXTPNDXX/temporary.txt input records in a single segment. The calculation of total memory required includes as input the number/size of the input records and the count of the number of records in the header and detail DSL data tables which are stored in STCSDT15.* Once the GISLSDRV memory calculation is finished, it is compared to the available memory on the server. If there is not enough physical memory available for GISLSDRV to process a single segment, then it will break (chunk) the input records into multiple segments. The initial memory calculation and the number and size of the multiple segments are both reflected in the log file(s).
If you determine GISLSDRV is chunking, a straightforward solution is to add memory to the Windows server so you have approximately 20% more memory than the amount of memory being calculated by GISLSDRV for the single segment.
If adding more memory to the server is not an option, there is an alternative – but it may require a bit more work. You can load the EXTPNDXX/temporary.txt from your source system with just enough data to prevent chunking. This in all likelihood will require modifications to those daily processes that extract source data and populate the input files. If multiple years are being loaded you can try loading a single year, running GISLSDRV and then repeating the process for subsequent years. Another approach is to load the input files with a single category, run GISLSDRV and repeat for other categories. The key is to utilize all the available memory while minimizing the number of times you need to load EXTPNDXX/temporary.txt and run GISLSDRV. The performance improvements can be worth the time spent.
In either case above you will want to check and possibly change the default memory size setting for Stratum. You will find the /MS (Memory Size) switch in the REGISTERY.dat file in the DTPROD directory of the Stratum.Server directory. We would recommend setting this switch to approximately 80% of the memory available on the server.
K-indexes are unique to Microsoft SQL Server and a vast improvement over K-tables. A K-index is considerably smaller than the K-table which allows a greater number of K-indexes vs. K-tables to be stored in memory. This in turn will improve the performance of the GISLSDRV load process. The GIDROPKY executable can be used to create K-indexes once you have chosen which dimensions to include. We recommend all SQL Server customers run GIDROPKY to create these indexes and drop the K-tables. There is a whitepaper on CustomerNet with more specific information on the creation of these indexes.
*The STCSDT15 table can occasionally become corrupt if a GISLSDRV load process ended abnormally and was not recovered through the standard Stratum recovery process. If the table is corrupt, it could adversely affect the GISLSDRV memory calculation. The BLDSLCNT command will refresh the STCSDT15 table if you suspect that your STCSDT15 may be incorrect.
Categorized in: Intelligent Technology
This post was written by Pat Hennel