A system catalog is essentially a
database file that i5/OS maintains behind the scenes and then uses to track
objects and other information on the system. And a little bit of basic knowledge
about them can make programming simpler for you.
As an example, let's
talk about QADBXREF (the Database Cross-Reference system catalog). This system
catalog is a physical file with an object of type *FILE and an attribute of
PF-DTA. It contains a record for each database file and SQL table on your
iSeries. Whenever you add a file to your iSeries—using CRTPF, CRTLF,
CREATE TABLE, etc.— i5/OS adds a record to QADBXREF containing all sorts
of information about that file: file name, library name, attribute of the file
(physical, logical, table, index, view, etc.), owner.... In fact, here's a list
of all the fields in QADBXREF:
|
Field Description
|
Field Name
|
Field Type |
Start Position
|
|---|---|---|---|
|
File name
|
DBXFIL
|
10 A
|
1
|
|
Library name
|
DBXLIB
|
10 A
|
11
|
|
Dictionary name
|
DBXDIC
|
10 A
|
21
|
|
User profile name of owner
|
DBXOWN
|
10 A
|
31
|
|
File text
|
DBXTXT
|
50 A
|
41
|
|
PF=physical, LF=logical, TB=table
|
DBXATR
|
2 A
|
91
|
|
E=ext
|
DBXLNK
|
1 A
|
93
|
|
I=IDDU, S=SQL, C=CRTDTADCT, X
|
DBXSQL
|
1 A
|
94
|
|
D=data file, S=source file
|
DBXTYP
|
1 A
|
95
|
|
Maximum number of fields
|
DBXNFL
|
5 S 0
|
96
|
|
Maximum number of key fields
|
DBXNKF
|
5 S 0
|
101
|
|
Maximum record length
|
DBXRDL
|
11 S 0
|
106
|
|
Dictionary internal file
|
DBXIDV
|
11 S 0
|
117
|
|
Relational file: Y=Yes, N=No
|
DBXREL
|
1 A
|
128
|
|
Long file name
|
DBXLFI
|
130 A
|
129
|
|
Long file name is quoted
|
DBXLFQ
|
1 A
|
259
|
|
C=CASCADED, Y=Yes (local),
|
DBXWCO
|
1 A
|
260
|
|
Y=Yes, N=No
|
DBXUPD
|
1 A
|
261
|
|
D=No, V=Yes (allow NULL)
|
DBXUNQ
|
1 A
|
262
|
|
Long file description
|
DBXREM
|
2002 A | 263 |
|
SQL VIEW definition
|
DBXDFN
|
10002 A
|
2265
|
|
File change timestamp
|
DBXATS
|
Z
|
12267
|
|
Library name
|
DBXLB2
|
130 A
|
12293
|
|
Alias relational database
|
DBXADB
|
20 A
|
12423
|
|
Alias library name
|
DBXALB
|
130 A
|
12443
|
|
Alias file name
|
DBXAFL
|
130 A
|
12573
|
|
Alias member name
|
DBXAMB
|
12 A
|
12703
|
|
Y=Yes, N=No
|
DBXINSERT
|
1 A
|
12715
|
|
Primary ASP
|
DBX_PASP
|
5 I 0
|
12716
|
|
Y=S/O logical, D=DYNSLT, N
|
DBXSO
|
1 A
|
12718
|
|
Y=system File
|
DBXSYS
|
1 A
|
12719
|
|
Number of partitions
|
DBXPART
|
5 I 0
|
12720
|
|
Index over partitioned TBL
|
DBXISPAN
|
1 A
|
12722
|
So what can system catalogs do for you? They provide an alternative means
to solve certain problems. If you have an application that needs a list of files
on the system, you can...
- Get that list using an API. This requires some complex coding, and probably a user space.
- Use DSPFD to generate a list of files to an output file. This requires a front-end CL driver and possibly some database overriding.
- Simply read QADBXREF from your program.
System catalogs have
logical files, so you can use the access path that best meets your needs. Use
DSPDBR and DSPFD to find the best logical. You can also create your own
logicals, which many applications do.
SQL creates logical files over
system catalogs as well—for example, SYSTABLES. In fact, a SYSTABLES file
is generated for every schema created through SQL, selecting only the files in
that schema.
Now, some things to be aware of when using system
catalogs:
- Always open them as input, never as update. Access them only as read-only.
- You may not have enough authority to use a system catalog. If not, try one of its logicals.
- Study the record format and fields of a system catalog carefully; some information you need may not be immediately available and will need to be calculated.
Other useful system catalogs:
- QADBIFLD lists every field and column on your iSeries.
- QADBKFLD lists every key field and column on your iSeries.
- QADBXTRIGB lists the triggers on your iSeries. I once published a utility called Trigger Tracker that uses this system catalog.
- QAOKP01A lists the users in the System Distribution Directory.
- QATMSMTPA contains the email addresses for the users in the System Distribution Directory.
Here is a sample program using QAOKP01A
and QATMSMTPA:
|
System catalogs are a great place to get information about your iSeries
objects and are an alternative to APIs and command outfiles. Just be sure to use
them carefully.
Doug Eckersley is the chief
iSeries developer at Dominion Homes in Dublin, Ohio. He has over 16 years of
application development experience and is the co-author of Brainbench’s
RPG/IV certification test.
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online