Transcript QMF FOR WORKSTATION / WEB Client

MasterCard and QMF: Providing
Analytics for More Than DB2; DDZ-1281
Tom Glaser (MasterCard)
© 2015 IBM Corporation
Agenda
• Installation of the Workstation Client
• QMF W/ DB2
• Weekly Zero Counter Report
• Weekly Top 20 Report
• Access to Performance Database
• W/ solidDB
• W/ PostgreSQL
• W/ Oracle
• Moving data between different relational RDBMSs
• Joining tables from different RDBMSs
• QMF for Web Client (Job Scheduler Monthly Jobs)
• Workspaces
• Dashboard; DB2 zParms
• Using QMF for the IBM DB2 Analytics Accelerator Stored
Procedures
• Hidden GEMS
Who is MasterCard?
Our Role
Consumers want better ways to pay.
We invent them.
People want financial access.
We find ways to serve them.
Checkout lines are too slow.
We help them move faster.
Commuters are busy.
We speed them on their way.
Procurement is complicated.
We make it simple.
Who is MasterCard?
Key Statistics
10,300
employees
210
countries and territories
43 billion
processed transactions
US $4.5 trillion*
gross dollar volume
US $9.5 billion
net revenue
* Excludes volume generated by Maestro and Cirrus cards
All numbers as reported as of December 31, 2014
MasterCard: Our Environment
55 DB2 z/OS Subsystems
• Data Sharing and non Data Sharing
• V10 QMF for TSO, Workstation, Web Clients
• Relational DBMSs:
• DB2
• Oracle
• SQL Server
• PostgreSQL
• solidDB
• MySQL
•
Where does MasterCard use QMF?
Windows
TSO
Workstation
Web Client
Where does MasterCard use QMF?
Web client
PostgreSQL
CDC
SolidDB
QMF
IBM DB2
for z/OS
Workstation client
TSO client
Oracle
QMF TSO Client
Weekly Performance Reports: Zero Counter Report
D2P1
METRIC
D2P1
CB
D2P2
D2P3
D2P4
-----------------------------------------------------------------------BUFFERPOOL:
4KUsed, Not 32K
32KUsed, Not 4K
DM Crit Thresh
Expansion/Full
Expansion/GETM
GETPAGE/NoBuff
1511306
1481379
910025
Merge/No Buffer
WrkFile/BufRes
WrkFile/No Buf
2365519
DATA SHARING:
CF Lack of Stor
EngNotAvaiPLock
ExplicCrosInval
GBP CastoutThrs
MVX XES ResCont
EDM POOL:
EDM DBD Full
-
QMF TSO Client…one more example
Weekly Performance Reports: Top 20 Report
#2:TOP CPU CONSUMERS BY PKGS FOR ALL DB2 SUBSYSTEMS
PACKAGE
-------SYSLH200
DSNTIAUL
CZIUOWPG
DSNTIAUL
GP038750
DSNTIAUL
DSNTIAUL
SYSSH200
DA831010
SYSSH200
SYSSH200
SYSSH200
MASTERCARD
DB2
CPU
HOURS
---------296.08
275.44
197.71
197.00
171.12
162.90
139.82
133.65
131.53
129.48
114.12
102.99
TOTAL
DB2
CPU
COST
---------19244.93
17903.82
12851.07
12804.89
11122.66
10588.35
9088.56
8686.97
8549.55
8415.90
7418.10
6694.20
DB2
SSID
---DDP6
DDP6
DDP6
D2P1
D2P1
DBP6
D2P1
D2P1
DDP6
DBP7
DAP7
D2P1
LPAR
---CPUI
CPUI
CPUI
CPUA
CPUA
CPUJ
CPUC
CPUC
CPUI
CPUJ
CPUI
CPUA
QMF Workstation Client….adhoc / perform DB
08/07/2015 - 08/08/2015
OBJECT
ELAPSED
CORRID
NAME
BP
------
------------------
------
ENTRPS
DCTGN_XCHG_GENRFIN
DCIGN1
ENTRXH
GPs
SYNC
HRS
READS
SYNC READS
SYNC READS
PCT
CUM PCT
--------------
--------
----------------
----------
----------
BP34
7,205,738
2.77
2,735,878
51
51
BP33
15,010,822
1.31
2,665,291
49
100
----------------
----------
----------
*
5,401,169
2
2
ADDRESS_TB
BP32
36,729,225
5.78
19,707,071
72
72
ADFA21X1
BP31
105,670,833
1.57
7,717,667
28
100
----------------
----------
----------
27,424,738
11
14
*
FORMS
…Percentage
…Cumulative Percentage
Installation for the QMF Workstation Client
Microsoft Software Center:
- We test to make sure DB2 Connect has been installed (for the *.jar files)
Installation for QMF for Workstation Client…cont
Two Repositories:
- Used by QMF for the Workstation and Web clients
- Both Repositories are identical
xxxxxxxxx
xxxxxxxxx
Going with a shared repository allows us to add one ssid
and everyone has access to it. Simple!
solidDB
In-memory Database:
• CDC Replication from DB2 to solidDB
• Command line interface on AIX
• We needed a better method of querying solidDB so we turned to
the Workstation and Web clients of QMF.
solidDB…cont
Adding solidDB Driver:
solidDB…cont
AIX server + Environment
solidDB…cont
Which do you prefer:
OR
OR
PostgreSQL
• What is PostgreSQL
• Low cost open source database
• Been around for about 15 years
• Runs on most Distributed OS; not on z/OS
• ACID Compliant (Atomicity, Consistency, Isolation, Durability)
• Why PostgreSQL
• Reduce software costs
• First choice for new apps
• No additional charge for using QMF
• DB2 Support
• Introduction to SQL Class
• Covers DB2, Oracle, PostgreSQL
• To work with PostgreSQL, V10 QMF requires Fix Pack 10
PostgreSQL…cont
Download PostgreSQL driver
Download…
PostgreSQL…cont
Bounce started task
after copying driver.
PostgreSQL…cont
Only one driver needed for everyone…
Oracle
Auditing: First, add jar file for Oracle: odbc6.jar
Oracle
Copying Data
And how did we copy data from one RDBMS to another, such as PostgreSQL?
…and select the data source
Copying Data…cont
Several other options, such as a QMF Proc:
CONNECT TO DB2
DISPLAY TABLE Q.STAFF
CONNECT TO “solidDB esb0stl2_dtl1(CPUE)”
SAVE DATA AS ownerid.STAFF
-- If run as a Scheduled Job it will take less local memory and CPU time
-- than if done manually: returned results from DISPLAY are not rendered
-- as a grid. Proc can also be parameterized:
CONNECT TO &DatabaseSource
DISPLAY TABLE &Table
CONNECT TO &DatabaseTarget
SAVE DATA AS &Table
-- This would result in the new table having the same name as the old.
-- Of course it could be like this SAVE DATA AS &OWNER.&TABLE as
-- shown
Joining Tables from Different RDBMSs
Not possible with DB2 on z/OS:
-512; STATEMENT REFERENCE TO REMOTE OBJECT IS INVALID
To correct:
- DB2 LUW
- Federated Database
- Nicknames
- Or QMF FOR
WORKSTATION / WEB Client
Joining Tables from Different RDBMSs…cont
Create virtual data source (example using with Web Client)
Joining Tables from Different RDBMSs…cont
Copy solidDB and PostgreSQL tables into virtual data source
solidDB staff already added…
Let’s add Org from PostgreSQL
Joining Tables from Different RDBMSs…cont
Enter SQL and run as normal….
Joining Tables from Different RDBMSs…cont
A few thoughts about joining data:
• Subject to SQL supported by the database
• Data stored on physical disk
• Understand the amount of data being returned
• QMF will execute “SELECT *” from each table to join.
• Add predicate to minimize the amount of data being joined
Joining Tables from Different RDBMSs…cont
Analytical Query:
• Stores and joins data in memory, not disk like Virtual Data Source
• For large tables, add a predicate “WHERE”
Joining Tables from Different RDBMSs…cont
Analytical Query: No SQL is written, QMF handles this for you
Job Scheduler on QMF Web Client
Monthly CPU Stats via Job Scheduler…3 steps.
1. QMF Proc to run queries
- Output will be Excel spreadsheet
• xlsx available in QMF 11
- Output will be mailed
- Subject heading on email note
Job Scheduler on Web Client…cont
Monthly CPU Stats via Job Scheduler
2. Define new job schedule & calendar:
Job Scheduler on Web Client…cont
Monthly CPU Stats via Job Scheduler
3. Output is sent to user and QMF tracks job completion:
Excel Spreadsheet
Note: The QMF Workstation client uses job scheduler on your PC;
the QMF Web client uses built-in scheduler
QMF Dashboard & DSNWZP
IBM Stored Procedure DSNWZP & QMF Dashboard (Let the
fun begin)
DSNWZP: Used to retrieve zParms;
ADDRESS DSNREXX
"EXECSQL CALL DSNWZP USING DESCRIPTOR :INSQLDA "
IF SQLCODE ¬= 0 THEN
Do
…
Command ===>,
000245,SQLSTMT1 = "INSERT INTO D2INS01.ZPARM_TABLE",
000246, "VALUES ('"||
report_date
000247,
||","||"'"||getssid
000248,
||","||"'"||ssid
000249,
||","||"'"||abexp
000250,
||","||"'"||abind
000251,
||","||"'"||accel
.
.
.
||"'",
||"'",
||"'",
||"'",
||"'",
||"'",
QMF Dashboard & DSNWZP…cont
Stored Procedure DSNWZP & QMF Dashboard, Cont.
In the beginning…build a scene
QMF Dashboard & DSNWZP…cont
Stored Procedure DSNWZP & QMF Dashboard, Cont.
In the beginning…add buttons, tables, etc
QMF Dashboard & DSNWZP…cont
Stored Procedure DSNWZP & QMF Dashboard, Cont.
QMF Workspaces - Security
Direct quote from one of our applications: “Just
the shared
workspaces makes QMF for Workstation a valuable query tool
for teams.”
Shared Workspaces: In Eclipse, workspaces
are used to organize a collection of projects.
In QMF for Workstation, users can create a
workspace to house projects that hold the
artifacts (data sources and repository objects)
needed to build interactive visual tools, such
as visual dashboards and reports. As the
administrator you can choose to have your
QMF for Workstation users work with
repository objects from a workspace, or
directly from the repository.
“Admin View”
QMF Workspaces – Security...cont
• Automatically created by first access
• Can be created by Administrator
• Only viewable by those who have the
authority
• Right click on objects within QMF to
select “INCLUDE IN HOME WORKSPACE”
“User View”
QMF Workspaces – Security…cont
QMF and the IBM DB2 Analytics Accelerator
(Stored Procedures)
QMF and the IBM DB2 Analytics Accelerator
(Stored Procedures)…cont
Double “click” for Lob Indicator
QMF and the IBM DB2 Analytics Accelerator
(Stored Procedures)
•
By default QMF for Workstation and Web Client inherit the acceleration from for
Query Acceleration DB2 Analytics Accelerator based on the DB2 for z/OS setting
CURRENT QUERY ACCELERATION
•
To modify IDAA Current Register:
QMF and the IBM DB2 Analytics Accelerator
(Stored Procedures)
QMF; enhanced to work with IDAA:
• Since the introduction of the Accelerator, SAVE DATA has failed.
Due to the way SAVE DATA works, queries that use that QMF
feature are not eligible to run in the Accelerator.
• IDAA - IDT….In-Database Transformation (IDT)
• Used by QMF to build tables stored only in IDAA. This now allows
SAVE DATA to run in Accelerator.
PI30376: NEW FUNCTION ON DB2 10 FOR Z/OS is the APAR that
introduced accelerator-only tables along with in-database
transformation (IDT) and temporal query offload…Save Data
Hidden Gems
Hidden Gems
Font size
Hidden Gems…cont
F11 on QMF for Web Client
Hidden Gems…cont
Entity Relationship Diagram; many features in QMF, like Prompted
Query, but ER Diagrams visually show it.
Hidden Gems…cont
Password Propagation
Hidden Gems…cont
What SSID am I working on?
Hidden Gems…cont
Content Assist: Content Assist helps to complete SQL
statements by providing lists of options while the statement
is being typed.
The contents of the dropdown list depend on the
location of the cursor in
the SQL statement and
the contents of the table
being called.
Ctrl +
Space Bar
Hidden Gems…cont
Content Assist…
Ctrl + Space Bar
Hidden Gems…cont
Content Assist…
Ctrl + Space Bar
Hidden Gems…cont
Format SQL Text: (Not available on Web)
Hidden Gems…cont
Color Text: The SQL Query editor provides coloring support
for your SQL statements. (not available with Chrome)
Hidden Gems…cont
Multiple Windows
Hidden Gems…cont
Running multiple queries
Hidden Gems…cont
No need to click the green arrow…
(Ctrl + R) or F2
to execute
Hidden Gems…cont
Reset Perspective; Eclipse can be confusing, get your view back
Hidden Gems…cont
Save DB2 CPU….Cache
Prepare: Alt+Q+P just does a PREPARE and validates the query without the
overhead of executing it and returning the result.
Note: If a user runs the same query more than once during the same caching
interval (2 hours here) then QMF leaves the database alone and uses the
locally cached data.
Hidden Gems…cont
Charts….you don’t need Excel
Hidden Gems…cont
Troubleshooting: Viewing Errors
Hidden Gems…cont
Troubleshooting: Collecting data for IBM
Places to visit:
• youtube.com
• SG24-8012-00; Complete Analytics with QMF
• Getting Started with QMF for Workstation and Web Client
• developerWorks.com (search for QMF)
Thank You
“A special thank you to Shawn Sullivan of Rocket Software
for his assistance over the many years.”
Thank You
© 2015 IBM Corporation