Dear experts,

On behalf of a customer I have the following question – better the following problem.

At first the environment.
WebFocus 7.6.5 (mainframe)
DB2 V8.1 for z/Os (mainframe)

Now I would like to describe the problem.
We are having a view which looks like this:
SELECT A.HHHHHH, A.VVVVVV, A.CCCCC, A.BBBBBB, A.NNNNNN, …..A.AAAAA,
A….
FROM XXXXX.XXXXXXX A
RIGHT OUTER JOIN
YYYYYY.YYYYYYYY B
ON AWWWW = B.EEEEE
WHERE CONCAT(A.HHHHHH, '28')
BETWEEN CHAR(B.DDDDDD) AND CHAR(B.FFFFFF);

The fields are defined as
HHHHHH CHAR 6 (contains rows like 200403 (year + month))
DDDDDD Date
FFFFFFF Date

If I select now data from 2010 or 2008 the result is fine (I compare the output with the result getting in Spufi), but if I select year 2009 – the result is completely different. (amazing)

Now I have changed the WHERE clause and everything is fine – but I would like to know if there is a bug in WebFocus or in DB2 and how is it possible that the view is working for certain data and doesn’t work for other dates.

SELECT A.HHHHHH, A.VVVVVV, A.CCCCC, A.BBBBBB, A.NNNNNN, …..A.AAAAA,
A….
FROM XXXXX.XXXXXXX A
RIGHT OUTER JOIN
YYYYYY.YYYYYYYY B
ON AWWWW = B.EEEEE
WHERE CONCAT(A.HHHHHH, '28')
BETWEEN B.DDDDDD AND B.FFFFFFF;

TIA and best regards,
Siegfried

This message has been edited. Last edited by: Kerry,

quote:

and doesn’t work for other dates

quote:

WHERE CONCAT(A.HHHHHH, '28')
BETWEEN CHAR(B.DDDDDD) AND CHAR(B.FFFFFF);

Which dates? What you were making was in reality string comparisons. Dates should be used as dates, not as character strings.

quote:

I would like to know if there is a bug in WebFocus or in DB2

There is no bug! When you attempt to use DATE fields as strings most frequent than not you will get inconsistent results. Any database "knows" that a date of 08-25-2009 should come before 02-17-2010, right? However, if you attempt to use them as strings, by ASCII comparison "02-17-2010" will be retrieved before "08-25-2009". That is the reason why your WHERE ... BETWEEN ... AND structure may have been giving you "incorrect" results.

If you cannot avoid comparing a string to a date due to your database design, make sure that when you convert your date field into a string (I assume that's what the CHAR(date_field) function does) you are using a specific format mask that matches the string you are comparing to.

This would be better doing conversion on char field to date other than reverse as you do CHAR(B.DDDDDD).

date('1999-10-22')

Kofi

Dear experts,


Thanks a lot for your replies. I can see your points - but I have still one question.
Why are the results different - I do the SELECT statement with Spufi - the result shows the expected values. If I do it with WebFocus then the returned rows are "wrong".
TIA

Siegfried

Two questions:

1. What is Spufi?
2. How are you issuing the SELECt statement from WebFOCUS? Are you using SQL passthru? If you post the actual WebFOCUS code you are running (not the made-up one you posted originally) we will be better able to determine what's going on.

Dear experts,

1)SPUFI stands for SQL Processing Using File Input. It supports the online execution of SQL statements from a TSO terminal – that means in a mainframe environment. It's like DB2 zOS's command line processor. Spufi is using DB2®-supplied plans (DSNESPCS, DSNESPRR, and DSNESPUR). SPUFI plans and packages using ENCODING(EBCDIC).
2)Yes - the statement is started in WebFocus (at the mainframe) and the result is wrong. To find out what is wrong the statement was also executed in Spufi.

In WebFocus a PLAN called DSNACLI (DB2® ODBC ) is used to access the data.

Here the SQL statement I’m talking about:

SELECT A.ERSTMON, A.HVNL, A.FB_ID, A.ABT_ID, A.ZAHLST, A.GRUPPE,
A.ABL, A.JRLBUZ, A.SL, A.SUMME, A.BEARBD, A.SCHWEBD,
A.ABT_NAME, A.BUZB, A.BUZBER, A.BUZKL, A.BUZME, A.BUZR,
A.ERFD, A.ERLD, A.ERSCHW, A.FB_NAME, A.FID, A.FIR,
A.GESELL, A.LIST, A.LZ, A.POL, A.RENT, A.REP, A.SJL,
A.TARIF, A.UNT, A.VNR, A.VT, A.WEG, A.WIED, A.VERDIFAEHIG,
A.ZBM_PHGR, B.DG, B.BS, B.LB, B.ABTEILUNG, B.SKILLNAME,
B.PHGR
FROM LS77IDV.TLDWAANTRAGSBEARB A
RIGHT OUTER JOIN
LS77IDV.TLDWASKILLGRUPPE B
ON A.ZBM_PHGR = B.PHGR
WHERE
CONCAT(A.ERSTMON, '28') BETWEEN CHAR(B.GUELTIG_VON) AND CHAR(
B.GUELTIG_BIS)) ;

ERSTMON looks like this:

ERSTMON
--------------------------
200403
200404
200405

And here an example of the different results:
Spufi:
ERSTMON = '201001': DSNE610I NUMBER OF ROWS DISPLAYED IS 5680
ERSTMON = '200901': DSNE610I NUMBER OF ROWS DISPLAYED IS 4647
ERSTMON = '200812': DSNE610I NUMBER OF ROWS DISPLAYED IS 8314
WebFocus:
ERSTMON = '201001': NUMBER OF RECORDS IN TABLE= 5680 LINES= 5680
ERSTMON = '200901': NUMBER OF RECORDS IN TABLE= 0 LINES= 0
ERSTMON = '200812': NUMBER OF RECORDS IN TABLE= 8314 LINES= 8314

Here the SQL which runs without having a problem in WebFocus and Spufi as well:

SELECT A.ERSTMON, A.HVNL, A.FB_ID, A.ABT_ID, A.ZAHLST, A.GRUPPE,
A.ABL, A.JRLBUZ, A.SL, A.SUMME, A.BEARBD, A.SCHWEBD,
A.ABT_NAME, A.BUZB, A.BUZBER, A.BUZKL, A.BUZME, A.BUZR,
A.ERFD, A.ERLD, A.ERSCHW, A.FB_NAME, A.FID, A.FIR,
A.GESELL, A.LIST, A.LZ, A.POL, A.RENT, A.REP, A.SJL,
A.TARIF, A.UNT, A.VNR, A.VT, A.WEG, A.WIED, A.VERDIFAEHIG,
A.ZBM_PHGR, B.DG, B.BS, B.LB, B.ABTEILUNG, B.SKILLNAME,
B.PHGR
FROM LS77IDV.TLDWAANTRAGSBEARB A
RIGHT OUTER JOIN
LS77IDV.TLDWASKILLGRUPPE B
ON A.ZBM_PHGR = B.PHGR
WHERE
CONCAT(A.ERSTMON, '28') BETWEEN GUELTIG_VON AND
B.GUELTIG_BIS;
TIA and best regards,

Siegfried

pls post the webfocus master files you have on this project
and the fex you have created

to make it readable put it between code tags

I suggest turning on the SQL trace facility and running your code for 2008 and 2009. Then compare the trace output for the two years and see if there are any differences between the two.

Are you sure the DB2 fields are just date fields and not date/time fields? I'm not a DB2 users, but in Oracle date fields are in reality date/time fields. To use the EQ test and to have it evaluated by the database, we have to make sure that the time portion is midnight. This is why we also code something like this:

WHERE DATE1 GE '20100110'
  AND DATE1 LT '20100110'

instead of

WHERE DATE1 EQ '20100110'

This way I don't care if there is a time stored with the data or if WebFOCUS or the database does the screening, I will always get the correct number of rows for that day. The second WHERE clause will only find the dates where the time portion is midnight.

Hi experts,

Thanks a lot for your replies.

At first I would like to answer the questions.

@Dan - we did this already and we saw the statements were correctly transmited from WebFocus to DB2.

@jgelona - here I will show the definitions of the two fields. In DB2 we have several formats to deal with date and time. E.g. Date, we have also a time a have a timestamp. This is different compared to Oracle
GUELTIG_VON 7 DATE
GUELTIG_BIS 8 DATE

@FrankDutch

I will ask my customer if they can send me these two files. But I have to ask this question - are these files also available for the mainframe? And what does it mean FEX?


I would also like to express a compliment. Compared to other platforms I have the impression all the people here are really trying to help people like me. Thanks a lot.
TIA,

Siegfried

Siegfried

Maybe it is good to learn the bsics of Focus.

You can create a connection to your DB2 database within focus.
The master file describes the database tables or a view on the database.
A FEX is an Focus EXecutable that runs to give you the results you want within your Focus application.
I suppose you have them both, else you would not be able to show/get anything out of it.

Dear experts,

It took a bit, but I have to collect all these information and as you probably know – it takes time.

I got the following information back:
FEX:

SQL DB2
SELECT
* FROM
LS77IDV.VLDW078U
WHERE ERSTMON = '200901' AND PHGR = 'LM5F';

TABLE
ON TABLE HOLD
END

-RUN

TABLE FILE HOLD
PRINT *
END

This statement goes directly to DB2 (see the trace data below). The results are going back to WebFocus and will be displayed there. (Back to my basic question: Is this entire problem related to WEBFOCUS or is it in DB2)

Here in SQL DB2 there are no WebFOCUS-Masterfiles!!

Below trace data out of OmegaMon:

At first the statement executed in SPUFI. As you can see we are trying to get out data from ‘200901’ and we are getting 3216 Rows back:

Dynamic SQL Call Text :
SELECT ERSTMON, ZBM_PHGR, PHGR, SKILLNAME, VNR, DG, BS, LB, ABTEILUNG FROM LS77IDV.VLDW078U WHERE ERSTMON = '200901' AND PHGR = 'LM5F'
Planname=DSNESPCS Connid=TSO Corrid=LS95PFO Authid=LS95PFO

Call Type Stm# Program Count InDB2 Time MRet Rws Pc Rws DM Rws RD
---------------- ----- -------- ----- ------------ ---- ------ ------ ------
PREPARE 134 DSNESM68 1 00:00.00044 0 0 0 0
OPEN CURSOR 208 DSNESM68 1 00:00.00001 0 0 0 0
FETCH 201 DSNESM68 3216 00:00.75245 100 8734 7864 3216 <========
CLOSE CURSOR 215 DSNESM68 1 00:00.00001 0 0 0 0


Here the same statement execeuted in WebFocus. And as you can see we getting nothing back.
Dynamic SQL Call Text :
-----------------------
SELECT ERSTMON, ZBM_PHGR, PHGR, SKILLNAME, VNR, DG, BS, LB, ABTEILUNG FROM
LS77IDV.VLDW078U WHERE ERSTMON = '200901' AND PHGR = 'LM5F' FOR FETCH ONLY;


Planname=DSNACLI Connid=DB2CALL Corrid=WFHUB27 Authid=LS95PFO

Call Type Stm# Program Count InDB2 Time MRet Rws Pc Rws DM Rws RD
---------------- ----- -------- ----- ------------ ---- ------ ------ ------
PREPARE 1481 DSNCLINF 1 00:00.00051 0 0 0 0
OPEN CURSOR 1245 DSNCLINF 1 00:00.00003 0 0 0 0
FETCH 1156 DSNCLINF 1 00:00.94521 100 10165 9296 0 <=======
CLOSE CURSOR 931 DSNCLINF 1 00:00.00002 0 0 0 0


Any suggestion or idea where I have to looking for?

Many thanks and best regards,

Siegfried

Siegfried,

As the core part of the select statements are identical then the only other place in which your problem could reside, is in the connection string. Make sure that you are directing the SQL to the correct location including plan etc. You should be able to get the info. on syntax etc. from the WF manual(s) for the DB2 interface.

T

You can test the DB2 connection from the data adaptors on the reporting console.

My perference is to use DSNTEP2 in a batch job to test my DB2 SQL. Both SPUFI and QMF have some user friendliness build it. This can prevents the SQL from behaving exactly like the WebFocus DB2 connection.

quote:

You can test the DB2 connection from the data adaptors on the reporting console.

My perference is to use DSNTEP2 in a batch job to test my DB2 SQL. Both SPUFI and QMF have some user friendliness build it. This can prevents the SQL from behaving exactly like the WebFocus DB2 connection.

This is actually a good idea. I will send this to my customer.

TIA

Siegfried