WebQuery Synonym using External SQL Scripts

Troubleshooting

Problem

When creating a synonym to use for WebQuery, you have the option to use an External SQL Script that is stored in the Integrated File System. (IFS)

These scripts can be created with Access Client Solutions - Run SQL scripts where you have the option to save the script into an IFS stream file.

When saving the script you can specify what encoding to use for the new file and the default is UTF-8.

Symptom

However when you try to create a synonym with a UTF-8 script it will fail.

(FOC1400) SQLCODE IS -104 (HEX: FFFFFF98) XOPEN: 42601
(FOC1500) : 42601| Token Ë was not valid. Valid tokens: ( CL END GET SET CALL DROP
(FOC1500) : FREE HOLD LOCK OPEN WITH ALTER.
(FOC1405) SQL PREPARE ERROR.

The same problem will occur when running reports using synonyms that are base on UTF-8 scripts.

Cause

This is caused by the fact that the WebQuery Reporting Server expects any files for synonyms to be encoded in the job CCSID.

Diagnosing The Problem

When you do a DSPATR (Display Attribute) on the IFS stream file:

DSPATR OBJ('/home/storch/wq230.sql')

You will notice that CCSID is shown as 1208.

CCSID . . . . . . . . . : 1208

Resolving The Problem

When you save the script, you need to select the Data encoding as Use CCSID of the job.

Please note that when you replace an existing IFS file it will not change the CCSID of the file.

You should always save to a new stream file and not replace an existing file in order to change the CCSID successfully.

There should be no leading spaces at the beginning and no final semicolon ; at the end of the SELECT;

Document Location

Worldwide

[{"Type":"SW","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m50000000CacYAAS","label":"IBM i WebQuery->Synonym"}],"ARM Case Number":"TS004609097","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Version(s)"}]

Was this topic helpful?

Document Information

More support for:
IBM i

Component:
IBM i WebQuery->Synonym

Software version:
All Version(s)

Operating system(s):
IBM i

Document number:
6451137

Modified date:
18 May 2021

UID

ibm16451137

IBM Support

Tips