201011 fun how to access db2 efficiently from focus
TRANSCRIPT
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2009 CIGNA
How to Access DB2 Efficiently from FOCUS
Michelle Cavanaugh
Information Builders Summit 2010 User Conference
June 2010
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
2
How to Access DB2 Efficiently from FOCUS
Introduction & Level Set Basic FOCUS/DB2 Steps DB2 Optimization Steps Traces How to Optimize FOCUS/DB2
Code Examples Q & A’s
Agenda
2
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
How to Access DB2 Efficiently from FOCUS
CIGNA Corporation is…
“An employee benefits company uniquely positioned to help companies succeed and help people enjoy a better quality of life.”
Disclaimer:
The opinions and conclusions expressed are those of the speaker and not those of CIGNA Corporation.
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
4
How to Access DB2 Efficiently from FOCUS
Me: Installation Troubleshooting Optimization
Hardware: 3 IBM z10’s 5 Complexes/Groups 15 LPARs (Logical PARtitions)
Software: OS: z/OS 1.10 & 1.11 DB2: 27 SSIDs
- V8 New Function Mode (NFM)- V9 Conversion Mode (CM) & NFM
FOCUS:7.3.1 - Production & 7.6.10 - Test
Introductions & Level Set
4
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
5
Basic FOCUS/DB2 Processing
FOCUS calls requested module (TABLE, GRAPH) Reads & Parses MFD Parses the request (FOCEXEC) Calls the FOCUS/DB2 Interface
1. FOCUS
5
2. FOCUS/DB2 Interface
Reads & Parses the AFD Checks for Optimization – System Level Analyzes & Optimizes the request Creates SELECT statements Prepares, allocates & opens cursors Calls DB2 to FETCH the rows of data
3. DB2
Analyzes & Optimizes the passed SQL Chooses the access path & retrieval method Retrieves data Creates answer set
FOCUS
FOCUS/DB2Interface
DB2
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
6
Basic FOCUS/DB2 Processing
DB2 sends 1 row of data or a SQL status message to FOCUS
Converts non-standard data into FOCUS formats FOCUS processes are done (IF/WHERE, DEFINEs, etc.) Puts valid rows into the FOCUS Internal Matrix Loops back to get the next row of the DB2 answer set
This continues until the end of the answer set –
SQL code 100+ (End)
4. FOCUS/DB2 Interface
6
5. FOCUS
Processes the Internal Matrix The report is displayed
FOCUS
FOCUS/DB2Interface
DB2
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
7
FOCUS/DB2 Optimization
1. Projection & Record Selection – Always Done
2. JOINs
3. SORTs
4. Aggregation - When Required
If your code does not pass these steps, your request is not optimized
Optimization Order
7
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
8
FOCUS/DB2 Optimization
Projection – Specific columns only – SELECT PRINT/SUM/COUNT Fields Objects of JOINs & DEFINEs PRINT * - All columns in Master – Never a SELECT *
Record Selection – SQL WHERE DEFINEs – Within a Single Segment YMD or YYMD Date Fields LIKEs - With Same Length or Wildcards (%)*
WHERE (T1.<Field> LIKE ‘<Value>’)
* If different lengths or no wildcards WHERE (T1.<Field> = ‘<value>’)
1. Projection & Record Selection – Translates FOCUS to SQL
8
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
9
FOCUS/DB2 Optimization
JOIN is Optimized: Single SELECT Statement One DB2 Subsystem (SSID) AFD has KEYS >0 Less than 15 TABLEs in a Single JOIN
JOIN Optimized = JOIN Order does not matter
JOIN is Not Optimized: FOCUS-Managed JOIN Multiple DB2 Subsystems (SSID) MASTER contains an OCCURS DB2 to Fix files
JOIN is NOT Optimized = JOIN Order Critical
If JOINs Optimizes SORTs are checked
2. JOIN Optimization
9
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
10
FOCUS/DB2 Optimization
10
3. SORT Optimization
SORT is Optimized: SQL ORDER BY SORT on REAL fields SORT on COMPUTE fields before DEFINE fields With FST. & LST. – MFD/AFD KEYs must be correct
SORT is Not Optimized if: JOIN was not Optimized Multi-Verb Request ACROSS’s, RETYPE’s, & COMPUTE’s that use Direct Operators Direct Operators that require the Internal Matrix (e.g. TOT.PCT. or RPCT.)
If SORT Optimizes Aggregates are checked
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
11
FOCUS/DB2 Optimization
Why? DB2 is much better at aggregation Less data returned to FOCUS Less Data = Faster FOCUS Processing
Aggregates that are Translated to SQL: Verbs (SUM, COUNT & WRITE) Direct Operators: MIN. MAX. AVE. Most DEFINE fields
- Constant fields using CNT.- Expressions that return with an arithmetic or character string
value
Aggregates that DO NOT get Translated to SQL: COUNT with MISSING=ON Multi-verb Requests
Aggregation can be turned off via the following command:SQL <engine> SET OPTIMIZATION NO AGGR
4. Aggregation Optimization – Optional
11
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
12
Trace Facility
1. STMTRACE – Statement Trace
2. SQLAGGR – SQL Aggregate Trace
3. SQLDI – SQL Details Trace
4. SQLCALL – See All Trace
Available Traces
12
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
13
Trace Facility
All SQL & DDL code All generated SQL code – SELECTs & WHEREs All SQL Data Definition Language (DDL) for CREATEs Output can be saved & Run in native SQL
Pre- FOCUS 7.2 = FSTRACE4
1. STMTRACE
13
2. SQLAGGR
JOIN, SORT & Aggregate Optimization Analysis Last optimization level completed
Best = “AGGREGATION DONE…”
Pre-FOCUS 7.2 = FSTRACE3
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
14
Trace Facility
Shows All the details SQL Statements DB2 Return Codes (RC) COMMIT & ROLLBACK Commands SQL Cursor Operations Works with ALL FOCUS Requests
Pre-FOCUS 7.2 = FSTRACE
3. SQLDI
14
4. SQLCALL
Shows All Commands & Data exchange between
Physical & Logical Layers of the Data Adaptor
New with FOCUS 7.2
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
15
Basic Steps to Optimize FOCUS/DB2 Code
1. Set up Job for Traces
2. Restrict Record Retrieval
3. Pick your Trace Levels
4. Activate Selected Traces
5. Review Trace Output – Optimization Levels & SQL Code
Did you make it to “AGGREGATION DONE…”?
If not – Modify your FOCUS code and try again
Optimization Steps:
15
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
16
Optimization Steps
Setup Trace file – FSTRACE
Pre-Allocate FSTRACE & Reference in FOCUS JCL:
//FSTRACE DD DSN=<file name>,// DCB=(LRECL=80,BLKSIZE=80,RECFM=F),// VOL=SER=<pack name>,SPACE=(CYL,(1,5,5)),// DISP=(NEW,CATLG,DELETE),UNIT=SYSALLDA
And in your job...
//FSTRACE DD DSN=<file name>,DISP=SHR
SYSOUT:
//FSTRACE DD SYSOUT=*, DCB=(LRECL=80, BLKSIZE=80,RECFM=F)
1. Setup Job for Traces
16
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
17
Optimization Steps
Eliminate Database Retrieval:
SET XRETRIEVAL = OFF
Limit the Number of Rows Being Read:
YES - IF READLIMIT EQ 100
NO - IF RECORDLIMIT EQ 100
2. Restrict Data Retrieval
17
3. Pick Your Trace Levels
Recommended levels are:
STMTRACE & SQLAGGR
Use only if absolutely necessary:
SQLDI & SQLCALL
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
18
Optimization Steps
Start the Trace Facility
SET TRACEUSER = ON
SET TRACEOFF = ALL
Activate the Requested Traces
SET TRACEON=SQLAGGR/DB2/FSTRACE
SET TRACEON=STMTRACE/DB2/FSTRACE
Check Trace Levels:
SET TRACEON = ?
Execute your FOCUS/DB2 request
4. Activate Selected Traces
18
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
19
Optimization Steps
You should try for only one SELECT statement
There should be WHEREs
You should see “AGGREGATION DONE…”
If not – return to & try again!
5. Review Trace Output
19
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2009 CIGNA
FOCUS/DB2 Example:Single TABLE
“CIGNA” and the “Tree of Life” logo are registered service marks of CIGNA Intellectual Property, Inc., licensed for use by CIGNA Corporation and its operating subsidiaries. All products and services are provided exclusively by such operating subsidiaries and not by CIGNA Corporation. Such operating subsidiaries include International Rehabilitation Associates, Inc. (Intracorp), CIGNA Behavioral Health, Inc., vielife Limited, Connecticut General Life Insurance Company and HMO subsidiaries of CIGNA Health Corporation. 000000 00/09 © 2010 CIGNA
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
21
FOCUS/DB2 Optimization - Setup
-* TURN OFF DATA RETRIEVAL SET XRETRIEVAL = OFF-* ACTIVATE THE TRACE FACILITY SET TRACEUSER=ON SET TRACEOFF=ALL -* ACTIVATE SPECIFIC TRACES SET TRACEON=SQLAGGR/DB2/FSTRACE SET TRACEON=STMTRACE/DB2/FSTRACE -* CHECK TRACE LEVEL SET TRACEON = ?
DB2OPT FOCEXEC
21
DB2OPT JCL
Allocate all the Required FOCUS files and...//* ALLOCATE TRACE FILE //FSTRACE DD DSN=TEST.FOCUS.FSTRACE,DISP=SHR <- FSTRACE //*//SYSIN DD *EXEC DB2OPT <- Opt Set up EXEC <FOCEXEC to be optimized> FIN /* //
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
22
FOCUS/DB2 Optimization – Single DB2 TABLE
MASTER File Description - MFD
22
$$$ CREATED BY AUTODB2 ON 06/24/04 AT 11.47.33 BY XXXXFILENAME=HTRARGEL,SUFFIX=SQLDS,$SEGNAME='ARGUS_EL',SEGTYPE=S0,$FIELD=CUST_NUM ,CUST_NUM ,A4 ,A4 ,MISSING=OFF,$FIELD=CLNT_NUM ,CLNT_NUM ,A4 ,A4 ,MISSING=OFF,$FIELD=HMO_CD ,HMO_CD ,A3 ,A3 ,MISSING=OFF,$FIELD=MBR_END_DT ,MBR_END_DT ,A10 ,A10 ,MISSING=OFF,$ MANY MORE FIELDS...
Access File Description - AFD
$$$ CREATED BY AUTODB2 ON 06/24/04 AT 11.47.33 BY XXXX $$$ FILENAME=HTRARGEL,SUFFIX=SQLDS,$
SEGNAME='ARGUS_EL', TABLENAME='"WINDSOR2_DB2P"."GHTR"."ARGUS_ELIGIBILITY"',KEYS=04,WRITE=YES,KEYORDER=LOW,$
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
23
FOCUS/DB2 Optimization – Single DB2 TABLE
FOCEXEC
23
-SET &THISYY=EDIT(&DATEYYMD,'9999$$$$$$'); <- Date Setup
-SET &ENDYY=(&THISYY - 2);-SET &THISMM=EDIT(&DATEYYMD,'$$$$$01$$$');-SET &THISDD=EDIT(&DATEYYMD,'$$$$$$$$01');-SET &ENDDT8=(&ENDYY | &THISMM | &THISDD);-SET &ENDDT=EDIT(&ENDDT8,'9999-$01-$01');-* DEFINE FILE HTRARGEL
<- DEFINEs ACCT/A4 = EDIT(SUBACT_NO,'9999$$');TOSS3501/A1 = IF HMO_CD EQ '200' THEN 'Y' ELSE 'N';TOSS3502/A1 = IF ACCT FROM '0400' TO '0499' THEN 'Y' ELSE IF ACCT FROM '4800' TO '4999' THEN 'Y' ELSE 'N'; TOSS3503/A2 = TOSS3501|TOSS3502;TOSS350/A1 = IF TOSS3503 EQ 'YY' THEN 'Y' ELSE 'N';END -* TABLE FILE HTRARGEL <- TABLE RequestPRINT CUST_NUM CLNT_NUM HMO_CD SUBACT_NO MBR_END_DTIF CUST_NUM EQ '9999' IF CLNT_NUM NE '9999' IF TOSS350 EQ ‘Y'IF MBR_END_DT GE '&ENDDT' END
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
24
FOCUS/DB2 Optimization – Single DB2 TABLE
FSTRACE Output
24
(FOC2598) FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : TOSS350 (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON: (FOC2596) ONE OR MORE EXPRESSION(S) CAN NOT BE TRANSLATED TO SQL SELECT T1."CUST_NUM",T1."CLNT_NUM",T1."HMO_CD",T1."SUBACT_NO", T1."MBR_END_DT" FROM "WINDSOR2_DB2P"."GHTR"."ARGUS_ELIGIBILITY" T1 WHERE (T1."MBR_END_DT" >= '2008-01-01') AND (T1."CLNT_NUM" <> '9999') AND (T1."CUST_NUM" = '9999') FOR FETCH ONLY;
FSTRACE Translation
Record Selection Failed:(FOC2598) FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : TOSS350
Aggregation Failed:(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON: (FOC2596) ONE OR MORE EXPRESSION(S) CAN NOT BE TRANSLATED TO SQL
The SET’s Passed: WHERE (T1."MBR_END_DT" >= '2008-01-01')
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
25
FOCUS/DB2 Optimization – Single DB2 TABLE
Original FOCEXEC
25
DEFINE FILE HTRARGEL <- DEFINEs
ACCT/A4 = EDIT(SUBACT_NO,'9999$$');TOSS3501/A1 = IF HMO_CD EQ '200' THEN 'Y' ELSE 'N';TOSS3502/A1 = IF ACCT FROM '0400' TO '0499' THEN 'Y' ELSE IF ACCT FROM '4800' TO '4999' THEN 'Y' ELSE 'N'; TOSS3503/A2 = TOSS3501|TOSS3502;TOSS350/A1 = IF TOSS3503 EQ 'YY' THEN 'Y' ELSE 'N';END -* TABLE FILE HTRARGEL <- TABLEPRINT CUST_NUM CLNT_NUM HMO_CD SUBACT_NO MBR_END_DTIF CUST_NUM EQ '9999' IF CLNT_NUM NE '9999' IF TOSS350 EQ ‘Y‘ FIELD not translated to SQLIF MBR_END_DT GE '&ENDDT' END
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
26
FOCUS/DB2 Optimization – Single DB2 TABLE
Modified FOCEXEC
26
DEFINE FILE HTRARGEL
ACCT/A4 = EDIT(SUBACT_NO,'9999$$'); END -* TABLE FILE HTRARGEL PRINTCUST_NUM CLNT_NUM HMO_CD SUBACT_NO MBR_END_DT IF CUST_NUM EQ '9999'IF CLNT_NUM NE '9999'-* CHANGE THE DEFINE TO A WHEREWHERE (HMO_CD EQ '200') AND ((ACCT FROM '0400' TO '0499') OR (ACCT FROM '4800' TO
'4999'))IF MBR_END_DT GE '&ENDDT'
END
Changes:
Convert DEFINEs to WHEREs Make sure that you are using REAL fields
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
27
FOCUS/DB2 Optimization – Single DB2 TABLE
27
FSTRACE Output
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON: (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED SELECT T1."CUST_NUM",T1."CLNT_NUM",T1."HMO_CD",T1."SUBACT_NO", T1."MBR_END_DT" FROM "WINDSOR2_DB2P"."GHTR"."ARGUS_ELIGIBILITY" T1 WHERE (T1."MBR_END_DT" >= '2008-01-01') AND (T1."HMO_CD" = '200') AND (T1."CLNT_NUM" <> '9999') AND (T1."CUST_NUM" = '9999') AND (((SUBSTR(T1."SUBACT_NO", 1, 4)) BETWEEN '0400' AND '0499') OR ((SUBSTR(T1."SUBACT_NO", 1, 4)) BETWEEN '4800' AND '4999')) FOR FETCH ONLY;
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2009 CIGNA
FOCUS/DB2 Example:Multiple TABLEs
“CIGNA” and the “Tree of Life” logo are registered service marks of CIGNA Intellectual Property, Inc., licensed for use by CIGNA Corporation and its operating subsidiaries. All products and services are provided exclusively by such operating subsidiaries and not by CIGNA Corporation. Such operating subsidiaries include International Rehabilitation Associates, Inc. (Intracorp), CIGNA Behavioral Health, Inc., vielife Limited, Connecticut General Life Insurance Company and HMO subsidiaries of CIGNA Health Corporation. 000000 00/09 © 2010 CIGNA
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
29
FOCUS/DB2 Optimization – Multiple DB2 TABLEs
JOIN NDC_NUM IN HTRACLM TO ALL NDC_NUM IN HTRNDC AS JOIN1ENDJOIN CUST_NUM AND CLNT_NUM AND MBR_NO IN HTRACLMTO CUST_NUM AND CLNT_NUM AND MBR_NO IN HTRARGEL AS JOIN2ENDJOIN CLM_NUM IN HTRACLM TO CLM_NUM IN HTRCLSTF AS JOIN3END
JOIN Code:
29
Active JOINs:
? JOIN
JOINS CURRENTLY ACTIVE HOST CROSSREFERENCE
FIELD FILE TAG FIELD FILE TAG AS ALL WH----- ---- --- ----- ---- --- -- --- --NDC_NUM HTRACLM NDC_NUM HTRNDC JOIN1 Y N CUST_NUM HTRACLM CUST_NUM HTRARGEL JOIN2 N N CLM_NUM HTRACLM CLM_NUM HTRCLSTF JOIN3 N N
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
30
FOCUS/DB2 Optimization – Multiple DB2 TABLEs
JOIN Structure:
30
HOST
JOIN1 JOIN2 JOIN3
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
31
FOCUS/DB2 Optimization – Multiple DB2 TABLEs
HOST - HTRACLM $$$ CREATED BY AUTODB2 ON 07/29/04 AT 07.38.29 BY XXXX $$$ FILENAME=HTRACLM,SUFFIX=SQLDS,$ SEGNAME='CLAIM',TABLENAME='"GHTR"."CLAIM"', KEYS=01,WRITE=YES,KEYORDER=LOW,$
JOIN1 - HTRNDC $$$ CREATED BY AUTODB2 ON 09/28/04 AT 11.19.52 BY XXXX $$$ FILENAME=HTRNDC,SUFFIX=SQLDS,$ SEGNAME='NDC',TABLENAME='"GHTR"."NDC"', KEYS=01,WRITE=YES,KEYORDER=LOW,$
JOIN2 - HTRARGE $$$ CREATED BY AUTODB2 ON 06/24/04 AT 11.47.33 BY XXXX $$$ FILENAME=HTRARGEL,SUFFIX=SQLDS,$ SEGNAME='ARGUS_EL', TABLENAME='"WINDSOR2_DB2P"."GHTR"."ARGUS_ELIGIBILITY"', KEYS=04,WRITE=YES,KEYORDER=LOW,$
JOIN3 - HTRCLSTFSEGNAME= HTRCLSTF ,TABLENAME= 'GHTR."CLAIM_STAFF"‘, KEYS= 1 ,WRITE= YES ,$
Access File Descriptions - AFDs
31
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
32
FOCUS/DB2 Optimization – Multiple DB2 TABLEs
SET ALL=PASS SET ALL
-* DEFINE FILE HTRACLM
DEFINEs INGRD_CST_AMT/P9.2=IF CLNT_INGRD_CST_AMT GT 0 THEN CLNT_INGRD_CST_AMT ELSE INGRD_CST_AMT ;DISP_FEE/D9.2=IF CLNT_INGRD_CST_AMT GT 0 THEN CLNT_DISP_FEE_AMT ELSE DISP_FEE_AMT ;TOT_PD_AMT/P9.2=IF CLNT_INGRD_CST_AMT GT 0 THEN CLNT_TOT_PD_AMT ELSE TOT_PD_AMT ;FILL/A10=EDIT(FILL_DT,'9999-99-99') ;PAID/A10=EDIT(PAID_DT,'9999-99-99') ;MBR_NO/A12=IF CUST_NUM NE '0215' THEN MBR_NO ELSE IF NEW_MBR_NO EQ ' ' THEN MBR_NO ELSE NEW_MBR_NO ;EMP_ID/A9=EDIT(MBR_NO,'999999999$$$') ;END-* TABLE FILE HTRACLM
TABLE RequestLIST MBR_NO EMP_ID FILL_DT PAID_DT RX_ACCT_NUM INGRD_CST_AMT DISP_FEE TOT_PD_AMT CUST_NUM CLNT_NUM BY CLM_NUM BY HIGHEST 1 ARGUS_RUN_DT WHERE RX_ACCT_NUM IN ('3209348') AND (PAID_DT GE '2013-01-01') AND (PAID_DT LT '2018-01-01') AND PLAN_OF_BEN IN ('COMP3') AND FUNDNG_SVC_CD EQ '2'ON TABLE HOLD AS HOLD2 END
Original FOCEXEC
32
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
33
FOCUS/DB2 Optimization – Multiple DB2 TABLEs
(FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
(FOC2519) THE JOINED SEGMENTS RESIDE IN MORE THAN ONE NODE OR SUBSYSTEM
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED SELECT T1."CLM_NUM",T1."CUST_NUM",T1."CLNT_NUM",
T1."RX_ACCT_NUM",T1."PLAN_OF_BEN",T1."MBR_NO",
T1."NABP_PHARMCY_NUM",T1."FILL_DT",T1."PAID_DT",
T1."COPAY_COIN_AMT",T1."INGRD_CST_AMT",T1."AWP_INGRD_CST_AMT",
T1."DISP_FEE_AMT",T1."SALES_TAX_AMT",T1."TOT_PD_AMT",
T1."UNC_AMT",T1."NEW_MBR_NO",T1."FUNDNG_SVC_CD" FROM
"GHTR"."CLAIM" T1 WHERE (T1."FUNDNG_SVC_CD" = '2') AND
(T1."PLAN_OF_BEN" = 'COMP3') AND (T1."PAID_DT" < '2011-01-01')
AND (T1."PAID_DT" >= '2006-01-01') AND (T1."RX_ACCT_NUM" =
'3209348') FOR FETCH ONLY;
SELECT T2."MBR_FRST_NM",T2."MBR_LAST_NM",T2."MBR_BRTH_DT",
T2."ARGUS_RUN_DT" FROM
"WINDSOR2_DB2P"."GHTR"."ARGUS_ELIGIBILITY" T2 WHERE
(T2."CUST_NUM" = ?) AND (T2."CLNT_NUM" = ?) AND (T2."MBR_NO" =
?) FOR FETCH ONLY;
SELECT T3."CLNT_INGRD_CST_AMT",T3."CLNT_DISP_FEE_AMT", T3."CLNT_TOT_PD_AMT" FROM GHTR."CLAIM_STAFF" T3 WHERE (T3."CLM_NUM" = ?) FOR FETCH ONLY;
FSTRACE Output - #1
33
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
34
FOCUS/DB2 Optimization – Multiple DB2 TABLEs
(FOC2519) THE JOINED SEGMENTS RESIDE IN MORE THAN ONE NODE OR SUBSYSTEM
HOST - HTRACLM SEGNAME='CLAIM',TABLENAME='"GHTR"."CLAIM"', KEYS=01,WRITE=YES,KEYORDER=LOW,$
JOIN1 - HTRNDC SEGNAME='NDC',TABLENAME='"GHTR"."NDC"', KEYS=01,WRITE=YES,KEYORDER=LOW,$
JOIN2 - HTRARGE SEGNAME='ARGUS_EL', TABLENAME='"WINDSOR2_DB2P"."GHTR"."ARGUS_ELIGIBILITY"', KEYS=04,WRITE=YES,KEYORDER=LOW,$
JOIN3 - HTRCLSTFSEGNAME= HTRCLSTF ,TABLENAME= 'GHTR."CLAIM_STAFF"‘, KEYS= 1 ,WRITE= YES ,$
1st Change - AFDs
34
KEYORDER=LOW, $
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
35
FOCUS/DB2 Optimization – Multiple DB2 TABLEs
(FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S): (FOC2513) OUTER JOIN (ALL=ON or ALL=PASS) CANNOT BE PASSED (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON: (FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLEDSELECT T1."CLM_NUM",T1."CUST_NUM",T1."CLNT_NUM",
T1."RX_ACCT_NUM",T1."PLAN_OF_BEN",T1."MBR_NO",
T1."NABP_PHARMCY_NUM",T1."FILL_DT",T1."PAID_DT",
T1."COPAY_COIN_AMT",T1."INGRD_CST_AMT",T1."AWP_INGRD_CST_AMT",
T1."DISP_FEE_AMT",T1."SALES_TAX_AMT",T1."TOT_PD_AMT",
T1."UNC_AMT",T1."NEW_MBR_NO",T1."FUNDNG_SVC_CD" FROM
"GHTR"."CLAIM" T1 WHERE (T1."FUNDNG_SVC_CD" = '2') AND
(T1."PLAN_OF_BEN" = 'COMP3') AND (T1."PAID_DT" < '2011-01-01')
AND (T1."PAID_DT" >= '2006-01-01') AND (T1."RX_ACCT_NUM" =
'3209348') FOR FETCH ONLY;
SELECT T2."MBR_FRST_NM",T2."MBR_LAST_NM",T2."MBR_BRTH_DT",
T2."ARGUS_RUN_DT" FROM "GHTR"."ARGUS_ELIGIBILITY" T2 WHERE
(T2."CUST_NUM" = ?) AND (T2."CLNT_NUM" = ?) AND (T2."MBR_NO" =
?) FOR FETCH ONLY;
SELECT T3."CLNT_INGRD_CST_AMT",T3."CLNT_DISP_FEE_AMT", T3."CLNT_TOT_PD_AMT" FROM GHTR."CLAIM_STAFF" T3 WHERE (T3."CLM_NUM" = ?) FOR FETCH ONLY;
FSTRACE Output - #2
35
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
36
FOCUS/DB2 Optimization – Multiple DB2 TABLEs
(FOC2513) OUTER JOIN (ALL=ON or ALL=PASS) CANNOT BE PASSED
Original Code: SET ALL=PASS
Optimized Code: Delete the line or SET ALL=OFF/ON
The ALL setting handles missing segment instances in a report.
OFF = Always tries to Optimizes - DEFAULT
ON = Outer JOIN – Missing cross-referenced rows are processed – R 7.0.9
SET <SQL engine> SET SQLJOIN OUTER ON/OFF
PASS = Never Optimizes
2nd Change – ALL Setting
36
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
37
FOCUS/DB2 Optimization – Multiple DB2 TABLEs
(FOC2525) FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: ARGUS_EL(FOC2509) RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE(FOC2524) JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON: (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED SELECT T1."CLM_NUM",T1."CUST_NUM",T1."CLNT_NUM", T1."RX_ACCT_NUM",T1."PLAN_OF_BEN",T1."MBR_NO",T1."FILL_DT", T1."PAID_DT",T1."INGRD_CST_AMT",T1."DISP_FEE_AMT", T1."TOT_PD_AMT",T1."NEW_MBR_NO",T1."FUNDNG_SVC_CD", T2."ARGUS_RUN_DT",T3."CLNT_INGRD_CST_AMT", T3."CLNT_DISP_FEE_AMT",T3."CLNT_TOT_PD_AMT" FROM "GHTR"."CLAIM" T1,"GHTR"."ARGUS_ELIGIBILITY" T2,"GHTR"."CLAIM_STAFF" T3 WHERE (T2."CUST_NUM" = T1."CUST_NUM") AND (T2."CLNT_NUM" = T1."CLNT_NUM") AND (T2."MBR_NO" = T1."MBR_NO") AND (T3."CLM_NUM" = T1."CLM_NUM") AND (T1."FUNDNG_SVC_CD" = '2') AND (T1."PLAN_OF_BEN" = 'COMP3') AND (T1."PAID_DT" < '2018-01-01') AND (T1."PAID_DT" >= '2013-01-01') AND (T1."RX_ACCT_NUM" = '3209348') ORDER BY T1."CLM_NUM", T2."ARGUS_RUN_DT" DESC FOR FETCH ONLY;
FSTRACE Output - #3
37
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
38
FOCUS/DB2 Optimization – Multiple DB2 TABLEs
(FOC2525) FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: ARGUS_EL(FOC2509) RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE(FOC2524) JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
3rd Change – AFD
38
HOST - HTRACLM SEGNAME='CLAIM',TABLENAME='"GHTR"."CLAIM"', KEYS=01,WRITE=YES,KEYORDER=LOW,$
JOIN1 - HTRNDC SEGNAME='NDC',TABLENAME='"GHTR"."NDC"', KEYS=01,WRITE=YES,KEYORDER=LOW,$
JOIN2 - HTRARGEL SEGNAME='ARGUS_EL', TABLENAME='"GHTR"."ARGUS_ELIGIBILITY"', KEYS=04,WRITE=YES,KEYORDER=LOW,$
JOIN3 - HTRCLSTFSEGNAME= HTRCLSTF ,TABLENAME= ‘”GHTR”."CLAIM_STAFF"‘, KEYS= 01 ,WRITE= YES , KEYORDER=LOW,$
Too Many Keys – Needs to be 02
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
39
FOCUS/DB2 Optimization – Multiple DB2 TABLEs
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON: (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED SELECT T1."CLM_NUM",T1."CUST_NUM",T1."CLNT_NUM", T1."RX_ACCT_NUM",T1."PLAN_OF_BEN",T1."MBR_NO",T1."FILL_DT", T1."PAID_DT",T1."INGRD_CST_AMT",T1."DISP_FEE_AMT", T1."TOT_PD_AMT",T1."NEW_MBR_NO",T1."FUNDNG_SVC_CD", T2."ARGUS_RUN_DT",T3."CLNT_INGRD_CST_AMT", T3."CLNT_DISP_FEE_AMT",T3."CLNT_TOT_PD_AMT" FROM "GHTR"."CLAIM" T1,"GHTR"."ARGUS_ELIGIBILITY" T2,"GHTR"."CLAIM_STAFF" T3 WHERE (T2."CUST_NUM" = T1."CUST_NUM") AND (T2."CLNT_NUM" = T1."CLNT_NUM") AND (T2."MBR_NO" = T1."MBR_NO") AND (T3."CLM_NUM" = T1."CLM_NUM") AND (T1."FUNDNG_SVC_CD" = '2') AND (T1."PLAN_OF_BEN" = 'COMP3') AND (T1."PAID_DT" < '2018-01-01') AND (T1."PAID_DT" >= '2013-01-01') AND (T1."RX_ACCT_NUM" = '3209348') ORDER BY T1."CLM_NUM",
T2."ARGUS_RUN_DT" DESC FOR FETCH ONLY;
FSTRACE Output - #4 – Optimized
39
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2010 CIGNA
40
FOCUS/DB2 & FOCUS Optimization Documentation
FOCUS Documentation is Available at: http://documentation.informationbuilders.com/masterindex/doc.asp
Current FOCUS/DB2 Documentation:
FOCUS for S/390 Relational Data Adapter - User’s Manual - Version 7.2
Document Number: DN1001155.1002
Current Optimization Documentation:
FOCUS for S/390 PowerBook - Version 7.0
Document Number: DN1001134.0401
Confidential, unpublished property of CIGNA. Do not duplicate or distribute. Use and distribution limited solely to authorized personnel. © 2009 CIGNA
Questions & Answers
“CIGNA” and the “Tree of Life” logo are registered service marks of CIGNA Intellectual Property, Inc., licensed for use by CIGNA Corporation and its operating subsidiaries. All products and services are provided exclusively by such operating subsidiaries and not by CIGNA Corporation. Such operating subsidiaries include International Rehabilitation Associates, Inc. (Intracorp), CIGNA Behavioral Health, Inc., vielife Limited, Connecticut General Life Insurance Company and HMO subsidiaries of CIGNA Health Corporation. 000000 00/09 © 2010 CIGNA