How to remove Carriage return character from PF

[Scott M]

Re: How to remove Carriage return character from PF

You will want to replace the x'0D' you have and the x'25' also. That is a very old version so that is probably why REPLACE isn't valid. Don't know why TRANSLATE isn't working, could be the release or something else. Did you look at your job log for any messages? Running under debug (STRDBG) might put more messages in the job log.

[pintusrm2010]

Re: How to remove Carriage return character from PF

How to run STRQMQRY under debug?

You will want to replace the x'0D' you have and the x'25' also. That is a very old version so that is probably why REPLACE isn't valid. Don't know why TRANSLATE isn't working, could be the release or something else. Did you look at your job log for any messages? Running under debug (STRDBG) might put more messages in the job log.

[Scott M]

Re: How to remove Carriage return character from PF

Just run STRDBG prior to running STRQMQRY. Any SQL run after the STRDBG command may put additional messages in the job log. If you can't find anything there then don't know what to tell you. It appears to be correct what you are doing but the old release may be causing the issue.

[pintusrm2010]

Re: How to remove Carriage return character from PF

Tried same. TRANSLATE query not at all throwing any error but again its not changing anything in file.Tried for some other files also with valid data and same result. Also as per you i run STRQMQRY after STRDBG and it run sucessfully without any error. After tat i typed DSPJOBLOG and checked and no information there . My bad luck

Just run STRDBG prior to running STRQMQRY. Any SQL run after the STRDBG command may put additional messages in the job log. If you can't find anything there then don't know what to tell you. It appears to be correct what you are doing but the old release may be causing the issue.

[pintusrm2010]

Re: How to remove Carriage return character from PF

Hi,

Just checked the log for TRANSLATE part and it says that rows updated. But in real rows not got updated. DOnt know where i am wrong

Just run STRDBG prior to running STRQMQRY. Any SQL run after the STRDBG command may put additional messages in the job log. If you can't find anything there then don't know what to tell you. It appears to be correct what you are doing but the old release may be causing the issue.

[tomliotta]

Re: How to remove Carriage return character from PF

Not much useful help is available for V4R3. That's a pretty old release. However, the joblog did show the rows were update. If no updated rows were seen after the process finished, the first thing I would suspect is that the updates were not committed (i.e., they were rolled back.)

Also, the previous suggestion to run the initial tests against a copy of the production data is a very good one. Make sure it works properly against a copy of a bunch of records first.

See if the command works like this:

UPDATE mylib/myfile SET Comment = TRANSLATE( Comment , x'40', x'0D' )
WHERE LOCATE( x'0D', Comment ) > 0  [B]WITH NC[/B]

Is the file that you run against journaled?

[tomliotta]

Re: How to remove Carriage return character from PF

"UPDATE myTable SET myColumn = REPLACE( myColumn , x'0D', ' ' )" . This is the exact wording and i am getting below error which clearly states REPLACE not valid.

If that is the exact wording, then you actually have a file named "myTable" that has a column named "myColumn".

When a problem happens against a program instruction, we need to see the exact statement rather than one that has been modified. We can't verify if any modification might have changed how the SQL parser might look at the statement.

Have you checked the SQL Reference for V4R3 to verify that REPLACE() exists? I'm pretty sure TRANSLATE() existed in V4R3, but it's possible that it had minor changes to its syntax. A V4R3 SQL Reference should show proper usage. The V4R3 Reference library is still available. At least some of the manuals can be downloaded as .PDFs. You should grab copies before IBM decides to remove them.

[pintusrm2010]

Re: How to remove Carriage return character from PF

Hi Tom,

It worked now. I think i was doing somthing wrong before. I used below sql query and all such characters got replaced by blanks.

UPDATE PINTU/PAYCAL  SET PBD52=                                        
TRANSLATE(PBD52, X'40' ,                                               
X'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F2021222324252+
62728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F') 

I am really thankful to you for your help in solving this query.


I have one more query. TRANSFORM work only if the field is defined as character. If field is defined as numeric then TRANSFORM is showing invalid. In such case how to remove such characters from numeric fields. I thought of removing ++++++++++ type characters from PF which i discussed with you on another Thread.

If that is the exact wording, then you actually have a file named "myTable" that has a column named "myColumn".

When a problem happens against a program instruction, we need to see the exact statement rather than one that has been modified. We can't verify if any modification might have changed how the SQL parser might look at the statement.

Have you checked the SQL Reference for V4R3 to verify that REPLACE() exists? I'm pretty sure TRANSLATE() existed in V4R3, but it's possible that it had minor changes to its syntax. A V4R3 SQL Reference should show proper usage. The V4R3 Reference library is still available. At least some of the manuals can be downloaded as .PDFs. You should grab copies before IBM decides to remove them.

[Scott Klement]

Re: How to remove Carriage return character from PF

Are you running this under commitment control? Maybe the updates are happening, but have not been committed? Or are getting rolled back somehow?

[tomliotta]

Re: How to remove Carriage return character from PF

The only good way that I can think of is to write a program to examine numeric areas of a record format and change bytes as needed. Use a program-described record format to get clear access to those areas that need to be fixed.

Or it might be possible to create a flat file matching the record length of the problem file. Use CPYF with FMTOPT(*NOCHK) to copy records into the flat file. You might then use SQL to repair one or more problem areas. After any fixes, CPYF FMTOPT(*NOCHK) could put the record images back into the original file. It'd be necessary to examine the record images to guess what SQL functions would be used. I don't know if anyone can say with certainty everything you might need to do; direct access to the file is probably needed in order to see all possibilities.

The true fix is to go back to the process that put the records into the file and fix it. It's clearly not doing it correctly. Perhaps the first thing that should be done is generate SQL to recreate the file. Delete the existing file description and recreate it as a SQL table. That will avoid questionable data getting into numeric fields in the future. Character fields would probably need still need to be handled correctly when putting data in them.

[pintusrm2010]

Re: How to remove Carriage return character from PF

The only good way that I can think of is to write a program to examine numeric areas of a record format and change bytes as needed. Use a program-described record format to get clear access to those areas that need to be fixed.

Or it might be possible to create a flat file matching the record length of the problem file. Use CPYF with FMTOPT(*NOCHK) to copy records into the flat file. You might then use SQL to repair one or more problem areas. After any fixes, CPYF FMTOPT(*NOCHK) could put the record images back into the original file. It'd be necessary to examine the record images to guess what SQL functions would be used. I don't know if anyone can say with certainty everything you might need to do; direct access to the file is probably needed in order to see all possibilities.

The true fix is to go back to the process that put the records into the file and fix it. It's clearly not doing it correctly. Perhaps the first thing that should be done is generate SQL to recreate the file. Delete the existing file description and recreate it as a SQL table. That will avoid questionable data getting into numeric fields in the future. Character fields would probably need still need to be handled correctly when putting data in them.

Hi Tom,

This issue is resolved.

I tried with CPYF FMTOPT(*NULLFLAGS) and field containing ++++ characters got downloaded as zero. Others data's not got affected.

[tomliotta]

Re: How to remove Carriage return character from PF

You possibly got lucky with CPYF *NULLFLAGS. But if it gave a result that fit your need, it's as good as any other method.