Refresh Web Query Synonym

(1)

(2)

2

Added to CRTWQSYN command. Available in 1.1.2 HotFix 5

Use to synchronize Web Query synonym when structure/format of underlying table changes. Can be called from command line, CL program, Job Scheduler.

The refresh feature has 3 primary functions:

Adds new columns from the underlying table or view.

Deletes dropped columns from the underlying table or view

(3)

Available in DB2 Web Query 1.1.2 HotFix 5

Supports the refresh of synonyms created via web metadata console, Developer Workbench, and CRTWQSYN command.

Only supported for synonyms based on DB2 cli adapter. No support for DB Heritage, Query/400, JD Edwards, or SQL Server adapters.

*LOCAL databases only (no support for tables on remote systems, partitions, or databases).

Supported file types: – Table – Physical File – SQL View – Logical file

*ALL and generic (wildcard) search supported in the FILE parameter. Useful for refreshing ALL files in a library (or all files that start with ORD*)

(4)

4

Create DB2 Web Query Synonym (CRTWQSYN)

File (table/view) name . . . FILE ORD* Schema (library) name . . . SCHEMA QWQCENT

Synonym prefix . . . PREFIX MYPREFIX_ Synonym suffix . . . SUFFIX *NONE Application folder . . . APPFLR *BASEAPP

With foreign keys . . . FRGNKEYS *NO *YES, *NO Include library qualification . QUALLIB *YES *YES, *NO

Existing synonym option . . . . OPTION *REFRESH *NONE, *REPLACE, *REFRESH Use field short name as alias . SHORTALIAS *NO *YES, *NO

Example:

CRTWQSYN FILE(ORD*) SCHEMA(QWQCENT) PREFIX(MYPREFIX_) APPFLR(*BASEAPP) OPTION(*REFRESH)

In this example, existing synonyms (in BASEAPP with prefix MYPREFIX_) will be refreshed for all files that start with the characters “ORD” in the library QWQCENT.

QWQCENT/ORDDETAIL MYPREFIX_ORDDETAIL QWQCENT/ORDHEADER MYPREFIX_ORDHEADER QWQCENT/ORDERS2011 MYPREFIX_ORDERS2011

(5)

Add new columns

If new columns are added to the underlying table or view, the refresh feature will add them to the existing synonym. All existing synonym fields and customizations are persevered in the Master File

SQL: ALTER TABLE ORDERS ADD COLUMN NEWFIELD CHAR (10) CRTWQSYN FILE(ORDERS) SCHEMA(QWQCENT) PREFIX(ZZCOBB_) OPTION(*REFRESH)

---Column added to synonym---Segment: zzcobb_orders

Column: NEWFIELD Actual: A10

(6)

6

Remove dropped columns

If columns are dropped from the underlying table or view, the refresh feature will remove the corresponding field from the existing synonym unless there are

dependencies in the synonym. A dependency is defined as any one of the following.

A define/virtual field with an expression referencing the synonym field. A computed field with an expression referencing the synonym field. A filter with an expression referencing the synonym field.

An OLAP dimension or hierarchy referencing the synonym field. A Business View folder containing the synonym field.

A DBA restriction based upon the synonym field.

If any of the above dependencies exist, the field is not deleted from the Master File.

This dependency checking can be ignored and the column subsequently dropped from the synonym.

(7)

Remove dropped columns

SQL: ALTER TABLE ORDERS DROP COLUMN OLDFIELD

CRTWQSYN FILE(ORDERS) SCHEMA(QWQCENT) PREFIX(ZZCOBB_) OPTION(*REFRESH)

---Column dropped from synonym---Segment: zzcobb_orders

Column: OLDFIELD Actual: A10

(8)

8

Update altered columns

If certain column attributes are changed in the underlying table or view, the refresh feature will apply those changes to the corresponding field in the synonym. The following attributes are updated if the data type or length of the corresponding table/view column is changed:

Actual

For example, if a database table column was altered from CHAR(4) to CHAR(6), the Actual value for that field in the synonym would be changed from A4 to A6.

Usage

If the Actual value for the synonym field has changed, it is possible that the Usage value will need to be updated accordingly.

Where appropriate, the Refresh utility attempts to preserve any customized usage

values/attributes such as C (Comma inclusion as thousands separator) and M (Floating Currency)

(9)

Update altered columns

SQL: ALTER TABLE ZZCOBB/ORDERS ALTER COLUMN WARRANTYEXP SET DATA TYPE NUMERIC ( 20, 2)

CRTWQSYN FILE(ORDERS) SCHEMA(QWQCENT) PREFIX(ZZCOBB_) OPTION(*REFRESH)

---Column updated in synonym---Segment: zzcobb_orders

Column: WARRANTYEXP Prv Actual Value: P12

New Actual Value: P11 Prv Usage Value: P24.2CM

(10)

10

Pos Description

1 _{Trace/debug level. Valid values are 0-3. Default is 2.}

0 = No tracing

1 = Include input parameter values, errors, and summary info

2 = Same as 1 plus each segment analyzed and all specific columns added, updated, dropped 3 = Same as 2 plus print comparison of all columns before and after refresh.

2 _{Backup synonym flag. Indicates if a backup of the master file is to be made before refresh. If on, the}

program will make a backup copy of the master file and use a timestamp as part of the new name.

For example, if you are refreshing a synonym named cen_orders.mas, a copy of the synonym will be made before the synonym is refreshed and given the following name:

cen_orders_20111221144653180.mas

The backup copy resides in the same application path as the requested synonym. A backup access file (.acx extension) is NOT made as it wasn't really necessary. The idea is to give users a way to get back their

original synonym (prior to the refresh request) in case it is somehow corrupted by the refresh.

3 _{Ignore dependencies flag. Indicates whether to ignore dependencies (like define fields and filters) when}

refreshing synonym. For example, if it is on, a column removed from the table will also be removed from synonym (even if a define field is based on that deleted column). If this setting is off, a column (that was dropped from the table) will not be removed from the synonym if there is a dependency in the synonym (such as a define field) based on the column.

Example to update trace/debug level:

(11)

Pos Description

4 _{Compare only flag. Turn on if you only want to generate a spool file trace of what}

changes the Refresh would make. If turned on, the refresh is NOT applied to the synonym and it remains unchanged.

5 _{Update column title. Turn on if you want to overlay all column titles with values}

from table/view that synonym is based on. Useful if you changed column titles in table/view and you want to apply those new titles to synonym.

6 _{Update column description. Turn on if you want to overlay all column descriptions}

with values from table/view that synonym is based on. Useful if you changed column descriptions in table/view and you want to apply those new descriptions to synonym. If off, the column title values in the synonym are preserved.

7 _{Update column names. Turn on if you want to overlay all column names with}

values from table/view that synonym is based on. Useful if you changed column

long names in table/view and you want to apply those new names to synonym. If off, the column names in the synonym are preserved. Regardless of the value of this

(12)

12

To help you determine what was changed during a Refresh process, a log is

generated and written to a spooled file. It can be located by issuing the WRKSPLF command searching for File QSYSPRT and User Date RFSWQSYN.

(13)

Sample log spooled file:

---Input parameters---File. . .: ORDERS

Schema. .: CUSTLIB File Type: *ALL Prefix: CUSTLIB_ Suffix: *NONE

App Folder: ODDSENDS With Foreign Keys:*NO

Include library qualification: *YES Existing synonym option: *REFRESH Use field short name as alias: *NO QWQRFSSYN Data area values

App path: /qibm/UserData/webquery/ibi/apps/ Trace level: 2

Backup master file: 1 Ignore Dependencies: 0 Compare Only Mode: 0 Update column titles: 0 Update column descriptions: 0 Update column field names: 0

---Analyzing Web Query synonym CUSTLIB_ORDERS for refresh... Segment Name: CUSTLIB_ORDERS

Table Name: CUSTLIB/ORDERS

---Column updated---Segment: CUSTLIB_ORDERS

Column: REVENUE Prv Actual Value: P5 New Actual Value: P7 Prv Usage Value: P10.2C New Usage Value: P14.2C Prv Column Title Value:

New Column Title Value: REVENUE ---Summary

Info---New columns added . . : 0 Updated columns . . . : 1 Dropped columns . . . : 0 Dropped dependencies . : 0

(14)

14

Basic Troubleshooting

1. Check job logs and log spooled file.

2. If not enough info in log spooled file, increase trace/debug value:

CHGDTAARA DTAARA(QWEBQRY77/QWQRFSSYN (3 1)) VALUE(‘3')

3. Verify based on table/view exists

4. Verify synonym exists (with specified prefix and suffix)

5. Error QWQ0503 (No candidate objects found). Check QCCSID system value or job CCSID. If 65535, change to appropriate language CCSID and run again.