|
View previous topic :: View next topic
|
| Author |
Message |
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
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 |
|
 |
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
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 |
|
|
xknight
Active User
Joined: 22 Jan 2008
Posts: 117
Location: Liberty city
|
|
|
|
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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
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 |
|
|
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania
|
|
|
|
| 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| thanks for your answers. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
Ed Goodman
Active Member
Joined: 08 Jun 2011
Posts: 556
Location: USA
|
|
|
|
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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| BTS (IMS Batch Terminal Simulator) has an SQLTRACE feature that shows the host variables associated with each SQL call. |
|
| Back to top |
|
|
Ed Goodman
Active Member
Joined: 08 Jun 2011
Posts: 556
Location: USA
|
|
|
|
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 |
|
|
|
|
