RE: CPYTOIMPF and CSV Format

The fact that it's not "your" database is obvious hair-splitting.  I'm sure
it's not your iSeries either.

Despite misdirection to what you insist the rest of the world is doing, this
really is about the difference between fixed-length data types and
variable-length data types.  It's also about your knee-jerk reaction to
blame the iSeries every time your job gets hard (see also -- "my FTP is
taking 10 hours -- it obviously must be a problem with iSeries design").

If Excel exports dates as numbers requiring translation do you immediately
blame Microsoft for being behind the times?

I've asked the Oracle DBAs who report to me how they would do this in
Oracle.  Their answers started with -- "there's not a native CSV utility so
you'd have to use..." or "you'd have to write..."   If you were doing this
in Oracle you'd be using a third-party tool or writing SQL.  You'd have to
manually trim any fixed-length fields (or variable-length fields for that
matter) while concatenating the delimiters and testing for nulls using
"native" Oracle.  I think Oracle is a pretty good example of the rest of the
world.  But because SQL is considered open you won't see whiners such as
yourself insisting it's just another way Oracle doesn't play well with
others.

The iSeries has a CSV export utility that some other databases do not
provide.  It takes a lazy, closed mind to view it as a liability instead of
an asset.

You're so often generalizing about the industry, suggesting that everyone
else does it the same way and the iSeries does it differently.  Like you,
for years I've worked on integration projects between VMS, Ingres, OS/400,
various Unixes, Oracle, Win2K and NT, and SQL Server, to name a few.  EBCDIC
to ASCII has been such an non-issue.  And there are interface compatibility
problems between all platforms.

You obviously get under my skin with your "typical iSeries" comments because
I've worked with people like you for years.  If Oracle or Microsoft's "open"
LDAP implementations are restrictive, proprietary, and marginally compatible
it's no big deal.  If three different Unix operating systems require three
different administrative skill sets then I guess we'll have to deal with it.
If our open SAN doesn't work with one of our open servers then one of them
will have to be replaced.  If SQL is incompatible between any two open
databases it's just the way it is.  If someone has to cobble together a Korn
shell script to clean up the end of record characters on a flat file they
just do it.  I've watched conversion files passed through five filters of
grepping.  I've seen SQL Server exports brought into Access AND Excel for
reformatting.  Of course, the moment someone has to adapt something from or
to an AS/400 there's always some clown like you complaining about how they
wouldn't have to do this if it were coming to or from a PC or a Unix server.
A network engineer can debug Unix auto port aggregation for months while
dismissing an AS/400's flapping NIC as a problem with AS/400 network
compatibility.  (It was, however, great fun to see the open systems bigots
crawl into their hole the day we got an $80K quote to port a fairly simple
HP/UX and AIX compatible product to Solaris.)

I agree with anyone who says that the CPYTOIMPF feature could be enhanced by
providing an option to trim blank characters from fixed length fields.  It
would have to be an option because the default should certainly be to
maintain the integrity of the data type.

What I have been taking exception to is the Ken Slaugh dim view.  "To me,
this is another classic reason the 'iSeries doesn't play well with others.'"

The fact that IBM has provided a built-in facility for creating CSV's is
still an example of how the iSeries DOES play well with others.  If they
deleted the CPYTOIMPF command from the OS they'd be in good company.  The
fact that the command doesn't work exactly the way you need it today,
because you blew your deadline downloading files on a poorly configured
system, is an example of your tough luck.

Then again, if your data conversion has come down to this kind of last
minute hacking without testing or an implementation plan I suppose you're
gonna need to blame something.

-Jim

James P. Damato
Manager - Technical Administration
Dollar General Corporation
<mailto:jdamato@xxxxxxxxxxxxxxxxx>



-----Original Message-----
From: Ken.Slaugh@xxxxxxxxxx [mailto:Ken.Slaugh@xxxxxxxxxx]
Sent: Thursday, August 14, 2003 5:21 PM
To: Midrange Systems Technical Discussion
Subject: RE: CPYTOIMPF and CSV Format




<snip>
Your database isn't playing well with others, and you're blaming it on the
system.
</snip>

Actually.. It isn't even my database. It's 10.5gb of data within 15 files
created by DDS on a legacy system. I don't know why the CPYTOIMPF wouldn't
by default convert to variable length data since the rest of the world,
others, seem to prefer such a format.

Ken Slaugh (707) 795-1512 x118
Chouinard & Myhre, Inc.
CA/400 Certified Specialist
iSeries Network/MSE Administrator
http://www.cm-inc.com/


 

                      Jim Damato

                      <jdamato@xxxxxxxxxxx        To:       "'Midrange
Systems Technical Discussion'"                
                      al.com>                      <midrange-l@xxxxxxxxxxxx>

                      Sent by:                    cc:

                      midrange-l-bounces@x        Subject:  RE: CPYTOIMPF
and CSV Format                             
                      idrange.com

 

 

                      08/14/2003 02:08 PM

                      Please respond to

                      Midrange Systems

                      Technical Discussion

 

 





CPYTOIMPF converts nulls to nulls, fixed length character fields to fixed
length delimited strings, and variable length character fields to variable
length delimited strings.  If a column in your table is defined with a
fixed
length data type that does not allow nulls, I don't know why you would
expect the conversion to distort or truncate the value.  For that data type
a value of "" is not valid.  You should only expect to reduce blanks if the
blanks are reduced in variable length fields.  Remember you're exporting
from a database, not Excel.

Your database isn't playing well with others, and you're blaming it on the
system.

-Jim

James P. Damato
Manager - Technical Administration
Dollar General Corporation
<mailto:jdamato@xxxxxxxxxxxxxxxxx>


-----Original Message-----
From: Ken.Slaugh@xxxxxxxxxx [mailto:Ken.Slaugh@xxxxxxxxxx]
Sent: Thursday, August 14, 2003 8:16 AM
To: Midrange Systems Technical Discussion
Subject: Re: CPYTOIMPF and CSV Format



Scott says:

Though, on the other hand, people typically do have spaces in their PF,
and people typically don't have spaces in their CSV's...  so, in the real
world, this isn't very practical.   Someone should really point that out
to IBM, maybe they'll add a new feature to the command...

I totally agree. I wonder why IBM even has to be told. To me, this is
another classic reason the 'iSeries doesn't play well with others'. Too bad
it could have been avoided in Rochester.

Unfortunately, I didn't create the original files containing the source
data and preprocessing the 10.5gb within all 13 files is not an option due
to time constraints. I'll be lucky to even get the CPYTOIMPF commands
executed in time for conversion. Wish me luck, please, I think I'm going to
need it. :)

Thanks for the post Scott confirming my suspicions.

Ken Slaugh (707) 795-1512 x118
Chouinard & Myhre, Inc.
CA/400 Certified Specialist
iSeries Network/MSE Administrator
http://www.cm-inc.com/




                      Scott Klement

                      <klemscot@xxxxxxxxxx        To:       Midrange
Systems
Technical Discussion
                      om>
<midrange-l@xxxxxxxxxxxx>

                      Sent by:                    cc:

                      midrange-l-bounces@x        Subject:  Re: CPYTOIMPF
and CSV Format
                      idrange.com





                      08/13/2003 10:27 PM

                      Please respond to

                      Midrange Systems

                      Technical Discussion











On Wed, 13 Aug 2003 Ken.Slaugh@xxxxxxxxxx wrote:
> Anyone know how to get the CPYTOIMPF command to output CSV? Here's the
> command I ran...
[SNIP]
> As you can see the record contains LOTS OF SPACES. Can they be eliminated
> somehow to results in:

Those spaces are actually in the PF you're copying the data from, it didn't
add them.   If you don't write the extra spaces to the PF in the first
place (by using variable-length fields, etc) then your CPYTOIMPF won't
have the extra spaces, either.

Though, on the other hand, people typically do have spaces in their PF,
and people typically don't have spaces in their CSV's...  so, in the real
world, this isn't very practical.   Someone should really point that out
to IBM, maybe they'll add a new feature to the command...