1) Planning

IBM DB2 9

(Section -1)

Planning Presented By: By Ajay Shankar Khare Technical Consultant DB2 –IBM Academic Initiative WEBTEK LABS PVT LTD [email protected]

© 2008 IBM Corporation

IBM DB2 9

Section 1 - Planning (15%)  Knowledge of DB2 UDB products (client, server, etc.)  Knowledge of the features and functions provided by DB2 tools  Knowledge of database workloads (OLAP versus data warehousing)  Knowledge of non-relational data concepts (extenders)  Knowledge of XML data implications (non-shredding) 2

IBM DB2 9

3

IBM DB2 9

DB2 9 Product Family  DB2 Everyplace  DB2 Express  DB2 Express-C  DB2 Personal Edition  DB2 Workgroup Server Edition  DB2 Enterprise Server Edition  DB2 Data Warehouse Edition  DB2 Developer's Editions  DB2 for i5/OS  DB2 for z/OS 4

IBM DB2 9

DB2 9 Product Family

5

IBM DB2 9

6

IBM DB2 9

About Family  The code on the Linux, UNIX, and Windows (LUW) platforms is about 90% common, with 10% of the code on each operating system reserved for tight integration into the underlying OS.  There are two other members of the DB2 family that are - DB2 for System i and DB2 for System z with 95% portable SQL.

7

IBM DB2 9

DB2 Everyplace  It is a small-footprint (200-350 KB) relational database and a high-performance data synchronization solution.  Manages synchronization of data from back-end data sources such as DB2 9, Informix, Oracle, Sybase, and MS SQL Server.  It runs (with multithreaded support) on a wide variety of handheld devices, such as: Palm OS, Microsoft Windows Mobile Edition, Symbian, J2ME devices like RIM's Blackberry pager 8

IBM DB2 9

Express Editions  It is an entry-level data server that is designed to be used on microcomputers that have up to two CPUs (a dual-core processor is treated as a single CPU), have up to 4 GB of memory.  Supports Linux, Unix, or Windows.

9

IBM DB2 9

DB2 Express-C  It is a no-charge entry-level data server that is designed to be used on microcomputers that have up to two CPUs, have up to 4 GB of memory, and are running a supported version of Linux or Windows.  Where pureXML is available as an add-on feature for DB2 Express, it is included with DB2 Express-C but missed many other features of Express Edition. 10

IBM DB2 9

Why DB2 Express-C ? 1. No Artificial Data Server Limitations Production ready, same superior database features as DB2 Express

2. Innovative Technology Viper--Industry first relational and XML data server

3. Application Development Flexibility Choice of programming languages and tools 4. Scalable on Demand: no application or database changes required for other DB2 Editions 11

IBM DB2 9

DB2 Personal Edition  It is a single-user, full-function relational database management system that is ideal for desktop or laptopbased deployments.  Remote clients cannot access databases that are under DB2 Personal Edition's control, but PCs running DB2 Personal Edition can act as remote clients and access data stored on other DB2 servers.  Supports Linux & Windows. 12

IBM DB2 9

DB2 Workgroup Server Edition  It is a multi-user, full-function client/server database management system designed to be used on microcomputers that have up to four CPUs, have up to 16 GB of memory.  Its an ideal data server for SMB environments that comprise a small number of internal users.  Supports Enterprise Linux & Windows, AIX, HP-UX, Solaris and Novell. 13

IBM DB2 9

DB2 Enterprise Server Edition  It is the ideal foundation for building multi-terabyte data warehouses, high-availability, high-volume OLTP systems, or Web-based Business Intelligence (BI) solutions.  It is designed to be used on any size of server (from one to hundreds of CPUs) that is running Enterprise Linux & Windows, AIX, HP-UX, Solaris or Novell operating systems.  This edition includes all of the functionality found in DB2 Workgroup Edition, plus features that are needed to handle high user loads and provide 24x7x365 availability.  It comes packaged with DB2 Connect that allows it to participate in heterogeneous networks using the Distributed Relational Database Architecture (DRDA) protocol. 14

IBM DB2 9

DB2 Data Warehouse Edition  It is designed for today's data center environments, where OLTP and decision support are merged into integrated information management systems.  The core engine for DB2 Data Warehouse Edition is DB2 Enterprise Server Edition and the DB2 Data Partitioning Feature.

15

IBM DB2 9

DB2 Developer's Editions  Using these editions developers can design, build, or prototype applications (using JDBC, PHP, .Net, Python, ODBC, SQLJ and Embedded SQL etc.) that interact with DB2 9 databases. It cannot be used for production systems.  DB2 Personal Developer’s Edition (PDE) contains DB2 Personal Edition, DB2 Extenders, DB2 Connect Personal Edition, and a SDK that can be used to develop applications.  DB2 Universal Developer's Edition (UDE) is designed for any of the DB2 client or server platforms available. Its Package contains PDE + DB2 Everyplace, Express, Workgroup Server and Enterprise Server Edition for each operating system supported.  DB2 Enterprise Developer's Edition package contains UDE + Informix Dynamic Server (IDS), Cloudscape, and DB2 Connect Unlimited Edition for zSeries for each OS supported. 16

IBM DB2 9

DB2 for i5/OS and z/OS  DB2 for i5/OS is an advanced, 64-bit relational database management system that leverages the On-Demand capabilities of System i, such as Dynamic Logical Partitioning to respond quickly to changing workloads in order to ensure business continuity in a dynamic environment.  Unlike other DB2 editions, DB2 for i5/OS is built directly into the operating system.  DB2 for z/OS is a multi-user, full-function database management system that has been designed specifically for z/OS, IBM's flagship mainframe operating system. 17

IBM DB2 9

DB2 9 Clients

 DB2 9 Runtime Client The bestalloption if your only Includes the functions found This 2 MB fully redistributable New in DB2are 9, this client requirements to enable  DB2 9 Client in the DB2 Runtime Client plus client provides JDBC and performs similar functions to applications to access DB2 9 SQLJ applications access to of functions for client-server the JCC client, but instead data servers. Theydatabase provide the DB2 data servers without  Java Common Client configuration, (JCC) supporting Java-based access APIs necessary to perform this installing and maintaining administration, and application to a DB2 data server it'sDB2 used task, but this client comes with client code. development through a set of for ODBC applications.  DB2 9 Client Lite norich management tools. graphical tools.

18

IBM DB2 9

Add-on features  Add-on features are available for an additional licensing fee. Some of them are – - pureXML - High Availability - Workload Management - Performance Optimization - DB2 Homogenous Federation Feature - Storage Optimization - Advanced Access Control 19

IBM DB2 9

DB2 9 Connect  DB2 Connect provides a robust, highly scalable communications infrastructure for connecting Linux, UNIX, Windows, and mobile applications to data stored in z/OS (mainframe) and i5/OS (minicomputer) databases.  DB2 Connect is an add-on product that must be purchased separately unless DB2 Enterprise Server Edition, which provides a limited-use version, is installed. 20

IBM DB2 9

Versions of DB2 Connect  DB2 Connect Personal Edition: Intended to be used by a single user on a single workstation who needs connection to any number of mainframe and/or minicomputer DB servers.  DB2 Connect Enterprise Edition: Intended to be used by multiple users on multiple workstations who need connection to any number of mainframe and/or minicomputer database servers. User based licensing.  DB2 Connect Application Server Edition: same as Enterprise Edition, however, license based on the number of processors being used by Web or application servers  DB2 Connect Unlimited Edition: same as Enterprise Edition, however, license charges are based on Millions of Service Units (MSUs) per hour. 21

IBM DB2 9

Example Q: A client application on z/OS must access a DB2 database on a Solaris Server. At a minimum, which of the following products must be installed on the Solaris workstation?  A. DB2 Connect Enterprise Edition  B. DB2 Workgroup Server Edition  C. DB2 Workgroup Server Edition and DB2 Connect Enterprise Edition  D. DB2 Enterprise Server Edition and DB2 Connect Enterprise Edition 22

IBM DB2 9

Example Q: Which of the following is the lowest cost DB2 product that can be legally installed on an HP-UX server?  A. DB2 Express-C  B. DB2 Express  C. DB2 Personal Edition  D. DB2 Enterprise Server Edition

23

IBM DB2 9

Example Q: Which of the following is the lowest cost DB2 product that can be legally installed on an AIX server?  A. DB2 Express Edition  B. DB2 Personal Edition  C. DB2 Workgroup Server Edition  D. DB2 Enterprise Server Edition

24

IBM DB2 9

Example Which of the following products must be installed on an AIX server in order to build an application for AIX that will access a DB2 for z/OS database?  A. DB2 Enterprise Server Edition  B. DB2 Personal Developer's Edition  C. DB2 Universal Developer's Edition  D. DB2 Universal Database Enterprise Edition and DB2 Connect Enterprise Edition 25

IBM DB2 9

Example Which of the following is the lowest cost DB2 product that can be legally installed on a Linux server that has 6 CPUs?  A. DB2 Express Edition  B. DB2 Personal Edition  C. DB2 Workgroup Server Edition  D. DB2 Enterprise Server Edition

26

IBM DB2 9

DB2 9 Extenders  Extenders extend the basic functionality and capabilities of a DB2 database by providing a rich set of built-in data types, along with a set of functions that are designed to work with each data type provided.  They are: - Audio, Video, and Image (AVI) Extender - Text Extender - Net Search Extender - XML Extender - Spatial Extender - Geodetic Extender 27

IBM DB2 9

 AVI Extender - contains a set of user-defined data types and functions that allow a DB2 database to store and manipulate nontraditional data such as audio clips, movies, and pictures.  Text Extender – Its powerful linguistic search and text-mining capability allows users to construct queries that will search through any kind of text document (in DB or File system)  Net Search Extender - well suited for high-end ebusiness applications where search performance on large indexes can be critical and the ability to scale the processing of concurrent queries is needed. 28

IBM DB2 9

 XML Extender - contains a set of user-defined data types and functions that can be used to store XML documents in a database (as character data or in external files) and to manipulate them, regardless of where they are stored.  Spatial Extender - With its capability, you can generate, analyze, and exploit spatial information about geographic features, such as the locations of office buildings or the size of a flood zone, and present it in a 3D format.  Geodetic Extender - It treats the Earth like a globe rather than a flat map thus making it easier to develop applications for business intelligence and e-government that require geographical location analysis, uses a latitude/longitude coordinate system. 29

IBM DB2 9

30

IBM DB2 9

First Steps With First Steps, you can create sample databases, launch the DB2 tools for management and connectivity, work with the Business Intelligence tutorials, view the product library, find more DB2 resources on the Web, and take a quick tour of all the new features in DB2 Version 9.5.

31

IBM DB2 9

Control Center  Central management tool for DB2 servers  Used to: -

-

-

-

32

Add DB2 systems, local and remote instances and databases to the object tree for management. Manage database objects (databases, tablespaces, tables, views, indexes, triggers, and schemas) Manage data. You can load, import, or export data, reorganize data, and collect statistics. Launch other DB2 centers. Perform preventive maintenance by backing up and restoring databases or tablespaces.

IBM DB2 9

db2cc

33

IBM DB2 9

Command Center  Used to: Execute SQL Statements interactively - Execute SQL using SQL Assist Wizard - Scripts can be saved using the Save Script option - View Query Access Plans - Delete, update, export and view the result set  Can be invoked by: - Start  Programs IBM DB2  Command Line Tools  Command Center -

34

IBM DB2 9

35

IBM DB2 9

Configuration Assistant 

Used to connectivity databases.

establish to remote

 CA lets you maintain a list of databases to which your applications can connect, manage, and administer.  set DB2 registry parameters  Catalog Databases

36

IBM DB2 9

Configuration Assistant

37

IBM DB2 9

Task Center  Used to create, schedule and run tasks either immediately or according to a schedule, and to notify people about the status of completed tasks, like - DB2 scripts that contain DB2 commands - OS scripts containing OS commands - Grouping tasks, containing other tasks  E-mail notifications can be set

38

IBM DB2 9

Task Center

db2tc

39

IBM DB2 9

Health Center  Used to monitor the state of the DB2 environment and make any necessary changes to it.  Management by exception - Limit the need of time-consuming, prosaic tasks - Alert the DBA for a possible health issue, and allow time to react  Lower the skills requirement for monitoring, troubleshooting and problem resolution  DB2 comes with a set of predefined threshold values, which you can customize.

40

IBM DB2 9

Health Center

db2hc

41

IBM DB2 9

Journal  Displays the status of the jobs and logs their results  Used to: - View Job histories - Monitor running and pending DB2 jobs - Review Job results  To start: - Start  Programs  IBM DB2  General Administration Tools  Journal 42

IBM DB2 9

Journal

db2journal

43

IBM DB2 9

DB2 Information Center

db2ic

44

IBM DB2 9

Visual Explain  Visual Explain lets you view the access plan for an explained SQL statement as a graph.  It lets you dynamically explain an SQL statement and view the resulting access plan graph.

45

IBM DB2 9

SQL Assistance

It’s a graphical tool you can use to build SELECT, INSERT, UPDATE, and DELETE statements.

46

IBM DB2 9

Command Line Processor

47

IBM DB2 9

Command Window

db2cw

48

IBM DB2 9

Developer Workbench  Develop and debug an SQL stored procedure  Develop and debug a user-defined function  Develop and run XML queries

49

IBM DB2 9

Memory Visualizer

50

IBM DB2 9

Storage Management

51

IBM DB2 9

Example Q: Which two of the following allow you to perform administrative tasks against database objects?  A. Control Center  B. Journal  C. Command Line Processor  D. Task Center  E. Health Center

52

IBM DB2 9

Data Warehousing  Systems that contain operational data -- the data that runs the daily transactions of a business -- contain information that business analysts can use to better understand how the business is operating.  In data warehousing, you create stores of informational data -- data that is extracted from operational data and then transformed and cleansed for end-user decision making.  End users can query the separate database (the warehouse) without affecting the operational databases.  DB2 is built for business intelligence. Not only is DB2 the world's most scalable database, but it also has a robust set of business intelligence functions. 53

IBM DB2 9

54

IBM DB2 9

On-Line Analytical Processing (OLAP) "What is my profitability for the third quarter across the southeast region of India for my focus products?” The above question requires multiple perspectives on data, such as time, regions, Countries and products. Q

These perspectives are called dimensions.

OLAP server processes multidimensional requests that calculate, consolidate, and retrieve information from a multidimensional database, a relational database, or both. 55

IBM DB2 9

56

IBM DB2 9

1. Which of the following is the lowest cost DB2 product that can be legally installed on a Windows server that has 2 CPUs? A. DB2 Everyplace B. DB2 Express Edition C. DB2 Workgroup Server Edition D. DB2 Enterprise Server Edition

57

IBM DB2 9

2. Which of the following is the lowest cost DB2 product that can be legally installed on an HP-UX server? A. DB2 Express-C B. DB2 Express C. DB2 Personal Edition D. DB2 Enterprise Server Edition 58

IBM DB2 9

3.Which of the following DB2 products can only be installed on a System i server? A. DB2 for z/OS B. DB2 for i5/OS C. DB2 Data Warehouse Edition D. DB2 Enterprise Server Edition

59

IBM DB2 9

4. What is the purpose of the Design Advisor? A. To analyze workloads and make recommendations for indexes and MQTs B. To present a graphical representation of a data access plan and recommend design changes that will improve performance C. To replicate data between a DB2 database and another relational database D. To configure clients so they can access databases stored on remote servers 60

IBM DB2 9

5. Which of the following DB2 tools allows a user to set DB2 registry parameters? A. Task Center B. Visual Explain C. Configuration Assistant D. Satellite Administration Center 61

IBM DB2 9

6. What is the SQL Performance Monitor used for?  A. To examine the health of a DB2 Database Manager instance  B. To visually construct complex DML statements and examine the results of their execution  C. To schedule tasks, run tasks, and send notifications about completed task s to other users  D. To analyze database operations performed against a DB2 database

62

IBM DB2 9

7. Which of the following tasks can NOT be performed using the Developer Workbench? A. Develop and debug an SQL stored procedure B. Develop and debug a user-defined data type C. Develop and debug a user-defined function D. Develop and run XML queries

63

IBM DB2 9

8. Which of the following tools can be used to automate table reorganization operations? A. Control Center B. Command Center C. Command Line Processor D. Task Center 64

IBM DB2 9

9. Which of the following can be viewed with the Journal?  A. Historical information about tasks, database changes, messages, and notifications  B. Information about licenses associated with each DB2 9 product installed on a particular system  C. Graphical representations of data access plans chosen for SQL statements  D. Warning and alarm thresholds for database indicators 65

IBM DB2 9

10. Which of the following is NOT a characteristic of an OLTP database? A. Granular transactions B. Current data C. Optimized for queries D. Frequent updates 66

IBM DB2 9

11. Which of the following is NOT a characteristic of a data warehouse? A. Summarized queries that perform aggregations and joins B. Heterogeneous data sources C. Voluminous historical data D. Sub-second response time 67

IBM DB2 9

12. Which of the following products is used to shred extensible markup language documents? A. DB2 AVI Extender B. DB2 Text Extender C. DB2 XML Extender D. DB2 Spatial Extender 68

IBM DB2 9

13. Which of the following best describes the difference between the DB2 Spatial Extender and the DB2 Geodetic Extender? A. The DB2 Spatial Extender uses a latitude-longitude coordinate system; the DB2 Geodetic Extender uses a planar, x- and ycoordinate system B. The DB2 Geodetic Extender is used to describe points, lines, and polygons; the DB2 Spatial Extender is used to find area, endpoints, and intersects C. The DB2 Spatial Extender treats the world as a flat map; the DB2 Geodetic Extender treats the world as a round globe D. The DB2 Geodetic Extender can be used to manage information like the locations of office buildings or the size of a flood zone; the DB2 Spatial Extender can be used for calculations and visualizations in disciplines like military control and asset management, meteorology and oceanography 69

IBM DB2 9

14. Which of the following is true about XML columns?  A. XML columns are used to store XML documents as a hierarchical set of entities  B. Only XQuery can be used to retrieve an XML document from an XML column  C. XML columns must be altered to accommodate additional parent/child relationships if they are used in referential constraints  D. In order to access any portion of an XML document stored in an XML column, the entire document must be retrieved 70

IBM DB2 9

15. Which of the following describes how DB2 9 stores an XML document if the XML Extender is not used?  A. CLOB  B. BLOB  C. Hierarchically  D. Rows and columns

71

IBM DB2 9

15. Which of the following is the major difference between relational data and XML data?  A. Relational data is self-describing; XML data is not  B. Relational data has inherent ordering; XML data does not  C. Relational data must be tabular; XML data does not have to be tabular  D. Relational data is comprised of entities; XML data is comprised of numbers, characters, and dates 72

IBM DB2 9

16. Which of the following tools can be used to automate table reorganization operations?  A. Control Center  B. Command Center  C. Command Line Processor  D. Task Center

73

IBM DB2 9

Grazie

Hebrew

Italian

Gracias

Spanish

Russian

Arabic

Traditional Chinese

Obrigado

Thank You

Portuguese

Merci

French

Danke

German

English

Thai Simplified Chinese

Japanese

74

Tamil

Korean