Inserting values from IBM DB2 into Teradata

[Kavya432]

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 );

[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

[jrgo · Accepted solution]

@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

[Kavya432]

Thanks A lot Jimmy. It worked after that change. Appreciate your help!

Thanks

Kavya