SAP IPS/CMC: MS SQL Server Windows Authentication

Product: SAP IPS Information Platform Services (or SAP CMS)
Version: 4.1.x, 4.2.x, 4.3.x
OS: Windows Servers 64-bit

Overview

There is relatively limited information on Java web applications using MS SQL Server's Windows authentication in the internet.  Microsoft's JDBC documentation is relatively incomplete and leaves gaps in its installation, and leads to many wrong information around.

SAP IPS, or also called as SAP CMS, is a web application that is running on Apache Tomcat.  It uses SAP Java 8 for many years covering SAP IPS v4.1.x until now (v4.3.x) in 2025.

This post mainly to use SAP IPS as an example of web application server to discuss about the proper installation when needs to use MS SQL Server's Windows authentication

Background

Main MS SQL Server JDBC Driver Homepage: https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16

As of today, MS SQL Server JDBC version 12.8 is the current version.  It supports Java 8, 11, 17, 21, and 22.  Moreover, it bundled with 32-bit and 64-bit DLL to be used when the application supports MS SQL Server's Windows authentication.  This post mainly focus in this DLL, as improper installation will leads to the Java application, and web application failed to use Windows authentication.

Support of 32-bit Java adds another layer of complexity, where a lot of people is not aware of it, and do not know 64-bit Windows in term of 32-bit Java application support.

Both Microsoft JDBC driver page, as well as Java does not clarify the Windows DLL search order, which often leads to many Internet forums to contain mix messages.  You need to be a DLL developer in order to know Microsoft has long documented DLL search order, which now is located in following web page:

• Title: Dynamic-link library search order
• URL: https://learn.microsoft.com/en-ca/windows/win32/dlls/dynamic-link-library-search-order

This page's content is not "formatted" for Java.exe, so anyone who is poor in Windows OS, or never done any Windows programming will easily lost, and most probably think this page is not applicable.

Preparation

1. Identify your java.exe is 32-bit or 64-bit.  This could be a complicated process, if you install your own Java and forgotten about whether you download 32-bit or 64-bit Java.  For SAP IPS, it bundled with its own 64-bit Java, so this can be skipped

2. If you java.exe is 32-bit, seriously consider to switch to use 64-bit java.exe so that you can leverage more than 4GB RAM (virtual or physical).  Considering Windows Server 2016 only offers in 64-bit, and no 32-bit, so it is not sustainable to use 32-bit java.exe.  For SAP IPS, this can be skipped, as it is using 64-bit java

3. Determine whether the application uses JDBC 2.0 distributed transactions, which its JDBC class will be SQLServerXADataSource.  It is also called as extended architecture, or XA for short.  Most Java program will not uses this.  SAP IPS does not use this as well

4. Download MS SQL Server JDBC Driver in either zip or tar.gz format

3. Unzip it into a working directory instead of C:\Program Files\Microsoft JDBC DRIVER 12.8 for SQL Server indicated in the tar.gz/zip > install.txt.  SAP IPS only needs 1 DLL responsible for Windows authentication, but not the rest of the files

3.1. This JDBC zip file contains both 32-bit and 64-bit DLL to used for Windows authentication called below

3.1.1. enu\auth\x64\mssql-jdbc_auth-12.8.1.x64.dll - used for 64-bit Java

3.1.2. enu\auth\x86\mssql-jdbc_auth-12.8.1.x86.dll - used for 32-bit Java

3.1.3. This Windows DLL version does not need to match mssql-jdbc-<JDBC version>.jre<jre ver>.jar

3.2. The zip file contains DLL for Java XA extended architecture in both 32-bit and 64-bit.  SAP IPS does not use it, so do not need to install these DLL.  I have not see any Java applications that uses MS SQL Server JDBC XA, so not much to say, except following mapping.  I will recommend to rename the DLL filename to include x64 or x86, as default name can't easily tell the different:

3.2.1. enu\xa\x64\sqljdbc_xa.dll - used for 64-bit Java

3.2.2. enu\xa\x86\sqljdbc_xa.dll - used for 32-bit Java

3.3. Determine your Java version.  Picks the corresponding JAR file based on your Java version.  SAP IPS web application bundled with its own JAR file, so it does not use any of these  For other readers who uses Java 8 - 21, see following table mapping based on latest JDBC 12.8.1:

3.3.1. Java 8 - 10: uses mssql-jdbc-12.8.1.jre8.jar

3.3.2. Java 11 and higher: uses mssql-jdbc-12.8.1.jre11.jar

4. Determine how many java.exe is installed and used in the client machine.  SAP IPS often only has its own Java, and not using external Java that installed by administrator/developer/user

5. Does the Java application's JAR file bundled with its own JDBC JAR file.  If yes, then just need to focus in mssql-jdbc_auth-12.8.1.x64.dll (64-bit) and mssql-jdbc_auth-12.8.1.x86.dll (32-bit) to match the corresponding java.exe bitness

Planning and Installation

1. In the computer that you are setting up, determine how many different version of MS SQL Server daemon/instance your applications, as this related to the JDBC driver, and mssql-jdbc_auth-<version>.<bit>.dll.  For SAP IPS, and SAP Data Services, it is often to work with 1-2 version of MS SQL Server.  This indirectly implies 1 JDBC driver is required in SAP IPS machine, which often support few MS SQL Server version. SAP IPS does not install this DLL, and administrator needs to manually copy mssql-jdbc_auth-12.8.1.x64.dll (64-bit only).  Recommended directory is C:\Windows\system32\ (64-bit only)

2. If the Java applications installed in the same machine required to install multiple different Java JDBC - MS SQL Server JAR file, and different mssql-jdbc_auth.dll, then take note of this

2.1. This implies following files in the same MS SQL Server client machine:

2.1.1. mssql-jdbc-12.8.1.jre8.jar (or jre11) must not resides in jdk\lib\ext\ directory, unless there are multiple Java directory

2.1.2. mssql-jdbc_auth.dll (32-bit and 64-bit) must not resides in C:\Windows\system32\ or C:\Windows\SysWOW64\

3. If multiple Java.exe is used, then decide whether each will has its own JDBC JAR version, and mssql-jdbc_auth-<version>.x64.dll version.  I will ignore anyone will use 32-bit java.exe

3.1. If only 1 JDBC client version is required, then copy the JAR file into each of the jdk\lib\ext\, or jre\lib\ext\ directory.  This is not required for SAP IPS, or any web application which bundled the JDBC jar file in its WEB-INF\lib\ directory

3.2. If only 1 version of Window authentication DLL is required, then copy the DLL file into C:\Windows

3.2.1. 64-bit: Copy into C:\Windows\system32\mssql-jdbc_auth-12.8.1.x64.dll

3.2.2. 32-bit: Copy into C:\Windows\SysWOW64\mssql-jdbc_auth-12.8.1.x86.dll

4. If you want to keep a separate directory to keep mssql-jdbc_auth-<version>.<bitness>.dll, then edit Windows system environment variable, and set PATH to include this custom directory.  I will recommend this as well, but this is a global setup, and will affects all java to use the same DLL in this machine

Troubleshooting

Troubleshooting any applications that use MS SQL Server Windows authentication will require to troubleshoot using the specific Window username.  This is often a ActiveDirectory domain user account.  ActiveDirectory's user group policy can further configured to prevent the user from login interactively (using Windows login screen).

Due to user group policy could disable interactive login to the Windows machine, troubleshooting will require to ask ActiveDirectory admin to temporary enable/allow interactive login.  This page assumes the ActiveDirectory user is able to login to Windows, or uses RunAs command to run CMD to perform troubleshooting.

Starts the troubleshooting using following 2 methods to login as the Windows user who is authorized to login to MS SQL Server (Windows authentication):

1.. In Windows Login screen, login as the Windows user who used to start the Java.  In this example, SAP IPS/CMS

2. Login to Windows as your personal username, or other username that allows to login to the SAP IPS machine.  In Start menu, type CMD > right-click > Run As Other User > enter the Windows user who used to start Java.  You will get Command Prompt, with the username shown as the one you just entered

Once you reached this, the rest of the steps are relatively easy for Java application.  I will use the wording "authorized Windows user" to indicate the Windows username authorized to login to MS SQL Server in the rest of the procedure.

1. Download full MS SQL Server JDBC file as zip (Windows) from official homepage of https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16

2. As of today, JDBC version 12.8.1 is compatible with Java 8 - 21, and MS SQL Server version 2016 - 2022

3. If the Java program needs to connects to MS SQL Server 2014, then download JDBC version 12.6 or older (minimum 3.0)

4. Extract sample Java login program from the zip file

4.1. Directory name: sqljdbc_12.8 > enu > samples > connections > ConnectURL.java

5. Modify ConnectURL.java with the MS SQL Server login, e.g. hostname, port, DB name

5.1. Modify line 30: String connectionUrl = "jdbc:sqlserver://<server>:<port>;databaseName=AdventureWorks;user=<user>;password=<password>";

5.1.1. Fill in <server> as the MS SQL Server hostname

5.1.2. Fill in <port> as the MS SQL Server port number

5.1.3. Replace AdventureWorks as the MS SQL Server logical database nane.  If there is no DB, then master DB will always be there

5.1.4. Removes ";user=<user>;password=<password>"

5.1.5. Append ";integratedSecurity=true" after the databaseName parameter, e.g. "jabc:sqlserver://......databaseName=master;integratedSecurity=true;trustServerCertificate=true"

5.1.6. The parameter "trustServerCertificate=true" is optional, but many DBA has configured to force SSL, as well as used self-sign SSL certificate.  More description can be found in Learn.Microsoft.com: https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-ver16

6. Compile the Java program. Let's keep the filename as ConnectURL.java.  For SAP IPS, please uses Java 8. e.g. AdoptOpenJDK, Oracle Java, Amazon Java 8

6.1.<JAVA JRE 8 PATH>\bin\javac ConnectURL.java.  You can use either 64-bit or 32-bit as this is a small program that won't use up 4GB RAM

6.2. You will see new file ConnectURL.class in the current directory

7. Run the ConnectURL.class program with debug mode.  This step will provides more helpful information in troubleshooting the login issue

7.1. Copy the MS SQL Server JDBC JAR file into the same directory as ConnectURL.class

7.2. Copy mssql-jdbc_auth-12.6.2.x64.dll into the same directory as ConnectURL.class.  SAP BODS 4.2SP3PL2 bundled with JDBC MS SQL Server v12.6.2, so I will use this version.  You will download and test based on the version you use

7.3. Runs Java.exe v8 64-bit to run the program.  Uses following syntax:

7.3.1. Command: java -Djavax.net.debug=ssl  --classpath mssql-jdbc-12.6.2.jre8.jar:. ConnectURL

7.4. If you failed, you can test with different scenarios like below:

7.4.1. java.exe 32-bit or 64-bit

7.4.2. java.exe on different version, e.g. 8, 9, 10, 11, all the way to 21

7.4.3. different JDBC JAR file for mssql-jdbc-<JDBC version>.jre8.jar

7.4.4. different javax.net.debug, such as only shows SSL handshake - "java -Djavax.net.debug=ssl:handshake  --classpath mssql-jdbc-12.6.2.jre8.jar:. ConnectURL"

7.4.5. has multiple copy of ConnectURL.class which compiled with different Java.exe version

7.5. Tries different combination so that you fully understand the issue is in

7.5.1. java.exe - 64-bit, distribution of the java.exe, e.g. SAP Java, IBM Java, Oracle Java, AdoptOpenJDK Java, Amazon AWS Java, RedHat Java

7.5.2. mssql-jdbc-<version>.jre8.jar - could be different JDBC version, jre8.jar or jre11.jar

7.5.3. mssql-jdbc_auth-<version>.x64.dll - Microsoft never say whether the DLL version must match JDBC JAR, so you can try different version that bundled with 3rd party software, e.g. SAP BODS 4.3SP3PL3 bundled with mssql-jdbc_auth-12.6.0.x64.dll with mssql-jdbc-12.6.2.jre8.jar (different version)

7.5.4. OS, machine - easy to copy this to any machine, laptop, server, gateway so that you can repeat this test easily

7.5.5. Copy mssql-jdbc_auth-<version>.x64.dll into a dedicated directory, e.g. D:\ReportServer\JDBC\MSSQLServer12.6.2\, and modify PATH to include this directory (must change PATH to points to the directory where this DLL located)

7.5.6. For above PATH, you can replace it by specifying parameter "-Djava.library.path=D:\ReportServer\JDBC\MSSQLServer12.6.2"

8. mssql-jdbc_auth-<version>.x64.dll - Some recommendations to perform more troubleshooting in this section.  Removes mssql-jdbc_auth-<version>.x64.dll from current directory, and place it in different directories based on the application that you work on

8.1. Example #1, SAP IPS/BODS placed it in C:\Windows\system32\ (for 64-bit DLL) and C:\Windows\SysWOW64\ (for 32-bit DLL)

8.2. Example #2, <java JRE>\lib\ext\ directory, which is the recommended directory to store external JAR.  Some developers or 3rd party vendors recommend to put into the application's JAR file, while I recommend to put into dedicated Java directory in its lib\ext for ease of security patching, and upgrade by isolating the Java application from popupar vendor, like MS SQL Server

8.3. Example #3, put it in your own Java JAR's WEB-INF\lib\ directory

8.4. Do not place 32-bit of DLL, e.g. mssql-jdbc_auth-12.8.1.x86.dll into C:\Windows\SysWOW64\.  Window's directory architecture design for this directory is for 32-bit application in 64-bit Windows (in other word, in 32-bit Windows 10, SysWOW64 directory doesn't exists, and you need to put the 32-bit DLL into C:\Windows\system32\ directory).  Java 64-bit application will work with this DLL in SysWOW64, as it is ignoring DLL in 32-bit SysWOW64 directory, even it exits

8.5. Do not place 64-bit of DLL, .g. mssql-jdbc_auth-12.8.1.x64.dll into C:\Windows\system32\ for Windows 64-bit.  This will leads to Java 64-bit failed to work in its Windows authentication, and you will required an OS 

9. By now, you will know the relationship of mssql-jdbc_auth-<version>.x64.dll, 32-bit Java, 64-bit Java, JDBC JAR version, SSL handshake, C:\Windows\system32\ for DLL (if using 64-bit Java.exe), and C:\Windows\SysWOW64\mssql-jdbc_auth-<version>.x86.dll (if using 32-bit Java.exe)

SAP IPS v4.3 - Upgrade from IPV v4.2

Product: SAP Data Services (BODS), Information Platform (IPS)

Version: 4.3

Related Product: SAP Data Services v4.3.x

Overview

This is another cycle of IPS v4.2 -> 4.3 upgrade again, and the same issues happened last time 10+ year ago for IPS v4.1 -> 4.2 is happening again this time.  This is due to no SAP IPS v4.3 upgrade document, and it is based on SAP KB that only those who have paid SAP Support account can see it.

This post is to highlight all the known issues which SAP IPS team think this is the "normal" process that customer should aware of.  My objective is to address the frustration in both IPS v4.3 and Data Services v4.3 upgrade.

Issues Highlight

1. SAP IPS v4.3 fresh installation does not required to enter license key by using InstallIPS.exe (Windows) or InstallIPS (Linux(, but if you perform in-place upgrade, then you need to enter IPS v4.3 license key which SAP document doesn't say (there is no IPS upgrade doc)

2. When doing IPS v4.2 -> v4.3 in-place upgrade, both InstallIPS.exe and setup.exe will not pass the IPS v4.3 license key into the IPS v4.2.  So you must enter SAP IPS v4.3 license key into it after completed IPS v4.3 upgrade

3. SAP KB title will use the word "BIP 4.3" instead of "IPS 4.3" so it is not easy to find SAP KB to tell you that you need to use their license request page to generate IPS v4.3 license key (SAP's desire process)

4. SAP admin who has SAP Support access is not able to see the IPS product in the license key request page in SAP Support homepage (which in another SAP KB for IPS v4.1 to tell you to raise SAP ticket)

5. To leverage the bundled IPS v4.3 license key, you need to fully uninstall IPS v4.2

6. For those who plan to uninstall IPS

6.1 if you have multiple IPS v4.2 patches installed in the machine (Windows only), the Windows uninstall method might break the IPS v4.2 before you fully uninstall all IPS product patches.  So the Windows machine will not be able to completely uninstall IPS v4.2

6.2. if you didn't select "Reset database" during IPS installation, and reuse your IPS v4.2 DB, then InstallIPS.exe installer still won't register the IPS v4.3 license kehy

7. SAP Support actually recommends fresh IPS and BODS installation instead of in-place upgrade. Any in-place upgrade issues will get their recommendation to perform fresh IPS & BODS installation

8. IPS v4.3 license key should be entered in CMC > License Key screen immediately after IPS v4.3 installation.  Some people proceeds with SAP BODS v4.3 installation without entering IPS v4.3 key, and causes IPS 4.3 and BODS 4.3 to be broken

9. After BODS v4.3 is broken, entering IPS v4.3 license key will not automatically fix it.  It only allows IPS daemons to be able to start

10. SAP BODS v4.2 and v4.3's ATL file is not compatible, but SAP DS Designer v4.2 will allow to import v4.3 ATL file.  For Designer v4.3, it will display a warning of incompatibility, but allows user to proceed to import atl

11.. SAP Data Services v4.2 will get a false login failed message when trying to login to BODS v4.3, which often confuse admin and developer that the credential is wrong, or there is a network issue

12. If developers are doing development in BODS v4.2 and v4.3, then it is a length process to clone BODS v4.2 repo to another DB, runs repoman (Linux) to upgrade it, and finally register into CMC v4.3.  This has to repeat many times until all developers moved to v4.3

Manually Getting IPS v4.3 License Key

1. InstallIPS.exe or InstallIPS contains the IPS v4.3 license key even you can't open the executable file

2. Uses Linux to remove all binary data: strings InstallIPS.exe > /tmp/installips_key.txt

3. Uses Linux to display the IPS v4.3 license key: grep "ProductKey" /tmp/installips_key.txt

Reminder:

1. If you IPS v4.2 is currently not healthy, upgrading to v4.3 won't automatic fix a broken IPS

2. If you installed SAP BODS v4.3 over IPS v4.3 without license key, entering IPS v4.3 license key later might not make BODS fully functional.  This is version specific, so I can't tell for all the IPS and BODS patches

Recommendation for Upgrade

Performs fresh IPS and BODS v4.3 installation instead of in-place upgrade.  Takes the opportunity to upgrade your Windows OS, or RHEL OS, MS SQL Server, Oracle, DB drivers.

Recommended procedure:

1. Backup your both IPS v4.2 and BODS v4.2 repositories

2. Creates a new empty BODS repositories to be used for v4.3

3. You can reuse IPS v4.2 repositories, but BODS v4.2 repositories can be re-use (manually register into SAP CMC > Data Services)

4. Perform fresh IPS v4.3 installation using either old or new IPS DB

5. If you are re-using IPS v4.2 DB, then enable checklist "Reset database" in the installer wizard

6. After installation (~ 1 hr), login to CMC, and ensures License Key screen show the license is valid

7. Proceeds with SAP BODS v4.3 installation, but uses the newly created BODS repository DB

8. After SAP BODS v4.3 installation completed, checks in CMC > Data Services to ensure you are seeing 1 repository

9. Run "Repository Manager" (Windows) or repoman (Linux) and choose "Upgrade" option to upgrade each of the BODS v4.2 repositories.  You need to do this multiple round, as you can only specify 1 DB to upgrade its table content v4.2 -> v4.3

10. Login to CMC v4.3 to register each of the BODS v4.2 (upgraded to v4.3 now).  You should not get version conflict

11. Run Server Manager (Windows) or svrcfg (Linux), and register each of the BODS repositories you added to CMC just now

12. Login to SAP DSMC, and generate new password files

13. In SAP DSMC, create new schedules

14. In SAP DSMC (Linux only), create Excel adapter, if you leverage xls file operations in Dataflow

15. In SAP CMC, configures user/group permission, such as allow user to retrieve BODS repositories credential (DS Designer won't show add'l prompt to ask for BODS repository password)

 16. For Windows, if you want to start the Windows service with specific account (service account), then configures Windows Services startup properties

17. If you enable SSL, then repeat the SSL configuration for DB, IPS, Tomcat, BODS, DS Designer

Windows: Installer/Uninstaller error code 2203 when trying to install iTunes

Product: Windows
Version: 10

Overview

When trying to uninstall many software in Windows 10, the uninstallation failed with following error

"The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2203."

Procedure

1. Get more detail error message from Event Viewer

1.1. Press Windows key, and type "Event Viewer" to run "Event Viewer"

1.2. On left panel, navigates to Windows Logs > Application

1.3. Checks right panel, and focus in Source = MSInstaller

1.4. In lower right panel, check under General tab for msiexecdetail message.  In this example, iTunes failed during uninstallation with error code 2203

1.5. Following is displayed in General tab:

Product: iTunes -- The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2203. The arguments are: C:\windows\Installer\inprogressinstallinfo.ipi, -2147286788, 

1.6. Clicks on Details tab to see more detail, which will contain the product ID that we need for iTune software

1.7. Scrolls down to section "In Bytes" and take note of the hex code, which is iTune product ID.  In this screenshot, the Product ID is {E42D1B4D-78C2-4144-8E4D-FA82C53B68A4}

2. Explanation of above error:

2.1. The MS Windows installer/uninstall called program msiexec.exe at the background with product ID {E42D1B4D-78C2-4144-8E4D-FA82C53B68A4}

2.2. The actual command executed was "msiexec.exe  /uninstall {E42D1B4D-78C2-4144-8E4D-FA82C53B68A4}"

2.3. msiexec.exe will write temporary files to %LOCALAPPDATA%\temp\ of current login user, e.g. administrator

2.3.1. If current login user cannot create any files in directory %LOCALAPPDATA%\temp, then the installer will failed with this error.  So, test create a dummy file or directory in %LOCALAPPDATA%\temp\ using "cmd" prompt

2.4. msiexec.exe will write temporary files to %TEMP% as system user.  The value will be from system environment variable, under section "System variables"

2.4.1. Even you have TEMP variable defined under "User variables," system user will only use TEMP value under "System variables"

2.4.2. You can monitor above TEMP directory while running uninstaller/installer to see whether it is creating any temporary file

2.4.3. You cannot run "cmd" as administrator, and type "set" command to display the value of TEMP, because that will still cause TEMP value inherits from current login user

2.4.4. Ensures that the permission for "SYSTEM" in folder properties has Full Control

2.5. msiexec.exe will creates a temporary file in C:\windows\Installer\inprogressinstallinfo.ipi

2.5.1. Monitor directory "C:\windows\Installer\" in real time during uninstallation.and ensures file inprogressinstallinfo.ipi get created

2.5.2. The file will get deleted when the uninstaller failed

2.5.3. You can try to create a text file in above directory to test you can create file using cmd (run as administrator) to ensure there is no permission issue.  If you do, ensures the SYSTEM user has Full Control permission

2.6. You could find in Internet to suggest to disable Windows Virus Defender temporary.  This is assume the utility created temporary files which Windows Defender automatically deleted the temporary files by assuming it is a virus.  If you are working on common software, such as iTune, Acrobat, Notepad++, then you can ignore those suggestion, as it is not related

3. Manually construct the uninstallation command yourself, and enable debug log

3.1. Run notepad.exe to prepare a command to run later which will create a debug log to show us more info

3.2. Type: msiexec.exe  /uninstall {product ID} /l*v <log file location>

3.3. For my iTune, the product ID that we found in Event Viewer is E42D1B4D-78C2-4144-8E4D-FA82C53B68A4}

3.4. I would like to create the new log file in i:\temp\itune_uninstall.txt

3.5. So the final command is: C:\windows\system32\msiexec.exe  /uninstall {E42D1B4D-78C2-4144-8E4D-FA82C53B68A4} /l*v i:\temp\itune_uninstall.txt

3.6. Run cmd.exe as administrator

3.7. Copy-paste the final msiexec.exe command in cmd to start uninstaller for iTune, and create debug log in log file i:\temp\itune_uninstall.txt

3.8. Let the uninstallation failed with error 2203

4. Gets the detail error message for error 2203

4.1. Open debug log created in above step

4.2. In this example, it is i:\temp\itune_uninstall.txt

4.3. Open the log file in Notepad, or Notepad++

4.4. Search for "DEBUG: Error"

4.5. In this iTune example, the error is below

MSI (s) (68:78) [10:15:46:439]: BeginTransaction: Locking Server

MSI (s) (68:78) [10:15:46:440]: Note: 1: 2203 2: C:\windows\Installer\inprogressinstallinfo.ipi 3: -2147287038 

MSI (s) (68:78) [10:15:46:440]: Note: 1: 2203 2: C:\windows\Installer\inprogressinstallinfo.ipi 3: -2147287038 

MSI (s) (68:78) [10:15:46:440]: Server not locked: locking for product {E42D1B4D-78C2-4144-8E4D-FA82C53B68A4}

MSI (s) (68:78) [10:15:46:441]: Note: 1: 2203 2: C:\windows\Installer\inprogressinstallinfo.ipi 3: -2147286788 

Action start 10:15:46: InstallInitialize.

DEBUG: Error 2203:  Database: C:\windows\Installer\inprogressinstallinfo.ipi. Cannot open database file. System error -2147286788

MSI (s) (68:78) [10:15:48:055]: Product: iTunes -- The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2203. The arguments are: C:\windows\Installer\inprogressinstallinfo.ipi, -2147286788, 

MSI (c) (84:8C) [10:15:46:447]: Font created.  Charset: Req=0, Ret=0, Font: Req=MS Shell Dlg, Ret=MS Shell Dlg

The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2203. The arguments are: C:\windows\Installer\inprogressinstallinfo.ipi, -2147286788, 

Action ended 10:15:48: InstallInitialize. Return value 3.

Action ended 10:15:48: INSTALL. Return value 3.

4.6. This error message is longer that the one you see in Event Viewer, or Control Panel > Uninstall software

4.7. The line to focus on will be below:

DEBUG: Error 2203:  Database: C:\windows\Installer\inprogressinstallinfo.ipi. Cannot open database file. System error -2147286788

4.8. If you review other log entries, you will see no entry is referring to TEMP dir, which in my case, it is "I:\TEMP\Windows"

5. This post does not cover Windows 10 system files minor corruption

6. If your Windows 10 has minor corruption, then fix them using following steps:

6.1. Run cmd as administrator

6.2. Run: DISM.exe /Online /Cleanup-image /Scanhealth

6.3. Run: DISM.exe /Online /Cleanup-image /Restorehealth

7. A quick workaround will be to change System environment variables below:

7.1. TEMP=C:\temp

8. If you are stuck, repeat the installer/uninstaller while monitor following dir to ensure it has the permission.  Don't simply follows many suggestions in Internet to modify directories permission, if you see it is able to create temporary files

8.1. %LOCALAPPDATA%\temp - this is the value of users environment variable

8.2. %temp% - this is the value of the global environment variable, not users environment variable

Oracle RDBMS: Manual Archive Log Clean Up with FAST_RECOVERY_AREA

Product: Oracle RDBMS

Version: 11.1.0 - 21c (19.3.x)

OS: Windows, Linux, HP-UX, AIX

The introduction of FAST_RECOVERY_AREA has been more than 10 years, which automatically housekeep archive logs.  This allow DBA to indirectly delete physical archive log files resides in the OS, even they do not have physical OS access.

However, for those DBA or SA who have access to the OS, or managed the storage and DB, it is often required to cleanup archive log files in OS first, then in Oracle's FAST_RECOVERY_AREA later.

This post show how to perform the manual clean up of it

1. Verify the usage of FAST_RECOVERY_AREA to get a breakdown what used up all the storage

col file_type format a40
set pagesize 50
set line 110

select * from v$recovery_area_usage;

2. For the purpose of this post, this is covering scenario where FILE_TYPE=ARCHIVED LOG.  So the SQL will be

select * from v$recovery_area_usage where FILE_TYPE='ARCHIVED LOG';

3. If archive log have not backup, and you want to backup the archive log to disk, then use following RMAN command to backup to disk, and delete from the disk

RMAN target /

backup archivelog all delete input;

4. If archive log already been deleted, then you need to sync up the status with Oracle DB.  Uses this RMAN command to sync up the status of all the archive log files

RMAN target /

crosscheck archivelog all;

5. After FAST_RECOVERY_AREA detected the archive log files are deleted, then you can run RMAN command to delete it from FAST_RECOVERY_AREA's internal table,  This does not delete archive log which present (no deleted) in FAST_RECOVERY_AREA

RMAN target /

delete noprompt expired archivelog all;

Fresh Tomato Custom Router Firmware: Blocks Incoming Hacker IP

Product: Fresh Tomato Custom Router Firmware

Version: Firmware 2024.2 K26MIPSR2_RTAC USB AIO-64K

Router Model: ASUS RT-AC66U

Overview

Testing out custom firmware in ASUS router above using build-in NGINX web server on custom http port.  Trying to figure out how this custom firmware can blocks incoming hacker by their IP. 

Web server log is able to log visitors' IP, and the URL request URI.  So I can figure out the attackers' IP, which I considered as hacker.

This gives me an idea how aggressive hacker community and search engine will send valid and invalid attacks to a random web site.

I am leveraging build-in software firewall which configures using command iptables, configuration file /etc/iptables, web GUI called "Access Restriction" that stores in NVRAM (access using command "nvram get rrule0" and "nvram set rrule0").  Research of iptables chains and rules found that the menu "Access Restriction" is only restrict WAN to LAN, and doesn't restrict build-in NGINX web server, which is not consider in the LAN subnet.  So this post document how to configure it to leverage "Access Restriction."

Initially, I uses following methods to configures the software firewall to block hackers' IP, but it is not persistent:

1. /etc/iptables configuration file, with scheduler

2. Using iptables command, with scheduler

Every few hours, Fresh Tomato seems to replace the above 2 files and configuration with the value in web GUI, which later found the setting is stored in NVRAM.  So I lost all my firewall setting, and hacker can attack my NGINX web server again.  It took me few days to figure out the web GUI menu called "Access Restriction" will configures iptables, and its setting is permanently stores in NVRAM in addition of above 2 files/configuration, even after reboot or power cycle.

Google search is not returning any info about blocking hacking using Fresh Tomato on build-in NGINX web server, but finally I found following Fresh Tomato documentation that gives me a clue how to configure NVRAM (so that I can blocked hacker by using NGINX access.log file):

• https://wiki.freshtomato.org/doku.php/access_restrictions

Web GUI's "Access Restriction" configuration resides in NVRAM, and needs to set using "nvram set [internal rule name]" command.  Its current configuration can be retrieve using command "nvram get [internal rule name]" command.  The internal rule name starts with rrule0, follows by rrule1, rrule2, and each can be enabled and disabled separately.  Moreover, you can set the time start/end for every day, and the router will automatically trigger iptables command by the schedule.

In this post, I will assume there is no rule set, so I will use rrule0.  This indirectly will create iptables chain name "rdev00."

Every time the "Access Restriction" configuration is changed, it will reload the configurations from NVRAM, as well as certain network related configurations in the router.  I need to adds custom iptables command into "Scripts" section called "Firewall" as well to modify OOTB iptables chain to apply "Access Restriction" configuration in addition of LAN traffic.

Procedure

1. Creates rule #1 with:

1.1. Rule name: Blocks Hacker 2024-07

1.2. Blocks IP: 103.246.195.85, 5.183.103.248, 118.123.105.93

2. Connects to the LAN port using cable, or WiFi

3. Assumes WiFi router's IP is 192.168.100.1, then uses ssh utility such as PuTTY to ssh to port 192.168.100.1

4. Login as root user

5. At the "#" shell prompt, creates a new rule 1 with internal rule name rrule0

5.1. nvram set rrule0="1|-1|-1|127|104.37.174.65>117.146.152.81>118.123.105.93|||0|Blocks Hacker 2024-07"

5.2. Expect output:

root@rt-ac66u:~# nvram set rrule0="1|-1|-1|127|104.37.174.65>117.146.152.81>118.123.105.93|||0|Blocks Hacker 2024-07"

root@rt-ac66u:~#

6. Login to WiFi routner's web GUI to check. URL: http://192.168.100.1

7. On left menu, lick on menu item "Access Restriction" to display all the rules

8. You will see the first rule call "Blocks Hacker 2024-07" on the right panel, and as the 1st rule in the list

9. Following is the expected output

10. The current "Access Restriction" are:

10.1. Only in configuration of NVRAM, but not saved yet

10.2. Current setting will lost after reboot, or power lost

10.3. Router has not run iptables to activate the setting.  There is delay of about 1 hr

11. To make this setting permanent after reboot, enter: # nvram commit

12. Waits for about 1 hr, and checks firewall to see the IP are blacklisted.  Command: iptables -L rdev00 -n

13. Expected output:

Chain rdev00 (1 references)

target     prot opt source               destination

DROP       all  --  104.37.174.65        0.0.0.0/0

DROP       all  --  117.146.152.81       0.0.0.0/0

DROP       all  --  118.123.105.93       0.0.0.0/0

14. If you want to force the setting to activate immediately, runs these 2 commands:

# nvram set action_service=restrict-restart
# kill -USR1 1

15. You will see following in /var/log/messages:

# tail /var/log/messages | grep "Activating rule"

Jul 14 11:23:31 rt-ac66u user.info rcheck[1867]: Activating rule 0

16. Lastly, modify iptables firewall's INPUT chain to make web server port from the web GUI

16.1. In router's web GUI, on left panel, navigates to Administration > Scripts

16.2. Click on tab Firewall

16.3. Enter following command to create a new custom chained called "hacker" and replaces exiting rule for NGINX TCP port 85 to this custom chain:

logger "webUI-Scripts-Firewall Configures NGINX to follow Access Restriction"

iptables -N hacker

iptables -A hacker -j restrict

iptables -A hacker -j logaccept || iptables -A hacker -j ACCEPT

iptables -R INPUT 15 -p tcp -m tcp --dport 85 -j hacker

16.4. Sample screenshot

Maintenance

You might to perform other maintenance of the firewall.

1. Removes/delete the rule, e.g. removes rule 10: nvram unset rrule10

2. Delete firewall rule 10 (rdev09):

2.1. iptables -D restrict -j rdev09

2.2 .iptables --flush rdev09

2.3. iptables -X rdev09

3. Checks current actively blocked IP for rule 1 (chain name rdev00) in memory: iptables -L rdev00 -n

4. Checks current block IP for rule 1 in /etc/iptables configuration file: grep rdev00 /etc/iptables

5. Forces Access Restriction to activate now:

5.1. Command #1: nvram set action_service=restrict-restart

5.2. Command #2: kill -USR1 1 (after above completed)

5.3. Command #3: nvram get action_service (if completed, command will not return any value. If not completed, then returns "restricgt-restart")

6. Uses "top" Linux command to monitor the CPU usage with the new firewall rule. You will need to reduces number of blocked IP, if encountered CPU contention, or buy a faster/newer WiFi router

Known Limitation

1. Web GUI's menu Administration > Logging > Syslog > Connection Logging to control following won't always going to capture above traffic:
1. Inbound: If blocked by Firewall
2. Outbound: If blocked by Firewall
3. Reason: above logic/commands need to modify to use iptables chain "logaccept" and "logdrop" which will make it longer to write (updated to allows logging to syslog /var/log/messages)
2. iptables firewall configuration is based on my configuration of Fresh Tomato on ASUS RT-AC66U, version 2024.2.  It might needs further modification
3. When number of blocked IP is getting long (more than 100), the web GUI's menu "Access Restriction" won't be able to delete or modify rule #1, and will consistently prompt to delete IPs (then it will work)
1. Fresh Tomato firmware seems to enforce a restriction to limit the number of entries inside the rule
2. This error can be ignore, and doesn't affect the IP blocking

Linux: How to get Parent Process (PPID)?

Product: Linux
Version: All
CPU: All

Overview

Many Linux forum and documentation often base on full Linux distribution, such as RedHat, Fedora, Ubuntu, SuSE, MacOS, and left out Linux installed in single box computer (SBC) such as WiFi router, smartphone, tablet, Raspberry Pi, Nano Pi, car's head unit, webcam, etc.  This leads to some standard UNIX design and operation slowly forgotten and replaces with newer commands.

During day-to-day troubleshooting, sometimes in addition of process ID (PID), it is required to check parent process ID (PPID) as well.  For example, what spawn the process, does the parent process terminated, etc. 

Procedure

Most Linux installed in SBC has a reduced version of Linux, such as BusyBox which is ~ 600KB used in DD-WRT and FreshTomoto firmware used in WiFi router.

These smaller footprint of Linux often bundled with "ps" command which can display limited info of a process PID.  For example, it does not display parent PID (PPID).

You can use following command to get the PPID using /proc virtual directory.

For example, assumes the PID is 2914:

root@rt-ac66u:/tmp/home/root# ls -l /proc/2914/status

-r--r--r--    1 root     root             0 Jun 28 09:20 /proc/2914/status

root@rt-ac66u:/tmp/home/root# cat /proc/2914/status

Name:   dropbear

State:  S (sleeping)

SleepAVG:       95%

Tgid:   2914

Pid:    2914

PPid:   1

TracerPid:      0

Uid:    0       0       0       0

Gid:    0       0       0       0

FDSize: 32

Groups:

VmPeak:     1156 kB

VmSize:     1156 kB

VmLck:         0 kB

VmHWM:       256 kB

VmRSS:       256 kB

VmData:      348 kB

VmStk:        84 kB

VmExe:       304 kB

VmLib:       384 kB

VmPTE:        12 kB

Threads:        1

SigQ:   0/2047

SigPnd: 00000000000000000000000000000000

ShdPnd: 00000000000000000000000000000000

SigBlk: 00000000000000000000000000000000

SigIgn: 00000000000000000000000000001000

SigCgt: 00000000000000000000000000024402

CapInh: 0000000000000000

CapPrm: 00000000fffffeff

CapEff: 00000000fffffeff

To display the PPID alone, then uses following command:

root@rt-ac66u:/tmp/home/root# grep PPid /proc/2914/status

PPid:   1

Virtual directory /proc exists as very basic design of UNIX, so it always available in all UNIX, while "ps" command might has stripped off functionality.

Above example is captured from FreshTomato firmware in WiFi router

Java SQL Programming - Tuning SELECT Statement Performance, JDBC Performance Tuning

Product: Java Programming
Version: 0.1 - current
OS: Any operating system, Windows, Linux, UNIX, AIX, Solaris, HP-UX
Related Product: Any programming language that uses SELECT in the world
Java API, Java class, Java method: java.sql.Statement, java.sql.PreparedStatement
Java method: java.sql.PreparedStatement.setFetchSize, java.sql.Statement.setFetchSize

Overview

Any programming that can work with databases will offer an API to write SELECT statement.  In term of Java, they are following Java classes:

• java.sql.Statement
• java.sql.PreparedStatement

Certainly there are more way to run a SELECT statement without using above, but this post will focus in these 2, as they are indirectly called by any SELECT statement execution by any programming languages in the world.  This has been since the 1980 when a database programming was introduces, and many programmer today has no clue how this begin, especially why this is required.

This post is going to explain how to use Java programming language to tune a SELECT statement for a very specific use case:

• SELECT statement is returning a lots of data, such as 1000 rows and above

The tuning will be to tune either of following Java JDBC class:

• java.sql.Statement
• java.sql.PreparedStatement
• java.sql.ResultSet

Architecture

In order to explains the tuning of these 2 JDBC class, first I need to explain how SELECT statement work in low level.  I will use programmer's perspective to explain, and skip many low level database execution, and pick Oracle DB as an example.  This is mainly I worked more in Oracle, and Oracle KB and blogs are sharing more of its internal architecture than MS SQL Server, for example.

A SELECT statement execution goes through following process:

1. Crafts a SELECT statement
1. In Java programming, this often be stored as a String variable, or many SELECT statements in many String variables
2. Creates a String variable to store above SELECT statement
3. Create a new variable of JDBC class java.sql.PreparedStatement by using above String (or SELECT statement) to that it becomes an object that is parsed (Oracle's keyword), and in programmer's terminology, it means compiled by DB
1. PreparedStatement or DB SQL parsing is always required to run any SQL, including SELECT, INSERT, DELETE, CREATE, DROP, TRUNCATE, function call, store procedure call.  Let's call these DDL (data definition) and DML (data manipulation)
2. PreparedStatement will indirectly getting call by any JDBC if the program doesn't defined/specify/create this object when executing any SQL
3. There is no way to bypass SQL parsing (Java PrepareStatement) when executing any DDL and DML
4. The object in Java takes up very little memory (Java called heap memory)
1. In Java documentation, you often read this will allocate some memory, but the document was written when max desktop RAM was 512MB
5. The object in Oracle takes up very little memory
1. In Oracle documentation, you often read this will allocate some memory, but the document was written when max desktop RAM was 2GB
6. The variable will auto set its row fetch size of 10 rows, i.e. PreparedStatement.setFetchSize(10).  It will download 10 rows on every ResultSet..next() method. You can call PreparedStatement.getFetchSize() to display its row fetch size
7. This PreparedStatement variable is ready to run by calling  PreparedStatement.executeQuery()
8. The FetchSize doesn't take up memory in object PreparedStatement
4. Creates a commonly used variable for JDBC class of java.sql.Statement that either
1. Uses above PreparedStatement variable
2. Runs above String variable contains a SELECT statement
3. Runs a a SELECT statement, typically used to get current timestamp
4. The variable will auto set its row fetch size of 10 rows, i.e. Statement.setFetchSize(10).  It will download 10 rows on every ResultSet..next() method. You can call Statement.getFetchSize() to display its row fetch size
5. Oracle DB will indirectly performs SQL parsing inside the DB, which takes up a little memory in Oracle DB server
6. The FetchSize doesn't take up memory in object Statement
5. Run the SQL by calling either following JDBC method:
1. PreparedStatement.executeQuery()
2. Statement.executeQuery()
6. Oracle DB will takes time to execution the SQL statement.  It will take longer time if Oracle DB has poor SQL execution plan, or high data volumes that DB takes time to load the data from hard disk, or joins them using CPU.  Java program doesn't do any processing, and no data is available for the Java program to download.  In other word, Java program will stop and wait until DB finished execute the SQL, and ready to return the data. executeQuery() will wait as long as the Oracle DB takes to run the SELECT statement.  If the SELECT takes 50 hr to execute, then executeQuery() will wait for 50 hr
7. After Oracle completed executing the SELECT statement, fetch/download the rows into Java using JDBC class ResultSet
1. Creates a new variable of JDBC class ResultSet
2. Download 10 rows (record) of records using ResultSet.next() method
3. If you set FetchSize to be higher using setFetchSize(), then ResultSet.next() will download more rows from the DB server
4. ResultSet.next() method will pause longer the higher the FetchSize set to.  Imagine you are downloading files from Internet with bigger size.  The bigger the file, the longer it takes to download
5. The more columns the SELECT statement indicates, the bigger the data volume and longer it takes to run next() method
6. So total time to fetch 10 rows (or FetchSize if set to diff value) depends on (excludes time waiting for DB to run SELECT statement):
1. Number of columns defined in SELECT statement
2. Number of columns with data. If no data, then no impact
3. Data size for each column
4. Number of rows to fetch that defined by setFetchSize. Default is 10 rows
7. FetchSize can be modify in real time JDBC method ResultSet.setFetchSize(new_value) anytime

SELECT Statement Tuning

After lengthy explanation of the SELECT statement architecture, let's get into SELECT statement tuning.

We will not discuss about tuning of following as some are business requirement, and some are SQL tuning beyond Java programming tuning:

1. slow running SELECT statement that DB takes time to process
2. poorly written SELECT statement
3. SELECT statement is joining plus expecting to retrieve all data from very large tables, such as 10GB - 300TB

The performance tuning in Java program is for following Java JDBC class in their FetchSize:

• java.sql.Statement
• java.sql.PreparedStatement
• java.sql.ResultSet

To further elaborate, performance tuning in Java program is increase number of rows to download from Oracle DB each time by calling following Java JDBC method:

• java.sql.Statement.setFetchSize()
• java.sql.PreparedStatement.setFetchSize()
• java.sql.ResultSet.setFetchSize()

Why Tune Default Row Fetch Size 

1. Default of 10 rows (FetchSize) is too small by today's standard.  A desktop computer often comes with 1Gbps network card, while servers are using 10Gbps network card.  Back to 30 years ago, network card was running at 10Mbps.  Many residential internet are between 50Mbps and 1Gbps.
2. Data size are significantly bigger, such as storing image, video, audio.
3. Max column size has increased. Oracle LONG column type is changed to CLOB (character) and BLOG (binary) which can hold 8TB - 128TB for 1 column.
4. Business users have valid usage to display 1000 rows per screen, such as viewing reports, YouTube video, X tweets, so 10 rows is too little, and anonying
5. Too frequent pause when calling next() after it finished process/display 10 rows due to network latency
6. It is safe to set a proper FetchSize, such as 1000 rows when the SELECT returns 1 row.  It doesn't take longer to download 1 row, when FetchSize=1000

Tuning Consideration

Fully study following when considering tuning FetchSize:

1. Does users/application really needs to retrieve so many columns in the SELECT statement?
2. How big (in bytes) the column size in the SELECT statement?
3. How big (in bytes) the total size of all columns in the SELECT statement?
4. How many records it will return by the SELECT statement?
5. Can user adjust the FetchSize from the application?

Scenario 1: Showing number, strings, and time

90% of regular applications design often returns these column types in their SELECT statement, and their max size in Oracle as non-Unicode (byte):

• number - 40 bytes
• varchar2 - 32 KB
• datetime - 16 bytes

Let's assume SELECT statement is returning so many number of column types, with total of 10 columns:

• number - 3
• varchar2 - 5
• datetime - 2

So total max bytes that the next() will download from the DB server is

Max row size in byte = 3 * 40 + 5 * 32KB + 2 * 16

= 120 bytes + 160 KB + 32 bytes

= 163992 bytes

= 160.1484375 KB

Assumes that business users is able to accept 1 sec pause to display the data (download 10 columns from the Oracle DB server), then following is the size of data download size for different network speed of 1 sec:

1 sec for 1Mbps = 1 sec * 1,000,000 bit/s = 122.070 KB

1 sec for 10Mbps = 1 sec * 10,000,000 bit/s = 1220.703 KB

1 sec for 1Gbps = 1 sec * 1,000,000,000 bit/s = 119.209 MB

1 sec for 2Gbps = 1 sec * 2,000,000,000 bit/s = 237.418 MB

1 sec for 10Gbps = 1 sec * 10,000,000,000 bit/s = 1192.093 MB

Uses above data download size for 10 columns (160.1484375 KB max per row size) and determine the acceptable FetchSize:

• 1Mbps: FetchSize=1
• 10Mbps: FetchSize=7
• 1Gbps: FetchSize=762
• 2Gbps: FetchSize=1524
• 10Gbps: FetchSize=7622

If business users said even the varchar2 column type can hold 32KB of data, but 99% of the data is below 2 KB, then max column size in bytes (keeping other columns type as their max size):

Max row size in byte = 3 * 40 + 2 * 2KB + 2 * 16

= 120 bytes + 4 KB + 32 bytes

= 4248 bytes

= 4.148 KB

Then the acceptable FetchSize for different network download speeds are below:

• 1Mbps: FetchSize=29
• 10Mbps: FetchSize=294
• 1Gbps: FetchSize=29425
• 2Gbps: FetchSize=58851
• 10Gbps: FetchSize=294256

Default FetchSize of 10 rows is too small for business servers which often uses 1Gbps between Oracle server and Java application.

Over Tune Warning

Be aware about maximum column size (in bytes) in the SELECT statement when adjusting FetchSize:

• varchar2 - 32KB max
• LONG - 2GB max
• BLOB - 8 - 128TB max
• CLOB - 8 - 128TB max
• BFILE - 4GB max

Not all SELECT statement are identical, so don't simply reuse a very high FetchSize value that used by another SELECT statement.

Following are bad example of over tune FetchSize:

1. Business users often enter 4000 or more characters for a Note column inside a table.  The application set to display 1000 rows (FetchSize=1000) which easily download 4MB of data into Java application/servlet.. The Java application is used by 2000 concurrent users.  Programmer designed above screen in a frequently used home screen, which has 2000 concurrent users (4MB * 2000 ~ 8GB)
1. Business users should consider fetching the first 100 characters from the Note column instead of displaying entire Note.  If business users need to read specific Note, then click and display the specific Note
2. Business users want to auto download monthly pdf when click on the "Download Monthly Check Deposit" button, similar to an invoice screen that the application has (FetchSize=50).  However, business users didn't consider that the pdf in this screen often contains images of checks that are taken with cellphone, and each are 2MB easily.  This screen is used by business customers who deposit max 100 of check daily, and the screen suppose to display 1 months of records.  This means the button will download 100 checks * 31 days = 3,100 rows of checks in pdf file.  When JDBC FetchSize was set to 100 rows in this check deposit screen, it will download 302GB of data for the entire month per business customer
1. This functionality to download entire month's check should not be offered unless end-to-end network speed is 10Gbps (this still takes ~ 30 sec to download the data into Java application)
3. Reporting users designed a simple monthly operation reports which are showing 5,000 columns from few tables with big data volume, and high transactions volume daily. When setFetchSize to a high value, the report always crash the Java application (run out of heap memory)
1. setFetchSize should set to a lower value
2. Java heap memory needs to increase
3. Controls number of rows to display the output to the screen
4. Encourage users to save to file
5. Offers pagination in the screen, and set setFetchSize to match the number of rows display on the screen, or 1/5 (so next() will indirectly download 5 times from the DB server) of the total rows. Uses following Java code to determine number of rows (not all DB type supports last())
1. ResultSet rs = stmt.executeQuery(sql);
2. int rowCount = rs.last() ? rs.getRow() : 0;
3. rs.beforeFirst();