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
 

 

SQL query not working in Cobol program.

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

New User


Joined: 28 Feb 2012
Posts: 9
Location: India

PostPosted: Wed Feb 22, 2017 5:56 pm    Post subject: SQL query not working in Cobol program.
Reply with quote

Hi ,

I have embedded a select query to a DB2 table in my cobol program. The query is always returning a not found , when I add the statement
doc_update_ts <> '0001-01-01-01.01.01.000001' in where clause . returns a value when this statement is removed.
The row i'm trying to fetch does not have doc_update_ts as '0001-01-01-01.01.01.000001', so it should be returning me the row.
When I run the exact same query on toad or in SPUFI , it works fine.

Query is as below

Code:
Select doc_name
from
doc
where
 doc_id = '123' and
 doc_update_ts <> '0001-01-01-01.01.01.000001'
order by audit_update_ts
fetch first 1 row only


Can someone please help be solve this issue.

coded
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


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

PostPosted: Wed Feb 22, 2017 10:34 pm    Post subject:
Reply with quote

Please check against which environment the Cobol Program is executed vs SPUFI.
Back to top
View user's profile Send private message
CuriousMainframer

New User


Joined: 28 Feb 2012
Posts: 9
Location: India

PostPosted: Thu Feb 23, 2017 11:33 am    Post subject: Reply to: SQL query not working in Cobol program.
Reply with quote

both running against the same environment.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 361
Location: USA

PostPosted: Thu Feb 23, 2017 11:58 am    Post subject: Reply to: SQL query not working in Cobol program.
Reply with quote

Okay. 2 things here:

1. Does your COBOL program abends at sqlcode = 100 ?
2. Is your COBOL program doing a delete and then trying to select the record which it has just deleted ?

There could be a case when your COBOL program deletes a record and then tries to read the same record later. Since, the record no longer exists (as the program sees it), it issues sqlcode = 100 and if you have an abend at sqlcode = 100, then ROLLBACK takes place.

So, after the abend or before the program executes, if you try to run the same query through SPUFI, you would see a record but when the program flow was executing the query there was No record and hence sqlcode = 100. You would think that why sqlcode = 100 when there was a record as you verify from SPUFI but keep in mind that the COBOL program may do a lot of other things than just running your query and that may include first deleting the record you are trying to read later.

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

New User


Joined: 28 Feb 2012
Posts: 9
Location: India

PostPosted: Thu Feb 23, 2017 12:52 pm    Post subject:
Reply with quote

Hi RahulG131 ,

Good point here , but cobol program is not deleting the record . also the strange part is that the query works fine when I remove the statement
doc_update_ts <> '0001-01-01-01.01.01.000001'
from the where clause .
the record I am trying to fetch has doc_update_ts as a valid date - 2016-01-12-07.08.15.001256. So it should fetch me the row with the check on doc_update_ts field.


The SQL statement does return a 100.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1768
Location: UK

PostPosted: Thu Feb 23, 2017 2:47 pm    Post subject:
Reply with quote

How is doc_update_ts defined in working storage and in the table?
Back to top
View user's profile Send private message
CuriousMainframer

New User


Joined: 28 Feb 2012
Posts: 9
Location: India

PostPosted: Thu Feb 23, 2017 3:16 pm    Post subject:
Reply with quote

In the table doc_update_ts is defined as timestamp . In the program doc_update_ts is defined as x(26) , also tried giving the value directly in quotes.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1228
Location: Israel

PostPosted: Thu Feb 23, 2017 4:09 pm    Post subject: Reply to: SQL query not working in Cobol program.
Reply with quote

Have you tried different things like:
Code:
doc_update_ts > '0001-01-01-01.01.01.000001'
doc_update_ts > '2001-01-01-01.01.01.000001'
doc_update_ts = '2016-01-12-07.08.15.001256'

it could help understand what is going on.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 361
Location: USA

PostPosted: Thu Feb 23, 2017 9:25 pm    Post subject:
Reply with quote

CuriousMainframer wrote:
Hi RahulG131 ,

Good point here , but cobol program is not deleting the record . also the strange part is that the query works fine when I remove the statement
doc_update_ts <> '0001-01-01-01.01.01.000001'
from the where clause .
the record I am trying to fetch has doc_update_ts as a valid date - 2016-01-12-07.08.15.001256. So it should fetch me the row with the check on doc_update_ts field.


The SQL statement does return a 100.


Does your COBOL program abends at sqlcode = 100 ?

.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2253
Location: @my desk

PostPosted: Thu Feb 23, 2017 10:19 pm    Post subject: Re: Reply to: SQL query not working in Cobol program.
Reply with quote

Marso wrote:
Have you tried different things like:
Code:
doc_update_ts > '0001-01-01-01.01.01.000001'
doc_update_ts > '2001-01-01-01.01.01.000001'
doc_update_ts = '2016-01-12-07.08.15.001256'

it could help understand what is going on.
I would do this. Or even select the doc_update_ts and see what it returns.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Sat Feb 25, 2017 12:54 am    Post subject:
Reply with quote

It looks like ,either your host variable is getting overlaid in your program, check the value of the host variable prior to the SELECT OR you have a wrong QUALIFIER in your BIND card, which makes you point to different database in the same Region.
If you say none of above suggestions are applicable then talk to your other senior people around you to know what's wrong and still if you couldn't get it then send an email to DBA and explain the situation.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1534
Location: Andromeda Galaxy

PostPosted: Mon Feb 27, 2017 12:49 pm    Post subject:
Reply with quote

Did you try to displaying your hostvariable just before query execution?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 361
Location: USA

PostPosted: Wed Mar 01, 2017 10:04 pm    Post subject: Reply to: SQL query not working in Cobol program.
Reply with quote

I believe, it should be made mandatory for TS to provide the actual solution, else we would never know if it was even a real question. If they don't come back within a week, give a warning.

Three (3) warnings and you are out.

.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Wed Mar 01, 2017 10:38 pm    Post subject: Reply to: SQL query not working in Cobol program.
Reply with quote

Ah, but then we'd risk having no users but ourselves, over time :-)

Don't worry, we tend to have memories (and the post count), so if they seem to make a habit of it, it tends to "come up" next time.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10243
Location: italy

PostPosted: Wed Mar 01, 2017 10:54 pm    Post subject: Reply to: SQL query not working in Cobol program.
Reply with quote

I wonder why the TS was not asked/did not care to post the statements used to create the table(s) involved
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 Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm
No new posts Need help in REXX CALL program Raje1002 CLIST & REXX 5 Wed Apr 19, 2017 11:18 pm
No new posts COBOL Programming Sandpit? jodrisco COBOL Programming 6 Wed Apr 12, 2017 3:47 am
No new posts RULES(NOEVENPACK) in cobol jackzhang75 COBOL Programming 5 Wed Mar 29, 2017 12:47 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm


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