With distributed execution (also known as the Server Data Adapter or SUFFIX=EDA), you can access all data sources accessible to a server. Your Master Files and Access Files tell FOCUS where to find the data.
The Server Data Adapter provides the following advantages:
Using the following SUFFIX attribute in a Master File directs FOCUS to pass all requests for that data source directly to the data adapter, which passes them on to a server:
SUFFIX=EDA
You can establish the name of the target server in either of the following ways:
SERVER = servername
where:
Is the name of the target server (value of the NODE attribute in the client configuration file).
The ddname of the Access File is FOCSQL.
SQL EDA SET SERVER servername
A server name in an Access File overrides any name specified in an SQL EDA SET SERVER command. By removing the SERVER attribute from the Access File, you can dynamically control the server location with SQL EDA SET SERVER commands.
Your Access File member names must match your Master File member names and must reside in a partitioned data set allocated to ddname FOCSQL. For example,
DYNAM ALLOC F1 MASTER DS userid.MASTER.DATA SHR REU DYNAM ALLOC F1 FOCSQL DS userid.FOCSQL.DATA SHR REU
where:
Is the CAR Master File.
Is the CAR Access File.
The following example shows how to store server name IBMSERVE in an Access File.
SEGNAME=ONE,TABLENAME=CAR,KEYS=1,WRITE=YES,SERVER=IBMSERVE,$
Note: Regardless of the type of data source to be accessed, the Access File must contain the following attributes:
Consider the following request:
TABLE FILE DIGITEDA PRINT * END
The Master File named DIGITEDA on the client is:
FILENAME=DIGITEDA,SUFFIX=EDA,$ SEGNAME=DIGIT,SEGTYPE=S0,$ FIELD=THIS_DIGIT ,THIS_DIGIT ,I6 ,I4 ,$ FIELD=SSN ,SSN ,A9 ,A9 ,MISSING=OFF,$ FIELD=AMOUNT1 ,AMOUNT1 ,P8 ,P8 ,MISSING=ON ,$ FIELD=AMOUNT2 ,AMOUNT2 ,P9.0 ,P8 ,MISSING=ON ,$
The Access File named DIGITEDA on the client is:
SEGNAME=DIGIT,TABLENAME=DIGIT,KEYS=1,SERVER=PMSEDA,$
The Master File (named DIGIT) on the server is:
FILENAME=DIGIT,SUFFIX=FOC,$ SEGNAME=DIGIT,SEGTYPE=S0,$ FIELD=THIS_DIGIT ,THIS_DIGIT ,I9 ,I4 ,MISSING=ON,$ FIELD=SSN ,SSN ,A9 ,A9 ,MISSING=ON,$ FIELD=AMOUNT1 ,AMOUNT1 ,P16.0 ,P8 ,MISSING=ON,$ FIELD=AMOUNT2 ,AMOUNT2 ,P16.0 ,P8 ,MISSING=ON,$
The EDACS3 client communication configuration file is:
NAME = EDA CLIENT USING CS/3 TCP/IP NODE = PMSEDA BEGIN ; TRACE = 31 PROTOCOL = TCP CLASS = CLIENT HOST = IBIMVS ; DNS NAME (PNO 28109) SERVICE = 2386 ; TCP/IP PORT FOR SERVER END
The TABLE request references a local Master File named DIGITEDA. Its corresponding Access File contains information such as the server name and the Master File name as it is known at the server. In this case, Server PMSEDA contains a Master File called DIGIT. At the server, the DIGIT Master File describes a FOCUS data source. The communications configuration file contains an entry for server name PMSEDA so that the FOCUS Client can establish communications with the server.
Similarly, SQL can be used to reference the Master File. For example:
SQL SELECT * FROM DIGITEDA END
The following is a multi-segment Master File:
FILENAME=JOINEDA, SUFFIX=EDA SEGNAME=EMPDATSE, SEGTYPE=S0 FIELDNAME=EMP_ID, ALIAS=EMP_ID, FORMAT=A9, INDEX=I, $ FIELDNAME=LAST_NAME, ALIAS=LAST_NAME, FORMAT=A15, $ FIELDNAME=FIRSTNAME, ALIAS=FIRSTNAME, FORMAT=A10, $ FIELDNAME=MIDINITIAL, ALIAS=MIDINITIAL, FORMAT=A1, $ FIELDNAME=DIV, ALIAS=DIV, FORMAT=A4, $ ... SEGNAME=DIGIT,SEGTYPE=S0,$ FIELD=THIS_DIGIT ,THIS_DIGIT ,I4 ,I4 ,MISSING=OFF,$ FIELD=THIS_DIGIT ,THIS_DIGIT ,I9 ,I4 ,MISSING=OFF,$ FIELD=SSN ,SSN ,A9 ,A9 ,MISSING=OFF,$ FIELD=AMOUNT1 ,AMOUNT1 ,P16.0 ,P8 ,MISSING=ON ,$ FIELD=AMOUNT2 ,AMOUNT2 ,P16.0 ,P8 ,MISSING=ON ,$
The following is the corresponding multi-segment Access File:
SEGNAME=EMPDATSE, TABLENAME=EMPLOYEE, KEYS=0 ,SERVER=PMSEDA,$ SEGNAME=DIGIT , TABLENAME=DIGIT , KEYS=0 ,SERVER=PMSEDA, KEYFLD=EMP_ID, IXFLD=SSN ,$
Distributed execution provides location transparency because, once a Master File and Access File have been generated, end users can access data in the data source without knowing where it resides. You can manipulate data sources described with SUFFIX=EDA as if they were local. The data returned by a server can be converted to any format supported by FOCUS.
Generally, you create Master Files and Access Files only once. It is necessary to regenerate them only if the structure of the data source on the server changes.
Keep the following in mind:
How to: |
You can log on to a server by issuing SQL EDA SET commands in a FOCEXEC or at the FOCUS Session prompt.
SQL EDA SET SERVER servername
where:
Is the server name. It must match the SERVICE keyword in the configuration file on the server.
SQL EDA SET USER servername/userid,password
where:
Is the server with which you want to associate this user ID and password.
Is the user ID.
Is the password.
This command does not determine the server to which requests will be directed. It simply associates a user ID and a password with a particular server.
You can use the Server Data Adapter to join data sources on different platforms. The presence of SUFFIX=EDA in the Master File causes FOCUS to use the data adapter. For example, you can join an EMPLOYEE data source on a z/OS system to a JOBFILE data source on a UNIX system. The data adapter works faster if you join the smaller data source to the larger data source; the data adapter makes one SQL call to the first data source, then makes one SQL call to the second data source, for each value of the referenced field.
You can issue FOCUS or SQL commands to the server. Issue the SQL commands at the FOCUS Session prompt or place them in FOCUS procedures. For example, from the FOCUS Session prompt, you could issue the following commands:
> sql eda > select * from car > end
How to: |
In addition to making data available to the client, a server can store procedures, which are called remote procedures or stored procedures.
You can execute stored procedures from FOCUS, by issuing the command:
SQL EDA EX rpcname parm1, parm2, ... END
where:
Is a procedure on the server.
Are character strings sent to the server (they are the same as parameters you can pass on the execution line of a FOCEXEC).
To view Server Data Adapter parameter settings, issue the command:
SQL EDA ? >
The output is:
> sql eda ? (FOC1450) CURRENT EDA INTERFACE SETTINGS ARE : (FOC1446) DEFAULT DBSPACE IS - : IBIEDA (FOC1449) CURRENT SQLID IS - : USER1 (FOC1444) AUTOCLOSE OPTION IS - : ON FIN (FOC1496) AUTODISCONNECT OPTION IS - : ON FIN (FOC1499) AUTOCOMMIT OPTION IS - : ON COMMAND (FOC1441) WRITE FUNCTIONALITY IS - : OFF (FOC1445) OPTIMIZATION OPTION IS - : ON (FOC1484) SQL ERROR MESSAGE TYPE IS - : DBMS (FOC1552) INTERFACE DEFAULT DATE TYPE - : NEW
The Server Data Adapter provides an SQL Passthru mode, through which SQL requests can be sent directly to the server and passed to a relational DBMS with no translation by FOCUS. To use SQL Passthru:
For example, if you want to use SQL Passthru with DB2, you would execute the command:
SQL EDA SET ENGINE DB2
For more information, see your Reporting Server documentation.
SQL EDA sqlstatement
where:
Is a valid SQL statement.
You do not need a Master File or Access File on the client when using SQL Passthru.
|
Information Builders |