| Author |
Message
|
| niks29986 |
 Posted: Fri Sep 05, 2014 1:18 am Post subject: ODBC library error connecting to Netezza DB from WMB |
 |
|
Novice
Joined: 04 Sep 2014
Posts: 14
|
Hi All,
This is about connecting to Netezza DB from Message broker. We have made entries in odbc and odbcinst files with server name, port and other details with all the required env paths in place. Want to mention that, we also have made entries to .profile file. When we run a sample flow, the correct odbc file is being referred but it is throwing below error,
Severity:INTEGER:3 Number:INTEGER:2393 Text:CHARACTER:Root SQL exception Insert Type:INTEGER:2 Text:CHARACTER:-1 Insert Type:INTEGER:5 Text:CHARACTER:NZDSN Insert Type:INTEGER:14
Text:CHARACTER:/opt/ibm/IE02/2.0.0/lib/libodbcinterface.so
DatabaseException File:CHARACTER:/build/slot1/S800_P/src/DataFlowEngine/ImbOdbc.cpp Line:INTEGER:2437 Function:CHARACTER:ImbOdbcConnection::checkRcInner Type:CHARACTER: Name:CHARACTER: Label:CHARACTER: Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3 Number:INTEGER:2322 Text:CHARACTER:Child SQL exception Insert
Type:INTEGER:5 Text:CHARACTER:HY000 Insert Type:INTEGER:2 Text:CHARACTER:33
Insert Type:INTEGER:5 Text:CHARACTER:[unixODBC]Server and/or port attributes are empty
When we run the mqsicvp it gives same driver manager error.
It would be a great help if someone can help us. Kindly let me know if you need more details.
Kind Regards,
Nikhil |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Fri Sep 05, 2014 4:28 am Post subject: Re: ODBC library error connecting to Netezza DB from WMB |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| niks29986 wrote: |
| Code: |
| [unixODBC]Server and/or port attributes are empty |
When we run the mqsicvp it gives same driver manager error.
It would be a great help if someone can help us. Kindly let me know if you need more details.
Kind Regards,
Nikhil |
I think the odbc message points to missing attributes needed to create the connection... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| niks29986 |
| Posted: Fri Sep 05, 2014 7:14 am Post subject: ODBC library error connecting to Netezza DB from WMB |
|
|
Novice
Joined: 04 Sep 2014
Posts: 14
|
Hi, Thanks for the reply!
The odbc file does have all the required connection details,
[NZLAND]
Driver = /usr/local/nz/lib64/libnzodbc.so
Description = Netezza ODBC driver
Servername = dxch3netezza.xxx.com
Port = 5480
Database = DEV_xxx_LAND
Username = WMB_USER
Password = xx
ReadOnly = false
ShowSystemTables = false
..
Also the path names are verified that they are pointing to correct odbc file. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Sat Sep 06, 2014 5:07 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Run an odbc trace ?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| niks29986 |
| Posted: Mon Oct 20, 2014 3:25 am Post subject: |
|
|
Novice
Joined: 04 Sep 2014
Posts: 14
|
Hi Guys,
We received many issues one after the other while connecting to NZ DB. The last issue was Error loading transaction library. All have been resolved now.
In the process we found that NZ DB connectivity needs broker restarts. After we started bouncing broker we have not received any issues regarding library, driver etc.
So I need you guys input on requirement on Broker restart. Broker version is 8.0.0.2.
2nd question is, In one transaction we have about 40 MB of source data coming in as MQ segmented messages over 4 MB channel. We have a requirement to insert this data to Netezza DB. Current WMB performance is very poor for large data and would need help/ ways to sort this out.
We are splitting records from flat file and inserting one by one in DB.
Your inputs will be very helpful. Thanks! |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Oct 20, 2014 4:49 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Your performance will largely be dependent on how you manage your commit load for Netezza DB.
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| niks29986 |
| Posted: Mon Oct 20, 2014 7:35 am Post subject: |
|
|
Novice
Joined: 04 Sep 2014
Posts: 14
|
Hi,
Thanks for the response!
1. Can you please let me know how we can play around with Commit load from WMB?
2. Same data load with Oracle takes very less time.
3. Data load to Netezza from Informatica is very quick using ODBC connection only as in the case of WMB.
4. We are parsing data through DFDL and using passthrough in a for loop to insert the data to NZ DB. It is taking 50 mins for 25K records. The requirement is for more than 8 million records.
Kindly put your thoughts. Any inputs will be helpful. Thanks! |
|
| Back to top |
|
|
| Muthukrishnan |
| Posted: Tue Nov 04, 2014 2:41 am Post subject: |
|
|
Apprentice
Joined: 16 Feb 2013
Posts: 48
|
Hi niks29986,
We are trying to establish ODBC connectivity for Netezza with WMB 8.0.0.2. But we facing issues in connection. We have configured Netezza stanza and added environment variables. But we are still getting error when we are testing connection with “mqsicvp”.Can you please help?
| Code: |
BIP2393E: Database error: ODBC return code '-1' from data source ''ZZZ'' using ODBC driver manager ''/opt/ibm/IE02/2.0.0/lib/libodbcinterface.so''.
The broker received an error when processing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database about this error.
Use the following messages to determine the cause of the error. Typical problems are an incorrect data source, or table names. Correct either the database or the broker configuration. |
|
|
| Back to top |
|
|
| niks29986 |
| Posted: Tue Nov 04, 2014 3:42 am Post subject: |
|
|
Novice
Joined: 04 Sep 2014
Posts: 14
|
check the odbc params in netezza odbc.. normally it would be present in etc/nzodbc dir.
if everything looks correct and still mqsicvp doesnt work then try restarting the broker. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Nov 04, 2014 3:58 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
mqsicvp and the broker are not dependant upon each other.
you can run mqsicvp with the broker stopped.
In my broker creation scripts I run mqsicvp as part of the setup and provisioning process.
MQSICVP does use the configuration information of the broker but that is it when you are using it in the form
mqsicvp BROKER -n ODCBname
This will use the username & password that was supplied with the mqsisetdbparms command.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Muthukrishnan |
| Posted: Tue Nov 04, 2014 4:26 am Post subject: |
|
|
Apprentice
Joined: 16 Feb 2013
Posts: 48
|
ODBC params are set in odbc.ini as below. And I tried to stopped the broker and issued mqsicvp command. But no luck. Did I miss anything in the setup?
| Code: |
[XXX]
;# Netezza Stanza
Driver=/usr/local/nz/lib64/libnzodbc.so
Description=NetezzaSQL ODBC
Servername=XXX.com
Port=5480
Database=XXX
Username=XXX
Password=XXX123
ReadOnly=false
ShowSystemTables=false
LegacySQLTables=false
LoginTimeout=0
QueryTimeout=0
DateFormat=1
NumericAsChar=false
SQLBitOneZero=false
StripCRLF=false
|
|
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Nov 04, 2014 5:12 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
How about the access rights to
/usr/local/nz/lib64/libnzodbc.so
for strarters.
then does an 'ldd -v' command on that library show everything there and no missing libraries? (if using linux)
If the host (xxx.com) readable?
If there somethnig on that hose listening on port 5480? Is it the right thing?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Muthukrishnan |
| Posted: Tue Nov 04, 2014 5:36 am Post subject: |
|
|
Apprentice
Joined: 16 Feb 2013
Posts: 48
|
I guess there is no issue library. Below is the output with ldd -v option. There is no problem with the rights or port number.
| Code: |
linux-vdso.so.1 => (0x00007fffe1dfd000)
libc.so.6 => /lib64/libc.so.6 (0x00002af64ba7f000)
libm.so.6 => /lib64/libm.so.6 (0x00002af64bdd9000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00002af64c05c000)
libdl.so.2 => /lib64/libdl.so.2 (0x00002af64c278000)
/lib64/ld-linux-x86-64.so.2 (0x0000003e8ae00000)
Version information:
./libnzodbc.so:
libdl.so.2 (GLIBC_2.2.5) => /lib64/libdl.so.2
libpthread.so.0 (GLIBC_2.2.5) => /lib64/libpthread.so.0
libm.so.6 (GLIBC_2.2.5) => /lib64/libm.so.6
libc.so.6 (GLIBC_2.3) => /lib64/libc.so.6
libc.so.6 (GLIBC_2.2.5) => /lib64/libc.so.6
/lib64/libc.so.6:
ld-linux-x86-64.so.2 (GLIBC_2.3) => /lib64/ld-linux-x86-64.so.2
ld-linux-x86-64.so.2 (GLIBC_PRIVATE) => /lib64/ld-linux-x86-64.so.2
/lib64/libm.so.6:
libc.so.6 (GLIBC_2.2.5) => /lib64/libc.so.6
/lib64/libpthread.so.0:
ld-linux-x86-64.so.2 (GLIBC_2.3) => /lib64/ld-linux-x86-64.so.2
ld-linux-x86-64.so.2 (GLIBC_2.2.5) => /lib64/ld-linux-x86-64.so.2
ld-linux-x86-64.so.2 (GLIBC_PRIVATE) => /lib64/ld-linux-x86-64.so.2
libc.so.6 (GLIBC_2.3.2) => /lib64/libc.so.6
libc.so.6 (GLIBC_PRIVATE) => /lib64/libc.so.6
libc.so.6 (GLIBC_2.2.5) => /lib64/libc.so.6
/lib64/libdl.so.2:
ld-linux-x86-64.so.2 (GLIBC_PRIVATE) => /lib64/ld-linux-x86-64.so.2
libc.so.6 (GLIBC_PRIVATE) => /lib64/libc.so.6
libc.so.6 (GLIBC_2.2.5) => /lib64/libc.so.6
|
|
|
| Back to top |
|
|
| Muthukrishnan |
| Posted: Wed Nov 05, 2014 9:53 pm Post subject: |
|
|
Apprentice
Joined: 16 Feb 2013
Posts: 48
|
|
| Back to top |
|
|
|
|
