OLEDB Provider times out

• jere42

  Old Hand

  Points: 305

  More actions

  October 11, 2007 at 9:10 am

  #180139

  I've since narrowed it down to a specific case, it seems to be the INNER JOIN that makes it spaz. Removing the IJ with everything else being equal, it works! Unfortunately, the data is wrong without the IJ. :crying:

  ---

  We have a couple of ACCESS database running queries against our DB2 server and I nabbed them from the first and put it into an SSIS package that used IBM's connection mgr as the OLEDB provider for the connection on the source adaptor. Worked great cause they were your basic hits against a single table (select a,b,...x,y,z from tblA where a='blah' and b='blah'). Now I'm trying the queries from the other ACCESS database which uses the same connection but, inner joins 2 tables from 2 different databases and returns 1 col from one DB and the rest from the other DB. The Where clause is the same as in the first queries that worked except for the addition of an AND term on the 1 and only column from the 1st table.

  It starts off, connects, and plays the little train that could but, it causes the following grief at the DB2 server end (I got this courtesy of the DB2 DBA)

  FROM MI1997.MAP1 AS mine INNER JOIN XDUD0000.XVUD0101_REG_CLMS AS prof

  ON ((prof.CONTRACT_NUM = mine.CONTRACT) AND (prof.PROCEDURE_CD = mine.PROCCODE))

  WHERE ((mine.MONTH='200707') AND (prof.YR_MTH BETWEEN '200707' AND '200708') AND (prof.PARTITION_KEY_CD BETWEEN 91 AND 92) AND (prof.BCBSM_CLM_STAT_CD In('P','R')));

  ---

  BROWSE -- DSNEMSTR.S02253.DATASETS ----------------- Line 00001869 Col 001 080

  Command ===> Scroll ===> CSR

  07.59.59 STC02253 ---- THURSDAY, 11 OCT 2007 ----

  07.59.59 STC02253 DSNL027I -DSNE SERVER DISTRIBUTED AGENT WITH 820

  820 LUWID=GA406928.A23C.071011112841=6624

  820 THREAD-INFO=AB123:LCNU5122797:ab123:devenv.exe

  820 RECEIVED ABEND=04E

  820 FOR REASON=00D3003B

  07.59.59 STC02253 DSNL028I -DSNE GA406928.A23C.071011112841=6624 821

  821 ACCESSING DATA FOR

  821 LOCATION 10.64.105.40

  821 IPADDR 10.64.105.40

  Item ==> * MVS/QuickRef 6.2 * Col 1 Line 1 of 55

  Command ==> Scroll ==> PAGE

  397 FOR REASON=00D3003B

  ---------------- V=IBM P=DB2 ERROR CODES R=V8 I=00D3003B D=M -----------------

  ********************* Text Below Copyright (c) 2007, IBM *********************

  00D3003B

  Explanation: A distributed thread was canceled because the timeout value

  in the DB2 startup parameters module (usually DSNZPARM) was exceeded. This

  value is specified by the IDLE THREAD TIMEOUT value in the DSNTIPR

  installation panel or the IDTHTOIN value in the DSN6FAC macro, which is

  used to build the DB2 start up parameters module.

  System Action: The execution unit abends.

  Operator Response: Notify the system programmer.

  System Programmer Response: The server thread was holding DB2 resources

  and the requester application did not make a request to the DB2 server

  thread for an extended period of time. The server thread is terminated in

  order to releases resources that might affect other threads.

  This usually occurs for one of these reasons:

  o The ACTIVE thread option was specified in the DDF THREADS field of the

  DSNTIPR installation panel, and a requester application or its user

  did not make a request to the DB2 server for an extended period. This

  can happen, for example, during a lengthy end user absence. As a

  result, the server thread becomes susceptible to being canceled

  because of the timeout value.

  Determine why the requester application has not made a request to the

  DB2 server in the specified time. If the design or use of the

  application requires additional time, increase the IDLE THREAD TIMEOUT

  value or set it to zero to deactivate the function.

  o The INACTIVE thread option was specified in the DDF THREADS field of

  the DSNTIPR installation panel, and a requester application or its

  user:

  - Failed to commit before an extended dormant period (such as end

  user absence), or

  - Committed before an extended dormant period (such as end user

  absence), but database resources are still held because of other

  existing conditions.

  As a result, the server thread cannot be moved to the inactive state

  and becomes susceptible to being canceled because of the timeout

  value.

  Determine why the server thread was not moved to the inactive state.

  For information about active and inactive threads, see the description

  of the DDF THREADS option of the DSNTIPR installation panel in

  "Planning and Installing DB2" in Part 2 of DB2 Installation Guide. If

  the design or use of the application requires additional time,

  increase the IDLE THREAD TIMEOUT value or set it to zero to deactivate

  the function.

  ---

  When I take the inner join out of the equation it starts working again.

  I've seen this in ACCESS land where you use passthru queries and have to tell the connection to give me as much time as I need. I set the timeout = 0 and it won't time out no matter how long the query takes! How do I go about doing that for a DB2 connection in SSIS is it possible. I don't think I'm the only one timing out in this situation.

  Thanks for any help,

  Gerald