View previous topic :: View next topic
|
Author |
Message |
CuriousMainframer
New User
Joined: 28 Feb 2012 Posts: 9 Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Please check against which environment the Cobol Program is executed vs SPUFI. |
|
Back to top |
|
|
CuriousMainframer
New User
Joined: 28 Feb 2012 Posts: 9 Location: India
|
|
|
|
both running against the same environment. |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
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 |
|
|
CuriousMainframer
New User
Joined: 28 Feb 2012 Posts: 9 Location: India
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
How is doc_update_ts defined in working storage and in the table? |
|
Back to top |
|
|
CuriousMainframer
New User
Joined: 28 Feb 2012 Posts: 9 Location: India
|
|
|
|
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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Did you try to displaying your hostvariable just before query execution? |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
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 |
|
|
|