AS/400 DB2 adapter to SQL adapter error

 

Hi,

I have been attempting to create a solution that will move data between an AS/400 DB2 database and a SQL 2005. But I keep getting the following error:

The adapter “SQL” raised an error message. Details “HRESULT=”0x80040e14″ Description=”Could not find stored procedure ‘WWTSREG’.”

WWTSREG is the name of the table/file from the DB2 file (also the table node of schema file) and I don’t know why the orchestration simply neglects the schema mapping that I created and still uses the source schema for the SQL send port.

Really hoping somebody can help out on this trouble. And my project set up details are listed below if someone needs it for diagnosis.  Thanks.

 

 

 

<DB2 Schema Generation>

SELECT * FROM AAA.BBB.WWTSREG (db info masked)

 

(db info masked)

Intial catalog

AAA

Package collection

NULLID

Default schema

BBB

 

Document Target Namespace: http://DB2abif02

Document Root Element Name: DB2abifReq02

 

<SQL Schema Generation>

Document Target Namespace:http://SQLabif02

Document Root Element Name for Request:SQLabifReq02

Document Root Element Name for Request:SQLabifRep02

 

<Mapping>

just map the fields one by one

 

<Receive Pipeline>

(I’ve tried both including or not cluding the Receive Pipeline)

XML Disassembler into the Disassembler box, choose DB2 schema for Document Schema

 

<Orchestration Objects>

Message_1

Identifier: Db2_Msg

Message Type: Schemas-> DB2 Service Schema

 

Message_2

Identifier: SQL_Msg

Message Type: Schemas->SQL Reuqest Schema

 

PortType_1

Identifier: Db2_Rcv_PortType

Message Type: DB2 Service Schema

 

PortType_2

Identifier: SQL_Snd_PortType

 Message Type: SQL Reuqest Schema

 

<Orchestration Design>

 

Port_1

Name: Db2_Rcv_Port

Port Type: Db2_Rcv_PortType

Port Direction: Receive

Port Binding: Specify later

 

Port_2

Name: SQL_Snd_Port

Port Type: SQL_Snd_PortType

Port Direction: Send

Port Binding: Specify later

 

Receive

Message: Db2_Msg

Operation: Db2_Rcv_Port.Operation_1.Request

 

Construct Message

Message Constructed: SQL_Msg

 

Transform

 

Select the Ellipses from the Input Messages property and from the Transform Configuration dialog box that  appears, select Existing map. From Source select Db2_Msg and for Destination, select SQL_Msg.

 

Send

Message: SQL_Msg

Operation: SQL_Snd_Port.Operation_1.Request

 

<DEPLOY>

assign app name

assign strong name key

deploy

 

<CONFIGURE PORTS – Receive Port>

Receive Port :Db2_RP_abif02

Receive Location: Db2_RL_abif02

Type: DB2

Document Target Namespace: http://DB2abif02

Document Root Element Name: DB2abifReq02

SQL Command: SELECT * FROM AAA.BBB.WWTSREG (db info masked)

Pipline: PassThruReceive (if not receive piline in the project file)

 

<CONFIGURE PORTS – Send Port>

Receive Port :SQL_SP_abif02

Type: SQL

Pipline: PassThruTransmit

 

Document Target Namespace: http://SQLabif02

Document Root Element Name: SQLabifReq02

Filter: BTS.ReceivePortName = Db2_RP_abif02