not table, view, or physical file
Calvin
in XYZ not table, view, or physical file.
Please have mercy on a newbie. I know very little about the AS/400
and I keep getting this error message that makes no sense to me. My
company has a herd of AS/400s and I've had little trouble using IBM's
SQL tool that they have made for Linux until just recently. I Googled
the error message and the best I could find was a blurb from an IBM
manual that said to use a CR program instead, basically "don't do
that". This message is coming from cwbrunsql on my Linux box when I
try to query the file in question. I can use Query400 with the file
just fine but again cwbrunsql gives me nada.
What is it about this file that makes it not work with cwbrunsql and
is there some workaround I can try?
Peter H. Coffin
Maybe it's a device file, or a message file, or a DDM[1] file or any of a
whole slew of objects that aren't tables, views, or physical files. The
DDM is the most likely suspect, since they LOOK a lot like regular files
or tables, but since not all the data is actually on *that particular*
machine, the DB2 engine can't do anything with it.
[1] Distributed Data Management -- basically like a alias for a file on
another machine. An actual record-interacting program can use it like a
regular file, with some networking magic taking care of actually
fetching the data from the hosting machine transparently. Nifty, but you
can't query them.
--
25. No matter how well it would perform, I will never construct any
sort of machinery which is completely indestructible except for one
small and virtually inaccessible vulnerable spot.
--Peter Anspach's list of things to do as an Evil Overlord
CRPence
> On Thu, 29 Apr 2010 14:36:07 -0700 (PDT), Calvin wrote:
>> Error msg: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7011 -
>> ABCDEFG in XYZ not table, view, or physical file.
What does DSPFD XYZ/ABCDEFG show for the file type? What is the
result from the following request?: SELECT TABLE_TYPE, FILE_TYPE
FROM QSYS2.SYSTABLES WHERE SYS_TNAME='ABCDEFG' AND SYS_DNAME='XYZ'
>> Please have mercy on a newbie. I know very little about the
>> AS/400 and I keep getting this error message that makes no
>> sense to me. My company has a herd of AS/400s and I've had
>> little trouble using IBM's SQL tool that they have made for
>> Linux until just recently. I Googled the error message and the
>> best I could find was a blurb from an IBM manual that said to
>> use a CR program instead, basically "don't do that". This
>> message is coming from cwbrunsql on my Linux box when I try to
>> query the file in question. I can use Query400 with the file
>> just fine but again cwbrunsql gives me nada.
About the only *FILE object that a Query/400 query definition can
query that SQL can not, is an SQL INDEX. The "based on" database
file in the DSPFD output would show the file name(s) that the SQL
statement should reference, instead of the INDEX name.
>> What is it about this file that makes it not work with
>> cwbrunsql and is there some workaround I can try?
Unless the /file/ appears in SQLTables() results, it is probably
not supported to be referenced in a SQL SELECT statement.
> Maybe it's a device file, or a message file, or a DDM[1] file or
> any of a whole slew of objects that aren't tables, views, or
> physical files. The DDM is the most likely suspect, since they
> LOOK a lot like regular files or tables, but since not all the
> data is actually on *that particular* machine, the DB2 engine
> can't do anything with it.
A message file is object type *MSGF, so the reference to an
object name which is a *MSGF would be no different than referring to
any other object type that is not a *FILE; i.e. -204 except when
some *FILE by that name does exist.
> [1] Distributed Data Management -- basically like a alias for a
> file on another machine. An actual record-interacting program can
> use it like a regular file, with some networking magic taking
> care of actually fetching the data from the hosting machine
> transparently. Nifty, but you can't query them.
Although a remote DDM file [i.e. not a /local/ DDM file as
implementation object for the SQL ALIAS] can not be queried by SQL,
the OPNQRYF, Query/38, and the QQQQRY API all support query of the
DDMF device file. Note that Query/400 does not.
Regards, Chuck
Calvin
> Peter H. Coffin wrote:
> > On Thu, 29 Apr 2010 14:36:07 -0700 (PDT), Calvin wrote:
> >> Error msg: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7011 -
> >> ABCDEFG in XYZ not table, view, or physical file.
>
> What does DSPFD XYZ/ABCDEFG show for the file type? What is the
> result from the following request?: SELECT TABLE_TYPE, FILE_TYPE
> FROM QSYS2.SYSTABLES WHERE SYS_TNAME='ABCDEFG' AND SYS_DNAME='XYZ'
>
> Unless the /file/ appears in SQLTables() results, it is probably
> not supported to be referenced in a SQL SELECT statement.
>
The whatever it is doesn't appear at all in the results from: SELECT
TABLE_TYPE, FILE_TYPE FROM QSYS2.SYSTABLES WHERE SYS_DNAME='XYZ'.
However, with that list I was able to find the same data in one of the
files that was listed. Thanks again for your help.
CRPence
No problem.
Since the SQL catalog VIEW named SYSTABLES excludes the /file/
attribute of 'IX' [i.e. excludes all database *FILE objects with the
SQL INDEX attribute], and since the Query/400 feature allows
querying an SQL INDEX whereas the SQL does not, the *FILE object for
which the msgSQL7011 was issued is almost positively an SQL INDEX.
Regards, Chuck