|
View previous topic :: View next topic
|
| Author |
Message |
Behrooz
New User
Joined: 12 Jun 2021
Posts: 2
Location: New Zealand
|
|
|
|
CHC0102E SQL error:
DSNT408I SQLCODE = -310, ERROR: DECIMAL HOST VARIABLE OR PARAMETER 14 CONTAINS
NON-DECIMAL DATA
DSNT418I SQLSTATE = 22023 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRIHB SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -235 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF15' X'00000000' X'00000000' X'FFFFFFFF' X'00000000'
X'00000000' SQL DIAGNOSTIC INFORMATION, Diagnostic information (dtcExcSqlStmt;6,259) |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
You need to correct the value of host variable or parameter 14, and make it a really decimal.
This is the only possible conclusion from the information you have provided. If you need more, please, give more details on your issue. |
|
| Back to top |
|
|
Behrooz
New User
Joined: 12 Jun 2021
Posts: 2
Location: New Zealand
|
|
|
|
I read one segment from IMS database and Insert some filed to DB2, but in IMS we have garbage in filed (s9(15) comp-3) and I can't fix data from source.
Is it possible use default value in DB2 during insert when data was corrupt ? |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
| Behrooz wrote: |
| Is it possible use default value in DB2 during insert when data was corrupt ? |
DB2 is not, and not supposed to be a sort of artificial intelligence, to wisely fix bad data explicitly supplied by a developer.
The only default value can be defined for a column not presented explicitly when a new DB record is created, but no default value can be defined for a badly prepared column value. The task of preparing the correct values for all columns is what the developer must do, with no exception. Otherwise no developers would be needed in this world.    |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| Behrooz wrote: |
I read one segment from IMS database and Insert some filed to DB2, but in IMS we have garbage in filed (s9(15) comp-3) and I can't fix data from source.
Is it possible use default value in DB2 during insert when data was corrupt ? |
Possible by following a standard coding practice of validating before INSERT to default to 0 if it’s bad use DEC(:IMS field,15,0) function and if sqlcode <> 0 then default to 0 else actual value. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
| Rohit Umarjikar wrote: |
| Behrooz wrote: |
I read one segment from IMS database and Insert some filed to DB2, but in IMS we have garbage in filed (s9(15) comp-3) and I can't fix data from source.
Is it possible use default value in DB2 during insert when data was corrupt ? |
Possible by following a standard coding practice of validating before INSERT to default to 0 if it’s bad use DEC(:IMS field,15,0) function and if sqlcode <> 0 then default to 0 else actual value. |
The problem is: there may be multiple fields causing various types of SQL errors; in general case (except a very specific particular situations) the code to properly handle all possible issues would be a sort of an artificial intelligence, and the required efforts to do this would not worth the final result of this useless activity.
The correct approach is: always to verify the validity of all field values before they are submitted to DB2, instead of analyzing the reason of SQL failure due to badly prepared data. |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| IMS data base segments often have multiple layouts that the application has to be aware of. So it may not be bad data at all, but you could be basing your process on the wrong layout. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| sergeyken wrote: |
| Rohit Umarjikar wrote: |
| Behrooz wrote: |
I read one segment from IMS database and Insert some filed to DB2, but in IMS we have garbage in filed (s9(15) comp-3) and I can't fix data from source.
Is it possible use default value in DB2 during insert when data was corrupt ? |
Possible by following a standard coding practice of validating before INSERT to default to 0 if it’s bad use DEC(:IMS field,15,0) function and if sqlcode <> 0 then default to 0 else actual value. |
The problem is: there may be multiple fields causing various types of SQL errors; in general case (except a very specific particular situations) the code to properly handle all possible issues would be a sort of an artificial intelligence, and the required efforts to do this would not worth the final result of this useless activity.
The correct approach is: always to verify the validity of all field values before they are submitted to DB2, instead of analyzing the reason of SQL failure due to badly prepared data. |
of course, that’s what I said for this field and should happen for all the fields which are non char.
Second , at first place it should have been validated before saving into IMS segment or it could be a wrong layout as said above. |
|
| Back to top |
|
|
|
|
