Having a problem with db2 mapping

[rhartzog]

I am very new to trying to access data on a db2 sitting on an AS400
server. There is a column which shows in EZVIEW as data type "A" with
a length of 12. Using iNavigator and looking at the table definition
it shows as character 12. When I map this to a string property it
writes out as System.Byte[] so I am a bit confused. There is very
little in the way of documentation or examples for using NH with db2
so I am coming up short on finding any help with this. Any help with
how to map these data types or mapping to a db2 in general would be
greatly appreciated.

[MattO]

Welcome to DB2/AS400 hell, I'm in the same boat (sinking) as you. All
joking aside, here is what I would do to help trouble shoot the
issue. It may be a character code set translation issue (which your
DB2 driver must use)

1. Login to the AS400 and do a DSPFFD (display phsyical file
description) on the table in question, then copy and paste the results
of that column information. The info I'm looking for is fieldname,
data type, field length, and coded character set.
2. Copy and paste your connection string that you are using in here,
this is important as it controls how character sets are decoded.
3. Go into iSeries navigator and go to Databases>>your
database>>Schemas>>Library>>Tables, then find the table in question
and right click on it and click on "Generate SQL". Then hit the
"Generate" button. Copy the information from the creation script for
the field in question to this forum post.
4. List the version of AS400 you are running (V5R4, V6R1, etc) and
tell me the version of the IBM.DB2.Iseries .NET driver you are using
5. Also paste in your HBM XML mapping file so I can compare all the
information.

Once we have that we may be able to get to the bottom of whats going
on.

[rhartzog]

Thank you MattO, here is the information you have requested:

Key column:
Column: ABAN8
Data Type: Zoned
Field Length: 8 0 (yes a space is between those two numbers)
Code Character Set: null

No problems displaying information:
Column: ABALPH
Data Type: CHAR
Field Length: 40
Code Character Set: 37

Problems displaying information:
Column: ABALKY
Data Type: CHAR
Field Length: 20
Code Character Set: 65535

2) I took a screenshot of my hibernate.cfg file:
http://lh5.ggpht.com/_HHLbJSECQBs/TG1nAn5p8FI/AAAAAAAAATY/dqugRhiflhA/sessionFactory.gif

3) -- Generate SQL
-- Version: V5R4M0 060210
-- Generated on: 08/19/10 12:04:01
-- Relational Database: DWHOMES
-- Standards Option: DB2 UDB iSeries
CREATE TABLE TESTDATA.F0101 (
-- SQL150B 10 REUSEDLT(*NO) in table F0101 in TESTDATA ignored.
ABAN8 NUMERIC(8, 0) NOT NULL DEFAULT 0 ,
-- SQL150D 10 EDTCDE in column ABAN8 ignored.
ABALKY CHAR(20) CCSID 65535 NOT NULL DEFAULT '' ,
ABALPH CHAR(40) CCSID 37 NOT NULL DEFAULT '' , ...

4) Looks like V5R4 from item #3 above. I am using version 12.0.0.0 of
the IBM.Data.DB2.iSeries.dll

5) A screenshot of the mapping file. I have tried various definitions
with the problematic column. http://lh5.ggpht.com/_HHLbJSECQBs/TG1r1AGDT2I/AAAAAAAAATk/ceHT9vWi3_I/mapping.gif


I am not even sure my session factory is set up properly, so if
something looks wrong it probably is.

Thanks again,
Ryan

[MattO]

Ryan,

Okay, so even though you didn't tell me which column you are having
issues with I am going to assume it is the ABALKY column that has the
problem. I *BELIEVE* the issue is the code page of 65535. Really
that should probably be code page 37 (US English) like the other
fields, but someone may have accidently? made the wrong code page
value. Code page 65535 from what I can tell means that it should be
treated as binary data. Here is a snippet that leads me to believe
that:

*HEX: The CCSID 66535 is used, which indicates that the character data
in the fields is treated as bit data and is not converted.

This is from this page: http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/cl/crtsrcpf.htm

So as I see it you have two options.

1. Change the code page for that column to 37 like the other CHAR
columns (this would require potentially a lot of work, any RPG program
that is bound to that table or CHAIN to it would have to be recompiled
I assume, but I'm not an RPG master but this is one of those things I
despise the AS400 for).

2. Add a flag to your connection string to tell it how to convert
binary data. This is probably the simplest option. adding
TRANSLATE=1; to your connection string should fix the issue. (see the
third link below for why).

See here for more information:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzaii/rzaiiodbc53.htm
http://www.ibm.com/developerworks/data/library/techarticle/dm-0506chong/index.html

and finally the good part:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/rzaik/rzaikconnstrkeywordstranprop.htm

Isn't the AS400 FUN!!!! (I'm being very sarcastic here).

On Aug 19, 12:39 pm, rhartzog <rhart...@gmail.com> wrote:
> Thank you MattO, here is the information you have requested:
>
> Key column:
> Column: ABAN8
> Data Type: Zoned
> Field Length: 8 0 (yes a space is between those two numbers)
> Code Character Set: null
>
> No problems displaying information:
> Column: ABALPH
> Data Type: CHAR
> Field Length: 40
> Code Character Set: 37
>
> Problems displaying information:
> Column: ABALKY
> Data Type: CHAR
> Field Length: 20
> Code Character Set: 65535
>

> 2) I took a screenshot of my hibernate.cfg file:http://lh5.ggpht.com/_HHLbJSECQBs/TG1nAn5p8FI/AAAAAAAAATY/dqugRhiflhA...

>
> 3) --  Generate SQL
> --  Version:                    V5R4M0 060210
> --  Generated on:               08/19/10 12:04:01
> --  Relational Database:        DWHOMES
> --  Standards Option:           DB2 UDB iSeries
> CREATE TABLE TESTDATA.F0101 (
> --  SQL150B   10   REUSEDLT(*NO) in table F0101 in TESTDATA ignored.
>         ABAN8 NUMERIC(8, 0) NOT NULL DEFAULT 0 ,
> --  SQL150D   10   EDTCDE in column ABAN8 ignored.
>         ABALKY CHAR(20) CCSID 65535 NOT NULL DEFAULT '' ,
>         ABALPH CHAR(40) CCSID 37 NOT NULL DEFAULT '' , ...
>
> 4) Looks like V5R4 from item #3 above. I am using version 12.0.0.0 of
> the IBM.Data.DB2.iSeries.dll
>
> 5) A screenshot of the mapping file.  I have tried various definitions

> with the problematic column.http://lh5.ggpht.com/_HHLbJSECQBs/TG1r1AGDT2I/AAAAAAAAATk/ceHT9vWi3_I...

> > > greatly appreciated.- Hide quoted text -
>
> - Show quoted text -

[rhartzog]

It makes me want to slam my head in a car door a few times.

Alright, so that is some good information, but when I add TRANSLATE=1
or ForceTranslation = 1 into the connection string I get an invalid
property exception. Is my connection string properly formed as you
see it? I have to add the schema names to the mapping file as you can
see by my earlier screenshot.

Definitely can't change the table... errr... I mean file structure.
There are a ton of rpg programs running off of this.

Man this is annoying.

On Aug 19, 2:48 pm, MattO <xant...@gmail.com> wrote:
> Ryan,
>
> Okay, so even though you didn't tell me which column you are having
> issues with I am going to assume it is the ABALKY column that has the
> problem.  I *BELIEVE* the issue is the code page of 65535.  Really
> that should probably be code page 37 (US English) like the other
> fields, but someone may have accidently? made the wrong code page
> value.  Code page 65535 from what I can tell means that it should be
> treated as binary data.  Here is a snippet that leads me to believe
> that:
>
> *HEX: The CCSID 66535 is used, which indicates that the character data
> in the fields is treated as bit data and is not converted.
>

> This is from this page:http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/cl/c...

>
> So as I see it you have two options.
>
> 1.  Change the code page for that column to 37 like the other CHAR
> columns (this would require potentially a lot of work, any RPG program
> that is bound to that table or CHAIN to it would have to be recompiled
> I assume, but I'm not an RPG master but this is one of those things I
> despise the AS400 for).
>
> 2.  Add a flag to your connection string to tell it how to convert
> binary data.  This is probably the simplest option.  adding
> TRANSLATE=1; to your connection string should fix the issue.  (see the
> third link below for why).
>
> See here for more information:
>

> http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic...http://www.ibm.com/developerworks/data/library/techarticle/dm-0506cho...

>
> and finally the good part:
>

> http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic...

[MattO]

Okay, well the links to the keyword propertys I sent you apparently
are only for the ODBC driver. The .NET driver is completely different
(sigh, about ready to go kick that old mainframe).

<property
name="connection.connection_string">DataSource=servername;CharBitDataAsString=True;UserID=xxxxx;Password=xxxx;</
property>

The property is CharBitDataAsString should be set to TRUE, the default
is false.

If your wondering how the hell I found it, I actually opened up SSIS
(sql server integration services), and added a new ADO .NET source and
told it to use the IBM DB2 driver and it gives me a nice GUI of all
the propertys and their descriptions.

If you search for this on the internet you will find nothing because
the AS400 is ancient, and there is so much undocumented stuff
(particularly around the AS400) that it makes my head spin and roll my
eyes at IBM. I've wasted countless hours interacting with this beast
of a machine. Sorry, I'm very jaded at this point.

> >http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic......

> > > - Show quoted text -- Hide quoted text -

[rhartzog]

That was my first thought, "how the hell did he find this stuff, I've
googled it". I hadn't even thought about ssis.

I think the error is actually coming from nhibernate. I believe it's
parsing the connection string and making sure it's valid before
passing it on to the driver. I'm going to dig around in the source
code and try to find where this is happening.

On Aug 19, 4:29 pm, MattO <xant...@gmail.com> wrote:
> Okay, well the links to the keyword propertys I sent you apparently
> are only for the ODBC driver.  The .NET driver is completely different
> (sigh, about ready to go kick that old mainframe).
>
> <property

> name="connection.connection_string">DataSource=servername;CharBitDataAsStri ng=True;UserID=xxxxx;Password=xxxx;</

[MattO]

I don't think you need to examine the nHibernate source code. the
translate property doesn't exist on the IBM.DB2.Iseries .NET driver,
it only exists on the ODBC driver which is why you received the error
when setting Translate=1 (I also go the error).

After setting the CharBitDataAsString=TRUE value however nHibernate
did not complain.

To be honest, you might be better taking nHibernate completely out of
the picture and create a standard ADO .NET connection you will see the
same behaviour the nHibernate framework is erroring about as it's
really just a wrapper around ADO .NET.

[rhartzog]

Hmm, what version of Nhibernate are you using? I have 2.1.2.4000
here.

Thanks for sticking with me here by the way.

[MattO]

I'm using 2.1.2... However I don't have a table that has a 65535
encoding to test with. I just meant to say that I no longer got the
connection string error message after using the appropriate property
in the IBM .NET driver...

[rhartzog]

I don't know what I was doing last night, but it works this morning
after re-entering the connection string with
CharBitDataAsString=TRUE. Maybe I had a typo that I didn't see
because my eyes were crossing.

Thank you very much for the help MattO.

> ...
>
> read more »