Case Study 1
You recently installed DB2 Express-C on your Windows laptop. During the installation, the DB2 instance was created. Now you want to start using DB2 by creating a database. Because you are new to DB2, you decide to use the DB2 First Steps tool (refer to Figure 4.41).
You click the Create Sample Database button from the First Steps. Next you choose the option XML and SQL objects and data, which creates a Unicode database, and click OK. Wait for a few minutes for the SAMPLE database to be created. Alternatively, you can create the SAMPLE database from a Windows command prompt or a Linux/UNIX shell using this command:
db2sampl –sql –xml
After the database is created, you launch the DB2 Command Line Processor by choosing Start > Programs > IBM DB2 > DB2COPY1 > Command Line Processor. You should see a prompt like this: db2 =>, which is the DB2 CLP prompt. From the prompt, connect to the SAMPLE database.
connect to sample
You want to examine the table spaces created by the tool; issue the following command:
Five table spaces are created by default. You wonder what sample user tables were created. The following command lists all tables defined under the schema of the user ID currently connected to the database.
To list tables of a specific schema (xyz, for example), use this command:
list tables for schema xyz
Issue quit to exit the CLP interactive mode. Sample codes and scripts are installed with the installation that works with the SAMPLE database. Run the file tbread.db2 that contains SQL statements to query the sample tables. Issue the following in the CLP.
db2 –tvf ..\samples\clp\tbread.db2
You now want to get familiar with the CLPPlus. Issue the command clpplus to start the command line interface. But first, connect to the SAMPLE database.
Load a sample file into the SQL buffer with the GET command.
Run the script with the RUN command. You get an error complaining about the end label “@”. Use the EDIT command to remove the @ sign at the end of the file. Save and close the editor.
Run the script again. This time it should work, and a stored procedure is successfully created. Call the stored procedure with the following command:
Congratulations! You have successfully created a Unicode database, queried few tables, and executed SQL scripts using the command-line tools CLP and CLPlus.