Joined: 22 Dec 2007 Posts: 126 Location: Bangalore
Hi,
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
Code:
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: 22 Dec 2007 Posts: 126 Location: Bangalore
Hi Nic,
I understand what you said. But it's third party who is creating file in windows system and before the Loading step in JCL, we are just running FTP GET command to copy the file into ZOS system.
So it's our responsibility to have a check at our end. This can be done I guess using pgm but I want to do it using JCL.
Joined: 16 Feb 2015 Posts: 13 Location: Madrid, Spain
Hello Subrata,
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.
Joined: 22 Dec 2007 Posts: 126 Location: Bangalore
Hi All,
Thanks a lot!! for your time and effort. Thanks again for pasting the code snippet. Let me try at my end with what you all have given.. for any further query I will come back..
Joined: 22 Dec 2007 Posts: 126 Location: Bangalore
Hi,
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..
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
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.
Joined: 22 Dec 2007 Posts: 126 Location: Bangalore
Hi Bill,
Thanks for the reply..
Quote:
Quite why you'd want to replace non-numeric values with space, I don't know. Space isn't numeric.
Below is excerpt of the Load JCL..
Code:
//SYSIN DD *
LOAD DATA INDDN USPR99 LOG NO NOCOPYPEND RESUME NO REPLACE
INTO TABLE XXXXX.<table>
( GKZ
POSITION(00001:00008) DECIMAL EXTERNAL(8,0)
, PLZ
POSITION(00009:00014) DECIMAL EXTERNAL(6,0)
, ORT
As DECIMAL EXTERNAL has been mentioned then I guess if I overwrite carriage return with space (say from 999X'0D' to 999<blank>) then it will load into DB2.. plz correct me if I am wrong
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
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.
Joined: 22 Dec 2007 Posts: 126 Location: Bangalore
Hi Bill
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..
