Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2011
    Posts
    16

    Unanswered: Insert query giving error SQL0901N (Reason "Bad decimal literal".)

    A insert query giving error in db2udbV9.7 while it is running in v9.1

    query: db2 "insert into FRZE_RQST_INSTR ( FRI_CLNT_ID , FRI_DP_ID , FRI_EXEC_ID , FRI_TXN_ID , FRI_FRZE_TYP , FRI_CRNT_STAT , FRI_FRZE_STAT , FRI_ISIN , FRI_ACA_TYP_FLG , FRI_CRTE_DT , FRI_CRTE_BY, FRI_EXEC_DT) values ( 10000015,'IN399992',100007253,100005951,'3','2','1 ','INE064K08012','N', CURRENT TIMESTAMP ,'PMC_SC1','2012-10-25')"

    error : DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0901N The SQL statement failed because of a non-severe system error.
    Subsequent SQL statements can be processed. (Reason "Bad decimal literal".)
    SQLSTATE=58004



    The table is same in both environment

    bash-3.2$ db2 "describe table instspd1.FRZE_RQST_INSTR"

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    FRI_CLNT_ID SYSIBM DECIMAL 8 0 No
    FRI_DP_ID SYSIBM CHARACTER 8 0 No
    FRI_EXEC_ID SYSIBM DECIMAL 15 0 No
    FRI_TXN_ID SYSIBM DECIMAL 15 0 No
    FRI_FRZE_TYP SYSIBM CHARACTER 2 0 No
    FRI_CRNT_STAT SYSIBM CHARACTER 2 0 No
    FRI_FRZE_STAT SYSIBM CHARACTER 2 0 No
    FRI_ISIN SYSIBM CHARACTER 12 0 No
    FRI_QNTY_RQST SYSIBM DECIMAL 18 3 No
    FRI_QNTY_FRZED SYSIBM DECIMAL 18 3 No
    FRI_INTL_FRZE_QNTY SYSIBM DECIMAL 18 3 No
    FRI_DM_ORDR_NO SYSIBM DECIMAL 10 0 No
    FRI_UPLD_ID SYSIBM DECIMAL 7 0 Yes
    FRI_DWNLD_ID SYSIBM DECIMAL 7 0 Yes
    FRI_RJCTN_RSN_1 SYSIBM DECIMAL 5 0 Yes
    FRI_RJCTN_RSN_2 SYSIBM DECIMAL 5 0 Yes
    FRI_RJCTN_RSN_3 SYSIBM DECIMAL 5 0 Yes
    FRI_RJCTN_RSN_4 SYSIBM DECIMAL 5 0 Yes
    FRI_ACA_TYP_FLG SYSIBM CHARACTER 2 0 No
    FRI_CRTE_DT SYSIBM TIMESTAMP 10 6 Yes
    FRI_CRTE_BY SYSIBM VARCHAR 8 0 Yes
    FRI_UPDT_BY SYSIBM VARCHAR 8 0 Yes
    FRI_UPDT_DT SYSIBM TIMESTAMP 10 6 Yes
    FRI_EXEC_DT SYSIBM DATE 4 0 No
    FRI_BP_INSTR_ID SYSIBM DECIMAL 14 0 Yes
    FRI_INIT_ORDR_NO SYSIBM VARCHAR 10 0 Yes

    26 record(s) selected.




    Ashish

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    compare:

    #> set | grep LC_

    on both systems and see if there is a difference
    --
    Lennart

  3. #3
    Join Date
    Sep 2011
    Posts
    16
    Lennart

    There is no difference in both system


    Ashish

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Is there anything in db2diag.log?

    Andy

  5. #5
    Join Date
    Sep 2011
    Posts
    16
    I didn't understand what is in diaglog.....
    Here it is

    Information in this record is only valid at the time when this file was
    created (see this record's time stamp)

    2012-10-25-14.57.04.418968+330 E1602A769 LEVEL: Error
    PID : 47513820 TID : 40912 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : SPEEDEP
    APPHDL : 0-19667 APPID: *N0.db2inst1.121025092603
    AUTHID : DB2INST1
    EDUID : 40912 EDUNAME: db2agent (SPEEDEP) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:10
    MESSAGE : ADM14005E The following error occurred: "AppErr". First Occurrence
    Data Capture (FODC) has been invoked in the following mode:
    "Automatic". Diagnostic information has been recorded in the
    directory named
    "/home1/db2inst1/sqllib/db2dump/FODC_AppErr_2012-10-25-14.57.04.41807
    2_47513820_40912_000/".

    2012-10-25-14.57.04.422474+330 E2372A549 LEVEL: Severe
    PID : 47513820 TID : 40912 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : SPEEDEP
    APPHDL : 0-19667 APPID: *N0.db2inst1.121025092603
    AUTHID : DB2INST1
    EDUID : 40912 EDUNAME: db2agent (SPEEDEP) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:200
    MESSAGE : ADM0001C A severe error has occurred. Examine the administration
    notification log and contact IBM Support if necessary.

    2012-10-25-14.57.04.424556+330 I2922A450 LEVEL: Severe
    PID : 47513820 TID : 40912 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : SPEEDEP
    APPHDL : 0-19667 APPID: *N0.db2inst1.121025092603
    AUTHID : DB2INST1
    EDUID : 40912 EDUNAME: db2agent (SPEEDEP) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:250
    RETCODE : ZRC=0x00000000=0=PSM_OK "Unknown"

    2012-10-25-14.57.04.425784+330 I3373A623 LEVEL: Severe
    PID : 47513820 TID : 40912 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : SPEEDEP
    APPHDL : 0-19667 APPID: *N0.db2inst1.121025092603
    AUTHID : DB2INST1
    EDUID : 40912 EDUNAME: db2agent (SPEEDEP) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:275
    MESSAGE : MASTER TIME INFO
    DATA #1 : Hexdump, 32 bytes
    0x07800002FF505CF8 : 0000 0000 504D 8B46 00C6 0000 0003 06BA ....PM.F........
    0x07800002FF505D08 : 0019 6445 EFF9 81B7 0000 0000 003A 2B14 ..dE.........:+.

    2012-10-25-14.57.04.426751+330 I3997A248 LEVEL: Severe
    PID:47513820 TID:40912 NODE:000 Title: MASTER TIME INFO
    Dump File: /home1/db2inst1/sqllib/db2dump/FODC_AppErr_2012-10-25-14.57.04.418072_47513820_40912_000/47513820.40912.000.dump.bin

    2012-10-25-14.57.04.428966+330 I4246A844 LEVEL: Severe
    PID : 47513820 TID : 40912 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : SPEEDEP
    APPHDL : 0-19667 APPID: *N0.db2inst1.121025092603
    AUTHID : DB2INST1
    EDUID : 40912 EDUNAME: db2agent (SPEEDEP) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:300
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 19
    sqlerrmc: Bad decimal literal
    sqlerrp : SQLNQ8B6
    sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0xFFFFFFF6 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate:

    2012-10-25-14.57.04.429964+330 I5091A237 LEVEL: Severe
    PID:47513820 TID:40912 NODE:000 Title: SQLCA
    Dump File: /home1/db2inst1/sqllib/db2dump/FODC_AppErr_2012-10-25-14.57.04.418072_47513820_40912_000/47513820.40912.000.dump.bin

    2012-10-25-14.57.04.431570+330 I5329A524 LEVEL: Severe
    PID : 47513820 TID : 40912 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : SPEEDEP
    APPHDL : 0-19667 APPID: *N0.db2inst1.121025092603
    AUTHID : DB2INST1
    EDUID : 40912 EDUNAME: db2agent (SPEEDEP) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:400
    MESSAGE : CREATOR
    DATA #1 : Hexdump, 8 bytes
    0x07800003522C3618 : 4E55 4C4C 4944 2020 NULLID

    2012-10-25-14.57.04.432541+330 I5854A524 LEVEL: Severe
    PID : 47513820 TID : 40912 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : SPEEDEP
    APPHDL : 0-19667 APPID: *N0.db2inst1.121025092603
    AUTHID : DB2INST1
    EDUID : 40912 EDUNAME: db2agent (SPEEDEP) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:450
    MESSAGE : PACKAGE
    DATA #1 : Hexdump, 8 bytes
    0x07800003522C3610 : 5351 4C43 3248 3233 SQLC2H23


    2012-10-25-14.57.04.451024+330 I9086A2148 LEVEL: Severe
    PID : 47513820 TID : 40912 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : SPEEDEP
    APPHDL : 0-19667 APPID: *N0.db2inst1.121025092603
    AUTHID : DB2INST1
    EDUID : 40912 EDUNAME: db2agent (SPEEDEP) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_sibling, probe:200
    MESSAGE : section stmt
    DATA #1 : Hexdump, 322 bytes
    0x0780000301A197E0 : 696E 7365 7274 2069 6E74 6F20 4652 5A45 insert into FRZE
    0x0780000301A197F0 : 5F52 5153 545F 494E 5354 5220 2820 4652 _RQST_INSTR ( FR
    0x0780000301A19800 : 495F 434C 4E54 5F49 4420 2C20 4652 495F I_CLNT_ID , FRI_
    0x0780000301A19810 : 4450 5F49 4420 2C20 4652 495F 4558 4543 DP_ID , FRI_EXEC
    0x0780000301A19820 : 5F49 4420 2C20 4652 495F 5458 4E5F 4944 _ID , FRI_TXN_ID
    0x0780000301A19830 : 202C 2046 5249 5F46 525A 455F 5459 5020 , FRI_FRZE_TYP
    0x0780000301A19840 : 2C20 4652 495F 4352 4E54 5F53 5441 5420 , FRI_CRNT_STAT
    0x0780000301A19850 : 2C20 4652 495F 4652 5A45 5F53 5441 5420 , FRI_FRZE_STAT
    0x0780000301A19860 : 2C20 4652 495F 4953 494E 202C 2046 5249 , FRI_ISIN , FRI
    0x0780000301A19870 : 5F41 4341 5F54 5950 5F46 4C47 202C 2046 _ACA_TYP_FLG , F
    0x0780000301A19880 : 5249 5F43 5254 455F 4454 202C 2046 5249 RI_CRTE_DT , FRI
    0x0780000301A19890 : 5F43 5254 455F 4259 2C20 4652 495F 4558 _CRTE_BY, FRI_EX
    0x0780000301A198A0 : 4543 5F44 5429 2076 616C 7565 7320 2820 EC_DT) values (
    0x0780000301A198B0 : 3130 3030 3030 3135 2C27 494E 3339 3939 10000015,'IN3999
    0x0780000301A198C0 : 3932 272C 3130 3030 3037 3235 332C 3130 92',100007253,10
    0x0780000301A198D0 : 3030 3035 3935 312C 2733 272C 2732 272C 0005951,'3','2',
    0x0780000301A198E0 : 2731 272C 2749 4E45 3036 344B 3038 3031 '1','INE064K0801
    0x0780000301A198F0 : 3227 2C27 4E27 2C20 4355 5252 454E 5420 2','N', CURRENT
    0x0780000301A19900 : 5449 4D45 5354 414D 5020 2C27 504D 435F TIMESTAMP ,'PMC_
    0x0780000301A19910 : 5343 3127 2C27 3230 3132 2D31 302D 3235 SC1','2012-10-25
    0x0780000301A19920 : 2729 ')

    2012-10-25-14.57.04.720689+330 E53883A2284 LEVEL: Info (Origin)
    PID : 47513820 TID : 40912 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : SPEEDEP
    APPHDL : 0-19667 APPID: *N0.db2inst1.121025092603
    AUTHID : DB2INST1
    EDUID : 40912 EDUNAME: db2agent (SPEEDEP) 0
    FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
    MESSAGE : ZRC=0x8016000E=-2146041842=SQLRXITD "invalid output type"
    DIA8014C Invalid data type in host variable "".
    DATA #1 : String, 62 bytes
    An unexpected error was detected during statement compilation.
    DATA #2 : Boolean, 1 bytes
    true
    DATA #3 : Boolean, 1 bytes
    false
    DATA #4 : Boolean, 1 bytes
    false
    DATA #5 : Boolean, 1 bytes
    false
    DATA #6 : Hex integer, 4 bytes
    0x00000000
    DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 19
    sqlerrmc: Bad decimal literal
    sqlerrp : SQLNQ8B6
    sqlerrd : (1) 0x801A006D (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0xFFFFFFF6 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate:
    DATA #8 : Hex integer, 4 bytes
    0x00000040
    DATA #9 : String, 977 bytes
    Compiler error stack for rc = -2146041842:
    sqlnn_cmpl[390]
    sqlno_exe[500]
    sqlno_prep_phase[300]
    sqlno_scan_utils_qgm[200]
    sqlno_each_qur[400]
    sqlno_top_qtb[100]
    sqlno_each_opr[800]
    sqlno_assign_walk_quns_and[100]
    sqlno_each_opr[850]
    sqlno_assign_walk_quns_and[100]
    sqlno_each_opr[850]
    sqlno_assign_walk_quns_and[100]
    sqlno_each_opr[800]
    sqlno_assign_walk_quns_and[100]
    sqlno_each_opr[800]
    sqlno_assign_walk_quns_and[100]
    sqlno_each_opr[800]
    sqlno_assign_walk_quns_and[100]
    sqlno_each_opr[800]
    sqlno_assign_walk_quns_and[100]
    sqlno_each_opr[800]
    sqlno_assign_walk_quns_and[100]
    sqlno_each_opr[400]
    sqlno_assign_prd[100]
    sqlno_assign_optprd[400]
    sqlno_analyze_pred_expr[20]
    sqlno_analyze_pred_expr[90]
    sqlno_pred_init_plan_gener[30]
    sqlnx_exp_gsql_opt_pid[10]
    sqlnx_exp_gsql_proc_pid[670]
    sqlnx_exp_gsql_std[65]
    sqlnx_exp_gsql_std[120]
    sqlnx_exp_gsql_proc_sel_li[30]
    sqlnx_exp_gsql_proc_exp[100]
    sqlnx_exp_gsql_proc_pid[53]
    sqlnx_exp_get_colname[100]
    sqlnq_qtb::get_col_name[125]


    Ashish

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    FRI_EXEC_ID , FRI_TXN_ID ,
    100007253,100005951
    It is just an idea ..
    would it be possible that decimal point in your case is ,
    and that 100007253,100005951 is considered as 1 input value and col is 15,0
    remark that has nothing todo with the problem : why do people define datatype decimal x,y with y =0 and store id in this col that will never have decimals, why not use bigint or datatype that reflects the contents..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Sep 2011
    Posts
    16
    If problem with the dataype (decimal (15,0)) then why it is working in 9.1


    Ashish

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •