Skip to main content

By clicking Submit, you agree to the developerWorks terms of use.

The first time you sign into developerWorks, a profile is created for you. Select information in your developerWorks profile is displayed to the public, but you may edit the information at any time. Your first name, last name (unless you choose to hide them), and display name will accompany the content that you post.

All information submitted is secure.

  • Close [x]

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

By clicking Submit, you agree to the developerWorks terms of use.

All information submitted is secure.

  • Close [x]

DB2 10.1 fundamentals certification exam 610 prep, Part 3: Working with databases and database objects

Gerald Kupris (gkupris@us.ibm.com), Solution Migration Specialist, IBM
Gerald Kupris
Gerald A. Kupris is a DB2 migration specialist working in the IBM Information Management Technology Ecosystems team, of which he has been a member since 2010. He has worked with numerous partners and customers, providing DB2 knowledge transfer, implementation guidance, and assistance converting from competitive databases to DB2.

Summary:  This tutorial shows you the basic steps and requirements to create and connect to a database in DB2® 10.1. This tutorial also introduces you to the objects that make up a DB2 database, as well as how to create and manipulate them. This tutorial is third of a series of six "DB2 10.1 fundamentals certification exam 610 prep" tutorials that will help prepare you for the DB2 10.1 fundamentals certification exam 610.

View more content in this series

Date:  18 Oct 2012
Level:  Intermediate PDF:  A4 and Letter (1082 KB | 21 pages)Get Adobe® Reader®

Comments:  

Creating, altering, and dropping objects in Data Studio

Now that you know how to create, connect to, and drop a database, and have an overview of the types of objects in DB2, you'll learn how to use Data Studio to interact with and create those objects. Once again, for the detailed creation commands to enter into the command line, use the links to the Information Center in the Resources section.

In this latest version of Data Studio, instead of deploying each change to database objects individually and immediately, now Data Studio utilizes change plans, which will let you deploy all changes at one time.

Creating database objects

In this example, we will create a new schema in the Sample database called TEMP. Once you connect to the Sample database, left-click on the Schema folder in the Administration Explorer view, which will load the list of all schemas in the List Editor. At the top of the List Editor, there is a green plus sign (+) that, when clicked, creates a new object with default values and opens the Properties view at the bottom of the screen with editable values. You may instead right-click on the Schema folder in the Administration Explorer view and select Create Schema.


Figure 12. Create Button in the List Editor
Screenshot shows close-up of the Create button in the List Editor

Figure 13. Creating an object from the Administration Explorer View
Screenshot shows close-up of the Menu from right-clicking on the schema folder in the Administration Explorer view

For our example, a new schema will appear with the default name of Schema2 and to the left of that name in the List Editor will be a delta symbol, denoting an undeployed change. Change the name to TEMP in the Properties view. Until these changes are deployed, you may reselect the undeployed object and continue to alter it. You will not see the List Editor reflect the name change until you click on it in the List Editor or click on the schema folder in the Administration Explorer view.


Figure 14. The new schema in the List Editor
Screenshot shows the newly created schema in the List Editor


Figure 15. Editing the schema name in the Properties view
Screenshot shows the Properties View, showing the change of the schema name from Schema2 to TEMP

To deploy this change to the database, you may click on the Change Plans folder in the Administration Explorer view, right-click on the change plan that is still in Pending status, then select Review and Deploy. Alternatively, you may click the Review and Deploy Changes button no matter what objects you may be reviewing in the List Editor. This button is third from the right on the second level of buttons at the top right of the List Editor.


Figure 16. Reviewing the Change Plan in the List Editor
Screenshot shows close-up of the Change Plans in the List Editor


Figure 17. The Review and Deploy Button
Screenshot shows close-up of the Review and Deploy button in the List Editor

Before deploying changes to the database, Data Studio will open the Review and Deploy window, which will show you the exact command that it is about to issue to the database. This is the same command that you would issue if you created the schema using the command line.


Figure 18. The Review and Deploy window
Screenshot shows the Review and Deploy Window

While our example created a schema, you should know that the same process can be used to create any database objects mentioned in Introduction to Databases and Database Objects, with the differences being in the options you would need to select and alter in the Properties View.


Altering and dropping database objects

Altering and dropping database objects is done in a similar format. Start by viewing the object in the Administration Explorer view. Right-click on the object you want to alter or drop and select the appropriate option. If you select Alter, the values in the Properties view become editable, just as they were when creating a new object. When all your changes are complete, you may then deploy them as you did when creating an object.


Altering table data

Data Studio can also be used to insert, update, and delete data from tables. To do so, click on the Tables folder in the Administration Explorer view and find the table you would like to alter in the list editor. Right-click on that table and select Edit Data. This will open the table data editor in another tab, showing the data is in the table's rows and columns. Individual cells may be updated, and whole rows may be created or deleted.


Figure 19. The Commit button
Screenshot shows close-up of the Commit button in the List Editor

As with altering to database objects, these changes do not take effect until committed. The button to commit your changes is in a similar same location to the deploy changes button from the List Editor, third from the right, at the top-right of the table data editor's tab. If you would not like to commit the changes you have made, you may click the refresh button, which is the right-most button of the group in the top-right corner of the tab.

5 of 8 | Previous | Next

Comments



static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=840784
TutorialTitle=DB2 10.1 fundamentals certification exam 610 prep, Part 3: Working with databases and database objects
publish-date=10182012
author1-email=gkupris@us.ibm.com
author1-email-cc=