interesting sql solutions to real life problems anthony tichonoff florida hospital sr. db2 dba tampa...
TRANSCRIPT
Interesting SQL Solutions to Real Life Problems
Anthony TichonoffFlorida Hospital Sr. DB2 DBA
Tampa Bay RUG MeetingFri, Feb 21, 2013 12:45 AM - 1:45 AM
Presentation Overview
• Using the full power of SQL as a programming language• Fully exploiting common SQL features in creative ways• Fun SQL –
• Real Time Statistics Auditing DASD Growth• Probabilistic Matching• DB2 Catalog Logger• Auditing Temporal Data for Errors• Calendars• Dynamic Screens• DDL SQL Generation
• Implementing interesting SQL in different languages
2
The Steps
Build DB2 table to collect daily RTS History Stats - Columns for RTS History Table
Insert both tablespace and indexspaces into RTS History table - SQL Insert for RTS Tablespace - SQL Insert for RTS Indexspace
SQL to determine DASD Growth - SQL for DASD Growth
The Insert SQL
INSERT INTO {Your RTS History Table} SELECT CURRENT TIMESTAMP AS T_STATS ,A.DBNAME AS I_DATB ,A.NAME AS I_OBJC ,'T' AS I_TYPE_OBJC ,A.PARTITION AS I_PART ,COALESCE(INT(A.TOTALROWS),0) AS Q_ROWS ,COALESCE(A.NACTIVE,0) AS Q_PAGE_ACTV ,COALESCE(A.SPACE,0) AS Q_SPAC_KB ,COALESCE(A.EXTENTS,0) AS Q_EXTN ,SUBSTR(B.STORNAME,1,8) AS I_N_STOR
FROM SYSIBM.SYSTABLESPACESTATS A JOIN SYSIBM.SYSTABLEPART BON B.DBNAME = A.DBNAME AND B.TSNAME = A.NAME AND B.PARTITION = A.PARTITION
JOIN SYSIBM.SYSTABLESPACE CON C.DBNAME = A.DBNAME AND C.NAME = A.NAME AND C.DBID = A.DBID AND C.PSID = A.PSID
WHERE {Your Filter}
Tablespace Growth SQL
Part 1
WITH RTS_HISTORY ( I_DATB ,I_OBJC ,T_STATS ,Q_ROWS ,Q_PAGES ,Q_SPAC_KB ,Q_EXTN ) AS ( SELECT I_DATB ,I_OBJC ,T_STATS ,SUM(Q_ROWS) AS Q_ROWS ,SUM(Q_PAGE_ACTV) AS Q_PAGES ,SUM(Q_SPAC_KB) AS Q_SPAC_KB ,MAX(Q_EXTN) AS Q_EXTN FROM { Your RTS History Table } WHERE I_TYPE_OBJC = ‘T’ AND T_STATS BETWEEN CURRENT TIMESTAMP –
(&Q_DAYS + 1) DAYS AND CURRENT TIMESTAMP GROUP BY I_DATB ,I_OBJC ,T_STATS )
SELECT C.I_DATB, C.I_OBJC ,&Q_DAYS AS Q_DAYS,Output : Row, Page, Space, Extent Data
FROM ( SELECT Minimum Values by Database & Object FROM RTS_HISTORY ) LJOIN ( SELECT Maximum Values by Database & Object FROM RTS_HISTORY ) HON H.I_DATB = L.I_DATB AND H.I_OBJC = L.I_OBJCJOIN ( SELECT Current Values by Database & Object FROM RTS_HISTORY
WHERE T_STATS = SubSelect for MAX(T_STATS) ) CON C.I_DATB = L.I_DATB AND C.I_OBJC = L.I_OBJCJOIN ( SELECT Oldest Values by Database & Object FROM RTS_HISTORY
WHERE T_STATS = SubSelect for MIN(T_STATS) ) OON O.I_DATB = L.I_DATB AND O.I_OBJC = L.I_OBJC
Tablespace Growth SQL
Part 2
The OutputRows
Database Object # Days Delta Max Current
DKM97PRD SKM9732 30 -162,082,402 248,311,973 56,125,070
DKM97PRD SKM9733 30 -35,025,961 49,524,240 9,494,040
DIS97PRD SIS9761 30 -28,795,566 201,123,146 124,806,402
DHC97PRD SHC9708 30 22,001,796 - 62,233,272
DGL97PRD SGL9749 30 18,007,738 - 229,901,672
Pages Space MB
Delta Max Current Delta Max Current
-1,889,280 3,021,840 810,000 -8,136 12,528 3,240
-406,440 603,730 133,210 -1,620 2,628 720
-1,299,136 6,105,568 3,292,608 -5,231 24,496 13,232
769,625 - 2,282,476 3,008 - 9,180
67,841 - 3,160,942 272 - 12,962
The Breakdown & Steps
Compare Program
SQL
ExternalData
DemographicTable
Load
Compare Demographic DataExternal
Internal
Data Match?
The SQL
Select Medical Record Id (Patient Key) ,Demographic Data (Compare Keys)From Internal Demographic TableWhere IN.SSN = :EX.SSNUnion IN.Last Name = :EX.Last Name
& IN.DOB = :EX.DOBUnion IN.Driver Lic = :EX.Driver LicUnion• • •
Result Set outputs0 to Many Keys
No rows located in the internal table.
No Match
Match ?Possible match, more processing needed to confirm.
External InternalEX IN0/Many
1
The SQL
Compare ProgramSQL
If EX.cKey1 = IN.cKey1 and EX.cKey2 = IN.cKey2 and …
Then Add to Score
If EX.cKey4 = IN.cKey4 and EX.cKey5 = IN.cKey5 and EX.cKey6 = IN.cKey6 and …
Then Add to Score
CheckCompare
Keys
Match Ratio Score
Full 1:1 100%
Partial 1:M 1% - 100%
No 1:0 0%
The Output
FULL Match
Insert into Cross Reference Table
PARTIAL Match
Matchor
Reject
NO Match
Insert into Demographic & Cross Ref Table
The Breakdown & Steps
• Create Snap Shot Catalog Tables • Create Catalog Chronology Table• Program must utilize Declare Global Temp Tables to collect
all catalog data for all DB2 Subsystems• Use Full Outer Joins comparing data for changes• Differences are loaded into Catalog Chronology Table
DB2 Subsystem A DB2 Subsystem B
Databases -Tablespaces -Tablespace PartsTables -Table Columns-Indexes-Index Parts-Index Columns
Views-View Columns-Aliases Routines -Triggers
Catalog Chronology
DB2 Catalog Logger Program
The Breakdown
DB2 Catalog Snapshot
(24 hours old)
DB2 Catalog (Current)
DB2 Chronology
DB2 Declared Temporary
The SQL
S CS C
S C Objects exists in both tables but the column values are different thenobject was Altered.
Objects only exists in snapshot catalog then object was Dropped.
Objects only exists in current catalog then object was Created.
Processing DB2 Declared Temporary Tables
Log Alter into Chronology Table
Log Drop into Chronology Table
Log Create into Chronology Table
The Output
Work Date
ObjectWork
Operator
Request
TypeCommentTyp
eName Creat
or
9/12/2012 X XRSD97321 PCTL AAT89A AO
ALTERED PRIMARY OLD 36,000 NEW 108,000
8/2/2012 T SSD9732 PCTL AAT89A CO CREATED NEW TABLESPACE
8/2/2012 R RSD97321 PCTL AAT89A CO CREATED NEW TRIGGER
8/2/2012 R RSD97320 PCTL AAT89A CO CREATED NEW TRIGGER
8/2/2012 X XSD97321 PCTL AAT89A CO CREATED NEW INDEX
8/2/2012 X XSD97320 PCTL AAT89A CO CREATED NEW INDEX
8/2/2012 T SSD9732 PCTL AAT89A CO CREATED NEW TABLE
2/27/2012 T SSD9732 TEST RLB77C AC F_VALD_PDX_LOCK "LOCK PRINCIPAL DIAGNOSIS"
1/4/2012 R RSD97321 TEST RLB77C CO CREATED NEW TRIGGER
1/4/2012 R RSD97320 TEST RLB77C CO CREATED NEW TRIGGER
1/4/2012 X XSD97321 TEST RLB77C CO CREATED NEW INDEX
1/4/2012 T SSD9732 TEST RLB77C CO CREATED NEW TABLESPACE
1/4/2012 X XSD97320 TEST RLB77C CO CREATED NEW INDEX
1/4/2012 T SSD9732 TEST RLB77C CO CREATED NEW TABLE
Temporal Audit Inclusive – Inclusive
SELECT A.I_KEY ,Output Data FROM SD00 A JOIN SD00 B ON A.I_Key = B.I_KEYWHERE A.D_STAR <> B.D_STAR AND A.D_END <> B.D_END AND ( ( A.D_STAR <= B.D_STAR AND A.D_STAR <= B.D_END AND ( A.D_END >= B.D_STAR OR A.D_END >= B.D_END ) ) OR ( A.D_END >= B.D_STAR AND A.D_END >= B.D_END AND ( A.D_STAR <= B.D_STAR OR A.D_STAR <= B.D_END ) ) OR ( A.D_STAR >= B.D_STAR AND A.D_END <= B.D_END ) )
Join to Self
Start Overlapped
End Overlapped
Encapsulated
SQL
22
Same row Check
Temporal Audit Inclusive – Inclusive
UNION ALL
SELECT A.I_KEY ,Output Data FROM SD00 A JOIN SD00 B ON A.I_Key = B.I_KEY WHERE &GAPS = 'Y' AND B.D_STAR > A.D_STAR AND B.D_END = (
SELECT MIN(C.D_END) FROM SD00 C WHERE C.I_KEY = A.I_KEY AND C.D_END > A.D_END ) AND DAYS(B.D_STAR) – DAYS(A.D_END) > 1
ORDER BY I_KEY ,D_STAR_AWITH UR
Join to Self
Is Gap Span > 1 Day
Find Next End Date
SQL
23
Gap Check
The Output
Key Error Start -A End - A Start - B End - B
KEY E EMBEDDED 01/01/0001 12/31/9999 04/10/2008 12/31/2050
KEY E EMBEDDED 04/10/2008 12/31/2050 01/01/0001 12/31/9999
KEY G GAP 10/23/2002 12/12/2002 12/18/2002 01/08/2003
KEY G GAP 12/18/2002 01/08/2003 09/05/2003 12/31/9999
KEY O OVERLAP 07/01/2004 11/16/2005 10/02/2005 10/31/2009
KEY O OVERLAP 11/01/2009 01/25/2010 01/02/2010 12/31/9999
The Breakdown & Steps
1. Calendar 4 dimensional arraya. Year b. Month c. Week of Yeard. Day of Week
2. Need row for each day of month(Use Recursive SQL)
3. Use SQL Functions for dimensionsa. Week of the Year (Group by)
b. Day of the Week
4. Assign Day of Week to each column(Sun, Mon, Tue, Wed, Thu, Fri, Sat)
SQL
WITH DAYTAB ( D_DAY ) AS ( SELECT LAST_DAY(DATE(&DATE) – 1 MONTH) + 1 DAY AS D_DAY FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT D_DAY + 1 DAY AS D_DAY FROM DAYTAB WHERE D_DAY < LAST_DAY(&DATE) )
Must build a result set with a row for each day of the month
Recursive SQL
SELECT DAYOFYEAR(&DATE) AS DY ,YEAR(&DATE) AS YY ,UCASE( (SUBSTR(CHAR(DATE(&DATE), LOCAL),1, POSSTR(CHAR(DATE(&DATE), LOCAL),' ')) ) ) AS MM_NAME , WY_DEM ,MAX(CASE WHEN DW_DEM = 1 THEN DD ELSE '' END) AS SUN ,MAX(CASE WHEN DW_DEM = 2 THEN DD ELSE '' END) AS MON ,MAX(CASE WHEN DW_DEM = 3 THEN DD ELSE '' END) AS TUE ,MAX(CASE WHEN DW_DEM = 4 THEN DD ELSE '' END) AS WED ,MAX(CASE WHEN DW_DEM = 5 THEN DD ELSE '' END) AS THU ,MAX(CASE WHEN DW_DEM = 6 THEN DD ELSE '' END) AS FRI ,MAX(CASE WHEN DW_DEM = 7 THEN DD ELSE '' END) AS SATFROM ( SELECT WEEK(D_DAY) AS WY_DEM ,DAYOFWEEK(D_DAY) AS DW_DEM ,RIGHT(' ' || STRIP(CHAR(DAY(D_DAY))),2) AS DD FROM DAYTAB ) X
GROUP BY WY_DEM WITH UR
Formats Output & Builds
Week Day Columns
Calculates Dimensions forWeek Of Year
& Day Of Week
SQL
The Output
Recursive Table Result Set
D_DAY
02/01/2013 02/15/201302/02/2013 02/16/201302/03/2013 02/17/201302/04/2013 02/18/201302/05/2013 02/19/201302/06/2013 02/20/201302/07/2013 02/21/201302/08/2013 02/22/201302/09/2013 02/23/201302/10/2013 02/24/201302/11/2013 02/25/201302/12/2013 02/26/201302/13/2013 02/27/201302/14/2013 02/28/2013
49 FEBRUARY 2013
S M T W T F S-- -- -- -- -- -- -- 1 2 3 4 5 6 7 8 910 11 12 13 14 15 1617 18 19 20 21 22 2324 25 26 27 28
QMF Result Set using a form
Program Display
The Breakdown & Steps
Hospital Grand Totals (Counts & Totals)
$$$$
Operator
Campus
Accounts $$$$
Errors to work
. . .
. . .
. . .
Campus Totals $$$$
. . .
Operator Totals $$$$
+
+
+
The SQL – Utilize Nested Common Table Expressions
Build common table expression for all account error detail – AccnDetail
The SQL – Utilize Nested Common Table ExpressionsWith AccnDetail
( Type Row ,Account ,Campus ,Operator ,Account Details)
AS( Select ‘2D’ as Type Row ,Account ,Campus ,Operator ,Other Details From { User Table_A } Join { User Table_B } Join { User Table_C }
Where {Account Detail Filtering})
,AccnSumm ( Continue … ) ,CampSumm ( Continue … ) ,OprtSumm ( Continue … ) ,HospSumm ( Continue … )
Account Details
The SQL – Utilize Nested Common Table ExpressionsWith AccnDetail ( Previous … ) ,AccnSumm ( Continue … )
( Type Row ,Account ,Campus ,Operator ,Aggregated Account Details)
AS( Select ‘1H’ as Type Row ,Account ,Campus ,Operator , Aggregated Account Details From { AccnDetail } Group By
Operator,Campus,Account
)
,CampSumm ( Continue … ) ,OprtSumm ( Continue … ) ,HospSumm ( Continue … )
Account Summary
The SQL – Utilize Nested Common Table ExpressionsWith AccnDetail ( Previous … ) ,AccnSumm ( Previous … ) ,CampSumm
( Type Row ,Account ,Campus ,Operator ,Aggregated Account Summary)
AS( Select ‘3C’ as Type Row ,Null Account ,Campus ,Operator , Aggregated Account Summary From { AccnSumm } Group By
Operator,Campus
)
,OprtSumm ( Continue … ) ,HospSumm ( Continue … )
Campus Summary
The SQL – Utilize Nested Common Table ExpressionsWith AccnDetail ( Previous … ) ,AccnSumm ( Previous … ) ,CampSumm ( Previous … ) ,OprtSumm
( Type Row ,Account ,Campus ,Operator ,Aggregated Campus Summary)
AS( Select ‘4O’ as Type Row ,Null Account ,Null Campus ,Operator , Aggregated Campus Summary From { CampSumm } Group By
Operator)
,HospSumm ( Continue … )
Operator Summary
The SQL – Utilize Nested Common Table ExpressionsWith AccnDetail ( Previous … ) ,AccnSumm ( Previous … ) ,CampSumm ( Previous … ) ,OprtSumm ( Previous … ) ,HospSumm
( Type Row ,Account ,Campus ,Operator ,Aggregated Operator Summary)
AS( Select ‘5T’ as Type Row ,Null Account ,Null Campus ,Null Operator ,Aggregated Operator Summary From { OprtSumm }
)
Hospital Summary
The SQL – Utilize Nested Common Table ExpressionsSELECT *FROM ( SELECT Type Row , Account, Campus, Operator
, Account Details FROM AccnDetail Where ShowDetail = ‘Y’ ) Union All ( SELECT Type Row , Account, Campus, Operator,Aggregated Account Details FROM AccnSumm Where ShowAccn = ‘Y’) Union All ( SELECT Type Row , Account, Campus, Operator,Aggregated Account Summary FROM CampSumm Where ShowCampus = ‘Y’) Union All ( SELECT Type Row , Account, Campus, Operator,Aggregated Campus Summary FROM OprtSumm ) Union All ( SELECT Type Row , Account, Campus, Operator,Aggregated Operator Summary FROM HospSumm )
ORDER BY Operator, Campus, Account, Type Row
Main SQL
The Output
Row Type Operator Campus Account #
Errors Charges Error Id # Accounts
1H 3544 $ 5,182,626.03 0 17601A AGU2E0 01 22814252 1 $ 213.00 0 12D 183250672 02D 183270843 0
1A AGU2E0 01 22814405 2 $ 213.00 0 12D 183250703 0
3C AGU2E0 01 3 $ 426.00 0 2
1A AGU2E0 02 22799806 2 $ 31,166.50 0 12D 183249295 02D 183270206 0
3C AGU2E0 02 2 $ 31,166.50 0 1
4O AGU2E0 5 $ 31,592.50 0 3
Account Detail Account SummaryCampus Summary Operator Summary
Hospital Summary
The Breakdown & Steps
Florida Hospital DDL Generator A collection of Rexx modules which regenerates our DDL from the DB2 Catalog
Rexx Modules DB2 Object to Regenerate Rexx
Modules DB2 Object to Regenerate
DDLAL01 Aliases DDLMQ01 MQTsDDLCT01 Create All Objects DDLRT01 RoutinesDDLDB01 Databases DDLSQ01 SequencesDDLGR01 Table Grants DDLST01 Object Stats DataDDLGR02 Database Grants DDLTB01 TablesDDLGR03 Tablespace Grants DDLTR01 TriggersDDLGR04 Sequence Grants DDLTS01 TablespacesDDLIX01 Indexes DDLVW01 ViewsDDLLO01 LOBs DDLWR01 Output: File or Online ViewDDLV Online View of DDL DDLG Generates DDL to a File
The SQL WITH VIEWLIST ( BTYPE ,LEVEL ,BCREATOR ,BNAME ,DCREATOR ,DNAME ) AS ( SELECT ROOT.BTYPE ,1 ,ROOT.BCREATOR ,ROOT.BNAME ,ROOT.DCREATOR ,ROOT.DNAME FROM SYSIBM.SYSVIEWDEP ROOT WHERE BCREATOR = :CREATOR AND BNAME = :TBNAME UNION ALL SELECT CHILD.BTYPE ,PARENT.LEVEL + 1 ,CHILD.BCREATOR ,CHILD.BNAME ,CHILD.DCREATOR ,CHILD.DNAME FROM VIEWLIST PARENT JOIN SYSIBM.SYSVIEWDEP CHILD ON PARENT.DCREATOR = CHILD.BCREATOR AND PARENT.DNAME = CHILD.BNAME WHERE PARENT.LEVEL < 6 )
SELECT BTYPE ,LEVEL ,BCREATOR ,BNAME ,DCREATOR ,DNAME FROM VIEWLIST WITH UR
Uses Recursive
SQL to Locate All
Views
The Output
D B 2 D D L 13/02/21 21:30 DB2 ===> DSN2
Object ===> PCTL.TDA9700
DDL Options:
Entire DDL ===> _ Object Only ===> _
Tablespace ===> X Table ===> _ Indexes ===> _ Views ===> _ Aliases ===> _ Triggers ===> _ Grants ===> _ LOB ===> _
Enter "X" to select option
PF1 for Help
COMMAND ===>
TSO DDLV
The Output---- DDL EXEC GENERATION BY: AAT89A - 13/02/21---------------------------------------------------------- DDL WAS BUILT USING TABLE PCTL.TDA9700 AS INPUT---------------------------------------------------------------------------------------------------------------- THIS MEMBER CONTAINS SQL STATEMENTS TO CREATE:-- TABLESPACE-- FOR DDA97PRD.SDA9700-------------------------------------------------------- SET CURRENT SQLID = 'DBSYSADM' ;---- ========== S T A T I S T I C S ==========---- PCTL.TDA9700 - DB2 APPLICATION TABLE RECOVERY PROFILE-- ROW COUNT 6,267-- SIZE 0.0021 GB-- ROW LENGTH 279-- INDEXES 5-- PACK DEPS 61--
TSO DDLV
The Output-- ========== T A B L E S P A C E ==========----DROP TABLESPACE DDA97PRD.SDA9700--;--COMMIT WORK--;-- CREATE TABLESPACE SDA9700 IN DDA97PRD USING STOGROUP PCTL PRIQTY 720 SECQTY 3600 ERASE NO FREEPAGE 30 PCTFREE 10 COMPRESS NO SEGSIZE 64 BUFFERPOOL BP2 LOCKSIZE PAGE LOCKMAX SYSTEM MAXROWS 255 CCSID EBCDIC CLOSE NO ;---- ========== T S - G R A N T S ==========--
TSO DDLV
Interesting SQL Solutions to Real Life Problems
Anthony [email protected] Hospital Sr. DB2 DBA
Tampa Bay RUG Meeting