| Author |
Message
|
| wmqstankela |
 Posted: Thu Feb 02, 2017 4:48 am Post subject: Connect integration node to SQL Server db using odbc |
 |
|
Voyager
Joined: 29 Feb 2016
Posts: 94
|
Hi everyone,
I'm trying to connect integration node with SQL Server. I configured ODBCINI to point on odbc.ini file and ODBCSYSINI to point on folder where odbcinst.ini file is. I gave mqbrkrs group to have rw permision on these files.
Also, I've created odbc datasource and run mqsisetdbparms command.
When I run mqsicvp command, everything is fine:
| Code: |
BIP8873I: Starting the component verification for component 'IBN3'.
BIP8876I: Starting the environment verification for component 'IBN3'.
BIP8894I: Verification passed for 'Registry'.
BIP8894I: Verification passed for 'MQSI_REGISTRY'.
BIP8894I: Verification passed for 'Java Version - 1.7.0 IBM Linux build pxa6470_27sr3fp40-20160422_01(SR3 FP40)
BIP8894I: Verification passed for 'MQSI_FILEPATH'.
BIP8878I: The environment verification for component 'IBN3' has finished successfully.
BIP8882I: Starting the WebSphere MQ verification for component 'IBN3'.
BIP8884I: The WebSphere MQ verification for component 'IBN3' has finished successfully.
BIP8290I: Verification passed for the ODBC environment.
BIP8299I: User 'PregledRS' from security resource name 'odbc::EmployeeEngagementProd' will be used for the connection to datasource 'EmployeeEngagementProd'.
BIP8270I: Connected to Datasource 'EmployeeEngagementProd' as user 'PregledRS'. The datasource platform is 'Microsoft SQL Server', version '10.50.2500'.
BIP8275I: Verification passed for User Datasource 'EmployeeEngagementProd'.
BIP8292I: '1' User data sources were not verified, because they do not have mqsisetdbparms credentials.
BIP8874I: The component verification for 'IBN3' has finished successfully.
BIP8071I: Successful command completion.
|
Problem is when I set datasource to compute node and run the flow. I've got following excepion:
| Code: |
(0x01000000:Name ):DatabaseException = (
(0x03000000:NameValue):File = '/build/S1000_slot1/S1000_P/src/DataFlowEngine/MessageServices/ImbDatabaseManager.cpp' (CHARACTER)
(0x03000000:NameValue):Line = 1270 (INTEGER)
(0x03000000:NameValue):Function = 'ImbDatabaseManager::getConnection #4' (CHARACTER)
(0x03000000:NameValue):Type = '' (CHARACTER)
(0x03000000:NameValue):Name = '' (CHARACTER)
(0x03000000:NameValue):Label = '' (CHARACTER)
(0x03000000:NameValue):Catalog = 'BIPmsgs' (CHARACTER)
(0x03000000:NameValue):Severity = 3 (INTEGER)
(0x03000000:NameValue):Number = 2348 (INTEGER)
(0x03000000:NameValue):Text = 'Exception connection to database' (CHARACTER)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = 'EmployeeEngagementProd' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = 'ODBC' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = 'PregledRS' (CHARACTER)
)
(0x01000000:Name ):DatabaseException = (
(0x03000000:NameValue):File = '/build/S1000_slot1/S1000_P/src/DataFlowEngine/MessageServices/ImbOdbc.cpp' (CHARACTER)
(0x03000000:NameValue):Line = 1864 (INTEGER)
(0x03000000:NameValue):Function = 'ImbOdbcConnection::connectNonCoordinated' (CHARACTER)
(0x03000000:NameValue):Type = '' (CHARACTER)
(0x03000000:NameValue):Name = '' (CHARACTER)
(0x03000000:NameValue):Label = '' (CHARACTER)
(0x03000000:NameValue):Catalog = 'BIPmsgs' (CHARACTER)
(0x03000000:NameValue):Severity = 3 (INTEGER)
(0x03000000:NameValue):Number = 2393 (INTEGER)
(0x03000000:NameValue):Text = 'Root SQL exception' (CHARACTER)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 2 (INTEGER)
(0x03000000:NameValue):Text = '-1' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = 'EMPLOYEEENGAGEMENTPROD' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 14 (INTEGER)
(0x03000000:NameValue):Text = '' (CHARACTER)
)
(0x01000000:Name ):DatabaseException = (
(0x03000000:NameValue):File = '/build/S1000_slot1/S1000_P/src/DataFlowEngine/MessageServices/ImbOdbc.cpp' (CHARACTER)
(0x03000000:NameValue):Line = 1877 (INTEGER)
(0x03000000:NameValue):Function = 'ImbOdbcConnection::connectNonCoordinated' (CHARACTER)
(0x03000000:NameValue):Type = '' (CHARACTER)
(0x03000000:NameValue):Name = '' (CHARACTER)
(0x03000000:NameValue):Label = '' (CHARACTER)
(0x03000000:NameValue):Catalog = 'BIPmsgs' (CHARACTER)
(0x03000000:NameValue):Severity = 3 (INTEGER)
(0x03000000:NameValue):Number = 2347 (INTEGER)
(0x03000000:NameValue):Text = 'Child SQL exception' (CHARACTER)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = 'IM002' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 2 (INTEGER)
(0x03000000:NameValue):Text = '0' (CHARACTER)
|
odbc.ini file:
| Code: |
;##########################################################################
;# ODBC database driver manager initialisation file. #
;##########################################################################
;# It is recommended that you take a copy of this file and then edit the #
;# copy. #
;# #
;# 1. For each data source, add the name of the data source into #
;# the 'List of data sources stanza' section. #
;# #
;# 2. For each data source, create a stanza in the #
;# 'Individual data source stanzas' section. #
;# #
;##########################################################################
;#######################################
;#### List of data sources stanza ######
;#######################################
[ODBC Data Sources]
EmployeeEngagementProd=DataDirect ODBC SQL Server Wire Protocol
;###########################################
;###### Individual data source stanzas #####
;###########################################
;# UNIX to SQLServer stanza
[EmployeeEngagementProd]
Driver=/opt/IBM/iib-10.0.0.6/server/ODBC/drivers/lib/UKsqls95.so
Description=DataDirect SQL Server Wire Protocol
Database=EmployeeEngagementProd
HostName=10.1.3.222
PortNumber=1433
AnsiNPW=1
LoginTimeout=0
QueryTimeout=0
;# To use Integrated Windows Authentication uncomment the following lines.
;# AuthenticationMethod=9
;# Domain=<Your Windows Domain Name>
;##########################################
;###### Mandatory information stanza ######
;##########################################
[ODBC]
InstallDir=/opt/IBM/iib-10.0.0.6/server/ODBC/drivers
UseCursorLib=0
IANAAppCodePage=4
UNICODE=UTF-8
|
odbcinst.ini file:
| Code: |
[ODBC]
;# To turn on ODBC trace set Trace=yes
Trace=yes
TraceFile=/home/mqm/log/odbc/odbctrace.out
Threading=2
[EmployeeEngagementProd]
Trace=yes
TraceFile=/home/mqm/log/odbc/odbctrace.out
Threading=2
|
Can anyone help me? |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Thu Feb 02, 2017 5:11 am Post subject: Re: Connect integration node to SQL Server db using odbc |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| wmqstankela wrote: |
| BIP8292I: '1' User data sources were not verified, because they do not have mqsisetdbparms credentials. |
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| wmqstankela |
| Posted: Thu Feb 02, 2017 5:13 am Post subject: |
|
|
Voyager
Joined: 29 Feb 2016
Posts: 94
|
| But this Datasource 'EmployeeEngagementProd' does have credentials and iib reads username and password |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Feb 02, 2017 5:18 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| wmqstankela wrote: |
| But this Datasource 'EmployeeEngagementProd' does have credentials and iib reads username and password |
The mqsicvp command does not agree with you. It threw that message because it does not believe a user data source (presumably the one your flow is using) does not have an associated set of credentials.
This is reinforced by the flow also not being able to use the data source.
So you might think you've set up credentials, but the software disagrees with you.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| wmqstankela |
| Posted: Thu Feb 02, 2017 5:22 am Post subject: |
|
|
Voyager
Joined: 29 Feb 2016
Posts: 94
|
| Code: |
| User 'PregledRS' from security resource name 'odbc::EmployeeEngagementProd' will be used for the connection to datasource 'EmployeeEngagementProd'. |
How does it know username 'PregledRS'? I've set this only with mqsisetdbparms command |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Feb 02, 2017 6:17 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY
|
| wmqstankela wrote: |
| Code: |
| User 'PregledRS' from security resource name 'odbc::EmployeeEngagementProd' will be used for the connection to datasource 'EmployeeEngagementProd'. |
How does it know username 'PregledRS'? I've set this only with mqsisetdbparms command |
And what does the SQL DB say about users? Use windows or SQL authentication?? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Feb 02, 2017 7:06 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| wmqstankela wrote: |
| Code: |
| User 'PregledRS' from security resource name 'odbc::EmployeeEngagementProd' will be used for the connection to datasource 'EmployeeEngagementProd'. |
How does it know username 'PregledRS'? I've set this only with mqsisetdbparms command |
And as a default in the ODBC data source?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| wmqstankela |
| Posted: Thu Feb 02, 2017 7:44 am Post subject: |
|
|
Voyager
Joined: 29 Feb 2016
Posts: 94
|
I've run this command
| Code: |
| mqsisetdbparms IBN3 -n odbc::EmployeeEngagementProd -u PregledRS -p 12345 |
then restart IBN3 node
When I run mqsicvp IBN3 -n EmployeeEngagementProd
| Code: |
BIP8299I: User 'PregledRS' from security resource name 'odbc::EmployeeEngagementProd' will be used for the connection to datasource 'EmployeeEngagementProd'.
BIP8290I: Verification passed for the ODBC environment.
BIP8270I: Connected to Datasource 'EmployeeEngagementProd' as user 'PregledRS'. The datasource platform is 'Microsoft SQL Server', version '10.50.2500'.
===========================
databaseProviderVersion = 10.50.2500
driverVersion = 07.16.0292 (B0299, U0201)
driverOdbcVersion = 03.52
driverManagerVersion = 03.52.0002.0002
driverManagerOdbcVersion = 03.52
databaseProviderName = Microsoft SQL Server
datasourceServerName = 10.1.6.104
databaseName = EmployeeEngagementProd
odbcDatasourceName = EmployeeEngagementProd
driverName = UKsqls95.so
supportsStoredProcedures = Yes
procedureTerm = stored procedure
accessibleTables = Yes
accessibleProcedures = Yes
identifierQuote =
specialCharacters = None
describeParameter = Yes
schemaTerm = owner
tableTerm = table
sqlSubqueries = 31
activeEnvironments = 0
maxDriverConnections = 0
maxCatalogNameLength = 128
maxColumnNameLength = 128
maxSchemaNameLength = 128
maxStatementLength = 524288
maxTableNameLength = 128
supportsDecimalType = Yes
supportsDateType = No
supportsTimeType = No
supportsTimeStampType = No
supportsIntervalType = No
supportsAbsFunction = Yes
supportsAcosFunction = Yes
supportsAsinFunction = Yes
supportsAtanFunction = Yes
supportsAtan2Function = Yes
supportsCeilingFunction = Yes
supportsCosFunction = Yes
supportsCotFunction = Yes
supportsDegreesFunction = Yes
supportsExpFunction = Yes
supportsFloorFunction = Yes
supportsLogFunction = Yes
supportsLog10Function = Yes
supportsModFunction = Yes
supportsPiFunction = Yes
supportsPowerFunction = Yes
supportsRadiansFunction = Yes
supportsRandFunction = Yes
supportsRoundFunction = Yes
supportsSignFunction = Yes
supportsSinFunction = Yes
supportsSqrtFunction = Yes
supportsTanFunction = Yes
supportsTruncateFunction = Yes
supportsConcatFunction = Yes
supportsInsertFunction = Yes
supportsLcaseFunction = Yes
supportsLeftFunction = Yes
supportsLengthFunction = Yes
supportsLTrimFunction = Yes
supportsPositionFunction = No
supportsRepeatFunction = Yes
supportsReplaceFunction = Yes
supportsRightFunction = Yes
supportsRTrimFunction = Yes
supportsSpaceFunction = Yes
supportsSubstringFunction = Yes
supportsUcaseFunction = Yes
supportsExtractFunction = Yes
supportsCaseExpression = Yes
supportsCastFunction = Yes
supportsCoalesceFunction = Yes
supportsNullIfFunction = Yes
supportsConvertFunction = Yes
supportsSumFunction = Yes
supportsMaxFunction = Yes
supportsMinFunction = Yes
supportsCountFunction = Yes
supportsBetweenPredicate = No
supportsExistsPredicate = Yes
supportsInPredicate = No
supportsLikePredicate = No
supportsNullPredicate = Yes
supportsNotNullPredicate = Yes
supportsLikeEscapeClause = Yes
supportsClobType = No
supportsBlobType = No
charDatatypeName = char
varCharDatatypeName = varchar
longVarCharDatatypeName = varchar(max)
clobDatatypeName = N/A
timeStampDatatypeName = N/A
binaryDatatypeName = binary
varBinaryDatatypeName = varbinary
longVarBinaryDatatypeName = varbinary(max)
blobDatatypeName = N/A
intDatatypeName = int
doubleDatatypeName = N/A
varCharMaxLength = 0
longVarCharMaxLength = 0
clobMaxLength = 0
varBinaryMaxLength = 0
longVarBinaryMaxLength = 0
blobMaxLength = 0
timeStampMaxLength = 0
identifierCase = Mixed
escapeCharacter = \
longVarCharDatatype = -1
clobDatatype = 0
longVarBinaryDatatype = -4
blobDatatype = 0
BIP8273I: The following datatypes and functions are not natively supported by datasource 'EmployeeEngagementProd' using this ODBC driver: Unsupported datatypes: 'DATE, TIME, TIMESTAMP, INTERVAL, CLOB, BLOB' Unsupported functions: 'POSITION, BETWEEN, IN, LIKE'
Examine the specific datatypes and functions not supported natively by this datasource using this ODBC driver.
When using these datatypes and functions within ESQL, the associated data processing is done within IBM Integration Bus rather than being processed by the database provider.
Note that "functions" within this message can refer to functions or predicates.
BIP8071I: Successful command completion.
|
|
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Feb 02, 2017 7:57 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
Looking at your OP, is that all the exception text? It seems to end abruptly. Is there anything else that's possibly probative?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| wmqstankela |
| Posted: Thu Feb 02, 2017 8:35 am Post subject: |
|
|
Voyager
Joined: 29 Feb 2016
Posts: 94
|
| This is whole exception list |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Feb 02, 2017 8:42 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| wmqstankela wrote: |
| This is whole exception list |
So it stops abruptly without nesting back in, and with the single cryptic "Child SQL error"?
I'd raise a PMR with IBM. You should at least get the (possibly auto generated) text of the SQL being executed in the last insert.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| wmqstankela |
| Posted: Thu Feb 02, 2017 8:49 am Post subject: |
|
|
Voyager
Joined: 29 Feb 2016
Posts: 94
|
Thanks all for answers!
I've moved odbc.ini and odbcint.ini files to /var/mqsi folder. Then change ODBCINI and ODBCSYSINI environment variable to point on these files. After that I restart node again and now everything works fine. Integration node successfully connect to SQL Server.
I have another question. How to store result in message tree from calling stored procedure?
CREATE PROCEDURE GetEmployeeWorkHours(IN pUsername CHAR, IN pDateFrom TIMESTAMP, IN pDateTo TIMESTAMP) LANGUAGE DATABASE EXTERNAL NAME "dbo.GetEmployeeWorkHours"; |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Feb 02, 2017 9:02 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| wmqstankela wrote: |
| I've moved odbc.ini and odbcint.ini files to /var/mqsi folder. Then change ODBCINI and ODBCSYSINI environment variable to point on these files. After that I restart node again and now everything works fine. Integration node successfully connect to SQL Server. |
That shouldn't have made a difference. If there was a problem with those files the mqsicvp command should have failed as well.
| wmqstankela wrote: |
I have another question. How to store result in message tree from calling stored procedure?
CREATE PROCEDURE GetEmployeeWorkHours(IN pUsername CHAR, IN pDateFrom TIMESTAMP, IN pDateTo TIMESTAMP) LANGUAGE DATABASE EXTERNAL NAME "dbo.GetEmployeeWorkHours"; |
With a SET statement. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| wmqstankela |
| Posted: Thu Feb 02, 2017 9:18 am Post subject: |
|
|
Voyager
Joined: 29 Feb 2016
Posts: 94
|
| Can you please give me an example for this procedure GetEmployeeWorkHours? |
|
| Back to top |
|
|
| wmqstankela |
| Posted: Thu Feb 02, 2017 9:20 am Post subject: |
|
|
Voyager
Joined: 29 Feb 2016
Posts: 94
|
| GetEmployeeWorkHours procedure is select query into SQL Server db. Query return 3 columns with many rows. How to store this result into message tree? |
|
| Back to top |
|
|
|
|
