Creating Synonyms in Developer Studio

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.


Top of page

x
Procedure: How to Create a Synonym in Developer Studio

If it is not already open, launch the Create Synonym tool from the WebFOCUS Environments area of the Explorer:

  1. Expand your WebFOCUS environment (for example, localhost).
  2. Expand Data Servers.
  3. Expand the server.
  4. Expand the Applications folder.
  5. Expand an application, and right-click the Master Files folder for the application in which you wish to access the data source.

    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.

  6. Select New and then Synonym. The Create Synonym tool opens.
  7. In the first window, you can choose any configured Data Adapter or Remote Server that has been added to the default Reporting Server. Note that when you are creating a synonym through a Remote Server, the remote server should already contain the required synonyms and the corresponding adapters.

    Select adapter to configure or Select connection to create synonym dialog box

    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.

  8. Select the server or adapter that you configured and click OK.

    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.

    Select synonyn candidate dialog box

    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.

  9. For some data sources, you have the option to choose a database or other parameter.

    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.

  10. Optionally, check Filter by owner/schema and object name to filter the results based on owners or table prefixes. This limits the list of tables returned from the remote data source and makes it easier to choose the data for which you want to create synonyms. If you do not include selection criteria, the entire list of tables is displayed.
  11. Click Next.

    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 Create Synonym window

    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.

  12. Click Create Synonym.

    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.

    Create Synonym confirmation window

    The synonyms are created in the selected application directory. In this example, the default application, baseapp, is used.

  13. Click Close.

Top of page

x
Reference: Synonym Creation Parameters for Microsoft SQL Server

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:

  • Check Default database to use the database that has been set as the default database.
  • Select a database from the Select Database drop-down list, which lists all databases in the current DBMS instance.

    Uncheck Default database if you are going to select a database other than the default (This does not apply to Informix SE, for which Default database must be checked.)

Filter by owner/schema and object name

Selecting this option adds the owner/schema and object name parameters to the screen.

  • Owner/Schema. Type a string for filtering the selection, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter:
    • ABC% to select tables or views whose owner/schema begin with the letters ABC.
    • %ABC to select tables or views whose owner/schema end with the letters ABC.
    • %ABC% to select tables or views whose owner/schema contain the letters ABC at the beginning, middle, or end.
  • Object name. Type a string for filtering the procedure names, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter:
    • ABC% to select all procedures whose names begin with the letters ABC.
    • %ABC to select all procedures whose names end with the letters ABC.
    • %ABC% to select all procedures whose names contain the letters ABC at the beginning, middle, or end.

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:

  • In the Base Location field, specify the physical directory location of the file that contains the SQL Query.
  • In the Document Extension field, enter the extension of the script files to filter the list of candidates.

On IBM i, you can use alternative IFS naming conventions to access library members. The following entry illustrates this method:

  • In the Location of External SQL Scripts field, enter:
    /QSYS.LIB/MYLIBRARY.LIB/MYSRC.FILE
  • The Extension is understood to be MBR. You can enter this value explicitly or leave the input box blank.

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:

  • To select all tables in the list, select the check box to the left of the Default synonym name column heading.
  • To select specific tables, select the corresponding check boxes.


x
Reference: Synonym Creation Parameters for Stored Procedures

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:

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.

  • Owner/Schema. Type a string for filtering the selection, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter:
    • ABC% to select tables or views whose owner/schema begin with the letters ABC.
    • %ABC to select tables or views whose owner/schema end with the letters ABC.
    • %ABC% to select tables or views whose owner/schema contain the letters ABC at the beginning, middle, or end.
  • Object name. Type a string for filtering the procedure names, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter:
    • ABC% to select all procedures whose names begin with the letters ABC.
    • %ABC to select all procedures whose names end with the letters ABC.
    • %ABC% to select all procedures whose names contain the letters ABC at the beginning, middle, or end.

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:

  • Library. Type a string for filtering the Library (or DB2 Collection), inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter:
    • ABC% to select tables or views whose owner IDs begin with the letters ABC.
    • %ABC to select tables or views whose owner IDs end with the letters ABC.
    • %ABC% to select tables or views whose owner IDs contain the letters ABC at the beginning, middle, or end.
  • Object name. Type a string for filtering the table, view, or object names, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter:
    • ABC% to select all tables, views, or objects whose names begin with the letters ABC.
    • %ABC to select all whose names end with the letters ABC.
    • %ABC% to select all whose names contain the letters ABC at the beginning, middle, or end.

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:

  • Explicit input values (and separate synonyms) are required when input parameter values cause answer sets with different data structures, which vary depending on the input parameters provided.
  • Explicit input values are not required when you know the internal logic of the procedure, and are certain that it always produces the same data structure. In this situation, only one synonym needs to be created and you can leave the Value input blank for synonym creation purposes.

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.



Example: Synonym for Microsoft SQL Server Stored Procedure CustOrders

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

Top of page

x
Procedure: How to Refresh Synonyms

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.

  1. Right-click a synonym (Master File).
    • In the WebFOCUS Environments folder, Master Files are listed within an application in the Data Servers Applications area.
    • In the Projects area, Master Files are listed in a Master Files folder under a project name.
  2. Select Refresh Synonym to recreate the Master File.

    Note: Refresh is not applicable to Cube data sources at the current time, and not supported for FOCUS files.


Top of page

x
Procedure: How to Delete Synonyms

Master Files and Access Files are removed from the server when you delete a synonym.

  1. Right-click the synonym you want to delete and select Delete.

    You are prompted to confirm the deletion.

  2. Click Yes to delete or No to cancel.

Top of page

x
Using the Reporting Server Console for Data Access and Synonym Preparation

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:



x
Procedure: How to Access the Reporting Server Web Console

To open the Reporting Server Console in Developer Studio:

  1. Click the Reporting Server Console icon from the Object Explorer toolbar or open the following page in a web browser:
    http://hostname:port#/webconsole

    The Server Console opens in your web browser.

  2. To access the Server Console help, click Help and select Contents and Search. Expand the Adapters folder in the Table of Contents, and review the information for your adapter.

WebFOCUS