No index information in file QSYS2/SYSINDEXES

[Thomas Raddatz]

Hello to all specialists,

Currently I am working on a re-engineering project of an AS/400 database
using a pc-based database development tool.

The problem I have is that there are no index information stored in the
table QSYS2/SYSINDEXES if the indexes had been described by DDS. If I
create an index by sql´s CREATE INDEX the information of the index is
been stored into SYSINDEXES. What a strange behavior!

The information is necessary for the re-engineering tool.

Any suggestions?

Thomas Raddatz, Germany.

--
*=====================================================*

e-mail Adresse:
Thomas....@Online-Club.de

Homepage:
http://www.online-club.de/~Thomas.Raddatz/

*=====================================================*

[Charles R. Pence]

Thomas Raddatz wrote:

> <<SNIP>>

> The problem I have is that there are no index information stored in the
> table QSYS2/SYSINDEXES if the indexes had been described by DDS. If I
> create an index by sql´s CREATE INDEX the information of the index is
> been stored into SYSINDEXES. What a strange behavior!
>
> The information is necessary for the re-engineering tool.
>
> Any suggestions?

DSPFD QSYS2/SYSINDEXES will show that there is selection requiring that the
file's attribute be an SQL INDEX <ie. WHERE ... IX.DBXATR = 'IX'>, for which
only a file created using the SQL CREATE INDEX will be selected. If this
VIEW does not meet your requirement, then use a VIEW which effects your
desired results; for if you were using only SQL, you may not want those DDS
created LFs included -- the pitfalls of mixed-mode environment.

Regards, Chuck
All comments provided "as is" with no warranties of any kind whatsoever.

[Guillermo]

Thomas:

Check the following files, may be they can help you
from DB2 for AS/400 SQL Reference V4R1
H.0 Appendix H. DB2 for AS/400 Catalog Views
The views contained in a DB2 for AS/400 catalog are described in this
section. The database manager maintains a set of tables containing
information about the data in the database. These tables are collectively
known as the catalog. The catalog tables contain information about
tables, parameters, procedures, packages, views, indexes, and constraints
on the AS/400 system. The catalog tables include the following files in
the QSYS library:

o QADBXREF
o QADBPKG
o QADBFDEP
o QADBXRDBD
o QADBFCST
o QADBFCST
o QADBCCST
o QADBIFLD
o QADBKFLD

The catalog tables also include the following files in the QSYS2 library:

o SQL_LANGUAGES
o SYSPARMS
o SYSPROCS

Unfortunately seems that you do not have the posibility of specifying
SYSINDEXES for LF created by DDS :(

hth

Guillermo

Thomas Raddatz wrote in message <36474B6A...@online-club.de>...

>Hello to all specialists,
>
>Currently I am working on a re-engineering project of an AS/400 database
>using a pc-based database development tool.
>

>The problem I have is that there are no index information stored in the
>table QSYS2/SYSINDEXES if the indexes had been described by DDS. If I
>create an index by sql´s CREATE INDEX the information of the index is
>been stored into SYSINDEXES. What a strange behavior!
>
>The information is necessary for the re-engineering tool.
>
>Any suggestions?
>

[Thomas Raddatz]

Thank you Chuck,

that was the idea that I missed.

Thomas Raddatz, Germany.

Charles R. Pence schrieb:
>
> Thomas Raddatz wrote:
>
> > <<SNIP>>

> > The problem I have is that there are no index information stored in the
> > table QSYS2/SYSINDEXES if the indexes had been described by DDS. If I
> > create an index by sql´s CREATE INDEX the information of the index is
> > been stored into SYSINDEXES. What a strange behavior!
> >
> > The information is necessary for the re-engineering tool.
> >
> > Any suggestions?
>

> DSPFD QSYS2/SYSINDEXES will show that there is selection requiring that the
> file's attribute be an SQL INDEX <ie. WHERE ... IX.DBXATR = 'IX'>, for which
> only a file created using the SQL CREATE INDEX will be selected. If this
> VIEW does not meet your requirement, then use a VIEW which effects your
> desired results; for if you were using only SQL, you may not want those DDS
> created LFs included -- the pitfalls of mixed-mode environment.
>
> Regards, Chuck
> All comments provided "as is" with no warranties of any kind whatsoever.

--

[Thomas Raddatz]

Thank you Guillermo,

for pointing me to that interesting manual.

Thomasd Raddatz, Germany.

Guillermo schrieb:

> >The problem I have is that there are no index information stored in the
> >table QSYS2/SYSINDEXES if the indexes had been described by DDS. If I
> >create an index by sql´s CREATE INDEX the information of the index is
> >been stored into SYSINDEXES. What a strange behavior!
> >
> >The information is necessary for the re-engineering tool.
> >
> >Any suggestions?
> >

> >Thomas Raddatz, Germany.

[Charles R. Pence]

re: QSYS/QADB* physical files
Note that the authority to these files is not sufficient for general access; that
a logical file <per DSPDBR on the physical> should be used instead -- creating one
of your own if *PEON users are going to need access <caveat WRKMSGD CPF32D1>

[Thomas Raddatz]

Good to know.

Thank you Chuck.

Thomas Raddatz, Germany.

Charles R. Pence schrieb:
>

--

[Bill Davidson]

Any view/logical file built over the QADB* files is subject to deletion (should the
cross reference require rebuilding), with no system recovery of your views. That is, if
the cross reference were to be found damaged, all views/lfs would be deleted, the PFs
recreated (should happen almost never). The system will recreate all of the system
lfs/views, but any user views/ls would be deleted, and not recreated. Thus, first touch
of these objects should be prepared to handle their not existing (anymore).