Session: G10 and G1 (Double session)
IBM Data Studio: The Toolset of the Future Title… Here Today!
Paul Zikopoulos, BA, MBA IBM Canada
15th October 2008 • (1) 13:30-4:30 (2) 14:45-15:45 Platform: Tools and Utilities
1
Presenter’s Biography
Paul C. Zikopoulos, BA, MBA, is an awardwinning writer and speaker with IBM. In his current role, he is the Program Director of the DB2 Technical Evangelist team. He has more than ten years of experience with DB2 UDB and has written over sixty magazine articles and several books about it. Paul has co-authored the books: DB2 9 New Features, DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë – his new daughter. You can reach him at: paulz_ibm@msn.com.
2
2
The Application Development Lifecycle
The People. The Framework. The Tasks
Application Developer
Database Developer
Database Administrator
Manage
•Database administration •Data management •Change management •Recovery management •Storage management •Performance management
Develop
•Coding •Debugging •Teaming •Testing •Tuning
Design
•Data Modeling •Process Modeling •Business Modeling
Govern
•Security access •Security analysis •Data auditing/encryption •Data archiving/masking
Business Analyst
Database Architect
Security Administrator 3
3
The Problem Today
User Interfaces Microsoft Web Eclipse Java Command Line Design
Rational Data Architect
Develop
Developer Workbench Table Editor Web Query Tool Data Quant
Deploy
Change Manager Expert Migration Tool Kit Control Center Developer Workbench
Manage
Performance Expert Recovery Expert Change Manager Expert Data Archive Expert Test Database Generator High Performance Unload DS Admin Console DSAC Control Center Developer Workbench DWE Studio & Admin
Govern
Test Database Generator Data Archive Expert Audit Mgmt Expert Encryption Expert DB Security Analyst Control Center Developer Workbench
Tools
Control Center Test Database Generator Visual Studio .Net Plug-in
DB2 for LUW
DB2 for z/OS
DB2 for i5/OS
IDS
Non-IBM Non-
4
4
Toolset Q&A
• • • • • • • • I need to create a logical data model • You need to use Rational Data Architect I need to build and debug a SQL stored procedure • You need to use Developer Workbench I need to create a table for DB2 for LUW • You need to use Control Center I need to execute some commands on DB2 for z/OS • You need to use Command Editor I need to catalog my DB2 data servers • You need to use Configuration Assistant I need to change a few configuration parameters • You need to use Control Center I need to check the health of my DB2 for LUW data server • You need to use Health Center or Performance Expert I need to administer my DB2 for LUW Replication configurations • You need to use Replication Center
5
5
Why IBM Data Studio?
• Increases productivity -- throughout data life cycle • Integrated data management environment for every role - Developers, DBAs, Data Architects • Promote collaboration between Developers and DBAs to optimize performance • Accelerate Java development productivity with pureQuery technology • Simplify development of next generation pureXML applications • Monitor data server operation & performance anywhere, anytime from a Web browser • Shortens the learning curve • Learn once, use with all your IBM data servers (DB2 and IDS) • Easy-to-use and intuitive user interface • Compatible with Rational Software Development Platform • Extensible with Eclipse plug-ins to customize your development environment • Accelerates SOA implementation • Develop and publish data as a web service without programming • Simple to use and ready for Info 2.0
6
6
The IBM Data Studio
A New Era of Tooling for IBM Data Servers
• New evolution of the DB2 Developer’s Workbench No longer just for developers • Key new enhancements Improved database connection management (includes Informix IDS support) Enhanced SQL editor, routine editors, and table editor New administrative tasks in the Database Explorer pane New IBM Data Web Services framework for easier SOA enablement pureQuery development tooling for quick development for relational and in-memory Java collection development using standard SQL Installation via the IBM Installation Manager for all install, updates, and maintenance
7
7
What is the IBM Data Studio?
8
8
Users of the IBM Data Studio
9
9
IBM Data Studio
Free complimentary version Available Q4 2007 Support for DB2 on all platforms and IDS
DB2 for LUW
Physical Data Modeling Data Distribution Viewer Integrated Query Editor SQL Builder/Debugger XML Editor XML Schema Editor pureQuery for Java Data Web Services Object Management Data Management Health and Availability Monitoring Security Access Controls Project Management Source Control
DB2 for z/OS
Physical Data Modeling Data Distribution Viewer Integrated Query Editor SQL Builder XML Editor XML Schema Editor Data Web Services Object Management Data Management Security Access Controls Project Management Source Control
DB2 for i5/OS
Physical Data Modeling Data Distribution Viewer Integrated Query Editor SQL Builder Data Web Services Object Management Data Management Security Access Controls Project Management Source Control
IDS
Physical Data Modeling Data Distribution Viewer Integrated Query Editor SQL Builder Data Web Services Object Management Data Management Security Access Controls Project Management Source Control
10
10
IBM Data Server Client
• Single client for access to IBM Data Servers (Informix IDS and DB2) • One deploy: IBM data server • Still need DB2 Connect for DB2 for i5/OS and DB2 for z/OS connections
11
11
IBM Data Studio
A comprehensive and productive work environment
12
12
A Peek at Database Management Stuff
13
13
Introducing the Data Studio Administration Console
• What is it? • A Web based interface to perform operational database administration tasks • Immediate access to critical data server information and functions from anywhere, anytime What’s available in the first release • Health and availability monitoring • DB2 for LUW V9.5 • Problem determination and recommendations • 72 Hours of History with the ability to monitor up to 100 data servers • Q Replication monitoring and administration What will likely (hint… hint…) be available in the future? • Support for all IBM data servers – IDS, DB2 on all platforms • Home for commonly performed operational database management functions • Performance Monitoring and Tuning • Configuration Management • Storage Management • Security Management • Advanced Deployment Capabilities
14
•
•
14
Data Studio Administration Console Design Goals
• Web-based console to address short-comings of previous generation CC tools: Lightweight: Goal of less than 100 MB disk footprint, equally diminutive memory footprint Fast and responsive: Web 2.0 UI: Ajax means you never wait as web pages update asynchronously Task-based: Supports key end-to-end admin scenarios directly in task-driven console Simple but powerful: Do the simple things easily & quickly, make the complex possible for everybody Scalable: Big (100s of databases) or small, we take ‘em all: visualizations, multi-db actions Easy deployability and upgradeability: Bundled with data servers & downloadable, upgrade console server not clients
15
15
Data Studio Administration Console vs. Control Center
16
16
Quick and Easy Problem Determination
Heatchart – Overall Health Status Dashboard – Adhoc Investigation
Where are the most important hotspots that need my attention? Alert List – Historical Investigation
Administrator
Something doesn‘t seem quite right. I wonder what‘s happening? Recommendations – Root Cause Analysis
„What happened when I was out for lunch? ... Away for weekend?
Guide me to the root cause and help me fix it properly; I need to know all the revelant info to make the best decision.
17
17
Data Studio Administration Console Architecture
Browser tier
Console server tier
Data server tier
18
18
Monitoring Architecture
DB2 for z/OS
Monitor Agent
DB2 for LUW
Monitor Agent
DB2 for i5/OS
Monitor Agent
IDS
Administration via SQL
Monitor Agent
Managed Resources
Mid-Tier Server Monitor Server Data Studio Server
Web Administration Console
19
19
The Heat Chart
Overall Health Status
Locking Application causing lock escalation Table space inaccessible due to quiesced state Application waiting indefinitely for lock held by in-doubt transaction Database Storage Table space inaccessible because it is offline Running out of database storage Storage I/O problem Recovery Database inaccessible because recovery is incomplete or failed Table space cannot be updated because it is in backup pending state Table space inaccessible because it is in drop pending state System Bad response times due to high CPU usage Bad response times due to high memory usage Logging Long running app is using too much log space Transaction log access problems Full log held by in-doubt transaction Reduced application performance due to logging problems Monitor Status Monitor agent offline Monitoring turned off by user Data Server Status Data server not responding
20
20
The Dashboard
Ad-hoc Investigation
Locking Number of locks held Deadlock rate Lock escalation rate Number of lock waits Transaction Activity Transaction rate Failed transaction rate Number of in doubt transactions I/O Activity Data Volume I/O Volume Connections Number of connections High-water-mark of connection System CPU load % Used / Free real memory Used / Free virtual memory Used / Free swap memory Logging Logging volume
Recovery
Days since last backup
21
21
The Alert List
Historical Analysis
Locking Application causing lock escalation Table space inaccessible because its in a quiesced state Application waiting indefinitely for lock held by in-doubt transaction Database Storage Table space inaccessible because it is offline Running out of database storage Storage I/O problem Recovery Database inaccessible because recovery is incomplete or has failed Table space cannot be updated because it is in backup pending state Table space inaccessible because it is in drop pending state System Bad response times due to high CPU usage Bad response times due to high memory usage Logging Long running app is using too much log space Transaction log access problems Full log held by in-doubt transaction Reduced application performance due to logging problems Monitor Status Monitor agent offline Monitoring turned off by user Data Server Status Data server not responding
22
22
Recommendations
Detailed Root Cause Analysis
23
23
Data Studio Administration Console
24
24
Lots of New Things for DBAs
25
25
Things Better Left to a Demo
Data Server Connections and Filtering
26
26
Things Better Left to a Demo
Data sampling and browsing capabilities
27
27
Things Better Left to a Demo
Create functions
28
28
Things Better Left to a Demo
Overview Diagrams
29
29
Things Better Left to a Demo
Create schema objects
30
30
Things Better Left to a Demo
Create SQL statements
31
31
Things Better Left to a Demo
Compare Functionality
32
32
Things Better Left to a Demo
Create stored procedures
33
33
Teaming Support
34
34
Things Better Left to a Demo
Various XML stuff
35
35
Data Web Services
36
36
Service Oriented Architecture
An architectural guideline Changing the way applications are modeled and developed – both from a business and technical perspective Some characteristics: reusable Components, loosely coupled
Governance and Process
Lifecycle
Services
Focus of this section will be on the Services aspect of SOA Specifically Services implemented using Web services Web services != SOA, but they are important component towards achieving SOA
37
37
IBM Data Web Services
• • Next generation Web service provider Full Web service interface • REST-style (HTTP GET/POST binding) • SOAP over HTTP and automatic WSDL generation Best of all…. NO programming required • Build Web services using drag-and-drop • Single-click deploy Tomcat • Test using integrated Web Services Explorer Supports the IBM Data Server family: Community Edition • DB2 LUW v8, v9, v9.5+ Application Server • DB2 z/OS v7, v8, v9 • DB2 i5/OS v5 • Informix IDS v9,v10 Supports populate Web servers • IBM WebSphere Application Server v6.1 • IBM WebSphere Application Server Community Edition v1.1 • Apache TomCat v5.5
38
•
•
•
38
IBM Data Web Services
Jumpstart your SOA: Deployment Without Programming
• Generate Web services from business logic (UDFs, stored procedures, triggers, SQL statements, XQuery statements, etc.) without writing a single line of code The ability to instantly take these data access operations, and the underlying rules that make them, and make them available as Web service will help jump start your company’s SOA adoption plans Expose legacy application components and expose them as services • Before DB2 9.5 Lots of work, varying frameworks (WORF), configuration files (DADX) toolsets, and development expertise To add to this, Web developers aren’t SQL experts (and viceversa) Different IBM Data Servers had different frameworks
39
39
Data Web Services…What is it ?
• Next generation Web Services provider for IBM database servers Generate web services from DML statements (select, insert, update, delete and stored procedure calls) Eclipse based IDE with web services tooling integrated into existing database tooling. Deploy generated services into supported application servers
•
•
•
DB2 Distributed, DB2 z/OS, DB2 i5/OS Informix Data Servers
40
40
IBM Data Web Services
What is Desirable?
Application 1 .NET WID Message Broker Tomcat Service A Service B WSRR WASCE
Application 2 RAD WD/z ESB Data Power WebSphere Service E UDDI
Service D
Operation
Operation
Operation
SQL
ROUTINES
XQuery
41
41
IBM Data Web Services
Point-and-Click SOA
• DB2 9.5
Data Web Services eliminates all coding via a drag-and-drop development paradigm Data Studio IDE has integrated Web services tooling to accelerate this process Deploy generated services with clicks of a button into supported application servers Enable database operations as Web services and generate both SOAP and REST-styled end points with the ability to GET or POST bindings The data can then be accessed by any Web service consumer Lots of endpoints supported so there are plenty of clients that can invoke them such as SOAP, browser-based clients, XForms, HTML forms, and so on Web 2.0 capabilities (JSON, feeds via XSLT, AJAX) Automatic generation of WSDL file describes the service
42
42
Data Web Services
•
•
•
•
Web service creation embedded in “Data” perspective • No programming required • Easy conversion of SQL statements and stored procedures into Web service operations Usage of many default settings • Makes generation of Web service quick and easy • More complex customization possible Automated artifact generation • WSDL, data source configuration, runtime artifacts Integrated deploy and test environment
43
43
5 Simple Steps
WS Runtime Package
Configuration File WS Description (WSDL)
44
44
IBM Data Web Services
45
45
Data Web Services Supported Bindings
SOAP/HTTP HTTP GET Input SOAP Parameter
Use SOAP/HTTP to integrate with message list SOA environment ESB, WSRR, in URL query SOAP-client frameworks, WS-* sting specifications. Complex compared to other styles (needs SOAP clients)
HTTP POST
(parameter URL encoded)
HTTP POST XML
Parameter list
URL-encoded in POST request document
Output
SOAP message
HTTP GET is used from browsers to quickly execute a service but some issues (browsers constraint URL length, values need to be URL-encoded, cumbersome XML, issues with multi-byte character data
XML HTML TEXT
XML HTML TEXT
XML HTML TEXT
SOAP-style
“REST”-style
HTTP POST (XML) provides programmatic access from XMLaware clients (e.g. XForms or AJAX). It’s not as complex as SOAP/HTTP binding
46
HTTP POST (URL-encoded) is Straight forward to use with HTML forms
46
A New Era for Java Productivity
47
47
pureQuery - An Introduction
• Despite the perky name, JDBC programming can be somewhat less than stimulating • Using JDBC to create an application requires a lot of steps, many of which are tiresome • You have to create the Java data access layer, as well as handle CRUD operations via SQL • This typically involves working with 2 different tools: one for Java development and another for the database CRUD operations IBM introduces a Java-based language integrated query called pureQuery that can be used to query relational databases as well as Java collection and database caches • Unifies the toolset challenges detailed easier with super-charged development tools • Introduces a high performance data access platform, an advanced runtime, and APIs to simplify the tasks of development and managing Java applications • Benefits cover the application life cycle (design, develop, deploy, manage, and govern) • Query in-memory unmanaged objects without copies or storage and optionally join with relational data
•
48
48
pureQuery - An Introduction
• pureQuery makes a lot of Java-related data access tasks a lot easier • Provides an environment where you can find problems earlier in the development lifecycle and tie them back from the database back to the application source • Sits as a thin layer on top of JDBC • For the most part, will make your applications run faster than traditional JDBC programming • pureQuery encapsulates the “best practice” use of JDBC-like batch updates, and database specific APIs to provide performance gains beyond what is possible with stand JDBC APIs • Unlocks the benefits of static SQL without having to change any code
49
49
The Challenges pureQuery Solves
The Problem with Today’s Data Access APIs
• Direct SQL Access • Well understood: allows easy control over the issued SQL which in turn can improve performance • BUT: the JDBC development process can be tedious and time consuming
• Java programmers have to map result sets from JDBC to Java objects, create statements and bind parameter markers • For good performance, needs to master the JDBC API and exploit features such as batching and result optimization
• Good improve performance via SQLJ, but this requires you to rewrite the data access layer
•
Managed Objects (Object Relational Mapping framework) • These issues detailed for JDBC have lead to ORM which require less initial effort to create the data access layer • BUT: ORMs have limited control over the SQL that gets issued which creates a layer of complexity when diagnosing runtime performance issues
• Developers can’t control the SQL that gets issued
50
50
The Challenges pureQuery Solves
The Problem with Today’s Data Access APIs
• Java developers have a wide spectrum of Java access APIs to build applications • These APIs can be grouped into two categories: SQL access and Managed objects
51
51
Compounded Problem: Web Applications
JDBC Package
52
52
ORM Example
SELECT object(e) FROM Employee e WHERE e.dept=?1 AND e.salary>=?2
Often, application query is In most cases, persistence Application by queriesis is Query language map intercepted query syntax to JDBC. fromabilityquery. in differentsubset of SQL. SQL a the SQL to layer, and No resultinglock access you track problem How dopath nothing like the Doesn’t have all the query looks at program deployment. back to the SQL queries No you want. SQL query. The resulting original features ability to search catalog issued the application thatto seebadly. query might perform which queries are issued by a given original query??? Changing the application program. query might not result in a similar change in the SQL query.
SELECT * FROM PROD.EMP WHERE DEPT=? AND SALARY>?
53
53
The Problem: Lots of Java Methodologies
Spectrum of Choices
54
54
How pureQuery Helps
• The pureQuery tools, APIs, and runtime provide access to data in databases and to in-memory Java objects • pureQuery essentially eliminates the need for JDBC programming because it integrates the SQL query language with Java as a 1st class programming citizen pureQuery runtime works with all database that have a JDBC driver • Additional tools and integration are provided for IBM data servers • pureQuery allows you to use static or dynamic SQL using the same API • Don’t have to choose between JDBC and SQLJ • Static SQL improves performance, development with early error identification, governance, security, and predictability • Easily switch between static and dynamic execution so develop in static mode and go to production in static mode You can use pureQuery with the J2EE JPA standard, or with popular open source projects such as Spring, iBatis, and so on • pureQuery unifies the implementation of any kind of Java-based data access • You are not forced to use a specific API such as EJB2, JPA, Spring, iBatis, and so on pureQuery is integrated with the recently announced Project Zero
55
•
•
•
55
How pureQuery Helps
pureQuery alleviates these limitations:
Provides a Java editor with SQL intelligence Includes integrated editor with code completion, assistance for SQL in the same manner that they have for Java Write data access code directly in Java with design time syntax and schema checks But it’s not just another SQL-like language such as LINQ or EJB Query It’s SQL that can declaratively query relational databases as well as Java collections Test SQL directly within the Java editor to improve productivity and get design time (not build-time) error identification Errors highlighted just like Java errors
56
56
Example of Data Heap Access
• pureQuery also allows developers to query in-memory un-managed objects • Query original objects in a heap without copies or storage • These objects could be optionally joined with results from a data serve query since pureQuery uses standard SQL • Example joins two Java collections (Department and Purchase) using pureQuery’s in-memory feature • Objects are managed, copied, duplicated, or changed: access in memory (heap) where they live
Department[] depts = …; Employee[] emps = …; Data qoc = DataFactory.getData();
Note the ? parameter marker is used to identify predicate values in the WHERE clause and to identify Java collections in the FROM clause
List e = data.queryList( “select e.firstname, e.lastname, e.empno, e.workdept from ?test.Employeee as e, ?test.Department as d where e.workdept = d.depno”, Employee.class, emps, depts);
57
57
pureQuery – Beyond Function
• Development of applications
• Tools to assist SQL development in .java source file • Simple SQL APIs, easy to write to and extend • Multiple API “styles” to align with popular Java frameworks
• Query important data sources simply
• Database, Cache, Collections, XML
• Problem Determination
• When problems occur, find source quickly.
• Governance / Management
• Track SQL back to individual apps, lock in access paths with static SQL packages, align with customer change control processes
• Provide high performance/scalability
• Application: short path length, coding over metadata, optional code gen, JDBC and static SQL runtime optimizations • Database: static SQL, batching, pass app SQL directly to database
58
58
pureQuery API “Styles”
• Support several API styles to fit well into all of the popular Java programming models/frameworks
• Inline style (familiar JDBC and SQLJ approach) • Method style (similar to JDBC 4 ease of use enhancements) • Named query style (similar to iBatis/JDO/Hibernate/JPA)
59
59
pureQuery Summary
• pureQuery is an IBM project that adds native SQL querying syntax to Java programming languages • pureQuery works with all databases with a JDBC driver • Additional optimizations are provided for IBM data servers and Web servers • Optionally use with the J2EE standard Java Persistence Architecture (JPA), Spring, iBatis and open source projects • pureQuery is integrated with Web Zero/Project Zero as the underlying technology in the Query Zero portion of Data Zero pureQuery is a new set of language features that adds data querying to Java pureQuery is a programming model that introduces queries as a 1st class concept into the Java language pureQuery is a methodology that simplifies and unifies the implementation of any kind of data access • pureQuery does not force you to use a specific architecture • It facilitates the implementation of several existing architectures in Java for accessing data IBM Data Studio provides breakthrough tooling integrating for SQL editing in Java
60
• • •
•
60
Where We Are, Where We Are Going?
• Data Studio Designer • RDA gives us best-of-breed technology • Data Studio Developer • Good progress in Viper 2 • Strong story for Java and .NET, but other areas need further improvement • Data Studio Administrator • We have a bit of a way to go here • Data Studio Performance Manager • DB2 PE gives us a good starting point. • Data Studio Governor • Getting started…something new coming
61
61
Session A10 and A11 IBM Data Studio: The Toolset of the Future Title…Here Today!
Paul C. Zikopoulos, BA, MBA
IBM Canada paulz_ibm@msn.com
62
62