Help please! Errors with both DB2 for LUW and DB2 for z/OS
R
Ray
started a topic
over 8 years ago
I am not only completely new to DbVis, but also to DB2, so please forgive me if any of these questions sound silly to you. I am hoping to find someone who can guide me some guidance on at least one of these implementations.
By the way, I'd like to add I can successfully connect to and query both of these databases from IBM Data Studio and Toad for DB2, so the parameters are right and I have the right access. I want to be able to use DbVis, however, and that's where I'm finding trouble.
DB2 for LUW
I am able to successfully connect. However, whenever I issue a simple SELECT against a table (and I can list all tables, for example, so I know I'm connected), I get the following error. I am also showing the connection parameters I am using.
DB2 for z/OS
In this case, I cannot even connect.
Here is the entire error in that window above (which I cannot expand for a screenshot):
An error occurred while establishing the connection:
Long Message:
[jcc][t4][201][11237][4.19.49] Connection authorization failure occurred. Reason: Security mechanism not supported. ERRORCODE=-4214, SQLSTATE=28000
Details:
Type: com.ibm.db2.jcc.am.SqlInvalidAuthorizationSpecException
Error Code: -4214
SQL State: 28000
1) Nested Exception:
Long Message:
[jcc][1071][10615][4.19.49] Caught java.security.InvalidAlgorithmParameterException while initializing EncryptionManager. See attached Throwable for details. ERRORCODE=-4223, SQLSTATE=null
Details:
Type: com.ibm.db2.jcc.am.SqlException
Error Code: -4223
I did find the following link which may refer to this specific error:
However, I click on the link on the bottom to download the latest IBM Java development kit (JDK) (is this different from the Oracle one JDK?), which takes me to a page where all downloads are for Linux. I don't know what to do at this point.
Help please! Please do keep in mind I can connect and query from other environments as mentioned above. This should make a difference.
Thanks in advance!
Best Answer
R
Ray
said
over 8 years ago
This has been fixed. I wanted to share my solution in case someone who comes across this post can benefit from it.
As I suspected, this was related to the Java environment that installs with DbVisualizer. In order to connect to DB2 for z/OS (at least with our configuration), it seems DbVisualizer needs to use the IBM SDK instead.
IBM does not provide the SDK for Windows, but it is included in the IBM Development Package for Eclipse downloadable from the following location:
Changing the environment variable did not work for me. Modifying the pref_jre.cfg, however, did. All this file needs to contain is the full path of the ibm_sdk80 directory in the Eclipse package you downloaded and unzipped.
If everything goes well, your DbVisualizer Help >About screen should look like this:
You do not even need to specify the securityMechanism property mentioned above. The default value (3) worked for me, as did 7.
Roger, thanks a lot for your help once again and good luck to everyone who has this problem!
Are you accessing DB2 running on z/OS or DB2 running on Linux/Unix/Windows (LUW)?
In DbVisualizer make sure the Database Type setting is set to the first Auto Detect entry.
In the Connection tab for your DB2 connection, and in the lower Options section there is a Show Detailed Error Messages. Make sure it is checked.
Last, what is the SQL you are running in the SQL Commander? The error indicates there is a syntax error somewhere.
Regards
Roger
1 person likes this
R
Ray
said
over 8 years ago
Roger, thank you for responding and trying to help.
I am reporting both an issue while connecting to z/OS and an issue while issuing a statement against a LUW database I successfully connected to. This is broken out in my thread above in two sections clearly marked.
The z/OS screenshot also shows the Database Type is set to Auto Detect and it did detect z/OS effectively. It still couldn't connect due to that "Security mechanism not supported" error (details above).
The SQL I am running against the LUW database is the same SQL I am running in both IBM Data Studio and Toad for DB2 against the same database and is running successfully, so it doesn't have any syntax errors. It's a simple SELECT * FROM TABLE command.
Any ideas?
Thanks again!
Ray
Roger Bjärevall
said
over 8 years ago
Hi Ray,
It seems you may need to specify the driver property securityMechanism for your DB2 connection in DbVisualizer. The default is to send the password as CLEAR_TEXT_PASSWORD_SECURITY (3) from the below table:
USER_ONLY_SECURITY = 4;
CLEAR_TEXT_PASSWORD_SECURITY = 3;
ENCRYPTED_PASSWORD_SECURITY = 7;
ENCRYPTED_USER_ONLY_SECURITY = 16;
ENCRYPTED_USER_AND_PASSWORD_SECURITY = 9;
ENCRYPTED_USER_AND_DATA_SECURITY = 12;
ENCRYPTED_USER_PASSWORD_AND_DATA_SECURITY = 13;
KERBEROS_SECURITY = 11;
PLUGIN_SECURITY = 15;
TLS_CLIENT_CERTIFICATE_SECURITY = 18;
What you should do is to open the Properties tab for your DB2 connection and in the list of categories select Driver Properties. In the right grid, add a new row and specify securityMechanism in the Parameter field and in the Value field pick the integer for the corresponding option above. Check with your DBA what type of mechanism should be used.
Please let me know the progress.
Regards
Roger
1 person likes this
R
Ray
said
over 8 years ago
Roger,
Thank you for your help once again.
So I went ahead and tried each of those different security mechanisms you suggested and, even though none of them worked, they did behave differently. I wanted to share what I got with each hoping this can shed some light on this issue.
CLEAR_TEXT_PASSWORD_SECURITY = 3;
... throws the following 2-part error:
Long Message:
[jcc][t4][201][11237][4.19.49] Connection authorization failure occurred. Reason: Security mechanism not supported. ERRORCODE=-4214, SQLSTATE=28000
Details:
Type: com.ibm.db2.jcc.am.SqlInvalidAuthorizationSpecException
Error Code: -4214
SQL State: 28000
1) Nested Exception:
Long Message:
[jcc][1071][10615][4.19.49] Caught java.security.InvalidAlgorithmParameterException while initializing EncryptionManager. See attached Throwable for details. ERRORCODE=-4223, SQLSTATE=null
Details:
Type: com.ibm.db2.jcc.am.SqlException
Error Code: -4223
Long Message:
[jcc][1071][10615][4.19.49] Caught java.security.InvalidAlgorithmParameterException while initializing EncryptionManager. See attached Throwable for details. ERRORCODE=-4223, SQLSTATE=null
Details:
Type: com.ibm.db2.jcc.am.SqlException
Error Code: -4223
TLS_CLIENT_CERTIFICATE_SECURITY = 18;
... says "Connecting..." for about 5 minutes and, eventually, the following error comes up:
Product: DbVisualizer Free 9.5 [Build #2574]
OS: Windows 7
OS Version: 6.1
OS Arch: amd64
Java Version: 1.8.0_92
Java VM: Java HotSpot(TM) 64-Bit Server VM
Java Vendor: Oracle Corporation
Java Home: c:\program files\dbvisualizer\jre
DbVis Home: C:\Program Files\DbVisualizer
User Home: C:\Users\rgutier1
PrefsDir: C:\Users\rgutier1\.dbvis
SessionId: 803
BindDir: null
An error occurred while establishing the connection:
Long Message:
[jcc][t4][2030][11211][4.19.49] A communication error occurred during operations on the connection's underlying socket, socket input stream,
or socket output stream. Error location: Reply.fill() - socketInputStream.read (-1). Message: Remote host closed connection during handshake. ERRORCODE=-4499, SQLSTATE=08001
Details:
Type: com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException
Error Code: -4499
SQL State: 08001
From some of these error messages and the IBM link I provided in my original post above, I still suspect this may be related to the JRE/JDK being used by DbVisualizer. I downloaded and install the version that includes the Java VM. According to Help > About > System Properties, DbVisualizer is using the Sun/Oracle JRE. I read somewhere that in order to connect to DB2 on z/OS, it should be using the IBM JDK (not JRE). Yay? Nay?
Thanks a lot once again!
Ray
R
Ray
said
over 8 years ago
Answer
This has been fixed. I wanted to share my solution in case someone who comes across this post can benefit from it.
As I suspected, this was related to the Java environment that installs with DbVisualizer. In order to connect to DB2 for z/OS (at least with our configuration), it seems DbVisualizer needs to use the IBM SDK instead.
IBM does not provide the SDK for Windows, but it is included in the IBM Development Package for Eclipse downloadable from the following location:
Changing the environment variable did not work for me. Modifying the pref_jre.cfg, however, did. All this file needs to contain is the full path of the ibm_sdk80 directory in the Eclipse package you downloaded and unzipped.
If everything goes well, your DbVisualizer Help >About screen should look like this:
You do not even need to specify the securityMechanism property mentioned above. The default value (3) worked for me, as did 7.
Roger, thanks a lot for your help once again and good luck to everyone who has this problem!
Roger Bjärevall
said
over 8 years ago
Hi Ray,
Thanks a lot for the details how you solved this. It will most definitely be useful for others.
Ray
I am not only completely new to DbVis, but also to DB2, so please forgive me if any of these questions sound silly to you. I am hoping to find someone who can guide me some guidance on at least one of these implementations.
By the way, I'd like to add I can successfully connect to and query both of these databases from IBM Data Studio and Toad for DB2, so the parameters are right and I have the right access. I want to be able to use DbVis, however, and that's where I'm finding trouble.
DB2 for LUW
I am able to successfully connect. However, whenever I issue a simple SELECT against a table (and I can list all tables, for example, so I know I'm connected), I get the following error. I am also showing the connection parameters I am using.
DB2 for z/OS
In this case, I cannot even connect.
Here is the entire error in that window above (which I cannot expand for a screenshot):
I did find the following link which may refer to this specific error:
http://www-01.ibm.com/support/docview.wss?uid=swg21244597
However, I click on the link on the bottom to download the latest IBM Java development kit (JDK) (is this different from the Oracle one JDK?), which takes me to a page where all downloads are for Linux. I don't know what to do at this point.
Help please! Please do keep in mind I can connect and query from other environments as mentioned above. This should make a difference.
Thanks in advance!
This has been fixed. I wanted to share my solution in case someone who comes across this post can benefit from it.
As I suspected, this was related to the Java environment that installs with DbVisualizer. In order to connect to DB2 for z/OS (at least with our configuration), it seems DbVisualizer needs to use the IBM SDK instead.
IBM does not provide the SDK for Windows, but it is included in the IBM Development Package for Eclipse downloadable from the following location:
https://www.ibm.com/developerworks/java/jdk/eclipse/index.html
You need to unzip that package somewhere on your hard drive and then specify the "ibm_sdk80" directory as the root of your Java VM in DbVisualizer.
According to the following post, there are two ways to do this:
https://support.dbvis.com/support/solutions/articles/1000231944-how-do-i-change-the-java-version-that-dbvisualizer-use-
Changing the environment variable did not work for me. Modifying the pref_jre.cfg, however, did. All this file needs to contain is the full path of the ibm_sdk80 directory in the Eclipse package you downloaded and unzipped.
If everything goes well, your DbVisualizer Help >About screen should look like this:
You do not even need to specify the securityMechanism property mentioned above. The default value (3) worked for me, as did 7.
Roger, thanks a lot for your help once again and good luck to everyone who has this problem!
- Oldest First
- Popular
- Newest First
Sorted by Oldest FirstRoger Bjärevall
Ray,
Are you accessing DB2 running on z/OS or DB2 running on Linux/Unix/Windows (LUW)?
In DbVisualizer make sure the Database Type setting is set to the first Auto Detect entry.
In the Connection tab for your DB2 connection, and in the lower Options section there is a Show Detailed Error Messages. Make sure it is checked.
Last, what is the SQL you are running in the SQL Commander? The error indicates there is a syntax error somewhere.
Regards
Roger
1 person likes this
Ray
Roger, thank you for responding and trying to help.
I am reporting both an issue while connecting to z/OS and an issue while issuing a statement against a LUW database I successfully connected to. This is broken out in my thread above in two sections clearly marked.
The z/OS screenshot also shows the Database Type is set to Auto Detect and it did detect z/OS effectively. It still couldn't connect due to that "Security mechanism not supported" error (details above).
The SQL I am running against the LUW database is the same SQL I am running in both IBM Data Studio and Toad for DB2 against the same database and is running successfully, so it doesn't have any syntax errors. It's a simple SELECT * FROM TABLE command.
Any ideas?
Thanks again!
Ray
Roger Bjärevall
Hi Ray,
It seems you may need to specify the driver property securityMechanism for your DB2 connection in DbVisualizer. The default is to send the password as CLEAR_TEXT_PASSWORD_SECURITY (3) from the below table:
USER_ONLY_SECURITY = 4;
CLEAR_TEXT_PASSWORD_SECURITY = 3;
ENCRYPTED_PASSWORD_SECURITY = 7;
ENCRYPTED_USER_ONLY_SECURITY = 16;
ENCRYPTED_USER_AND_PASSWORD_SECURITY = 9;
ENCRYPTED_USER_AND_DATA_SECURITY = 12;
ENCRYPTED_USER_PASSWORD_AND_DATA_SECURITY = 13;
KERBEROS_SECURITY = 11;
PLUGIN_SECURITY = 15;
TLS_CLIENT_CERTIFICATE_SECURITY = 18;
What you should do is to open the Properties tab for your DB2 connection and in the list of categories select Driver Properties. In the right grid, add a new row and specify securityMechanism in the Parameter field and in the Value field pick the integer for the corresponding option above. Check with your DBA what type of mechanism should be used.
Please let me know the progress.
Regards
Roger
1 person likes this
Ray
Roger,
Thank you for your help once again.
So I went ahead and tried each of those different security mechanisms you suggested and, even though none of them worked, they did behave differently. I wanted to share what I got with each hoping this can shed some light on this issue.
CLEAR_TEXT_PASSWORD_SECURITY = 3;
... throws the following 2-part error:
USER_ONLY_SECURITY = 4;
KERBEROS_SECURITY = 11;
PLUGIN_SECURITY = 15;
... all throw the following error:
ENCRYPTED_PASSWORD_SECURITY = 7;
ENCRYPTED_USER_ONLY_SECURITY = 16;
ENCRYPTED_USER_AND_PASSWORD_SECURITY = 9;
ENCRYPTED_USER_AND_DATA_SECURITY = 12;
ENCRYPTED_USER_PASSWORD_AND_DATA_SECURITY = 13;
... all throw the following error:
TLS_CLIENT_CERTIFICATE_SECURITY = 18;
... says "Connecting..." for about 5 minutes and, eventually, the following error comes up:
From some of these error messages and the IBM link I provided in my original post above, I still suspect this may be related to the JRE/JDK being used by DbVisualizer. I downloaded and install the version that includes the Java VM. According to Help > About > System Properties, DbVisualizer is using the Sun/Oracle JRE. I read somewhere that in order to connect to DB2 on z/OS, it should be using the IBM JDK (not JRE). Yay? Nay?
Thanks a lot once again!
Ray
Ray
This has been fixed. I wanted to share my solution in case someone who comes across this post can benefit from it.
As I suspected, this was related to the Java environment that installs with DbVisualizer. In order to connect to DB2 for z/OS (at least with our configuration), it seems DbVisualizer needs to use the IBM SDK instead.
IBM does not provide the SDK for Windows, but it is included in the IBM Development Package for Eclipse downloadable from the following location:
https://www.ibm.com/developerworks/java/jdk/eclipse/index.html
You need to unzip that package somewhere on your hard drive and then specify the "ibm_sdk80" directory as the root of your Java VM in DbVisualizer.
According to the following post, there are two ways to do this:
https://support.dbvis.com/support/solutions/articles/1000231944-how-do-i-change-the-java-version-that-dbvisualizer-use-
Changing the environment variable did not work for me. Modifying the pref_jre.cfg, however, did. All this file needs to contain is the full path of the ibm_sdk80 directory in the Eclipse package you downloaded and unzipped.
If everything goes well, your DbVisualizer Help >About screen should look like this:
You do not even need to specify the securityMechanism property mentioned above. The default value (3) worked for me, as did 7.
Roger, thanks a lot for your help once again and good luck to everyone who has this problem!
Roger Bjärevall
Hi Ray,
Thanks a lot for the details how you solved this. It will most definitely be useful for others.
Regards
Roger
-
Forward Engineering
-
DB2 Database Connection Dropping every 20-30mins or so ?
-
roles are not unfolded
-
Install4j: Java(Tm) VM could not be started
-
Worksheet name default when exporting to Excel?
-
sqlformatter and extract
-
Bug with empty password
-
dbvis 5.0 gives error after opening
-
Saving file makes it incompatible with other SQL tools!!!?!?!?!?
-
Error Schema Name in References view.
See all 1316 topics