Error trying to insert to DB2 from DTS

Question

Error trying to insert to DB2 from DTS

Ken-226891

Valued Member

Points: 69
More actions
April 20, 2005 at 10:18 am

#88243

I am trying to insert in to a table on our DB2 mainframe from a DTS package and get the following error. Any thoughts would be appriciated. I have also attached the query from the transformation task. And on a side note even after picking the IBM DB2 ODBC Driver, once I save the package it always changes to the "Other ODBC Data Source" driver.
DATA TRANSFORMATION SERVICES: Data Pump Exception Log
Package Name: dbAI DEV dbENWAgentInfo - Return NeworkId & PDSSystemId
Package Description: DTS package description
Package ID: {A433941D-53EB-442D-B1E2-209BBE910DD5}
Package Version: {24720371-2B31-4500-87B9-DD272E0EA316}
Step Name: DTSStep_DTSDataPumpTask_1
Execution Started: 4/20/2005 11:33:36 AM
Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description:Insert error, column 2 ('NETWORK_ID', DBTYPE_STR), status 9: Permission denied.
Error Help File:sqldts80.hlp
Error Help Context ID:30702
Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description:Insert error, column 1 ('SYSTEM_ID', DBTYPE_STR), status 9: Permission denied.
Error Help File:sqldts80.hlp
Error Help Context ID:30702
Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Description:Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Error Help File:
Error Help Context ID:0
Execution Completed: 4/20/2005 11:33:36 AM

DECLARE @SourceDate datetime
DECLARE @ImportDate datetime
SELECT @SourceDate = getdate()
SET @ImportDate = (SELECT ImportDate FROM tblSystem WHERE RecordId = 1)
set nocount on
SELECT
RTRIM(CONVERT(NVARCHAR(20), PP.PdsPersonId)) PdsPersonId,
  case
  when P.txtLowercaseNetworkId is NULL THEN RTRIM(CONVERT(NVARCHAR(10),'NULL'))
  when P.txtLowercaseNetworkId = 'NULL' THEN RTRIM(CONVERT(NVARCHAR(10),'NULL'))
  else RTRIM(CONVERT(NVARCHAR(10), UPPER(P.txtLowercaseNetworkId)))
END AS txtLowercaseNetworkId
FROM
tblPerson P inner join
tblPdsPerson PP on isnull(PP.AliasPersonId,PP.PersonId) = P.PersonId and
  (PP.AliasPersonId is null or PP.AliasPersonId = PP.PersonId) and
  PdsPersonId not like ('S%') and right(PdsPersonId,3) not like ('per')
WHERE
P.NetworkIdLastUpdate > @ImportDate
set nocount off
UPDATE tblSystem SET ImportDate = @SourceDate WHERE RecordId = 1

Kindest Regards,
Ken
SELECT * FROM USER WHERE CLUE > 0

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

EdVassie SSC Guru Points: 60472 More actions · Answer 1

Have you checked what is happening at the mainframe end? The 'permission denied' message may be telling the truth.

I suggest you talk with your DB2 colleagues to try to work this one out. If you have never got a transfer to DB2 working before, then it may be worth starting off with inserts to a crash&burn database, so you can get the syntax and permissions right, before trying the insert into a live database.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Ken-226891 Valued Member Points: 69 More actions · Answer 2

It seems to be a DB2 connect issue. When I use the DB2 provider instead of the driver and execute it with DB2 connect version 7.0(thats whats on my laptop) it runs, but when I schedule the job and run it from the DEV server, if fails.

Kindest Regards,

Ken

SELECT * FROM USER WHERE CLUE > 0

EdVassie SSC Guru Points: 60472 More actions · Answer 3

You really should be using DB2 Connect between SQL Server and the mainframe if you are moving anything more than a trivial amount of data.

IBM have chosen to package DB2 differently to the way Microsoft package SQL Server. The free DB2 functionality gives row-by-row data transmission, while DB2 Connect gives rowset transmission. Your network costs for large data volumes will be significantly lower when using DB2 Connect than when using the free stuff.

DB2 V7 uses an old 'private protocol' for rowset transmission, but DB2 V8 has standardised on DRDA, an open protocol. Even though DRDA is still pretty much DB2 only, because it is open there are some vendors out there providing an alternative to DB2 Connect for V8 at a different price point.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Ken-226891 Valued Member Points: 69 More actions · Answer 4

The provider is also IBM. When installing DB2 connect you get both the IBM DB2 Driver and the IBM DB2 Provider. When I use the provider over the driver with the old version v7.0 it works, but with the new version it does not.

Kindest Regards,

Ken

SELECT * FROM USER WHERE CLUE > 0

rchristo1 SSC Rookie Points: 46 More actions · Answer 5

We are having a similar problem. When we use the IBM Version 7 driver we can insert into the mainframe DB2 table. When we use the IBM version 8 driver we fail on every row. We can access the table with either driver and display the data. Only fails on the insert.

Ken-226891 Valued Member Points: 69 More actions · Answer 6

I currently have an open ticket with IBM. I will be sending them a trace from both the 7 version and the 8 version either Friday or Monday, when ever I can get to it. Hopefully they'll have a solution, and I'll post it up.

Ken...

Kindest Regards,

Ken

SELECT * FROM USER WHERE CLUE > 0

Ha Le Grasshopper Points: 22 More actions · Answer 7

We are having the same issue. Did you get any resolution from IBM? Thanks.