I need small help/suggestion on my DFSORT query. Detail of the issue given below..
Our job failed while loading data into Prod DB2 table because of Junk value.Here Junk means Non-Numeric char in Numeric field (Data type mentioned in table as DECIMAL). To prevent from that happening I am thinking to use sort before Loading step.
Means SORT will check specific columns whether it is Numeric or not. Once it gets Non Numeric value in any of those specific columns that that specific Non Numeric value/s will be replaced with space/spaces.
Can anyone please guide me on should I start. I was thinking of using
INREC IFTHEN=(WHEN=(Start Pos,End Pos,FS,NE,NUM),
but once it will match that Non Numeric found then shall I use OVERLAY or BUILD? and how to replace those non numeric values only with space?
Joined: 16 Feb 2015 Posts: 13 Location: Madrid, Spain
How many fields do you need to fix?
I have a first apprach to solve this problem but it is with many steps depending on the fields to be fixed. Also too you need to have identified the non numeric values.
In my example I am taking into account all the letters as non numeric values and 2 fields to be fixed.
I apologize for my delay in reply. Thanks to all for their valuable suggestion. I have gone through all the stmts given above... There is one small twist happen in my earlier given scenario. We were actually kept an eye on this issue.. for few days there was no problem and suddenly for yesterday we saw the job went down and issue was little different this time our oncall support person found that there were X'0D' Hex char which is "Carriage Return" and this happened at the end of line (not for all the lines but few out of many). After discussing with few others I got to know this may be related to Carriage Return and Line Feed which is normal for any file gets created under Windows env.
We are still trying to find out why suddenly caused this issue and for temp solution I have written below code (excerpt of the main code)
Anything apart from numeric value it will overwrite with space.
I thought to adopt the code example given by rinsio above. But in that case I had to mentioned all possible combinations (apart from normal A to B I had to include all other numerous junk values) which was not possible so I thought to opt the above way.. Not sure if there would be any other better way to handle through SORT..
If you want to replace non-numeric characters with space, then that code should do.
Hopefully the Windows system is not sending you signed data.
Quite why you'd want to replace non-numeric values with space, I don't know. Space isn't numeric. Can't you drop the lines or bounce the file? There seem to be too many problems with the data. You, quickly, need to know of potential impacts if the entire data (including the "good" stuff) is wrong, and then down the scale from that.
Well, that may initially work, or it may not. I suspect if you replace the low-order byte of an external-decimal you'll get a problem, somewhere, but I'm not going to guess where, because I wouldn't do that.
If it does not fail, perhaps likely for spaces other than the low-order bytes, whether it does what you think depends on if or how quickly the space becomes a zero.
If you are going to replace non-numeric by something, I'd go for zero. But I'd be really wary of just stuffing "incorrect" things into a data-base.
If the data is supposed to be numeric and meaningful, then clobbering any dodgy-looking data is going to give you "good", but wrong, data. I hope you can find it afterwards.
If it is not meaningful, just load it as is, as character data, so that it can be sorted out later.
We can't know enough about your system to do more than generalise, but I'd really not do what you're doing without a rock-solid audit-trail and sign-offs from your boss and the users.
I actually tried to overwrite that CR value to Space/Spaces and then tried to load the same into DB2 table.. and it is happening.
BUT I completely agree with you on the authenticity and integrity of the data that we are overwriting with spaces. I also thought the same what u had mentioned above. Yes I am checking with my client on the same... have explained in details through mail and waiting for reply.. lets see what they has to say..