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 code 100 in an online program

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

New User


Joined: 13 Sep 2005
Posts: 35
Location: India

PostPosted: Wed Mar 28, 2007 5:39 pm    Post subject: SQL code 100 in an online program
Reply with quote

I am getting an SQL code of 100 in one of my SQLs in an online program
When I try to run the same query through SPUFI it is fetching a record.What could be the reason
Back to top
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Wed Mar 28, 2007 6:43 pm    Post subject: Re: SQL code 100
Reply with quote

rinkubhat wrote:
I am getting an SQL code of 100 in one of my SQLs in an online program
When I try to run the same query through SPUFI it is fetching a record.What could be the reason
The simplest answer is that something is different between either the querys or the environments.
Back to top
View user's profile Send private message
rinkubhat

New User


Joined: 13 Sep 2005
Posts: 35
Location: India

PostPosted: Wed Mar 28, 2007 6:56 pm    Post subject:
Reply with quote

No both the queries are same and also the environments
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Mar 29, 2007 1:51 pm    Post subject:
Reply with quote

Can you paste the sql used in the online program and also the one you used in SPUFI..?
Back to top
View user's profile Send private message
rinkubhat

New User


Joined: 13 Sep 2005
Posts: 35
Location: India

PostPosted: Thu Mar 29, 2007 3:26 pm    Post subject:
Reply with quote

Hey Vinay
The Spufi Code is
Code:
  select                                                   
    iss_alt_id ,                                           
    setup_rcpn ,                                           
    coalesce(setup_tmst,'0001-01-01-00.00.00.000000') ,   
    coalesce(req_tmst,'0001-01-01-00.00.00.000000') ,     
    req_stat_c ,                                           
    rtry_cnt_n ,                                           
    coalesce(rply_sent_tmst,'0001-01-01-00.00.00.000000'),
    rply_stat_c ,                                         
    rply_c ,                                               
    coalesce(sl.create_tmst,'0001-01-01-00.00.00.000000'),
    sl.upd_id ,                                           
    coalesce(sl.upd_tmst,'0001-01-01-00.00.00.000000'),   
    req_id ,                                               
    req_ty_c ,                                             
    coalesce(cd.ref_code_abbr, 'Desc not avail') ,         
    coalesce(cd1.ref_code_abbr, 'Desc not avail') ,       
    coalesce(cd2.ref_code_abbr, 'Sec type not avail')     
        from                                             
 smd13dbo.t_smsl_setup_log as sl                         
          left outer join                               
  smd13dbo.t_smcd_code_set as cd                         
              on cd.ref_code_nm  = 'PMRQSTCD'  and       
                 cd.code_low_val = req_stat_c           
             and cd.multi_co_n   = 0                     
          left outer join                               
  smd13dbo.t_smcd_code_set as cd1                       
              on cd1.ref_code_nm  = 'PMRPLYCD'  and     
                 cd1.code_low_val = rply_stat_c         
                 and cd1.multi_co_n   = 0               
          left outer join                               
   smd13dbo.t_smcd_code_set as cd2                       
              on cd2.ref_code_nm  = 'ISSIDTYP'    and   
                 cd2.code_low_val = iss_xref_ty_c       
                 and cd2.multi_co_n   = 0               
        where                                           
          setup_rcpn ='XTRAC' and                       
          req_id     ='W589873-26MAR07'   
and sl.multi_co_n = 0           
[/b]

And the code in program is

Code:
     exec sql                                                     
       select                                                     
         iss_alt_id ,                                             
         setup_rcpn ,                                             
         coalesce(setup_tmst,'0001-01-01-00.00.00.000000') ,     
         coalesce(req_tmst,'0001-01-01-00.00.00.000000') ,       
         req_stat_c ,                                             
         rtry_cnt_n ,                                             
         coalesce(rply_sent_tmst,'0001-01-01-00.00.00.000000'),   
         rply_stat_c ,                                           
         rply_c ,                                                 
         coalesce(sl.create_tmst,'0001-01-01-00.00.00.000000'),   
         sl.upd_id ,                                             
         coalesce(sl.upd_tmst,'0001-01-01-00.00.00.000000'),     
         req_id ,                                                 
         req_ty_c ,                                               
         coalesce(cd.ref_code_abbr, 'Desc not avail') ,           
         coalesce(cd1.ref_code_abbr, 'Desc not avail') ,         
         coalesce(cd2.ref_code_abbr, 'Sec type not avail')       
 into                               
   :sl-iss-alt-id ,                 
   :sl-setup-rcpn ,                 
   :sl-setup-tmst ,                 
   :sl-req-tmst ,                   
   :sl-req-stat-c ,                 
   :sl-rtry-cnt-n ,                 
   :sl-rply-sent-tmst ,             
   :sl-rply-stat-c ,               
   :sl-rply-c ,                     
   :sl-create-tmst ,               
   :sl-upd-id ,                     
   :sl-upd-tmst ,                   
   :sl-req-id ,                     
   :sl-req-ty-c ,                   
   :ps308-req-sta-desc-o ,         
   :ps308-rply-stat-desc-o ,       
   :ps308-alt-id-desc-o             
from                                             
  t_smsl_setup_log as sl                         
  left outer join                               
    t_smcd_code_set as cd                       
      on cd.ref_code_nm  = 'PMRQSTCD'  and       
         cd.code_low_val = req_stat_c           
         and cd.multi_co_n   = :ws-multi-co-n   
  left outer join                               
    t_smcd_code_set as cd1                       
      on cd1.ref_code_nm  = 'PMRPLYCD'  and     
         cd1.code_low_val = rply_stat_c         
         and cd1.multi_co_n   = :ws-multi-co-n   
  left outer join                               
    t_smcd_code_set as cd2                       
      on cd2.ref_code_nm  = 'ISSIDTYP'    and   
         cd2.code_low_val = iss_xref_ty_c       
         and cd2.multi_co_n   = :ws-multi-co-n   
where                                           
     setup_rcpn = :sl-setup-rcpn  and     
     req_id     = :sl-req-id             
     and sl.multi_co_n = :ws-multi-co-n   
 end-exec                                 
[/b]
Back to top
View user's profile Send private message
prabs2006

Active User


Joined: 12 Jan 2006
Posts: 103

PostPosted: Thu Mar 29, 2007 3:49 pm    Post subject: Re: SQL code 100 in an online program
Reply with quote

Check the where clause values in your program. Is it the same value that you have given in SPUFI?
Back to top
View user's profile Send private message
rinkubhat

New User


Joined: 13 Sep 2005
Posts: 35
Location: India

PostPosted: Thu Mar 29, 2007 4:21 pm    Post subject:
Reply with quote

yes u can see it in code also
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Mar 29, 2007 6:21 pm    Post subject:
Reply with quote

rinkubhat,

DB2 cannot behave so strange. The only thing i can suspect is the values of the host variables during the run time. Try putting the dispaly of these variables in the program before the query is executed and check with the values you have hard coded in SPUFI. This can be the reason provided that your program is not deleting any rows in any of the tables before it executes this query.

Please check for the values of the host variables during the run time.
:ws-multi-co-n ..?
:sl-setup-rcpn ..?
:sl-req-id.........?
Back to top
View user's profile Send private message
rinkubhat

New User


Joined: 13 Sep 2005
Posts: 35
Location: India

PostPosted: Thu Mar 29, 2007 6:25 pm    Post subject:
Reply with quote

I checked the values in Intertest they are same as they are in SPUFI
Dont know why this is happening??????????????
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Mar 29, 2007 6:34 pm    Post subject:
Reply with quote

ok..what is the OWNER & QUALIFIER keywords used in the BIND step.

This time let me make sure both online and spufi are referring to the right tables.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Thu Mar 29, 2007 6:38 pm    Post subject:
Reply with quote

Is setup_rcpn 5 bytes and is :sl-setup-rcpn 5 bytes?
Is req_id 15 bytes and is :sl-req-id 15 bytes?
Back to top
View user's profile Send private message
rinkubhat

New User


Joined: 13 Sep 2005
Posts: 35
Location: India

PostPosted: Thu Mar 29, 2007 6:50 pm    Post subject:
Reply with quote

Hi William
Both setup_rcpn and :sl-setup-rcpn are defined for 8 bytes
And req_id and is :sl-req-id for 20 bytes

does that make a difference?
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Thu Mar 29, 2007 7:03 pm    Post subject:
Reply with quote

It could, it depends on how the one backfills the literals or pads the dissimilar lengths.
Assuming the host variables are standard character definitions with trailing blanks, try the Spufi with all 8 and 20 bytes between the quotes.
That's just about the only thing I can think of..... icon_confused.gif
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Mar 29, 2007 8:00 pm    Post subject:
Reply with quote

I can still only say there is something wrong from your perspective.
Though i dont know whats wrong i could suspect that because i cannot suspect DB2 icon_razz.gif

Hope you will not come up by saying something wrong with DB2 icon_razz.gif
Back to top
View user's profile Send private message
rinkubhat

New User


Joined: 13 Sep 2005
Posts: 35
Location: India

PostPosted: Fri Mar 30, 2007 3:09 pm    Post subject:
Reply with quote

Thanks Everyone for their suggestions

I was able to fetch the data there was nothing wrong with the SQL
I compiled the program which was calling this particular program and it worked

I guess it was earlier compiled for some other region.


But I have still one query Since the Calling program was NON DB2
How come the re compilation of that program solved the problem?
As even before compiling the calling program again I was able to process the subprogram through Intertest.!!!
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Fri Mar 30, 2007 3:49 pm    Post subject:
Reply with quote

It must have something the way the host variables were defined and passed.... icon_confused.gif
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Fri Mar 30, 2007 8:05 pm    Post subject:
Reply with quote

Yes, that could be the only reason then.

You might have changed the calling program to initialise the host variable or the way they get values. It still wont through timestamp error since it does not have any DBRM's.
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 I can not compile my program PL1 V3.R... Miguel Fernandez PL/I & Assembler 13 Tue Dec 06, 2016 8:30 pm
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What is the code in CLIST to enable t... jackzhang75 CLIST & REXX 1 Fri Dec 02, 2016 3:02 am
No new posts IDEAL - Code Retrieval yugendran CA Products 0 Fri Nov 25, 2016 3:27 pm
No new posts IMS BMP program causes 878 system abend Artemk IMS DB/DC 7 Tue Nov 22, 2016 8:26 pm


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