Topic
  • 2 replies
  • Latest Post - ‏2009-06-02T08:25:36Z by SystemAdmin
SystemAdmin
SystemAdmin
6968 Posts

Pinned topic BIGINT mapping as a VARCHAR

‏2009-05-08T11:14:22Z |
Hi all,
I've read in DB2Everyplace info center (http://publib.boulder.ibm.com/infocenter/db2e/v9r1/index.jsp?topic=/com.ibm.db2e.doc/dbsap_b.html) that BIGINT mapping is defined as VARCHAR in DB2e but as BIGINT in Cloudscape (Derby).

We are syncing a table with a DB2 udb source BIGINT column that is being mapped as a VARCHAR(20) into the Derby local DB.

Looking inside the properties defined in the server, we found:

<AddProperty>
<Type>DatatypeMappings Generic Target:*</Type>
<Name>-5</Name>
<Value>12.20 VARCHAR</Value>
</AddProperty>

so we tried adding:

<AddProperty>
<Type>DatatypeMappings Generic Target:db2j</Type>
<Name>-5</Name>
<Value>-5 BIGINT</Value>
</AddProperty>

restarted servers but with no effect (still mapping to VARCHAR(20)).

Anyone knows what the problem could be?
Thanks in advance.
Updated on 2009-06-02T08:25:36Z at 2009-06-02T08:25:36Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    6968 Posts

    Re: BIGINT mapping as a VARCHAR

    ‏2009-05-12T15:03:39Z  
    Hi,
    As we only use Derby as local DB, we tried adding

    <AddProperty>
    <Type>DatatypeMappings Generic Target:*</Type>
    <Name>-5</Name>
    <Value>-5 BIGINT</Value>
    </AddProperty>

    through "dsyadminxml" utility, restarted servers and this time it worked.

    Don't know what the behaviour will be using DB2e as local DB...
    Regards
  • SystemAdmin
    SystemAdmin
    6968 Posts

    Re: BIGINT mapping as a VARCHAR

    ‏2009-06-02T08:25:36Z  
    Hi,
    As we only use Derby as local DB, we tried adding

    <AddProperty>
    <Type>DatatypeMappings Generic Target:*</Type>
    <Name>-5</Name>
    <Value>-5 BIGINT</Value>
    </AddProperty>

    through "dsyadminxml" utility, restarted servers and this time it worked.

    Don't know what the behaviour will be using DB2e as local DB...
    Regards
    Hi GuiGross,

    Are you using which version of DB2e?
    AS I know, APAR IC53100 describes this problem: "Some data mappings are missing for Derby/Cloudscape in DB2e 9.1 and they are added back in 9.1.1 release."

    If your DB2e version is 9.1, please upgrade it to 9.1.1 or later, or simply insert the following records into the control table in DSYCTLDB since you only use Derby:

    insert into dsy.properties(type,name,value) values('DatatypeMappings Generic Target:db2j','-1','-1 LONG VARCHAR');
    insert into dsy.properties(type,name,value) values('DatatypeMappings Generic Target:db2j','2','2 NUMERIC');
    insert into dsy.properties(type,name,value) values('DatatypeMappings Generic Target:db2j','6','6 FLOAT');
    insert into dsy.properties(type,name,value) values('DatatypeMappings Generic Target:db2j','7','7 REAL');
    insert into dsy.properties(type,name,value) values('DatatypeMappings Generic Target:db2j','8','8 DOUBLE PRECISION');

    insert into dsy.properties(type,name,value) values('DatatypeMappings Source to Target:db2j','COM.ibm.db2.jdbc.app.DB2Driver -1','-1 LONG VARCHAR');
    insert into dsy.properties(type,name,value) values('DatatypeMappings Source to Target:db2j','COM.ibm.db2.jdbc.app.DB2Driver -2','-2 CHAR() FOR BIT DATA');
    insert into dsy.properties(type,name,value) values('DatatypeMappings Source to Target:db2j','COM.ibm.db2.jdbc.app.DB2Driver -3','-3 VARCHAR() FOR BIT DATA');
    insert into dsy.properties(type,name,value) values('DatatypeMappings Source to Target:db2j','COM.ibm.db2.jdbc.app.DB2Driver -4','-4 LONG VARCHAR FOR BIT DATA');
    insert into dsy.properties(type,name,value) values('DatatypeMappings Source to Target:db2j','COM.ibm.db2.jdbc.app.DB2Driver -5','-5 BIGINT');

    insert into dsy.properties(type,name,value) values('DatatypeMappings Mirror to Target:db2j','COM.ibm.db2.jdbc.app.DB2Driver -1','-1 LONG VARCHAR');
    insert into dsy.properties(type,name,value) values('DatatypeMappings Mirror to Target:db2j','COM.ibm.db2.jdbc.app.DB2Driver -2','-2 CHAR() FOR BIT DATA');
    insert into dsy.properties(type,name,value) values('DatatypeMappings Mirror to Target:db2j','COM.ibm.db2.jdbc.app.DB2Driver -3','-3 VARCHAR() FOR BIT DATA ');
    insert into dsy.properties(type,name,value) values('DatatypeMappings Mirror to Target:db2j','COM.ibm.db2.jdbc.app.DB2Driver -4','-4 LONG VARCHAR FOR BIT DATA');
    insert into dsy.properties(type,name,value) values('DatatypeMappings Mirror to Target:db2j','COM.ibm.db2.jdbc.app.DB2Driver -5','-5 BIGINT');

    In addition, please don't change anything in DSYCTLDB manually unless you know the impact. So please also delete added record by xml tool:

    delete from dsy.properties where type='DatatypeMappings Generic Target:*' and name='-5' and value='-5 BIGINT'