Odbc dsn db setup

Infosphere's Quality Product

Moderators: chulett, rschirm

dj
Participant
Posts: 78
Joined: Thu Aug 24, 2006 5:03 am
Location: india

Odbc dsn db setup

Post by dj »

Hi All,
For the match designer, I am trying to set up the odbc dsn.
I have searched the forum and found odbc.in and uvconfig files to be updated.

We have recently installed iis quality stage and I could find only the default XMETA and IADB db2

Do we need to create a separate db on the default Db2 for match designer and pls let me know for any other steps.

Thanks, Dj
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Yes, you will need to create a match designer database, create an ODBC DSN for it on your server (on the engine tier), and create an ODBC DSN for it on your client, with the same data source name across both tiers. Follow these two links:

http://www-01.ibm.com/support/knowledge ... klist.html

http://www-01.ibm.com/support/knowledge ... qs_md.html
Choose a job you love, and you will never have to work a day in your life. - Confucius
dj
Participant
Posts: 78
Joined: Thu Aug 24, 2006 5:03 am
Location: india

Post by dj »

Thanks for your reply.

I'm encountering the following error when I tried to create a test DB for Match DB.

login:db4inst1

db2 => CREATE DATABASE QSMATFREQ AUTOMATIC STORAGE YES ON '/proj/<proj_name>/IBM/DB2' DBPATH ON '/proj/<proj_name>/IBM/DB2' ALIAS QSMATFREQ USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 32768

SQL1326N The file or directory "/proj/<proj_name>/IBM/DB2" cannot be accessed.
SQLSTATE=58038

From the installation response text I could see default db2 is in "/proj/<proj_name>/IBM/DB2.
---------------------------------------------------------------------
suite.installLocation=/proj/<proj_name>/IBM/InformationServer
db2.install.location=/proj/<proj_name>/IBM/DB2
db2.instance.home.directory=/xxx/.u_01/db4inst1
db2.instance.name=db4inst1
db2.instance.user.name=db4inst1
-----------------------------------------------------------------------------------
ds.project.1.path=/proj/<proj_name>/IBM/InformationServer/Server/Projects/DEVQS
ia.db.location=/proj/<proj_name>/IBM/InformationServer/Repos/iadb
is.admin.user=isadmin
xmeta.db.location=/proj/<proj_name>/IBM/InformationServer/Repos/xmeta
xmeta.odb.db.location=/proj/<proj_name>/IBM/InformationServer/Repos/xmeta
xmeta.odb.db.name=xmeta
xmeta.odb.db.name=xmeta
xmeta.odb.db.registration=true
xmeta.odb.db.schemaName=DSODB
xmeta.odb.db.tablespace=DSODBSPACE
xmeta.odb.db.user.name=dsodb
-----------------------------------------------------------------------------

Are there any permission error or the path should be as same as XMeta's repost directory.

Thanks,
Dj
Last edited by dj on Thu Dec 10, 2015 12:29 pm, edited 1 time in total.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

The path can be anything so long as the instance owner ID you are using has permissions to create files in that path. I suspect it does not. I would recommend creating it in same location as XMETA or the like.
Choose a job you love, and you will never have to work a day in your life. - Confucius
dj
Participant
Posts: 78
Joined: Thu Aug 24, 2006 5:03 am
Location: india

Post by dj »

The error was due to the permission issue. After granting permission for the Instance Owner id, we were able to create the MatchDB in the same path as XMETA resides./proj/<proj_name>/IBM/InformationServer/Repos.

To establish the ODBC setup's , I have added the respective entries in ODBC.ini and UVconfig files.

I'm able to connect to the TestDb QSFREQDB in the DSDB2-plugin metadata through datastage.However the below commands failed.

1) ./bind00 QSFREQDB
./bind00: error while loading shared libraries: libVMicu00.so: cannot open shared object file: No such file or directory

2) In the Client Windows -File DSN there are no IBM DB2 Wireprotocol drivers to create DSN.


Thanks,Dj
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

1) If you have setup ODBC on the server side, then you can test the connection by using an ODBC Connector stage. I do not recall having to manually run any bind step. Your .odbc.ini file should look something like this:

Code: Select all

[ODBC Data Sources]
...
your_match_db_name=DataDirect DB2 Wire Protocol Driver
...

[your_match_db_name]
Driver=/your_path_here/branded_odbc/lib/VMdb200.so
Description=DataDirect DB2 Wire Protocol Driver
...
Database=your_match_db_name
...
IpAddress=your_host_name_or_IP_address
...
TcpPort=your_db_port_number
...
2) On the client side, I would guess you have 64-bit Windows (required for 11.x clients), but you still need to run the 32-bit ODBC Data Source Administrator tool, which is sort of buried. The control panel only lets you open the 64-bit ODBC tool. When you open the 32-bit ODBC tool, you should find the correct ODBC drivers available when you got to add a System DSN. Run this for the 32-bit tool:

Code: Select all

%systemdrive%\Windows\SysWoW64\Odbcad32.exe
Choose a job you love, and you will never have to work a day in your life. - Confucius
dj
Participant
Posts: 78
Joined: Thu Aug 24, 2006 5:03 am
Location: india

Post by dj »

On the client side, I have ran the 32-bit ODBC Data Source Administrator tool Odbcad32.exe and tried to create the DSN.

IBM Db2 Wire Protocol driver -Driver
MatchDB Database- DBName
Server:ServerIP and PortNumber.

But the test connection throws error TCP Socket Closed.Are there any DB2 server side settings to be enabled?

Thanks,Dj
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Errors like that are generally on the client. It could be from incorrect client driver settings or caused by local firewall or security software or a firewall in between client and server. Disable such software for testing. Then work with your local DBA. Then local firewall/network/security may need to help.
Choose a job you love, and you will never have to work a day in your life. - Confucius
dj
Participant
Posts: 78
Joined: Thu Aug 24, 2006 5:03 am
Location: india

Post by dj »

My bad, the Db2 port number was incorrect :-( and in client windows i was able to establish the connectivity for the System ODBC DSN.

However, in Server still it throws the same error while trying to connect to the match db. DB2 is the default db that got installed during iis installation and resides in the same server.

I have picked up the port number from the iis response file
db2.port.number=XXX

Code: Select all

[TestDB]
Driver=/proj_path/IBM/InformationServer/Server/branded_odbc/lib/VMdb200.so
Description=DataDirect DB2 Wire Protocol Driver
AddStringToCreateTable=
AlternateID=
Collection=OS/390 and AS/400 (Remove for DB2 UDB)
CipherList=DEFAULT:!RC4
Database=TestDB
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=Server IP
IsolationLevel=CURSOR_STABILITY
Location=OS/390 and AS/400 (Remove for DB2 UDB)
LogonID=dsadm
Password=
Package=DB2 package name
PackageOwner=
TcpPort=DB2Port
WithHold=1
*uvconfig file
<TestDB>
DBMSTYPE = ODBC

I have checked

> ./ddtestlib path/IBM/InformationServer/Server/branded_odbc/lib/VMdb200.so
Load of path/IBM/InformationServer/Server/branded_odbc/lib/VMdb200.so successful, qehandle is 0x501060
File version: 07.15.0206 (B0212, U0148)

and

path/IBM/InformationServer/Server/branded_odbc/samples/example> ./example
./example DataDirect Technologies, Inc. ODBC Example Application.

Enter the data source name : TestDB

Enter the user name : dsadm

Enter the password : XX
SQLSTATE = 08S01
NATIVE ERROR = 0
MSG = [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver]Socket closed.

Are there any change in dsenv file?

Code: Select all

if [ -n "$DSHOME" ] && [ -d "$DSHOME" ]
then
	ODBCINI=$DSHOME/.odbc.ini; export ODBCINI
	HOME=${HOME:-/}; export HOME
	
	#LANG="<langdef>";export LANG
	#LC_ALL="<langdef>";export LC_ALL
	#LC_CTYPE="<langdef>";export LC_CTYPE
	#LC_COLLATE="<langdef>";export LC_COLLATE
	#LC_MONETARY="<langdef>";export LC_MONETARY
	#LC_NUMERIC="<langdef>";export LC_NUMERIC
	#LC_TIME="<langdef>";export LC_TIME
	#LC_MESSAGES="<langdef>"; export LC_MESSAGES
	
	LD_LIBRARY_PATH=`dirname $DSHOME`/biginsights/IHC/c++/Linux-amd64-64/lib:`dirname $DSHOME`/branded_odbc/lib:`dirname $DSHOME`/DSComponents/lib:`dirname $DSHOME`/DSComponents/bin:$DSHOME/lib:$DSHOME/uvdlls:`dirname $DSHOME`/PXEngine/lib:$ISHOME/jdk/jre/lib/amd64/j9vm:$ISHOME/jdk/jre/lib/amd64:$ASBHOME/lib/cpp:$ASBHOME/apps/proxy/cpp/linux-all-x86_64:/path/IBM/DB2/lib64:$LD_LIBRARY_PATH
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

On your server's .odbc.ini entry, try setting all of these to blank.

Collection=
Location=
LogonID=
Password=

And double check your host name/IP address and port number.
Choose a job you love, and you will never have to work a day in your life. - Confucius
rjdickson
Participant
Posts: 378
Joined: Mon Jun 16, 2003 5:28 am
Location: Chicago, USA
Contact:

Post by rjdickson »

First, you do not need to update dsenv or uvconfig.odbc.

Second, there is still something wrong with your .odbc.ini file. Here is mine that works:

Code: Select all

[QSMATCHDriver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMdb200.so
Description=QualityStage Match Designer Database
AddStringToCreateTable=
AlternateID=
Database=QSMATCH
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=is-server
IsolationLevel=CURSOR_STABILITY
LogonID=db2inst1
Password=<password removed for security>
Package=
PackageOwner=
TcpPort=50000
WithHold=1
Here is the return from my ./example:

Code: Select all

[dsadm@is-server example]$ ./example
./example DataDirect Technologies, Inc. ODBC Example Application.

Enter the data source name : QSMATCH

Enter the user name        : db2inst1

Enter the password         : <password removed for security>

Enter SQL statements (Press ENTER to QUIT)
SQL> 
Note that there are many differences between our entries. For example, I removed the 'Collection=' because we are using DB2 UDB. I also removed 'Location='. Another is that I used db2inst1 to create the database, so that its the ID that should be used.

Next: Remember you need to restart the server engine and the ASBNode (step 3 on http://www-01.ibm.com/support/knowledge ... _odbc.html

Finally. my ./example does not error (yours does with an 08S01 which indicates a bad port or ipaddress..)

I hope this helps
Regards,
Robert
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Are you using HTTPS when trying to open Launchpad? Are you sure that port number 9447 is the one on which it is listening?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
dj
Participant
Posts: 78
Joined: Thu Aug 24, 2006 5:03 am
Location: india

Post by dj »

Yes Ray,its 9447 and we are able to run Launchpad.

But when trying to connect through client rpc fail error-81009

Failed to connect to Information Server Engine: server:31539, project: dstage1.
(The RPC failed (81009))

Code: Select all

--
> ps -ef | grep dsrpcd
root     22285     1  0 Jan11 pts/0    00:00:00 /path/InformationServer/Server/DSEngine/bin/ad4dsrpcd

>ps -ef | grep dsapi_slave -- no output.

>netstat -a | grep dsrpc
tcp        0      0 *:ad4dsrpc              *:*                     LISTEN


>ps -ef | grep -i agent
dsadm    21468     1  0 Jan11 pts/0    00:00:00 /bin/sh ./Agent.sh
dsadm    21473 21468  0 Jan11 pts/0    00:01:58 path//jdk/jre/bin/java -Xbootclasspath/a:conf:eclipse/plugins/com.ibm.iis.client -Xss2M -Xmso2M -
Duser.language=en -Duser.country=US -Djava.ext.dirs=path//jdk/jre/lib/ext:lib/java:eclipse/plugins:eclipse/plugins/com.ibm.iis.client -Djava.util.logging.config.file=path//ASBNode/conf
/asbagent-logging.properties -classpath conf:eclipse/plugins/com.ibm.iis.client com.ibm.iis.isf.agent.impl.AgentImpl run
Pls let me know how to resolve this rpc error
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

All 81xxx errors pertain to the RPC mechanism, and can be decoded via the ERRD.H file in the INCLUDE sub-directory of DSEngine.

Are you running multiple versions? If not, why are you using non-default port number (31539) and non-default user name (ad4dsrpcd)?

And are you running the services and engine tiers on the same machine or on separate machines?
Last edited by ray.wurlod on Tue Jan 12, 2016 4:17 pm, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
dj
Participant
Posts: 78
Joined: Thu Aug 24, 2006 5:03 am
Location: india

Post by dj »

Thanks all for your replies.
We did a server restart from root and ran ds impersonation shell and rpc issue got solved.
After db2 restart, the odbc connection was resolved.
Thanks
Post Reply