Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Facing issue while executing multi row insert query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Facing issue while executing multi row insert query
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 https://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: 8723
Location: Welsh Wales

PostPosted: Tue Oct 02, 2018 11:30 am    Post subject:
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: 1300
Location: Israel

PostPosted: Tue Oct 02, 2018 5:42 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2031
Location: NY,USA

PostPosted: Tue Oct 02, 2018 7:23 pm    Post subject:
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    Post subject: Re: Facing issue while executing multi row insert query
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: 2100
Location: UK

PostPosted: Sat Oct 06, 2018 11:37 am    Post subject: Reply to: Facing issue while executing multi row insert query
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: 1300
Location: Israel

PostPosted: Mon Oct 08, 2018 6:24 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DUMP A Multi volume datasets and rest... lind sh JCL & VSAM 6 Mon Oct 01, 2018 12:02 pm
No new posts DB2 SQL Query to fetch all instances ... MallikarjunSM DB2 2 Thu Sep 27, 2018 6:46 pm
No new posts Generate SQL query dynamically using ... vnktrrd DB2 7 Tue Aug 28, 2018 8:11 pm
No new posts S0C4-X'11' issue - page translation e... ashek15 JCL & VSAM 8 Wed Aug 15, 2018 4:23 am
No new posts Issue While Recovering a Tablespace satish.ms10 DB2 4 Tue Aug 07, 2018 2:54 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us