MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

EBCDIC, UNICODE Conversion Problem, SQL -873

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany
PostPosted: Tue Apr 05, 2016 10:21 am    Post subject: EBCDIC, UNICODE Conversion Problem, SQL -873 Reply with quote
Dear *,

I use DB2 zOS , V11 and I have a UNICODE / EBCDIC conversion problem.


First I have a database, tablespace and table. Everything is EBCDIC.


Code:


   CREATE DATABASE MyDatabase
     BUFFERPOOL BP0
     INDEXBP    BP0
     CCSID      EBCDIC
     STOGROUP   xxxxxxxxxxxxxxx
    ;



Code:


CREATE TABLESPACE   S0TEST
                 IN MyDatabase
                          CLOSE       NO
                          SEGSIZE     4
                                      ;




Code:


  CREATE TABLE    XXX      .TBTEST
  (
    FIELD1   CHAR(10)                       NOT NULL
  ) IN MyDatabase  .S0TEST                                              ;




So far, ok. Works.

For some reason the data is required in UNICODE and EBCDIC.

Thus a view is created like this to provide the data as UNICODE.


Code:


CREATE VIEW XXX.TBTESTU
   (FIELD1)
 AS SELECT
     CAST(FIELD1  AS GRAPHIC(10) CCSID UNICODE)
   FROM XXX.TBTEST ;




Select for this view works, no problem.

Update, Insert doesn't work.

Code:


  INSERT INTO XXX.TBTESTU (FIELD1)
          VALUES ('MY DATA');
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -150, ERROR:  THE OBJECT OF THE INSERT, DELETE, OR UPDATE
         STATEMENT IS A VIEW, SYSTEM-MAINTAINED MATERIALIZED QUERY TABLE, OR
         TRANSITION TABLE FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED
DSNT418I SQLSTATE   = 42807 SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNXOVC SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = -115 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'FFFFFF8D'  X'00000000'  X'00000000'  X'FFFFFFFF'
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+





Solution for Update, Insert: Instead of Trigger like this, but it is not possible to create this Trigger.



Code:


    CREATE TRIGGER  XXX.TBTESTU_T
      INSTEAD OF INSERT ON XXX.TBTESTU
      REFERENCING NEW AS NEWROW
      FOR EACH ROW MODE DB2SQL
        INSERT INTO XXX.TBTEST
          (
            FIELD1
          )
          VALUES (
            CAST(NEWROW.FIELD1 AS CHAR(10) CCSID EBCDIC)
          );
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -873, ERROR:  THE STATEMENT REFERENCED DATA ENCODED WITH
         DIFFERENT ENCODING SCHEMES OR CCSIDS IN AN INVALID CONTEXT
DSNT418I SQLSTATE   = 53090 SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNXICTR SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = 245 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'000000F5'  X'00000000'  X'00000000'  X'FFFFFFFF'
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION




Why ?
Any comments ?
How to provide the data of one table as EBCDIC data and UNICODE data ?


Functions EBCDIC_STR and UNICODE_STR instead of CAST doesn't work.

Remark: The Topic, that not all UNICODE chars (2 Byte) can be converted to EBCDIC (1 Byte) is of course well know, but not relevant for this question.

Thank you for all helpful comments.
bauer
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Apr 05, 2016 10:51 am    Post subject: Re: EBCDIC, UNICODE Conversion Problem, SQL -873 Reply with quote
bauer wrote:


Select for this view works, no problem.

Update, Insert doesn't work.

Solution for Update, Insert: Instead of Trigger like this, but it is not possible to create this Trigger.


Why ?
Any comments ?
How to provide the data of one table as EBCDIC data and UNICODE data ?


Functions EBCDIC_STR and UNICODE_STR instead of CAST doesn't work.

Remark: The Topic, that not all UNICODE chars (2 Byte) can be converted to EBCDIC (1 Byte) is of course well know, but not relevant for this question.

Thank you for all helpful comments.
bauer


Bauer,

Unless I am missing something why can't you do an INSERT on the base table itself instead of the View? View is merely snapshot of what you have in your base table.
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany
PostPosted: Tue Apr 05, 2016 11:07 am    Post subject: Reply with quote
kolusu,

thank you for your reply.

The base table is EBCDIC.

We have an environment which requires UNICODE (tables or views). The complete interface to the database should be UNICODE. So we created the UNICODE view.

If we would use the base table, the interface to the database wouldn't be UNICODE.

Hope you understand, what I mean.

bauer
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Apr 05, 2016 11:15 am    Post subject: Reply with quote
bauer wrote:
We have an environment which requires UNICODE (tables or views). The complete interface to the database should be UNICODE. So we created the UNICODE view.

If we would use the base table, the interface to the database wouldn't be UNICODE.

Hope you understand, what I mean.

bauer


I understand your need about having unicode view but you would always insert the data into base table but for looking at the data you would use the View which would convert the ebcdic data to unicode.

If you want to insert the unicode data then you just do the conversion on the base table

Code:

INSERT INTO XXX      .TBTEST (FIELD1)                 
          VALUES (UNICODE_STR('MY DATA',UTF16)) 
  ;   

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany
PostPosted: Tue Apr 05, 2016 1:33 pm    Post subject: Reply with quote
Well, not exactly what I want.

I have one base table, EBCDIC.

I like to access this table in System A, which deals with UNICODE.
I like to access this table in System B, which deals with EBCDIC.

In both systems I like to use views, with the goal, what changes in the base table (data model) are possible, just by changing the view definition. But without changing the coding.

Background is an migration project. System B shall be replaced step by step with functions from System A.

And additional existing EBCDIC tables shall we migrated step by step to UNICODE tables.

The functional migration shall be done independent from the data migration.
Back to top
View user's profile Send private message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany
PostPosted: Fri Apr 15, 2016 8:17 am    Post subject: Reply with quote
Just for information:

The answer from IBM is, that this is not possible. With DB2 you cann't do this. No Views are possible which deal with different CCSIDs.

Possible of course is:

- One UNICODE Table
- Access to this table from any UNICODE environment
- Access to this table from any EBCDIC Environment, data is transferred to EBCDIC, with correct CCSID, handled by DB2 (specification of correct CCSID during BIND PACKAGE process in case of PL/1, COBOL Destination of course is necessary)
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group