Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2007
    Posts
    14

    Unanswered: As400 - Sqlcode -255

    Hellow every body,

    I am working on a AS400 machine with DB2 Database V5R2.
    When I tried to execute the query bellow, I got this error message:

    [SQL0255] DB2 Multisystem query error.
    SQL State: 42999
    Error Code: -255

    Code:
    SELECT TAB.* FROM ( 
    	SELECT NTLRP CONCAT NTLRE AS NOTIER, CDTYPT, CDTITR,  NMRSOC, NMPRST, CHAR(DSNAIS) CONCAT CHAR(DANAIS) CONCAT CHAR(DMNAIS) CONCAT CHAR(DJNAIS) AS DTNAIS 
    	FROM PTIP 
    	WHERE EXISTS (  
    		SELECT DISTINCT NOTIER   
    		FROM PNOM    
    		WHERE  PNOM.NOTIER = PTIP.NTLRP CONCAT PTIP.NTLRE   
         	          AND  PNOM.NMFICH = 1     
    		  AND  PNOM.NMPROP LIKE  'MA%')
    	ORDER BY NMRSOC 
    	FETCH FIRST 009 ROWS ONLY ) TAB  
    EXCEPT DISTINCT 
    SELECT TAB.* FROM ( 
    	SELECT NTLRP CONCAT NTLRE AS NOTIER, CDTYPT, CDTITR,  NMRSOC, NMPRST, CHAR(DSNAIS) CONCAT CHAR(DANAIS) CONCAT CHAR(DMNAIS) CONCAT CHAR(DJNAIS) AS DTNAIS 
    	FROM PTIP 
    	WHERE EXISTS (  
    		SELECT DISTINCT NOTIER   
    		FROM  PNOM    
    		WHERE PNOM.NOTIER = PTIP.NTLRP CONCAT PTIP.NTLRE     
    		  AND  PNOM.NMFICH = 1     
    		  AND  PNOM.NMPROP LIKE  'MA%') 
    	ORDER BY NMRSOC 
    	FETCH FIRST 004 ROWS ONLY ) TAB
    Can anyone, please, explain to me what such error message mean??

    thank you,

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I looked into SQL Reference V5R2, V5R3, V5R4 and V6R1 PDF manuals.

    EXCEPT(and EXCEPT DISTINCT, INTERSECT, INTERSECT DISTINCT) was supported from DB2 Universal Database for iSeries SQL Reference Version 5 Release 3.

    "ORDER BY" and "FETCH FIRST n ROWS ONLY" in subselect were supported from V6R1.
    Last edited by tonkuma; 03-11-09 at 09:40.

  3. #3
    Join Date
    Mar 2007
    Posts
    14
    No, "Except, Intersect, Fetch, Order By" exist on V5R2..
    The one thing that i could not found on V5R2 was ROW_NUMBER() OVER() .. that's why i used this algo to get special Rows from database..this algo lets me do pagination easly.. I tried to use UDF counter instead of ROW_NUMBER() but it did not work as it should, because i don't have unique keys in my tables..

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How did you confirmed that 'No, "Except, Intersect, Fetch, Order By" exist on V5R2..' ?

    I wrote that I looked into SQL Reference V5R2, V5R3, V5R4 and V6R1 PDF manuals.
    It implies that I didn't checked on real AS/400 environment.
    You must be right.
    I have no word to say further more, because I have no AS/400 or iSeries environment to check your comments.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you received the reason code in the error message,
    it would give you more specific cause of error.

    IBM Systems - iSeriesDatabase SQL messages and codes Version 5 Release 4
    SQL0255
    Message Text: Function not supported for query.
    Cause Text: The reason code is &1:
    v 1 - Scalar subselects and lateral correlation from a nested table expression are not allowed with distributed files
    v 2 - Error occurred while using a temporary distributed file.
    v 3 - EXCEPT or INTERSECT not supported for this query.
    v 4 - A sequence reference is not supported with distributed files.
    v 5 - A recursive common table expression is not supported for this query.
    v 6 - An OLAP function is not supported for this query.
    Recovery Text: A list of corrective actions follow:
    v If code 1, change the query so it does not use scalar subselects or correlation from a nested table expression.
    v If code 2, see the previous messages for more information.
    v If code 3, remove EXCEPT or INTERSECT from the query.
    v If code 4, remove the sequence reference from the query.
    v If code 5, remove the recursive common table expression from the query.
    v If code 6, remove the OLAP function from the query.
    SQLCODE or SQLCODEs: -255
    SQLSTATE or SQLSTATEs: 42999

  6. #6
    Join Date
    Mar 2007
    Posts
    14
    thank you tonkuma for your replies..
    in fact, i found that by replacing the LIKE 'MA%' with = 'MA', the querry works, it does not return the rows i need but at least it works.. do you, or anybody, have an idea why my DB2 doesn't like the LIKE.. do you think there is a method that i can use to make my LIKE 'MA%' without using it.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This may be worth to try.
    Code:
    	WHERE EXISTS (  
    		SELECT *
    		FROM PNOM
    Manual "SQL Reference" wrote:
    EXISTS Predicate
    .....
    .....
    The values returned by the subselect are ignored.
    Example
    EXISTS (SELECT * FROM EMPLOYEE WHERE SALARY > 60000)

  8. #8
    Join Date
    Mar 2007
    Posts
    14
    Even with the query below it doesn't work; it gives me the same Error instead of 0 rows. (I tested it on V5R4 and V6R1 and it works well)

    Code:
    SELECT * FROM PNOM WHERE NMPROP LIKE 'MA%'
        EXEPT DISTINCT
    SELECT * FROM PNOM WHERE NMPROP LIKE 'MA%'
    I found many reasons of this error on the net. below one of the test that i made (I found it on the net):
    Create and Update a New Version of QAQQINI
    Code:
    CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(COBBG) DATA(*NO) TRG(*YES)
    Code:
    INSERT INTO COBBG/QAQQINI VALUES('IGNORE_DERIVED_INDEX', '*YES', NULL)
    Create the Program to Be Called by Exit Point
    Code:
    PGM        PARM(&CURUSER)                     
    
    DCL        VAR(&CURUSER) TYPE(*CHAR) LEN(10)
    DCL        VAR(&FORUSER) TYPE(*CHAR) LEN(10)
                                      
    RTVDTAARA  DTAARA(COBBG/CLIEXITDA (1 10)) RTNVAR(&FORUSER)
    IF         COND((&FORUSER = '*ALL') *OR (&FORUSER = +
                 &CURUSER)) THEN(CHGQRYA QRYOPTLIB(COBBG))
    
     
    ENDPGM
    Code:
    CRTDTAARA DTAARA(CLIEXITDA) TYPE(*CHAR) LEN(10) VALUE(*ALL)
    Activate the Exit Point
    Code:
    ADDEXITPGM EXITPNT(QIBM_QSQ_CLI_CONNECT) FORMAT(CLIC0100) PGMNBR(1) PGM(COBBG/CLIEXIT)
    At this point, i tested the query and i got the same Error,
    so
    I Activate the copied QAQQINI
    Code:
    CHGQRYA JOB(USER/QPADEV001P) QRYOPTLIB(COBBG)
    and i tested but always the same error..

    the only solution i found is to change completely the algo i use to paginate.. my new querry is this one and it works well

    Code:
    SELECT TAB.* FROM ( 
    	SELECT NTLRP CONCAT NTLRE AS NOTIER, CDTYPT, CDTITR,  NMRSOC, NMPRST, CHAR(DSNAIS) CONCAT CHAR(DANAIS) CONCAT CHAR(DMNAIS) CONCAT CHAR(DJNAIS) AS DTNAIS 
    	FROM PTIP 
    	WHERE EXISTS (  
    		SELECT DISTINCT NOTIER   
    		FROM PNOM    
    		WHERE  PNOM.NOTIER = PTIP.NTLRP CONCAT PTIP.NTLRE   
         	  AND  PNOM.NMFICH = 1     
    		  AND  PNOM.NMPROP LIKE  'MA%')
    	ORDER BY NMRSOC 
    	FETCH FIRST 009 ROWS ONLY ) TAB  
    	
    WHERE NODENAME(TAB) CONCAT RRN(TAB) NOT IN
    (
    SELECT DISTINCT NODENAME(TAB) CONCAT RRN(TAB) FROM ( 
    	SELECT NTLRP CONCAT NTLRE AS NOTIER
    	FROM PTIP 
    	WHERE EXISTS (  
    		SELECT DISTINCT NOTIER   
    		FROM  PNOM    
    		WHERE PNOM.NOTIER = PTIP.NTLRP CONCAT PTIP.NTLRE     
    		  AND PNOM.NMFICH = 1     
    		  AND PNOM.NMPROP LIKE  'MA%') 
    	ORDER BY NMRSOC 
    	FETCH FIRST 004 ROWS ONLY ) TAB  
    )

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    in fact, i found that by replacing the LIKE 'MA%' with = 'MA', the querry works, it does not return the rows i need but at least it works..
    What query did you tested?
    Is it same in original post? (except replacing the LIKE 'MA%' with = 'MA')

    Even with the query below it doesn't work; it gives me the same Error instead of 0 rows. (I tested it on V5R4 and V6R1 and it works well)
    Code:
    SELECT * FROM PNOM WHERE NMPROP LIKE 'MA%'
        EXEPT DISTINCT
    SELECT * FROM PNOM WHERE NMPROP LIKE 'MA%'
    How about this on V5R2 and V5R4?
    Code:
    SELECT * FROM PNOM WHERE NMPROP = 'MA'
        EXCEPT DISTINCT
    SELECT * FROM PNOM WHERE NMPROP = 'MA'
    If it worked, then LIKE may be the issue.
    If it didn't worked on V5R2 and worked on V5R4, then V5R2 doesn't support EXCEPT(as described in SQL Reference).
    Last edited by tonkuma; 03-14-09 at 03:13.

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    If it's indeed the LIKE causing trouble, replace it with
    Code:
    WHERE SUBSTR(NMPROP,1,2) = 'MA'
    If on the other hand it's the EXCEPT DISTINCT which is not yet supported, replace it with
    Code:
    WHERE NOT EXISTS
    and then of course add a correlation condition in the subquery.
    Last edited by Peter.Vanroose; 03-14-09 at 14:09.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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