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
 

 

Facing problems in Fetching the field

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Wed May 20, 2009 5:25 pm    Post subject: Facing problems in Fetching the field
Reply with quote

Hi All,

I am facing problem while fetching data from DB2 tables.The query is giving me correct output in SPUFI but while trying to do the same in the program I am able to fetch only one record for beta_nr but the Kunde_nr is being fetched correctly.Please suggest what might be the problem or any alternate workaround will also be appreciated.

The query is as below:

Declaration of Cursor

EXEC SQL DECLARE KUND_BETA CURSOR FOR
SELECT DISTINCT C.KUNDE_NR,B.BETA_NR
FROM RABAT_AFTALE A
,RABAT_MODTAGER B
,RABAT_CU_KUND C
WHERE (B.RABAT_MODTAGER_ID = A.RABAT_MODTAGER_ID
AND C.RABAT_KUNDEN_ID = A.RABAT_KUNDEN_ID)
QUERYNO 1
END-EXEC.

Opening the Cursor : before performing the fetch loop
EXEC SQL OPEN KUND_BETA END-EXEC.

Fetch :This is being done in a loop with the first fetch before the loop and then calling the fetch section in a inline perform untill sqlcode =+100.I am able to fetch the Kunde-Nr but Beta-nr is only getting fetched once.

EXEC SQL FETCH KUND_BETA
INTO :DCLRABAT-CU-KUND.KUNDE-NR,
:DCLRABAT-MODTAGER.BETA-NR
END-EXEC.


Closing the Cursor:This is being done once the loop for fetching is complete

EXEC SQL CLOSE KUND_BETA END-EXEC.


Can anyone please suggest what might be the problem.


Thanks in Advance,
Rahul
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed May 20, 2009 11:30 pm    Post subject:
Reply with quote

Hello,

Suggest the program code is incorrect.

Why are all of the fetches not done within the loop?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu May 21, 2009 12:08 am    Post subject: Reply to: Facing problems in Fetching the field
Reply with quote

Quote:
Why are all of the fetches not done within the loop?


to avoid an if inside the loop
( general logic - not necessarily db2 related )

Code:
call some_function(retcode,parm1,parm2,.....)
do  while ( retcode = 0 )
    ....
    .... carry on some process
    ....
    call some_function(retcode,parm1,parm2,.....)
end


as opposed to

Code:
do forever
    call some_function(retcode,parm1,parm2,.....)
    if retcode <not_equal> 0 then leave
    ....
    .... carry on some process
    ....
end


the language mimics rexx , but the logic is sound
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Thu May 21, 2009 10:22 am    Post subject: Reply to: Facing problems in Fetching the field
Reply with quote

I have even tried to perform the fetch section twice before the loop still it fetched the beta_nr only once.Kindly note that I am able to fetch the kunde number for each fetch.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu May 21, 2009 10:30 am    Post subject:
Reply with quote

Rahul_kumar wrote:
till it fetched the beta_nr only once


Rahul_kumar,

the quoted phrase is confusing. What do you mean by this?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu May 21, 2009 11:12 am    Post subject:
Reply with quote

Rahul_kumar,

are you saying that the FETCH does not populate all host variables? That is BS.

I have no idea what your code looks like, but keep in mind that you are populating two different structures with your FETCH:
:DCLRABAT-CU-KUND for KUNDE-NR,
and
:DCLRABAT-MODTAGER for BETA-NR
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu May 21, 2009 7:31 pm    Post subject:
Reply with quote

Hello,

Quote:
I have even tried to perform the fetch section twice before the loop still it fetched the beta_nr only once
How does this manifest itself? Why do you believe beta_nr was "fetched only once"?

Does the code check the sqlcode before using the host variables returned from a fetch?

Keep in mind that your code is not the same as the spufi. . .

It sounds like the program code to deal with the cursor is the problem. . .

You might isolate the sql from this program into a small test program that only declares and opens the cursor and in a loop fetchs all of the results from the cursor open and displays them. No real logic whatsoever - just the sql to process the cursor.
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Sat May 23, 2009 2:56 am    Post subject: Reply to: Facing problems in Fetching the field
Reply with quote

Rahul_kumar, The best way to understand how your cursor is working is to go by what Dick has suggested in his last post.
If the results are not what you have seen in SPUFI then you might try looking for the database base your query is hitting in your program.

Remember the creator you would have given while fetching data in SPUFI.

WTH.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Sat May 23, 2009 3:05 am    Post subject:
Reply with quote

keep in mind the TS is complaining that only one of the two host variables is being populated

that would have nothing to do with creator
- though that is often a problem in other threads.
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Sat May 23, 2009 3:28 am    Post subject: Reply to: Facing problems in Fetching the field
Reply with quote

icon_biggrin.gif..i am getting so confused with this KUNDE_NR and BETA_NR, Obviously the FETCH must be populating both the fields , its just that one has a value and the other having spaces or anything depending on data type.
So, I just thought of the scenario where for one Creator the value is populated for both the variables and for other its only for one.
May be I am thinking too much icon_smile.gif.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat May 23, 2009 4:27 am    Post subject:
Reply with quote

Hello,

Quote:
May be I am thinking too much
Happens a lot when the TS does not follow up with needed info and we start using our imagination icon_wink.gif
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Sat May 23, 2009 4:35 am    Post subject: Reply to: Facing problems in Fetching the field
Reply with quote

Quote:
Happens a lot when the TS does not follow up with needed info and we start using our imagination

That is so true , having said that ,Dick do you really think my assumption is an impossible scenario which can never occur and it should not be considered while resolving this issue.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Sat May 23, 2009 5:11 am    Post subject:
Reply with quote

Quote:
That is so true , having said that ,Dick do you really think my assumption is an impossible scenario which can never occur and it should not be considered while resolving this issue.


Succor,

Since I made the original criticism, and my name is also Dick,
I will answer with my reasoning.

Actually, my criticism was for the TS, for making such a comment:
no way in hell will a FETCH populate only some of the host variables.
(I consider NULL-Indicators a host variable - without which a negative SQLCODE would be returned).

you got caught in my sights, because I did not want the TS
to even try to think that DB2 was selectively populating host variables.
I assumed that the TS was not looking in the proper structure for the host variable.

I will give you the fact that different CREATORs (different tables)
could/would return SPACES for one of the host variables
and
that is the reason the TS thinks that one of the host variables is not being populated.
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Sat May 23, 2009 6:38 pm    Post subject: Reply to: Facing problems in Fetching the field
Reply with quote

Quote:
that would have nothing to do with creator
I reckon people have mis understood you in past and they are still doing, irrespective of everything its great to learn things from you people.
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 Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts Amount field is getting corrupted whe... thesumitk SYNCSORT 5 Tue Oct 18, 2016 8:20 pm
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am


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