DB2 9.5 for Windows

Schema name is XPRESSION (9 characters). All tables can be accessed.

A view is defined as a complex query over some tables.

Search the view: select * from V_RULE_UCDINFO

Result: SQL0204N "XPRESSIO.T_RULE_UCDINFO" is an undefined name. SQLSTATE=42704

For some reason, in accessing the table name used to define the view, the schema name has been truncated to 8 characters, and the resulting object is not found.

This does not happen with every view, and not (apparently) with every DB2 isntallation.

Any ideas?

The entire view definition (not mine):

CREATE VIEW V_RULE_UCDINFO AS
SELECT
t_document.document_id as document_id, t_document.name as document_name, t_document.publishertype as document_type,
t_businesscategory.category_id as category_id, t_businesscategory.category_name as category_name,
t_ruleusage****le_usage_id as rule_usage_id,
t_ruleusage.bdt_id as bdt_id, t_businessdoctemplate.effective_date as effective_date,
t_rule_ucdinfo****le_id as rule_id, t_rule.name as rule_name, t_rule_ucdinfo****le_type as rule_type, t_rule.shared as shared,
t_rule_ucdinfo.ucd_type as ucd_type, t_rule_ucdinfo.static_def as static_def,
t_rule_ucdinfo.definition as definition, t_rule_ucdinfo.truncated as truncated, t_rule_ucdinfo.subdoc_id as subdoc_id
FROM
t_rule_ucdinfo JOIN t_rule ON t_rule_ucdinfo****le_id = t_rule****le_id
JOIN t_ruleusage ON t_rule****le_id = t_ruleusage****le_id
LEFT JOIN t_businessdoctemplate ON t_ruleusage.bdt_id = t_businessdoctemplate.bdt_id
JOIN t_document ON t_ruleusage.document_id = t_document.document_id
JOIN t_businesscategory ON t_document.category_id = t_businesscategory.category_id;

Fully qualify your table names and use correlation clause for each table. It will make the query easier to read and prevent using the default schema when one is not supplied, which in your case is wrong.

Andy

Redefining the view with an explicit schema name for each consituent table did the job. Thank you so much!