Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to use a dynamically created variable in DB2 query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 134
Location: India

PostPosted: Thu Dec 22, 2011 7:02 pm    Post subject: How to use a dynamically created variable in DB2 query
Reply with quote

HI,

I am trying to use a variable (group variable) in DB2 query whose length varies due to the presence of variable size cobol table defined in it.

Query looks like as given below:

Code:

     UPDATE  RETAIL_TABLE
        SET  LAST_DT    = :WS-LAST-DT,
     WHERE   ITEM_NBR       = :WS-ITEM-NBR
       AND   STORE_NBR     IN  :WS-TEMP-ARRAY


And, the array is defind as given below:

Code:

       01  WS-TEMP-ARRAY.
           05  FILLER                       PIC X(01)     VALUE     '('.
           05  WS-TEMP-ARRAY-STR            OCCURS 0 TO 99 TIMES
                                            DEPENDING ON WS-COUNT.
               10  WS-TEMP-STR-NBR          PIC S9(04)    COMP.
               10  WS-COMMA                 PIC X(01).
           05  WS-FILLER                    PIC X(02).


now, e.g. WS-TEMP-STR-NBR is populated with values 100,101,102,103 and 104 and WS_COMMA is populated with ',' for each instance. WS-COUNT is having the value 5. WS-FILLER with value '0)'.

Now, if I compile the program, it shows precompile error as the datatype of the variable is not matching with STORE_NBR (SMALLINT, S9(04) COMP).
if I use it as
Code:


       AND   STORE_NBR     IN  (WS-TEMP-ARRAY)


then it gives bind error as WS-TEMP-ARRAY is not defind as the table column.

I populated WS-TEMP-ARRAY variable dynamically this in order to update the table in one shot for the same "item number" and "last date" but for differnt store numbers in the input file. Is there any way to use such a dynamically populated variable in the query? Please suggest, if anything can be done in such a case. thanks.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Dec 22, 2011 7:48 pm    Post subject:
Reply with quote

i don't remember how a WORKING-STORAGE INLIST needs to be referenced,
but either way:
1. place a colon in front of your host variable (WS-TEMP-ARRAY in your example)

2. WS-FILLER does not do what you think.
In and ODO WS-FILLER's address always starts at what would be the 100th occurance.

i don't think you are building an appropriate literal for the INLIST.
db2 takes the length of the structure referenced in the SQL.
since odo's are compiled with max length.

Large INLIST's should be sorted.
the complete table must be populated with valid data values - even if it is zero.

try removing comma from last item.
oh, and get rid of the ( and ).
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Dec 22, 2011 7:59 pm    Post subject:
Reply with quote

in your sql you may have to reference a structure
that starts with a comp-3 field,
and ends with a comp-3 field,
not a space.
(and yes, each comp-3 field is separated by a comma)
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 -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts Writing on to GSAM variable length rohanthengal JCL & VSAM 6 Fri Mar 03, 2017 7:46 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us