Distributed Execution

Syntax: How to Implement Distributed Execution

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:

Store the name of the target server in an Access File. The syntax in the Access File is
```
SERVER = servername
```
where:

servername

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.
Issue the following command:
```
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:

userid.MASTER.DATA(CAR): Is the CAR Master File.
userid.FOCSQL.DATA(CAR): Is the CAR Access File.

Example: Storing a Server Name in an 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:

SEGNAME. The segment name in the Access File must match the segment name in the Master File.
TABLENAME. This attribute specifies the name of the Master File on the server.
SERVER. This attribute specifies the name of the server.

Example: Submitting a Request Using SUFFIX=EDA

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

Example: Using a Remote Multi-Segment Master and Access File

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 ,$

How Location Transparency Works

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:

FOCUS can join data sources that reside on different platforms. The communication protocols used to connect to the servers may be the same or different (for example, TCP/IP to a Server for VMS, and LU0 to a Server for z/OS).
The Server Data Adapter (SUFFIX=EDA) is a relational view. Like other relational data adapters such as Oracle or SQL Server, it uses the ALIAS value from a Master File when generating a report request to send to the DBMS. Consequently, make sure that you provide values for the ALIAS attributes in your data source descriptions.
Master Files should also contain values for ACTUAL format attributes.

Logging On to the Server With Distributed Execution

How to:

Set the Server Destination With Distributed Execution
Send a User ID and Password to the Server With Distributed Execution

You can log on to a server by issuing SQL EDA SET commands in a FOCEXEC or at the FOCUS Session prompt.

The SQL EDA SET SERVER command sets the server destination (an alternative to placing the server name in the Access File).
The SQL EDA SET USER command sends a user ID and password to the server.

Syntax: How to Set the Server Destination With Distributed Execution

SQL EDA SET SERVER servername

where:

servername: Is the server name. It must match the SERVICE keyword in the configuration file on the server.

Syntax: How to Send a User ID and Password to the Server With Distributed Execution

SQL EDA SET USER servername/userid,password

where:

servername: Is the server with which you want to associate this user ID and password.
userid: Is the user ID.
password: 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.

Joining Data Sources Across Platforms With Distributed Execution

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.

Issuing SQL Commands to the Server With Distributed Execution

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

Executing Stored Procedures With Distributed Execution

How to:

Execute a Stored Procedure Using Distributed Execution
Query Server Data Adapter Settings

In addition to making data available to the client, a server can store procedures, which are called remote procedures or stored procedures.

Syntax: How to Execute a Stored Procedure Using Distributed Execution

You can execute stored procedures from FOCUS, by issuing the command:

SQL EDA EX rpcname parm1, parm2, ...
END

where:

rpcname: Is a procedure on the server.
parm1, parm2,...: Are character strings sent to the server (they are the same as parameters you can pass on the execution line of a FOCEXEC).

Syntax: How to Query Server Data Adapter Settings

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

Using SQL Passthru With Distributed Execution

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:

Invoke SQL Passthru mode on the server through the use of a server profile or stored procedure.
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.
Execute an SQL Passthru command either from the FOCUS Session prompt or in a FOCUS application using the format
```
SQL EDA sqlstatement
```
where:

sqlstatement

Is a valid SQL statement.

You do not need a Master File or Access File on the client when using SQL Passthru.