Product Documentation
Abstract
The IBM i Access Client Solutions data transfer to an XLS file format has size limitations that correlate to the amount of memory available in the Java virtual machine (JVM).
Content
The IBM i Access Client Solutions data transfer feature provides the function to download data to a file in XLS and XLSX file formats. The limitations discussed in this document apply to the XLS file format, not the newer XLSX file format.
The IBM i Access Client Solutions data transfer tool uses an open source component, Apache POI, to create and populate the XLS file. The POI classes to create the XLS file creates the entire spreadsheet in memory rather than streaming the data to disk. These classes introduce a requirement that the entire spreadsheet must fit into the memory available to the Java virtual machine (JVM). With that in mind, you can download more records if you have more memory available. The first thing to do then is to ensure that you are using a 64-bit version of Java because 32-bit versions are limited to using less than 2 GB of memory. You can then start IBM i Access Client Solutions by using the Java command and specifying the minimum amount of memory for the JVM by using the following syntax:
java -Xmx8192m -jar <path>/acsbundle.jar
This command gives the JVM a minimum of 8192 megabytes of memory (8 GB).
The number of rows of data you are able to download before you encounter the memory limit varies depending upon the size of the rows and the amount of memory available to the JVM.
If you reach the limit, the number of rows transferred stops increasing. Unless you started IBM i Access Client Solutions from a command shell, you have any other indication that the out of memory failure occurred. At that point the IBM i Access Client Solutions client can do nothing because the JVM is no longer able to do anything. Here is an example of the errors you see when you start IBM i Access Client Solutions from a command prompt so that the JVM has a way to display errors (STDERR device is the command session). In this example, I started IBM i Access Client Solutions with a 32-bit JVM and only gave it 1 GB of memory. After about 200,000 rows of my 18 million-row table were downloaded, the JVM ran out of memory.
After you encounter this failure, the only option is to kill the process. The excel file is not be created because the POI code for XLS writes the file to disk only after it is created in memory.
Based on a few tests and some rough extrapolations, it would require over 72 GB of memory for me to be able to download the 18 million-row table that I was using for my test.
Alternatives
Downloading to an XLSX format, the modern Excel file format, is the most obvious alternative. The Apache POI classes for the XLSX file format stream the data to the file rather than create it in memory, so it is able to create the file. In my test, I was able to download the 18 million-row file in about 81 minutes.
There are techniques to convert an XLSX file to XLS format, here is one example that uses Windows PowerShell: https://superuser.com/questions/875831/using-powershell-is-it-possible-to-convert-an-xlsx-file-to-xls.
The transfer to active spreadsheet is another option. This feature does make extensive use of memory as well but as much as the transfer to XLS file does. And it is the Excel process that uses this memory, not IBM i Access Client Solutions, and not its JVM. Using my 18 million-row test table, I was able to perform the transfer to active Excel spreadsheet in about 33 minutes. The Excel process consumption of private memory peaked at 8.8 GB. Excel didn't release that memory until the file was closed (because that is how much memory Excel requires to open this 18 million-row workbook).
Document Location
Worldwide
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSRQKY","label":"IBM i Access Client Solutions"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Was this topic helpful?
Document Information
Modified date:
24 June 2021
UID
ibm10879941