Hi,

My requirement is that our project is to be converted from Mainframes COBOL to AIX COBOL. But in db2 programs where the data is picked is coming in a different order compared to data picked between Mainframes and AIX.
Because of this i am getting mismatch in the o/p files.

In Mainframes for a db2 query with multiple rows satisfying we are not using CURSOR and in this case -811 error comes and even though we are picking the data and in Mainframes it picks the 2nd row satisfying it but in AIX DB2 it is not picking any rows. So in AIX DB2 also we want to pick the same row picked in Mainframes and in this way we tried several ways but not able to match the Mainframe results.

Can anybody suggest me how to get the solution for this?

Thx,
Satish

Not clear! Please give an example or if possible, the same code and query that is not 'working'

What version of DB2 are you using ?

Version of AIX DB2 using :- DB2 Client 9.5.4

Query executed :-
SELECT DISTINCT
ROWNUMBER() OVER() AS RN1,
COMM_PROV_ID
FROM (SELECT
COMM_PROV_ID,
ROWNUMBER() OVER() AS RN
FROM DSNT.DWS_MTH_PROVIDER
WHERE PROV_NUMBER LIKE '382205859%' AND
NAME_SORT LIKE 'STERLINGAR%' AND
PROV_STATE = 'MI' AND
SOURCE_CODE = 'UB'
order by COMM_PROV_ID DESC) T where RN = 2

Result got in AIX DB2 :-
RN1 COMM_PROV_ID
-------------------- ------------
1 78290

Result got in Mainframe DB2 QMF :-
COMM
PROV
RN1 ID
----------- -----------
1 78291

---------------------------------------------------------

I am using the above query in the program and moving the result into
output file and also writing to report. So because of the above mismatch the
Mainframe o/p file and AIX o/p file are not matching.

Kindly let me know for any further clarifications.

Thx,
Satish

You should use ORDER BY to ensure consistent ordering of records.

Even though i tried using ORDER BY but it is not picking the same records in Mainframes and AIX.

Can you please suggest me any ways?

Thx,
Satish

The order should be in here :

ROWNUMBER() OVER() AS RN

Only then, your RN will be based on a specific order of the ciolumn

I tried by giving as you suggested but even then it is not working :-

One more example :-

SELECT
PROV_SPECIAL
FROM (SELECT
PROV_SPECIAL,
ROWNUMBER() OVER() AS RN
FROM DSNW.DWS_PROVIDER_SPEC
WHERE US_AMIS_PROV_SPEC = ' ')
WHERE RN = 2
order by PROV_SPECIAL DESC

AIX DB2 o/p :-

PROV_SPECIAL
------------
PE

Mainframe o/p :-

PROV
SPECIAL
-------
A0

Why i am picking Row number = 2 because as part of migration from Mainframe to AIX,
in Mainframes if any -811 error comes it picks the second row, but in AIX it doesnt pick any row so i am giving as Row number = 2 in AIX to give same results as in Mainframes, but the results are not matching.

Please guide me further with any ideas.

Thx,
Satish

Once again, rows in a database do NOT have any inherent order, so any record can have the row number equal 2. To ensure consistent ordering you must use ORDER BY in the OLAP window specification.

Satish K, This is just a guess, but the Mainframe is probably EBCIDIC and AIX is probably ASCII. The different code sets could be causing your sorting problems.

Satish K, Another possibility. ORDER BY will ensure the order of a unique set of column values. If the column values you are sorting are not unique, the order of these duplicate rows can not be guaranteed. If nothing changes, the rows will probably come back in the same order for the same query. But if the rows are moved to different pages in the table space (Delete / Insert, Reorg, etc.) they may (or may not) be returned in the same order.

If possible, can you try adding another column to the ORDER BY that will make the columns ordered Unique with your ROW_NUMBER() OLAP function?

I guessed that Satish K doesn't know "ORDER BY in the OLAP window specification" which all three(sathyaram_s, Nick Ivanov and Stealth DBA) suggested.

And, result must be one row only. So, DISTINCT and "ROWNUMBER() OVER() AS RN1" must be unnecessary.

The query would be like this:

SELECT -- DISTINCT
/* ROWNUMBER() OVER() AS RN1 */ 1,
COMM_PROV_ID
FROM (SELECT
COMM_PROV_ID,
ROWNUMBER() OVER(order by COMM_PROV_ID DESC) AS RN
FROM DSNT.DWS_MTH_PROVIDER
WHERE PROV_NUMBER LIKE '382205859%' AND
NAME_SORT LIKE 'STERLINGAR%' AND
PROV_STATE = 'MI' AND
SOURCE_CODE = 'UB'
/* order by COMM_PROV_ID DESC */) T where RN = 2

Even i tried many ways it is showing the same :-

The mode of retreival in Mainframes DB2 is EBCDIC and in AIX is ASCII, so how can i make changes in my query to make the mode of retreival as EBCDIC while executing in AIX.

Example for mismatch :-
SELECT PROV_SPECIAL FROM DSNW.DWS_PROVIDER_SPEC
WHERE US_AMIS_PROV_SPEC = ' '
FETCH FIRST 10 ROWS ONLY

In Mainframes showing as :-

PROV
SPECIAL
-------
AL
A0
A0
A0
A0
A0
A0
A0
A0
A0

In AIX showing as :-

PROV_SPECIAL
------------
02
02
03
03
03
03
03
04
04
04

Kindly guide me further with your valuable suggestions.

Thx,
Satish

If the column has only upper alpahbet and numeric digit, then try following for ASCII mode:
If the column includes other characters, you can add these characters.

The same query got executed successfully in mainframes and not able to get executed in AIX :-


AIX-PROD ==>db2 "SELECT PROV_SPECIAL FROM DSNW.DWS_PROVIDER_SPEC
> WHERE US_AMIS_PROV_SPEC = ' '
> ORDER BY TRANSLATE(PROV_SPECIAL, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789') DESC
> FETCH FIRST 10 ROWS ONLY
> "SQL0440N No authorized routine named "TRANSLATE" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

Satish K, you should only have to Translate it at one location. If you can Translate the z/OS output to ASCII, the AIX data is already in ASCII.

By the way, if you have z/OS V9, you can look at the functions ASCII_STR and EBCDIC_STR for conversion.