Using Views and Procedures in CA IDMS: avoiding DB005547 & other errors

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

In CA IDMS SQL, a view can be created on any data source except a function. However, if one is created on a Procedure, certain restrictions apply.

Environment

This applies to any environment where CA IDMS SQL is employed.

Resolution

In CA IDMS, a view can be defined on virtually any data source(s) except for a function. In the SQL Reference, the CREATE VIEW statement indicates that a View definition includes a query reference. The expansion of the query syntax indicates that the data source(s) can be "one or more tables, views, procedures or table procedures from which the result table is to be derived."

However, some additional considerations apply. If a view is defined on a procedure, then a query against the view must be coded as if it were selecting from a table, not a procedure. The distinction is that on a select against a procedure, parameters can be passed to the query using the syntax

Select * from <schema>.<procedure> (column = 'value ');

Logically, if a view is defined on a procedure, it might appear that this same syntax could be used to retrieve data from that view. However, this syntax will only work if the named data source is a procedure. Otherwise, an error message is produced such as

DB005547 C-4M324: Procedure CADEMO.V_CADEMO03 not found in catalog.

This error indicates that the syntax can be used only when the data source is defined as a procedure, and no procedure with the specified name can be found in the catalog. That is because ( column = 'value') is a procedure reference, which is specifically a way to pass parameter values to the procedure. This cannot be done with a view or any other type data source.

You can also pass parameters to a procedure by referencing the column name in a WHERE clause. The one big difference between the two is that the procedure reference only supplies input values to the procedure. Referencing the column in the where clause will also pass the value to the procedure but also filter the output based on that WHERE criteria. To select from a view that's defined on a procedure, then, use instead the syntax

Select * from <schema>.<view> WHERE <column> = 'value ';

Specifying the value in the WHERE clause for the view will pass that value along to the procedure and will also filter it in the output. If the view specifies column names that are different from those of the procedure on which it is defined, that could cause undesired results.

Additional Information

Syntax and detailed documentation on the CREATE VIEW statement can be found here:

https://docops.ca.com/ca-idms-ref/19/en/sql-reference/statements/create-view