In this section: How to: Reference: |
The point at which you begin to create synonyms will depend on your adapter, Reporting Server, and possibly your WebFOCUS Client configuration (as described in Data Access Configuration Variations). Whenever you are ready, you can use the Create Synonym tool to create synonyms for the data sources you need to access.
If it is not already open, launch the Create Synonym tool from the WebFOCUS Environments area of the Explorer:
If you wish to use the data source in multiple applications or have not yet created your application, you can use the baseapp application, where resources can be stored for sharing and access by other applications.
Tip: If you are developing from the Projects area of the Explorer, expand the project, and right-click the Master Files folder, then proceed as described.
The server configuration, indicated by the server profile, determines which adapters and servers appear. In this example, the Adapter for Microsoft SQL Server is configured. If you have configured remote servers, they will appear expanded in the Remote Server folder.
A window opens in which you can enter additional information about the data source you wish to access.
Note: The options in this window vary depending on the type of adapter or server you are using. To access the pertinent information for your adapter directly from Developer Studio, choose Reporting Server Console from the Command menu. The Server Console opens. Click Help on the menu bar, select Contents and Search, expand the Adapters topic in the Table of Contents pane, and look for the adapter for which you wish to create the synonym. The relevant synonym creation parameters are fully defined.
The following example is for accessing an adapter for EDASERVE.
After you complete this window, the data source or server is queried to determine the metadata you can use to create synonyms. For a relational database, this is usually a list of tables or views. This window lets you filter the results so there are fewer tables from which to choose. Optionally, choose whether both Tables and Views should be returned. By default, both check boxes are selected.
For some data sources, you can select to generate synonyms for other object types, for example, Stored Procedures. If you do, the remaining input parameters will vary slightly.
You will have to select an appropriate database or choose Default Database. You will be able to choose from the database tables when this window is complete.
The top of the Create Synonym window now displays additional fields you can use to refine your synonym. Remember that the parameters vary depending on the type of adapter or server you are using.
The bottom of the Create Synonym window provides a list of tables for which you can create synonyms. To choose all tables, select the Default synonym name check box.
The synonym is created and a confirmation window appears. Once again, note that the window may vary depending on the type of adapter or server you are using.
The synonyms are created in the selected application directory. In this example, the default application, baseapp, is used.
The following table describes the synonym creation parameters for Microsoft SQL Server, based on Tables, Views, or External SQL Scripts.
Parameter/Task |
Description |
---|---|
Restrict object type to |
Restricts candidates for synonym creation based on the selected object types: Tables, Views, External SQL Scripts, and any other supported objects. Selecting External SQL Scripts from the drop-down list enables you to represent SQL SELECT statements as synonyms for read-only reporting. A Synonym candidate can be any file that contains only one valid SQL Query and does not contain end-of-statement delimiters (";" or "/") and comments. For related information, see Location of External SQL Scripts in this chart. Depending on the adapter, you can further restrict your search by choosing check boxes for listed objects. |
Database selection |
To specify a database from which you can select a table or other object, do one of the following:
|
Filter by owner/schema and object name |
Selecting this option adds the owner/schema and object name parameters to the screen.
|
Location of External SQL Scripts Extension |
If you specify External SQL Scripts in the Restrict object type to field, these additional fields are displayed. The following standard naming conventions apply for UNIX, IBM i IFS, and z/OS HFS:
On IBM i, you can use alternative IFS naming conventions to access library members. The following entry illustrates this method:
During synonym generation, the adapter issues native API calls to obtain a list of elements in the select list and builds the Master File with a field for each element. The generated Access File references the location of the SQL script in the DATASET attribute, which contains the full path, including the file name and extension to the file containing the SQL Query. For example, DATASET=/ul/home2/apps/report3.sql When a WebFOCUS report is created, the SQL Query is used to access data. |
Select Application |
Select an application directory. The default value is baseapp. |
Cardinality |
Select the Cardinality check box to reflect the current cardinality (number of rows or tuples) in the table during metadata creation. Cardinality is used for equi-joins. The order of retrieval is based on the size (cardinality) of the table. Smaller tables are read first. If the cardinality of the tables to be used in the application are dynamic, it may not be beneficial to choose this setting. |
With foreign keys |
Select the With foreign keys check box to include within this synonym every table related to the current table by a foreign key. The resulting multitable synonym describes all of the foreign key relationships for the table. |
Dynamic columns |
To specify that the Master File created for the synonym should not contain column information, select the Dynamic columns check box. If this option is selected, column data is retrieved dynamically from the data source at the time of the request. |
Prefix/Suffix |
If you have tables with identical table names, assign a prefix or a suffix to distinguish them. For example, if you have identically named human resources and payroll tables, assign the prefix HR to distinguish the synonyms for the human resources tables. Note that the resulting synonym name cannot exceed 64 characters. If all tables and views have unique names, leave prefix and suffix fields blank. |
Customize data type mappings |
To change the data type mappings from their default settings, select this check box. The customizable mappings are displayed. |
Overwrite existing synonyms |
To specify that this synonym should overwrite any earlier synonym with the same fully qualified name, select the Overwrite existing synonyms check box. |
Default synonym name |
This column displays the name that will be assigned to each synonym. To assign a different name, replace the displayed value. |
Select tables |
Select tables for which you wish to create synonyms:
|
For data sources that support stored procedures, you can use a reporting tool to execute a procedure and report against its output parameters and answer set. Among the benefits of this method of executing a stored procedure are:
The first step is to create a synonym for the stored procedure you wish to report against. A synonym describes the parameters and answer set for the stored procedure.
An answer set structure may vary depending on the input parameter values that are provided when the procedure is executed. Therefore, you need to generate a separate synonym for each set of input parameter values that will be provided when the procedure is executed at run time. For example, if users can execute the stored procedure using three different sets of input parameter values, you need to generate three synonyms, one for each set of values. Unless noted otherwise, input parameters refers to IN parameters and to INOUT parameters in IN mode.
Note: If you know the internal logic of the procedure, and are certain which range of input parameter values will generate each answer set structure returned by the procedure, you can create one synonym for each answer set structure. For each synonym, simply provide a representative set of the input parameter values necessary to return that answer set structure.
A synonym includes the following segments:
If there are no IN parameters or INOUT parameters in IN mode, the segment describes a single dummy field.
If there are no OUT parameters or INOUT parameters in OUT mode, the segment is omitted.
If there is no answer set, the segment is omitted.
The following chart describes the parameters used to create the synonym.
Parameter/Task |
Description |
---|---|
Restrict object type to |
Select Stored Procedures. |
Filter by owner/schema and object name (for DB2, this applies to all platforms except IBM i) |
Selecting this option adds the owner/schema and object name parameters to the screen.
|
Library Object Name (IBM i only) |
To avoid the return of an extremely large and potentially unmanageable list, always supply a value for Library or Object Name:
|
Select |
Select a procedure. You can only select one procedure at a time since each procedure will require unique input in the Values box on the next synonym creation pane. |
Name |
The name of the synonym, which defaults to the stored procedure name. |
Select Application |
Select an application directory. The default value is baseapp. |
Prefix/Suffix |
If you have stored procedures with identical names, assign a prefix or a suffix to distinguish their corresponding synonyms. Note that the resulting synonym name cannot exceed 64 characters. If all procedures have unique names, leave the prefix and suffix fields blank. |
Overwrite existing synonyms |
To specify that this synonym should overwrite any earlier synonym with the same fully qualified name, select the Overwrite existing synonyms check box. |
Customize data type mappings |
To change the data type mappings from their default settings, select this check box. The customizable mappings are displayed. For information about them, see Data Type Support in the chapter for your adapter in the Adapter Administration for UNIX, Windows, OpenVMS, IBM i, and z/OS manual. |
Values |
Select the check box for every parameter displayed for the specified procedure. Note the following before you enter parameter values. If the procedure you selected has input parameters (IN parameters and/or INOUT parameters in IN mode), you will be prompted to enter values for them. However, the need for an explicit Value entry depends on the logic of the procedure and the data structures it produces. Therefore, while you must check the parameter box, you may not need to enter a value. Follow these guidelines:
If a Value is required, enter it without quotation marks ("). Any date, date-time, and timestamp parameters must have values entered in an ISO format. Specify the same input parameters that will be provided when the procedure is executed at run time if it is a procedure that requires explicit values. |
The following synonym describes a Microsoft SQL Server stored procedure with one input parameter, one output parameter, and one answer set containing four variables.
The Master File for the synonym is:
FILENAME=CUSTORDERS, SUFFIX=SQLMSS, $ SEGMENT=INPUT, SEGTYPE=S0, $ FIELDNAME=@CUSTOMERID, ALIAS=P0001, USAGE=A5, ACTUAL=A5, MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $ SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $ FIELDNAME=@RETURN_VALUE, ALIAS=P0000, USAGE=I11, ACTUAL=I4, $ SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $ FIELDNAME=ORDERID, ALIAS=OrderID, USAGE=I11, ACTUAL=I4, $ FIELDNAME=ORDERDATE, ALIAS=OrderDate, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $ FIELDNAME=REQUIREDDATE, ALIAS=RequiredDate, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $ FIELDNAME=SHIPPEDDATE, ALIAS=ShippedDate, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $
The Access File for the synonym is:
SEGNAME=INPUT, CONNECTION=ITarget, STPNAME=Northwind.dbo.CustOrders, $ SEGNAME=OUTPUT, STPRESORDER=0, $ SEGNAME=ANSWERSET1, STPRESORDER=1, $
Refreshing a synonym enables you to update field information while preserving the original synonym title, description, usage, virtual field, and DBA information. The action also synchronizes the Master File with the table on which the synonym is based.
You can refresh synonyms from either the Data Servers area under the WebFOCUS Environments folder or from the Projects area.
Note: Refresh is not applicable to Cube data sources at the current time, and not supported for FOCUS files.
Master Files and Access Files are removed from the server when you delete a synonym.
You are prompted to confirm the deletion.
How to: |
A server administrator or an application administrator who has been granted these privileges can configure and manage WebFOCUS Reporting Servers through the Reporting Server Console. You can use the Server Console as an alternative to the Developer Studio tools to configure adapters, add remote servers, and create synonyms. In addition, many other configuration options are available through the Server Console. For a full understanding of configuration options and server capabilities, see the Server Console Help system or the following manuals: Server Administration for UNIX, Windows, OpenVMS, IBM i, and z/OS and Adapter Administration for UNIX, Windows, OpenVMS, IBM i, and z/OS.
Note:
To open the Reporting Server Console in Developer Studio:
http://hostname:port#/webconsole
The Server Console opens in your web browser.
WebFOCUS |