1 / 30

Unintended Consequences or…. Why Did FOCUS Do THAT !

Unintended Consequences or…. Why Did FOCUS Do THAT !. Walter Brengel Information Builders FUN 2008. Agenda. -SET &ECHO=ALL; Dialogue Manager Vs Non Dialogue Manager CHECK FILE HOLD -READ -TYPE FILEDEF -GOTO Processing &ECHO On Steroids STYLING SQL TRACES.

huey
Download Presentation

Unintended Consequences or…. Why Did FOCUS Do THAT !

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. Unintended Consequences or….Why Did FOCUS Do THAT! Walter Brengel Information Builders FUN 2008

  2. Agenda • -SET &ECHO=ALL; • Dialogue Manager Vs Non Dialogue Manager • CHECK FILE HOLD • -READ • -TYPE • FILEDEF • -GOTO Processing • &ECHO On Steroids • STYLING • SQL TRACES

  3. Execution ControlThe Report: This Is The Report We Would Like. Salaries greater than 54100.00 DEPT LASTNAME FIRSTNAME SALARY ---- -------- --------- ------ ACCOUNTING SANCHEZ EVELYN $83,000.00 SOPENA BEN $79,000.00 WANG JOHN $62,500.00 CUSTOMER SUPPORT WHITE VERONICA $62,500.00 HIRSCHMAN ROSE $62,500.00 FERNSTEIN ERWIN $54,100.00 MARKETING VALINO DANIEL $55,500.00 BELLA MICHAEL $62,500.00 ADAMS RUTH $62,500.00 PATEL DORINA $55,500.00 CVEK MARCUS $62,500.00 GOTLIEB CHRIS $58,800.00 PERSONNEL NOZAWA JIM $80,500.00 SALES CASSANOVA LOIS $70,000.00 ADDAMS PETER $54,100.00 LASTRA KAREN $115,000.00

  4. Execution ControlThe Report: Our Code: TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE END -READ MYSAVE &SKIP.A16. &SALARY.D9. TABLE FILE EMPDATA "Salaries greater than &SALARY </1" PRINT LN FN SALARY BY DEPT WHERE SALARY GE &SALARY END

  5. TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE END -RUN -READ MYSAVE &SKIP.A16. &SALARY.D9. Add A –RUN Before The –READ Execution ControlThe Report: MYSAVE &SKIP.A16. &SALARY.D9. ERROR AT OR NEAR LINE 14 IN PROCEDURE setupforsummit (FOC339) DIALOGUE MANAGER -READ FAILED: CHECK FILEDEF OR ALLOCATION FOR: -READ MYSAVE &SKIP.A16. &SALARY.D9. Why Do We Get An Error ? TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE END -READ MYSAVE &SKIP.A16. &SALARY.D9. How Do We Fix It ?

  6. Execution ControlThe Report: -READ MYSAVE &SKIP.A16. &SALARY.D9. ERROR AT OR NEAR LINE 33 IN PROCEDURE setupforsummit (FOC299) UNRECOGNIZED FORMAT OF AMPER VARIABLE IN -READ: -READ MYSAVE &SKIP.A16. &SALARY.D9. Why Do We Get This Error ? First Let’s See What We Are Getting -SET &ECHO=ALL: TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE END -RUN -READ MYSAVE &SKIP.A16. &SALARY.D9. TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE END -RUN 0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1 -READ MYSAVE &SKIP.A16. &SALARY.D9. 0 ERROR AT OR NEAR LINE 33 IN PROCEDURE Setupforsummit (FOC299) UNRECOGNIZED FORMAT OF AMPER VARIABLE IN -READ: -READ MYSAVE &SKIP.A16. &SALARY.D9. How Do We Debug It ?

  7. Execution ControlThe Report: -READ MYSAVE &SKIP.A16. &SALARY.D9. ERROR AT OR NEAR LINE 33 IN PROCEDURE setupforsummit (FOC299) UNRECOGNIZED FORMAT OF AMPER VARIABLE IN -READ: -READ MYSAVE &SKIP.A16. &SALARY.D9. TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE END -RUN -READ MYSAVE &SKIP.A16. -EXIT -* &|SALARY.A9. TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE END -RUN -READ MYSAVE &SKIP.A16. &SALARY.D9. Do We Get The Error? No – Not That Format Yes – It Is That Format How Do We Find It ? 0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1 -READ MYSAVE &SKIP.A16. -*&|SALARY.D9. Extra Credit!! Why &|SALARY???

  8. Execution ControlThe Report: -* ECHO OUTPUT TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE END -RUN 0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1 -READ MYSAVE &SKIP.A16. &SALARY.A9. TABLE FILE EMPDATA "Salaries greater than PETER </1" PRINT LN FN SALARY BY DEPT WHERE SALARY GE PETER END -EXIT TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE END -RUN -READ MYSAVE &SKIP.A16. &SALARY.D9. • Looks Like The Problem Is With The SALARY! • &SALARY.D9 ????? D9?? • Only Allowed Formats Are: A & I • And I Is Read As A • Lets Change It To A9 0 ERROR AT OR NEAR LINE 23 IN PROCEDURE setupforsummit (FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: PETER BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT

  9. Execution ControlThe Report: TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE END -RUN -READ MYSAVE &SKIP.A16. &SALARY.A9. TABLE FILE EMPDATA "Salaries greater than &SALARY </1" PRINT LN FN SALARY BY DEPT WHERE SALARY GE &SALARY END -* FILE NAME CHKMAST -* Make sure you use your hold file -* name or use an &VARIABLE CHECK FILE MYSAVE HOLD AS MYCHK TABLE FILE MYCHKPRINT FIELDNAME ALIAS FORMAT ACTUAL END -INCLUDE CHKMAST PAGE 1 FIELDNAME ALIAS FORMAT ACTUAL LASTNAME E01 A15 A016 FIRSTNAME E02 A10 A012 FIRSTNAME E03 A1 A004 SALARY E04 D12.2M D008 SALARY E05 D9 D008 • Where Is SALARY Coming From? • First TABLE Output – MYSAVE • MYSAVE Is The Result Of HOLD • Let’s Check the HOLD Master • Copy And –INCLUDE CHKMAST (Shown above)

  10. Execution ControlThe Report: • What’s Wrong With This Picture? • Why Two Firstnames & Two Salaries • How Do We Correct This? • Correct &Skip • What Is The Length Of &Skip • HOLDLIST=PRINTONLY SET HOLDLIST=ALL (Default) The Output: PAGE 1 FIELDNAME ALIAS FORMAT ACTUAL LASTNAME E01 A15 A016 FIRSTNAME E02 A10 A012 FIRSTNAME E03 A1 A004 SALARY E04 D12.2M D008 SALARY E05 D9 D008 SET HOLDLIST=PRINTONLY The Output: PAGE 1 FIELDNAME ALIAS FORMAT ACTUAL LASTNAME E01 A15 A016 FIRSTNAME E02 A1 A004 SALARY E03 D9 D008 TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE END -RUN -READ MYSAVE &SKIP.A40. &SALARY.A8. TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE ON TABLE SET HOLDLIST PRINTONLY END -RUN -READ MYSAVE &SKIP.A20. &SALARY.A8.

  11. Execution ControlThe Report: TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE ON TABLE SET HOLDLIST PRINTONLY END -RUN -READ MYSAVE &SKIP.A20. &SALARY.A8. TABLE FILE EMPDATA "Salaries greater than &SALARY </1" PRINT LN FN SALARY BY DEPT WHERE SALARY GE &SALARY ENDERROR AT OR NEAR LINE 20 IN PROCEDURE runcode FOCEXEC * (FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: Çj?@ BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT • Lets Look At The &ECHO Output • TABLE FILE EMPDATA • PRINT • FIRSTNAME/A1 • SALARY/D9 • BY LASTNAME • IF LN EQ 'ADDAMS' • ON TABLE HOLD AS MYSAVE • ON TABLE SET HOLDLIST PRINTONLY • END • -RUN • NUMBER OF RECORDS IN TABLE= 1 LINES= 1 • -READ MYSAVE &SKIP.A20. &SALARY.A8. • TABLE FILE EMPDATA • "Salaries greater than Çj?@ </1" • PRINT LN FN SALARY • BY DEPT • WHERE SALARY GE Çj?@ • END • ERROR AT OR NEAR LINE 20 IN PROCEDURE runcode FOCEXEC * • (FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: Çj?@ • BYPASSING TO END OF COMMAND • (FOC009) INCOMPLETE REQUEST STATEMENT • What’s Happening Now? • How Can We Find Out? • -TYPE

  12. Execution ControlThe Report: TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE ON TABLE SET HOLDLIST PRINTONLY END -RUN -READ MYSAVE &SKIP.A20. &SALARY.A8. -TYPE THE VALUE FOR SKIP IS:&SKIP -TYPE THE VALUE FOR SALARY IS: &SALARY -EXIT TABLE FILE EMPDATA "Salaries greater than &SALARY </1" PRINT LN FN SALARY BY DEPT WHERE SALARY GE &SALARY END THE VALUE FOR SKIP IS:ADDAMS P THE VALUE FOR SALARY IS: Çj?@ • What Is Going On? • What Format Is The HOLD File Saved In? • How Can The HOLD Data Be Saved So That The Numeric Values Are Readable?

  13. Execution ControlThe Report: ON TABLE HOLD FORMAT ALPHA ON TABLE SAVE TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE HOLD AS MYSAVE FORMAT ALPHA ON TABLE SET HOLDLIST PRINTONLY END -INCLUDE CHKMAST -EXIT TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE SAVE AS MYSAVE ON TABLE SET HOLDLIST PRINTONLY END -EXIT PAGE 1 FIELDNAME ALIAS FORMAT ACTUAL LASTNAME E01 A15 A015 FIRSTNAME E02 A1 A001 SALARY E03 D9 A009 ALPHANUMERIC RECORD NAMED MYSAVE FIELDNAME ALIAS FORMAT LENGTH LASTNAME LN A15 15 FIRSTNAME A1 1 SALARY D9 9 TOTAL 25

  14. Execution ControlThe Report: TABLE FILE EMPDATA PRINT FIRSTNAME/A1 SALARY/D9 BY LASTNAME IF LN EQ 'ADDAMS' ON TABLE SAVE AS MYSAVE ON TABLE SET HOLDLIST PRINTONLY END -RUN -READ MYSAVE &SKIP.A16. &SALARY.A9. TABLE FILE EMPDATA "Salaries greater than &SALARY </1" PRINT LN FN SALARY BY DEPT WHERE SALARY GE &SALARY END Salaries greater than 54100.00 DEPT LASTNAME FIRSTNAME SALARY ---- -------- --------- ------ ACCOUNTING SANCHEZ EVELYN $83,000.00 SOPENA BEN $79,000.00 WANG JOHN $62,500.00 CUSTOMER SUPPORT WHITE VERONICA $62,500.00 HIRSCHMAN ROSE $62,500.00 FERNSTEIN ERWIN $54,100.00 MARKETING VALINO DANIEL $55,500.00 BELLA MICHAEL $62,500.00 ADAMS RUTH $62,500.00 PATEL DORINA $55,500.00 CVEK MARCUS $62,500.00 GOTLIEB CHRIS $58,800.00 PERSONNEL NOZAWA JIM $80,500.00 SALES CASSANOVA LOIS $70,000.00 ADDAMS PETER $54,100.00 LASTRA KAREN $115,000.00

  15. Execution ControlFILEDEF Verification -SET &ECHO=ALL; FILEDEF ALLCRS DISK C:\IBI\APPS\DEBUGGGING\ALLCRS.FTM TABLE FILE ALLCRS PRINT * END &ECHO Output: FILEDEF ALLCRS DISK C:\IBI\APPS\DEBUGGGING\ALLCRS.FTM TABLE FILE ALLCRS PRINT * END -RUN NUMBER OF RECORDS IN TABLE= 0 LINES= 0 • Somethings wrong! • Let’s see if we can –READ the file

  16. Execution ControlFILEDEF Verification -SET &ECHO=ALL; FILEDEF ALLCRS DISK C:\IBI\APPS\DEBUGGGING\ALLCRS.FTM -RUN -READ ALLCRS &LINE.A1. -TYPE &LINE -EXIT TABLE FILE ALLCRS PRINT * END -RUN TABLE FILE ALLCRS PRINT * END -READ ALLCRS &LINE.A10. 0 ERROR AT OR NEAR LINE 5 IN PROCEDURE FILEDEF FOCEXEC * 0 ERROR AT OR NEAR LINE 8 IN PROCEDURE FILEDEF FOCEXEC * (FOC295) A VALUE IS MISSING FOR: &LINE . • Let’s Look At The FILEDEF Oops!! Somebody Can’t Type! C:\IBI\APPS\DEBUGGGING\ALLCRS.FTM

  17. -SET &ECHO=ALL; FILEDEF ALLCRS DISK C:\IBI\APPS\DEBUGGING\ALLCRS.FTM -RUN TABLE FILE ALLCRS PRINT * END Ah! Success….. PAGE 1 IPIN COURSESTART COURSECODE EXPENSES ---- ----------- ---------- -------- 000000410 91/02/11 EDP690 3,100.00 000000380 91/06/05 UNI780 3,350.00 000000370 91/06/01 UMI710 2,050.00 000000360 90/11/05 EDP690 3,000.00 000000350 90/09/28 SSI220 1,850.00 000000350 89/07/10 MC90 1,730.00 Execution Control FILEDEF Verification -SET &ECHO=ALL; FILEDEF ALLCRS DISK C:\IBI\APPS\DEBUGGING\ALLCRS.FTM -RUN -READ ALLCRS &LINE.A10. -TYPE &LINE -EXIT • FILEDEF ALLCRS DISK C:\IBI\APPS\DEBUGGING\ALLCRS.FTM • -RUN • -READ ALLCRS &LINE.A10. • -TYPE 0000004109 0000004109 • -EXIT • Well Did It Work??

  18. Execution ControlUsing -GOTO • Use –GOTO For: • Transfer Of Control To A Specified Label. • If Dialogue Manager Finds The Label,processing Continues With The Line Following It. • If Dialogue Manager Does Not Find The Label, Processing Ends And An Error Message Is Displayed. • ‑Goto Label • ‑Label • Label - Is A User‑defined Name Of Up To 12 Characters That Specifies The Target Of The ‑GOTO Action. • Do Not Use • Embedded Blanks • The Name Of Any Other Dialogue Manager Command Except ‑Quit Or ‑Exit. • Words That Can Be Confused With Functions, Arithmetic And Logical Operations,

  19. Execution ControlThe Report: -*GOTO RPT2 TABLE FILE CAR "THIS IS RPT1" PRINT CAR END -*EXIT -RPT2 TABLE FILE EMPDATA "THIS IS RPT2" PRINT LN END -*EXIT -RPT3 TABLE FILE CAR "THIS IS RPT3" PRINT COUNTRY END -*EXIT -RPT4 TABLE FILE EMPDATA "THIS IS RP41" PRINT FN END -GOTO RPT2 TABLE FILE CAR "THIS IS RPT1" PRINT CAR END -*EXIT -RPT2 TABLE FILE EMPDATA "THIS IS RPT2" PRINT LN END -EXIT -RPT3 TABLE FILE CAR "THIS IS RPT3" PRINT COUNTRY END -*EXIT -RPT4 TABLE FILE EMPDATA "THIS IS RP41" PRINT FN END PAGE 1 THIS IS RPT2 LASTNAME --------------- VALINO BELLA CASSANOVA ADAMS

  20. Execution Control&ECHO On Steroids -SET DEFECHO=ON; • SET DEFECHO Command Enables You To Specify A Default Value For &ECHO That Spans All Procedures Executed In An Application. • Any FOCEXEC Executed That Does Not Explicitly Establish A Value For &ECHO Uses The Default Value Established By The SET DEFECHO Command. • You Can Explicitly Control The Value Of &ECHO For An Individual Procedure By Either: • Passing A Value On The EX Command • Issuing A -SET Command For &ECHO • Issuing A -DEFAULT Command For &ECHO. • SET DEFECHO = {OFF|ON|ALL} • where: • OFF - Establishes OFF as the default value for &ECHO. OFF is the default value. • ON - Establishes ON as the default value for &ECHO. • ALL - Establishes ALL as the default value for &ECHO.

  21. Execution ControlREADLIMIT and RECORDLIMIT • READLIMIT Is Synonymous With RECORDLIMIT Except As Follows: • Oracle Data Adapter Passes Readlimit To The Oracle As; • SQL WHERE ROWNUM <=N Clauses. • Rownum Is A Pseudo Column Allowing Oracle To Limit The Number Of Rows Retrieved And Returned. • DB2, Readlimit Appends A “Fetch First N Rows Only” To The Generated SQL. • See Ibm DB2 SQL Reference Manual. • Readlimit And Recordlimit Reduce Communication Traffic, Terminal And Disk I/Os. • Search Limit Tests Are Helpful When: • Testing A New Master File.

  22. Execution ControlStyling DEFINE FILE CAR BLANK/A1=' '; END TABLE FILE CAR SUM COMPUTE PROFIT/D6=RETAIL_COST - DEALER_COST; BLANK AS '' RETAIL_COST DEALER_COST BY COUNTRY ON TABLE PCHOLD FORMAT PDF ON TABLE SET STYLE * TYPE=REPORT,COLUMN=RETAIL_COST,COLOR=BLUE,$ ENDSTYLE END PAGE 1 COUNTRY PROFIT RETAIL_COST DEALER_COST ------- ------ ----------- ----------- ENGLAND 7,466 45,319 37,853 FRANCE 979 5,610 4,631 ITALY 9,830 51,065 41,235 JAPAN 966 6,478 5,512 W GERMANY 10,169 64,732 54,563

  23. Execution Control Styling DEFINE FILE CAR BLANK/A1=' '; END TABLE FILE CAR SUM COMPUTE PROFIT/D6=RETAIL_COST - DEALER_COST; BLANK AS '' RETAIL_COST DEALER_COST BY COUNTRY ON TABLE PCHOLD FORMAT PDF ON TABLE SET STYLE * TYPE=REPORT,COLUMN=N3,COLOR=BLUE,$ ENDSTYLE END BACKCOLOR=RED PAGE 1 COUNTRY PROFIT RETAIL_COST DEALER_COST ------- ------ ----------- ----------- ENGLAND 7,466 45,319 37,853 FRANCE 979 5,610 4,631 ITALY 9,830 51,065 41,235 JAPAN 966 6,478 5,512 W GERMANY 10,169 64,732 54,563

  24. Execution Control Styling DEFINE FILE CAR BLANK/A1=' '; END TABLE FILE CAR SUM COMPUTE PROFIT/D6=RETAIL_COST - DEALER_COST; BLANK AS '' RETAIL_COST DEALER_COST BY COUNTRY ON TABLE PCHOLD FORMAT PDF ON TABLE SET STYLE * TYPE=REPORT,COLUMN=N3,COLOR=BLUE,BACKCOLOR=RED,$ ENDSTYLE END PAGE 1 COUNTRY PROFIT RETAIL_COST DEALER_COST ------- ------ ----------- ----------- ENGLAND 7,466 45,319 37,853 FRANCE 979 5,610 4,631 ITALY 9,830 51,065 41,235 JAPAN 966 6,478 5,512 W GERMANY 10,169 64,732 54,563

  25. Execution Control Styling DEFINE FILE CAR BLANK/A1=' '; END TABLE FILE CAR SUM COMPUTE PROFIT/D6=RETAIL_COST - DEALER_COST; BLANK AS '' RETAIL_COST DEALER_COST BY COUNTRY ON TABLE PCHOLD FORMAT PDF ON TABLE SET STYLE * TYPE=REPORT,COLUMN=N4, COLOR=BLUE,$ ENDSTYLE END PAGE 1 COUNTRY PROFIT RETAIL_COST DEALER_COST ------- ------ ----------- ----------- ENGLAND 7,466 45,319 37,853 FRANCE 979 5,610 4,631 ITALY 9,830 51,065 41,235 JAPAN 966 6,478 5,512 W GERMANY 10,169 64,732 54,563

  26. Execution ControlSQL Traces • SQLDI - • Records SQL statements. • RDBMS return codes. • COMMIT and ROLLBACK commands. • SQL cursor operations such as PREPARE, OPEN, FETCH, and CLOSE. • Used with all FOCUS report requests, and native SQL commands. • Formerly FSTRACE. • SQLAGGR • Displays data adapter‑to‑RDBMS aggregation and join analysis. • Used only for FOCUS reporting operations such as TABLE, GRAPH, and MATCH FILE. • If the data adapter is able to pass all join, sort, and aggregation operations, the SQLAGGR trace is not populated. The message "AGGREGATION DONE..." appears. • Formerly FSTRACE3.

  27. Execution Control SQL Traces • STMTRACE • Records SQL SELECT statements generated by the data adapter for FOCUS report requests. • MODIFY procedures, or Direct SQL Passthru SELECT requests. It also records the SQL Data. • Definition Language (DDL) statements generated by the CREATE FILE command. • Display the trace information online. • Store it in a file or sequential data set. • The data adapter terminates its generated SQL SELECT statements with a semicolon. • You can submit them to the RDBMS for processing, interactively or in batch. • Use STMTRACE for: • Debugging. • Performance tuning. • Capturing SQL Data Definition and Data Manipulation statements to reuse. • Formerly FSTRACE4. • SQLCALL • Traces commands and data exchange between the physical and the logical layers of the data adapter. • You can activate all or any combination of these traces during your FOCUS session or in batch. • You can display the results online or store them in a file or sequential data set.

  28. Execution Control SQL Traces • SET TRACEOFF = ALL • SET TRACEON =SQLAGGR//CLIENT • SET TRACEON =STMTRACE//CLIENT • SET TRACEUSER = ON AGGREGATION DONE ... 07.27.38 AE SELECT T1."OrderID", SUM(T1."Freight"), SUM(T1."Freight") FROM 07.27.38 AE Northwind.dbo.Orders T1 GROUP BY T1."OrderID" ORDER BY 07.27.38 AE T1."OrderID"; • SET TRACEOFF = ALL • SET TRACEON = SQLAGGR//CLIENT • SET TRACEON = STMTRACE//CLIENT • SET TRACESTAMP = OFF • SET TRACEUSER = ON AGGREGATION DONE ... SELECT T1."OrderID", SUM(T1."Freight"), SUM(T1."Freight") FROM Northwind.dbo.Orders T1 GROUP BY T1."OrderID" ORDER BY T1."OrderID";

  29. Execution ControlReview • -SET &ECHO=ALL; • Dialogue Manager Vs Non Dialogue Manager • CHECK FILE HOLD • -READ • -TYPE • FILEDEF • -GOTO Processing • &ECHO On Steroids • STYLING • SQL TRACES

  30. Questions Thanks for Coming

More Related