ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How to Pass Array as Input Parameter to the DB2 stored Procedure.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to Pass Array as Input Parameter to the DB2 stored Procedure.

    Hi,
    I am writing a DB2 SQL Stored Procedure.
    This SP will recieve an Input Parameter in the form of a Big String from the Front End Java Application. The String would contain a set of Numbers (comma delimited Strings).
    In the Stored Procedure I need to parse that data and do the required calculations by fetching data from some other files based on the Input Parameter.
    I am unable to find a way as to how the parse the data in the Stored Procedure.
    I had a look at the existing Stored Procedure Code.
    Here it is:

    set lineSelectionInsert =
    'Insert into qtemp/OLINESEL (' || lineSelectionStrng || ' )';

    Execute Immediate lineSelectionInsert;

    where LineSelectionStrng is an Input Parameter to the SP & lineSelectionInsert is a work variable declared as Varchar(4000).

    Can anyone explain me how the above code works??
    I am also giving the structure of the File OLINESEL.

    Code:
    Create Table qtemp/OLINESEL                  
    (                                            
           brandID       char(2)        CCSID 37 
          ,zone          char(4)        CCSID 37 
          ,lineNumber    char(9)        CCSID 37 
          ,mlnModel      char(6)        CCSID 37 
          ,mlnEndDate    date                    
          ,mlnECLFlag    char(1)        CCSID 37 
          ,mlnNewPrc     decimal(15,0)           
          ,mlnPERMInp    decimal(15,0)           
          ,mlnPOSInp     decimal(15,0)           
          ,mlnClStkU     decimal(7,0)            
          ,mlnPwNAMAD    decimal(15,0)           
          ,mlnPERMMdc    decimal(15,0)           
          ,mlnRqUpFLt    decimal(9,2)            
          ,mlnPrcAdj     decimal(15,0)           
    );
    (What is CCSId 37??)


    Would Appreciate if anyone could provide me with the Answers.

    Thanks,
    Chandra.

  • #2
    Re: How to Pass Array as Input Parameter to the DB2 stored Procedure.

    Chandu,

    here is your file structure

    Code:
    DATE:  5/25/05            DISPLAY FIELD FOR FILE OLINESEL             PAGE:    1
    PHYSICAL FILE........... OLINESEL       FILE TYPE...............         PF
    LIBRARY................. QTEMP          RECORD LENGTH...........         89
    RECORD FORMAT........... OLINESEL       NUMBER OF FIELDS........            14
     KEY    FIELD   LENGTH   DEC  TYPE  FROM   TO      TEXT....................
          BRANDID      2           A      1     2
          ZONE         4           A      3     6
          LINENUMBE    9           A      7    15
          MLNMODEL     6           A     16    21
          MLNENDDAT   10           L     22    31
          MLNECLFLA    1           A     32    32
          MLNNEWPRC   15      0    P     33    40
          MLNPERMIN   15      0    P     41    48
          MLNPOSINP   15      0    P     49    56
          MLNCLSTKU    7      0    P     57    60
          MLNPWNAMA   15      0    P     61    68
          MLNPERMMD   15      0    P     69    76
          MLNRQUPFL    9      2    P     77    81
          MLNPRCADJ   15      0    P     82    89

    ccsid 37 = english.......see IBM etract below

    Code:
    Problems with data integrity develop because users are operating 
    with CCSIDs that have varied character support. That is, not all 
    characters in CCSID 37 are available in CCSID 875 and vice-versa. 
    
    
    Assume that the following names are to be entered by the 
    English-speaking user (display device supports a CCSID of 37): 
    
    
    alson 
    Gifford 
    When these entries are stored, the data integrity remains intact. 
    That is, an  is stored as an . This is because the display device 
    CCSID and the database CCSID are both 37. 
    
    Assume the following names are also input into 
    DATABASE1 by the Greek-speaking user (display device CCSID of 875): 
    
        
         
    DATABASE1 now consists of the following logical entries: 
    
    alson 
    Gifford 
        
         
    The Greek characters that make up the name are stored as those 
    characters only if the same character exists within CCSID 37. If
     the character does not exist, the server converts the characters 
    using a predetermined algorithm to a code point from code 
    page 37. The algorithm converts  to . 
    
    The following list shows the code point used to store the 
    first character of each name in DATABASE1. (Using only the 
    first character makes the example easier by eliminating long 
    strings of code points which would be shown if we presented 
    the code point for each character in the name.)

    Comment


    • #3
      Re: How to Pass Array as Input Parameter to the DB2 stored Procedure.

      Guys,

      We have a similar scenario where we’ll be receiving an input string from a JAVA web service.
      This string would have information in the form of array, which would be recieved by the Stored procedure on i-series. This SP will further refer to a COBOL API which will then process different elements of the array.

      Could anyone forward a sample code, which performs similar functionality.

      cheers,

      shanky

      Comment

      Working...
      X