DB2 Interfaces with SPSS

You can analyze DB2 data using SPSS 10 software by using an ODBC connection. There are just a few steps necessary to make it work. The SPSS - DB2 interface is for reading data only.

First, you must establish your ODBC connection to the DB2 database. If you have not yet installed the DB2 Client on your PC, then visit our DB2 Client Enabler software page for instructions.

SPSS recommends use of their Database Wizard to initially setup the SPSS - DB2 relationship.

• While in SPSS, choose File / Open Database / New Query to start the Database Wizard.

  

• In this window you will see all the database connections that your PC knows, including the one you created when installing the DB2 Client Enabler software. These are called DSNs for data source names.
• If you do not see the DSN for your database, you may use this window to add a new one to your list. Choose the Add Data Source button.
• In the sample window above, we have setup a DSN called SAMPLE, which is the ODBC connection to a DB2 database called SAMPLE.
• Once you highlight the desired DSN you want to work with, choose the Next button.
• You will be prompted for username and password for your DB2 database.

  

• After a successful logon, you will be given a list of all the tables within the DB2 database (this includes system catalogs and tables which usually begin with SYS). In most instances you do not need to use these.

  

• To retrieve the entire table, click on the table then drag and drop it to the right.
• If you only want to work with a few columns in the table, click on the plus sign (+) to the left of the table, then drag and drop the columns you wish to work with.
• Once you have all the columns listed on the right, choose Next or Finish. If you choose Next it will provide a way to limit the records (cases) retrieved from the database. This also allows data manipulation by using function statements.

  

• Again choose Next or Finish. This time the Next button will allow you to name the SPSS variables that will hold the values from the database. By default the variables are the names of the columns from the database.
• SPSS variable names: Since DB2 permits column names up to 18 characters, be aware that SPSS may truncate your DB2 column names and if necessary make them unique by adding numbers at the end of the column name. Suggestion: When creating DB2 tables that will be analyzed with SPSS, use a maximum of 8 characters for column names.

  

• The last window will display the SQL code that was generated and allow you to modify and/or save it for a later use.

  

• When you choose Finish, SPSS will retrieve the data from the database and display it in the SPSS window.

Database Table Update via SPSS

After you make a DB2 database connection, retrieve a table into SPSS (as described above), and make changes to the data, you can, of course, save the data as an SPSS data file on your PC, in the usual manner (by choosing File / Save As from the menus).

However, you can also save those changes back to the DB2 database table. That is, you can update the table, directly from SPSS.

To do the database update, you must issue a syntax command. Thus, you need to open a Syntax window: from the File menu select "New" and then select "Syntax".

In the Syntax window, type the following command:

Be careful to type the command exactly as shown above (and note the period at the end of the command).

In particular, be sure to type the table name in ALL CAPITAL letters; otherwise, you will create a new table. For example, if you incorrectly type "Table3" (instead of "TABLE3"), you will create a new table named Table3, instead of updating the original table (which is named TABLE3). And, you will NOT be able to access Table3 via DB2 - not even to delete it!. This is because when you use DB2 directly on Aurora, it only recognizes table names that are in all capital letters.

After you type the command, pull down the Run menu (in the Syntax Window) and select All. This will run the command (and any other commands that are in the Syntax window - so don't type any others).

After you run the command, switch to the SPSS Output window, if it's open, and look for any error messages that might have been generated by the command. If all goes well, there are no messages displayed.

This facility is available in SPSS 11, 12, and 13.

More about.....

• DB2 - this link will provide access to the online DB2 Online Books.