Skip to Content
author's profile photo Former Member
Former Member

Data Federator XI 3.0 using DB2 VARCHAR FOR BIT DATA Column?

We have a column in a DB2 database that is defined as VARCHAR(16) FOR

BIT DATA.

We are using the suggested IBM JDBC driver, db2jcc.jar, against a DB2

OS/390 8.1.5 version database.

The Datasource column displays a data type of NULL, indicating the DF

does not understand or cannot handle this IBM data type.

We have two issues.

First, target tables are not able to return any columns, regardless if

we exclude columns defined as NULL as mentioned above. We see the

'Wait' animation for a very long time when we use the 'Target table

test tool' option. Selecting to display the count only, returns zero.

We are able to fetch and view non-NULL column data when using the

'Query tool' under the Datasource pane.

I also get the same result when using the 'My Query Tool' in Server

Administrator; a selection agains the sources returns data while

selecting from a target table returns no data. Also, a 'select

count(*)' returns zero.

The second issue is in mapping a relationship between two DB2 tables

where the join is between two columns of the above mentioned type

(NULL).

The error we get back when we use "Show Errors" is "The types

'NULL' (in 'S1.PLANNEDGOALID') and 'NULL' (in 'S2.PLANNDEDGOALID') are

not compatible.". When reviewing the relationship, a dashed red line

appears instead of a solid grey line between the two tables in the

"Table relationships and pre-filers" section of our mapping pane.

The following query returns an error via the Server Administrator

Query Tool; "Types 'NULL' and 'NULL' are not compatible for operator

'=' (Error code : 10248)".

-


select count(*)

from

(select s1.CASEID, s2.PLANNEDGOALID, s2.NAME, s2.PLANNEDGRPSTTYCD

from "/DF_CMS_ODS/sources/CMFSREPT/CMSPROD.PLANNEDGOAL" AS s1

,"/DF_CMS_ODS/sources/CMFSREPT/CMSPROD.PLANNEDGOAL" s2

where s1.PLANNEDGOALID = s2.PLANNEDGOALID)

-


Here are the properties settings in the Resource Connector Settings

for jdbc.db2.zSeries we are using.

capabilities: isjdbc=true;orderBy=false

driverLocation: drivers/db2jcc_license_cisuz.jar;drivers/db2jcc.jar

jdbcClass: com.ibm.db2.jcc.DB2Driver

sourceType: db2

supportsCatalog: no

urlTemplate: jdbc:db2://<hostname>[:<port>]/<databasename>

Here are the Connection parameters as defined for the datasource in DF

Designer.

Defined resource: jdbc.db2.zSeries

Jdbc connection URL: jdbc:db2://DB2D03:50000/CMFSREPT

Authentication: Use a specific database logon for all Data Federator

users.

User Name: x

Password: hidden

Login domain: -- Choose a defined login domain --

Supports Schema: checked

Schema: is empty

Prefix table names with schema name: checked

Supports catalog: unchecked

Prefix table names with the database name: unchecked

Table types: TABLE and VIEW

So, the following is the two questions we require answers for...

Is this a limitation of Data Federator?

Is there a work around short of changing the datatype in the database.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 17, 2008 at 05:07 PM

    Mokrane,

    Thanks for your time. We tried that suggestion and I specifically just tried your format of it and it does nothing for us.

    I've logged a request with support.

    Thanks again.

    Darren

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Darren,

      Mokrane suggestion is correct. But there is a missing space at step 7 (between "VARCHAR" and "()"). The value for castColumnType parameter should be "VARCHAR () FOR BIT DATA=VARCHAR", not "VARCHAR() FOR BIT DATA":

      7. Set its value to: VARCHAR () FOR BIT DATA=VARCHAR

      If you are using jdbc.db2.zSeries resource, you should select this resource at step 4.

      Regards,

      Mohamed

  • author's profile photo Former Member
    Former Member
    Posted on Oct 17, 2008 at 08:31 AM

    Hi Darren,

    The VARCHAR() FOR BIT DATA is a binary data type and Data Federator does not support binaries. But if in your case, it makes sense to map this column to a VARCHAR data type you can configure the DB2 connector to view this column as a VARCHAR.

    Your column can be mapped explicitly to a data type of your choice using a property: castColumnType.

    This property can be set updating the resource you selected when you registered you DB2 data source.

    If the resource is "jdbc.db2", then:

    1. Launch Data Federator Administrator

    2. Click on "Administration" tab

    3. Click on "Connector Settings"

    4. Select the right resource: "jdbc.db2"

    5. Click "Add a property"

    6. Select "castColumnType"

    7. Set its value to: VARCHAR() FOR BIT DATA=VARCHAR

    8. Click on Ok

    You should see this column as a VARCHAR.

    Regards,

    Mokrane

    PS: For the target table issue, we have forwarded your mail to the Data Federator Designer team.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 21, 2008 at 10:57 PM

    Mohamed,

    Thanks a bunch. The space was the issue and has allowed me to move forward.

    Darren

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.