Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2009
    Posts
    7

    Unanswered: Order of picking data mismatch in Mainframes DB2 and AIX DB2

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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 ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Nov 2009
    Posts
    7
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You should use ORDER BY to ensure consistent ordering of records.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Nov 2009
    Posts
    7
    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

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The order should be in here :

    ROWNUMBER() OVER() AS RN

    Only then, your RN will be based on a specific order of the ciolumn
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Nov 2009
    Posts
    7
    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

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Satish K View Post
    Why i am picking Row number = 2
    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.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  10. #10
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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?

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  12. #12
    Join Date
    Nov 2009
    Posts
    7
    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

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If the column has only upper alpahbet and numeric digit, then try following for ASCII mode:
    Code:
     ORDER BY
           TRANSLATE( PROV_SPECIAL
                    , '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' -- ASCII sequence
                    , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' -- EBCDIC sequence
                    ) DESC
    If the column includes other characters, you can add these characters.
    Last edited by tonkuma; 11-06-09 at 11:11.

  14. #14
    Join Date
    Nov 2009
    Posts
    7
    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

  15. #15
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •