Managing Db2 Metadata
When the server accesses a data source, it needs to know how to interpret the data stored there. For each data source the server will access, you create a synonym that describes the structure of the data source and the server mapping of the Db2 data types.
Creating Synonyms
Synonyms define unique names (or aliases) for each Db2 table or view that is accessible from the server. Synonyms are useful because they hide the underlying data source location and identity from client applications. They also provide support for extended metadata features of the server, such as virtual fields and additional security mechanisms.
Using synonyms allows an object to be moved or renamed while allowing client applications to continue functioning without modification. The only modification required is a redefinition of the synonym on the server. The result of creating a synonym is a Master File and an Access File, which represent the server metadata.
Note that creating a synonym for a stored procedure is described with reporting against a stored procedure, in Generating a Synonym for a Stored Procedure.
Create a Synonym
To create a synonym, you must have previously configured the adapter. You can create a synonym from the Applications or Adapters pages of the WebFOCUS Reporting Server browser interface.
- Procedure
- From the WebFOCUS Reporting Server browser interface Application page, click Get Data.
- On the
Configured Adapters section of the page, in Simple Mode, right-click an adapter
and click
Show
Connections. Right-click a connection.
Depending on the type of adapter you choose, one of the following options appears on the context menu.
- Show DBMS objects. This option opens the page for selecting synonym objects and properties.
- Create metadata objects. This option opens the page for selecting synonym objects and properties.
- Show files. This option opens a file picker. After you choose a file of the correct type, the page for selecting synonym objects and properties opens.
- Show local files. This option opens a file picker. After you choose a file of the correct type, the page for selecting synonym objects and properties opens.
- Show topics. This option opens the page for selecting synonym objects and properties for topics within the environment.
- Enter values for the parameters required by the adapter as described in the chapter for your adapter.
- After
entering the parameter values, click
Add.
This button may be labeled Next, Create Synonym, Create Base Synonyms, Create Cluster Synonym, or Update Base Synonyms.
The synonym creation process for most adapters has been consolidated so that you can enter all necessary parameters on one page. However, for some adapters such as LDAP, continue clicking Next until you get to a page that has a Create Synonym button.
The synonym is created and added under the specified application directory.
Synonym Creation Parameters for Db2
The following list describes the synonym creation parameters for which you can supply values.
Restrict candidates for synonym creation based on the selected object type(s): Tables, Views, External SQL Scripts, and any other supported objects.
Choosing External SQL Scripts from the drop-down list enables you to represent an SQL Query as a synonym for read-only reporting. A Synonym candidate can be any file that contains one (and only one) valid SQL Query and does not contain end-of-statement delimiters (";" or "/") and comments.
Depending on the adapter, you can further restrict your search by choosing checkboxes for listed objects.
- If you selectAliases as a filtering option and the Alias points to a Remote Db2 system, it must be Version 8 or higher. Only one level of Remote Db2 is supported.
- If you select Nicknames as a filtering option on platforms where Db2 supports the Nickname object type (Windows and UNIX), a basic synonym is created for this type of Db2 object; no Titles, Remarks, or Keys are recorded in the synonym. The user ID who creates the synonym must have SELECT authority for this object type.
Selecting this option adds the Owner/Schema and Object Name parameters to the screen. Select an owner/schema from the drop-down list or type a string for filtering the selection, inserting the wildcard character (%) as needed at the beginning and/or end of the string. Then click Search. 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.
Selecting this option adds the Owner/Schema and Object Name parameters to the screen. Type a string for filtering the object names, inserting the wildcard character (%) as needed at the beginning and/or end of the string. Then click Search. For example, enter: ABC% to select all 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.
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/Db2 Schema, 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 library name begins with the letters ABC; %ABC to select tables or views whose library name ends with the letters ABC; %ABC% to select tables or views whose library name contains 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.
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. You can type a directory name or click on the ellipsis. This opens the Select Base Location dialog.
- In the Document Name field, enter the file name with or without wild card characters.
- 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 Base
Location field, enter:
/QSYS.LIB/MYLIBRARY.LIB/MYSRC.FILE
- The Document 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 name and extension of 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 the number of objects to display on the Create Synonym page.
Only available when External SQL Scripts is selected from the object type drop-down menu. When selected, a SUBQUERY keyword is added to the Access File of the generated synonym. If the corresponding SQL string has valid syntax that can be used in the FROM statement of the generated SQL (what is known as a Derived Table), then the SQL SCRIPT will be processed as a subquery embedded into a FROM clause. This usage allows for more flexibility. For example, the synonym can be used as a target for a JOIN.
If the SQL SCRIPT has parameter markers, such as ? or :, or the syntax contains constructs that are invalid for a derived table, for example ORDER BY, then this keyword should not be selected. At runtime, if SUBQUERY=Y is present and it is determined that the SQL SCRIPT cannot be used in the FROM statement, the setting will be ignored, and a FOC1782 warning message will be issued. The default is selected (SUBQUERY=Y).
Select an application directory. The default value is baseapp.
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 the prefix and suffix fields blank.
To change the data type mappings from their default settings, select this checkbox. The customizable mappings are displayed.
For information about customizable mappings, see Data Type Support Report.
Select the button for the type of synonym you want to create.
On the IBM i platform, the One-part name checkbox is unchecked by default. The unchecked behavior generates a table name that includes the explicit name of the library containing the table. For example, if you specified a library on the first Create Synonym pane, a qualified name like the following is automatically created in the Access File for a TABLE or VIEW:
TABLENAME=MYLIB/MYTABLE
For a Stored Procedure, this would be a Db2 RPC and identified in the Access File as:
STPNAME=MYLIB/MYPROC
With this explicit type of entry in the Access File, at run-time the library is directly referenced and the object opened.
If you select the checkbox, the explicit library name is not stored in the metadata (Access File). When the synonym is generated, the library portion of the table name is omitted from the Access File, and appears as follows:
TABLENAME=MYTABLE
or
STPNAME=MYPROC
With this type of entry in the Access File (one-part name), the Db2 library search path will be used at run time. The exact composition varies depending on if Db2 is configured as a CLI or SQL connection.
For CLI configured servers, the search path will be the default library of Db2 for a CLI connected user (usually QSYS and QSYS2 plus the default library of the user). It is also controllable by use of a passthru command setting the path, such as SLQ DB2 SET PATH "QSYS","QSYS2","FOO","EXTRAFOO" as issued within a requests FOCEXCEC procedure or a profile. For SQL configured servers, the search path will be *LIBL. The library path of the process can be controlled by commands, such as ADDLIBLE, either before server start or as issued within a requests FOCEXCEC procedure or a profile.
This column displays the name that will be assigned to each synonym. To assign a different name, replace the displayed value.
The user account that created the object or a collection of objects owned by a user.
You can check Fact or Dimension to generate the segment as a fact table or a dimension segment. If you are creating a cluster synonym, you can right-click a selected fact table and select Show Related Dimensions or Add Related Dimensions to show a list of related dimensions or add related dimensions to the synonym.
Is the name of the underlying object.
The object type (Table, View, and so on).
Select tables for which you wish to create synonyms:
- When creating base synonyms, you can select all tables in the list by clicking the Select All checkbox.
- To select specific tables, select the corresponding checkboxes.
Once you have selected the objects for which you want to create synonyms, click the Create Base Synonyms or Create Cluster Synonym button on the ribbon.
Sample Generated Synonym
An Adapter for Db2 synonym comprises a Master File and an Access File. This is a synonym for the table nf29004.
Generated Master File nf29004.mas
FILE=DIVISION, SUFFIX=DB2 ,$ SEGNAME=SEG1_4, SEGTYPE=S0 ,$ FIELD=DIVISION4, DIVISION4, I9, I4 ,MISSING=OFF ,$ FIELD=DIVISION_NA4, DIVISION_NA4, A25, A25 ,MISSING=ON ,$ FIELD=DIVISION_HE4, DIVISION_HE4, I9, I4 ,MISSING=ON ,$
Generated Access File nf29004.acx
SEGNAME=SEG1_4,TABLENAME=EDAQA.NF29004, CONNECTION=DB1,KEYS=1,WRITE=YES,$
Mapping Db2 Table and Column Attributes Into a Synonym
These mappings are OS-system specific:
Mapping Db2 Table Attributes
|
Platform |
Db2 Table Attribute |
Mapping in Synonym |
Notes |
|---|---|---|---|
|
UNIX/Windows |
COMMENT |
REMARKS |
|
|
IBM i |
REMARK LABEL |
REMARKS |
The synonym creation facility picks up a Db2 table TABLE level REMARK or LABEL value, whichever is not null, for use as a Master File REMARKS= value. If the Db2 table has both a populated REMARK and a populated LABEL, the REMARK value will be used. |
|
z/OS |
COMMENT LABEL |
REMARKS |
The synonym creation facility picks up a Db2 table TABLE level COMMENT or LABEL value, whichever is not blank, for use as a Master File REMARKS= value. If the Db2 table has both a populated COMMENT and a populated LABEL, the COMMENT value will be used. |
Mapping Db2 Column Attributes
|
Platform |
Db2 Column Attribute |
Mapping in Synonym |
Notes |
|---|---|---|---|
|
UNIX/Windows |
COMMENT |
DESCRIPTION |
|
|
IBM i |
LABEL COMMENT |
TITLE DESCRIPTION |
|
|
z/OS |
LABEL COMMENT |
TITLE DESCRIPTION |
|
Access File Keywords
This chart describes the keywords in the Access File.
|
Keyword |
Description |
|---|---|
SEGNAME |
Value must be identical to the SEGNAME value in the Master File. |
TABLENAME |
Name of the table or view. This value can include a location or owner name as follows: TABLENAME=[location.][owner.]tablename Note: Location is valid only with
Db2 CAF and specifies the subsystem location name.
For IBM i, the syntax is: TABLENAME=[library/]tablename |
CONNECTION |
Indicates a previously declared connection. The syntax is: CONNECTION=connection
Absence of the CONNECTION attribute indicates access to the default database server. |
DBSPACE |
Optional keyword that indicates the storage area for the table. For example: datasource.tablespace DATABASE datasource |
KEYS |
Indicates how many columns constitute the primary key for the table. Corresponds to the first n fields in the Master File segment. This attribute requires the columns that constitute the key to be described first in the Master File. See the KEY attribute below for information about specifying the key fields without having to describe them first in the Master File. |
KEY |
Specifies the columns that participate in the primary key without having to describe them as the first fields in the Master File. The syntax is: KEY=fld1/fld2/.../fldn |
WRITE |
Specifies whether write operations are allowed against the table. |
KEYFLD IXFLD |
Supply the names of the primary key and foreign key fields that implement the relationships established by the multi-table Master File. Together, KEYFLD and IXFLD identify the field shared by a related table pair.
KEYFLD and IXFLD must have the same data type. It is recommended, but not required, that their lengths also be the same. Note: An
RDBMS index on both the KEYFLD and IXFLD columns provides the RDBMS
with a greater opportunity to produce efficient joins. The columns
must have the same data type. If their length is the same, the RDBMS
handles the join more efficiently.
|
AUTO INCREMENT |
Set to Yes to enable autoincrementing. |
START |
Initial value in incrementing sequence |
INCREMENT |
Increment interval. |
INDEX_NAME INDEX_UNIQUE INDEX_COLUMNS INDEX_ORDER |
Indicate a name of the index in a database, uniqueness, name, and order of the indexed column(s). |
Managing Synonyms
Once you have created a synonym, you can right-click the synonym name in the navigation pane of either the WebFOCUS Reporting Server browser interface or ibi Data Migrator desktop interface to access the available options.
For a list of options, see Synonym Management Options.
Db2 Data Type Support
SQL Data Type mapping options are available in a report available from the WebFOCUS Reporting Server browser interface.
For more information, see Access File.
Controlling the Mapping of Variable-Length Data Types
The SET parameter VARCHAR controls the mapping of the Db2 data types VARCHAR. By default, the server maps this data type as variable character (AnV).
The following table lists data type mappings based on the value of VARCHAR.
|
Db2 Data Type |
Remarks |
VARCHAR ON |
VARCHAR OFF |
||
|---|---|---|---|---|---|
|
USAGE |
ACTUAL |
USAGE |
ACTUAL |
||
|
VARCHAR (n) |
n is an integer between 1 and 32768 |
AnV |
AnV |
An |
An |
Control the Mapping of Variable-Length Data Types
ENGINE DB2 SET VARCHAR {ON|OFF}
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Maps the Db2 data type VARCHAR as variable-length alphanumeric (AnV). This is required for Unicode environments. ON is the default value.
Maps the Db2 data type VARCHAR as alphanumeric (A).
BLOB Activation
Db2 data types that support the for bit data attribute including VARCHAR(n), where n > 256 and LONG VARCHAR, can be supported in the server as Binary Large Objects (BLOBs). This support is for both read and write access.
Activate BLOB
To activate this support, you must issue the following command in one of the supported server profiles
ENGINE DB2 SET CONVERSION LONGCHAR BLOB
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Activates long binary support. ALPHA is the default value.
BLOB Read/Write Support
For Db2 data types VARCHAR (>256) and LONG VARCHAR which have the for bit data attribute, the server provides read and write support using three server remote procedures routines. These routines are:
|
Routine |
Used to... |
|---|---|
|
EDABS |
Send binary image data to the server. |
|
EDABE |
Mark the end of the binary image. |
|
EDABK |
Purge the binary image from server storage. |
The sequence of operations for the client application is:
- Converts every binary byte of the image into 2 bytes of hexadecimal data and stores the result in an internal buffer. If the image is large, this could be split into manageable pieces.
- Sends the converted binary bytes to the server using remote procedure EDABS. The server converts the hex data back to binary and stores the image ready for INSERT/UPDATE into Db2.
- Repeats steps 1 and 2 until the complete image is sent to the server in hex format. It then sends remote procedure EDABE to mark the end of the image.
- The client application prepares an SQL INSERT/UPDATE using parameter markers for the columns of the row.
- The client application issues a BIND for the columns using CHAR(16) for the image column.
- The client application issues an EXECUTE USING command giving the data values for the row columns but using 'BLOB' for the image. The row will be INSERTed/UPDATEd using the image buffer stored on the server.
- Once the application has finished with the stored image on the server (and COMMITed the data), it should send the EDABK Remote Procedure to release server storage.
For full details and examples of how to maintain Db2 for bit data columns, see the API Reference and Connector for ODBC manuals.
Trailing Blanks in SQL Expressions
The new SQL Expression generator in the TABLE Adapter by default preserves literal contents, including trailing blanks in string literals and the fractional part and exponential notation in numeric literals. This allows greater control over the generated SQL.
In some rare cases when trailing blanks are not needed, the following syntax
ENGINE DB2 SET TRIM_LITERALS ON
is available to ensure backward compatibility.
Changing the Precision and Scale of Numeric Columns
You can alter the length and scale of numeric columns returned by a SELECT request to the server by creating different specifications in your login profile or in a stored procedure. The conversion settings are reflected in the Master File in the USAGE and ACTUAL formats of the fields generated by CREATE SYNONYM. This affects how the fields are processed and formatted by the server.
For more information, see Override the Default Precision and Scale.