DB2 UDB for iSeries: Using CPYTOIMPF to trim blanks

435 pts.
Tags:
AS/400
Database
IBM DB2
Oracle
Mark is looking for an alternative to trimming blanks from the end of a record. He writes: "I would like to use the CPYTOIMPF command only if I can Trim Blanks from end of Record. I found out how to do this in FTP, but would really like to stay on this path and avoid FTP using Override Inputs. I have a 60 byte alpha field and I need the last record to be EOF <CRLF> not EOF BLANK Blank ect..<CRLF>" Do you have any suggestions? Michelle Davidson Editor Search400.com

Answer Wiki

Thanks. We'll let you know when a new response is added.

We recently got this suggestion from a reader:

“You could try explicitly specifying: RCDDLM(*CRLF)
in your CpyToImpF command. It works for me we’re running OS/400 V5R2.”

– Michelle

=====================================================

If the field being transferred is a CHAR field, then trailing spaces is correct and they should not be truncated. If the field is VARCHAR, then the trailing spaces shouldn’t exist to be truncated.

If the field is CHAR and you want to <i>convert</i> it to VARCHAR, then you need to tell the system that that’s what you want to do. One good way of doing that is with SQL DDL, since that’s the language designed to communicate data formatting info to the database.

Create a VIEW that does the conversion and use the VIEW for input to CPYTOIMPF:
<pre>
CREATE VIEW mylib/myview
(VCol1) AS
SELECT varchar(trim(CharCol1)) as VCol1
FROM mylib/mytable
</pre>
That view converts column CharCol1 from the base table into VCol1 in the view. You can use iSeries Navigator or RUNSQLSTM to execute SQL if you don’t have interactive SQL available.

Tom

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following