FOCUS to DB2
Download
1 / 29

FocusDB2 interface - PowerPoint PPT Presentation


  • 362 Views
  • Updated On :

FOCUS to DB2 By: Meadow Heath FOCUS to DB2 Define DB2 table to FOCUS How FOCUS processes a DB2 request FOCUS to DB2 Efficiencies Define DB2 table to FOCUS CAR Master File Description CAR Access File Description CAR MFD

Related searches for FocusDB2 interface

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'FocusDB2 interface' - adamdaniel


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Slide1 l.jpg

FOCUS to DB2

By: Meadow Heath


Focus to db2 l.jpg
FOCUS to DB2

  • Define DB2 table to FOCUS

  • How FOCUS processes a DB2 request

  • FOCUS to DB2 Efficiencies


Define db2 table to focus l.jpg
Define DB2 table to FOCUS

  • CAR Master File Description

  • CAR Access File Description


Car mfd l.jpg
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 l.jpg
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 l.jpg
FOCUS to DB2 Processing

  • High level diagram of process

    • FOCUS Request

    • DB2 Interface

    • Results


Focus request l.jpg
FOCUS Request

Programmer submits a FOCEXEC:

TABLE FILE CAR

PRINT CAR

MODEL

DEALER_COST

RETAIL_COST

END


High level diagram of process l.jpg
High Level Diagram of Process

DB2

Interface

(creates SQL)

FOCUS

(Processing)

db2 request

focexec

Programmer executes FOCEXEC

FOCUS parses request and passes to DB2 Interface


Db2 interface l.jpg
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 process10 l.jpg
High Level Diagram of Process

db2 results

DB2

Interface

(creates SQL)

FOCUS

(Processing)

results

db2 request

focexec

d

a

t

a

s

q

l

Programmer executes FOCEXEC

FOCUS parses request and passes to DB2 Interface

RDBMS

DB2 Interface builds SQL & passes to RDBMS

RDBMS retrieves data from DB2 table(s)

RDBMS returns data to DB2 Interface

Table

Table

FOCUS completes any process not handled by DB2

Results returned to programmer


Results l.jpg
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 l.jpg
FOCUS to DB2 Efficiencies

  • Sorting Data

  • Selection Criteria

  • Data Calculations

  • How to determine efficiency


Sorting data l.jpg
Sorting Data

  • Sorting Defined field

  • Sorting on DB2 field


Sorting on define field l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Data Calculations

  • Calculations in a Compute

  • Calculations in a Define


Calculations with compute l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Summary

  • Define DB2 table to FOCUS in the Master File Description and Access File Description


Define the db2 table to focus l.jpg
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 ,$


Summary25 l.jpg
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 process26 l.jpg
High Level Diagram of Process

db2 results

DB2

Interface

(creates SQL)

FOCUS

(Processing)

results

db2 request

focexec

d

a

t

a

s

q

l

Programmer executes FOCEXEC

FOCUS parses request and passes to DB2 Interface

RDBMS

DB2 Interface builds SQL & passes to RDBMS

RDBMS retrieves data from DB2 table(s)

RDBMS returns data to DB2 Interface

Table

Table

FOCUS completes any process not handled by DB2

Results returned to programmer


Summary27 l.jpg
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 l.jpg
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



ad