1 / 47

Interesting SQL Solutions to Real Life Problems

Interesting SQL Solutions to Real Life Problems. Anthony Tichonoff Florida Hospital Sr. DB2 DBA Tampa Bay RUG Meeting Fri, Feb 21, 2013 12:45 AM - 1:45 AM. Using the full power of SQL as a programming language Fully exploiting common SQL features in creative ways Fun SQL –

romeo
Télécharger la présentation

Interesting SQL Solutions to Real Life Problems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Interesting SQL Solutions to Real Life Problems Anthony Tichonoff Florida Hospital Sr. DB2 DBA Tampa Bay RUG Meeting Fri, Feb 21, 2013 12:45 AM - 1:45 AM

  2. 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 Presentation Overview

  3. The Challenge I need to identify DB2 objects that grow rapidly.

  4. 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 DASDGrowth

  5. The Breakdown RTS History RTS SQL SQL Select Insert

  6. 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 B • ON B.DBNAME = A.DBNAME • AND B.TSNAME = A.NAME • AND B.PARTITION = A.PARTITION • JOIN SYSIBM.SYSTABLESPACE C • ON C.DBNAME = A.DBNAME • AND C.NAME = A.NAME • AND C.DBID = A.DBID • AND C.PSID = A.PSID • WHERE {Your Filter}

  7. Tablespace Growth SQLPart 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 • )

  8. Tablespace Growth SQLPart 2 • SELECT C.I_DATB, C.I_OBJC ,&Q_DAYS AS Q_DAYS • ,Output :Row, Page, Space, Extent Data • FROM • ( SELECT MinimumValues by Database & Object • FROM RTS_HISTORY • ) L • JOIN • ( SELECT MaximumValues by Database & Object • FROM RTS_HISTORY • ) H • ON H.I_DATB = L.I_DATB AND H.I_OBJC = L.I_OBJC • JOIN • ( SELECT Current Values by Database & Object • FROM RTS_HISTORY • WHERE T_STATS = SubSelect for MAX(T_STATS) • ) C • ON C.I_DATB = L.I_DATB AND C.I_OBJC = L.I_OBJC • JOIN • ( SELECT Oldest Values by Database & Object • FROM RTS_HISTORY • WHERE T_STATS = SubSelect for MIN(T_STATS) • ) O • ON O.I_DATB = L.I_DATB AND O.I_OBJC = L.I_OBJC

  9. The Output

  10. The Challenge I need DB2 to match people and identify duplicates from two separate systems.

  11. The Breakdown & Steps Data Match? Compare Demographic Data Internal External Load External Data Demographic Table SQL Compare Program

  12. The SQL External 1 IN EX Internal 0/Many • Select • Medical Record Id (Patient Key) • ,Demographic Data (Compare Keys) • From Internal Demographic Table • Where • IN.SSN = :EX.SSN • Union • IN.Last Name = :EX.Last Name • & IN.DOB = :EX.DOB • Union • IN.DriverLic= :EX.DriverLic • Union Match ? Possible match, more processing needed to confirm. No Match No rows located in the internal table. Result Set outputs 0 to Many Keys

  13. The SQL Check Compare Keys 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 SQL Compare Program

  14. The Output

  15. The Challenge Can I audit the differences in two different DB2 subsystems.

  16. 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 Catalog Chronology • Databases -Tablespaces -Tablespace Parts • Tables -Table Columns-Indexes-Index Parts-Index Columns Views-View Columns-Aliases • Routines -Triggers

  17. The Breakdown DB2 C DB2 B DB2 A DB2 Declared Temporary DB2 Catalog (Current) DB2 Catalog Logger Program DB2 C DB2 B DB2 A DB2 Chronology DB2 Catalog Snapshot (24 hours old)

  18. The SQL Processing DB2 Declared Temporary Tables Objects exists in both tables but the column values are different thenobject was Altered. Log Alter into Chronology Table S C Objects only exists in snapshot catalog then object was Dropped. Log Drop into Chronology Table S C Objects only exists in current catalog then object was Created. S C Log Create into Chronology Table

  19. The Output

  20. The Challenge I need to audit the data in my user maintained inclusive - inclusive temporal tables.

  21. The Breakdown & Steps Inclusive - InclusiveTime Series

  22. Temporal Audit Inclusive –Inclusive SQL SELECT A.I_KEY ,Output Data FROM SD00 A JOIN SD00 B ON A.I_Key = B.I_KEY WHERE 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 Same row Check Start Overlapped  End Overlapped  Encapsulated 

  23. Temporal Audit Inclusive –Inclusive SQL 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 SD00C 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_A WITH UR Join to Self Gap Check  Find Next End Date  Is Gap Span > 1 Day 

  24. The Output

  25. The Challenge I need to build a dynamic calendar in my application.

  26. The Breakdown & Steps • Calendar 4 dimensional array • Year • Month • Week of Year • Day of Week • Need row for each day of month(Use Recursive SQL) • Use SQL Functions for dimensions • Week of the Year (Group by) • Day of the Week • Assign Day of Week to each column(Sun, Mon, Tue, Wed, Thu, Fri, Sat)

  27. SQL Must build a result set with a row for each day of the month 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) ) Recursive SQL

  28. 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 SAT FROM ( 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 for Week Of Year & Day Of Week

  29. The Output 49 FEBRUARY 2013 S M T W T F S -- -- -- -- -- -- -- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 QMF Result Set using a form Program Display Recursive Table Result Set

  30. The Challenge I need to build dynamic report screens in CICS with scrolling.

  31. The Breakdown & Steps + + +

  32. The SQL – Utilize Nested Common Table Expressions

  33. The SQL – Utilize Nested Common Table Expressions With 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

  34. The SQL – Utilize Nested Common Table Expressions With 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

  35. The SQL – Utilize Nested Common Table Expressions With 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

  36. The SQL – Utilize Nested Common Table Expressions With 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

  37. The SQL – Utilize Nested Common Table Expressions With 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

  38. The SQL – Utilize Nested Common Table Expressions SELECT * FROM ( SELECT Type Row , Account, Campus, Operator , Account DetailsFROM 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

  39. The Output Account Detail Account Summary Operator Summary Campus Summary Hospital Summary

  40. The Output

  41. The Challenge I need to be able to generate current DDL for all of my objects.

  42. The Breakdown & Steps • Florida Hospital DDL Generator • A collection of Rexx modules which regenerates our DDL from the DB2 Catalog

  43. 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

  44. The Output TSO DDLV 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 ===>

  45. The Output TSO DDLV -- -- 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 --

  46. The Output TSO DDLV -- ========== 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 ========== --

  47. Interesting SQL Solutions to Real Life Problems Anthony Tichonoff anthony.tichonoff@flhosp.org Florida Hospital Sr. DB2 DBA Tampa Bay RUG Meeting

More Related