Solved

Permission Denied from SQLServer Package

Posted on 2004-04-06
19
2,149 Views
Last Modified: 2012-08-13
Hello,

I have a problem that seems to be linked with DB2Connect.

I created a MS DTS (Microsoft Data Transaction Services) package with SQLServer.
That package imports data from a SQL Server database, and delete/insert into DB2 database.

I created the package on my computer (Win2000 Pro SP4,SqlServer2000 SP3a,DB2Connect 7.1.3)
When I run the package all works fine (4 steps: delete from tableA,Insert in tableA, delete from tableB, insert in tableB)

The same package fails when runing from a NT Server (Win2000 SP4,SQLServer 2000 SP3a, DB2Connect 7.1.10)
The delete steps are working, but both insert steps give me:
Error Description:Insert error, column 2 ('MO', DBTYPE_I4), status 9:  Permission denied.

I then installed DB2 FixPack 11 (7.1.11) , and I now get the same result from My PC. (I did execute the package just before patching and it worked)
Would anyone have a clue of what might cause the problem?

Dan

PS: more details/Points on MS SQL Groups: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20938448.html






0
Comment
Question by:dapcom
  • 7
  • 6
  • 3
  • +1
19 Comments
 
LVL 18

Expert Comment

by:BigSchmuh
Comment Utility
Did you check that the NT account (Usually named db2admin) used to start your DB2 services is allowed to access your package files ?
==> This may happen if you installed the FixPak using another NT account than this one

Hope this helps.
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
has a DBA actually Bound the necessary plans/packages for your newly installed version of DB2 connect?

although i'd probably expect a different error message...

do you have access to the DB2 client runtime tools ? (CLI/CLP)

can you try a select/insert  through them  and see if that works?.
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
has a DBA actually Bound the necessary plans/packages for your newly installed version of DB2 connect?

although i'd probably expect a different error message...

do you have access to the DB2 client runtime tools ? (CLI/CLP)

can you try a select/insert  through them  and see if that works?.
0
 
LVL 1

Author Comment

by:dapcom
Comment Utility
Hello BiugSchmuh,

Thank you for your help, but the package runs. The both steps that delete records in DB2 work. It fails only on setps that does insert with the specified message. Anyway, it's a SQLServer package that I test manually with DTSRun or with a .Net program I wrote, or with MS Enterprise Manager. The results are the same. My package is loaded from SQLServer with the necessary rights. The definition of the package currently contains all necessary id/logons for both the SQLServer side and the DB2 side.

Hello Lowfatspread,

No I haven't made the bound. I'll try that as soon as I can get the DBA. Thanks for the hint.

I'm not sure what mean CLI/CLP... I do have access to tools such as the Command Center, with wich I can make any kind of queries, including delete and inserts.

Do you think the bound is still an option? I'm not really sure what it means...

Daniel
0
 
LVL 1

Author Comment

by:dapcom
Comment Utility
The bind didn't seem to help. I'm gonna uninstall db2connect on my PC and reinstall 7.1.3 to see if it works again...

Dan
0
 
LVL 18

Expert Comment

by:BigSchmuh
Comment Utility
To fully understand the "bind" problem, please be aware that a DB2 client in FixPak 10 can connect to a DB2 server in another FixPak but may not be able to run DB2 stored procedures including system ones.

Binding the CLIPKG ("Client package") is necessary to use different FixPaks between db2 clients and the db2 server.
==> Using a db2 client (Administrator or Development type not the Runtime) with the expected client FixPak (not the server one of course), you just need to issue the following:
-CD ...LocateDB2...\SQLLIB\BND
-DB2 CONNECT YourDBAlias USER db2admin USING pwd
-DB2 BIND @db2cli.lst blocking all grant public

Hope this helps.
0
 
LVL 1

Author Comment

by:dapcom
Comment Utility
The package works again with db2connect 7.1.3. installed. (without the need to bind again).

My problem is on the production server I can't do the same. There is db2connect 7.1.10. And I won't be allowed to go back to 7.1.3

What Could I do now? Anyone has an idea?

Dan
0
 
LVL 18

Expert Comment

by:BigSchmuh
Comment Utility
Yes, just act as my previous post said...(We were almost posting at the same time ;-)
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:dapcom
Comment Utility
OK BigSchmuh, II'll try that, but unfortunately I'll require a DBA to enter his logon. Is that correct? (the db2admin used to install db2connect on my PC doesn't have access to the DB2 database, my own userID does not have sufficient privilege to do the bind. I'll have to wait next tuesday because of the easter long week-end.

But wouldn't this command have the same effect as clicking the 'Bind' button in the Client Configuration Wizard and choose DB2Utilities? Because I did that with the DBA logon/pwd and it didn't change the results.

And I couldn't have a precise answer from the DBA concerning the version of the DB2 server. I know it's 7.1, but what about the fixpack? Someone told me the version system is not the same, on os390.

By the way, I think I didn't mention the fact that the DB2 database is on OS/390 mainframe...

Thanks again for your help.
Dan
0
 
LVL 18

Expert Comment

by:BigSchmuh
Comment Utility
You are right:
-The BIND require a DBA pwd
-The "Bind" button in the client configuration assistant using "DB2 Utilities" is an equivalent method (I never noticed this before...)
==> I think we are closed to the solution but it may be more efficient to work with your DBA and IBM support.
0
 
LVL 18

Expert Comment

by:BigSchmuh
Comment Utility
Did your NT server been configured using this Bind button as well as your pc ?
==> Otherwise we are done...and we'll have a good easter time ;-)
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
glad to see you appear to have a solution

low
0
 
LVL 1

Author Comment

by:dapcom
Comment Utility
Hello guys,

I am sorry to disappoint you, but what in my last post made you think the problem was solved?
At last I hope we all had a good easter. ;-)

I already used the bind button (Db2 Utilities) and it didn't make it work(with the dba password), that's why I asked if it was the same as the manual procedure Big gave for binding.

This morning, I tried the 'manual' bind
It seems the button don't binds the same 'things': I don't have the same result.

The binding process using the button seemed to run well except for the 4 warnings (see at bottom: DB2Utilities Bind)

When I try the 'manual' procedure, that is:

connect to DBAlias user dbaUserID using dbaPwd
bind @db2cli.lst blocking all grant public

I have 62 warnings, (but no errors). (see at botom some of them: Manual Bind)


Something seem very wrong here...

Then I tried to use the button and 'bind user applications' rather than db2 utilities, and selecting the db2cli.lst file. Here, no warning!! Good?

No: my package still don't work (that is: the package runs, the delete steps runs, but the insert steps fails)! I'm lost and don't really understand what i'm trying to bind anyway. Wich exactly of the binds are necessary for use from SQLServer?
The client DB2 tools (ie Command Center) are working fine: select or insert queries are working fine.

And I made another interresting test: I made a new SQL DTS package that just insert values into the same DB2 tables, but using an 'Execute SQL Task' and  hard-coded values rather than a  'transform data task' . And you know what? It worked.
It seems the problem only arrise when using a 'transform data task' in SQL Server (for reminder: that step works with db2connect 7.1.3). And I know it is retrieving data from sqlserver because it show me the data failing to insert in the log (look the original thread in SQL group:  http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20938448.html

I think maybe all this bind thing is not the problem after all, even if it seemd promising. Or it needs a 'specific' bind just for the transform.
Anyway, when I did reinstall the db2connect 7.1.3, I didn't had to bind to make it work again...

I'm that close, it's so frustrating...

Perhaps mixing ms and ibm worlds is not such a good idea? (lol)

About the IBM support: I contacted them at the same time I posted here, but they are a lot less responsive than you guys
;-) I've been told that I will be contacted... still waiting...

And our DBa are not that familiar with db2conect.

Dan

Here are the logs anyway:
DB2Utilities Bind:
---------------------8<--------------------
LINE    MESSAGES FOR db2clprr.bnd
------  --------------------------------------------------------------------
        SQL0061W  The binder is in progress.
  822   SQL0440N  No function by the name "PROCEDURE" having
                  compatible arguments was found in the function path.  
                  SQLSTATE=58020
 1040   SQL0440N  No function by the name "PROCEDURE" having
                  compatible arguments was found in the function path.  
                  SQLSTATE=58020
 1043   SQL0440N  No function by the name "PROCEDURE" having
                  compatible arguments was found in the function path.  
                  SQLSTATE=58020
 1074   SQL0104N  An unexpected token "SCHEMA" was found following
                  "".  Expected tokens may include:  "CURRENT DATE, TIME,
                  TIMESTAMP, CURRENT_DATE, CURRENT_TIME, CUR".
        SQL0091N  Binding was ended with "0" errors and "4" warnings.
-------------------------8<-------------------------------

Manual Bind:
------------------8<----------------------------
db2 => bind @db2cli.lst blocking all grant public

LINE    MESSAGES FOR db2cli.lst
------  --------------------------------------------------------------------
        SQL0061W  The binder is in progress.

LINE    MESSAGES FOR db2cliws.bnd
------  --------------------------------------------------------------------
 1908   SQL0206N  "LONGLENGTH " is not valid in the context where it
                  is used.  SQLSTATE=01533
:
:

 2880   SQL0204N  "SYSIBM.SYSSCHEMATA" is an undefined name.
                  SQLSTATE=01532
 2887   SQL0440N  No function by the name "FUNCTION" having
                  compatible arguments was found in the function path.
                  SQLSTATE=58020
:
:
 3059   SQL0206N  "COLNAMES " is not valid in the context where it is
                  used.  SQLSTATE=01533
 3187   SQL0206N  "T.NAME" is not valid in the context where it is
                  used.  SQLSTATE=01533
 3204   SQL0206N  "CONTROLAUTH " is not valid in the context where it
                  is used.  SQLSTATE=01533
 3245   SQL0206N  "SYSIBM.SYSTABAUTH.CONTROLAUTH" is not valid in the
                  context where it is used.  SQLSTATE=01533
:
 3299   SQL0206N  "UPDATERULE " is not valid in the context where it
                  is used.  SQLSTATE=01533
 3314   SQL0204N  "SYSIBM.SYSPROCPARMS" is an undefined name.
                  SQLSTATE=01532

LINE    MESSAGES FOR db2cli.lst
------  --------------------------------------------------------------------
        SQL0091N  Binding was ended with "0" errors and "62"
                  warnings.
-----------------------8<-----------------------------

0
 
LVL 1

Author Comment

by:dapcom
Comment Utility
For anyone interested in that problem,

IBM knows the problem, but doesn't have a solition right now...I'll post when i know more...

But in short, it seems using sqlserver DataPump action doesn't work well since at least db2connect 7.1.8

Dan
0
 
LVL 1

Author Comment

by:dapcom
Comment Utility
Last news from IBM: to make it work I had to add the following in the db2cli.ini (in folder \SQLLib)

[ConnectionName]
DISABLEKEYSETCURSOR=1
USESERVERKEYSETCURSOR=0
PATCH2=6

It worked fore me.
0
 
LVL 18

Expert Comment

by:BigSchmuh
Comment Utility
Thanks for the info, you should ask for a refund or choose yourself as the expert for EE to improve the KB.
0
 

Accepted Solution

by:
modulo earned 0 total points
Comment Utility
Closed, 50 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now