[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


DB2 - Multi Fetch Cursor and character position in a string

Posted on 2011-02-23
Medium Priority
Last Modified: 2012-05-11
I have two kinds of exception tables, I need help writing DB2 SQL PL to fetch the store numbers for a product

PRODUCT_STORE is one kind of an exception where only one record is stored for a particular product when all the attributes(Vendor,Retail,Cost) are same for most of the stores, and its STORE_I is given 0, which denotes its a base record and it may contain many store numbers, which have to be accessed from another table (STORE_STATUS).
when any attribute of a product for a particular store changes then that record is store in this table as it is.
prod_i   store_i    vend_i     retl_a    cost_a
--------  ---------   ---------    ------   ---------
123                 0     23047        7.0          5.0
123             107     23047        6.0          5.0
123             251     23047        6.0          5.0
123            1042    23047        6.0          4.0

Open in new window

Now my main challenge is to decode all the store numbers from that base record.

 STORE_STATUS is another kind of an exception table where STATUS_T field is 1000 characters, where each character position denotes the store number if it is active
             ex:     'AA A  AAAA'  ( stores 1,2,4,7,8,,9,10 are active )
prod_i    beg_i     end_i     status_i
--------   --------   -------     ----------
123                1     1000     'AA A  AAAA A AA ..........' (until 1000 characters, only 'A' or ' ')
123          1001     2000     '   AA        A    ...................'
123          2001     3000     '                               .........A'

Open in new window

In above example, I should get an output
prod_i    store_i    vend_i   retl_a   cost_a
--------   ---------    --------  -------   ---------
123              107    20347      6.0           5.0
123              251    20347      6.0           5.0
123              107    20347      6.0           4.0
123                  1    20347      7.0           5.0
123                  2    20347      7.0           5.0
123                  4    20347      7.0           5.0
123                  7    20347      7.0           5.0
123                  8    20347      7.0           5.0
123                  9    20347      7.0           5.0
123                10    20347      7.0           5.0
123             3000    20347      7.0           5.0
all store ids which are active from 1 to 3000 

Open in new window

I should be using a multi fetch cursor to fetch multiple rows for each product and then find out the actual stores for each product and display them with other attirbutes.

Please Help, I am novice to PL/SQL

Question by:smartsujiki
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
LVL 37

Accepted Solution

momi_sabag earned 2000 total points
ID: 34969293
i am not sure i completely understand your case but i think the easiest solution would be:
1) create a table (for examples nums) with a single column (id) and insert the values 1 to 1000 into that table
2) now you can join that table to your statuses table and do the decoding, something like

select t1.*
from store_status t1 join nums t2
  on substr(store_t, t2.id, 1)  = 'A'

this should generate a table with a row for every active store
from here on, all you need is another join to your product table and you are good to go
LVL 46

Expert Comment

by:Kent Olsen
ID: 34969868
Hi smarts,

Whoever designed your database with a 1000 character status field that has to be indexed to get a store status might have thought that he was doing a good thing, but that completely negated the power of a relational database.  Is there any way to get the redesigned?

Unless you want to write a stored procedure to "take apart" the string, this seems to be a prime candidate for a recursive solution, which can do that in SQL.


Author Comment

ID: 34976937
Hi Kdo,

there is a store table already which lists each and every store for a particular product.
The volume of that table is huge and it takes hours to query against that table, so they came up with this new solution.
there are 1000+ products and each product can be active in about 3000 stores.

I don't think that there is any chance of redesign, since the new structure stores 95% less records than the old system
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 46

Expert Comment

by:Kent Olsen
ID: 34980075
Hi SS,

It may store 95% less records, but that isn't the only criteria.   :)

In the OP, you show an example where store_i is 1042, and describe status_i as a 1,000 character string indexed by store number.  How do you resolved store 1042 into a string of only 1,000 characters?


Author Comment

ID: 34981203

They have created two fields (beg_i & end_i).
So, for each product there would be three rows created if there are only 3000 stores

So, there will be three character strings created
beg_i.    end_i.      Store_status
_____.   _____.     ___________
1.              1000.      'AAA A A .....'
1001.        2000.      'AA.         A ..'
2001.        3000.      '.              A.  '

Author Comment

ID: 34999017
Hello All,

Can anyone give me a link to a sample db2 sql pl program. Whic must include use of cursors and procedure.
My search in google gave me evrything written in COBOL.
I don't use that.
Please help
LVL 46

Expert Comment

by:Kent Olsen
ID: 34999784
Hi Smart,

Sorry -- I missed your previous post....

What flavor of DB2 are you using?  There are 3:  DB2 on the AS/400; DB2 for Z/OS (OS390); and UDB/LUW (for Linux, Unix, and Windows).

There are some significant differences in the 3.

LVL 37

Expert Comment

ID: 34999985
have you even looked at my post?
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35913493
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question