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

Facing issue while executing multi row insert query
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
aagarwal88

New User


Joined: 02 Oct 2018
Posts: 2
Location: India
PostPosted: Tue Oct 02, 2018 8:11 am
 Reply with quote
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
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
PostPosted: Tue Oct 02, 2018 11:30 am
 Reply with quote
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
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1353
Location: Israel
PostPosted: Tue Oct 02, 2018 5:42 pm
 Reply with quote
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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Tue Oct 02, 2018 7:23 pm
 Reply with quote
-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
View user's profile Send private message
aagarwal88

New User


Joined: 02 Oct 2018
Posts: 2
Location: India
PostPosted: Fri Oct 05, 2018 11:56 pm
 Reply with quote
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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
PostPosted: Sat Oct 06, 2018 11:37 am
 Reply with quote
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
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1353
Location: Israel
PostPosted: Mon Oct 08, 2018 6:24 pm
 Reply with quote
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
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 SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us