Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2006
    Posts
    11

    Post Fetch First in DB2 mainframe

    Hi,
    I need to execute 2 select clause seperated by a union clause. These two select should fetch only first 10 rows (in order to save the search time). I also want to sort them in speciifc order (desc). The query which i was trying on my mainframe terminal (SPUFI DB2) is given below

    (SELECT * FROM XXXXX FETCH FIRST 10 ROWS ONLY)
    UNION
    (SELECT * FROM XXXXX FETCH FIRST 10 ROWS ONLY)
    ORDER BY 1 DESC WITH UR;

    But this query is not working (giving an error). Can any body help to suggest any subsitute query for this

    (Note that the search time is very imp as there are lacs of records)

    Thanks for your time.

    Nouman Memon
    Patni computers

  2. #2
    Join Date
    Jan 2003
    Posts
    1,595
    Hi,
    The first 10 rows only can only be used at the and of the SQL statement.

    Which mainfraime system do you use: db2/vse, db2/vm, db2/zOs, db2/os-400, db2-linux390?

    Grofaty

  3. #3
    Join Date
    Mar 2005
    Posts
    7

    could you try this?

    It might be helpfull if you provide more information regarding the error (sqlcode?)

    Anyways, using union together with fetch first might give you unexpected results. Although i'm not sure on mainframe though...

    Could you try executing the folowing statements and share your results here?
    1.
    Code:
    values ('A',1),('A',1) 
    UNION
    values ('B',1),('B',2),('B',3) 
    UNION
    values ('A',1)
    UNION
    values ('A',2)
    UNION
    values ('A',2)
    UNION
    values ('C',1)
    should return 6 rows
    A 1
    B 1
    C 1
    A 2
    B 2
    B 3

    2.
    Code:
    (values ('A',1),('A',1) fetch first row only)
    UNION
    (values ('B',1),('B',2),('B',3) fetch first 2 rows only)
    UNION
    values ('A',1)
    UNION
    values ('A',2)
    UNION
    values ('A',2)
    UNION
    values ('C',1)
    order by 1 desc with ur
    as per your example also returns as expected (no errors on my system with this one)

    however
    3.
    Code:
    values ('A',1),('A',1)  
    UNION
    values ('B',1),('B',2),('B',3) 
    UNION
    values ('A',1)
    UNION
    values ('A',2)
    UNION
    values ('A',2)
    UNION
    values ('C',1)
    fetch first 3 rows only
    Does NOT return the expected 3 rows (but less)

    Furthermore, order by cannot be used together with fetch first
    4.
    Code:
    values ('C',1) fetch first row only
    order by 1 desc with ur
    will fail, whereas
    5.
    Code:
    (values ('C',1) fetch first row only)
    order by 1 desc with ur
    will execute fine

    So could it be, you misplaced a bracket somewhere?

    regards
    ihendr

  4. #4
    Join Date
    Oct 2005
    Posts
    109
    ihendr, grofaty is correct:
    it is really the platform-syntax difference:
    on LUW the FETCH FIRST is part of a subselect and thus allowed with UNION,
    an z/OS it is allowed only once at the end of a select statement.
    Juliane

  5. #5
    Join Date
    Feb 2006
    Posts
    11
    Hi,
    Is there any way of fetching only required no of records in z/OS for subselect queries seperated by a UNION.

    Thanks for prevoius replies

    Nouman Memon
    Patni Computers

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649
    what version of db2 are you running, v2 ????
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    The following approach should work (apart from the fact that UNION would remove duplicates, while here the second INSERT might fail):
    Code:
    CREATE TABLE MYTMP LIKE XXXXX;
    INSERT INTO MYTMP
       SELECT * FROM XXXXX FETCH FIRST 10 ROWS ONLY WITH UR;
    INSERT INTO MYTMP
       SELECT * FROM XXXXX FETCH FIRST 10 ROWS ONLY WITH UR;
    SELECT * FROM MYTMP ORDER BY 1 DESC;
    DROP TABLE MYTMP;
    Last edited by Peter.Vanroose; 02-20-06 at 06:04.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Jan 2003
    Posts
    1,595
    Hi,

    don't know if row_number function is supported on DB2/zOS, but try the following:

    SELECT COL1, COL2, COL3 FROM (
    SELECT ROW_NUMBER () OVER () AS COUNT, COL1, COL2, COL3 FROM XXX
    ) AS TEMP WHERE COUNT <=10
    UNION ALL
    SELECT...

    Hope this helps,
    Grofaty

  9. #9
    Join Date
    Jan 2003
    Posts
    1,595
    Quote Originally Posted by nouman
    Hi,
    Is there any way of fetching only required no of records in z/OS for subselect queries seperated by a UNION.

    Thanks for prevoius replies

    Nouman Memon
    Patni Computers
    Hi,
    on DB2/LUW fetch first n rows is supported beggining version 8.1. It was not supported on v7.2 and before.

    Thanks,
    Grofaty

Posting Permissions

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