DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
maddy342
Participant



Joined: 12 Apr 2010
Posts: 1

Points: 52

Post Posted: Mon Apr 12, 2010 10:24 pm Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: TX
OS: Windows
Hi
I am new to DSTX and I am trying to run a map that takes data from a text file and writes it to a database table. I created the output card type tree from a table in the .mdq file. I am picking SQL Server Database as the target, I have used -T command for trace, I have tried several options w/o success. I have filled out the DatabaseQueryFile section with .mdq file, database name and table name. When i build the map it says target not available. And below is the trace ,please help.And let me know what is the Error ..Thanks

Validating the adapter command...
<5120-4380>: Database type is MS SQL Server 7
<5120-3920>: Connecting...
<5120-3920>: Datalink: SQL004\\DEV1
<5120-3920>: UserId : sa
<5120-3920>: Password: *****
<5120-3920>: Context: Output Card, Transaction scope: Map
<5120-3920>: On Failure: Rollback, On Success: Create
<5120-3920>: TRACE command specified, file: C:\Documents and Settings\paul01\Desktop\29yyy\M_S_DB.dbl
<5120-3920>: Table name: TABLE_THREE
<5120-3920>: Local transaction usage: Transaction ID 0x01546248
<5120-3920>: Transaction started - ISOLATIONLEVEL_READCOMMITTED
<5120-3920>: Connection to SQL Server SQL004 has been established.
<5120-3920>: Retrieving 1 rows per fetch.
<5120-3920>: Returned status: (0) Success
<5120-3920>: Interface library version 8.1(38)
<5120-3920>: Map: C:\Documents and Settings\paul01\Desktop\29yyy\M_S_DB.dbl.mmc, Timestamp: 04/09/09 10:42:12.
<5120-3920>: Loading data for output card 1.
<5120-3920>: Database adapter: OLE DB / SQL7 Version 8.1(38)
<5120-3920>: Starting database load to table TABLE_THREE...
<5120-3920>: Datalink: SQL004\\DEV1
<5120-3920>: UserId : sa
<5120-3920>: Password: *****
<5120-3920>: Context: Output Card, Transaction scope: Map
<5120-3920>: On Failure: Rollback, On Success: Create
<5120-3920>: TRACE command specified, file: C:\Documents and Settings\paul01\Desktop\29yyy\M_S_DB.dbl
<5120-3920>: Table name: TABLE_THREE
<5120-3920>: Update mode is off.
<5120-3920>: Retrieving 1 rows per fetch.
<5120-3920>: The columns are of the following types:
<5120-3920>: Column 1 (IN_ID) type is int [DBTYPE_I4].
<5120-3920>: Column 2 (UN_DT) type is datetime [DBTYPE_DBTIMESTAMP].
<5120-3920>: Column 3 (TN_ID) type is varchar(6) [DBTYPE_STR].
<5120-3920>: Column 4 (A_B) type is int [DBTYPE_I4].
<5120-3920>: Column 5 (C_D) type is int [DBTYPE_I4].
<5120-3920>: Column 6 (E_X) type is int [DBTYPE_I4].
<5120-3920>: Column 7 (B_T) type is int [DBTYPE_I4].
<5120-3920>: Column 8 (P_F) type is int [DBTYPE_I4].
<5120-3920>: Column 9 (A_T) type is int [DBTYPE_I4].
<5120-3920>: Column 10 (R_O) type is int [DBTYPE_I4].
<5120-3920>: Column 11 (L_P) type is int [DBTYPE_I4].
<5120-3920>: Column 12 (R_I) type is int [DBTYPE_I4].
<5120-3920>: Column 13 (T_I) type is int [DBTYPE_I4].
<5120-3920>: Column 14 (CNT) type is int [DBTYPE_I4].
<5120-3920>: Column 15 (CV_DT) type is datetime [DBTYPE_DBTIMESTAMP].
<5120-3920>: The insert statement to be executed is:
<5120-3920>: INSERT INTO TABLE_THREE VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
<5120-3920>: OLE DB Error code: 0x80040e2f
<5120-3920>: The statement has been terminated.
<5120-3920>: The following values were being inserted:
<5120-3920>: Column 1 IN_ID : 1
<5120-3920>: Column 2 UN_DT : 2009-2-21 00:00:00.000
<5120-3920>: Column 3 TN_ID : ABCDS
<5120-3920>: Column 4 (A_B) : NULL
<5120-3920>: Column 5 (C_D) : NULL
<5120-3920>: Column 6 (E_X) : NULL
<5120-3920>: Column 7 (B_T) : NULL
<5120-3920>: Column 8 (P_F) : NULL
<5120-3920>: Column 9 (A_T) : NULL
<5120-3920>: Column 10 (R_O) : NULL
<5120-3920>: Column 11 (L_P) : NULL
<5120-3920>: Column 12 (R_I) : NULL
<5120-3920>: Column 13 (T_I) : NULL
<5120-3920>: Column 14 (CNT) : NULL
<5120-3920>: Column 15 (CV_DT) : 2009-12-20
<5120-3920>: Failed to insert a row (rc = -6).
<5120-3920>: Failed after 1 rows inserted.
<5120-3920>: Database load complete.
<5120-3920>: Returned status: (-6) No error text found
<5120-3920>: Cleaning up and closing the transaction...
<5120-3920>: Returned status: (0) Success
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42622
Location: Denver, CO
Points: 219444

Post Posted: Tue Apr 13, 2010 6:44 am Reply with quote    Back to top    

Not a TX nor SQL Server person, but if you google for that OLE DB error it looks to be a unique index / primary key duplicate value issue.

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
rep
Participant



Joined: 19 Jun 2007
Posts: 82
Location: New York City
Points: 1097

Post Posted: Tue Apr 13, 2010 8:09 am Reply with quote    Back to top    

<5120-3920>: INSERT INTO TABLE_THREE VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
<5120-3920>: OLE DB Error code: 0x80040e2f
<5120-3920>: The statement has been terminated.
<5120-3920>: The following values were being inserted:



It has been a very long time since I've connected to a DB. Years, litteraly.

From what I recall, you obviously have to have access to the table. You know you have valid access while in the DB interface designer, when you click on a table an it allows you to build the type tree (the window comes up for "generate type tree from table").

Once you select the table and can access it, you must "set update keys". Include all the fields that are your keys in the top right window, and all the fields you want to update in the bottom window. In the event you have did NOT select a field you want to update, maybe that's causing the error. Once you set those keys and fields PROPERLY, you build the type tree. If you made a mistake, delete the type tree, make sure you set the fields properly, then re-build it.

My suggestion to test is to not use the text file from the get go; just have one output card with the DB, and try to insert a record by values you set in the map ( the rule for field1 is ="test1"). You have to put " -UPDATE" with the "-T" on the DB command line.

This looks wierd to me, but I really do not know exactly what wrong, but from what I recall (and really, I may be wrong), it will show the data you're trying to put in the fields. I see only see question marks.

<5120-3920>: INSERT INTO TABLE_THREE VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
<5120-3920>: OLE DB Error code: 0x80040e2f
<5120-3920>: The statement has been terminated.
<5120-3920>: The following values were being inserted:
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours