DB2 and decimals

[system]

Hi,

I made a very simple Dataflow:

Version 1:
Source -> table A on DB2 UDB, F1 as integer and F2 as decimal (15,2).
Transform -> Query, mapping all fields, and “F1 = 1” in the where condition.
Target -> table B on DB2, F1 as integer and F2 as decimal (15,2).

this runs OK, the SQL generated is something like “INSERT INTO … SELECT”, all the work is made by DB2

Version 2:
Source -> table A on DB2 UDB, F1 as integer and F2 as decimal (15,2).
Transform1 -> Query on Table A, mapping all fields, and “F1 = 1” in the where condition.
Transform2 -> Query on Table A, mapping all fields, and “F1 = 2” in the where condition.
Transform3 -> Merge of Transform1 and Transform2
Target -> table B on DB2, F1 as integer and F2 as decimal (15,2).

this fails!
Problem … the SQL generated for every row is like INSERT INTO (F1,F2) 1,12345,13
and DB2 says that is incorrect because 3 params are submited for 2 fields
the decimal separator seems to be , in the insert statement.

We are using a datastore DB2 UDB 8x … and Data Integrator has no options to change in the DB2 datastore

Any idea?

---

aesteve (BOB member since 2008-11-17)

[system]

Are there regional settings (that determine what the decimal and thousands-separators are) in the DB/2 client configuration?

---

dnewton (BOB member since 2004-01-30)

[system]

I think that regional settings are not important, beacuse the DF reads and writes in the same DB with the same client.

I understand that if the “.” is selected as decimal separator, it’s the same separator for selects and inserts in this DB.

When I open the source table to “view data” inside DI I can read all decimals ok, then DI knows the character used as decimal separator.

But why inserts fails?

I’ve created another datastore to the same DB2 but using the ODBC driver option. With this option I can choose the decimal separator, with “.” returns the same error as DB2 driver, and using “,” when I open the table for view data, all the values are shown incorrectly (without decimals).

There is an option in the ODBC parameters that solve this …
The option is “Parameterized SQL”. If I choose NO the inserts success!, but every insert is a full sql insert sentence … like insert into (f1,f2) values (x,y) … with a low perfomance.

thanks for your attention.

---

aesteve (BOB member since 2008-11-17)

[system]

regional setting will matter for decimal data
what is your OS regional setting ? and what is the locale value that is set in %LINK_DIR%\bin\DSConfig.txt file under al_engine section ?

The push down SQL works since the select from source and insert into target is done on the DB2 server side client is only submitting the SQL statement its not doing anything with the data

In case of parameterised SQL or complete Insert statement with value, the data is processed using client ODBC API calls, what is the value of environment varaible DB2CODEPAGE ?

have you tried selecting data from DB2 command line/Control Center from the same machine where DI Desginer/jobserver is installed ?

---

manoj_d (BOB member since 2009-01-02)

[system]

@aesteve

When I open the source table to “view data” inside DI I can read all decimals ok, then DI knows the character used as decimal separator.

“View Data” uses the database driver on your client PC. This is different than the database client installed on the job server, and used at execution-time. (My point is, just because it looks OK in View Data does not guarantee it’s correct elsewhere.)

---

dnewton (BOB member since 2004-01-30)

[system]

Hello!, thanks for your replies.

I’m working with aesteve in this project.

Job Server:

OS regional settings:
Decimal separator = ,

DSConfig:
AL_ENGINE = ENG_ES.CP1252
DECIMAL_SEPARATOR = ,

Datastore config:
LANGUAGE=ESP or

DB2 CLIENT:
DB2COUNTRY or DB2CODEPAGE is not set

The error returned by DB2 on INSERT is:
[IBM][CLI Driver] CLI0112E Error assignment

Running DI Designer at Job Server the option VIEW DATA in the table shows decimals ok.
Runnig a select with DB2 Client at Job Server, the query returns decimals as ,

thanks

---

xavibo (BOB member since 2007-06-11)

[system]

what is the datastore type that you are using DB2 or ODBC ?
datastore code page ?
DI version ?

can you attach the ATL for the job ? I can get all these info from that

in the trace you see the complete INSERT statement or INSERT statement for Parameterised SQL like INSERT INTO TABLE_NAME (Col, col) VALUES ( ? , ? )

---

manoj_d (BOB member since 2009-01-02)

[system]

I can’t access to the server now, but I remember some info:

Datastore TYPE: DB2 8.x (windows)
Datastore CODEPAGE:
Datastore LANGUAGE: or spanish

DI Version: I think 11.7.x, i don’t remember exactly, is the lastest version with BOXI R2.

In trace I can see
INSERT INTO TABLE_NAME (Col, col) VALUES ( ? , ? )
and after this something like
inserting row values (XXXXX, 12345,12)

Some test I’ve made:

• Using BulkLoader, data is inserted ok!

• If I try with a new DS, pointing to same DB2 database, but with DS TYPE: ODBC and turning off the “Parameterized SQL” option … data is inserted ok!

again, thanks for your support

---

xavibo (BOB member since 2007-06-11)

[system]

have you tried setting the locale to default in DSConfig.txt

[Locales]
AL_Engine=_.

---

manoj_d (BOB member since 2009-01-02)

[system]

@manoj_d

have you tried setting the locale to default in DSConfig.txt

[Locales]
AL_Engine=_.

same result.

This is the trace returned by DB2

SQLPrepare( hStmt=2:4, pszSqlStr="INSERT INTO "T1108C"."TEST_PRE1_QT" ("BS0100_CODENTID", "BS0200_CODCENTRO", "KM1600_CODCONGE", "KM0000_CODPERIODO", "19_KM1600_IMP", "19_LASTF") VALUES (?, ?, ?, ?, ?, ?) ", cbSqlStr=171 )
    ---> Time elapsed - +5,400000E-005 seconds
( StmtOut="INSERT INTO "T1108C"."TEST_PRE1_QT" ("BS0100_CODENTID", "BS0200_CODCENTRO", "KM1600_CODCONGE", "KM0000_CODPERIODO", "19_KM1600_IMP", "19_LASTF") VALUES (?, ?, ?, ?, ?, ?)" )

SQLPrepare( )
    <--- SQL_SUCCESS   Time elapsed - +4,060000E-004 seconds

SQLBindParameter( hStmt=2:4, iPar=1, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_CHAR, fSQLType=SQL_VARCHAR, cbColDef=5, ibScale=0, rgbValue=&amp;0434a008, cbValueMax=5, pcbValue=&amp;0432f270 )
    ---> Time elapsed - +1,090000E-004 seconds

SQLBindParameter( )
    <--- SQL_SUCCESS   Time elapsed - +4,930000E-004 seconds

SQLBindParameter( hStmt=2:4, iPar=2, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_CHAR, fSQLType=SQL_VARCHAR, cbColDef=9, ibScale=0, rgbValue=&amp;0434b398, cbValueMax=9, pcbValue=&amp;04346e58 )
    ---> Time elapsed - +4,500000E-005 seconds

SQLBindParameter( )
    <--- SQL_SUCCESS   Time elapsed - +4,910000E-004 seconds

SQLBindParameter( hStmt=2:4, iPar=3, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_CHAR, fSQLType=SQL_VARCHAR, cbColDef=7, ibScale=0, rgbValue=&amp;0434d6c8, cbValueMax=7, pcbValue=&amp;04343cb0 )
    ---> Time elapsed - +4,500000E-005 seconds

SQLBindParameter( )
    <--- SQL_SUCCESS   Time elapsed - +4,820000E-004 seconds

SQLBindParameter( hStmt=2:4, iPar=4, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_CHAR, fSQLType=SQL_VARCHAR, cbColDef=13, ibScale=0, rgbValue=&amp;0434f228, cbValueMax=13, pcbValue=&amp;04340c48 )
    ---> Time elapsed - +4,500000E-005 seconds

SQLBindParameter( )
    <--- SQL_SUCCESS   Time elapsed - +4,970000E-004 seconds

SQLBindParameter( hStmt=2:4, iPar=5, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_CHAR, fSQLType=SQL_DECIMAL, cbColDef=13, ibScale=2, rgbValue=&amp;043524f8, cbValueMax=42, pcbValue=&amp;0433dbe8 )
    ---> Time elapsed - +4,500000E-005 seconds

SQLBindParameter( )
    <--- SQL_SUCCESS   Time elapsed - +4,760000E-004 seconds

SQLBindParameter( hStmt=2:4, iPar=6, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_CHAR, fSQLType=SQL_VARCHAR, cbColDef=9, ibScale=0, rgbValue=&amp;0435c910, cbValueMax=9, pcbValue=&amp;0433abc0 )
    ---> Time elapsed - +5,000000E-005 seconds

SQLBindParameter( )
    <--- SQL_SUCCESS   Time elapsed - +4,870000E-004 seconds

SQLSetStmtAttr( hStmt=2:4, fOption=SQL_ATTR_PARAMSET_SIZE, pvParam=&amp;00000002, uiStrLen=0 )
    ---> Time elapsed - +8,800000E-005 seconds

SQLSetStmtAttr( )
    <--- SQL_SUCCESS   Time elapsed - +2,760000E-004 seconds

SQLFreeStmt( hStmt=2:4, fOption=SQL_CLOSE )
    ---> Time elapsed - +4,400000E-005 seconds

SQLFreeStmt( )
    <--- SQL_SUCCESS   Time elapsed - +1,980000E-004 seconds

SQLExecute( hStmt=2:4 )
    ---> Time elapsed - +7,200000E-005 seconds
( Package="SYSSH200          ", Section=4 )
( Row=1, iPar=1, fCType=SQL_C_CHAR, rgbValue="01", x'3031', pcbValue=2, piIndicatorPtr=2 )
( Row=1, iPar=2, fCType=SQL_C_CHAR, rgbValue="9750", x'39373530', pcbValue=4, piIndicatorPtr=4 )
( Row=1, iPar=3, fCType=SQL_C_CHAR, rgbValue="001", x'303031', pcbValue=3, piIndicatorPtr=3 )
( Row=1, iPar=4, fCType=SQL_C_CHAR, rgbValue="200901", x'323030393031', pcbValue=6, piIndicatorPtr=6 )
( Row=1, iPar=5, fCType=SQL_C_CHAR, rgbValue="14752171293,13", x'31343735323137313239332C3133', pcbValue=14, piIndicatorPtr=14 )


( iConvFunction=32, iConv=272, iPar=5, iRow=1, 
pSourceBuff=&amp;043524f8, pTargetBuff=&amp;057441fb, pTargetBuffStart=&amp;057441de, 
iTargetBytesLeft=32324, uiTotalBytesWritten=29, iSourceByteLength=14, 
iTracePoint=12, iTraceFunction=701, iTraceComponent=42, 
pTargetBuffStart (Data)=x'0000000230310000043937353000000330303100000632303039303100' )
( pBiParms->pSqldd="	  SQLDD Information
	  ~~~~~~~~~~~~~~~~~
	  numAllocVals = 6
	  numVals = 6
	  numLobVals = 0
	  numNonNullLobVals = 0
	  SQLAM level received in reply = 7
	  Default Data Type Definition = 2
	  Max Row Size = 0
	  flags = 0
	  codepage = 1252
	  singleByteCP = 1252
	  doubleByteCP = 1200
		  DDvalue[0] 	 DDtype = 87,	 DDlen = 32767,	 DDcodepage = 1252,	 DDLobLenSize = 7
		  DDvalue[1] 	 DDtype = 87,	 DDlen = 32767,	 DDcodepage = 1252,	 DDLobLenSize = 1252
		  DDvalue[2] 	 DDtype = 87,	 DDlen = 32767,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  DDvalue[3] 	 DDtype = 87,	 DDlen = 32767,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  DDvalue[4] 	 DDtype = 33,	 DDlen = 34406400,	 DDcodepage = 0,	 DDLobLenSize = 0
		  DDvalue[5] 	 DDtype = 87,	 DDlen = 32767,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  There is no extension for this dataDescriptor
" )
( Re-executing since original execute got SQLCODE=0 )
( Row=1, iPar=1, fCType=SQL_C_CHAR, rgbValue="01", x'3031', pcbValue=2, piIndicatorPtr=2 )
( Row=1, iPar=2, fCType=SQL_C_CHAR, rgbValue="9750", x'39373530', pcbValue=4, piIndicatorPtr=4 )
( Row=1, iPar=3, fCType=SQL_C_CHAR, rgbValue="001", x'303031', pcbValue=3, piIndicatorPtr=3 )
( Row=1, iPar=4, fCType=SQL_C_CHAR, rgbValue="200901", x'323030393031', pcbValue=6, piIndicatorPtr=6 )
( Row=1, iPar=5, fCType=SQL_C_CHAR, rgbValue="14752171293,13", x'31343735323137313239332C3133', pcbValue=14, piIndicatorPtr=14 )


( iConvFunction=32, iConv=272, iPar=5, iRow=1, 
pSourceBuff=&amp;043524f8, pTargetBuff=&amp;057440eb, pTargetBuffStart=&amp;057440ce, 
iTargetBytesLeft=32596, uiTotalBytesWritten=29, iSourceByteLength=14, 
iTracePoint=12, iTraceFunction=701, iTraceComponent=42, 
pTargetBuffStart (Data)=x'0000000230310000043937353000000330303100000632303039303100' )
( pBiParms->pSqldd="	  SQLDD Information
	  ~~~~~~~~~~~~~~~~~
	  numAllocVals = 6
	  numVals = 6
	  numLobVals = 0
	  numNonNullLobVals = 0
	  SQLAM level received in reply = 7
	  Default Data Type Definition = 2
	  Max Row Size = 0
	  flags = 0
	  codepage = 1252
	  singleByteCP = 1252
	  doubleByteCP = 1200
		  DDvalue[0] 	 DDtype = 87,	 DDlen = 2,	 DDcodepage = 1252,	 DDLobLenSize = 7
		  DDvalue[1] 	 DDtype = 87,	 DDlen = 4,	 DDcodepage = 1252,	 DDLobLenSize = 1252
		  DDvalue[2] 	 DDtype = 87,	 DDlen = 3,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  DDvalue[3] 	 DDtype = 87,	 DDlen = 6,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  DDvalue[4] 	 DDtype = 33,	 DDlen = 34406400,	 DDcodepage = 0,	 DDLobLenSize = 0
		  DDvalue[5] 	 DDtype = 87,	 DDlen = 4,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  There is no extension for this dataDescriptor
" )
( Unretrieved error message="[IBM][CLI Driver] CLI0112E  Error assignment. SQLSTATE=22005" )

SQLExecute( )
    <--- SQL_ERROR   Time elapsed - +9,039000E-003 seconds

SQLError( hEnv=0:1, hDbc=0:2, hStmt=2:4, pszSqlState=&amp;04317fb0, pfNativeError=&amp;042dc53c, pszErrorMsg=&amp;04337c38, cbErrorMsgMax=2048, pcbErrorMsg=&amp;05ebf9cc )
    ---> Time elapsed - +6,800000E-005 seconds
( iRowNumber=1, iColumnNumber=5 )

SQLError( pszSqlState="22005", pfNativeError=-99999, pszErrorMsg="[IBM][CLI Driver] CLI0112E  Error assignment. SQLSTATE=22005", pcbErrorMsg=66 )
    <--- SQL_SUCCESS   Time elapsed - +7,460000E-004 seconds

The prepared statement has 6 params, there are 6 SQLBindParameter, but the SQLExecute has only 5 !

DB2 problem, DI problem??

UPDATE:

   <--- SQL_SUCCESS   Time elapsed - +4,250000E-004 seconds

SQLPrepare( hStmt=2:4, pszSqlStr="INSERT INTO "T1108C"."TEST_PRE1_QT" ("BS0100_CODENTID", "BS0200_CODCENTRO", "KM1600_CODCONGE", "KM0000_CODPERIODO", "19_KM1600_IMP") VALUES (?, ?, ?, ?, ?) ", cbSqlStr=156 )
    ---> Time elapsed - +8,100000E-005 seconds
( StmtOut="INSERT INTO "T1108C"."TEST_PRE1_QT" ("BS0100_CODENTID", "BS0200_CODCENTRO", "KM1600_CODCONGE", "KM0000_CODPERIODO", "19_KM1600_IMP") VALUES (?, ?, ?, ?, ?)" )

SQLPrepare( )
    <--- SQL_SUCCESS   Time elapsed - +7,920000E-004 seconds

SQLBindParameter( hStmt=2:4, iPar=1, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_CHAR, fSQLType=SQL_VARCHAR, cbColDef=5, ibScale=0, rgbValue=&amp;04345008, cbValueMax=5, pcbValue=&amp;0432a620 )
    ---> Time elapsed - +1,120000E-004 seconds

SQLBindParameter( )
    <--- SQL_SUCCESS   Time elapsed - +7,540000E-004 seconds

SQLBindParameter( hStmt=2:4, iPar=2, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_CHAR, fSQLType=SQL_VARCHAR, cbColDef=9, ibScale=0, rgbValue=&amp;04346398, cbValueMax=9, pcbValue=&amp;04342150 )
    ---> Time elapsed - +6,800000E-005 seconds

SQLBindParameter( )
    <--- SQL_SUCCESS   Time elapsed - +7,450000E-004 seconds

SQLBindParameter( hStmt=2:4, iPar=3, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_CHAR, fSQLType=SQL_VARCHAR, cbColDef=7, ibScale=0, rgbValue=&amp;043486c8, cbValueMax=7, pcbValue=&amp;0433efa8 )
    ---> Time elapsed - +6,600000E-005 seconds

SQLBindParameter( )
    <--- SQL_SUCCESS   Time elapsed - +7,330000E-004 seconds

SQLBindParameter( hStmt=2:4, iPar=4, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_CHAR, fSQLType=SQL_VARCHAR, cbColDef=13, ibScale=0, rgbValue=&amp;0434a228, cbValueMax=13, pcbValue=&amp;0433bf30 )
    ---> Time elapsed - +7,600000E-005 seconds

SQLBindParameter( )
    <--- SQL_SUCCESS   Time elapsed - +7,220000E-004 seconds

SQLBindParameter( hStmt=2:4, iPar=5, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_CHAR, fSQLType=SQL_DECIMAL, cbColDef=13, ibScale=2, rgbValue=&amp;0434d4f8, cbValueMax=42, pcbValue=&amp;04338ed0 )
    ---> Time elapsed - +6,600000E-005 seconds

SQLBindParameter( )
    <--- SQL_SUCCESS   Time elapsed - +7,400000E-004 seconds

SQLSetStmtAttr( hStmt=2:4, fOption=SQL_ATTR_PARAMSET_SIZE, pvParam=&amp;00000002, uiStrLen=0 )
    ---> Time elapsed - +1,650000E-004 seconds

SQLSetStmtAttr( )
    <--- SQL_SUCCESS   Time elapsed - +4,290000E-004 seconds

SQLFreeStmt( hStmt=2:4, fOption=SQL_CLOSE )
    ---> Time elapsed - +6,700000E-005 seconds

SQLFreeStmt( )
    <--- SQL_SUCCESS   Time elapsed - +2,960000E-004 seconds

SQLExecute( hStmt=2:4 )
    ---> Time elapsed - +8,200000E-005 seconds
( Package="SYSSH200          ", Section=4 )
( Row=1, iPar=1, fCType=SQL_C_CHAR, rgbValue="01", x'3031', pcbValue=2, piIndicatorPtr=2 )
( Row=1, iPar=2, fCType=SQL_C_CHAR, rgbValue="9750", x'39373530', pcbValue=4, piIndicatorPtr=4 )
( Row=1, iPar=3, fCType=SQL_C_CHAR, rgbValue="001", x'303031', pcbValue=3, piIndicatorPtr=3 )
( Row=1, iPar=4, fCType=SQL_C_CHAR, rgbValue="200901", x'323030393031', pcbValue=6, piIndicatorPtr=6 )
( Row=1, iPar=5, fCType=SQL_C_CHAR, rgbValue="14752171293,13", x'31343735323137313239332C3133', pcbValue=14, piIndicatorPtr=14 )


( iConvFunction=32, iConv=272, iPar=5, iRow=1, 
pSourceBuff=&amp;0434d4f8, pTargetBuff=&amp;057441e9, pTargetBuffStart=&amp;057441cc, 
iTargetBytesLeft=32342, uiTotalBytesWritten=29, iSourceByteLength=14, 
iTracePoint=12, iTraceFunction=701, iTraceComponent=42, 
pTargetBuffStart (Data)=x'0000000230310000043937353000000330303100000632303039303100' )
( pBiParms->pSqldd="	  SQLDD Information
	  ~~~~~~~~~~~~~~~~~
	  numAllocVals = 5
	  numVals = 5
	  numLobVals = 0
	  numNonNullLobVals = 0
	  SQLAM level received in reply = 7
	  Default Data Type Definition = 2
	  Max Row Size = 0
	  flags = 0
	  codepage = 1252
	  singleByteCP = 1252
	  doubleByteCP = 1200
		  DDvalue[0] 	 DDtype = 87,	 DDlen = 32767,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  DDvalue[1] 	 DDtype = 87,	 DDlen = 32767,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  DDvalue[2] 	 DDtype = 87,	 DDlen = 32767,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  DDvalue[3] 	 DDtype = 87,	 DDlen = 32767,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  DDvalue[4] 	 DDtype = 33,	 DDlen = 34406400,	 DDcodepage = 0,	 DDLobLenSize = 0
		  There is no extension for this dataDescriptor
" )
( Re-executing since original execute got SQLCODE=0 )
( Row=1, iPar=1, fCType=SQL_C_CHAR, rgbValue="01", x'3031', pcbValue=2, piIndicatorPtr=2 )
( Row=1, iPar=2, fCType=SQL_C_CHAR, rgbValue="9750", x'39373530', pcbValue=4, piIndicatorPtr=4 )
( Row=1, iPar=3, fCType=SQL_C_CHAR, rgbValue="001", x'303031', pcbValue=3, piIndicatorPtr=3 )
( Row=1, iPar=4, fCType=SQL_C_CHAR, rgbValue="200901", x'323030393031', pcbValue=6, piIndicatorPtr=6 )
( Row=1, iPar=5, fCType=SQL_C_CHAR, rgbValue="14752171293,13", x'31343735323137313239332C3133', pcbValue=14, piIndicatorPtr=14 )


( iConvFunction=32, iConv=272, iPar=5, iRow=1, 
pSourceBuff=&amp;0434d4f8, pTargetBuff=&amp;057440e8, pTargetBuffStart=&amp;057440cb, 
iTargetBytesLeft=32599, uiTotalBytesWritten=29, iSourceByteLength=14, 
iTracePoint=12, iTraceFunction=701, iTraceComponent=42, 
pTargetBuffStart (Data)=x'0000000230310000043937353000000330303100000632303039303100' )
( pBiParms->pSqldd="	  SQLDD Information
	  ~~~~~~~~~~~~~~~~~
	  numAllocVals = 5
	  numVals = 5
	  numLobVals = 0
	  numNonNullLobVals = 0
	  SQLAM level received in reply = 7
	  Default Data Type Definition = 2
	  Max Row Size = 0
	  flags = 0
	  codepage = 1252
	  singleByteCP = 1252
	  doubleByteCP = 1200
		  DDvalue[0] 	 DDtype = 87,	 DDlen = 2,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  DDvalue[1] 	 DDtype = 87,	 DDlen = 4,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  DDvalue[2] 	 DDtype = 87,	 DDlen = 3,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  DDvalue[3] 	 DDtype = 87,	 DDlen = 6,	 DDcodepage = 1252,	 DDLobLenSize = 0
		  DDvalue[4] 	 DDtype = 33,	 DDlen = 34406400,	 DDcodepage = 0,	 DDLobLenSize = 0
		  There is no extension for this dataDescriptor
" )
( Unretrieved error message="[IBM][CLI Driver] CLI0112E  Error en la asignación. SQLSTATE=22005" )

SQLExecute( )
    <--- SQL_ERROR   Time elapsed - +1,309300E-002 seconds

5 params in prepared statement and 5 params in execute …
the problem is the decimal separator?

thanks

---

xavibo (BOB member since 2007-06-11)

[system]

looks like there is some problem with DB2, following is the link with the exact same problem when decimal separator as ‘,’ what is exact DB2 version you are using I tried with following version and it works fine

[ Product: QDB2/NT DB2 v8.1.17.644 ]
[ Level Identifier: 030A0106 ]
[ CLI Driver Version: 08.01.0000 ]
[ Informational Tokens: “DB2 v8.1.17.644”,“s080813”,“WR21416”,“Fixpack 17” ]

http://www-01.ibm.com/support/docview.wss?uid=swg1IY66088

as per the above link the issue is fixed in DB2 8.1 Fix Pack 10

I wouldn’t recommend you to apply Fix Pack 17 for DB2 if you are using DI 11.7 and DB2 8.1 FP 17 as repository since there are few issue that are currently being fixed

---

manoj_d (BOB member since 2009-01-02)

[system]

Hello,

Analyzing the traces of DB2 I can see that the decimal parameter is defined as char in the application and decimal in sql:

fCType=SQL_C_CHAR, fSQLType=SQL_DECIMAL,

If the types are different a conversion is made but fails.

I don’t know why fCTYPE is not defined as DECIMAL by DI.

I try running the Dataflow but modified, I change the DECIMAL field with a a DOUBLE field.
The DF runs correctly, and the trace shows that fCType and fSQLType are defined both as DOUBLE.

@manoj_d

looks like there is some problem with DB2, following is the link with the exact same problem when decimal separator as ‘,’ what is exact DB2 version you are using I tried with following version and it works fine

[ Product: QDB2/NT DB2 v8.1.17.644 ]
[ Level Identifier: 030A0106 ]
[ CLI Driver Version: 08.01.0000 ]
[ Informational Tokens: “DB2 v8.1.17.644”,“s080813”,“WR21416”,“Fixpack 17” ]

http://www-01.ibm.com/support/docview.wss?uid=swg1IY66088

as per the above link the issue is fixed in DB2 8.1 Fix Pack 10

I wouldn’t recommend you to apply Fix Pack 17 for DB2 if you are using DI 11.7 and DB2 8.1 FP 17 as repository since there are few issue that are currently being fixed

I will ask DB2 consultants to know versions and installed fixpacks.

Thanks!

---

xavibo (BOB member since 2007-06-11)

[system]

Hello,

Our DB2 UDB is:

Version: 8.1.14.292
DB2Country = 34
DB2Codepage = 1252

The bug showed in the link, is prior to this version and has diferent configuration. We have “,” as decimal separator, also locale is set to a country that use “,” as decimal separator.

I made some tests with a Visual Basic program using the DB2 Library and parametrized inserts.

• Input data (CType) can be defined as Decimal or Varchar.
• values assigned can be defined as “123,12”, 123.12 , “123.12”, …
• field is defined as fSQLType=SQL_DECIMAL

The results are :

1.- CType=SQL_C_CHAR (adVarChar in VB) and VALUE = “123,12”
ERROR

2.- CType=SQL_C_CHAR (adVarChar in VB) and VALUE = “123.12”
SUCCESS

3.- CType=SQL_C_DECIMAL (adDecimal in VB) and VALUE = “123,12”
SUCCESS

4.- CType=SQL_C_DECIMAL (adDecimal in VB) and VALUE = 123.12
SUCCESS

I don’t know where is the problem

• DI sends the CType incorrectly ( decimal isn’t better? ) ?
• DI sends the value incorrectly ( using “,” instead “.” ) ?
• DB2 fails when conversion is made (don’t understand “123,12” when decimal separator is “,”) ?

Anyone using DB2 UDB?
manoj_d, can you show me the DB2 trace for your inserts?

Thanks!

---

xavibo (BOB member since 2007-06-11)

[system]

According the SQL standards on a locale with , as decimal seperator the combinations

update tab set pi = 3.1415

is the only number format accepted, if you want to insert a decimal in the local number format you have to quote it

update tab set pi = '3,1415'

There is a thing called lacy decimal conversion in DI. When a column is a decimal in the source, all the mappings of this column are 1:1 mappings and the target is a decimal as well, then we read the decimal as string and write it as string. Is it possible the value is read inconsistant? Read on one format expected in the other??? Like the source returns the string as ‘3,1415’.

---

Werner Daehn (BOB member since 2004-12-17)

[system]

I am attaching the DB2 CLI log

try using the DB2 sample C program to isolate the problem, using the ODBC API to do the parameterised inserts

let me check if I have the same version of DB2 that you are using (8.1 Fix Pack 14)

if you look at the trace log that you have attcahed, looks like there is some problem is converting the string value to decimal

iConvFunction=32, iConv=272, iPar=5, if you can interpret the information that is printed in SQLDD that might help isolate the root cause
db2_decimal_test_log.txt (2.0 KB)

---

manoj_d (BOB member since 2009-01-02)

[system]

SOLVED!!

The problem was the DB2 CLI version installed on Job Server.
Updating version 8.1.4 to 8.1.7 solves the bug!

Thanks for your help.

---

xavibo (BOB member since 2007-06-11)