db2经典实验内容，希望大家赶快动手！（第2部）

borlanzy 发表于 2007-11-27 10:14

db2经典实验内容，希望大家赶快动手！（第2部）

DB2 Migration Workshop
LAB 1 – Working with Instances and Databases (Solution)

Expected duration: 45 mins

This lab will help you solidify concepts of instances and databases. It is to be completed BEFORE the presentation “DB2 Instances and Databases”.

The following commands are demonstrated:

db2icrt        ACTIVATE DB        FORCE APPLICATION
db2idrop        DEACTIVATE DB        GET CONNECTION STATE
db2ilist        CONNECT        LIST APPLICATIONS
db2start        CONNECT RESET        LIST DB DIRECTORY
db2stop        CREATE DATABASE        LIST TABLES
db2admin start        DROP DATABASE        
db2admin stop        DESCRIBE TABLE        

Prerequisites: 
·        DB2 Version 8 Server has been installed

Highly Recommended: Before each step in this lab, locate the demonstrated command in the CLP quick reference and mark it with an asterix (*). This will help you get familiar with the DB2 CLP cheat sheet.

Note: On Linux/UNIX, you can switch users (su) without logging out by using:

        su –                          .. to switch to root user (dash is important!)
        su –  [userid]                .. to switch to non-root user (dash is important!)
        exit                        .. to exit switch back


1.        Windows: Launch the DB2 Command Window. 

Start > Programs > IBM DB2 > Command Line Tools > Command Window
(or simply, db2cmd)

Linux: Login to the operating system as user db2inst1

2.        When DB2 is installed, no database exists. Create the SAMPLE database provided by DB2.

db2sampl

3.        Verify that a database now exists. Show the contents of the database directory:

db2 list db directory

Fill in the following information from the database directory:

Database alias                 SAMPLE
Directory entry type        Indirect

4.        DB2 does not have database users. Instead, DB2 uses userIDs and passwords defined at the operating system (or network authentication facilities) to authenticate users. Connect to the SAMPLE database using the userID logged into the operating system.

db2 connect to sample

5.        See all connections that currently exist for the current instance

db2 list applications

Fill in the following information:

Application name:                db2bp.exe
Application handle:        2 (your answer may vary)
Database name:                SAMPLE

6.        Disconnect from the database:

db2 connect reset

7.        Connect to the SAMPLE database using a specific userID and password.

db2 connect to sample user <userid>  using <password>

8.        View the tables that currently exist in the SAMPLE database

db2 list tables
or
db2 list tables for all

9.        From the output of list tables, you should see that a table called EMPLOYEE exists. Find out the structure of EMPLOYEE

db2 describe table employee

What is the maximum length of JOB column?        8 characters
What is the data type for the Salary Column?        DECIMAL(9,2)

The above two questions are indented to show how data type lengths from the output of DESCRIBE TABLE are interpreted differently for numeric and character types.

10.        Attempt to stop the instance. You should get an error because connections still exist.

db2stop

What is the SQLCODE associated with the error message?         SQL1025N

11.        To stop the instance, we have to get rid of the current connections. In this step, forcibly disconnect all applications.

db2 force applications all
db2stop

note: you can also force applications by application handle using:
        force application (<handle>)

12.        By using FORCE APPLICATION, you have terminated your own connection. Attempt to SELECT from the employee table.

db2 “select * from employee”

You should get the error code SQL1224N. Check your connection state.

db2 get connection state

13.        To solidify the concept of instances and databases, we’ll create a second instance and create a database in it. Create a new instance:

Windows: You need to be a user with administrative privileges

        db2icrt db2inst2 –u db2admin,<db2admin’s password>

Note: There is no space after the comma

Linux: Switch to root user (su - ). You’ll also need to create a user to “own” the instance

        useradd db2inst2
        passwd db2inst2
        /opt/IBM/db2/V8.1/instance/db2icrt –u db2fenc1 db2inst2

14.        Verify that the new instance exists

db2ilist

15.        Your current instance is DB2 (on windows) or db2inst1 (on Linux). Change your context to the new instance.

Windows: set the DB2INSTANCE environment variable to db2inst2

set DB2INSTANCE=db2inst2

Linux: Switch to user db2inst2. 

su – db2inst2


16.        Verify that your current instance is db2inst2

db2 get instance

17.        By default, a newly created instance will not be started. Start the new instance

db2start

18.        The default database configuration is not optimal for any significant use. Use DB2’s AUTOCONFIGURE feature to tune the database:

db2 connect to test
db2 autoconfigure using mem_percent 25 workload_type simple tpm 10  is_populated no num_local_apps 10 num_remote_apps 10 apply db and dbm > autoconfig.txt

Use a text editor to review output in autoconfig.txt

Stop and restart the instance for all changes to take effect:
        db2 terminate
        db2stop
        db2start

19.        Create a new database called TEST in the db2inst2 instance

db2 create database TEST

Note: you can also use “db” in place of “database”

Take a look at the database directory again. How many entries do you see?  one
What happened to the SAMPLE database we created earlier?

After creating a second instance and switching to it in step 16, we changed to a totally distinct database server context. The SAMPLE database exists in the first instance only. Therefore, after creating the TEST database, there still only the TEST database is listed in the database directory


20.        Once a database created, database global memory (most of which is buffer pool memory) is not allocated until the first connection or an explicit activation. If a database is not explicitly activated, the first connection incurs the full cost of resource allocation for the database and all resources are released when the last application disconnects. To prevent this, for production databases, we recommend that you explicitly activate the database. In this step, practice database activation and deactivation.

Optional: launch windows task manager (or vmstat on Linux) to watch global memory allocation in action.

db2 activate db test
db2 deactivate db test

21.        To clean up, drop the TEST database and stop the instance.

db2 drop database test
db2stop

Note: you can use “db” in place of “database”

22.        Drop the instance

Windows: You must a local administrator user
db2idrop db2inst2

Linux: switch to root user
/opt/IBM/db2/V8.1/instance/db2idrop db2inst2
userdel –r db2inst2

23.        The DB2 Admin server is a background process the facilitates remote database and instance administration and automatic task scheduling. Just for fun, stop and start the admin server.

Linux: You may have to source the db2admin user’s dasprofile file first:

        . /home/dasusr1/das/dasprofile

db2admin stop
db2admin start



////////**************************************//////
各位朋友，本人拥有以下正版软件：
(1)IBM Websphere application Server 企业版(可以支持集群和多CPU) ver：5.1   /v6.0
for Windows2000、2003,AIX,HP-UX and Solaris 等各个操作系统
(2)IBM Websphere application Server - Express (不支持集群、支持2CPU)  ver：6.0  
for Windows2000、2003,AIX,HP-UX and Solaris 等各个操作系统
(3) DB2通用数据库企业服务器版    ver：8.2/v9.0(可以支持集群和多CPU)
for Windows2000、2003,AIX,HP-UX and Solaris 等各个操作系统
如果有意请和我联系，以上每套的价格是220元，我的联系方式是：[email]borlanzy@126.com[/email]，我提供国内的刻盘和邮寄。
////////**************************************//////

weili163 发表于 2007-12-5 15:12

多谢老大共享！！！！

bajie2004 发表于 2008-3-20 23:03

回复 #2 weili163 的帖子

多谢老大共享 

页:  [1]

查看完整版本:  db2经典实验内容，希望大家赶快动手！（第2部）