Inserting values from IBM DB2 into Teradata

Hi Team,

I am trying to insert values from IBM DB2 table into teradata database table.Please find the attached data flow of mine. I am extracting the data successfully but while inserting into Teradata its causing an issue.

I tried post sql and all different methods but didn't had any luck. Appreciate you response.

Below is the table syntax I used to create table in Teradata, its been successfully created but couldn't able to insert the values into the table through Alteryx.

 CREATE MULTISET TABLE CA_Business_Info_Team.NWACD_AMF ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO     (
Record INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 
INCREMENT BY 1 
MINVALUE 1 
MAXVALUE 2147483647 
NO CYCLE),

RES_ST_# VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
RES_ST VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
RES VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
NPN VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
LAST_NAME VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
FIST_NAME VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
AGENT_DEFf VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
PDCR_TYPE VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
APPT_STATUS VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
DISB VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
LIC_NUM VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
EMAIL VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
AGENCY_NAME VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
AGENCY_TID VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
REGION VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
STATE VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
MKTG_TERR VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC)PRIMARY INDEX ( NPN );

AMF automation.yxmd

Database Connection

Accepted answers

jrgo

@Kavya432

Looking at the first error message regarding the object not existing, I'm thinking that your ODBC DSN may not have a default database defined. Its not required that a default DB be defined, but when there's not, you have to be more verbose to where your target table can be found. It needs to be entered as <database>.<table> syntax.

Assuming that:

DB = CA_Business_Info_Team

Table = NWACD_AMF

... update your output tool table reference to CA_Business_Info_Team.NWACD_AMF

Using a PostSQL is not going to work for this purpose.

Hope this helps!

Jimmy
Teknion Data Solutions

All comments

jrgo

Hi @Kavya432

I noticed in your Output tool that you have the option "Take File/Table Nam

e From Field" enabled. As it's configured it's modifying the target output table that's configured in the tool.

To elaborate further, the output tool is targeting the table "NWACD_AMF". But with the configuration, at runtime it'll actually target

"NWACD_AMF<value for [AGENCY_NAME]>"

If this was intentional, you may want to verify that the table for each "AGENCY_NAME" exists. You could also disable this option just to confirm whether it still generates an error or not to rule this issue out.

Also, I recall that Alteryx did have issues inserting records into tables that contained an auto incrementing identify column, but this was for MS SQL Server. I think it was addressed, but could be something with TD that wasnt fixed... this is completely speculatory though.

Hope this helps!

Jimmy
Teknion Data Solutions

Kavya432

Thanks for your response Jimmy,

The option was unintentional and I unchecked the box you mentioned below, but it still showing an error message. Is there anything I am missing in Teradata connection setup. Not sure.

below is how I configured Teradata as output file.

I tried to add post sql as below:

INSERT INTO CA_Business_Info_Team.NWACD_AMF
(
RES_ST_# ,
RES_ST ,
RES ,
NPN ,
LAST_NAME ,
FIST_NAME ,
AGENT_DEFf ,
PDCR_TYPE ,
APPT_STATUS ,
DISB ,
LIC_NUM ,
EMAIL ,
AGENCY_NAME ,
AGENCY_TID ,
REGION ,
STATE ,
MKTG_TERR
)

VALUES
( ?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?

)

Its still shoes the error message thought he values are 17 from input file and I am creating the same.

Appreciate your Response!

Thanks

Kavya

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703