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

[Former Member]

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.

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member]

Mohamed,

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

Darren

[Former Member]

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.