Hi Everyone.
We have recently upgraded to DB2 V8 on the Z/OS mainframe. during this the catalog is converted to unicode. I have an old piece of COBOL code which looks for key words in the text column of sysibm.sysstmt but now its in unicode can not find the keywords. Does anyone know of a way to convert this unicode string.

Thanks

The real problem is the fact that the "TEXT" column of sysibm.sysstmt is encoded with CCSID=65535 which means "do not convert". Try copying that data to a column with CCSID=1208 (i.e.: Unicode) and DB2 will do the "Unicode to EBCDIC" conversion for you.
Beware that old statements (from before v8) are still encoded as EBCDIC and should *not* be copied to a Unicode column! (It is exactly because of this that the column has CCSID=65535 and not CCSID=1208.)

Hi Peter

Thanks for your help with this.

I have used the SQL below to try to convert the column to EBCDIC
SELECT
CAST(CAST(TEXT AS VARCHAR(3500) CCSID 1208)
AS VARCHAR(3500) CCSID EBCDIC)
END
FROM SYSIBM.SYSSTMT
WHERE NAME = 'A002251'
AND EXPLAINABLE='Y'
;
the problem I have now is my result is full of SQL warnings as well as my results as (sorry for the amount of output) I now need to remove these DSNT warnings etc
---------+---------+---------+---------+---------+---------+---------+---------+
.......tDECLARE V001496-CUR CURSOR FOR SELECT * FROM V001496 WHERE CMPY_N = : H
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT418I SQLSTATE = 01517 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSN SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
DSNT417I SQLWARN0-5 = W,,,,, SQL WARNINGS
DSNT417I SQLWARN6-A = ,,W,, SQL WARNINGS
......gDECLARE V003012-CUR CURSOR FOR SELECT * FROM V003012 WHERE CMPY_GRP_N = :
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT418I SQLSTATE = 01517 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSN SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'

This is just SPUFI trying to signal you any non-zero SQLSTATE, although the SQLCODE is zero.
Try running that query in your COBOL program and you won't have any problems.
The reason for the SQLSTATE=01517 is the fact that the TEXT column has non-convertible characters (which is to be expected: e.g. any 0x00 is non-convertible). Since the output looks fine I wouldn't bother with this SQLSTATE.

Thanks Once again Peter
I didnt realise it was SPUFI just warning. this is great news i can pass this to the developers to try to re-run and fix the problem!!
Thanks again I owe you one at least
Cheers
Bren