Get database file from 400 with fields names?
John Larimer
To get a spoolfile to a PC, here is the scheme I used. Copy the spoolfile
to a DB file. Then copy the DB file to a PC file. Then you can then copy
it to your PC.
The commands would be:
CPYSPLF
and:
CPYTOPCD
Stamey wrote in message <356E14...@farther.com>...
-snip-
>Does anyone know a way to extract a spool file as a file without
>printing it? I would like to direct it to a sav file or something, then
>download it.
>
>Thanks,
>Chris Stamey
>--
>------------------------
>Network Engineer
>Datatrace Information Services Company Inc.
>sta...@farther.com
>http://www.farther.com/stamey
>--------------------------------
>Bradley's Bromide: "If computers get too powerful, we can organize them
>into a committee; that will do them in."
>
>Pursuant to US Code, Title 47, Chapter 5, Subchapter II, 227, any and
>all nonsolicited commercial E-mail sent to this address is subject to a
>download and archival fee in the amount of $1000 US. E-mailing denotes
>acceptance of these terms.
Stamey
I have been trying to figure out how to get a data file from the 400
with fields names in the file. Actually, these might me called fields
desciptions because when I connect using ODBC with Access, I get field
names but they are so cryptic, I can't figure out what they are.
Now, with this same situation, when I query the file using Query Manager
or interactive SQL, and display a report (or print it), There are
meaningful field names on each of the columns.
When I transfer the file from the 400 and open it with any type of PC
app, it has no field names in it at all.
After all of this, I just did a DSPFFD on the file and got back the data
definition of the file and it displays the field names but I cannot get
it out of the As400 except by printing it.
Bob Lepp
Hold the printer writer or assign the report to an outq with no writer,
Create a physical file 132 or 133 chrs long (CRTPF) use CPYSPLF to the
physical file, you have a choice of carriage control characters or not.
Then you can read the physical file containing the report, I guess
you'll parse it for field names??
Hope this helps.
Bob
Stamey wrote:
>
> I have been trying to figure out how to get a data file from the 400
> with fields names in the file.
snip
Ad van Dongen
Stamey,
DSPFFD FILE(FILE) OUTPUT(*OUTFILE) OUTFILE(*LIBL/OUTFILE)
gives you the field descriptions, col-headings etc. in a file you can
download
HTH
Ad.
Stamey wrote:
>
> I have been trying to figure out how to get a data file from the 400
Richard White
Stamey wrote in message <356E14...@farther.com>...
[snipped]
>After all of this, I just did a DSPFFD on the file and got back the data
>definition of the file and it displays the field names but I cannot get
>it out of the As400 except by printing it.
>Does anyone know a way to extract a spool file as a file without
>printing it? I would like to direct it to a sav file or something, then
>download it.
In a word - don't follow this route.
Instead take the option DSPFFD FILE(xlib/yfile) ........ OUTPUT(*OUTFILE)
OUTFILE(lib-nam/file-nam)
Specifying *OUTFILE will cause the OUTFILE (and other) parameters to appear
in the command prompt. To see the keywords in the command prompt - press
F11.
You will then get a physical file (in this example file 'file-nam' in
library 'lib-nam'). You can use Query, SQL QM Query, and from Client Access
File/Data Transfer , Operations Navigator and ODBC to view the contents of
this physical file.
This file will give you one record for each field in the record layout of
file yfile in library xlib (as above).
Regards,
Richard White
Software/400 Ltd.
Tel: +44 7050 606740
Fax: +44 7050 606742
Stamey
Thanks for the info on this. After experimenting, it seems that the AS400
puts cryptic field names on the actual file, and, somehow, dynamically adds
the descriptive field names into the display of the query on the file. the
thing is, these don't make it out into the file I save, the outfile.
Thanks for all of the info,
Chris Stamey
-------------------
Aspiring AS400 Administrator ;)
Stamey wrote in message <356E14...@farther.com>...
I have been trying to figure out how to get a data file from the 400
with fields names in the file. Actually, these might me called fields
desciptions because when I connect using ODBC with Access, I get field
names but they are so cryptic, I can't figure out what they are.
Now, with this same situation, when I query the file using Query Manager
or interactive SQL, and display a report (or print it), There are
meaningful field names on each of the columns.
When I transfer the file from the 400 and open it with any type of PC
app, it has no field names in it at all.
After all of this, I just did a DSPFFD on the file and got back the data
definition of the file and it displays the field names but I cannot get
it out of the As400 except by printing it.
Does anyone know a way to extract a spool file as a file without
printing it? I would like to direct it to a sav file or something, then
download it.
Thanks,
Caura
The AS400 does not put cryptic filed names in a file, the database
administrator does.
Formarly this field names could only be 8 chars long.
Aside of this he could (he should) add a TEXT descriptor tag and furthermore
a COLHDG tag with a maximum of 3 column heading textlines, which will be
displayed in Query results.
All of this can be found in the DDS reference.
Unfortunetely, as far as I know, you can't do anything with the Text or
Colhdg descriptives when you retrieve data p.e. via ODBC to MS Query, Excel,
or anything like that.
So here we end up indeed with cryptic field names, where other systems give
a nice description.
Stamey heeft geschreven in bericht ...
tho...@inorbit.com
Stamey:
Although there are circumstances where "the AS400" can create field names, it
probably didn't happen that way for the files you're looking at. At some
point, some programmer (or DB administrator or whoever) gave the fields those
names. But why were they cryptic?
Most likely because the primary programming language that was used to access
the files was RPG, which until recently had a six character limit on how long
field names could be in the programs. The AS/400 itself has always supported
10-character field names with 30-character aliases, but few programmers/DBAs
ever went so far as to attach decent alias names.
What you're seeing on your reports on the AS/400 are column headings, not
field names. These are assigned (by the programmer/DBA) as an attribute of
every field. If the attribute is unspecified, the 'cryptic' field name itself
will show as the column heading.
Now, as for how best to get those column headings through something like ODBC,
well, you might be a better expert at that than many of us who hang around
this NG. But if anyone has worked it out, an answer will show up sooner or
later. Until then, you can use the "system catalog" to cross-reference field
names to column headings. Try querying the file named QADBIFLD. It exists in
the QSYS library, but you shouldn't have to worry about the library name --
QSYS will almost certainly be the library list of any job you're likely to
run. (If it isn't, there's probably something else that's very wrong.)
Tom Liotta
In article <g0Xb1.561$_l2.5...@firenze.visi.net>,
"Stamey" <sta...@farther.com> wrote:
>
> Thanks for the info on this. After experimenting, it seems that the AS400
> puts cryptic field names on the actual file, and, somehow, dynamically adds
> the descriptive field names into the display of the query on the file. the
> thing is, these don't make it out into the file I save, the outfile.
>
> Thanks for all of the info,
> Chris Stamey
> -------------------
> Aspiring AS400 Administrator ;)
>
> Stamey wrote in message <356E14...@farther.com>...
> I have been trying to figure out how to get a data file from the 400
> with fields names in the file. Actually, these might me called fields
> desciptions because when I connect using ODBC with Access, I get field
> names but they are so cryptic, I can't figure out what they are.
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading
Jay Graves
If you have access to the DDS source you can add 'ALIAS' keywords to the fields
and the client access ODBC driver uses these names instead of the shorter ones.
We use Synon where I work and I wrote a program that adds ALIAS keywords to the
DDS using the Synon name.
Jay