As400 - Sqlcode -255

arammal · #1 (**permalink**) 03-11-09, 07:29

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:

Quote:

[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,

tonkuma · #2 (**permalink**) 03-11-09, 08:34

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.

arammal · #3 (**permalink**) 03-11-09, 09:09

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..

tonkuma · #4 (**permalink**) 03-11-09, 13:47

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.

tonkuma · #5 (**permalink**) 03-11-09, 18:14

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

Quote:

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

arammal · #6 (**permalink**) 03-12-09, 04:55

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.

tonkuma · #7 (**permalink**) 03-12-09, 18:22

This may be worth to try.

Code:

	WHERE EXISTS (  
		SELECT *
		FROM PNOM

Manual "SQL Reference" wrote:

Quote:

EXISTS Predicate
.....
.....
The values returned by the subselect are ignored.
Example
EXISTS (SELECT * FROM EMPLOYEE WHERE SALARY > 60000)

arammal · #8 (**permalink**) 03-13-09, 06:02

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  
)

tonkuma · #9 (**permalink**) 03-14-09, 02:09

Quote:

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')

Quote:

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).