Before you start

About this tutorial

This tutorial introduces new users of IBM Informix 11 to relational database concepts through a fun and interactive game called The Informix Detective Game. The recommended audience is small business owners, students, teachers and professors, or anyone wanting to gain an understanding of data management.

Back to top

Objectives

This tutorial assumes that you have no prior knowledge of relational databases or SQL. By completing this tutorial, you will:

• Gain an understanding of database concepts such as tables, rows (records), columns (fields), and values.
• Learn about relational databases, data models, unique keys, primary keys, foreign keys, and table joins.
• Gain a working knowledge of the primary SQL statements: select, update, insert, and delete.
• Use the Informix command window to import a database.
• Use dbaccess to create and execute SQL code.

We have included the solution set in a PDF, which you can download from this tutorial.

Back to top

System requirements

This tutorial was written assuming you are using Informix Developers Edition running on MS Windows. To play this game, you should have IBM Informix installed, the InformixGame database loaded, and the clue file running.

Back to top

Download Informix

1. If you do not already have Informix 11 installed on your PC, download Informix from the product download Web site.
2. Sign in with your IBM ID. Select Informix Developer Edition Windows 32 Version 11.70 xxxDE and click Continue.
3. Provide the information requested and indicate your agreement with the license agreement. Click I confirm to continue.
4. Verify that Informix Developer Edition Windows 32 is selected and that the file version number is 11.70. Click Download Now.
   
   Note: If Informix is already installed, there is no need to install another copy of the software. Proceed to the "Install the Informix Detective Game database" section of this tutorial.

Back to top

Install Informix

1. Once the download is complete, navigate to the folder containing the downloaded file.
2. Unzip the file. Choose the option to unzip and install the file.
3. If the unzip does not give the option to install, from the unzipped folder find and click ids_install.exe.
4. The install wizard will walk you through the software installation process.
5. In order to complete the install successfully, click Accept for each license agreement. Also, select Typical for each setup type, and make note of all the user Informix and passwords you are requested to create.

Back to top

Install the Informix Detective Game database

1. Ensure Informix is installed.
2. Download the Informix Detective Game database file from the Downloads section of this tutorial.
3. Create a directory called informixgame in drive C.
4. Unzip the InformixDetectiveFile.zip file to that directory.
5. From the Windows Start menu, choose All Programs > IBM Informix 11.70 > ol_informix1170. A command prompt window will pop up.
6. From the command prompt, type cd\informixgame to get to the informixgame directory. In order to create the database, you need to type dbaccess sysmaster createtables.sql. You should see the message Database created and Database closed.
7. On the next line, type SET DBDATE=y4md- to set the date format.
8. Then type dbaccess informixgame loadtables.sql to create the tables in the detective database. The tables are successfully created if you see four messages indicating rows were loaded. The rows loaded messages should reflect 151, 140, 10, and 140 rows loaded.
9. Close the command prompt window.

Back to top

Run the Informix Detective Game

In order to run the game's clue file (INFORMIXGAME.JAR), it has to be set up to open with Java Virtual Machine (JVM) software, for example: JAVAW.EXE. To set this up:

1. Right-click the INFORMIXGAME.JAR file.
2. Click Properties.
3. Click the General tab.
4. Specify Type of file to be Executable Jar File.
5. The Opens with window should reflect a JVM that you have locally installed on your machine. If this is not the case, click Change.
6. From the Open With pop-up window, locate and select your JVM software, for example JAVAW.EXE.
7. Click OK.
8. Click Apply.
9. Click OK again.

You can now launch the INFORMIXGAME.JAR file. To do so, double-click the icon, or you can create a shortcut to the file for easier access.

Back to top

To execute SQL commands

1. From the Windows start menu, choose All Programs > IBM Informix 11.70 > ol_informix1170.
2. From the command prompt window, type dbaccess. A DBAccess window will open.
3. From the main menu at the top of the interface, select Query-language.
4. Select informixgame@ol_informix1170 from the list of databases.
5. Select New from the Query-language menu.
6. Type an SQL statement.
7. Press the Esc key to exit the editor.
8. Select Run to run the SQL statement.

Back to top

Create a shortcut to the Informix Detective Game

To create a shortcut to the INFORMIXGAME.JAR file on your desktop:

1. Right-click anywhere on the desktop.
2. Select New > Shortcut.
3. In the Type the location of the item field, type C:\informixgame\informixgame.jar.
4. Click Next.
5. In the field called Type a name for this shortcut, type informixgame (it should already appear by default).
6. Click Finish.

Back to top

Tips for using Informix

1. To execute SQL statements, use dbaccess:
• From the Windows start menu, choose All Programs > IBM Informix 11.70 > ol_informix1170. A command prompt window will pop up.
• From the command prompt window, type dbaccess.
• From the dbaccess main menu, select Query-language.
• Select informixgame@ol_informix1170 from the list of databases.
• Click New from the Query-language menu.
• Type an SQL statement.
• Press the Esc key to exit the editor.
• Click Run to run the SQL statement.
2. To modify your previous SQL statement:
• From the Display menu, click Exit.
• From the SQL menu, click Modify.
• Edit your prior SQL statement.
• Press the Esc key when done editing.
• Click Run.
3. If the width of the fields to be displayed is less than 80 characters wide, the results are displayed in a table format.
4. If the width of the fields to be displayed is greater than 80 characters wide, the results for each record is displayed over multiple lines, as defined by the fields in the table.
5. To see the count of the number of records for an SQL statement, click Next until the number of rows retrieved is displayed.

You are now ready to play the game!

1 of 8 | Next

Comments

Back to top

Table of contents

• Before you start
• Game scenario
• Introducing Informix and relational databases
• SQL statements
• Joining tables
• Downloads
• Resources
• About the authors

---

1 of 8 | Next

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.