|
View previous topic :: View next topic
|
| Author |
Message |
aagarwal88
New User
Joined: 02 Oct 2018
Posts: 2
Location: India
|
|
|
|
I am trying to insert multiple rows in a single insert query using host variable array in my application program IV2XVAL to improve the performance of my insert program. I am getting SQLCODE -310 in the insert query whereas I have checked that we are not passing any non-numeric data to decimal field.
When I looked for a resolution on google, I got a reference to www-01.ibm.com/support/docview.wss?uid=swg1PM21877 . It looks like it might not be due to application program and something to do with DB2 itself. Can you please have a look and help me out if possible.
Insert Query
| Code: |
INSERT INTO VLXFS
(COMPANY_CODE ,
MASTER_ID ,
VALUATION_DATE ,
VALUATION_START_DATE ,
SEQUENCE_NUMBER ,
ENTRY_NUMBER ,
FUND_NUMBER ,
FUND_TYPE_INDICATOR ,
FUND_DOLLARS ,
FUND_UNIT_VALUE ,
FUND_VARIABLE_VALUE ,
FUND_GUAR_VALUE ,
FUND_EQTY_BASED_VALUE ,
FUND_PNT_TO_PNT_VALUE ,
FUND_ASIAN_VALUE ,
FUND_LOCK_IN_VALUE ,
FUND_LOOKBACK_VALUE ,
FUND_SURR_VALUE ,
FUND_FACTOR_VALUE ,
FUND_INTEREST ,
PRE_TEFRA_DOLLARS ,
PRE_TEFRA_VARIABLE_VALUE ,
PRE_TEFRA_GUAR_VALUE ,
PRE_TEFRA_INTEREST ,
POST_TEFRA_DOLLARS ,
POST_TEFRA_VARIABLE_VALUE ,
POST_TEFRA_GUAR_VALUE ,
POST_TEFRA_INTEREST ,
PV_FUND_CLASS)
VALUES ( :VLXFS-TABLE-ARRAY)
FOR :SUB7 ROWS
END-EXEC.
|
Where VLXFS-TABLE-ARRAY is defined as
| Code: |
01 VALUE11-FUND-ARRAY.
05 VLXFS-TABLE-ARRAY OCCURS 999.
10 COMPANY-CODE PIC X(3).
10 MASTER-ID PIC X(15).
10 VALUATION-DATE PIC X(8).
10 VALUATION-START-DATE PIC X(8).
10 SEQUENCE-NUMBER PIC S9(4)V COMP-3.
10 ENTRY-NUMBER PIC S9(8) COMP-3.
10 FUND-NUMBER PIC S9(8) COMP-3.
10 FUND-TYPE-INDICATOR PIC X.
10 FUND-DOLLARS PIC S9(9)V99 COMP-3.
10 FUND-UNIT-VALUE PIC S999V9(6) COMP-3.
10 FUND-VARIABLE-VALUE PIC S9(9)V99 COMP-3.
10 FUND-GUAR-VALUE PIC S9(9)V99 COMP-3.
10 FUND-EQTY-BASED-VALUE PIC S9(9)V99 COMP-3.
10 FUND-PNT-TO-PNT-VALUE PIC S9(9)V99 COMP-3.
10 FUND-ASIAN-VALUE PIC S9(9)V99 COMP-3.
10 FUND-LOCK-IN-VALUE PIC S9(9)V99 COMP-3.
10 FUND-LOOKBACK-VALUE PIC S9(9)V99 COMP-3.
10 FUND-SURR-VALUE PIC S9(9)V99 COMP-3.
10 FUND-FACTOR-VALUE PIC S9(9)V99 COMP-3.
10 FUND-INTEREST PIC S9(9)V99 COMP-3.
10 PRE-TEFRA-DOLLARS PIC S9(9)V99 COMP-3.
10 PRE-TEFRA-VARIABLE-VALUE PIC S9(9)V99 COMP-3.
10 PRE-TEFRA-GUAR-VALUE PIC S9(9)V99 COMP-3.
10 PRE-TEFRA-INTEREST PIC S9(9)V99 COMP-3.
10 POST-TEFRA-DOLLARS PIC S9(9)V99 COMP-3.
10 POST-TEFRA-VARIABLE-VALUE PIC S9(9)V99 COMP-3.
10 POST-TEFRA-GUAR-VALUE PIC S9(9)V99 COMP-3.
10 POST-TEFRA-INTEREST PIC S9(9)V99 COMP-3.
10 PV-FUND-CLASS PIC X.
|
I am getting SQLCODE -310 as below
SQLCODE -310
Decimal host variable or parameter 5 contains non-decimal data
I have checked all the parameters to insert query and there is no non-numeric data being moved to numeric field. Below is the snapshot from abend-aid with the values being passed to the failing insert query.
Field Type Values
----- ---- ------------
:VLXFS-TABLE-ARRAY.COMPANY-CODE CHAR(3) Below
16874B88 CHAR EQC
ZONE CDC
DIGIT 583
1..
:VLXFS-TABLE-ARRAY.MASTER-ID CHAR(15) Below
16874B8B CHAR 308601648
ZONE 444444FFFFFFFFF
DIGIT 000000308601648
1...5...10....*
:VLXFS-TABLE-ARRAY.VALUATION-DATE CHAR(8) Below
16874B9A CHAR 20180927
ZONE FFFFFFFF
DIGIT 20180927
1...5...
:VLXFS-TABLE-ARRAY.VALUATION-STAR CHAR(8) Below
A B E N D - A I D PAGE 776
T-DATE
16874BA2 CHAR 20180927
ZONE FFFFFFFF
DIGIT 20180927
1...5...
:VLXFS-TABLE-ARRAY.SEQUENCE-NUMBE DECIMAL(4,0) 1 Parameter 5 has valid data
R
:VLXFS-TABLE-ARRAY.ENTRY-NUMBER DECIMAL(8,0) 1
:VLXFS-TABLE-ARRAY.FUND-NUMBER DECIMAL(8,0) 1,676
:VLXFS-TABLE-ARRAY.FUND-TYPE-INDI CHAR(1) Below
CATOR
16874BB7 CHAR V
ZONE E
DIGIT 5
1
:VLXFS-TABLE-ARRAY.FUND-DOLLARS DECIMAL(11,2) 0.00
:VLXFS-TABLE-ARRAY.FUND-UNIT-VALU DECIMAL(9,6) 10.421485
E
:VLXFS-TABLE-ARRAY.FUND-VARIABLE- DECIMAL(11,2) 0.00
VALUE
:VLXFS-TABLE-ARRAY.FUND-GUAR-VALU DECIMAL(11,2) 0.00
E
:VLXFS-TABLE-ARRAY.FUND-EQTY-BASE DECIMAL(11,2) 0.00
D-VALUE
:VLXFS-TABLE-ARRAY.FUND-PNT-TO-PN DECIMAL(11,2) 0.00
T-VALUE
:VLXFS-TABLE-ARRAY.FUND-ASIAN-VAL DECIMAL(11,2) 0.00
UE
:VLXFS-TABLE-ARRAY.FUND-LOCK-IN-V DECIMAL(11,2) 0.00
ALUE
:VLXFS-TABLE-ARRAY.FUND-LOOKBACK- DECIMAL(11,2) 0.00
VALUE
:VLXFS-TABLE-ARRAY.FUND-SURR-VALU DECIMAL(11,2) 0.00
E
:VLXFS-TABLE-ARRAY.FUND-FACTOR-VA DECIMAL(11,2) 0.00
LUE
:VLXFS-TABLE-ARRAY.FUND-INTEREST DECIMAL(11,2) 0.00
:VLXFS-TABLE-ARRAY.PRE-TEFRA-DOLL DECIMAL(11,2) 0.00
ARS
A B E N D - A I D PAGE 777
:VLXFS-TABLE-ARRAY.PRE-TEFRA-VARI DECIMAL(11,2) 0.00
ABLE-VALUE
:VLXFS-TABLE-ARRAY.PRE-TEFRA-GUAR DECIMAL(11,2) 0.00
-VALUE
:VLXFS-TABLE-ARRAY.PRE-TEFRA-INTE DECIMAL(11,2) 0.00
REST
:VLXFS-TABLE-ARRAY.POST-TEFRA-DOL DECIMAL(11,2) 0.00
LARS
:VLXFS-TABLE-ARRAY.POST-TEFRA-VAR DECIMAL(11,2) 0.00
IABLE-VALUE
:VLXFS-TABLE-ARRAY.POST-TEFRA-GUA DECIMAL(11,2) 0.00
R-VALUE
:VLXFS-TABLE-ARRAY.POST-TEFRA-INT DECIMAL(11,2) 0.00
EREST
:VLXFS-TABLE-ARRAY.PV-FUND-CLASS CHAR(1) Below
16874C2F CHAR
ZONE 4
DIGIT 0
1 |
|
| Back to top |
|
 |
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
Learn to use the code tags - I'm getting fed up doing it for others
| Code: |
[code]
Your
stuff
goes
here
[/code] |
|
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1356
Location: Israel
|
|
|
|
Shouldn't it be:
| Code: |
01 VALUE11-FUND-ARRAY.
05 COMPANY-CODE PIC X(3) OCCURS 999.
05 MASTER-ID PIC X(15) OCCURS 999.
05 VALUATION-DATE PIC X(8) OCCURS 999.
05 VALUATION-START-DATE PIC X(8) OCCURS 999.
05 SEQUENCE-NUMBER PIC S9(4)V COMP-3 OCCURS 999.
|
and so on... ? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
-310
What you posted link is from 2010-2011 and recent versions have all of them fixed for sure. So correct your SELECT query and make sure it matches up the host declarations and it has nothing to do with DB2 or system errors but the data itself. Did you check Host variable number or parameter number which is bad one in the SQLCA?
Marso, I think (not sure) when we use individual variables listed in INSERT or SELECT then occurs goes individually otherwise Group Occurs should work else TS would get different Sqlcode even before -310. |
|
| Back to top |
|
|
aagarwal88
New User
Joined: 02 Oct 2018
Posts: 2
Location: India
|
|
|
|
Thank you so much Marso.. It worked.. But performance didn't improved as expected for multi row insert v/s single row insert.
| Code: |
01 VALUE11-FUND-ARRAY.
05 COMPANY-CODE PIC X(3) OCCURS 999.
05 MASTER-ID PIC X(15) OCCURS 999.
05 VALUATION-DATE PIC X(8) OCCURS 999.
05 VALUATION-START-DATE PIC X(8) OCCURS 999.
05 SEQUENCE-NUMBER PIC S9(4)V COMP-3 OCCURS 999. |
and so on...
Coded for you - do it yourself next time |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| Performance may not improve as it is impacted by outside influences such as the number of other jobs accessing DB2 and other workload on the machine. You must remember that the same amount of DB activity takes place - you are simply making fewer calls to DB2. |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1356
Location: Israel
|
|
|
|
| If you have a really large number of rows to insert, you may increase performance by writing them to a temporary file and then doing a LOAD RESUME. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
