Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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.

replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question