Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Posts
    23
    Provided Answers: 1

    Question Unanswered: Schema name limited to 8 chars for table accessed via view

    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;

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Oct 2010
    Posts
    23
    Provided Answers: 1
    Redefining the view with an explicit schema name for each consituent table did the job. Thank you so much!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •