Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2008
    Posts
    5

    Red face Unanswered: unicode to ebcidic

    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
    Last edited by Bren99; 12-16-08 at 10:18. Reason: spelling error

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126

    Re: Unicode to EBCDIC

    Quote Originally Posted by Bren99
    I have an old piece of COBOL code which looks for keywords in the text column of sysibm.sysstmt but now it's in unicode it cannot find the keywords.
    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.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Dec 2008
    Posts
    5
    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'

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Dec 2008
    Posts
    5
    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

Posting Permissions

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