db2

Information about db2

Published on November 19, 2007

Author: Javier

Source: authorstream.com

Content

Slide1:  By: Meadow Heath FOCUS to DB2:  FOCUS to DB2 Define DB2 table to FOCUS How FOCUS processes a DB2 request FOCUS to DB2 Efficiencies Define DB2 table to FOCUS:  Define DB2 table to FOCUS CAR Master File Description CAR Access File Description CAR MFD:  CAR MFD FILE=CAR ,SUFFIX=SQLDS ,$ SEGNAME=CAR ,SEGTYPE=S0 ,$ FIELD=COUNTRY ,COUNTRY ,A10 ,A10 ,MISSING=OFF,$ FIELD=CAR ,CARS ,A16 ,A16 ,MISSING=OFF,$ FIELD=MODEL ,MODEL ,A24 ,A24 ,MISSING=OFF,$ FIELD=BODYTYPE ,TYPE ,A12 ,A12 ,MISSING=OFF,$ FIELD=SEATS ,SEAT ,I3 ,I4 ,MISSING=OFF,$ FIELD=DEALER_COST ,DCOST ,D7 ,D8 ,MISSING=OFF,$ FIELD=RETAIL_COST ,RCOST ,D7 ,D8 ,MISSING=OFF,$ FIELD=SALES ,UNITS ,I6 ,I4 ,MISSING=OFF,$ FIELD=LENGTH ,LEN ,D5 ,D5 ,MISSING=OFF,$ FIELD=WIDTH ,WIDTH ,D5 ,D5 ,MISSING=OFF,$ FIELD=HEIGHT ,HEIGHT ,A2 ,A2 ,MISSING=OFF,$ FIELD=WHEELBASE ,BASE ,D6.1 ,D8 ,MISSING=OFF,$ FIELD=FUEL_CAP ,FUEL ,D6.1 ,D8 ,MISSING=OFF,$ FIELD=BHP ,POWER ,D6 ,D6 ,MISSING=OFF,$ FIELD=RPM ,RPM ,I5 ,I4 ,MISSING=OFF,$ ALIAS column should be the same name as the column name defined to the DB2 table MISSING = OFF default value and should be used with NOT NULL columns in DB2 MISSING = ON displays the FOCUS NODATA value for missing data ACTUAL format is converted from the SQL Datatype: CHAR(n)=An, VARCHAR(n)=An, SMALLINT=I4, INTEGER=I4, DECIMAL=P, REAL=F4, FLOAT=D8,DATE=DATE . . . CAR ACCESS File:  CAR ACCESS File SEGNAME= CAR ,TABLENAME= '”IBI".”CAR"' ,KEYS= 0 ,WRITE= YES ,$ SEGMENT associates the Master File with table it describes WRITE=YES (default) specifies read and write access using MODIFY and MAINTAIN WRITE=NO Read-only access using MODIFY and MAINTAIN PRIMARY KEY must be defined (if there is one) KYFLD and IXFLD would be included for multi-table structures -- used for dynamic JOINs. ALLOCATION for the Access File is done in the FOCSQL library DYNAM ALLOC FILE FOCSQL DA {node}.FOCSQL.DATA FOCUS to DB2 Processing:  FOCUS to DB2 Processing High level diagram of process FOCUS Request DB2 Interface Results FOCUS Request:  FOCUS Request Programmer submits a FOCEXEC: TABLE FILE CAR PRINT CAR MODEL DEALER_COST RETAIL_COST END High Level Diagram of Process:  High Level Diagram of Process FOCUS (Processing) DB2 Interface (creates SQL) db2 request focexec Programmer executes FOCEXEC FOCUS parses request and passes to DB2 Interface DB2 Interface:  DB2 Interface DB2 Interface translates the FOCEXEC into SQL code: TABLE FILE CAR PRINT CAR MODEL DEALER_COST RETAIL_COST END SQL: SELECT T1.CAR,T1.MODEL,T1.DCOST,T1.RCOST FROM CAR T1 FOR FETCH ONLY; High Level Diagram of Process:  High Level Diagram of Process FOCUS (Processing) DB2 Interface (creates SQL) RDBMS Table Table db2 request focexec db2 results s q l d a t a FOCUS parses request and passes to DB2 Interface DB2 Interface builds SQL & passes to RDBMS RDBMS retrieves data from DB2 table(s) RDBMS returns data to DB2 Interface FOCUS completes any process not handled by DB2 Programmer executes FOCEXEC results Results returned to programmer Results:  Results FOCEXEC: TABLE FILE CAR PRINT CAR MODEL DEALER_COST RETAIL_COST END SQL: SELECT T1.CAR,T1.MODEL,T1.DCOST,T2RCOST FROM CAR T1 FOR FETCH ONLY; RESULTS: CAR MODEL DEALER_COST RETAIL_COST --- ----- ----------- ----------- JAGUAR V12XKE AUTO 7,427 8,878 JAGUAR XJ12L AUTO 11,194 13,491 JENSEN INTERCEPTOR III 14,940 17,850 DATSUN B210 2 DOOR AUTO 2,626 3,139 MASERATI DORA 2 DOOR 25,000 31,500 . . . PEUGEOT 504 4 DOOR 4,631 5,610 FOCUS to DB2 Efficiencies:  FOCUS to DB2 Efficiencies Sorting Data Selection Criteria Data Calculations How to determine efficiency Sorting Data:  Sorting Data Sorting Defined field Sorting on DB2 field Sorting on Define field:  Sorting on Define field FOCEXEC: DEFINE FILE CAR NEW_NAME/A3 =EDIT(MODEL,'999'); END TABLE FILE CAR PRINT CAR BY NEW_NAME END SQL: SELECT T1.CAR,T1.MODEL FROM CAR T1 FOR FETCH ONLY; RESULTS: NEW_NAME CAR -------- --- B21 DATSUN COR TOYOTA DOR MASERATI . . . 530 BMW NOTICE: Sort statement is not passed to DB2; FOCUS will have to sort the data before sending results back to the programmer. Sorting on DB2 field:  Sorting on DB2 field FOCEXEC: DEFINE FILE CAR NEW_NAME/A3 =EDIT(MODEL,'999'); END TABLE FILE CAR PRINT NEW_NAME CAR BY MODEL NOPRINT END SQL: SELECT T1.CAR,T1.MODEL FROM CAR T1 ORDER BY T1.MODEL FOR FETCH ONLY; RESULTS: NEW_NAME CAR -------- --- B21 DATSUN COR TOYOTA DOR MASERATI . . . 530 BMW NOTICE: ORDER BY statement is added to the SQL passed to DB2. DB2 will sort the data before returning results to FOCUS. Selection Criteria:  Selection Criteria Selecting from fields created with User-written subroutines Selecting from fields created with Edit feature Selecting from fields created with User-written subroutines:  Selecting from fields created with User-written subroutines FOCEXEC: DEFINE FILE CAR NEW_NAME/A3 =SUBSTR(24,MODEL,1,3,3,NEW_NAME); END TABLE FILE CAR PRINT CAR BY NEW_NAME WHERE NEW_NAME LT 'C' END SQL: SELECT T1.CAR,T1.MODEL FROM CAR T1 FOR FETCH ONLY; RESULTS: NEW_NAME CAR -------- --- B21 DATSUN NOTICE: No selection criteria is passed to DB2; therefore, DB2 will pass all the data back, and FOCUS will have to complete the request. Selecting from fields created with Edit Feature:  Selecting from fields created with Edit Feature FOCEXEC: DEFINE FILE CAR NEW_NAME/A3 =EDIT(MODEL,'999'); END TABLE FILE CAR PRINT CAR BY NEW_NAME WHERE NEW_NAME LT 'C' END SQL: SELECT T1.CAR,T1.MODEL FROM CAR T1 WHERE ((SUBSTR(T1.MODEL,1,3)) < ‘C’) FOR FETCH ONLY; RESULTS: NEW_NAME CAR -------- --- B21 DATSUN NOTICE: WHERE NEW_NAME LT ‘C’ is translated by the DB2 Interface. Data Calculations:  Data Calculations Calculations in a Compute Calculations in a Define Calculations with Compute:  Calculations with Compute FOCEXEC: TABLE FILE CAR SUM COMPUTE NEW_AMT/D7 =RETAIL_COST - DEALER_COST; BY CAR END SQL: SELECT T1.CAR,SUM(T1.DCOST),SUM(T1.RCOST) FROM CAR T1 GROUP BY T1.CAR ORDER T1.CAR FOR FETCH ONLY; RESULTS: CAR NEW_AMT --- ------- ALFA ROMEO 3,330 AUDI 907 BMW 9,262 DATSUN 513 JAGUAR 3,748 JENSEN 2,910 MASERATI 6,500 PEUGEOT 979 TOYOTA 453 TRIUMPH 808 NOTICE: NEW_AMT is not translated by the DB2 Interface; therefore, FOCUS does this calculation once all the data is retrieved from DB2. Calculations in Define:  Calculations in Define FOCEXEC: DEFINE FILE CAR NEW_AMT/D7 =RETAIL_COST - DEALER_COST; END TABLE FILE CAR SUM NEW_AMT BY CAR END SQL: SELECT T1.CAR, SUM((T1.RCOST - T1.DCOST)) FROM CAR T1 GROUP BY T1.CAR ORDER BY T1.CAR FOR FETCH ONLY; RESULTS: CAR NEW_AMT --- ------- ALFA ROMEO 3,330 AUDI 907 BMW 9,262 . . TRIUMPH 808 NOTICE: NEW_AMT is passed to DB2 to do the calculation. How to determine efficiency:  How to determine efficiency Test efficiency with FSTRACE4 FSTRACE4 shows the translation into SQL code 1) DYNAM ALLOC FILE FSTRACE4 * (OR ALLOCATE TO A FILE) 2) Execute your FOCEXEC 3) Review FSTRACE4 to verify that the DB2 Translator converted your FOCEXEC into SQL code where possible Summary:  Summary Define DB2 table to FOCUS in the Master File Description and Access File Description Define the DB2 table to FOCUS :  Define the DB2 table to FOCUS Master File Description (MASTER) FILE=CAR ,SUFFIX=SQLDS ,$ SEGNAME=CAR ,SEGTYPE=S0 ,$ FIELD=COUNTRY ,COUNTRY ,A10 ,A10 ,MISSING=OFF,$ FIELD=CAR ,CARS ,A16 ,A16 ,MISSING=OFF,$ FIELD=MODEL ,MODEL ,A24 ,A24 ,MISSING=OFF,$ . . . FIELD=RPM ,RPM ,I5 ,I4 ,MISSING=OFF,$ Access File Description (FOCSQL) SEGNAME= CAR ,TABLENAME= '”IBI".”CAR"' ,KEYS= 0 ,WRITE= YES ,$ Summary:  Summary Define DB2 table to FOCUS in the Master File Description and Access File Description The DB2 Translator translates the FOCEXEC into SQL code to be passed to the RDBMS High Level Diagram of Process:  High Level Diagram of Process FOCUS (Processing) DB2 Interface (creates SQL) RDBMS Table Table db2 request focexec db2 results s q l d a t a FOCUS parses request and passes to DB2 Interface DB2 Interface builds SQL & passes to RDBMS RDBMS retrieves data from DB2 table(s) RDBMS returns data to DB2 Interface FOCUS completes any process not handled by DB2 Programmer executes FOCEXEC results Results returned to programmer Summary:  Summary Define DB2 table to FOCUS in the Master File Description and Access File Description The DB2 Translator translates the FOCEXEC into SQL code to be passed to the RDBMS Write your FOCEXEC to be more efficient by making DB2 do most of the work DB2 should do most of the work:  DB2 should do most of the work DB2 Translator should be able to convert your data processing into SQL code when: Sorting Data Selection Criteria Data Calculations Test efficiency with FSTRACE4 Questions:  Questions

Related presentations


Other presentations created by Javier

wap
26. 11. 2007
0 views

wap

PairashThajchayapong1
02. 01. 2008
0 views

PairashThajchayapong1

Lecture13 1
09. 10. 2007
0 views

Lecture13 1

Physical Features of Arab World
24. 10. 2007
0 views

Physical Features of Arab World

arbovirus
24. 10. 2007
0 views

arbovirus

Ch14 Lecture
29. 11. 2007
0 views

Ch14 Lecture

going in 13may02
01. 12. 2007
0 views

going in 13may02

cap3
14. 11. 2007
0 views

cap3

enfoques 4 ppt
15. 11. 2007
0 views

enfoques 4 ppt

DeafTalk
16. 11. 2007
0 views

DeafTalk

REACH Overview
05. 12. 2007
0 views

REACH Overview

Romantic English Literature
14. 12. 2007
0 views

Romantic English Literature

Treaty of Versailles
23. 12. 2007
0 views

Treaty of Versailles

conman15
28. 12. 2007
0 views

conman15

intro CS 1
04. 01. 2008
0 views

intro CS 1

Radiation Concepts
04. 01. 2008
0 views

Radiation Concepts

Kryptologie Folien Web
05. 01. 2008
0 views

Kryptologie Folien Web

meld ldp iros07 talk3
07. 01. 2008
0 views

meld ldp iros07 talk3

bird
29. 10. 2007
0 views

bird

Ideal Year 2006
02. 11. 2007
0 views

Ideal Year 2006

Saggia Ecologia Presentazione
01. 10. 2007
0 views

Saggia Ecologia Presentazione

Royal Europe consumer
30. 10. 2007
0 views

Royal Europe consumer

Undergrat Presentation 2004
24. 10. 2007
0 views

Undergrat Presentation 2004

report pixel2000
01. 11. 2007
0 views

report pixel2000

Johnson 1
06. 11. 2007
0 views

Johnson 1

USA Presentation Rev 4
08. 11. 2007
0 views

USA Presentation Rev 4

Divisenko
20. 11. 2007
0 views

Divisenko

Civil Society Index Project
23. 11. 2007
0 views

Civil Society Index Project

Unit07Log
01. 11. 2007
0 views

Unit07Log

presentaz roma trieste 4
29. 10. 2007
0 views

presentaz roma trieste 4

Montana Meth Presentation
27. 12. 2007
0 views

Montana Meth Presentation

careerbuilder
20. 02. 2008
0 views

careerbuilder

Brussels 11May06
25. 10. 2007
0 views

Brussels 11May06

EDMT14
27. 02. 2008
0 views

EDMT14

pisanelli
30. 10. 2007
0 views

pisanelli

Newch6www
29. 02. 2008
0 views

Newch6www

tunnista kulutustyyppisi
05. 11. 2007
0 views

tunnista kulutustyyppisi

StratTac06 Leggett
05. 03. 2008
0 views

StratTac06 Leggett

Teela powerpoint 6
14. 03. 2008
0 views

Teela powerpoint 6

67436
27. 03. 2008
0 views

67436

dli20071
30. 03. 2008
0 views

dli20071

GEP2007
25. 10. 2007
0 views

GEP2007

hort2 floraldesign
11. 12. 2007
0 views

hort2 floraldesign

Kodal MALTA
04. 10. 2007
0 views

Kodal MALTA

bcs 03 nottingham
26. 11. 2007
0 views

bcs 03 nottingham

17 Sussex
17. 12. 2007
0 views

17 Sussex

asdc ncss for website ihc
06. 11. 2007
0 views

asdc ncss for website ihc

frieman
15. 11. 2007
0 views

frieman

Sem Grd Ontology
19. 11. 2007
0 views

Sem Grd Ontology

Underground1
06. 12. 2007
0 views

Underground1

Avape Port
16. 11. 2007
0 views

Avape Port

ceciliat2
28. 12. 2007
0 views

ceciliat2

diane guatelli
31. 10. 2007
0 views

diane guatelli

cacti
12. 12. 2007
0 views

cacti

Attila Vitai Vodafone
26. 11. 2007
0 views

Attila Vitai Vodafone

kevin dustin
13. 11. 2007
0 views

kevin dustin

02 Italy Gorgucci
31. 10. 2007
0 views

02 Italy Gorgucci

wp4status russia2
26. 10. 2007
0 views

wp4status russia2