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
 

 

How to see the SQL statement that was executed?

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

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Mon Mar 12, 2012 7:14 am    Post subject: How to see the SQL statement that was executed?
Reply with quote

I have embedded some SQL statement like below to cobol application program:
Code:
 EXEC SQL                                                 
     DECLARE CURCBKOPCS02  SCROLL  CURSOR FOR             
     SELECT *                                             
     FROM   CBKOPCS                                       
     WHERE  OP_CGOF_FLAG = :WK-DBIO-PK-OP-CGOF-FLAG       
     AND    MCAC LIKE      :WK-DBIO-PK-MCAC               
     AND    CSTM_NO     >= :WK-CSTM-NO-LOW               
     AND    CSTM_NO     <= :WK-CSTM-NO-HIGH               
     AND    OP_CGOF_DATE>= :WK-DBIO-OP-CGOF-DATE-START   
     AND    OP_CGOF_DATE<= :WK-DBIO-OP-CGOF-DATE-END     
     ORDER BY PDAC_SEQ,BSAC_SEQ                           
 END-EXEC.


I pass host variable value to it, but the SQL code is always 100.

Now I want to verify if the SQL statement that is executed contain the exactly values I passed. How can I achieve this?

I read some materials on EXPLAN_TABLE and DSN_STATEMNT_TABLE , but no expected answer...
Back to top
View user's profile Send private message

dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Mon Mar 12, 2012 7:17 am    Post subject:
Reply with quote

for example, when I pass value to the program, and If the sql statement receive value correctly, I want to see below result:
Code:
 EXEC SQL                                                 
     DECLARE CURCBKOPCS02  SCROLL  CURSOR FOR             
     SELECT *                                             
     FROM   CBKOPCS                                       
     WHERE  OP_CGOF_FLAG = '1'       
     AND    MCAC LIKE      'A%'               
     AND    CSTM_NO     >= '1111'               
     AND    CSTM_NO     <= '9999'               
     AND    OP_CGOF_DATE>= 20110101   
     AND    OP_CGOF_DATE<= 99999999     
     ORDER BY PDAC_SEQ,BSAC_SEQ                           
 END-EXEC.
Back to top
View user's profile Send private message
xknight

Active User


Joined: 22 Jan 2008
Posts: 117
Location: Liberty city

PostPosted: Mon Mar 12, 2012 9:17 am    Post subject:
Reply with quote

Hello,

Do you have IBM Debugger installed at your site, making use of that it is possible to monitor every variables. Otherwise, If you are the owner of the program for testing purpose you shall modify the program to display the variables, when you receive SQLCODE = 100, then any DB2 related tools (SPUFI,QMF) might assit you from there.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon Mar 12, 2012 9:20 am    Post subject:
Reply with quote

Hi,

Will you please provide your open and fetch statements?

Also I am just wondeing with date format you have provided in query

Quote:
AND OP_CGOF_DATE>= 20110101


Doe is required to be
Code:
AND    OP_CGOF_DATE>= '2011-01-01'


Regards,
Chandan
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 982
Location: Oostende, Belgium

PostPosted: Mon Mar 12, 2012 12:41 pm    Post subject:
Reply with quote

chandan.inst wrote:
Hi,

Will you please provide your open and fetch statements?

Also I am just wondeing with date format you have provided in query

Quote:
AND OP_CGOF_DATE>= 20110101


Doe is required to be
Code:
AND    OP_CGOF_DATE>= '2011-01-01'

The fact that a field is called "whatever_date" does not imply that it is in DB2 DATE format!
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Mar 12, 2012 12:53 pm    Post subject:
Reply with quote

without a step thru debugger,
often it is easier to put in display statements of your host variables
just prior to OPENing the cursor.

or, only execute the display statements when you receive a +100 from the first FETCH.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Tue Mar 13, 2012 6:35 am    Post subject:
Reply with quote

thanks for your answers.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Mar 13, 2012 7:49 pm    Post subject:
Reply with quote

probably the problem is 'A%' in host variable WK-DBIO-PK-MCAC
trying filling it all with '%'
if WK-DBIO-PK-MCAC is pic x(4) move 'A%%%%'
Back to top
View user's profile Send private message
Ed Goodman

Active Member


Joined: 08 Jun 2011
Posts: 556
Location: USA

PostPosted: Tue Mar 13, 2012 8:25 pm    Post subject:
Reply with quote

I second this question! I was looking for this same thing last week, and had no luck.

I was hoping for something similar to the DLITRACE available in IMS. It's a function of IMS that allows you to keep a log of all of the DLI calls being made from a given IMS run. You don't need to do anything to the program, you just add some parms and a DD and out pops the trace.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Wed Mar 14, 2012 12:29 am    Post subject:
Reply with quote

BTS (IMS Batch Terminal Simulator) has an SQLTRACE feature that shows the host variables associated with each SQL call.
Back to top
View user's profile Send private message
Ed Goodman

Active Member


Joined: 08 Jun 2011
Posts: 556
Location: USA

PostPosted: Wed Mar 14, 2012 6:35 am    Post subject:
Reply with quote

I'm not sure if the OP is using IMS for his DB2 environment. I was just making a comparison.

I just convinced that there is an SQL trace out there somewhere. It's IBM after all!
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 -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm
This topic is locked: you cannot edit posts or make replies. Updating value of key using rewrite s... ParminderKumar COBOL Programming 4 Thu Aug 04, 2016 12:46 am
No new posts Inspect statement spoorni COBOL Programming 5 Mon Jun 06, 2016 9:53 pm


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