DB2

Sui and DB2 work very well together. During the development of Sui a number of users have extensively used Sui to access DB2 data. These users have accessed DB2 for OS/390 and z/OS (V5, V6, V7, V8, V9,V10,V11 and V12) using DB2 Connect.

Specific strengths when accessing DB2 for z/OS:

• DB2 sub-systems on z/OS often tend to have a large number of tables. Thousands or even tens of thousands of tables are not unusual. If you ever have tried to access DB2 for z/OS system using MS-Access or Excel thru ODBC (without customizing the ODBC Settings) you know that it can take a couple of minutes to build the table list. Sui let you choose if you want to limit the table list or not before listing the tables (Edit->Sui Preferences, Table Filter)
• Sui let you specify the isolation level of the query. If you want to access a production z/OS DB2 system and just want to issue Select statements, un-committed Read might be a good choice.
• Sui closes the cursor after the query is completed. The cursor is closed regardless if the cursor has fetched all rows in the result-set or the max no of rows have been reached. Implying that no locks are held after the query returns the result set.
• Sui releases the connection between queries, implying that the connection only exists when the query executes.

Set-up a connection to a DB2 database:

In this sample scenario the user wants to set up a database connection to a database that is catalogued in DB2 under the name SAMPLE using the type-2 driver (DB2 Appl). In this example the user have catalogued the database locally on the user machine (using DB2 Connect, DB2 UDB or DB2 Client). To make the database selectable from the query window follow these steps:

• Select File-> Available JDBC resources
• Select the Sample Driver Combo Box and select the row labeled ‘DB2-Appl’
• Select the + button, to add the DB2 Appl driver to the Driver Properties list
• Select the Sample URL Combo Box and select the row labeled ‘DB2-Appl’
• Select the + button, to add the DB2-Appl URL
• Set the value dbname to SAMPLE in the pop-up menu and click OK

Problem resolution Q and A’s for DB2

Q: When i try to connect, I get a message stating that the Driver can’t be found. (Or the connection fails with any obvious messages). What is wrong?

A: Make sure that DB2’s driver is available in the classpath. This short video shows a user adding a DB2 database Connection, including adding DB2’s driver to the classpath. Note that it might also be necessary to add the license jar file.

Q: Can I use the JDBC/ ODBC Driver to access DB2 data?

A: Yes you can, you should however be aware that you get performance degradation you also lose some functionality. The general recommendation is to use the native DB2 JDBC drivers, if possible.

Q: Does Sui support DB2’s universal driver (the universal driver is a native java JDBC type 4 driver).

A: Yes Sui supports the universal driver. You can select the JCC driver from the sample drivers. Sui has been extensively tested with the JCC driver.

Some usefull DB2 Links: