Excel>MS Query>AS400

[jla...@gmail.com]

Setup: Windows XP, Excel 2003, OS400 v5r2

I am trying to connect to the as400 using MS Excel & MSQuery but keep
getting the following error when I try to connect.

SQL0204 - systables in qsys2 type *file not found

I was able to do this on another AS400 in the past (don't recall OS400
version). I have Client Access installed so I think I have the
approporiate ODBC drivers already installed.

I read somewhere that the problem might be a missing or outdated
jt400.jar file. If this is the case, where do I get it & how/where do I
install it?

Can anyone suggest possible solutions to this problem?

Thanks,
Jim

[walker.l2]

The jt400.jar is the AS/400 Java Toolbox. If you are trying to connect
using JDBC, this could be the culprit. You can find find the jar on the
IBM website. If you are trying to connect another way (ODBC for
eample), then this Jar probably isn't relevant.

[walker.l2]

Are you sure you have authority to see the qsys2 library and systables
file?

[jla...@gmail.com]

walker,
Thanks for the reply. I am not explicitly trying to access qsys2. I am
guessing MS Query is trying to access this library/file to return a
list of tables for me to query. I tried changing my user type to SECOFR
and I got the same results.

I am not sure why it is looking at qsys2 since my default library is
qgpl.

Thanks,

Jim

[walker.l2]

It's looking in qsys because it thinks you are looking for a table
called systables (which is in the qsys2 library). Are you saying you
are trying to get the AS/400 to list all of the tables in the QGPL
library? Can you post the SQL you are attempting to run?

Walker.

[jla...@gmail.com]

I actualy haven't tried running any SQL yet. In Excel I go to

Data>Import External Data>New Database Query

It then asks me to choose a data souce. I select the iSeries ODBC
connection I set up previously on my machine. It is at this point that
I get the error message. I expected to get a list of libraries & tables
to select for my query using the query wizard or typed SQL statements.

Your post gave me an idea. I will check my connection settings once I
get back to the office. The library name setting in the connection
defaulted to QGPL which I Ieft alone. I am not sure then why it tried
to look in qsys2. Maybe it went through a library list? I should have
pointed out earlier that I'm an AS400 novice.

Thanks again

Jim

[Elvis]

Is it possible you're running this against a machine with one of the
older OS versions?

Availability of system catalogue views depends on the release you're
on. It started shipping with the base OS in V5R2 and was ptfed back to
V5R1 and V5R2. Here are the PTF numbers:
V4R5 : 5769SS1 SF67478
V5R1 : 5722SS1 SI04582

[Elvis]

meant to say, it was ptfed back to V4R5 and V5R1.

[jla...@gmail.com]

The problem turned out to be exactly what the message said - The
'systables' table is not in my qsys2 library. I also find out that
syscolumns is also missing.

As I understand it, these are system logical files so I figured it was
there all along. How do I create these files or what do I need to
install on the AS400 that will create this (logical) files?

Thanks,
Jim

[bluish]

I had a similar problem with SYSCOLUMNS. Such file "not found" can be absent or have metadata stored inside which are incorrect.
It can happen when copying a DB between 2 different machines, using option 21 (copy all objects, system and metadata objects included, like SYSIBM, QSYS and QSYS2). It's not the correct way, you should indicate all libraries to be copied one by one, avoiding system libraries. In such way the system will take charge of populating metadata in these system libraries. Instead if you make a bulk copy of every library you can corrupt destination DB if it is of a different version.
To solve this call IBM and ask them to tell you which recovery program you have to launch.

--http://compgroups.net/comp.sys.ibm.as400.misc/excel-ms-query-as400/1346113

[CRPence]

On 01-Feb-2014 05:22 -0800, bluish wrote:
> I had a similar problem with SYSCOLUMNS.

Not sure what is the point in resurrecting a thread from 2006; esp.
given no context from the replied-to message was included, thus anyone
using NNTP must look to some web archival to peruse the message thread...

But the answer to missing SQL Catalog VIEW object(s) from the QSYS2
library, which this ancient thread\topic was about, is to perform a
request to re-create those objects. Most likely origin, per the msg
CPF32D1 as part of system recovery actions in response to a problem
encountered with the *DBXREF feature; i.e. the feature attempted an
auto-recovery that may include DROP TABLE CASCADE of stored metadata.
The recovery is to perform, after changing the job CCSID to match the
CCSID of the character columns of QADBXREF in QSYS:
CALL QSYS2/QSQXRLF (CRT QSYS2)

The above is optionally preceded by a request to DROP the other SQL
Catalog VIEW file objects that are built over the System Database Cross
Reference (DBXREF) metadata files, if for example the desire was to have
all of them re-created at the same time:
CALL QSYS2/QSQXRLF (DLT QSYS2)

The 'CRT' invocation [to create missing catalog VIEWs in QSYS2; those
created over the *DBXREF data], may be run as part of the following if
one of the files is detected as missing:
CHKPRDOPT *OPSYS OPTION(01)

> Such file "not found" can be absent or have metadata stored inside
> which are incorrect.

If the metadata is absent or incorrect, there is likely a defect, or
there was some failure for which necessary recovery has not yet been
effected. However for TABLE, VIEW, INDEX, column, key, PACKAGE,
CONSTRAINT, ALIAS, TRIGGER, and ?perhaps a couple others like SEQUENCE?
[but *not* ROUTINES], the recovery is to refresh the *DBXREF data with a
request to Reclaim Storage (RCLSTG) [to include the *DBXREF] or Reclaim
Database Cross-Reference (RCLDBXREF)... and then pursue origin for any
like or repeat incidents of lost or incorrect information as a probable
defect [if the origin was not already determined to be a failure
understood to require that recovery action per a restriction or
non-defect action].

> It can happen when copying a DB between 2 different machines, using
> option 21 (copy all objects, system and metadata objects included,
> like SYSIBM, QSYS and QSYS2). It's not the correct way, you should
> indicate all libraries to be copied one by one, avoiding system
> libraries. In such way the system will take charge of populating
> metadata in these system libraries. Instead if you make a bulk copy
> of every library you can corrupt destination DB if it is of a
> different version.

The problem with it, is that it is undefined. What it is, needs to
be clearly defined.

Presumably that is an allusion to improper restore activity from
backups taken by GO SAVE option-21 [though the same applies to any
save\restore performed improperly] into one of the quasi-user libraries
such as QGPL, QUSRSYS, QSYS2, and SYSIBM. Following the appropriate and
documented restore\migration, restore\upgrade, or recovery scenario
avoids such difficulties. However... irrespective of what is done,
improperly or properly, the system automatically and implicitly
populates the catalog metadata; i.e. reflection of the properly or
improperly restored objects is done immediately, but as a proper
reflection there is no "corruption" with regard to the metadata.

The only issue that is problematic is a documented restriction, and
that is whereby libraries restored to a new name, the catalog VIEW files
specific to that restored SCHEMA will "point to" the old name; i.e. the
literal selection for SCHEMA_NAME is not updated in a catalog VIEW that
is private to that COLLECTION [aka SCHEMA or LIBrary]. The capability
to recreate those SCHEMA-specific catalogs is effected by the following
CL requests [where NEW_NAME is the new name for the *LIB object]:
CALL QSYS2/QSQXRLF (DLT NEW_NAME) /* dlt SQL catalog VIEWs in lib */
CALL QSYS2/QSQXRLF (CRT NEW_NAME) /* optional IMO; re-crt them */

> To solve this call IBM and ask them to tell you which recovery
> program you have to launch.

Not to sure what is alluded here, because as I suggested, there is
nothing about the "copying a DB between 2 different machines" [where DB
must refer to a SCHEMA; also known as a *LIB] for which a "recovery"
would be required for the metadata. If their improper restore effected
some other issue that needed some resolution, then the metadata would be
automatically\implicitly reflected according to whatever were those
recovery actions.

--
Regards, Chuck