IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL query not working in Cobol program.


IBM Mainframe Forums -> COBOL Programming
Post new topic   Reply to topic
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
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Feb 22, 2017 10:34 pm
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
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: 446
Location: USA

PostPosted: Thu Feb 23, 2017 11:58 am
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
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: 2455
Location: Hampshire, UK

PostPosted: Thu Feb 23, 2017 2:47 pm
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
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: 1353
Location: Israel

PostPosted: Thu Feb 23, 2017 4:09 pm
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: 446
Location: USA

PostPosted: Thu Feb 23, 2017 9:25 pm
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: 2481
Location: @my desk

PostPosted: Thu Feb 23, 2017 10:19 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sat Feb 25, 2017 12:54 am
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

Global Moderator


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

PostPosted: Mon Feb 27, 2017 12:49 pm
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: 446
Location: USA

PostPosted: Wed Mar 01, 2017 10:04 pm
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Mar 01, 2017 10:38 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Mar 01, 2017 10:54 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> COBOL Programming

 


Similar Topics
Topic Forum Replies
No new posts Using API Gateway from CICS program CICS 0
No new posts COBOL -Linkage Section-Case Sensitive COBOL Programming 1
No new posts COBOL ZOS Web Enablement Toolkit HTTP... COBOL Programming 0
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
No new posts Generate random number from range of ... COBOL Programming 3
Search our Forums:

Back to Top