RE: MCH1210 in RSTLIB (Receiver value too small to hold result.)

Chuck, I think you may have guided me to the solution.
A dspfd of the production view revealed a corrupted SQL CREATE STATEMENT. Had a lot of embedded white space between parameters, and probably most significant, no qualifiers on the from/join files and views. Contrasted with the dspfd of the views that came through the RSTLIB properly, those had all the parameters separated by a single space, and the "from" and "joins" had the qualifying library names.

The original problem object was created in production last February. My fix was done in May, but the create view source actually specifies "CREATE OR REPLACE...". Since there was an existing object, apparently the replace left the defects in the SQL CREATE STATEMENT.

I had sysadmin delete the production object, and create it anew from the same RUNSQLSTM, and we now have a production object with a properly qualified SQL CREATE STATEMENT. I'm betting that the next time they refresh the test library (provided they use a tape from tonight's save forward), the RSTLIB will be all better.

If I can reproduce the problem I was having with RUNSQLSTM (with the empty comments), I'll see if I can get some PMR action on that.

Many thanks!
-- Michael

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
CRPence
Sent: Wednesday, June 11, 2014 4:05 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: MCH1210 in RSTLIB (Receiver value too small to hold
result.)

On 11-Jun-2014 14:12 -0500, Koester, Michael wrote:

On 11-Jun-2014 13:29 -0500, CRPence wrote:

On 11-Jun-2014 12:43 -0500, Koester, Michael wrote:

<<SNIP>>
This SQL View is created from joining a physical file with a couple
other views. I verified from the joblog that the dependent views

had

been successfully restored prior to the attempt to restore the
problem view. Incidentally, the other five views (two of which join
to another view) restored without error.

When a VIEW is created the /statement text/ is maintained in the
*FILE object and in the catalogs. When the VIEW is restored to a new
library, the qualified table-references must be updated to reflect
the new schema [library] name.
<<SNIP>>

I don't know if "nonqualified" table names would need to be updated

or

not, but for the most part we don't qualify in our source code. We
rely on the library list for that, and that is the case here.

Irrespective of qualification for table-references [e.g. references
on the FROM clause, irrespective of TABLE vs VIEW] on the original DDL
CREATE VIEW, the Database SQL stores originally [and will also update
the /statement text/ as required; e.g. for RNMOBJ, MOVOBJ, RSTOBJ, and
??] to show the _fully qualified names_ that should *always* accurately
reflect the current definition.

A review of either\both of the "SQL view create statement" in the
Display File Description (DSPFD) output and the VIEW_DEFINITION column
data in the SYSVIEWS SQL Catalog VIEW [from the data in the column
DBXDFN of the System Database Cross-Reference file QADBXREF in QSYS]
for any VIEW would show the qualified name [using dot-naming aka SQL-
naming].

For example, if I issue the following [first] line of DDL CREATE
VIEW statement below while System-naming is in effect, then the output
of a DSPFD will appear as shown in the next line, and the output of a
query of the catalog via SELECT VIEW_DEFINITION FROM SYSVIEWS WHERE
NAME='MYFEAT' will appear as shown in the next line:

create view QGPL/MYFEAT as select * from SYSFEATURE

CREATE VIEW MYFEAT AS SELECT * FROM QSYS2.SYSFEATURE

SELECT * FROM QSYS2.SYSFEATURE

--
Regards, Chuck