On 20-Oct-2015 11:01 -0500, Tools/400 wrote:
On 20-Oct-2015 03:22 -0500, James Evans wrote:
When I run a search over a simple 4 letter string it falls over on
this line
LILINE = %SubSt(SRCDTA : LISTR : LILNG);
<<SNIP>>
It seems as if your SYSTABLES file in QSYS2 does not return a row for
your source file <<SNIP>>
FWiW, one might expect that a failure to find a "row for your source
file" would be exposed at that point in time; i.e. when\because the
SELECT ... INTO ... did not return a row, then an error would be
manifest in response to the sqlcode\sqlstate immediately after the
query, instead of allowing a number of lines of later code to operate
against the effective garbage-in established for the values of the
host-variables?
Please check your system cross reference catalog.
Also FWiW: Some potentially helpful [or just confusing and worthless]
information for the OP in that regard:
The preceding posts in the thread did not reveal what are the
'the_file' and 'the_libr' against which the search fails, so I left that
text to be replaced in the two suggested queries included below, that
perhaps James could try. For a specific source file name and library
name, the following queries represent a likeness of the alluded-as
failing [no row] query that could be tested in a dynamic SQL processor
[any that supports SELECT] such as Start Interactive SQL (STRSQL) or the
Run SQL Scripts GUI feature. Although I suppose, perhaps the search
could be performed over many files from a generic specification, and the
specific qualified file name remains to be determined?
SELECT substr(dbxatr, 1) , dbxtyp, int(DBXRDL)
FROM qsys.qadbxlfi /* qsys.qadbxref if a *ALLOBJ spcaut user */
where dbxlib = 'the_libr' /* replace with actual Library name */
and dbxfil = 'the_file' /* replace with actual File name */
and DBXREL='Y' and DBXATR not IN('IX','CL')
SELECT table_type, file_type, row_length
FROM qsys2.systables
where table_schema = 'the_libr' /* rpl w/ actual Library name */
and table_name = 'the_file' /* rpl w/ actual File name */
If either of those queries returns no rows, or the third column of
the one-row result-set is zero rather than the expected non-zero value,
then there is an apparent problem with the data [or a problem with
expectations of what should be returned in the failing scenario]. The
values returned for all columns probably would be of interest, if a row
is returned, irrespective the special case of the zero-value for the
third column.
For a no-rows issue:
• If the library of the file is QTEMP, then a row-not-found error
would be expected for the SELECT ... INTO, such that the values of the
host variables would remain set to what they were before the SQL was
invoked.
• The Reclaim Database Cross-Reference (RCLDBXREF) command by default
will run OPTION(*CHECK) LIB(*ERR) to diagnose any libraries known to
have data errors associated. If 'the_libr' appears in the output, then
the *DBXREF is aware there was a problem with the data for that library
name 'the_libr'.
• If a case of the missing row data is just something to be overcome
and there is nothing /wrong/ with the file per any of Display File
Description (DSPFD), Display File Field Description (DSPFFD), and Dump
Object (DMPOBJ), then probably simply issuing Move Object (MOVOBJ) of
the file to another library and then MOVOBJ the file back to the
original library will be sufficient [for that one file]. If the
check-dbxref tooling indicated issues, then more recovery than simply
the missing row tracking that one file might be more appropriate, for
which there is also the Reclaim Storage (RCLSTG) to request selecting
only the function to refresh the *DBXREF data: RCLSTG SELECT(*DBXREF)
For a zero-value as record length issue:
• If the file is a DDM file or ALIAS, then zero is the expected value.
• If a search includes [or otherwise does not exclude] a QRCL####
library, some of the files that reside in a QRCL library occasionally
will have no Record Format (RCDFMT) Object Type (*FMT), and will be
recorded in the *DBXREF data as having DBXRDL=0. When querying the
catalogs,
• AFaIK any other case of DBXRDL=0 is quite likely a defect [best to
be investigated]; a copy of the row if it exists [unfortunately the
journal likely is auto-purged, there is no before image, and the insert
of the data was too far in the past], the details about the life\history
of the x-ref and the problematic library and file might be able to
reveal something, along with things like the dump of the QDBXREFQ queue
object and the history log for anything related to the file or library
or more generally about the [operation or failure of] DBXREF and its job
controlling QDBSRVXR.
midrange.com
