IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DECIMAL HOST VARIABLE OR PARAMETER 14 CONTAINS


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Behrooz

New User


Joined: 12 Jun 2021
Posts: 2
Location: New Zealand

PostPosted: Sat Jun 12, 2021 4:01 pm
Reply with quote

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
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1201

PostPosted: Sat Jun 12, 2021 4:19 pm
Reply with quote

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
View user's profile Send private message
Behrooz

New User


Joined: 12 Jun 2021
Posts: 2
Location: New Zealand

PostPosted: Sat Jun 12, 2021 4:32 pm
Reply with quote

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
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1201

PostPosted: Sun Jun 13, 2021 2:01 am
Reply with quote

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. icon_axe.gif icon_fU.gif icon_butt.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2789
Location: NYC,USA

PostPosted: Sun Jun 13, 2021 6:58 am
Reply with quote

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
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1201

PostPosted: Mon Jun 14, 2021 1:14 am
Reply with quote

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
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 708
Location: Whitby, ON, Canada

PostPosted: Tue Jun 15, 2021 3:35 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2789
Location: NYC,USA

PostPosted: Sun Jun 20, 2021 10:49 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Make cobol variable value a variable COBOL Programming 3
No new posts SCHENV parameter JCL & VSAM 2
No new posts Merge a variable number of records fr... DFSORT/ICETOOL 5
No new posts Trying to reference a javascript glob... CLIST & REXX 7
No new posts passing stem variable with VPUT/VGET ... CLIST & REXX 6
Search our Forums:

Back to Top