Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Nov 16
    2006

    DB2 z/OS V8 NFM - UNICODE -- EBCDIC madness

    Roy Boxwell
    [Software Engineering GmbH]
    Hi all!

    I have found a *very* strange thing....perhaps I am just crazy but
    perhaps it is also a real problem...

    Here are the three queries and the output I get in V8 NFM (You also need a
    package or trigger that was pre-compiled with NEWFUN=YES to get this)

    ---------+---------+---------+---------+---------+---------+-------
    SELECT SUBSTR(STMT, 9 , 50)
    FROM SYSIBM.SYSPACKSTMT
    WHERE LOCATION = ' '
    AND COLLID = 'MVNXTEST'
    AND NAME = 'MDB2ACT'
    AND SUBSTR(STMT , 9 , 3) = 'SET'
    ;
    ---------+---------+---------+---------+---------+---------+-------

    ---------+---------+---------+---------+---------+---------+-------
    ëáè.+è â<á.< ëè^{ç +åá^íëáêñà.....íëáê............
    DSNE610I NUMBER OF ROWS DISPLAYED IS 1
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
    ---------+---------+---------+---------+---------+---------+-------
    SELECT SUBSTR(STMT, 9 , 50)
    ,CAST(SUBSTR(STMT, 9 , 50) AS CHAR(50) CCSID EBCDIC)
    FROM SYSIBM.SYSPACKSTMT
    WHERE LOCATION = ' '
    AND COLLID = 'MVNXTEST'
    AND NAME = 'MDB2ACT'
    AND SUBSTR(STMT , 9 , 3) = 'SET'
    ;
    ---------+---------+---------+---------+---------+---------+-------

    ---------+---------+---------+---------+---------+---------+-------
    DSNE610I NUMBER OF ROWS DISPLAYED IS 0
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
    SELECT SUBSTR(STMT, 9 , 50)
    ,CAST(SUBSTR(STMT, 9 , 50) AS CHAR(50) CCSID EBCDIC)
    FROM SYSIBM.SYSPACKSTMT
    WHERE LOCATION = ' '
    AND COLLID = 'MVNXTEST'
    AND NAME = 'MDB2ACT'
    AND SUBSTR(STMT , 9 , 3) = CAST('SET' AS CHAR(3) CCSID UNICODE)
    ;
    ---------+---------+---------+---------+---------+---------+------

    ---------+---------+---------+---------+---------+---------+------
    ëáè.+è â<á.< ëè^{ç +åá^íëáêñà.....íëáê............ ëáè.+è â<á.< ë
    DSNE610I NUMBER OF ROWS DISPLAYED IS 1


    The first query only has STMT and finds the one row with a SET as the
    first "keyword" --- OK it is correct
    The second query simply adds another column to the select and CASTs it as
    EBCDIC -- No row found ?!?!?!?!?!?!?!
    The third query then CASTs the where clause SET and it works again ?!?!?!?

    I am crazy or is DB2 V8 insane ???



    Roy Boxwell
    SOFTWARE ENGINEERING GMBH
    -Product Development-
    Robert-Stolz-Strasse 5
    40470 Duesseldorf/Germany
    Tel. +49 (0)211 96149-0
    Fax +49 (0)211 96149-35
    E-mail [login to unmask email]
    Homepage www.seg.de


    ---------------------------------------------------------------------------------
    Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
    Mike Vaughan
    [Principal Financial Group]
    I'm not sure I would say v8 is "insane", it just has an odd sense of humor... This particular column has also given me some headaches since converting to V8 as well. I haven't experienced what you are showing below, but I do have a theory on what might be going on. Just out of curiousity, could you try adding "POSSTR(STMT,CAST('SET' AS CHAR(3) CCSID UNICODE))" to the list of columns being selected? I'd be interested in seeing if the offset has magically shifted a couple positions or if it comes back with a 0.

    ________________________________

    From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Roy Boxwell
    Sent: Thursday, November 16, 2006 1:17 AM
    To: [login to unmask email]
    Subject: [DB2-L] DB2 z/OS V8 NFM - UNICODE -- EBCDIC madness



    Hi all!

    I have found a *very* strange thing....perhaps I am just crazy but perhaps it is also a real problem...

    Here are the three queries and the output I get in V8 NFM (You also need a package or trigger that was pre-compiled with NEWFUN=YES to get this)

    ---------+---------+---------+---------+---------+---------+-------
    SELECT SUBSTR(STMT, 9 , 50)
    FROM SYSIBM.SYSPACKSTMT
    WHERE LOCATION = ' '
    AND COLLID = 'MVNXTEST'
    AND NAME = 'MDB2ACT'
    AND SUBSTR(STMT , 9 , 3) = 'SET'
    ;
    ---------+---------+---------+---------+---------+---------+-------

    ---------+---------+---------+---------+---------+---------+-------
    ëáè.+è â<á.< ëè^{ç +åá^íëáêñà.....íëáê............
    DSNE610I NUMBER OF ROWS DISPLAYED IS 1
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
    ---------+---------+---------+---------+---------+---------+-------
    SELECT SUBSTR(STMT, 9 , 50)
    ,CAST(SUBSTR(STMT, 9 , 50) AS CHAR(50) CCSID EBCDIC)
    FROM SYSIBM.SYSPACKSTMT
    WHERE LOCATION = ' '
    AND COLLID = 'MVNXTEST'
    AND NAME = 'MDB2ACT'
    AND SUBSTR(STMT , 9 , 3) = 'SET'
    ;
    ---------+---------+---------+---------+---------+---------+-------

    ---------+---------+---------+---------+---------+---------+-------
    DSNE610I NUMBER OF ROWS DISPLAYED IS 0
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
    SELECT SUBSTR(STMT, 9 , 50)
    ,CAST(SUBSTR(STMT, 9 , 50) AS CHAR(50) CCSID EBCDIC)
    FROM SYSIBM.SYSPACKSTMT
    WHERE LOCATION = ' '
    AND COLLID = 'MVNXTEST'
    AND NAME = 'MDB2ACT'
    AND SUBSTR(STMT , 9 , 3) = CAST('SET' AS CHAR(3) CCSID UNICODE)
    ;
    ---------+---------+---------+---------+---------+---------+------

    ---------+---------+---------+---------+---------+---------+------
    ëáè.+è â<á.< ëè^{ç +åá^íëáêñà.....íëáê............ ëáè.+è â<á.< ë
    DSNE610I NUMBER OF ROWS DISPLAYED IS 1


    The first query only has STMT and finds the one row with a SET as the first "keyword" --- OK it is correct
    The second query simply adds another column to the select and CASTs it as EBCDIC -- No row found ?!?!?!?!?!?!?!
    The third query then CASTs the where clause SET and it works again ?!?!?!?

    I am crazy or is DB2 V8 insane ???



    Roy Boxwell
    SOFTWARE ENGINEERING GMBH
    -Product Development-
    Robert-Stolz-Strasse 5
    40470 Duesseldorf/Germany
    Tel. +49 (0)211 96149-0
    Fax +49 (0)211 96149-35
    E-mail [login to unmask email]
    Homepage www.seg.de
    --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


    -----Message Disclaimer-----

    This e-mail message is intended only for the use of the individual or
    entity to which it is addressed, and may contain information that is
    privileged, confidential and exempt from disclosure under applicable law.
    If you are not the intended recipient, any dissemination, distribution or
    copying of this communication is strictly prohibited. If you have
    received this communication in error, please notify us immediately by
    reply email to [login to unmask email] and delete or destroy all copies of
    the original message and attachments thereto. Email sent to or from the
    Principal Financial Group or any of its member companies may be retained
    as required by law or regulation.

    Nothing in this message is intended to constitute an Electronic signature
    for purposes of the Uniform Electronic Transactions Act (UETA) or the
    Electronic Signatures in Global and National Commerce Act ("E-Sign")
    unless a specific statement to the contrary is included in this message.

    While this communication may be used to promote or market a transaction
    or an idea that is discussed in the publication, it is intended to provide
    general information about the subject matter covered and is provided with
    the understanding that The Principal is not rendering legal, accounting,
    or tax advice. It is not a marketed opinion and may not be used to avoid
    penalties under the Internal Revenue Code. You should consult with
    appropriate counsel or other advisors on all matters pertaining to legal,
    tax, or accounting obligations and requirements.

    ---------------------------------------------------------------------------------
    Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
    Mike Bell
    Several issues here -
    1. SYSSTMT and SYSPACKSTMT are defined as for bit data - this requires cast
    to get translation. I have used
    ,CAST(CAST (B.STMT AS VARCHAR(3500) CCSID 1208)
    AS VARCHAR(3500) CCSID EBCDIC)
    but you really ought to start in pos 9 for a length of the value in pos 5-8.
    2. SYSSTMT and SYSPACKSTMT have embedded fields that are binary counters and
    offsets and stuff from the DBRM. so not all rows will process through the
    cast correctly.
    3. SYSSTMT and SYSPACKSTMT have a header row that comes from the DBRM header
    that is in EBCIDIC. STMTNO=0 AND SEQNO=0 AND SECTNO=0

    The result is that SPUFI is severely limited in processing these 2 tables.

    Mike

    -----Original Message-----
    From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
    Of Vaughan, Mike
    Sent: Thursday, November 16, 2006 11:31 AM
    To: [login to unmask email]
    Subject: Re: [DB2-L] DB2 z/OS V8 NFM - UNICODE -- EBCDIC madness

    I'm not sure I would say v8 is "insane", it just has an odd sense of
    humor... This particular column has also given me some headaches since
    converting to V8 as well. I haven't experienced what you are showing below,
    but I do have a theory on what might be going on. Just out of curiousity,
    could you try adding "POSSTR(STMT,CAST('SET' AS CHAR(3) CCSID UNICODE))" to
    the list of columns being selected? I'd be interested in seeing if the
    offset has magically shifted a couple positions or if it comes back with a
    0.

    ________________________________

    From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
    Of Roy Boxwell
    Sent: Thursday, November 16, 2006 1:17 AM
    To: [login to unmask email]
    Subject: [DB2-L] DB2 z/OS V8 NFM - UNICODE -- EBCDIC madness



    Hi all!

    I have found a *very* strange thing....perhaps I am just crazy but perhaps
    it is also a real problem...

    Here are the three queries and the output I get in V8 NFM (You also need a
    package or trigger that was pre-compiled with NEWFUN=YES to get this)

    ---------+---------+---------+---------+---------+---------+-------
    SELECT SUBSTR(STMT, 9 , 50)
    FROM SYSIBM.SYSPACKSTMT
    WHERE LOCATION = ' '
    AND COLLID = 'MVNXTEST'
    AND NAME = 'MDB2ACT'
    AND SUBSTR(STMT , 9 , 3) = 'SET'
    ;
    ---------+---------+---------+---------+---------+---------+-------

    ---------+---------+---------+---------+---------+---------+-------
    ëáè.+è â<á.< ëè^{ç +åá^íëáêñà.....íëáê............
    DSNE610I NUMBER OF ROWS DISPLAYED IS 1
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
    ---------+---------+---------+---------+---------+---------+-------
    SELECT SUBSTR(STMT, 9 , 50)
    ,CAST(SUBSTR(STMT, 9 , 50) AS CHAR(50) CCSID EBCDIC)
    FROM SYSIBM.SYSPACKSTMT
    WHERE LOCATION = ' '
    AND COLLID = 'MVNXTEST'
    AND NAME = 'MDB2ACT'
    AND SUBSTR(STMT , 9 , 3) = 'SET'
    ;
    ---------+---------+---------+---------+---------+---------+-------

    ---------+---------+---------+---------+---------+---------+-------
    DSNE610I NUMBER OF ROWS DISPLAYED IS 0
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
    SELECT SUBSTR(STMT, 9 , 50)
    ,CAST(SUBSTR(STMT, 9 , 50) AS CHAR(50) CCSID EBCDIC)
    FROM SYSIBM.SYSPACKSTMT
    WHERE LOCATION = ' '
    AND COLLID = 'MVNXTEST'
    AND NAME = 'MDB2ACT'
    AND SUBSTR(STMT , 9 , 3) = CAST('SET' AS CHAR(3) CCSID UNICODE)
    ;
    ---------+---------+---------+---------+---------+---------+------

    ---------+---------+---------+---------+---------+---------+------
    ëáè.+è â<á.< ëè^{ç +åá^íëáêñà.....íëáê............ ëáè.+è â<á.< ë
    DSNE610I NUMBER OF ROWS DISPLAYED IS 1


    The first query only has STMT and finds the one row with a SET as the first
    "keyword" --- OK it is correct
    The second query simply adds another column to the select and CASTs it as
    EBCDIC -- No row found ?!?!?!?!?!?!?!
    The third query then CASTs the where clause SET and it works again ?!?!?!?

    I am crazy or is DB2 V8 insane ???



    Roy Boxwell
    SOFTWARE ENGINEERING GMBH
    -Product Development-
    Robert-Stolz-Strasse 5
    40470 Duesseldorf/Germany
    Tel. +49 (0)211 96149-0
    Fax +49 (0)211 96149-35
    E-mail [login to unmask email]
    Homepage www.seg.de
    ----------------------------------------------------------------------------
    ----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
    home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
    select "Join or Leave the list". The IDUG DB2-L FAQ is at
    http://www.idugdb2-l.org. The IDUG List Admins can be reached at
    [login to unmask email] Find out the latest on IDUG conferences at
    http://conferences.idug.org/index.cfm


    -----Message Disclaimer-----

    This e-mail message is intended only for the use of the individual or
    entity to which it is addressed, and may contain information that is
    privileged, confidential and exempt from disclosure under applicable law.
    If you are not the intended recipient, any dissemination, distribution or
    copying of this communication is strictly prohibited. If you have
    received this communication in error, please notify us immediately by
    reply email to [login to unmask email] and delete or destroy all copies of
    the original message and attachments thereto. Email sent to or from the
    Principal Financial Group or any of its member companies may be retained
    as required by law or regulation.

    Nothing in this message is intended to constitute an Electronic signature
    for purposes of the Uniform Electronic Transactions Act (UETA) or the
    Electronic Signatures in Global and National Commerce Act ("E-Sign")
    unless a specific statement to the contrary is included in this message.

    While this communication may be used to promote or market a transaction
    or an idea that is discussed in the publication, it is intended to provide
    general information about the subject matter covered and is provided with
    the understanding that The Principal is not rendering legal, accounting,
    or tax advice. It is not a marketed opinion and may not be used to avoid
    penalties under the Internal Revenue Code. You should consult with
    appropriate counsel or other advisors on all matters pertaining to legal,
    tax, or accounting obligations and requirements.
    ----------------------------------------------------------------------------
    ----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
    home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
    select "Join or Leave the list". The IDUG DB2-L FAQ is at
    http://www.idugdb2-l.org. The IDUG List Admins can be reached at
    [login to unmask email] Find out the latest on IDUG conferences at
    http://conferences.idug.org/index.cfm


    ---
    Incoming mail is certified Virus Free.
    Checked by AVG anti-virus system (http://www.grisoft.com).
    Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003

    ---------------------------------------------------------------------------------
    Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact