![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Do you need to create Web pages to access a database? Do you want to develop and deploy on the operating system of your choice? Are you pressed for time? This article will show you how to develop an application to access your data using DB2 UDB and Application Developer. First, you'll create the database. After the database is created, you'll create a table, then add some columns. Jumping over to Application Developer, you'll then develop our application. You'll also create a SQL statement to access our database. We'll also need to create some JSPs, servlets, and HTML pages.
Biker Bob's Bike Shop and Bar needs a way to keep track of their parts. The parts arrive at the loading dock. Larry (a.k.a., Tiny) the parts guy needs to log the parts in and put them into the proper parts bin. Tiny knows bikes not computers, so the management of Biker Bob's Bike Shop and Bar would like to put a "Dumb terminal" on the loading dock for Tiny to use. If it's GUI-based and user friendly, Tiny should be able to navigate a browser-based thin client. The process works this way: From the loading dock, Bob logs in and updates the database with the Parts Number, Name, Location, Quantity, Price and any Notes. Tiny also needs to receive a reply that the database was successfully updated.
- IBM DB2 UDB V8.2 for Linux
You can download DB2 UDB here.
If you need assistance installing DB2 UDB, refer to the following Website.
- IBM WebSphere Studio Application Developer for Linux
You'll also need Application Developer.You can download a copy of Application Developer here.
If you need assistance installing Application Developer, refer to the following Website.
Assuming that DB2 UDB V8.2 and Application Developer are already installed, the first thing you need to do is create the database and table. We're going to do all of our database work using DB2's Command Center. The Command center is located inside the Control Center. So, you need to launch the Control Center. You can launch the Control Center two different ways, using either the GUI or the Command Line. Launch the Control Center now.
Figure 1. DB2 Control Center
On the left-hand side of the Control Center, you can see a heirarchical view of the Database Server. We need to create a database named parts. In the lower right-hand side of the window, click on Create New Database. See Figure 2, below.
Figure 2. Create New Database
During the creation of the database, you'll be prompted to answer several questions regarding database maintenance. The first one is creation of a maintenance window. DB2 UDB has the ability to automatically back up and defragment the database. DB2 UDB needs to make the database unavailable to do maintenance. Select the following:
- Yes, I can specify an offline maintenance window of at least an hour when the database is inaccessible.
Figure 3. Maintenance Window
Next you will need to set a time to take the database offline. Since Biker Bob's shop closes for the day at 5:00 p.m., we'll select an offline time for 1:00 in the morning. See Figure 4, below
Figure 4. Offline time
DB2 UDB can be set up for e-mail notification if something goes wrong.
Figure 5. Mail Server
The next window sets up the notification e-mail addresses. Joe (a.k.a., Road Rash) is the DBA. He understandably wants to be notified when something goes wrong.
Figure 6. e-mail notification
The window in Figure 7 shows a summary of the database creation. It includes Database Name, Directory Location, Maintenance Window, SMTP Server, and Contact Info. If you are satisfied with the selections, select Finish. The database will then be created.
Figure 7. Database Summary
Now that you have successfully created the database, you need to create a parts table. Take a look at Figure 8, below. You'll notice on the left-hand side the tree structure view of the parts database. The top window will contain the table, once it's created. The bottom right window contains our action items. This is where you will create the database table.
- Click on Create New Table
Figure 8. Create new table
This is where we specify the Table Name, Schema, and any Comments. See Figure 9.
Figure 9. Table Name
You will use the pre-defined columns option with DB2 UDB to help create the columns. Once you insert the columns, then you can do a little more customization to fit our application's need. Select the Add Predefined columns button.
Figure 10. Column Creation
In the Column categories, highlight Inventory and select PART_NUMBER, PART_NAME, LOCATION, QUANTITY, and NOTES. Select the MONEY Column category and insert UNIT_PRICE into the columns to create. Take a look at Figure 11, below. You should have six columns. Select Apply.
Figure 11. Predefined Columns
We need to to make some changes to the columns. All columns except the NOTES column needs to be set as Not Nullable. Highlight the columns and select Change. The nullable button should not be checked. The PART_NUMBER column datatype needs to be changed to smallinit. When you update the inventory, the PARTS_NUMBER column needs to assign a PART_NUMBER automatically. Select the Value generation tab. Pick identity and set Initial value to 1 and increment by 1. See Figure 11a, below.
Figure 11a. PARTS_NUMBER
The UNIT_PRICE Column has two values that need to be set. The two values are Precision and Scale. Precision is the total number of digits both left and right of the decimal. Scale sets the number of digits to the right of the decimal.
Figure 12. Unit Price
Once you have completed these tasks, the Column changes should look like Figure 13, below. Click the Finishbutton to create the table.
Figure 13. Predefined Columns
You will see the generated SQL and Command completed successfully message like shown in Figure 14.
Figure 14. Table Creation
Let's do a quick recap of what you've done so far. You created a parts database. You also set up a maintenance window and configured an e-mail contact in case something goes wrong. The next step was to create a database table called parts. This table contains six columns. The columns are PARTS_NUMBER, PARTS_LOCATION, QUANTITY, NOTES and UNIT_PRICE. The PARTS_NUMBER column will automatically create a number and increment by one. All columns were set to Not Nullable with the expection of the NOTES column.
WebSphere Studio Application Developer
The next step in our database Web page project is to actually develop the Web pages themselves. In this step, we'll use Application Developer to create our HTML, SQL, JSPs, and Servlets. Application Developer is an Integrated Development Environment based on the Eclipse framework.
Log in as the DB2 user, i.e., ( db2inst1) and start Application Developer. You'll be asked to select a workspace of your choice. Since you're going to create a New Project, select File - > New -> Project. I named the project BikerBobs.
Figure 15. New Project
You'll notice Application Developer will create a Web Prespective for us. In the left-hand column, you'll notice our BikerBobs project.
Figure 16. BikerBobs
Now that we have created our Web project, we need to set up the database connections. On the top menu bar, select Window- > Open Prespective- > Data. In the lower left, you'll see the Data prespective. See Figure 17.
Figure 17. Data Perspective
Move your mouse over the Data Perspective white space and right-click. Select New Connection to create a new database connection.You'll see the connection in the lower left-hand side. The following information needs to entered. Connection Name- > Database- > User ID- > Password- > Database Type- > JDBC Driver. The class file can be located at /home/db2inst1/sqllib/java2/db2java.zip See Figure 18, below.
Figure 18. Data Connection
The database connection has now been created.
Figure 18. Data Connection
Adding a simple Database Project
After creating a database connection, you should create a separate project to store the generated files in. Organizing files this way eliminates clutter from your main project, which makes it easier for you to divide work between team members. On the workbench menu bar, click File- > New- > Project. In the New Project wizard, from the list on the left, click Simple, and on the right, Project, then Next. In the Project Name field, type inventory and click Finish.
Figure 20. Database Project
Now that you have created a database connection, you need to associate the connection with the inventory project you created. To associate the connection with the project In the DB Servers perspective, double-click on the Con1 connection to expand it. Highlight the parts database, right-click and select Import to Folder. Import the database design into the inventory project.
Creating Web pages that access database tables is relatively easy when you use the Create Database Web Pages wizards. This wizard assists you in doing the following.
- Specifying dynamic web pages to create
- Specifying SQL statements information
- Constructing an SQL statement
- Specifying variable values
- Executing the SQL statement
- Specifying runtime database connection information
- Designing an input form
To start the create database wizard and specify dynamic Web pages to create:
- In the left margin of the workbench, switch to the Web perspective
- In the J2EE Navigator view, expand the BikerBobs project folder and select/right-click on the Web Content folder.
- On the pop-up menu, click New > Other
- In the New wizard, click Web, then Database Web Pages, then Next
Figure 21. Database Web pages
The Create Database Web Pages wizard opens. For the Model pull-down, select IBM Database Access Tag Library – Insert statement
Figure 22. Database Web pages
Click Next. The Choose SQL Method window appears. Select Be guided through creating an SQL statement. Make sure the option Use existing database model is selected. Click on Browse, and expand the inventory project folder. Then select the parts database. Click OK.
Figure 23. Creating SQL statements
Construct a SQL statement
- On the Tables tab, in the Available Tables list, expand the Schema node and the Tables node.
- Select the DB2INST1.parts table and click > to add it to the list of Selected tables.
Figure 24. Table Selection
We need to assign Column values for each row. Remember we don't have to assign a value to Part_Number because that value will be automatically assigned. Once you have assigned values, click Next.
Figure 25. Column Values
This window show the generated SQL. Click on Parse to check the statement for syntax errors. Let's now test the statement, click on Execute .
Figure 26 SQL Statement
Figure 27 shows the Insert SQL statement. Click on Execute.
Figure 27. Executing the SQL Statement
The Specify Variable window opens, as shown in Figure 28. We need to enter some values to test our Insert statement. Enter vaules for the five columns. Remember that by specifying a value 'Seat' (with single quotes), we are inserting string values into the database column PART_NUMBER. That holds true for the NOTES column as well. Click Finish to test the statement.
Figure 28. Varible Values
Under Query results, you'll notice that the statement executed successfully. Since this was an INSERT, no results were returned. Click on Close. The next page that comes up is the Runtime Connection page. Accept the defaults and click Next.
Figure 29. Query Results
We're now ready to create the Front Controller. Make sure the Front Controller is selected. We can also assign an Error page, as well as a Cascading Style sheet. Click Next.
Figure 30. Front Controller
The Designing an Input Form window allows us to customize the input form to meet our needs. We can change the Label names of the columns to something Human readable. Changing the label for PART_NAME to Name is one example. We can use the arrow buttons to change the column order, too. On the Page tab, we can set the Page Title, Background, Title, and Field color. Click Next.
Figure 31. Design Input Form
The Designing Insert View window lets us customize the return form after a successfull database update. As you can see, I've entered a title and set the Background,Title, and Field color. Click Next.
Figure 32. Design Insert Form
Specifing Prefix allows us to name the following three files. See Figure 33. Click on Next.
Figure 33. Specifing the Prefix
Under the Project Navigator Perspective, drill down and locate the two HTML files created. Double-click on one of the files to launch the editor. You can make changes to the HTML file in the editor. See Figure 34.
Figure 34. Parts Input HTML
The WebSphere test environment
The first thing we need to do is configure a Server. Open the Server Prespective. From the menu bar, select Window -> Open Perspective -> Server. In the Server Configuration window, highlight Server, right-click, and select New -> Server -> Server Configuration. Pick the Test Environment and assign a Server name. Click Finish.
Figure 35. Configuring a Server
We need to edit the Server's configuration file to update the class path. Double-click on Test Environment Server to bring up the editor. In the center window, select the Environment Options tab. Click on the Add External Jars tab. Insert the classpath to the db2java.zip file here. See Figure 36. Close and save the file.
Figure 36. Classpath configuration
Find and highlight the PartsInput.html file. Right-click and select Run on Server. A browser will launch after the Server has started. This will take a few minutes. Check the Servers Console tab for status. Once the browser is up, enter values in the columns and click Submit. See Figure 37.
Figure 37. Parts Input Form
If the database has been updated successfully, it will return the following page. See Figure 38.
Figure 38. Updating the parts database
Let's look at the updated database to verify our work. Launch the DB2 Control Center and drill down to the database/table view. Select the parts table and double-click. See Figure 39.
Figure 39. Database View
Congratulations! You have created your first interactive database Web pages on Linux. We first started off by creating a database using the wizards in DB2. We then created a table and used the predefined columns feature in DB2 UDB V8.2 to create our colunms. We used WebSphere Studio Application to develop the dynamic Web pages. We created servlets, JSPs, and HTML pages. We tested our application by installing and configuring a test environment and actually updating our database.
- Download DB2 UDB.
- Download
WebSphere Studio Application Developer.
- IBM Scholars Web site University outreach programs.
- Installing DB2 on LInux
- Speed start your Linux app is a great resource for support, trial software, and tutorials.
