Gone are the days when the DB2 Control Center was part of the DB2 install. In comes IBM Data Studio to replace and expand on the capabilities once provided by the DB2 Control Center. What can make this even better? This tool is entirely free and can go beyond just connecting to DB2.
To obtain and install IBM Data Studio, see this Technote: Download and install IBM Data Studio Version 4.1.x
Connecting to a Database:
Upon launching IBM Data Studio, in the left hand "Administration Explorer" you will find the "New connection to Database" icon (or you can right click on "All Database" -> "New connection to Database"
In our example, we will be connecting to the Release database used to store information in a WebSphere Portal Server.
This will bring up a "New Connection" window
Populating these fields:
- On the left hand side select the type of datasource you are connecting to
- JDBC driver: Select the appropriate drivers for your datasource
- Populate the Database/Host/Port number of the datasource you are connecting to
Note for WebSphere, you can find this under the Integrated Solutions Console --> Resources --> JDBC --> Data sources --> <dataSource> -> Database name
- Populate the "User name" and "Password" with a DB2 administrator account
Note for WebSphere, you can find this under the Integrated Solutions console --> Integrated Solutions Console --> Resources --> JDBC --> Data sources --> <dataSource> --> JAAS - J2C authenticationdata --> <JAAS Auth for the dataSource>
- Select the "Save Password" option to prevent future prompts for password when connecting to the database
- Use the "Test Connection" to verify that you have the proper information populated, then click Finish
Browsing the data
Once connected to the database, navigate to the "Tables" to find all the tables that Portal uses in the Release Database. The USER_DESC table is of interest it is a common table worked with when performing the CleanupUsers process. See this blog entry Understanding the CleanupUsers Process for more details.
To review the data for the USER_DESC find it in the list of tables then right click and go to Data -> Browse Data. Note that double clicking on the table itself will not browse it's data, instead it pulls up the attributes definitions available in the table.
The difference between "Browse Data" and "Edit Data" is that the values of each entry in the table will be editable. Use "Browse Data" to avoid accidentally make unwanted changes.
Once in the table, this tool displays a default maximum of 500 entries.
To find a specific subset of results, use the "Filter the Data" () icon to launch the Table Data Filter window.
In the filter window, the Column Selection allows you to add the desired columns to display. Then used the Add button under Row Selection Conditions apply your search criteria(s).
Select OK to apply The display conditions.
Often support will ask for exports of specific table(s) for investigation. Typically such requests are accompanied with exact SQL command to be executed via command line, however this process can also be done with IBM Data Studio. Right clicking the desired table and selecting Unload --> With SQL... option to export the entire table into a text format with delimiters, this export the full table to the workstation where IBM Studio is installed. For more complex export commands, use Unload --> Export with Utility. This option runs the command on the DB2 server, thus the export results will be located on the DB2 server.
Full documentation on IBM Data Studio can be found here.