Records are there but giving Sqlcode 100 in Spufi

lalitha.rayala5 · Posted: Fri May 13, 2011 6:13 pm

Hi,

Can anyone please clarify my issue below.

I am running the below query in Spufi. In the output dataset 1 record is being displayed but showing Sqlcode 100. Please let me how this happens.

SELECT distinct
WIDTH
,SUFFIX_LGTH_REQ
,CMDTY_CODE_DESC_1
,CMDTY_CODE_DESC_2
,SUFFIX_REQUIRED
FROM
P.TA0001
WHERE
COMMODITY_CODE = '123519'
AND SUFFIX = '099'

output dataset:

---------+---------+---------+---------+---------+---------+---------+
WIDTH SUFFIX_LGTH_REQ CMDTY_CODE_DESC_1
---------+---------+---------+---------+---------+---------+---------+
.00000 N LEVELROCK 2500 UNDERLAY 2700# BG
.00000 N LEVELROCK 2500 UNDERLAY 2700# BG
DSNE610I NUMBER OF ROWS DISPLAYED IS 2
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

Thanks,
Lalitha Rayala.

enrico-sorichetti · Posted: Fri May 13, 2011 6:30 pm

it happens ( that You are wondering ) because You did not care to read the manuals/doc

even a simple googling with spufi sql code 100 will return a pointer which answers Your question properly

publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.apsg/xf4d5c.htm

daveporcelan · Posted: Fri May 13, 2011 6:38 pm

enrico-sorichetti · Posted: Fri May 13, 2011 6:48 pm

the sql code 100 is a SUCCESS SQL CODE, not an error one

it happens because SPUFI blindly display the sql code from the <last executed> command

a sql code 100 is like the EOF indicator ...
fetch, fetch, ... , fetch with sql code 100 ==> no more rows ( end of file / no more rows )

in COBOL ( plain file access )
read, read, .... , read and raise the EOF condition ( end of file / no more records )

the general logic is the same in bothe cases
the query/read was <successful> it's up to the program to determine if any rows were returned or if any records were read

daveporcelan · Posted: Fri May 13, 2011 6:51 pm

Thank you Enrico,

I could not see the forrest for the trees.

The rows were found and displayed: