DB2实验报告2.docx

1、DB2实验报告2吉林大学DB2实验报告班级： 姓名：学号：姓名学号实验项目2. Creating databases and data placement实验性质演示性实验 验证性实验 操作性实验 综合性实验实验地点机器编号指导教师实验时间年 月 日 时 分一、实验综述1. 实验目的及要求During this lab, you will create your database and your table spaces. Also in this lab, you will select information on table spaces from the system catalog

2、 (SYSCAT) views.At the end of the lab, students should be able to: Create a database Create a table space Execute a script file to create multiple table spaces Access the SYSCAT views containing table space information List table space information List container information 2. 实验设备、软件PC,windowsXP Pr

3、ofessional,DB29Express-c二、实验过程（实验步骤、记录、数据、分析）Section 1 - Creating the Database 1. Creating a database can be done with the DB2 command create database. Use the online help facility to display the DB2 command syntax for create database. Show your work below by printing screen.2. What information can

4、you specify on the create database command? The name of the database, the location of the database, an alias name, the codeset and territory for storing the data, a collating sequence, a default extent size, automatic storage, and table space information can be specified on the create database comma

5、nd.3. Based on what you learned in lecture, what table spaces are created when a database is created? SYSCATSPACE, USERSPACE1, and TEMPSPACE1 Depending on what you specify when you create the database, there may be a fourth tablespace created with a name of SYSTOOLSPACE. This is created if you speci

6、fy With Automatic Maintenance instead of Standard when you create the database.4. What type of table space, SMS or DMS, will your database use by default for these default table spaces? DMS table spaces for SYSCATSPACE and USERSPACE1, and SMS for TEMPSPACE1. Note that in DB2 UDB V8.2 and earlier the

7、 default table spaces are SMS.5. Before we create our MUSICDB database, remember what you learned in lecture about what some of the defaults will be. What is the default path that the database will be created on? What will be the default table space type (SMS or DMS) for the table spaces that will b

8、e created to house User Tables, Catalog Tables, and Temporary Tables? What are the default Extent and Prefetch sizes? What are the default Territory, Code Set and Collating Sequence values? The database will be created on the C:DB2 path by default. The default table space type for User Tables and Ca

9、talog Tables is DMS, and for Temporary Tables is SMS. The default Extent and Prefetch sizes are 32 4KB pages each. The default6. Create your database with a Database name of MUSICDB using the default settings. It will take a few minutes to create the database.Show your work below by printing screen

10、for the last step.7. You should now have your MUSICDB database created. The System Database Directory contains an entry for all databases known by this instance. Check the System Database Directory for an entry for the MUSICDB database by issuing the list db directory command. Show your work below b

11、y printing screen.8. As there is a lot of information returned from the select statement and the database manager configuration, it might be helpful to store the information in a file called myout to be able to examine it step by step. Execute the script again and save the output in a file named myo

12、ut.Show your work below by printing screen.8. What is the Database alias name and where did it come from?The Database alias name is YL1016. It defaulted to the database name since no alias was specified when the database was created.9. What does a Directory entry type of Indirect mean?A Directory en

13、try type of Indirect means the database is located on this system, and that the local database directory can be found here. You will see this if you issue the list database directory command from your telnet window.10. What does a Directory entry type of Remote mean?A Directory entry type of Remote

14、means the database is located on another system. You will see this if you issue the list database directory command from the Windows client.11. Its time to connect to your MUSICDB database. Check your current connection state with the get connection state command.12. What is the connection state?Sho

15、w your work below by printing screen.The connection state is Connectable and Unconnected.13. Connect to your MUSICDB database.14. Did you get connected to your MUSICDB database?Show your work below by printing screen.15. Check your connection state again. What does it show?Show your work below by pr

16、inting screen.16. Every database has its own Database Configuration file that contains information about the database and tuning parameters. Look at the Database Configuration file for your MUSICDB database.Show your work below by printing screen.17. Find the default values for two specific configur

17、ation parameters for your database, LOCKLIST and MAXLOCKS. Since there are a large number of configuration parameters, we can use grep to find the specific ones we want when using a local connection (the equivalent for DB2 on Windows is: . | find /i “lock”) the option “i” means case insensitive.Show

18、 your work below by printing screen.18.Values of some of the parameters can be changed. Update the following parameters and specify the values shown. Change maxlocks to 20 Change num_freqvalues to 12Show your work below by printing screen.19. When do these database configuration file changes take ef

19、fect?For these configuration parameters, the change takes effect immediately. Note that when you changed the value for MAXLOCKS, the default value for LOCKLIST is also changed from automatic to an appropriate manual value.20. Check to see if the Current and Delayed Values are the same for maxlocks a

20、nd num_freqvalues.Show your work below by printing screen.21. Repeat Step #17 above, and record here the new values of LOCKLIST and MAXLOCKS.22. Some default table spaces were created during creation of the database. List the table space information.Show your work below by printing screen.23. What a

21、re the table space names and what ID number is associated with the table space?SYSCATSPACE ID 0 TEMPSPACE1 ID 1 USERSPACE1 ID 2 SYSTOOLSPACE ID 3 Since you queried the database size info, the fourth table space named SYSTOOLSPACE was created for you. There are four cases that SYSTOOLSPACE will be au

22、tomatically created on an active database starting with V8.2: 1.DB Summary View of the database is displayed in the Control Center, or similar information is displayed from the command line by issuing: 2.Create a database with automatic maintenance. 3.Turn on automatic maintenance for a standard dat

23、abase (in the DB CFG file). 4.For a standard database without automatic maintenance, which has not been connected to through the Control Center, hmon (the health monitor) will create one when it starts evaluating health indicators (by default every 2 hrs). db2 CALL GET_DBSIZE_INFO(?, ?, ?, -1)Thus,

24、eventually, a V9.1 database will have a minimum of four table spaces. The automatic statistics collection and reorganization features available starting with DB2 UDB V8.2 store working data in tables in your database. These tables are created in the SYSTOOLSPACE table space. The SYSTOOLSPACE table s

25、pace is created automatically with default options. Storage requirements for these tables are proportional to the number of tables in the database and should be calculated as approximately 1 KB per table. If this is a significant size for your database, you may want to drop and re-create the table s

26、pace yourself and allocate storage appropriately. The automatic maintenance and health monitor tables in the table space are automatically re-created. Any history captured in those tables is lost when the table space is dropped.24. Table space container information can be displayed with the list tab

27、lespace containers command. Use the Help facility to show the DB2 command syntax.Show your work below by printing screen.25. Show the container information for table space ID 0. What type of container is this and where is it located?Show your work below by printing screen.26. List the names of the s

28、ystem catalog tables. What are these tables?Show your work below by printing screen.27. Get more detailed information for the table spaces and indicate which table spaces are set to automatic size increase.Show your work below by printing screen.28. Verify which default path containers are associate

29、d with the temporary table space and the default user table space.Show your work below by printing screen.29. Retrieve detailed container information from the Catalog tables. What is the container types for each container?Show your work below by printing screen.Section 2 - Creating Table Spaces1. Cr

30、eate your first table space. It should have the following characteristics: Table space name is DMS01 Table Space Type is Regular Buffer Pool should be IBMDEFAULTBP (which is also the default) Table Space management is DMS (also called high performance) Container size should be 1006 pages with 4 KB p

31、ages Container should be a File Container path and filename should be C:dmsdms01 (Windows) Table space extent size and prefetch size should be 4 Show your work below by printing screen for the last step.2. Verify your new table space (DMS01) by listing table spaces. Show your work below by printing

32、screen.3. On your Windows Database Server, a script file named crtblsp contains SQL statements to create your additional table spaces. 4. Execute the script file to create your remaining table spaces.Make sure you have the following options set before executing the script: Auto commit should be enable