This tutorial describes how to create tables, stored procedures and other objects at DB2 server.

In this tutorial:

• Requirements
• General Information
• Creating Database Object
• Inserting Data into Table
• Additional Information

Requirements

In order to create database objects you have to connect to server. This process is described in details in the tutorial Logging Onto The Server.

General Information

Database objects are created using Data Definition Language (DDL), which is a part of SQL. The DDL statements can be executed on the server by an account that has necessary privileges.

There are two ways to manipulate a database. You can build SQL statements and run them within our components like DB2Command or DB2Script (design-time). Another way is to execute them from your code (run-time). In this walkthrough we will create a table via design-time and input some data into it using run-time.

Creating Database Object

1. Create the DB2Connection object in design-time ? drag and drop it from Toolbox on the Form Designer, assign its properties and connect to your database (tutorial Logging Onto The Server).

2. Put also the DB2Script component to your Form Designer. Click the tip on it and assign its Connection property to the DB2Connection object that you have created before.

3. Click on the ScriptText property and input the following DDL in the window of DB2Script Editor:

   CREATE TABLE DEPT(
     DeptNo INT NOT NULL,
     DName VARCHAR(14),
     Loc VARCHAR(13),
     PRIMARY KEY (DeptNo)
   );

4. This table is enough to demonstrate basic functionality. Now press the Execute script button to create the table in your database.

Inserting Data into Table

The following code fragment executes the query:

DB2Connection conn = new DB2Connection(@"user id=db2admin;server=db2:50000;database=SAMPLE");
DB2Command cmd = new DB2Command();
cmd.CommandText = "INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (10,'Accounting','New York')";
cmd.Connection = conn;
conn.Open();
try {
  int aff = cmd.ExecuteNonQuery();
  MessageBox.Show(aff + " rows were affected.");
}
catch {
  MessageBox.Show("Error encountered during INSERT operation.");
}
finally {
  conn.Close();
}

Dim conn As DB2Connection = New DB2Connection("user id=db2admin;server=db2:50000;database=SAMPLE")
Dim cmd As DB2Command = New DB2Command()
cmd.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (10,'Accounting','New York')"
cmd.Connection = conn
conn.Open()
Try
  Dim aff As Integer = cmd.ExecuteNonQuery()
  MessageBox.Show(aff & " rows were affected.")
Catch
  MessageBox.Show("Error encountered during INSERT operation.")
Finally
  conn.Close()
End Try

The sample first creates a connection with hardcoded connection string. Then it creates DB2Command object, assigns the query text and connection to the DB2Command instance. Connection is opened then. The ExecuteNonQuery() method of DB2Command runs SQL statement in the CommandText property and returns number of rows affected by the query. This method is not intended to run SELECT statements.

If the query is executed successfully you are notified about number of affected rows. If some error occurs you get the error message. The connection is closed anyway. It is recommended that you use try ... finally clauses to make sure the connections are closed properly.

After this, in the same way insert some more records to the Dept table. These records will be used in the latter tutorial Retrieving and Modifying Data. Execute the following SQL statements:

INSERT INTO dept (deptno, dname, loc) VALUES (20,'Sales','Dallas');
INSERT INTO dept (deptno, dname, loc) VALUES (30,'Sales2','Chicago');

Additional Information

Actually there are lots of ways to create tables on server. Any tool or component that is capable of running a SQL query, can be used to manage database objects. For example, DB2Command suits fine for creating objects one by one, while DB2Script is designed for executing series of DDL/DML statements. For information on DDL statements syntax refer to DB2 documentation.

See Also

Getting Started  | DB2Command Class  | DB2Script Class