BIGINT datatype is not supported with the SQL Server Classic Wire Protocol driver on Unix-Linux platforms on DataStage v8.5.
Problem(Abstract)
While
reading a BIGINT column as a decimal 19, the job can fail with
"[
IIS-DSEE-TFIP-00072]" Cpy_Stay: When checking operator: When
binding output schema
variable
"outRec": When binding output interface field "LM_ID"
to field
"LM_ID":
Implicit conversion from source type "decimal[19,0]" to
result
type
"int64": Possible range limitation.
Resolving the problem
The
SQL Server Classic Wire Protocol driver does not support BIGINT
datatype on Unix/Linux platforms. Here is the snapshot from the
DataDirect documentation - Progress® DataDirect Connect® Series for
ODBC [odbcuser.pdf].
Please
check KB Doc 8321 for more information:
The
newer versions of the Datadirect drivers ship two drivers for the SQL
Server.
1.
SQL Server Classic Wire Protocol driver (VMmsss24.so)
2.
SQL Server Native Wire Protocol Driver (VMsqls24.so).
This
issue is not seen with the Native Wire Protocol driver (VMsqls24.so).
Please use this driver to address the issue with the BIGINT data
type.
The
.odbc.ini might not have the sample stanza for the SQL Server Native
Wire protocol driver even though the driver VMsqls24.so is present in
the branded_odbc/lib folder.
Here
is the sample stanza that needs to be used for creating the DSN with
the SQL Server Native Wire Protocol driver :
The
attributes given in angular brackets ( < > ) needs to be
supplied while creating the DSN.
Sample
Stanza
------------
[SQL
Server Native Wire Protocol driver]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMsqls24.so
Description=
Database=<Database>
PortNumber=<Port
Number>
LogonID=
Password=
HostName=<Hostname>
WorkStationID=
Language=
ApplicationName=
PacketSize=0
XMLDescribeType=-10
LoginTimeout=15
QueryTimeout=0
AnsiNPW=1
ApplicationUsingThreads=1
EnableQuotedIdentifiers=1
QuotedID=Yes
FetchTSWTZasTimestamp=0
FetchTWFSasTime=1
InitializationString=
AuthenticationMethod=1
GSSClient=native
EncryptionMethod=0
TrustStore=
TrustStorePassword=
HostNameInCertificate=
ValidateServerCertificate=1
SnapshotSerializable=0
ReportCodepageConversionErrors=0
LoadBalancing=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
AlternateServers=
AlwaysReportTriggerResults=0
FailoverMode=0
FailoverGranularity=0
FailoverPreconnect=0
Pooling=0
ConnectionReset=0
MaxPoolSize=100
MinPoolSize=0
LoadBalanceTimeout=0
EnableBulkLoad=0
BulkBinaryThreshold=32
BulkCharacterThreshold=-1
BulkLoadBatchSize=32768
BulkLoadOptions=0